Excel Frequently Asked Questions and Answers. An ever growing list of popular questions and answers.

Excel Newsletter Archive Have a browse through our free Excel newsletter archives. Here you will find a bit of everything from basic shortcuts to VBA for Excel. If you like what you see then sign up, it's completely free!

Excel Date and Times This page is a must if you are wanting to work with dates and times in an Excel spreadsheet. It explains how Excel stores dates and times, Excel formulas for time & dates, give the magic numbers needed for dates and times and date and time conversions and functions.

Excel Calendar for Valid Dates Easy to follow steps on how to create a user friendly Excel Calendar.

Convert Dates How to convert those imported dates.

Excel SUM Formula Probably, the most widely used Excel formula, the SUM function in Excel is specifically designed to add values from different ranges, or one range. The SUM formula can be typed into a cell in Excel, or inserted via the Insert Function tool to the left of your Formula bar.

Excel Autosum Function Because adding numbers is probably the most common function that Excel is used for, Excel has a built-in Feature called AutoSum located on the Standard toolbar.

Round Excel Numbers Methods and Excel Formulas for rounding numbers in Excel

Mask Time Entries in Excel Unlike Access, Excel does not allow for us to mask time and/or date entries. However, we can use the Worksheet_Change Event to achieve masked time entries

Mask Date Entries in Excel

VLOOKUP Perhaps one of Excels most commonly used Excel Formulas is the VLOOKUP. It is also possibly the Excel formula that most people have problems understanding.

Excel HLOOKUP Another one of Excels most commonly needed Excel Formulas is the HLOOKUP.

Left Lookup in Excel Excel is very rich in Lookup formulas, with perhaps the VLOOKUP being the most popular. However, the draw-back with all Excel's Lookup formulas is that they will only look in the left most column and return the result from the corresponding cell to the right. There are times when users need to lookup data in any column of table and return the corresponding cell to the left.

Find Nth Occurrence in Excel As you may already know, we can use VLOOKUP, or INDEX/MATCH to locate the first occurrence of a specified value in a list, or table of data. However, Excel has no ready made formula that allows us to locate say the second, or third occurrence etc of a specified value.

Excel Lookup Table VLookup is the perfect Excel formula for numerical values contained in a range.  However if you tried to use VLookup with text in a table, it's use would be limited, For example surnames such as Smith, Smithson, Smithy, Smithson-Jacobs would create problems

Nested IF Formula Limitation One limitation of Excel is that we can only nest Excel formulas up to 7 levels. This is particularly limiting when trying to add nested IF Functions/Formulas that require greater than 7 conditions.

Increase/Decrease Values If you have values on an Excel Worksheet that you need to permanently increase, or decrease you can use Paste Special. No Excel formulas needed!

Calculate a Persons Age in Excel We can use the Excel formulas shown here to return the age of any person in years, months and days. It makes use of the little known DATEDIF Excel formula/Function

Average Excluding Zeros Excel has a built in formula/function that makes averaging a range of cells easy. However, the Excel formula AVERAGE does not exclude zeros.

Minimum Excluding Zeros Excel has a built in formula/function that makes finding the minimum value in a range of cells easy. However, the Excel formula MIN does not exclude zeros.

Return Weekday Day of Week To return the weekday of any date in Excel we can use any of the Excel formulas, or methods shown on this page.

Dynamic Named Ranges Dynamic ranges are ideal for charts and Pivot Tables. If you have never used dynamic ranges in Excel then you WILL after reading about them here. If you have used them, then look here for some news ways and means. There are easy to follow step-by-step instructions.

View Multiple Sheets of an Excel Workbook A very handy feature of Excel is its ability to allow you to create a copy of a workbook and compare and work with the copy whilst simultaneously working with and viewing the original workbook.  There are two ways in Excel that you can do this.

View Multiple Different Workbooks To allow you more flexibility when working with workbooks, Excel allows you to view workbooks in different windows by offering four different custom views to work with.

Save as Workspace If you wish to compare either multiple workbooks, or the same workbook over and over again,  and you have set up a custom view to do this, Excel has the facility to save a custom view as what is known as a workspace

Excel Freeze Panes When you have a large worksheet, it is often required that column or row labels remain in view.  By using Excel's Freeze Panes feature you can freeze either columns or rows in your worksheet so that they remain visible whilst you scroll.

Excel Split Panes A very handy feature of Excel is its ability to allow you view more than one copy of your worksheet, and for you to be able to scroll through each pane of your worksheet independently.

Excel Pivot Tables Take the FREE online tutorial on Excel Pivot Tables. Download the Excel Workbook examples are more.

Grouping Pivot Table Data Grouping Pivot Table Data. How to Group so you don't get: Cannot Group that Selection

