Using Excel in the Age of Big Data

We are constantly being told that we are living in the Information Age.  The Industrial Revolution marked the beginning of the Industrial Age and similarly, the Digital Revolution started the Information Age.

Is a Power Pivot Training Course Right for Me?

Have you even been on a training course and found you were completely out of your depth?  Or have you been on a “advanced” course and discovered that half the participants haven’t a clue and the instructor spends most of the day on the basics?  We want to make sure that this doesn’t happen to you!

If you’re thinking about coming on a Power Pivot training course, then you need to make sure that you have the right skills already in order to get the maximum benefit from your training.

Here are some questions that will help you to determine if this course is right for you:  You should:

  1. Understand and have used Excel’s SUMIF function
  2. Have a working knowledge of filtering data in Excel (e.g. Auto or Advanced Filters)
  3. Know how to deal with multiple criteria (e.g. SUMIFS, SUMPRODUCT or DBASE functions)
  4. Be able to import data from third party databases and / or files (e.g. Access, SQL, MIS systems)
  5. Regularly use, adapt and modify PivotTables
  6. Have created calculated fields in PivotTables
  7. Created and / or modified an Excel Table (a structured reference table, not a data table)
  8. Have access to either Excel 2010 (all versions) or Excel 2013 Professional Plus

Give us a call if you’d like to talk through your options prior to booking on a course.  If you think you’ll need a bit more help, consider booking an in-house private training course for your company.  The consultant will come your offices, and we can go as quickly or slowly as suits your needs.

If you need to brush up on your skills prior to coming on the course, here are some online resources that will help.

Download the Power Pivot add-in here.  There are also some videos you can watch to get you started.

  • Chandoo has a paid online Power Pivot course as well as a lot of free online resources on Power Pivot & Excel
  • To brush up on Pivot Tables, here’s an article from SumProduct.
  • Here’s another one on structured reference tables
  • Take a look at the Free Stuff section.  We have links to free tutorials, and a more                                                                extensive list of other online  resources

There are some amazing resources online – so spend a little bit of time reviewing the content and making sure you’re up to speed with all of the techniques in Excel.  The same goes for any of our Excel-based training courses, not just Power Pivot.  You’ll get a lot more out of your training course!

Learn to Love your Excel Errors

As I was scrolling through the index for my book recently, I noticed the #REF error jump out at me.  Why is it that the #REF strikes such fear into a modeller’s heart?  Probably because out of all common Excel errors, it is the one which is the most difficult to fix.  It means that your formula is referring to a cell that doesn’t exist anymore!  If someone has saved a model with a #REF error in it, it’s not very easy to correct because the formula that it’s referencing has gone; so the formula needs to be completely rebuilt.

As annoying as they are, I actually like seeing Excel errors in my formulas believe it or not, as it means that something isn’t working, and therefore I can fix it.  As I always tell my training course attendees – the only thing worse than finding a mistake in your model is NOT finding a mistake in your model and your model being wrong!  Worse than that is your boss finding the mistake in your model.  There’s no faster way to lose credibility as a modeller than for others to find formula mistakes in your model, and there are a number of strategies to reduce error that you can employ reduce this possibility in your modelling.

If you’ve attended one of my courses, you’ll know I’m quite fond of the SUMIF function is it’s much more versatile and robust than other popular functions such as a VLOOKUP.  One of my issues with the SUMIF though is that it does not warn you if you use mismatching ranges.  In the example below, the result in cell C13 will not return an error; it will simply give a wrong answer without any warning!

Excel Errors

Source:  Fig 6.6 on page 96 of Using Excel for Business Analysis

The thorough modeller in this example will check the totals as they work, and then put an error check below in cell C17 to make sure that any incorrect entry in the future is quickly identified.

Even better than the SUMIF is its big brother, the SUMIFS function which can handle multiple criteria and  –  critically –will warn the user if the ranges are mismatching, unlike the SUMIF.  It’s one of SUMIF’s annoying little quirks that it allows mismatching ranges and exactly why SUMIF allows this, is something I’ll never understand.   It’s highly unlikely that we’d deliberately want mismatching ranges and far more likely that it’s been done inadvertently (although I have been known it use it for staggering periods).

So instead of cringing at a horrid #VALUE! error value, or supressing it with an IFERROR function, we should instead embrace, use and perhaps even love our Excel error values!  Figure out what’s wrong, and get to the bottom of it.  An Excel error value is far more helpful than the alternative which is to have deceivingly innocent number values which are in fact incorrect.

Get to Know your Excel Error Values

