11.4 PivotTables/Charts

Insert Timeline

The Insert Timeline feature for a PivotTable allows you to quickly filter information based on a date field. You must have a field formatted as a date to use the Insert Timeline tool. Using the first four tabs in the Chapter 11 data file workbook ("timeline1," "timeline2," "timeline3," and "timeline4"), you can create a PivotTable using the skills learned in Advanced Excel Formulas, Functions, and Techniques. The PivotTable summarizes the enrollment English Index Score (EN Index Score) for various majors at a university over several years (Figure 11.73). You can use this data to see if there have been changes in the score over time. The Insert Timeline feature on the PivotTable Analyze tab allows you to add a date filter to the table.

Figure 11.73 A date filter can be applied to a dataset with the Insert Timeline tool. (Used with permission from Microsoft)

Click in the PivotTable to bring up the PivotTable Analyze tab. Choose Insert Timeline from the Filter command group. Click the checkbox next to the field title that contains the date. In this example, the field is called DATE. Click OK. The timeline scroll bar will appear (Figure 11.74). In this case, you only have annual data, so you can change the unit to years instead of quarters, months, or days.

Figure 11.74 The timeline scroll bar offers a quick way to filter data using time such as years. (Used with permission from Microsoft)

Currently, the PivotTable shows all years available in the dataset. You can deselect those years that you do not want and keep only those that you are interested in examining. Let’s select 2017 and 2018, which filters the table using those dates only. You can then use this filtered data for more in-depth analysis and calculations. To remove the timeline, right-click on the timeline scroll bar and choose Remove Timeline. If you had already filtered the data with the timeline, the data in the PivotTable will remain filtered to those dates. If you want the table to return to its original form with all of the dates included, be sure to click on the Remove Filter button prior to removing the timeline.

Insert Slicer

A slicer is a visual, interactive way to filter data in a PivotTable. When a PivotTable has slicers available, you can filter the data by clicking on buttons in the slicer as opposed to using the filter options in the column headings of a PivotTable. They are particularly useful when there are multiple users of a PivotTable who need to view the information that is relevant to their needs. You can add slicers through the PivotTable Analyze tab in the Filter command group.

Let’s look again at the dataset of enrollment ACT scores. A PivotTable can be created to include the years, major, average high school grade point average (HS GPA), EN Index Score, and average ACT Math. You can insert a slicer to delve deeper into the data. Assume you want to look at the HS GPA for biology and chemistry majors compared with that of accounting and business majors. You can use a slicer to make this process easier. First, click in the PivotTable to access the PivotTable Analyze tab. Then, choose Insert Slicer. A selection window will appear (Figure 11.75). Click the checkbox next to Major 1. Click OK.

Figure 11.75 The Insert Slicer tool allows you to choose which variable to filter. (Used with permission from Microsoft)

The slicer will appear on the spreadsheet with selection buttons for each of the majors in the list. You can resize and format the slicer to meet your needs. You can also choose various slicer styles under the Slicer tab that becomes accessible when you click inside the slicer (Figure 11.76).

Figure 11.76 The Slicer tab appears when you have inserted a slicer into a PivotTable. (Used with permission from Microsoft)

When you insert the slicer, the default is for all majors to be selected, as indicated by the blue highlighted buttons (Figure 11.77). You can click on the Multi-Select option to remove all the majors and start building your table for analysis. For this example, you want to look at the differences in HS GPA for Biology, Chemistry, Accounting, and Business Administration. You will choose only those majors to be displayed in your PivotTable (Figure 11.78).

Figure 11.77 By inserting a slicer, a user can quickly filter data by simply clicking on the major of interest in this example. (Used with permission from Microsoft)

Figure 11.78 To remove a filter, click on the Remove Filter button. (Used with permission from Microsoft)

Choosing only these majors filters the table for an easier and more direct comparison. Remember, you could simply use the filter option in the column heading, but slicers are a convenient and interactive way to filter data. To remove the filter, click on the Remove Filter button in the top right of the slicer window. To remove the slicer entirely, right-click on the slicer and choose Remove Slicer. Be sure to remove the filters first if you want the data to return to its original form. Simply removing the slicer window will not also remove the filters you set in place.

