Search Not Just Numbers

Tuesday 29 January 2013

Excel Tip: How to combine multiple sets of data for a pivot table - a practical solution

I have written many posts about the benefits of pivot tables and how to prepare data for use in them, but there is one scenario that I haven't covered which I come up against every now and again with clients. What if we have multiple sets of similar data that we wish to report across using a pivot table?

An example might be where we are comparing sales reports at a group level and are provided with a list of sales invoices from each of three departments or subsidiaries. To complicate matters further, the three departments use different invoicing systems and therefore the lists are in different formats.

Excel has a facility to consolidate ranges in a pivot table but this is for a very specific scenario and I, personally, have yet to find a practical use for this. It certainly does not help us here.

The good news though, is that I have developed a way of approaching this problem that works every time, and is pretty straight-forward.

To include them all in one pivot table, we will need them all in one list with one set of headings. And we want to do this in such a way that the work is already done for next month.

To start with we import, or paste, the three lists into their own individual tabs within our workbook.

We then set up a fourth tab and enter the column headings that we want to use for our consolidated table. These might be Date, Invoice Number, Salesperson, Customer, Amount. We also add an extra column for Department.

We want to allocate a section of the consolidated list to each of these departments.

Before we do that, we need to look at the typical size of each of these lists. Say that each is typically 2,000 rows - we might want to allocate 10,000 rows to each department to allow plenty of slack.

We now populate the first row of the consolidated sheet as follows.

In cell A2 (the first cell in the Date column), we enter a formula to pull the date from the first row of department 1. In cell B2 (the first cell in the Invoice Number column), we enter a formula to pull the Invoice Number from the first row of department 1, etc.

So, if department 1's data is held on a sheet called Department1 and the date is in column C and the Invoice Number is in column F, then on the consolidated sheet, cell A2 contains

=Department1!C2

and cell B2 contains

=Department1!F2

when this has been done for each of the columns we then enter the name of the department in the department column and copy the whole row down all the way to row 10,000.

