Academic Technology | EXCEL TABLES AND PIVOT TABLES
Excel Tables and Pivot Tables
The purpose of the workshop is to provide participants with an introduction to tables and pivot tables in Excel. Pivot tables are an extremely useful tool for performing a variety of very quick analyses on large data sets.
At the completion of the workshop, participants should be able to:
· Create a table and name the advantages of tables over ranges
· Create and modify pivot tables
· Customize pivot tables to specific needs
1 What is an Excel Table?
This is a Sub-section
A table in Excel is a special structure that used to be simply a range of data. In this range, each row is a single entity… a transaction, an employee, a customer, a sale, etc. Typically, each column in a table contains a piece of information about the data, and has a descriptive label. Tables normally have a header row, and also a total row, but each of these can be turned off.
To create a table from a range of data, simply click on the Insert tab, then Table.
Excel will then try to guess the range of your data, and by default, assume that your data has column headings.
You’ll know that Excel has successfully created a table because now your header row will look different and you’ll see “Table Tools” appear on top of the ribbon, as long as you have a cell selected in the table.
Here’s how your column headings look before your data becomes a table:
And here’s how they look once the table has been created:
One of the advantages of a table is that, when you scroll down toward the bottom of the table, you never lose sight of the column headings. As you scroll down, the standard column labels, A, B, C, etc., are replaced by the column names you specified in row 1.
Also, each column heading now has a drop-down next to the name. You now have the capability to easily filter or sort the data in each column.
In addition to sorting and filtering your data, you can check for duplicate rows and easily add a Totals row by selecting some of the options under Table Tools | Design.
Note that you can give your table almost any “look” by selecting one of the many choices under Table Styles.
If you need to add a new column to your table, just select a column to the right of the table and start entering data. The new column heading you type in will automatically become a “table drop-down” type of heading. Similarly, if you type in data beneath the last row of the table, it will automatically become part of the table, too. But note that this will not hold true if you have a “Totals” row. So if you need to add rows to the bottom of your table, you should deselect the ‘total row’ option until you’ve added the new row. Then you can select it again if you like.
If you should ever need to convert your table back to a data range, select Convert to Range in the Tools group, under Table Tools | Design.
2 Creating Pivot Tables
Pivot tables, once you get past the somewhat unusual name, are simply a method to quickly and easily summarize the data you have in a table. To create a pivot table, all you do is select a cell in your table or data range and click the Insert tab, then the PivotTable button.
Now the Create PivotTable dialog appears, and wants you to do two things: verify that the pivot table will be using the correct range, and decide if the pivot table should be placed in the same worksheet or a different worksheet. It’s usually not a bad idea to choose to place it in a different worksheet; that way you can delete the whole worksheet if you don’t like the way the pivot table looks, without the danger of possibly disturbing your source data.
Note in the figure above that we did not convert a data range into a table before starting to create a pivot table. You can tell this, because it shows a range above. If we’d converted the range into a table, it would say “Table1” (or Table2, etc). That’s because whenever you create a table, Excel automatically creates a new named range and gives it the name Tablen, where n is some number.
Now you’ve got a pivot table, and it should look like this:
The actual pivot table is on the left, and the pivot table “field list” is on the right. If it does not look like the above, first make sure you’ve selected a cell within the pivot table, so that you see “PivotTable Tools” above the Excel ribbon.
Make sure that all 3 buttons in the “Show/Hide” group on the far right of the ribbon are selected.
Now, make sure that the Options tab under PivotTable Tools is selected, and click on Options in the PivotTable group on the far left, and then Options again.
If you select the Display tab in the resulting PivotTable Options dialog, make sure it has these options checked:
One caution about pivot tables: although they’re amazingly useful and easy to create, you need to remember that pivot tables are a new, separate object in your worksheet. Unlike spreadsheet formulas, if your data should change, the pivot table will not automatically change to reflect the new data. You’ll need to remember to click the Refresh button in order for the pivot table to reflect the changes.
2 Using Pivot Tables to Analyze Data
Let’s get started: we’re using a spreadsheet of banking data that consists of information including date account was opened, type of account, what branch the account was opened at, and the amount of the account. Note that some of the information is descriptive (e.g., type of account) and some of the information is numeric (e.g. amount of account). This is the best kind of data to use in pivot tables; you’ll be summarizing the numeric data by using the descriptive categories.
Here’s an example… our banking data contains dates, but let’s say we’re not particularly interested in the dates the accounts were opened. What we do want to know is the total amount of each type of account for each branch. The key to creating the pivot table is the pivot table field list. This will determine exactly how your data is summarized.
Amount is our numeric data item, so we find it in the top of the field list and drag it to the box that says “Values”. Now drag AcctType to the box labeled “Row Labels”. This means that that the different account types will appear in the rows of the pivot table. Now drag Branch to the “Column Labels” box. Congratulations! You’ve just created a pivot table!
Notice a couple of things: we have drop-downs next to AcctType and Branch. If you click the drop-downs, you’ll see that you can filter these categories so the pivot table will show all values for the category, or whatever selection you want. Also note that you can format the data in a pivot table, just like any other range in a spreadsheet. You’ll want to do that, to make the pivot table look more polished. It’s a little different from the standard method for formatting cells; first select the range, then right-click and select Value Field Settings.
Now click on Number Format in the Value Field Settings dialog, and you’ll see the familiar choices for numeric format.
Note that by default, you have column totals and row totals. You can control that by selecting the Design tab under PivotTable Tools, then clicking on the Grand Totals button.
Also note that the Pivot Table Field List is only visible when you have a cell selected within the pivot table. If you click outside it, the Field List will disappear.
What does the “Drop Page Fields Here” heading mean on top of the pivot table? It simply means that it’s offering to summarize your data even further; for example, by the OpenedBy or Customer categories.
If we were to drag OpenedBy in the Pivot Table Field List to the Report Filter box, this is our result:
Notice we have a new drop-down that corresponds to the different values of the OpenedBy category. If we were to select “Teller”, the pivot table values would change to reflect only the amounts for accounts that were opened by tellers.
Once you’ve created a pivot table by dragging field names to the various boxes in the Field List, you can always change it easily by dragging them to another box, or back to the selection area. For example, say you don’t want the table to be summarized by the OpenedBy field, but you’d rather that be another field in the Row Labels area. Just drag it from Report Filter to Row Labels; there’s no problem in having multiple entries in the different areas. Here’s that table:
Here’s a few more examples:
Suppose you want to see the daily deposit totals for each bank branch. We’re not interested in the type of account, or who opened it, or whether it was a new or existing customer. To do this, we just drag the Date field to Row Labels, the Branch field to Column Labels, and the Amount field to Values. Here’s the result.
Here’s an interesting question: suppose you wondered about the number of accounts opened at each branch, by account type.
We can move AcctType to Column Labels, and Branch to Row Labels, and Amount to Values, but we have the same pivot table we had originally… it shows the sum of the accounts, not the number of accounts. But we can easily change that! Click on the drop-down next to Sum of Amount, and choose Value Field Settings.
Now choose Count and click OK in the Value Field Settings dialog. Now we have exactly what we wanted.
Suppose you wondered how the Central branch compares to the other two branches combined, in terms of total value of the different account types. Let’s put AcctType in Row Labels, the Branch field in Column Labels, and Amount in Values. But we want to compare Central to the combined value of North County and Westside; that means we need to group these two values, and that’s easy to do. Just select North County and Westside on the pivot table, right-click, and select Group.
Now our pivot table looks like this:
To combine the values of North County and Westside, just click on the “-“ next to Group1. That gives us this result.
I also changed the name “Group1” to “Other”.
When you’re ready to ungroup that information, just select the “Other” cell, right-click and choose Ungroup.
Do you want to make a pivot chart of this information? Let’s first click on the “-“ next to Central, then select any cell in the pivot table, then click on PivotChart in the Options tab.
Choose the first Column chart option in the Insert Chart dialog, and click OK.
There’s our pivot chart!
Suppose you wanted to know what types of bank accounts tellers open most often. We’re not interested in the Branch field, the Date field, or the Customer field. One way to approach this is to move OpenedBy to the Report Filters area, then move AcctType to Row Labels, then Amount to Values. I want to change the default Sum of Amount to Count, and select Teller for the OpenedBy field. Here’s the result.
But now I want to sort the values from highest to lowest. To do that, I click on the drop-down next to AcctType, select More Sort Options
Then select Descending by Count as shown.
Now, here’s the interesting part. Drag a second instance of Amount to the Values section. Change it to count, instead of sum as before. Then click on the drop-down next to Count of Amount and choose Value Field Settings.
Now click the “Show values as” tab and select “% of total”.
Your pivot table should now look like this:
But there’s one other neat thing we can do. Select the cells containing 39.9% through 4.03%, then click on the Conditional Formatting button in the Home tab.
Choose Data Bars, then choose whatever color you like.
Widen column C and look at the professional-looking result:
I changed the label in Columns B and C; remember, you can always modify column width and column headings with pivot tables.
These are just a few examples. You’re only limited by your imagination with this very useful tool in Excel.