PivotTable Data Preparation
We will start with a set of sales and profit by region, store, and quarter. Follow these rules to turn the data set into a PivotTable.
YAt an office, spreadsheets are formatted every which way. But they must be set up a specific way to use a PivotTable. Here are the Excel practices to avoid when setting up a PivotTable.
Blank column labels.
You will often see a blank column inserted between sets of data, usually for formatting purposes. PivotTables require that all columns be labelled. The easiest way to do this is to delete blank rows. If you must keep the column, then you’ll need to label it. I suggest using an “X” to remind you that this field is not to be used in the PivotTable.
Blank row after the labels.
This is another incompatible formatting touch. Data must connect to your column labels. Again, delete if possible; but fill in “X’s” if necessary.
Blank attributes.
Many spreadsheets will list an attribute once, then leave it blank for following rows. For example, in our spreadsheet, only the first sale for each quarter is marked. The following are blank, implying it is the same quarter. You will need to fill out these blank spaces in the PivotTable, or the data will not be recognized as a Q1 sale.
You can now turn this data source into a PivotTable. To do so, move your cursor anywhere inside the data. Then, from the Home Ribbon, go to Insert — PivotTable.
You will get a dialogue box that looks like this:
Excel will automatically assign the PivotTable’s range to be your data set. That’s great — for the average analyst. But you’re the savvy, forward-looking analyst. What if you need to add more data later? Then the range will not cover your new data. Similar to our vlookup trick, we will name our range to be the entire column. This way new data will always be within the range of the PivotTable.
To do this, put your cursor on the “Table/Range” dialogue box. Delete its contents. Then start with your mouse right on the top of the first column. Drop and drag to capture all the columns of your source data. Click OK.
Using the columns as your data range, you will never miss out on new data. Not picking up all the relevant data in your PivotTable is a classic analyst mistake. I am speaking from experience.
Leave a Reply