Then in row 10,001 we do the same for the first row of department 2 (obviously referring to the correct columns for department 2 as these may be different. We then copy this down to row 20,000 and repeat again for department 3 on row 20,001.

We can then use these 30,000 rows as the source range the pivot table, allowing us to report across the departments, and by department thanks to the department column we added.

Next month we just have to import or paste the new data over the old data for each the three departments and the consolidated list will automatically update.

If you don't know how to create the pivot table itself, take a look at my earlier post, or you can purchase my Introduction to Pivot Tables video course here. If you're quick and do this by 31st January 2013, you can get it for half price.

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 22 January 2013

Why do I need pivot tables? - and a song!

Before we get to the song, I want to answer a question I am asked over and over again:

"Why do I need pivot tables?"

Regular readers will know what a big fan I am of pivot tables, and how they can transform how you use Excel. However the biggest obstacle that people seem to face is not that pivot tables are complex, but that they don't understand what they are for.

In short, Pivot tables allow you to analyse and summarise large amounts of data quickly, easily and incredibly flexibly (if that's  word!).

Quickly:

It can take seconds to transform tens of thousands of rows of data into a summary table, displaying the important information you need to see.

For example: from a list of sales invoices pulled from your accounting system, you can create a 12 month sales summary by customer with a column for each month. That should typically take less than a minute to create from scratch.

Easily:

Building pivot tables is all done with wizards and the layout is built visually using drag and drop - no complicated coding just a simple way to analyse your data.

Flexibly (if it's not a word, it should be):

In the sales summary example above, what if you (or your boss) now wants to see the same sales summary by salesperson and region, rather than by customer. This can be done in about 20 seconds. Just think if you'd built that summary without pivot tables - how long would it take to change it?

If I've convinced you that you need to know more about pivot tables, you can still take advantage of my January deal if you're quick. Just click here and you can have mastered pivot tables within the next hour.

Or alternatively, maybe these boys can convince you with a song:




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 15 January 2013

How to create an Excel Pivot Table

I am a huge fan of Pivot Tables as they can completely transform your ability to analyse and report on data in Excel and thought it was about time I did a post on them. Then I had a better idea...why don't I let you watch the first video in a training course that I developed with Emily Coltman a few years ago?

This video shows you step-by-step how to create your first Pivot Table. Then I had an even better idea...why not let you have the full course for half price as an exclusive offer for Not Just Numbers readers.

After you've watched the video, just click the link above to get the full course for just £9.75 (or $14.75 if you prefer) - but hurry, the offer is only available until the end of January. After that you can still buy it but at the full price.

Here's the video:




If you have any problems viewing the video, you can also view it here.

And don't forget to take advantage of your exclusive January offer.

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 8 January 2013

Excel Tip: Using CHOOSE - or do you need OFFSET, or maybe INDEX?

How are those New Year Resolutions coming along now we're all back to work?

Did you set your Excel ones? I'd still love to hear what they are.

Just a couple of quick messages  before we get into today's post.

First of all, if you're not a subscriber to the blog and therefore didn't get my exclusive Christmas video showing you how to use drop-down lists, I have decided to give you one more chance. I will send it out again at the weekend to any new subscribers that have signed up by the end of this week. So get yourself subscribed now in the box at the top right of the blog - if only to laugh at me in a Santa hat!

Secondly, I have made a few changes to the colour scheme of the blog - to freshen it up for the New Year. I would really appreciate any feedback on the changes, both positive and negative - as well as any other suggestions for changes to the layout of the blog.

Well, on with the post...

Tom, an accountant in Australia and regular Not Just Numbers reader, asked the following when I asked to hear what you wanted to learn from the blog:

"I'd like to learn about the CHOOSE function and using this for selecting data from another area of a worksheet (I am not sure if this would be used in conjunction with OFFSET?)"

Well Tom, the CHOOSE and OFFSET functions both perform a similar function - but in different circumstances.

I have covered the OFFSET function before, so I thought I would introduce the CHOOSE function and then explain how to determine which one you need. And then show you why INDEX might be better anyway!

The CHOOSE Function

The CHOOSE function is pretty simple and is used to select from a fixed list as follows:

=CHOOSE(position in list,item1,item 2, etc.)

so =CHOOSE(3, "A","B","C","D") returns C, being the third item in the list.

The list can contain up to 254 items.

You could use cell references as the list items, e.g.

=CHOOSE(3,A1,B1,C1,D1) will return the contents of cell C1.

Or you could even use ranges as long as you told Excel what you wanted to do with the range, e.g.

=SUM(CHOOSE(3,A1:A3,B1:B3,C1:C3,D1:D3)) returns the sum of cells C1 to C3.

The OFFSET function

I will not go into the full workings of the OFFSET function here as you can read about it in my earlier post.

You could though use the OFFSET function for either of the last two options:

=OFFSET(A1,0,2) returns the contents of C1

(notice an offset of 2 columns returns the third item here as the first item (A1) would be an offset of zero columns)

=SUM(OFFSET(A1,0,2,3)) will return the sum of the range C1 to C3, being offset by zero rows and two columns and having a height of 3.

OFFSET can be far more flexible and and easy to use, particularly as the list gets longer., however will not work if the list contains ranges of differing sizes (in which case you would need to use CHOOSE).

In most cases when the items on the list are held in the spreadsheet, OFFSET will be the better choice - except where you need to select from different size ranges as mentioned above.

The INDEX function

The INDEX function is an alternative to OFFSET which can be a little more complicated to understand, but  makes a better use of resources - which is particularly important if your spreadsheet is getting large and cumbersome.

INDEX has two forms, but for this purpose, we only need to worry about the more common one:

=INDEX(Array,Row Number,Column Number (optional))

The function then returns the value from the array at the intersection of row and column. If the array is only one row high or one column wide then you only need include either the row or column number. Also, a row or column number of zero will return the whole row or column as a range.

So...

=INDEX(A1:C1,3) will return the contents of C1

and...

=SUM(INDEX(A1:C3,0,3)) will return the sum of cells C1 to C3

I hope that helps you Tom, as well as everyone else!

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 1 January 2013

What are your Excel New Year's Resolutions?

I'd like to take this opportunity to wish all of my readers around the world, a very Happy New Year.

To those of you in Australia and New Zealand, whose New Year's Day will already be coming to an end, to those in North America, some of whom will still be out celebrating New Year's Eve, to those of us in the UK just waking up with hangovers, and to my readers in every other country of the world, I hope this brand new year brings you everything you hope for.

But coming back to Excel, what are your Excel-related plans for the new year?

What changes do you plan to make in how you use Excel, to get more out of it in 2013? I'd love to hear about them in the comments.

If you're not sure, I have a few suggestions to get you started. These have all served me well and would certainly improve your Excel spreadsheets, if you're not already doing them. So, here goes...


For my part, I have resolved to do the following in 2013:

  • Have a proper dig around in Excel 2013 to highlight the best bits to you;
  • Likewise for PowerPivot, one of the 2010 features I still haven't spent enough time on;
  • Develop a series of video training courses for those of you who want to learn in more depth than a blog post allows.
Thanks for all of your contributions in 2012 and I look forward to working with many more of you in 2013.

Don't forget to leave your resolutions in the comments!

Happy New Year.


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".