Microsoft Excel - Maintaining Formatting when Refreshing PivotTables

PivotTables provide a great way to analyze large amounts of data and pull out the summary information that you need.

Once you have the PivotTable displaying the values you need, you can then format the table to make the data presentable.

When you update the data on which the PivotTable is based, your formatting work may go away after you refresh your PivotTable. 

To prevent loss of format when refreshing follow these steps:

1  Make sure that your PivotTable displays the values you want.

2  Format the PivotTable to your liking.

3  From the PivotTable toolbar, select Table Options from the

PivotTable menu. The PivotTable Options dialog box opens.

4 Make sure the Preserve Formatting check box is selected.

5 Click OK.

When you now refresh the PivotTable, your formatting should remain on rows and columns previously applied in the PivotTable.

Note: If the refresh results in new rows being added to the PivotTable, then you will still need to format those, unless you are using an AutoFormat.

Excel Pivot Tables Lesson 3 Charts

Excel Pivot Tables Lesson 2

Excel Pivot Tables Lesson 1

Excel Pivot Tables- Flattening a Cross tab (Normalising data)

Data for a human being

We usually work with and better understand data which is presented two-dimensionally in a matrix or cross-tabulation. In other words, the classic spreadsheet with headings up the top, descriptions down the side and data in the middle.

Data for a machine

But computers don't work like that, they need data in one-dimensional normalised tables. When you need source data for an Excel Pivot Table or for any sort of database table you have to "unpivot" a cross-tabulation into a simple list.

Converting one data presentation from one to the other is a horrible task involving a significant labour of moving and copying data. But not if you use Excel's old Pivot Table Wizard method.

Firstly, you have to get the shortcut trigger for the Wizard as it is not available on the ribbon and then you create a Pivot Table on Multiple Consolidation Ranges and from that you can easily generate your normalised data.

Getting the shortcut onto the Quick Access Toolbar 

More Commands

You only have to do this once, after that you can use it as many times as you like. To change Excel's Quick Access Toolbar you can either click File tab, Options and Quick Access Toolbar or (far easier, I think) click at the end of the Quick Access Toolbar (top left of the Excel window) and choose More Commands.

Click PivotTable and PivotChart Wizard from the Commands Not in the Ribbon category. Finally, click the Add button to add it to the list on the right and click OK. Job done.

PivotTable and PivotChart Wizard, Commands Not in the Ribbon

Creating the Pivot Table

Multiple consolidation ranges

Click on any cell in your cross-tab data and then click the PivotTable and PivotChart Wizard shortcut that you've just created.

In Step 1 of 3 of the Wizard click the Multiple consolidation ranges option under Where is the data that you want to analyze? and the PivotTable option under What kind of report do you want to create?

Then click Next.

I will create the page fields

In Step 2 of 3 of the Wizard click the I will create the page fields option under How many page fields do you want?

Then click Next.

Select your range and click Add

Step 2b of 3 of the Wizard, select the range containing your cross tab data and then click the Add button to complete the All ranges list box.

There's no need to go to Step 3, click the Finish button and Excel inserts a new worksheet and displays your dataset as a Pivot Table.

Normalising the Data

Double-click the Grand Total cell

It looks rather unexciting, all you've done is recreate the original data but as a Pivot Table with a few totals. Now comes the exciting bit, double-click the Grand Total cell.

We all know what happens when you double-click a Pivot Table cell, it's the shortcut for Show Details and quick as a flash all your data is deconstructed and returned as a table onto a new worksheet. Normalised data in a simple list, how easy was that?

Normalised data

If you've ever had to do this sort of thing the long way round you will now want to kick yourself and weep.

If you don't want your data in an Excel table format, click the Table Tools tabs and look for the None format under the Light category in the Table Styles gallery and then the Convert to Range control in the Tools group. I can never see these because I am too busy weeping. Yes, I have frequently done this the long way round.

Related Posts

Excel-Dynamic Chart Data Ranges

Excel-Tick marks and Check boxes in cells

Excel-Make Pivot Tables refresh automatically

Excel-Calculations without formulas

Excel-Sorting by last name

Excel-Switching columns to rows

Excel-Double click tricks

Excel-Converting USA dates to UK dates

Excel Top ten shortcut keys

Excel 2010 shortcut keys

Excel-AutoSum Revisited

Excel for Mac-Deleting a range and other shortcuts