Slicers are also available when you have data in a Data Table. With the data formatted as a Data Table, click inside the Data Table to bring up the Table Design tab. The Insert Slicer option is in the Tools command group (Figure 11.79). The process to insert, format, use, and remove slicers is the same as it is for PivotTables.

Figure 11.79 Slicers can be used with Data Tables as well through the Table Design tab. (Used with permission from Microsoft)

Calculated Field

The Calculated Field tool allows you to add columns of calculations to your PivotTable. The Calculated Field tool is accessed through the Fields, Items, & Sets menu on the PivotTable Analyze tab (Figure 11.80).

Figure 11.80 Calculated fields are added to a PivotTable through the PivotTable Analyze tab. (Used with permission from Microsoft)

These calculated field columns cannot be used with slicers, but they allow you to analyze the data in other ways. Using the dataset of enrollments and ACT scores, assume you want to get the average ACT scores across the various content areas (English, Math, etc.). You will need to get an average of the four ACT scores as a calculated field column. Click inside the PivotTable to access the PivotTable Analyze tab; choose Fields, Items, & Sets; and click on Calculated Field. An input window will appear (Figure 11.81).

Figure 11.81 Select the variable for the calculated field in the Insert Calculated Field window. (Used with permission from Microsoft)

You will need to name the calculated field—use “ACT average” for this example—and then you can input the formula. Remember that all formulas in Excel need to begin with an equals sign. You can use functions such as AVERAGE, SUM, and IF, as appropriate for your needs. For this example, you want the average of the English, Math, Reading, and Science fields. To accomplish this, type in the Formula input box “=AVERAGE(” to begin. Then, choose “eng” from the Fields list and select Insert Field. Then, type a comma and insert the next field, “math.” At the end of the formula, make sure to close your parentheses and then check for extra spaces in the formula. Then, click Add to create the calculated field. Finally, click OK (Figure 11.82a). You will now see the calculated field column in your PivotTable (Figure 11.82b).

Figure 11.82 Using the (a) Calculated Field feature will add (b) a new column to the PivotTable. (Used with permission from Microsoft)

The default setting for the column headings in PivotTables is “Sum of” even though you are calculating an average. When you created the calculated field, you named the field ACT average, but that is not the column heading Excel will use. Excel will automatically add on “Sum of” to the column heading. You can change that by simply clicking on the column heading and deleting the words “Sum of.” Notice the data in the first cell in the ACT average column for each major is much larger than the other numbers in the major. This cell represents the sum of all the averages for the subgroup. For accounting, 131.49 is the sum of the average ACT numbers for the years 2015 to 2020. Excel automatically adds the subtotal to the first field in the Rows list. This number is probably not as valuable as the averages per year under each major. You can delete this value by choosing the field settings for Major 1 and under Subtotals, select None (Figure 11.83).

Figure 11.83 The default setting is “Automatic.” This adds the subtotal for the first field in Rows in the PivotTable. (Used with permission from Microsoft)

Adding this calculated field for the accounting majors, you can see that the average ACT was at its lowest in 2019 and at its highest in 2018. Calculated fields can be used with sales and expense information to determine your profit, profit margin, and other similar calculations.

There are other options in the Insert Calculated Field input window that may also be useful. You can select the calculated field from the list and modify it to make changes. You can also select that calculated field and delete it entirely. Click OK after making your changes or deleting the calculated field. If you delete the field, the column will be removed from the PivotTable.

List Formulas

If there are multiple users of a data file, it can be important to ensure everyone is aware of the calculations behind the columns. This will assist the users in understanding the dataset and the information contained within. The List Formulas option in the Fields, Items, & Sets menu creates a separate tab that lists all the formulas (calculated fields) used in a PivotTable (Figure 11.84). This option is accessed when you are in the PivotTable in the PivotTable Analyze tab. Then, go to Field, Items, & Sets to get to List Formulas. It is good practice to include this with all data files that have multiple calculated fields. The list is not dynamic. If you create additional fields after the list is created, you will need to delete the original list and create a new one.

Figure 11.84 If there are multiple users for a spreadsheet, use the List Formulas tool to make sure all users are aware of the calculated fields and solve order in a PivotTable. (Used with permission from Microsoft)