In almost exactly three weeks’ time I’ll be presenting a lecture in Chartered Accountants Hall entitled: “The Spreadsheet is Dead”. The lecture will, in part, consider how Excel has gradually been evolving from spreadsheet to spreadsheet/database hybrid. Excel 2013 included a Data Model. This was first introduced in Excel 2010 as part of the optional Power Pivot add-in. The Excel Data Model allows Excel to establish links between separate tables of data, including tables from different sources, without the need to use any lookup or reference formulae.
In this example, we will use Excel 2013 to combine data from three tables and then extract totals from the data model without using a PivotTable (well, maybe just a little one…). We’ll present the resulting information using Slicers to add interactivity.
If all the tables that you want to use come from the same data source, then in Excel 2013, the Data ribbon tab, Get External Data, From Access command allow you to choose to import multiple tables:
When selecting multiple tables, Excel will automatically add them to the data model, linking them as they are linked in the Access database.
If your data tables are imported separately from one or more sources, then you can choose whether to add them to the data model as they are imported:
If you choose not to add them to the data model as they rare imported, or if they are just tables of data entered into Excel, then you can add them to the data model by setting up the links between them. The Data ribbon tab, Data Tools group, Relationships command allows you to edit existing relationships and create new ones. Creating relationships between tables automatically adds them to the data model. Here we have three tables of data on separate sheets in Excel. We have given each table a suitable name (we have given the worksheets the same name as the name given to the tables that they contain). We can go to the Relationships option and use New to create relationships between the fields that link our tables. Here we link Invoices to Products:
Here we link Products to Categories:
Having established how our products are linked we could now create a PivotTable or a Power View data visualisation based on fields from any of our tables. For example, the total of invoiced sales by product category.
If we have installed the Power Pivot add-in (the availability of this add-in depends on the edition of Excel that you have installed: it is available with the Professional Plus edition and it looks as though it is also now available as part of a standalone Excel edition) then you could use the Power Pivot ribbon tab to add a Calculated Field summing the ExtendedPrice field in the Invoices table for example.
Without Power Pivot, you could go to the Insert ribbon tab, Tables group and choose PivotTable. If you choose ‘Use an external data source’ and ‘Choose connections’ then you can select the Tables tab and use your workbook data model as the source:
We can then select the ExtendedPrice field from the Invoices table to create our small PivotTable:
Whether using a Power Pivot Calculated Field or a small PivotTable, we have now created a ‘measure’ in our Data Model that adds up all our invoice totals.
Obviously, we could go on and produce as many PivotTables and PivotCharts based on our data as we wanted, but just to demonstrate what else you can do with the Excel 2013 data model, we have used some particular Excel functions and a Slicer and a Timeline Slicer to build a PivotTable-less dashboard:
The Slicer is added by going to the Insert ribbon tab, Filters group, Slicer and selecting the Data Model tab. This will display all the fields in all the tables in the Data Model and we can select the CategoryName from the ProductCategories table:
The Timeline Slicer is added in a similar way.
Although the formulae may look a little daunting, they aren’t that difficult to enter. The formulae use different cube functions. If you start typing in =CU Excel will display the available cube functions:
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of ExtendedPrice]")
Here we have shown the characters we have actually typed in in red and bold, the rest of the formula is selected from Excel’s AutoComplete list. This formula will return the total of the ExtendedPrice column for our Data Model.
We can add references to particular, field values, including referencing cell contents. Here are some examples of the formulae used in our dashboard:
The formulas in D3 and D4 include references to the Timeline_OrderDate slicer and the Slicer_CategoryName slicer. These can again be entered by typing in a comma and then the beginning of the name before selecting the correct Slicer reference from the AutoComplete list.
This blog is brought to you by the Excel Community
where you can find additional blogs, extended articles and webinar
recordings on a variety of Excel related topics. In addition to live
training events, Excel Community members have access to a full suite of online training modules
from Excel with Business. There is also an online forum where you can
ask questions and share ideas with other community members.