Excel-Calculating age from date of birth

Maintaining Formatting when Refreshing PivotTables

Maintaining Formatting when Refreshing PivotTables

PivotTables provide a great way to analyse large amounts of data and pull out the summarisations that you need. Once you have the PivotTable displaying the values you need, you can then format the table to make the data presentable—for a while.

Note: when you update the data on which the PivotTable is based, after you refresh your PivotTable, your formatting work may go away.

To prevent loss of format when refreshing follow these steps:

1. Make sure that your PivotTable displays the values you want.
2. Format the PivotTable to your liking.
3. From the PivotTable toolbar, select Table Options from the PivotTable menu. The PivotTable Options dialog box opens. (See Figure 1.)

Figure 1. The PivotTable Options dialog box.

1. Make sure the Preserve Formatting check box is selected.
2. Click OK.

When you now refresh the PivotTable, your formatting should remain on rows and columns previously applied in the PivotTable.

Note: If the refresh results in new rows being added to the PivotTable, then you will still need to format those, unless you are using an AutoFormat.

Excel Slicers

Excel Slicers

Excel Slicers are a fun, friendly and powerful way to filter  the data displayed for Pivot Tables, Pivot Charts, Power Pivot Tables and (for Excel 2013) normal Excel Tables.

These visual filters are an alternative to the field drop down lists and one Slicer can be connected to several  different Pivot Tables or Charts to instantly apply a common filter. 

Inserting Slicers

To insert a Slicer select a cell in the Table or PivotTable that you want the Slicer to control and then click the Insert tab of the ribbon, the Slicer control is in the Filters group. In Excel 2013 you can also right-click on the Field in the PivotTable Field List and choose Add as Slicer from the shortcut menu.

Inserting a Slicer

Connecting Slicers to Multiple Pivot Tables and Charts

Once you’ve inserted your Slicer you can then choose which Pivot Tables or Charts you want it to control. Right-click the Slicer and choose Report Connections from the short cut menu. This opens the dialog box where you can tick the boxes for the Pivot Tables you want your Slicer to control.

Multiple Connections

A Slicer can only control Pivot Tables which share the same Pivot Cache. Typically Pivot Tables which reference the same data source share a Pivot Cache, but this is not always the case.

If the Pivot Tables that you want to connect to don’t appear in the list then it is because they have separate Pivot Caches.

Filtering with Slicers

Slicers are easy to use and allow us to filter one or more items.

• Click one item to apply the filter for that area. 
• Click and drag through items to select more than one or click the first item, hold down the SHIFT key and click on the last item in your range. 
• Hold down the CTRL while clicking to select multiple items.

The Slicer displays the selected items in a different colour to give a visual indication of the applied filter. To remove all filters, click the red X at the top right corner of the Slicer.

Multiple filters

Slicer Settings

Click the outer edge of the Slicer to display the Slicer Tools, Options tab on the ribbon. Here we can access all the Slicer Settings.

Slicer Settings

1. Change the Slicer name
2. Turn the Slicer header on or off, or change the caption.
3. Sorting settings.
4. Choose how the Slicer should handle items with no data.

Slicer Styles

In the Slicer Styles group we can choose the colour and style, the colour options will be based on the Theme that you have selected for the workbook in the Page Layout tab of the ribbon.

Slicer Styles

Buttons and Size

Use the Buttons and Size groups to set the number of columns, button height and width and the overall size of the slicer. You can also size the Slicer by grabbing and dragging the selection handles on its outer edge. 

Sizing Slicers

If the overall height of your Slicer is too small to show all of the filter values then a scroll bar is displayed.

Slicer scroll bar

Formatting Slicers

Slicer Styles

You can choose a design from the Slicer Styles gallery or create your own Slicer style. Click on the down arrow to expand the gallery and click New Slicer Style.

This opens the dialog where you can format each of the 10 Slicer elements exactly as you want. You can click the check box to Set as default slicer style for this document to use the style over and over again.

Copy and Modify Slicers

Duplicate the Slicer style

You may prefer to copy a Slicer that has the colours you want to use and then just modify the fonts, borders and fill colour.

To copy a style right-click the style in the gallery and click Duplicate. This opens the Modify Slicer Style dialog.

Multiple Slicers

After duplicating your Slicer style you can apply it to your Slicers. If you have multiple Slicers you can select them by holding down the CTRL key as you click each one. Then click on your new style in the Slicer Style gallery.

