Doing this introduces more room for error (based on the quality of the formula), and is a strain on calculation speed and memory. In prior versions of Excel, you would likely use a VLOOKUP function (or some other convoluted method) to add additional columns to bring in the associated Department or Employee Level. Especially if the output is being provided to someone without extensive systems knowledge, you’ll need to convert these codes into understandable values to provide the most clear and coherent information (see Figure 1-2 below for the related lookup ranges). Unless you’re very familiar with your company’s internal coding, the ‘Dept Code’ and the ‘Employee Type’ fields likely won’t provide you with too much information. This is a perfectly fine range of data, and you could certainly make a PivotTable based off of it, but there are a few issues. For example, you may have Travel Expenses for employees that you’ve extracted from your accounting system that looks something like this: In many instances, you won’t have a lot of control over the format of the initial data or the fields provided, especially when this is pulled into Excel from some other software. One of the limitations of PivotTables has been the fact that you could only use information from one data table, requiring any additional information to be added into the table in order to be included in the PivotTable.

Here’s the original Excel file to start with, and here’s the completed version.

You can download the example workbook to follow along (but it requires Microsoft 2013). With Excel 2013, Microsoft has introduced many new PivotTable features, including the new Excel Data Model which takes PivotTables to a whole new level!

Well, Microsoft has made a lot of strides over the years to introduce new functionality and options to make PivotTables easier and more intuitive to use. Yet there are still many Excel users who, for one reason or another, find themselves avoiding PivotTables.

PivotTables have certainly become one of the most powerful ways to quickly and easily transform data into information.

Get and transform excel 2013