Creating a Pivot Table from Data on Multiple Sheets in an Excel Workbook
Sometimes in a finance team we are required to combine the information on multiple sheets in an Excel workbook and then analyse the information in a Pivot Table. The information is in the same format in each tab, and needs to be brought together. Normally what you’d have to do is copy and paste the information from one tab and put it in another. This takes time and can lead to manual errors.
We can instead use PowerQuery to bring the information together and have a ready made template, every time we have to perform the task.
To connect to the Excel file with the multiple sheets of data, the first thing to do is save down the dataset that you wish to connect to and then close the file. Closing the file is very important as PowerQuery cannot connect to an open file.
Once the file is closed, open a new Excel workbook. In the new workbook, select the Data tab and then on the Get Data and Transform section of the ribbon, select the Get Data dropdown.
From here, select the From File option and then the From Workbook button. Excel will then ask you to select the file that you wish to connect to.
Once you have selected your file, the Navigator menu pops up asking you to select the sheet in the file that you wish to connect to. To select to multiple tabs you can select the ‘Select Multiple Items’ checkbox and then select the sheets that you’d like to connect to. However if you have to perform this task frequently, and the dataset sometimes has different sheet titles, then a better option would be to right click on the Folder title (in the video this is called ‘Dataset’) and then select Transform Data. This way, all the sheets will be connected to and in the PowerQuery Editor screen you can choose which sheets to exclude specifically, but all other sheets will be connected to.
Once we’re in the PowerQuery Editor, you will see a table showing the names of each of the sheets we can connect to. You can filter these if you wish to. Once you have the sheets you wish to connect to then in the top right hand corner of the Data Column Header, there is a button with two arrows heading in opposite directions. Click on this to expand all the data tables. You can
From here, you’re then able to perform any transformations that you wish, but there are then at least three transformations which you would generally perform.
a) Delete unnecessary columns. These are generally the first column and then the last three columns (called ‘Name’, ‘Item’, ‘Kind’ and ‘Hidden’). These are columns of data that have not come from the Sheets but the first view when you enter PowerQuery.
b) Promote Headers. Using this technique, you can’t tell PowerQuery that the data you’re connecting to has Headers at the initial connection so you have to do it in the PowerQuery Editor by selecting ‘Use First Row as Headers’.
c) Remove Headers from all tabs. Even though you’ve already Promoted Headers, this only promotes the first row in the dataset. The Headers from all the other tabs will still be in the dataset somewhere so we need to remove these. Simply go to any column header and select the drop down arrow. You’ll then see all of the expected rows based on the fields of that column but you’ll also see the column title in the list. Deselect it.
Once all transformations have been performed, on the Home tab, select Close and Load To. This will load the data back into Excel, but will give you the option to load into a Pivot Table or a Pivot Table Chart, rather than a standard table.
.... and a little more!
Do you need to do this every day? – To really save time, if you need to do this compilation every day or week, then use the same file but change the data going into it! How can you do that?
Well Option 1 is simply replace the dataset wherever you’ve saved it with your new dataset. Make sure you keep the name of the file that you’re replacing exactly the same (capitals and spaces matter!). After all, PowerQuery stores the file location and the exact file name. If you change the file name, then PowerQuery won’t be able to find it to connect to.
Once you’ve saved it, then just click ‘Refresh’ on the Data tab. PowerQuery will then run the same process with the new dataset.
Option 2 is to point PowerQuery at a new dataset that is called something different. To do this, use the Data source settings button in the Home tab of the PowerQuery editor. This allows you to change the currently connected Dataset to a new dataset.
Fancy learning more about what PowerQuery is, how it works and how it can save your Finance Team time?
Take a look at our free 2 hour course…