Search Not Just Numbers


Tuesday, 21 October 2014

Excel Tip: Eliminating #DIV/0! and other errors - an update

A while back, I wrote a post explaining an approach to eliminating #DIV/0! and other errors in Excel.

Although the information in the original post is still valid, there is a more streamlined approach you can use in more recent versions of Excel (Excel 2007 onwards).

But before we get into that, a quick reminder that Mynda Treacy's Excel Dashboards course is closing shortly, so get yourself over there if you don't want to miss out!

Anyway, on with the post...

If you want to read the original post (out of curiosity or to stay compatible with Excel 2003 and earlier), you can do so here,

Where the original post used a combination of IF and ISERROR, the newer approach uses the IFERROR function.

The syntax for the IFERROR function is as follows:

=IFERROR(value,value if error)

The value argument can be any calculation that may result in an error message, and the value if error argument is what you want to replace the error with.

So, say you have a margin percentage calculation that divides Profit in cell B5 by Sales in B2. You might use the following formula formatted as a percentage:


However if, in some instances, there might be zero sales, then this function will return the #DIV/0! error.

Instead of seeing #DIV/0! in this case, we can choose what should appear. We may prefer to show zero percent, or a dash, or nothing, or a message such as "No Sales". We can do any of these with the IFERROR function as follows:




=IFERROR(B5/B2,"No Sales")

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 14 October 2014

Excel Tip: Using an Override List to Tweak Imported Data

This week's post is not so much learning a new Excel function, as learning a concept that you can use to enable you to import data and correct it, without overwriting the imported data. This is important, so that you can refresh the imported data later!

Before we get into that though, just a quick reminder that the 20% discount offer on Mynda Treacy's Excel Dashboards course expires on Thursday, so if you're interested you need to get over there now.

Excel is a great tool for reporting on data held in an external database (this could be anything, such as an accounting system, ERP/MRP software or maybe a CRM system). However, sometimes we need the ability to edit or override the imported data with some tweaks that we wish to see included in the final reports.

In most cases, I would encourage any changes to be made in the external database, then the data to be refreshed, however this is not always possible. This can particularly be the case with transactional data (such as orders, invoices, etc.). Once an order is complete, most systems won't allow you to edit it, so if you need the order to be reflected differently in the final reports, this will need to be done in the spreadsheet.

You could over-type the imported data to make the changes, but this would be overwritten next time you refreshed the link to the external data. Far better to hold the changes in the spreadsheet to be applied to the data as it comes in. This is where I use what I call an override list.

As long as the imported data has a unique reference that you can link the adjustments to, you can use this approach. Simply have a separate sheet in the spreadsheet where you enter the references of any records that you want amended alongside the amendments. You can look up these amendments  in calculated columns alongside the data.

Say that you are importing a list of sales orders but that they are not always allocated to the correct salesperson in the external database. You could have an override list with the following two columns:

  • Sales Order No
  • Salesperson
Then for any orders that you needed to correct, you could type that order number and the salesperson that you wish to be allocated to that order.

Then all you need is a calculated column alongside the imported data that uses the new value if the Sales Order in question is on the list or the original salesperson if not.

