PART 3: WRANGLING DATA	345

Figure 886 Double-click A4 and all the data is inserted.

Gotcha: While I’ve used this trick a dozen times, it is always unnerving to delete your data. You should make a backup copy of the entire workbook before deleting the data. You never know if the pivot table cache would become corrupt.

DRAG FIELDS TO THE PIVOT TABLE

	Problem: I want to go back to dragging fields around the pivot table like I did back in Excel 97-2003.
	Strategy: There is a way to go back to the old style. Follow these steps:
	1.	Right-click any cell in the pivot table. Choose PivotTable Options.
			3
	2.	Go to the Display tab within the PivotTable Options dialog.
	3.	Choose Classic PivotTable Layout.

Figure 887 Like Coke Classic, with less bite.

You can now drag fields to the report.

346	POWER EXCEL WITH MR EXCEL

Figure 888 Drag fields from the field list to the report.

When you think about it, the four blue boxes in the old pivot table layout are really drop zones. In the cur- rent Excel, Microsoft moved the drop zones to the bottom of the pivot table field list.

Gotcha: this setting is not universal. You will have to change the setting for each pivot table.

Gotcha: if you save your file as an .xls file, you will get this style every time, but as you will read in the next topic, this is not a good strategy.

CREATE A REPORT THAT SHOWS COUNT, MIN, MAX, AVERAGE, ETC.

Problem: Most of the Pivot Table examples shown thus far are for summing revenue. What if I need to find out the average sale by customer or the smallest sale?

Strategy: Pivot tables offer eleven calculation functions in the Value Field Settings dialog.

To use Field Settings, select one numeric cell in the pivot table to make that field the active field. In the Analyze tab of the ribbon, click Field Settings.

Figure 889 Choose a revenue cell, then Field Settings.

You can now choose from the 11 functions.

PART 3: WRANGLING DATA	347

Figure 890 Choose from the 11 functions.

When you choose Average, the field heading will become “Average of Revenue”. You can edit the custom name in the Value Field Settings dialog to “Average Revenue” or “Average Sale” or any other heading that 3 you would like. Note, however, that you cannot reuse a name already in the pivot table. So, for example, Revenue would not be allowed, but Revenue_ or “Revenue ” or “ Revenue” would be allowed. Those last two include a trailing space and a leading space.

Gotcha: There is no built-in way to create a median for a pivot table. I’ve heard this question a few times.

BETTER CALCULATIONS WITH SHOW VALUES AS

Problem: Excel offers eleven functions on the Summarize Value By tab of the Value Field Settings dialog. Those are not the good ones. The good ones are on the Show Values As tab. Ninety percent of the time that I change the calculation, I am using one of the relatively hidden Show Values As calculations.

Strategy: Drag the Revenue field multiple times to the pivot table Values area. Select a cell in one column, use Field Settings and change the Show Values As.

Figure 891 Show values as a variety of calculations.

Gotcha: The calculations require one, two, or zero arguments. An example of each follows.

348	POWER EXCEL WITH MR EXCEL

The numbers in C4:C10 use the % of Column Total setting. You simply choose this setting. You don’t have to specify any additional information.

Figure 892 Show values as a percentage of the column total.

Rank and Running Total are examples where Excel will ask you to identify the base field. Most often, this will be the row field.

% Difference From is a calculation that requires a Base Field and a Base Item. The calculation in F4:F9 expresses revenue as a percentage of Manufacturing revenue.

Figure 893 Enter a base field and a base item.

The (previous) entry in Figure 893 is great for reports with dates. This report show the sales as a percent- age change from the previous day.

Figure 894 Specify a base field and a base item.

Additional Details: You can combine the 11 functions on Summarize Values By and the 15 settings un- der Show Values As. The figure below is showing the average sale for each day and then the % change from the previous day of the average sale.