Is there an undiscovered magical world in your Excel spreadsheet?

I love a good children's story about magical worlds where amazing things happen. Judging by the popularity of series like Harry Potter, which I enjoyed with my children, it seems a lot of other people do too.

When I was growing up I loved C.S. Lewis's Narnia books, starting with "The lion, the witch and the wardrobe" - more recently adapted for the "Chronicles of Narnia" film series.

What all these stories seem to have in common is the premise that children discover a magical world that appears to be hidden in plain sight. They go on all kinds of adventures and discover a lot about their own strengths and skills on the way.

Okay - so I may be over-exaggerating if I imply that using a spreadsheet can be as fantastic as fighting dragons, learning that you're the heir to the throne of a magical kingdom or using your superpowers.

But, as a I teased in last week's blog about my hot tub and gazebo experience , we can stop relying upon Excel cell formulae to solve every problem. Instead, there are features, hidden in plain sight, which offer a gateway to a whole new spreadsheet experience.

What I'm referring to are the features available on the Data Ribbon - specifically the "Get and Transform Data" and the "Queries and Connections" functions.

The eponymous wardrobe in the C.S. Lewis story was a doorway to the world of Narnia. It was sitting in a spare room and was discovered by one of the children, Lucy. At first glance it just looked like a regular piece of furniture, but she was bold enough to step far into it when she realised there was more to explore.

I believe that the 'Data' and 'Query' functions are similar - they look like ordinary menu options. But a new spreadsheet world awaits...

You're going to have to wait for later blog to see how we fully exploit these features., For now, let's me give you enough evidence just convince you why you should step into this world, whether or not you've already discovered these menu options.

Let's go through the wardrobe together - here are five reasons why I believe there's a magical world in Excel just waiting for you to explore.

I'm sure you've worked with a table of data in a spreadsheet. Very often you add formulae to a new column. My earlier blog showed you how we can help Excel to automatically add formulae when we add a new line at the bottom of the list. However, it's quite easy to accidentally erase a cell formula on a single cell, have inconsistent formulae i a column or find that blank cell somewhere in our data confuses Excel's ability to automatically add a formula to our new data.

When we use the Query functions to add columns our data, these inconsistencies do not occur

Changing my children's story metaphor for a moment, in the Harry Potter series, Harry possessed a magical map (the "Marauder's Map") which showed where everyone was at Hogwarts School.

Similarly, Excel has a 'Data Model' into which users can load data tables and define how they relate to each other.

By using this, you can have a simple map of your data, rather than relying upon debugging a large number of cell formulae containing VLOOKUP, SUMIF and similar functions.

So often, the data we want to work with has issues such as blanks, blank rows or rows with subtotals on, all of which we want to ignore.

By using a query, you can filter out any unwanted rows.

Also, one of the limitations of the VLOOKUP function is that if it doesn't find a match, it will return a "#N/A" value, which messes up any totals which include that cell. Using a query will not magically make such a data link work, but it will just return a blank which will be counted as a zero in any calculations.

So say goodbye to #N/A frustration.

This doesn't initially look like an advantage (until you see point 5 below!), but this strange new world has a language all of it's own. The DAX query language is different to the formula syntax that you know and use all the time.

But you shouldn't be scared by this.

For many years I worked with Swedish colleagues, and at one point was Financial Controller for the Nordic region. I didn't speak Swedish, but it was easy to make myself understood.

Firstly, most of my Colleagues spoke English, and were willing to help me learn rudimentary Swedish

Secondly, once I began to learn some Swedish out of politeness, I discovered that many of the word roots were similar to either northern dialect English or the German I had learned at School.

(As an aside - did you know that in the North East of England where I live, a child is referred to as a "bairn". The Swedish for child is "barn")

And so it is with the DAX language used in Excel queries.

Firstly, the menu options contain many click options which allows you to enter DAX functions by talking in your own language. At least in basic terms, you don't need to write DAX commands directly. It's like Excel talks English.

Secondly, when it does get a bit more complex, it's quite similar to the cell formula syntax. Therefore what you've already learned is useful. And there are very good online guides, so it's a bit like you have a good phrasebook available.

You can make yourself understood in this land as you learn to be as good as a native speaker.

I could allude to all kind of fantasy metaphors here... so I will do!

The DAX language from my fourth point is like the spoken incantations from Harry Potter. When you have the magical ability (which you do from the menu) they give you the ability to make amazing things happen with simple words.

Or, from the world of Narnia - the children were quite ordinary but what they were taught was that character, bravery and honesty - doing things they right way were the key to success. The world of Excel queries is a bit like that. Taking the time to develop good data and query habits will make you a good ruler of your spreadsheets.

To add another theme - the Marvel Cinematic Universe. Some of the characters have innate superpowers. But others, like Ironman, use technology to physically enhance their abilities. The Data and Query functions are like the Ironman suit - giving you super-strength and speed in response to your commands.

Beyond these simple metaphors, there are powerful abilities that you can acquire with learning and practice .



We haven't got into Star Wars until now, but let's just say that if you keep reading these blogs I hope that learn an apprenticeship that will one day turn you into a master... without having to turn to evil and the dark side.

Here are some examples:

• You can create time travel in a formula, and calculate the prior year value without counting back 12 columns. Or a large range of moving average measures. Or a Year-to-date total that changes the range of data is uses each month without you having to amend a formula to encompass a new range of columns.
• You can calculate the percentage gross margin on any combination of customers/products and rearrange the analysis 'on the fly' in a pivot table.
• You can create reporting hierarches based upon your chart of accounts that means you can report meaningful Profit and Loss Accounts, Balance Sheets and even Cashflow Statements from your raw accounting data.
• You can calculate actual to budget variances that apply to any criteria of data you specify.