Excel – Ten Useful Tips

These are ten useful tips at varying knowledge levels (I’ve indicated the knowledge level in italics in brackets for each point: basic, intermediate, advanced and expert).  These are all things I have recently used and/or explained in response to training questions and are in no particular order. I hope that you find these useful.

 1. Dynamic range names (advanced)

• These are range names which will vary as your data size varies. They are particularly useful where you are downloading data on a regular basis (e.g. sales orders) and the number of entries in the list will vary on each download occasion
• To make a range name dynamic you replace the standard range (e.g. =Sheet2!$G$5:$G$16) replace this with an offset formula such as =OFFSET(Sheet2!$G$5,,,COUNTA(Sheet2!$G$5:$G$50))
• As with fixed range names, dynamic range names can be used in formulas

 2. Pivot table slicers (intermediate)

• Slicers add the ability to filter a pivot table.  You can add one or many Slicers to your pivot table and they can be linked to several pivot tables if they come from the same data source
• Once your pivot table is created you can set up slicers from the icon on the PIVOT-ANALYZE ribbon

3. Index with match and indirect (advanced)

• INDEX is a lookup type formula.  It comes in two forms. The use below relates to the INDEX(Array, Row number, Column number) form
• On its own INDEX has limited use but is very powerful when combined with MATCH() and INDIRECT()
• MATCH can be used in place of the row and/or column part of the formula. It becomes powerful when you are matching  a selectable item from your spreadsheet(e.g. through a data validation list or a combo box)
• INDIRECT can be used in place of the array part of the formula and I use this when I want to pull data from different worksheets in the workbook. It converts a text string into a range that excel can use. It is at its best when used in conjunction with range names. E.g. I may have data for budgets, actuals and forecasts on different sheets. Having set up range names for each of these data sets, I then use these in a data validation list to select which set of data I require
• A full INDEX formula would look like =INDEX(INDIRECT(CustName), Match(costtype,$C$3:$C$30,0), Match(month,$B$2:$B$11,0))

4. Using autofill instead of dragging formulas down (basic)

• Double click on the small black square at the bottom right of the cell (or cells) that you have selected (within the red circle in the screenshot below). This square is called the ‘fill handle’ and will copy the contents of your cell(s) down as far as the data in the adjacent column on the left (in this case Column B).
• A couple of points to note on autofill: 1. If there is a gap in the adjacent column then the auto fill will only go to this point – 2. If you want to auto fill with exactly the same number then you need to highlight a block of two cells with that number.

5. Using NA() in charts (intermediate)

• Useful in line charts where no data exists. Without using =NA() the line would drop to the horizontal axis. If you use the +NA() formula in the chart data (e.g. =IF(C18=”Act”,C4,NA()) then the line is not drawn where the NA is produced. The example below plots 5 months data for where we have actuals for Jan to May: 

 6. SUMIFS  – dispense with SUMIF (intermediate)

• Now that the SUMIFS has been created in Excel you do not need to use the older SUMIF formula any longer. This is because SUMIFS is designed to work with multiple criteria it works equally well with a single criteria.  Additionally, the parts of the SUMIFS and SUMIF formulas work in a different order which can be confusing when you move from one to another.  Therefore, if you are new to these formulas then use SUMIFS and don’t bother with SUMIF

 7. Make your check totals equal zero if everything is correct.

• You should always include a check that your output report adds up to the same total as your input data.
• g. Subtract the total in your summarised report from the total of your input data.
• This will give zero if the values match and a value if they do not match (positive if your report total is less than the total of your data, negative if your report total is greater than the total of your data)
• Formatting zeros not to appear (with a number format) makes it even easier to spot where errors are indicated as these will be4 the only cells with values showing.

 8. Check data completeness using MATCH and COUNTIF (intermediate)

• After downloading detailed information from a system there is often a need to check if your spreadsheet is up to date e.g. if you are reporting sales product and new products are constantly coming on line. Using COUNTIF or MATCH are two good ways to check if there are new products to be included in your spreadsheet.
• COUNTIF method:
  • in a spare column to the right of your data (never insert a column in your data as it makes it much harder to refresh) use =COUNTIF(array, item) where array equals existing list of products in your spreadsheet and Item references the product name in the download.
  • This will return 1 if the product exists or zero if it doesn’t. A zero means that there is a new item in the download that doesn’t exist in your data.
  • Use conditional formatting to highlight zeros –especially if the list is long.
• MATCH method:
  • in a spare column to the right of your data (never insert a column in your data as it makes it much harder to refresh) use =Match(item, array, 0) where item references the product name in the download, array equals existing list of products in your spreadsheet and the zero will look for exact matches only.
  • This will return a number representing the row the product is on within your existing data or #N/A which means that there is a new item in the download that doesn’t exist in your data.
  • NB for the array part of the formula you could select the whole column that contains existing products. The MATCH formula will then return the row in the spreadsheet where the product is contained making it easy to find should you need to do this

9. Data validation – List option (intermediate)

• Data validation is a tool to control manual inputs into a cell. There are many options for this such as whole number, date etc but I find that the most useful is the list option. This restricts the input to a specific set of items such as names and is especially useful where the user wishes to ensure that something is spelled in a particular way so that it can be used elsewhere in the spreadsheet. In particular it will help protect against typos which we all make at some time or another!
• The source can be defined as a standard range or as a range name  – see examples below.
• 

  
•  I have experienced many examples where the main systems of a business spell a particular item in different ways (e.g. region, product, division) as do other spreadsheets that you may be using as a source of data. Almost always your spreadsheet will have a particular spelling to identify an item that you need to use consistently throughout the workbook – the Data Validation-List option is the way to guarantee this for manual entries.

10. Using Camera tool to create a dashboard (basic)

• The camera tool creates a linked picture of a set of cells in your workbook and allows you to paste this picture into another place in the work book such as a dashboard page. As you update the original cells then the picture in the dashboard is also updated. This is useful when you have 2 or more tables of numbers that you want to show in a dashboard with different numbers of columns as these can be shown beside or underneath each other without having to force fit columns. You can format these linked pictures and other objects such as charts to be the same size.  One note of caution: be careful not to create some bizarrely stretched fonts.