Search Not Just Numbers

Tuesday, 26 March 2013

Excel Tip: Presenting Pivot Table data in a fixed layout using GETPIVOTDATA

It's been a while since I covered GETPIVOTDATA on the blog, so I thought it was worth a re-visit, but with the specific objective of demonstrating a structured approach to populating a fixed layout report, with data pulled from a pivot table.

NB: This post assumes that you already have, or know how to create, a pivot table containing your data. If not, you might find my video training course on the subject useful.

A very common example of this type of application is monthly management accounts: A pivot table can be great for doing the analysis and producing the numbers for the accounts, but these usually then need to be presented in a particular monthly accounts pack template.

This post will explain how to populate any fixed report with data pulled from a pivot table. This can be a great way of automatically populating any existing report that you might currently be populating manually.

One question that I am sure some readers will ask is "Why not use SUMIF and skip the pivot table altogether?"

This is a fair question and I sometimes take that approach, however one big advantage of the pivot table step is that you get to see ALL of the data summarised and can identify any data that is not appearing on the final report - SUMIFs on the other hand will only show what you look for, but you will not see any new categorisations that are not on the final layout (for example, a new nominal ledger code). Also, it is much easier to trace how a number is made up using the pivot table approach.

Let us take the pivot table report we produced in my cash book post:

NB: I have made one slight tweak to the one produced in that post, in that I have moved the year and month to be columns rather than report filter fields. This means that all years and months will appear as data is added, and our final report can pull out the month required.

The final template we use looks like this, and we normally key in the numbers from the pivot table (in reality, this might be a 30-page management accounts pack, but the principles are just the same as in our simple example):

Our objective is to automatically populate this in a structured way, from the pivot table.

We need to do a little preparation on the final report, by inserting 2 columns to the left of the report (these can be hidden, or kept outside of the print area, once we are finished).

In the first column, on the rows we need to populate, we enter exactly how that row is described in the pivot table. In the second column, we enter a 1 or a -1, depending whether we want to reverse the sign on that row or not.

So the report sheet now looks like this:

We will now build a formula that can be copied into every cell that we want to pull data into. We will create the formula in cell F5 and copy it to the others (NB: The totals are all just simple sums and are already in the template).

If you remember from my earlier posts on GETPIVOTDATA, the syntax of the function is as follows:

=GETPIVOTDATA("Name of data field to return",location of pivot table,"field1","item1","field2","item2")
Name of data field to return is a text field, being the name of the pivot table field as it appears on the pivot table.
location of pivot table is a reference to a cell anywhere within the pivot table, it is often best to use the top left cell of the main body of the pivot table, as this will remain in a constant position no matter how much the data changes.
field1, item1,field2,item2,etc. are pairs of field and item names from the row, column or page (filter) areas of the pivot table. You can use as many pairs as you like. The field name comes from the original column heading in the data, the item name is the entry in that column that creates a unique row, column or page in the pivot table.

SO to return the figure we want, being the commission sales figure for month 11 of the year 2012, the GETPIVOTDATA formula would be:

=GETPIVOTDATA("Value",'Income and Expenditure'!$A$4,"Expense/Income Name","Consultancy Sales","Year",2012,"Month",11)

i.e. return the total of the field Value, from the Pivot Table at 'Income and Expenditure'!$A$4 where Expense/Income Name is "Consultancy", Year is 2012 and Month is 11.

For this formula to be able to be used throughout we need to change the values to references.

We can swap "Consultancy Sales" for $A5 (notice we place a dollar sign before the A, so that if we copy the formula to a different column, it will still look in column A for the Expense/Income Name).

We can swap 2012 for $F$2 (here we have used the dollar signs to fix both the row and the column as the year will always be in cell F2).

Finally we can swap 11 for $F$3.

So our new formula is:

=GETPIVOTDATA("Value",'Income and Expenditure'!$A$4,"Expense/Income Name",$A5,"Year",$F$2,"Month",$F$3)

There are a couple of additional changes that we need to make to complete the formula before we copy it to the other cells.

