Excel: None of the 46,273 Built-In Styles Do What My Manager Asks For

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


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.

  1. Copy an existing style.
    1. 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.
    2. At the bottom left of the dialog, choose Set as Default PivotTable Quick Style For This Document.
    3. In the Table Element list box, choose First Row Stripe. A new dropdown control appears, called Stripe Size. Open the dropdown and choose 2.

  2. Change the stripe size.
    1. Repeat step 4 with Second Row Stripe.
    2. 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.
    3. 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.

  3. 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.


For more resources for Microsoft Excel