Error values such as #REF!, #NA! or #VALUE! are Excel’s irritating but necessary way of telling us that something’s not quite right with your formula or function.  Although they can be frustrating and time-consuming to rectify, dealing with them is an obligatory part of modelling.  Instead of being ignored, supressed or yelled at, Excel errors should be heeded, taken care of and according to a recent article, perhaps even celebrated!

Excel errors should not be confused with error checks, which are formulas created by the modeller that have been designed to alert the user as soon as the model is not calculating correctly.  Excel error values are errors displayed by Excel when the formula has not been built properly.   For example, using the multiplication symbol “x” instead of “*” e.g. =I36xJ36 is an incorrect formula which will return the value #NAME? because Excel does not recognise it.  Other common sources of error are brackets (parentheses) that do not match, or missing sections for functions.

If you get an Excel error message, click OK on the error message’s dialogue box. Then either edit the formula on the Formula Bar to correct the formula, or click the Cancel button to clear it.

Here are some common error values you might get, and how to fix them:


The railroad tracks; my favourite!  This is not really an error, and very easily to fix. The result is too long to fit in the cell, so just make the column wider.


You’re trying to divide by zero.  Check that the formula is dividing by is not empty, as this will translate to a zero value.


There’s a name in the formula Excel doesn’t recognise.  You may have named a range, and then spelt it incorrectly, or typed “VLOOKKUP” instead of “VLOOKUP”.

If you used a name you defined, check its spelling. You can avoid this error by selecting a name in the Name Box instead of typing it in.

If you typed in a function, check its spelling or verify that such a function exists.
If you are performing operations on text, make sure you have enclosed it in double quotation marks.


Your formula refers to a cell that no longer exists, due to a change in the worksheet.  This happens if you deleted cells referred to in the formula or pasted moved cells on cells referred to in the formula.

This is probably one of the more difficult errors to fix because you no longer have access to the cell that was deleted. You will need to re-enter the cell reference which is missing.


The formula is using a different type of reference to what the function was expecting. The most common cause of this is if a user tries to enter text into a cell instead of a number, or the formula is referring to a label instead of the numeric value.  For example: “TBA” or “Less than 10” instead of a numerical value.  Check to see that some fields are not referring to cells containing text.


This means that there is some data missing; the value the formula looking for is not available.  A common reason for receiving this error is if a LOOKUP cannot find the criteria it is searching for in the source data.   Make sure that the

function can find the data you’ve asked it to look for.

With the exception of the #### railroad tracks, all of these error values can be suppressed using the IFERROR function which will stop the error from showing.  So, for example, if the cell A2 contained a zero, the formula =IFERROR(A1/A2,0)  will return a zero instead of #DIV/0!.  Be careful, of course that you don’t suppress a real error which should be shown, and fixed!

Have you Considered PowerPivot?

Imagine analysing your monthly results in real time without an army of management accountants / consultants spending valuable time and money constructing delayed, unfathomable reports. Did we say “imagine”? You might be doing this already. How useful would it be for you and your colleagues to produce key analysis in seconds from automated reports talking to your existing management information systems?

First introduced as a free add-in in Excel 2010, and to be honest, slightly more difficult to find in Excel 2013, PowerPivot replaces and improves the SQL Server Analysis Services for Microsoft’s Business Intelligence (BI) suite. Put simply, PowerPivot is PivotTables on steroids. It extends the capabilities of the PivotTable data summarisation and cross-tabulation feature by introducing the ability to import data from multiple sources. It will allow you to do things you couldn’t do before.

As a self-service BI product PowerPivot is intended to allow users with no specialised BI or analytics training to develop data models and calculations, sharing them either directly or through SharePoint document libraries. But it doesn’t end there. For more sophisticated users, PowerPivot can:
•    create your own BI solutions without purchasing expensive software
•    manipulate large data sets quickly, even if it consists of millions of rows (Excel can’t do that!)
•    construct complex what-if reporting systems with data modelling and Data Analysis Expressions (DAX)

by Liam Bastick, FCA, FCMA, CGMA, Excel MVP and Director of SumProduct

Learn more about Power Pivot and Power Query

The Power of PowerPivot

PowerPivot: The Next Big thing in Business Intelligence?

What’s all the fuss about PowerPivot?  Well, I could go on and on about the wonders of Excel – it’s really the tool of choice for business users who need to very quickly analyse data, create reports and share them with other users. Wonderful as it is, however, Excel stops being quite so wonderful when your data is more than 1,048,576 records long, or if the data needs to be consolidated from multiple sources.