Array Formulas in Excel I strongly suggest you read this very important information on using array formulas in your spreadsheets. Array formulas can let you specify more then one criteria to Sum, Average, Count etc by. Many examples of how to use them.

Sum With Multiple Criteria Examples of Excel formulas to sum a range of cells that meet multiple criteria. ,DSUM, SUMPRODUCT and SUM with an IF function/formula.

Count With Multiple Criteria Examples of Excel formulas to count a range of cells that meet multiple criteria. ,DCOUNT, DCOUNTA, and SUM with an IF function/formula.

Calculate Sliding Scale Tax Calculating tax, or commission that is based on a sliding scale, or by bracket, can be complicated. The Excel formula here is one that can be used using Excel's built in function/formulas. That is, the IF function/formula and the SUM function/formula

Calculate Fixed Rate Tax The Excel formula VLookup can be used to look for numeric values in lists and tables and to use those values in a calculation.  A perfect example of how VLookup can be used to glean information is to work out how much tax you are required to pay.

 
Count Words in a Cell Unlike Microsoft Word, Excel does not give us a ready made way to find out the number of words in a cell, or a range of cells containing text, or words. These Excel formulas show you how.

Extracting Words From Text in Excel Excel has very powerful and useful Text formula/functions that we can use to extract words from a string of words. Or, put another way, parse out specific words from text. The examples here show how we can use the Excel formulas to do this.

Convert Text to Upper/Proper Case Excel has 2 built in Excel Formulas for converting text to either UPPER CASE or Proper Case. However, there are many instances when using the Worksheet Function approach is not practical.

Workbook Downloads Here you will find some free workbook downloads that demonstrate, Excel formulas, features, functions and code.

All Excels Keyboard Shortcuts A complete list of Excel keyboard shortcuts.

Efficient Excel Spreadsheet Design This page is a must read for anyone with the task of designing and setting up an Excel spreadsheet. It talks about formatting, layout and calculation speed.

Efficient Excel Printing The content of a Worksheet is far more important than using a great deal of formatting to pretty it up (although there are some basic fundamentals for efficient Excel Spreadsheet Design).

Headers & Footers in Excel As the names suggest, a Header is something that appears at the top of every page, and a Footer is something that appears at the bottom of the page..

Workbook Size Blow-out and Phantom Links. Here are some step-by-step instructions that you can use to get your file size back to where it should be. There is also step-by-step instructions on how to get rid of those phantom links.

Limit/Restrict Scroll Area on an Excel Worksheet With Excel having 256 Columns and 65536 rows you may wish to limit, or restrict, the area that users can scroll to on your Worksheet.

Excel AutoFilters One of the most useful functions in Excel is the AutoFilter.  The AutoFilter allows a user to filter items in a list according to a set criteria. You can filter text, numbers or dates with AutoFilter.

Display AutoFilter Criteria Excel's AutoFilter is one of Excel's most useful features. However, the one small draw-back is it's hard top tell the criteria being used at a glance.

Excel Advanced Filter Other than Excel's very popular Auto Filter, we have at our disposal Excel's Advanced Filter. In a nutshell, Excel's Advanced Filter can be used to filter by more than 2 criterion, where Auto Filter has a max of 2! It also has a very nifty feature that allows us to create a list of unique items from a list with repeated data. In addition, we can even use Excel formulas for Excel's Advanced Filter criteria.

Goal Seek Goal Seek can be used when you know the result of a formula, but not the input value required by the formula to decide the result, reverse calculation.

Conditional Formatting In Excel 97, Microsoft introduced a nifty new feature called Conditional Formatting. It allows us to format a cell, or range of cells, based on a specified criteria for the cell(s), or other cell(s).

Conditional Formatting 3 Criteria Limit If you are familiar with Conditional Formatting in Excel you will know it has a 3 criteria limit. However, with the aid of Excel Custom Formats we can have up to 6 Conditional Formats for Font color. If you need background color and/or you need text values, see Conditional Formatting 3 criteria Limit for a Excel VBA method.

Excel Consolidate Consolidation is the process of combining values from several ranges of data either from within the same or different workbooks. It can be used to summarize data from different worksheets into master worksheet and create a report using a variety of calculations.

Excel Auto Outline Outlining is a means of viewing levels of detail as required by collapsing or expanding to hide or show information. You can identify subtotals and hide or collapse detail so that only subtotals appear on the screen.

Enable Outline/Outlining on a Protected Worksheet In Excel 2000 Microsoft added many new levels of Worksheet protection to Excel. Unfortunately they have neglected to add one that would allow Excel users to use Grouping/Outlining on a protected Worksheet.

Excel Data Tables Data Tables are a range of cells that are used for testing and analysing outcomes on a large scale.
 
Excel Scenarios Excel's Scenario Manager is a tool that can be used to determine different projected outcomes of data by changing different cells within a Worksheet model.