The last few Excel upgrades have enhanced the way it works with data imports and analysis. Simon Hurst shows how they can help you in the first of a series of updates to our catalogue of classic pivot table tutorials.
Changes in Excel 2010 and 2013 have the potential to transform the way we work with accounting data. The Excel 2010 PowerPivot addin and the inclusion of the PowerPivot ‘Data model’ within Excel 2013 itself should ensure that working with external data is easier, more automatic and, perhaps most importantly, more reliable.
In addition, the Tables feature introduced in Excel 2007 provides similar improvements when it’s necessary to manipulate the data in some way.
Nearly 6 years ago David Carter contributed a seminal article on working with accounting data in Excel entitled Create a P&L from monthly balances with a pivot table. The article was based on pre-2007 versions of Excel. To demonstrate how things have changed, we will repeat some of the steps the tutorial covered and explore how they can be improved using more recent Excel capabilities.
You can open the data files in Excel, as they have already been exported from the accounting application. Improvements in Excel’s ability to link to external data, together with increasing recognition by software suppliers that they need to make their data available to third-party packages, mean that we are more likely now to be able to link directly to the data using one of the options in the Get External Data group of the Excel Data ribbon tab or using the PowerPivot addin.
David’s call for more “analysis-ready” file options from accounting software vendors may have helped to bring about some of these improvements.
We will use the same files as those in the original tutorial but, as we will be linking to them rather than working with them directly, save each sheet of the original two files as separate CSV files.
Excel 2013 Data Model
Use the From Text option within the Get External Data group of the Data ribbon tab:
Locate each of the three CSV files in turn. For each one go through the 3 steps of the Text Import Wizard, using a separate sheet for each:
- Select the Original data type as Delimited. Confirm that My data has headers.
- Choose Comma as the Delimiter
- Select any empty columns by clicking in the column, then choose Do no import column (skip) for each one.
The next screen has a new option Add this data to the Data Model. Select this option and choose cell A1 as the destination.
In Excel 2007-2013 external data ranges are automatically set up as tables. If you click in any of your three tables you should see that a Table Tools, Design tab appears. When you select this tab you will see a range of tools for working with tables and, in the Properties group at the left-hand side, you will see the name of each table has been set to Table_Name of original file. You can overwrite this with a simpler name if required.
Excel 2010 PowerPivot
In Excel 2010, or 2007 for that matter, the Get External Data procedure would be the same, but only Excel 2013 includes the Add to Data Model Option. In Excel 2010 you could install the optional PowerPivot addin and use the PowerPivot window to link to the text files. We would then use a similar approach as outlined below to add the linking columns and create our relationships, but within the PowerPivot window.
From other sources
The From Other Sources option within the Get External Data group includes options to link to data in a wide range of different formats including SQL Server and also includes a Data Connection Wizard to help with unlisted data formats. This should allow you to connect to data from a wide range of different databases and applications.
Links vs Lookups
A key element of the Carter tutorial was to link the Trial Balance table with the other two tables. This was a two-stage process because the Trial Balance account code is set up as a multi-segment code where the first five digits represent the account and the last three the cost centre. We are assuming this structure is consistent for all existing and future account numbers. We need to split each code into these two different elements. In the original tutorial, this was achieved by using the Text to Columns feature – now available in the Data Tools group of the Data ribbon tab. Once new columns had been created using Text to Columns they were used to bring in the Account details from the Account Lookup table and the Cost Centre details from the Cost Centre Lookup table using two separate VLOOKUP() formulae.
One of the features of Excel Tables is their ability to copy any formulae in a column to any new rows added to the table automatically. This means that, for our external data range, we can add columns to manipulate the data and the formulae in those columns should automatically be added to any new rows when the data is refreshed next month or whenever.
Go to your Trial Balance table.
Type the heading ‘ACCTNO’ in cell G1. Your table should expand to include the new column.
Type the heading ‘CC’ in cell H1. Once again, your table should expand to include the new column.
In cell G2 start entering the formula =VALUE(LEFT(
Now click on cell A2 and then type ,5))
In Excel 2013 and 2010 you should end up with the formula:
Because you are working in a Table, Excel will use the name of the relevant column and the @ to represent ‘This row’. In Excel 2007 the formula is a little more cumbersome with [#ThisRow] being used instead of the simpler @. You will also find that your formula is copied to all the other rows in the table. We use the VALUE() function to convert the resulting text into a number, because the equivalent fields in both of our ‘Lookup’ tables are numbers and you can only link similar field types.
We repeat the procedure for the CC column but this time using the 3 characters from the right of our account number:
Our Trial balance table now has fields that contain values corresponding to those in our other two tables:
All that remains is to link our two lookup tables to our main Trial Balance table. In Excel 2013 we do this by going to the Data ribbon, Data Tools tab and clicking on Relationships. We click the New button to set up each of our relationships in turn, choosing the columns in Trial Balance and the equivalent column in each of our lookup tables:
We now have our tables linked.
Go to the Insert ribbon tab, Tables group and choose PivotTable. Choose the Use an external data source option and click on the Choose Connection button. Go to the Tables tab in the Existing Connections dialog and choose the option within This Workbook Data Model. We can use any of the fields from our Tables. Repeating the steps in the Carter tutorial:
From Table_Lookup drag ACGROUP into the ROW area, and underneath it, ACCTNAME.
From Table_cclookup drag CCNAME into the Filters area.
From Table_Trial_Balance drag PD1 into the DATA area, and underneath it, PD2, PD3, PD4: