PART 3: WRANGLING DATA |
311 |
|
|
Result: The pivot table shows Yes or No values.
Figure 797 Instead of numbers, show Yes/No.
Additional Details: At this point, the heading of “Sum of Revenue” is not really appropriate. Select any Yes/No cell to make the active field box in the Analyze tab of the ribbon say Sum of Revenue. You can type a new name in that box.
Figure 798 Change from “Sum of Revenue”.
|
PIVOT TABLE FORMAT DEFAULTS |
3 |
|
|
|
|
|
Problem: I hate Compact layout in Pivot Tables. I want all future pivot tables to start in Tabular.
Strategy: A November 2016 update to Office 365 introduces the ability to set defaults for all future pivot tables. Go to File, Options, Advanced, Data and choose Edit Default Layout.
Figure 799 After years of lobbying and 1000 votes at Excel.UserVoice.com, this feature was added.
Gotcha: If you have an older version of Excel, you can buy the Pivot Power Premium add-in from Debra Dalgleish at Contextures.com. This add-in addresses many annoyances with pivot tables. Read more at http://www.mrx.cl/pppdebra.
FORMAT PIVOT TABLES WITH THE GALLERY
Problem: Due to the dynamic nature of pivot tables, it is fairly hard to format them. If I start applying formats to individual cells, the formats are lost after I rearrange the pivot table. Help!
Strategy: You can solve this problem by using the gallery on the Design tab of the ribbon. This is an amaz- ing improvement over Excel 2003’s AutoFormat.
The gallery offers seven color styles (grayscale and six theme colors). There are four styles each in three shadings (light, medium, and dark). There is one style with no formatting. You have (6 x 4 x 3) 72 color styles, 12 grayscale styles, and 1 plain style for a total of 85 styles.
You can modify the color and grayscale styles by using the four check boxes Row Headers, Column Head- ers, Banded Rows, and Banded Columns. Since each checkbox offers 2 choices, 2 x 2 x 2 x 2 = 16 variations on each of the 84 styles. 84 x 16 + 1 yields 1345 styles, (1152 color, 192 grey, 1 plain)
312 |
POWER EXCEL WITH MR EXCEL |
|
|
By choosing a new theme, you can change the 6 accent colors to any of 40 built-in sets of colors. This leads to 46,080 color styles (1152 x 40). Adding the grayscale and plain style gives you 46,273 styles.
In case one of the built-in 46.273 different styles doesn’t work for you, then you can create your own custom formatting. See the "None of the 46,273 Built-In Styles Do What My Manager Asks For" on page 313.
In comparison, Excel 2003 offered 22 AutoFormats, and all of them were horrible. Many of them changed the layout of your table. Microsoft did an incredible job with the formatting options in Excel 2007. Here’s how you use them:
1. Select a cell in the pivot table. Select the Design tab on the ribbon.
2. Make selections in the PivotTable Style Options group, changing Row Headers, Column Headers, Banded Rows, and/or Banded columns. (You should do this before opening the Styles gallery, as the thumbnails in the gallery will reflect these settings.)
Figure 800 Turn on banded rows.
3. Open the PivotTable Styles gallery. Thanks to Live Preview, you can hover over various thumbnails and see the effect of each on the table. Figure 801 shows Pivot Style Light 10. Figure 802 shows Pivot Style Dark 19.
Figure 801 One of the light styles.
Figure 802 One of the dark styles.
Additional Details: On the Page Layout tab of the ribbon, you can change to any of the different built-in color schemes. This will affect the colors used in the gallery.
PART 3: WRANGLING DATA |
313 |
|
|
Figure 803 Change theme colors and the pivot table colors will change.
NONE OF THE 46,273 BUILT-IN STYLES DO WHAT MY MANAGER ASKS FOR
Problem: My manager asks for a pivot table to be formatted with alternating stripes that are two rows high. None of the built-in styles do this.
Strategy: You can create this effect by duplicating an existing style and modifying it. Follow these steps: 1. Find a style that is close to your manager’s request. In the PivotTable Styles gallery, right-click the
style and choose Duplicate.
3
Figure 804 Copy an existing style.
2. In the Modify PivotTable Quick Style dialog, give the style a new name. Excel initially gives the style a name by adding a 2 after the old name. Rather than PivotStyleLight 10 2, use a name like TwoStripe.
3. At the bottom left of the dialog, choose Set as Default PivotTable Quick Style For This Document. 4. In the Table Element list box, choose First Row Stripe. A new dropdown control appears, called
Stripe Size. Open the dropdown and choose 2.
314 |
POWER EXCEL WITH MR EXCEL |
|
|
Figure 805 Change the stripe size.
5. Repeat step 4 with Second Row Stripe.
6. Click OK to finish modifying the style. You have now created a new style, but Excel has not applied the style to your pivot table.
7. Open the PivotTable Styles gallery and find the new style at the top of the list, in the Custom section.
Choose that style.
Results: A new style is available, with stripes that are two rows tall.
Figure 806 A new style is available.
Additional Details: If you want all future pivot tables to use this format, right-click the style thumbnail and choose Set as Default.
Additional Details: While working in the Modify PivotTable Quick Style dialog, you can click the Format button to change the font, border, and fill.
Gotcha: The custom style is saved in the workbook. It is not available in other workbooks. Here is a work- around. Start with a blank workbook. Add a small pivot table. Format the pivot table correctly. Mark the new style as the default. Delete the data and the pivot table so you have a completely blank workbook.
Save the workbook as Book.xltx as shown in "Control Settings for Every New Workbook and Worksheet" on page 25
SELECT PIVOT TABLE PARTS FOR FORMATTING
Problem: I want to manually format a pivot table. Can I select all the row subtotals? For example, select the region totals in rows 8, 12, and 16.
PART 3: WRANGLING DATA |
315 |
|
|
Figure 807 Select row subtotals.
Strategy: A clever mouse trick will allow you to select similar rows in a pivot table. Follow these steps: 1. Select one cell in the pivot table. On the Design tab, choose Report Layout, Show in Tabular Form. 2. Hover the mouse over cell A8. This is the Central region total. Slowly move the mouse toward the left edge of the cell. Eventually, the cell pointer changes to a black arrow that points to the right. When
this cell pointer appears, click the mouse. Excel will now select all the subtotal rows.
3
Figure 808 One click select all subtotal rows.
3. Using the formatting icons on the Home tab of the ribbon, assign a color to the subtotal rows.
Additional Details: Click in the left side of cell B5, and you will select all the ABC records throughout the pivot table. Below, different colors are applied to ABC, DEF, and XYZ using this method.
Figure 809 Format all cells for one product.