Assuming that the imported data is in a table where two of the headings are Sales Order No and Salesperson, and the override list as described above is in the first two columns of a sheet called Override, then the following formula will work in the calculated column (which we might call CalcSalesperson:

=IF(COUNTIF(Override!$A:$A,[@[Sales Order No]])=0,[@Salesperson],VLOOKUP([@[Sales Order No]],Override!$A:$B,2,FALSE))

This uses an IF statement to check whether this row's Sales Order No appears in the first column of the Override sheet (The COUNTIF counts the occurrences of the Sales Order No in that column). If this returns zero, then the Salesperson field is used, otherwise the amended Salesperson is looked up (using VLOOKUP) from the override sheet.

Any references to the data (in PivotTables or SUMIF formulae for example) can now refer to CalcSalesperson, rather than Salesperson.

The specific formula will vary depending on what you are trying to adjust, but the principle remains the same - and the changes will be applied every time that particular Sales Order No appears in the imported data.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 7 October 2014

Excel Tip: Using the HYPERLINK function

This week, I want to introduce you to the HYPERLINK function.

This function allows you to add hyperlinks to your spreadsheet for both navigation, and to link to external websites or files.

And, because it is an Excel function, you can use cell contents elsewhere in the spreadsheet to determine the hyperlink.

The syntax of the HYPERLINK function is as follows:

=HYPERLINK(Link,[Friendly Name])

Link can be a URL or a file reference. So, some possible examples are:

"C:\My Documents\SalesSpreadsheet.xlsx"
"[C:\My Documents\SalesSpreadsheet.xlsx]Consultancy!A1" (note that this one will go to cell A1 of the Consultancy tab of SalesSpreadsheet.xlsx)

Friendly Name is optional and is the text that you want to use as the hyperlink. So, for example:

=HYPERLINK("[C:\My Documents\SalesSpreadsheet.xlsx]Consultancy!A1","Consultancy Sales")

will show a clickable hyperlink that says Consultancy Sales - and clicking on it will take you to cell A1 of the Consultancy tab of SalesSpreadsheet.xlsx.

If this optional argument is not entered, the Link itself will appear as the hyperlink.

The function really comes into its own though when we use other cell addresses in the arguments. If you have two columns of data being company name and website address, for example, then you could have a third calculated as


which will show the company name (from column B) as a clickable hyperlink that will take you to the company website (from column A).

Or you could use text functions such as the ampersand to create the links. For example you could have a list of sales transactions and the documents stored as Word documents with the invoice number as the file name.

Say column A contains the invoice number and the directory the invoice documents are in is C:\Invoices\, The following HYPERLINK formula will give you a clickable version of the invoice number that takes you to the document:


If A1 contains the invoice number 324, then the function will return a clickable 324 that takes you to the document C:\Invoices\324.docx.
Learn Excel Dashboard Course

Before I go, I have some great news. Mynda Treacy's Excel Dashboards course is once again available - for a limited time.

Order before 16th October 2014 to get 20% off and, as a special offer from me, enter the code FEECHAN when you order and I will send you my Introduction to Pivot Tables course absolutely free.

Click here to find out more about the Excel Dashboards course and to take advantage of this offer.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Tuesday, 30 September 2014

Excel Tip: What's the difference between Delete and Clear?

There was a great response to my Filtered Excel Training offer last week. If you missed it you can still sign up to get 53 Online Excel Training modules absolutely free.

Also, if you are looking for an Excel training solution across your business, take a look at Filtered's business offering and receive 20% discount as a Not Just Numbers reader.

On with today's post. Whether we have made a mistake or are tidying up a spreadsheet, there are many occasions where we wish to delete things that we, or someone else, has already done.

Excel offers a few ways to do this - and they're not all the same. Do you know what each of them do? If not, read on.

The most common method of deleting in Excel is to use the Delete key on your keyboard. This will delete the contents of the selected cell or cells, however it will leave any formatting intact. This makes it very useful for correcting incorrect data input, but not ideal when you are tidying up a spreadsheet and you don't want any old formats hanging around.

The next option (also called delete) is when you select Delete from the right-click menu or the Home ribbon. This option deletes the actual cell itself, giving you the option to move cells left or up, or to delete the entire row or column. This is quite different to the delete key as this is changing the structure of the spreadsheet.

Finally, there is the Clear option. This is available from the Home ribbon and offers the following options:

  • Clear All
  • Clear Formats
  • Clear Contents
  • Clear Comments
  • Clear Hyperlinks
These options are pretty self-explanatory and "do what it says on the tin", but it is worth commenting on the Clear All, as this is very useful when cleaning up a spreadsheet as it gets rid of any traces of what was there before.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".