As always, I hope you are well. As per my earlier blog post I am going to be offering two segments here:
Excel Novice 5 min tip
Learn how to print your spreadsheet so that you don’t spend time cutting, pasting, gluing and stapling (awww, the horror!) . This handout gives you the basics:Printing_summary_tipsheet_02 But the main thing you need to check is Page Layout – Scale to Fit .and change the width and/or height to get the spreadsheet to fit on the desired number of pages…
Excel Adept – Using Power Query to make a pivot friendly budget file
One of the questions that crops up quite frequently is how to transpose or re-organise your data so as to construct a pivot table from it. Excel expects your data to be normalized i.e. one row per transaction so an invoice with 5 products on it will have 5 lines in transaction list or it should have if you need to filter or make a pivot table from the data. If you get a data dump, your data *will* be in this format. However if you are doing up an annual budget, it’s more common for the data to appear like this and to have been done up in an Excel file. Note how the dates are going across in columns G onwards….but if we want to construct a pivot table from this or use it in Powerpivot to compare…we will need to change the layout of the data. To be honest, that’s a pretty cumbersome thing to do just using Excel. You can use a combination of the Transpose formula or you could use John Walkenbach’s Pivot table technique. (the link that I usually use for this has gone dead alas, but this tutorial does give you the steps.
But in Power Query – this literally takes minutes. You can download the file here: Budget_unpivot_example.
As a way to cross check our work, let’s check the total for these three account numbers:
487674 = €38,618
592724= €117,255.00
697700=€20,125.00
If you think you will be using this procedure again next year – but with a different file – I am suggesting that you do it this way.
- Make sure you have Power Query installed. It’s a free add-in which you can download from here
- Click on Power Query tab – From File – From Excel
- Navigate to where this file is saved.
- Click on the sheet has the data you want (Budget)
- Click on Load.
- Click on the Query tab (right hand side)
- Click on Edit Query
- Delete the Total Annual Budget column (highlight – right click and Remove)
- Highlight all the columns with a date at the top (note that you can highlight the first one, press your Shift key, keep it pressed down and then click on the last month column to highlight all of them – which you will need to do)
- Click on Transform – Unpivot Columns
- All done – data unpivoted.
- All that’s left to do now is rename the Attribute column to Date (Right click – Rename) and Value to Monthly budget.
- To get it back into Excel, click on Close and Load, Close and Load. All done.
- Now, let’s check our first account number : 487674. Filter for this account number. Now highlight the numbers in the monthly budget column. Check your total in the status. There it is 38,618. Repeat the same process for the other two account numbers.
- You now have a budget file ready to be used in a pivot table. Let’s call it annual_budget_pivoted
Reusing it again next year.
One of the things I love about Power Query is this – once I’ve done this piece of work all I need to do to repeat it is to just change the source file. So open up annual_budget_pivoted.
- If the Power Query pane doesn’t appear on the right, click on Power Query – click on Show Pane.
- Then click on Launch Editor beside it to launch the Power Query editor
- In the Query settings on the left, identify Source
- Note that it has a cog to the right of it.
- Click on the cog.
- Then click on Browse to navigate to the file you want to apply the same procedure to.
- Then just click Close and Load. Work is all done.
At the moment I am only starting to scratch the surface of what Power Query can do…but if you need to do data cleansing…well worth while checking it out.