This formula works great if there is a figure in the pivot table for the year, month and category in question, but will return an error otherwise, so we need to catch that and return a zero. I covered this in a post a couple of years ago (EXCEL TIP: Eliminating #DIV/0! and other errors automatically using ISERROR). In later versions of Excel, you can use IFERROR to replace the two functions IF and ISERROR, however I don't see that this makes the formula any simpler, yet stops it working in earlier versions, so we will stick to IF and ISERROR here. The new formula is now:

=IF(ISERROR(GETPIVOTDATA("Value",'Income and Expenditure'!$A$4,"Expense/Income Name",$A5,"Year",$F$2,"Month",$F$3)),0,GETPIVOTDATA("Value",'Income and Expenditure'!$A$4,"Expense/Income Name",$A5,"Year",$F$2,"Month",$F$3))

Finally, we need to reflect the column we added to allow us to reverse the sign. In our example, the pivot table has sales (credits) as a positive and expenses (debits) as a negative (if this had come from a traditional trial balance, they would have been the other way around). In our final report, we want to show both as positives and the totals will deduct expenses from sales, so we have entered 1 against the rows that we want to keep with the same sign and -1 against the expenses, where we want to reverse the sign.

This is simple to reflect in the formula as we just need to multiply the result by column B:

=IF(ISERROR(GETPIVOTDATA("Value",'Income and Expenditure'!$A$4,"Expense/Income Name",$A5,"Year",$F$2,"Month",$F$3)),0,GETPIVOTDATA("Value",'Income and Expenditure'!$A$4,"Expense/Income Name",$A5,"Year",$F$2,"Month",$F$3))*$B5

Note that we have again used the dollar sign to fix the column, but not the row.

We can now copy this formula into cells F6 and E10 to E13.

Our final report now looks like this:

The numbers will now all automatically update if the pivot table changes or if the year and/or month are changed at the top.

This approach works with far more complex reports than this and can be expanded to allow for multiple value fields in the pivot and multiple columns in the final report.

I use this approach regularly to automatically populate existing reports for clients. They often have very elaborate reports that take ages to complete manually. With this approach, I can insert a couple of columns and work out the formula, without changing the look of their report at all.

Go on, revisit all of those manual reports now. You'll be glad you did when next month end comes around!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Tuesday, 19 March 2013

Excel Tip: Using Goal Seek

Before we get into this week's post, I'd just like to say a big thank you for the magnificent response to my survey last week. I will reflect your feedback in the new training product I am currently developing, and which I expect to launch in the next few weeks.

This week's post is a short one on a useful little tool that Excel has tucked away. The Goal Seek tool is particularly useful when working with financial forecasts. Here we will show how to use it to find the break-even point.

Say you have a complex forecasting model with many interlinked variables, and you want to know what your break-even sales volume is. Goal Seek is designed for exactly this type of scenario.

What Goal Seek actually does is adjust one cell, to achieve a desired result in another cell that is ultimately dependent on the first cell. In the break-even example, we need to know what sales volume returns zero net profit.

We will use an incredibly simple example to illustrate the tool, but it really comes into its own the more complex the model is.

In the following spreadsheet, the white cells represent entered variables and the rest of the spreadsheet is calculated from these:

So, the formulae in cells B8 to B12 are as follows:

B8: =B3*B4
B9: =B3*B5
B10: =B8-B9
B11: =B6
B12: =B10-B11

We want to know what value in cell B3 will result in zero Net Profit in cell B12 (assuming the other variables stay the same). This will give us the break-even sales volume.

We select the Goal Seek tool by going to the Data ribbon, clicking What-If Analysis and selecting Goal Seek... We then populate it as follows:

Excel puts the dollar signs in when we click in the appropriate cells.

When we click OK, the Goal Seek tool will adjust cell B3 until cell B12 is equal to zero as follows:

Job done!

Obviously with our simple model we could have arrived at this ourselves quite easily, however the tool also works with complex models involving multiple sheets and many interlinked formulae.

Happy Goal-Seeking!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Monday, 11 March 2013

Excel Training - I need a minute of your time

Hopefully you are finding my Excel tips useful and they are helping you with your daily work. This week, however, I need a little something from you.

You may have seen that I do offer a small amount of chargeable training delivered from the blog - for example my Introduction to Pivot Tables video training course. This is typically training that goes deeper than  I could in a blog post (and we all have to make a living!).

I am looking to offer further items such as this, but I need a little information from you to help me to understand what you want. So if you could please spare me just one minute of your time to answer the simple questions below, it would be greatly appreciated.

Thanks in anticipation...

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

Tuesday, 5 March 2013

Excel Tip: INDEX and MATCH - an alternative to VLOOKUP (and HLOOKUP)

In my last post I described how to use both VLOOKUP and HLOOKUP to lookup and return data from lists or tables. Whenever I mention these functions in forums, etc. I usually get someone reminding me of how inefficient they are in terms of resource and how I should be using INDEX and MATCH.

I believe both approaches have their place - and use both where appropriate.

In a world of high speed processors and massive amounts of RAM (even on entry-level PCs), the resource issue tends to only become relevant for those handling significant volumes of data. Meanwhile it is usually easier for most users to understand and use VLOOKUP and HLOOKUP.

On the other hand, INDEX and MATCH are incredibly powerful, more flexible and are less resource intensive for high volumes of data.

Horses for courses!

So for those of you who do deal with large volumes of data, or who are looking for greater flexibility than VLOOKUP and HLOOKUP can provide, here is how to do lookups using INDEX and MATCH.

First of all, let's introduce the two functions:

INDEX is a function for returning a cell or range from within an array. At its simplest level this is done by referring to the cell by its row and column number (INDEX can do quite a bit more than this and also has another form which allows you to look at multiple ranges, however we only need to use its simple form here - I may do a post on some of its more advanced features at a later date). The simple form of INDEX is as follows:


Column can be omitted and, if so, it is assumed to be 1 - unless range is just a single column in which case Excel will assume that the omitted argument is the row.

So for example:

=INDEX(A1:D5,2,3) returns the value in C2

MATCH finds the position of a value in a single row or column range. Its syntax is:

=MATCH(lookup value,range,match type)

match type is optional and has the following three possible values:

1 (or omitted)  - finds the position of the largest value that is less than or equal to lookup value and requires the range to be in ascending order (this works the same way as using TRUE for the 4th argument in a VLOOKUP).

-1  - finds the position of the smallest value that is greater than or equal to lookup value and requires the range to be in descending order.

0  - finds the position of the first value that is exactly equal to lookup value (this works the same way as using FALSE for the 4th argument of VLOOKUP). In this case, the range can be in any order.

So if the range A1:A10, contains the values 5,6,3,8,12,4,9,34,23,54, then

=MATCH(4,A1:A10,0) returns 6, i.e the lookup value (4) appears 6th in the list.

Hopefully you are now starting to see how both these functions can combine to replicate a VLOOKUP (or HLOOKUP). We simply replace the row or column argument in the INDEX function with a MATCH function.

So for example in the following range:

=VLOOKUP("Mary",A2:B7,2,false) will return £35,213

We can do exactly the same with INDEX and MATCH.

Using INDEX, =INDEX(A2:B7,5,2) returns the same answer, but we had to know that Mary was on the 5th row of the range. Fortunately we know how to tell which row Mary is on using the MATCH function:

=MATCH("Mary",A2:A7,0) will return the position of Mary in the range (i.e. 5).

We can simply replace the row number in the INDEX function with the MATCH function and we have exactly the same functionality as the VLOOKUP:

=INDEX(A2:B7,MATCH("Mary",A2:A7,0),2) returns £35,213.

Although the result is the same, I am assured by those who know about these things that Excel handles the INDEX and MATCH version in a much more efficient way. Another advantage of this approach is that the lookup values don't have to be in the first row or column.

So, it is well worth having this approach in your arsenal, whether or not you choose to ditch VLOOKUP forever!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".