Related Posts

Excel-Make Pivot Tables refresh automatically

Excel-Calculations without formulas

Excel-Sorting by last name

Excel-Switching columns to rows

Excel-CTRL key tricks

Excel-Double click tricks

Excel-Converting USA dates to UK dates

Excel Top ten shortcut keys

Excel 2010 shortcut keys

Excel-AutoSum Revisited

Excel for Mac-Deleting a range and other shortcuts

Excel-Calculating age from date of birth

Excel- Automatically change the Pivot Table source data range

When you change the source data of a Pivot Table you need to Refresh to reflect these changes but if you add any new rows to the end or columns to the sides of the list, refreshing the data does not detect these additions to the original source data range unless you converted your original range to a Table and then used the Table as the source data  for your Pivot Table.

If you used the original range as your data source then you will have to update the definition of the Data Source by clicking the Change Data Source control on the Options tab of PivotTable Tools. And you will have to repeat this process every time you add any new rows to your list.

Changing the Data Source range automatically

We saw in an earlier article, Make Pivot Tables refresh automatically how to refresh a Pivot Table automatically using the Worksheet_Activate event.

You can change the Worksheet_Activate macro so that in addition to refreshing the Pivot Table it also updates the definition of the Data Source. Thus the Pivot Table responds to any additions or deletions made to the original data range. Copy and paste the section below into your Worksheet_Activate macro:

---

Private Sub Worksheet_Activate()
    Dim PTRange As Range

    'Reset the data source.
    Set PTRange = Sheets("Sheet1").Range("A1").CurrentRegion

    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        PTRange, _
        Version:=xlPivotTableVersion10)
 
    'Refresh PT.
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

End Sub

---

You need to be a wee bit careful with the macro above and check the following:

• The example assumes that your Pivot Table source data range is on "Sheet1" of the workbook and is a range that starts with cell reference "A1". Should this not be the case then you must change the following line as required:
"Set PTRange = Sheets("Sheet1").Range("A1").CurrentRegion"

• The example assumes that your Pivot Table is "PivotTable2". To find out the name of a specific Pivot Table, right-click one of its cells and choose Pivot Table Options from the short cut menu.

Visual confirmation

If you want to visually confirm that your Pivot Table has been refreshed you can place an unobtrusive message down the bottom of the Excel window on the Status Bar. Add the following line to your Worksheet_Activate macro and clear the message away when you activate a different sheet using the Worksheet_Deactivate event.

---

Private Sub Worksheet_Activate()
 
    'The updating & refreshing macro goes here…...see above
 
    'Show message.
    Application.StatusBar = "Pivot Tables updated at " & Time

End Sub

---

Private Sub Worksheet_Deactivate()
 
    'Clear message.
    Application.StatusBar = False

End Sub

---

Excel's Status Bar

Excel's Status Bar is visible at the bottom of the Excel window and your messages are always shown on the left hand side. If you don't want the time stamp shown in your message then leave out the "& Time" part of the message.

Related Posts

Excel-Calculations without formulas

Excel-Sorting by last name

Excel-Switching columns to rows

Excel-CTRL key tricks

Excel-Double click tricks

Excel-Converting USA dates to UK dates

Excel Top ten shortcut keys

Excel 2010 shortcut keys

Excel-AutoSum Revisited

Excel for Mac-Deleting a range and other shortcuts

Excel-Calculating age from date of birth

Training Courses

If you want to find out more about Excel Pivot Tables or Macros then you might like to arrange an Excel training course for yourself or with some of your colleagues. It's really easy to book one of our courses and they're great value for money. See our website for full details.

Excel- Make Pivot Tables Refresh Automatically

This post has now moved to our new site 

Related Posts

Excel-Calculations without formulas

Excel-Sorting by last name

Excel-Switching columns to rows

Excel-CTRL key tricks

Excel-Double click tricks

Excel-Converting USA dates to UK dates

Excel Top ten shortcut keys

Excel 2010 shortcut keys

Excel-AutoSum Revisited

Excel for Mac-Deleting a range and other shortcuts

Excel-Calculating age from date of birth

Training Courses

If you want to find out more about Excel Pivot Tables or Macros then you might like to arrange an Excel training course for yourself or with some of your colleagues. It's really easy to book one of our courses and they're great value for money. See our website for full details.