How to use Pivot Tables – Excel’s most powerful feature and also least known

This article explains the basics of Excel's pivot table, I have included VBA code for the most common actions.

 

1. What is a Pivot Table?

A pivot table allows you to summarize huge amounts of values amazingly fast in groups and sub-groups you specify. You can then analyze the data with ease, compare values by date or by group and see important trends. It is one of the best and most powerful Excel features and also one of the least known.

Pivot table charts is a great tool for visualizing your data.

Slicers allow you to quickly filter data, however, the report filter has the same functionality but perhaps not as elegant.

The following article shows you how to analyze pivot table data:

Recommended articles

Back to top

2. Prepare source data

Before you build your first pivot table make sure your data source table follows some simple rules.

1. Use unique header names for all your columns.
2. Make sure you have no blank cells.
3. Check your spelling. If you have one cell "West" and another "Westt" they will both show up in the pivot table. You can correct this later.
4. Convert your data source to an excel defined table (optional).

Back to top

3. Rearrange values

This picture below shows you a table with bad data structure, you can't use it in a pivot table.

The table below is much better, all values in this table are not shown for obvious reasons. A couple of things are missing though, can you see it? Unique table header names and the data table is not an excel defined table.

You don't have to use an excel defined table but it will make it a lot easier if you add more values later on to your table. An excel defined table is dynamic and it will save you time not needing to adjust the pivot table source range.

I have made a macro/udf that can help you rearrange your data, see these posts:

Recommended articles

Recommended articles

Back to top

4. Use an Excel Table as a data source

Why do you want to use an Excel Table as a data source to your pivot table? You don't have to but it will make your life easier whenever you want to add or delete records to your data set.

The Excel table adjusts automatically to new data and this saves you time since you don't need to update the data source cell reference each time.

Here is how you convert a generic data set to an excel defined table:

1. Select a cell in your data table.
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on "Table" button.

You can also use these shortcut keys: CTRL + T

This dialog box shows up.

Excel finds the entire data table automatically =$A$1:$C$48, do make sure this is correct.

My table does not have table headers so I don't select the "My table has headers" check box.

Press with left mouse button on the OK button.

Excel has now converted your data table to an excel defined table. It has also inserted new column headers, see picture above. I don't want to use these table header names as they are not descriptive of each column.

This table is much better, later on these table header names will make it much easier for us when working with the pivot table.

Learn how to change data source with a drop-down list:

Recommended articles

Learn more about excel tables:

Recommended articles

Back to top

5. How to build a pivot table

1. Select a cell in your data table.
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on the "Pivot table" button.

This dialog box appears.

It uses the excel defined table name in the first field, very good. It also allows you to choose where you want your new pivot table. I am going to place the pivot table on a new sheet. Press with left mouse button on the OK button.

Excel creates a blank pivot table for us on a new worksheet, see picture above.

5.1 How to insert a Pivot Table programmatically

This is what the macro recorder returns while inserting a pivot table.

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Source data!R1C1:R365C7", Version:=6).CreatePivotTable TableDestination:= _
"Sheet17!R3C1", TableName:="PivotTable7", DefaultVersion:=6
Sheets("Sheet17").Select
Cells(3, 1).Select

The following article shows you how to refresh a pivot table automatically:

Recommended articles

Back to top

6. How to configure a Pivot table

The pivot table on the worksheet is blank and it tells us "To build a report, choose fields from the PivotTable Field List".

You can find our fields in the blue box named 1, see picture below. The fields are Region, Date and Amount the same as your header names in your data source table, now you understand why it is important to name your data source headers.

The way this works is that you can press and hold with left mouse button on one of the fields and then drag it to an area. The areas are Filters, Columns, Rows and Values and I have drawn a blue box around them with the name 2, see above picture.

Drag Region to Filters, Date to Rows and Amount to Values, see picture below. Excel is trying to help me out here, it automatically grouped my dates into months se column "Row Labels". Don't worry, I will show you later on how to group and ungroup dates.

6.1 How to move a column header to the Filters area programmatically - Pivot Table

This is what the macro recorder returns when I drag Region to Filters area.

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Region")
.Orientation = xlPageField
.Position = 1
End With

6.2 How to move a column header to the Columns area programmatically - Pivot Table

Here is the output when I drag Region to Columns area.

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Region")
.Orientation = xlColumnField
.Position = 1
End With

6.3 How to move a column header to the Rows area programmatically - Pivot Table

This happens while recording Date to Rows area.

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Date")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").PivotFields("Date").AutoGroup

6.4 How to move a column header to the Values area programmatically - Pivot Table

Finally Amount to Values area.

ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Amount"), "Sum of Amount", xlSum

Learn how to use hyperlinks in a pivot table:

Recommended articles

Back to top

6.5 Report Filters

The report filter allows you to select a subset of your data. In this case I want to work with values in region "West". Simply press with left mouse button on the arrow and select a Region. Press with left mouse button on OK. See animated picture below.

If you are interested in the vba code for this action here it is.

ActiveSheet.PivotTables("PivotTable3").PivotFields("Region").ClearAllFilters
ActiveSheet.PivotTables("PivotTable3").PivotFields("Region").CurrentPage = _
"West"

Back to top

6.6 Column Labels

Press with left mouse button on and drag Region to Columns area.

Unique values from column Region in your source data table shows up horizontally on the pivot table (cell range C4:E4, pic above).

Back to top

6.7 Row Labels

The following picture shows Region in Rows area and Date in Filters area.

Back to top

6.8 Values

The last area is Values. You are not limited to numbers (Amount) here, you can drag Dates or Region here also. Notice that you can't sum text values but you can count them.

Learn how to link a combo box with a pivot table:

Recommended articles

Back to top

7. Pivot table features

Here is a list of the most used functionalities.

7.1 Summarize and analyze

The pivot table is phenomenal at processing huge amounts of data very quickly. Since calculations are done in almost no time you can easily drill down in every detail very quickly.

I have shown you earlier in this article that if you drag a field to Values area, the pivot table sums your values into groups depending on what specific fields you have in the Columns and Rows area.

It also allows you to see trends in your numbers, follow this example. Drag "Amount" once again to Values area, you should now have two "Amount" there.

Left press with left mouse button on the down pointing arrow next to the second "Amount" and then press with left mouse button on "Value Field Settings...". Press with mouse on tab "Show Values As"

Select Date in Base field: and (previous) in Base item:, see picture above. Press with left mouse button on OK

There is now a new column next to the sums. It shows the % difference compared to the previous values. The number in cell B8 compared to the number in cell B7 is -4.86% less. (8410/8840) -1 = -4.86%

Learn to build a pivot table calendar:

Recommended articles

Back to top

7.2 Count

The following picture shows Region in both Values area and Rows area. Date in Columns area grouped by month.

Back to top

7.3 Unique distinct list

You can use the a pivot table to extract a unique distinct list from a column in a large list. The picture shows countries, simply drag Country to Rows area.

Back to top

7.4 Extract unique distinct records

Here is a table with many duplicate records.

1. Select a cell in the table above.
2. Go to tab "Insert" and press with left mouse button on "Pivot table" button
3. Place the pivot table somewhere on your worksheet/workbook.
4. Drag "Name", "City" and "State" to "Row Labels" field
5. Also drag "Name" to "Values" field
   
   
6. Values are not on the same row which is confusing. Go to tab "Design" on the ribbon, press with left mouse button on "Report Layout" button and then "Show in tabular form"
   
   
7. To show all values, go to tab "Design" and press with left mouse button on "Report Layout". Then press with left mouse button on "Repeat All Item Labels"
   
8. The last thing to do is to hide "Subtotals", press with left mouse button on "Do Not Show Subtotals" button.
   

Here is a picture of the final table, it shows you unique distinct records. In other words, duplicate records are removed from the source data table.

It is possible to extract unique distinct records using a formula, read this post:

Recommended articles

Back to top

7.5 Count unique distinct values

Excel 2013 and later versions allows you to count unique distinct values. The following picture shows you data table, the scenario is this:

How many unique distinct products did Jennifer sell in region "South" and in January 2011?

I have highlighted the values below that match above criteria.

For this to work you need to enable a check box before creating a new pivot table.

Press with left mouse button on OK button. To be able to filter month January and not individual dates only, drag "Date" to Rows area.

There are now two "Date" fields in Rows area, the other is "Date (Month)". Drag "Date (Month)" from Rows area to Filters area and drag "Date" back to top area. It now looks like this:

Drag "Region" and "Sales person" to Filters area. Drag "Product" to Rows and Values area.

Press with mouse on "Count of Products" then press with left mouse button on "Value Field Settings..."

Select "Distinct Count" and press with left mouse button on "OK" button

Now let's filter the pivot table, press with left mouse button on filter "arrow" next to All and select "January", "South" and "Jennifer". Press with left mouse button on OK button.

Here is the final pivot table:

There are two unique distinct products shown next to "Grand Total".

7.5.1 Count unique distinct records (rows) in a Pivot Table

Excel versions earlier than 2013 need a different approach, the method demonstrated here uses formulas to count unique distinct records.

The table I am working with is in cell range B2:D7. I have then added another column (E) to count unique distinct rows.

Formula in cell E3:

Copy cell E3 and paste it down to cells below as far as needed.

7.5.2 Create pivot table

1. Select cell range B2:D7
2. Press with left mouse button on "Insert" tab on the ribbon
3. Press with left mouse button on "Pivot table" button
4. Choose where you want the pivot table to be placed.
5. Press with left mouse button on OK.

7.5.3 Setup pivot table

1. Press with left mouse button on and drag Name, Address and City to row labels.
2. Press with left mouse button on and drag Count to values.

There are three unique distinct rows (Grand Total)

7.5.4 Count duplicate records (rows) in a Pivot Table

The table I am working with in this example is in cell range B2:D12. I have then added another column (E) to count duplicate rows.

Formula in cell E3:

Copy cell E3 and paste down to E12.

7.5.5 Create pivot table

1. Select cell range B2:D12
2. Press with left mouse button on "Insert" tab on the ribbon
3. Press with left mouse button on "Pivot table" button
4. Choose where you want the pivot table to be placed.
5. Press with left mouse button on OK.

7.5.6 Setup pivot table

1. Press with left mouse button on and drag Name, Address and City to row labels.
2. Press with left mouse button on and drag Count to values.

There are six duplicate rows (Grand Total)

Back to top

7.5.5 Count unique distinct values in an Excel Pivot Table

This section describes how to count unique distinct values in a Pivot Table using a prior to Excel version 2013.

Answer:

Update: It is now possible to count unique distinct values in a Pivot Table. You need Excel 2013 or a later version.

I don´t think it is possible to count unique values in a pivot table unless you add another column to your table or use VBA:  Improve your Excel Pivot Table to count unique values or items

Formula in D3:

Copy cell D3 (Ctrl + c). Paste (Ctrl+ v) on cell range D4:D17.

Setup pivot table

1. Press with left mouse button on and drag Array formula to Row Labels
2. Press with left mouse button on and drag Count to Values

How the formula in cell D3 works

Step 1 - Construct the COUNTIF function

COUNTIF(range,criteria) counts the number of cells within a range that meet the given condition.

Select cell D3.

Type =COUNTIF(

Select cell range C3:C17

Press F4. You have now created absolute cell references to cell range C3:C17.
=COUNTIF($C$3:$C$17

Now create a relativ cell reference to cell C3.
=COUNTIF($C$3:$C$17, C3)

=COUNTIF($C$3:$C$17, C3)

becomes

=COUNTIF({"AA"; "AA"; "AA"; "BB"; "BB"; "BB"; "BB"; "BB"; "BB"; "BB"; "BB"; "CC"; "CC"; "CC"; "CC"}, "AA")

and returns 3. There are three AA in the array.

Step 2 - Divide 1 with the number of counted values

=1/COUNTIF($C$3:$C$17, C3)

becomes

=1/3

and returns

0.333333333333333 in cell D3.

Count values in a pivot table using an Excel defined table as a data source

7.6 Sort data

It is possible to sort almost anything with data in a pivot table. This picture shows a pivot table and I want to sort column East from smallest to largest. Press with right mouse button on on a cell in a column you want to sort. Press with mouse on "Sort" and then sort Smallest to Largest.

Recommended articles

Back to top

7.7 Group data

A pivot table allows you to group dates. You can group dates (and time) by seconds, minutes, hours, days, months, quarters and years.

Press with right mouse button on on a column or row you want to group, then press with left mouse button on group and this dialog box appears.

Below Group is Ungroup and I don't think I have to explain that.

Recommended articles

Back to top

7.8 Multiple levels

You can rearrange the pivot table so it has multiple levels on Rows area or Columns area or both. Drag more than one field to an area to create multiple levels.

The picture shows you data grouped by quarter and then by region. You can also drag fields in an area to sort them, the order is important.

The Date and Region field switched places.

Back to top

7.9 See data behind a pivot table cell

To see the data behind a pivot table cell just double press with left mouse button on a cell that interests you and excel creates a new sheet with the corresponding data shown.

Back to top

7.10 Row and column grand totals

If you don't need the grand totals, turn them off by going to tab "Design", press with left mouse button on "Grand Totals" button and pick a setting you want. The choices are:

• Off for Rows and Columns
• On for Rows and Columns
• On for Rows only
• On for Columns only

Back to top

8. Insert a pivot chart

A pivot chart helps you visualize Pivot Table data.

1. Make sure you have any cell selected on your Pivot Table
2. Go to tab "Analyze" on the ribbon.
3. Press with left mouse button on "Pivot chart" button.

From here you can pick a variety of charts, the preview helps you in your decision.

The chart will change if you apply a filter or sort pivot table.

Back to top

9. Slicers

Excel 2010 and later excel versions lets you insert slicers, they are no different than the Report Filter except that they look different and take up more space on your worksheet.

Go to tab "Insert" on the ribbon and press with left mouse button on the "Slicers" button.

From here you can pick a field, I will select Region and then press OK button. The slicer appears on your worksheet, you may need a new location for it.

Back to top

10. Refresh a pivot table

If you add, remove or edit values in your source data table you must update the pivot table to reflect the changes made, every time. This is easy to forget, here is how to do it.

Press with right mouse button on on a pivot table cell.

Press with left mouse button on "Refresh".

You can automate this with a macro.

Private Sub Worksheet_Activate()
Sheets("Pivot table").PivotTables("PivotTable1").RefreshTable
End Sub

Put it in your worksheet code module, see this post for more details.

Back to top

11. Copy a pivot table without

If you want to share your pivot table but not the source data, follow these steps.

1. Select the pivot table you want to copy
2. Press Ctrl +C or press copy button on tab "Home" on the ribbon
   
3. Press with right mouse button on on a cell where you want to paste the pivot table
   
4. Press with mouse on "Paste Special..."
   
5. Select "Values"
6. Press with left mouse button on OK button
7. Repeat step 3 and 4 and then select "Formats"
   
8. Press with left mouse button on OK button

You can tell that the copy has no link to source data by first selecting a single cell in the copied pivot table and then a single cell in the original pivot table. The are two more tabs (Analyze and Design) on the ribbon if the original pivot table is selected

Back to top

12. Customize the layout

You can easily change the pivot table layout with one of the pivot table styles or create a entirely new one.

Select a cell in the pivot table. Go to tab design. Press with mouse on a style and the pivot table is instantly changed. You can hover with mouse pointer over different styles and see the changes to your pivot table change before you make up your mind.

What happens if I record a macro while changing pivot table style?

Sub Macro2()
ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight14"
End Sub

To create a new pivot table style, go to tab Design. Press with left mouse button on the arrow in the lower right corner of pivottable styles window, see picture below.

Select a table element and press with left mouse button on "Format" button.

Change formatting and press with left mouse button on OK button.

Repeat with the remaining table elements you want to change.

Back to top

13. Consolidate data from multiple cell ranges

Excel has a feature that lets you consolidate data from multiple pivot tables or cell ranges.

Here are my two pivot tables for 2015 and 2014. I want to consolidate both pivot tables into one, notice that they share the same structure

Pivot table 1 - 2015

Pivot table 2 - 2014

Here is how to do it.

1. Press Alt + D + P (This opens the pivot table and pivot chart wizard)
   
2. Press with mouse on "Multiple consolidation ranges"
3. Press with left mouse button on Next
   
4. Select "Create a single page field for me"
5. Press with left mouse button on Next button
6. Select the first worksheet range you want to consolidate
   
   Notice that I select the row and column labels as well but not the grand totals.
7. Press with left mouse button on Add button
8. Repeat step 6 and 7 with your remaining ranges.
9. Press with left mouse button on Next button
10. Select if you want to create a new pivot table on an existing sheet or a new worksheet
    
11. Press with left mouse button on Finish
    
12. Left press with left mouse button on arrow next to Count of Value
    
13. Press with left mouse button on "Value Field Settings..."
    
14. Press with left mouse button on "Sum"
15. Press with left mouse button on OK

Back to top

15. Create monthly time sheet using a Pivot Table

Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet.

You can expand each month and see time spend on each project. The pivot table also shows a summary of both months and projects.

I made the pivot table from a simple table containing fake project time data.

This table can have duplicate dates, as you may be working on multiple projects in a single day.

How I created the Pivot Table

1. Select a cell in your table.
2. Go to tab "Insert".
3. Press with left mouse button on "Pivot table" button.
4. Press with left mouse button on OK.

Arrange data

1. Press with left mouse button on and drag "Day" from "Pivot table Field List" to "Column Labels" area.
2. Resize pivot table columns widths.
   
3. Press with left mouse button on and drag "Date" to "Row Labels" area.
4. Press with right mouse button on on a date and select "Group".
   
5. Select months and press with left mouse button on OK.
6. Press with left mouse button on and drag "Project" to "Row Labels" area.
7. Press with left mouse button on and drag "Regular hours" to "Values" area.
8. Go to tab "Design".
9. Press with left mouse button on "Subtotals" button.
10. Press with left mouse button on "Show all subtotals at bottom of group".
    
11. Press with left mouse button on and drag overtime hours to "Values" area.
12. Change headers "Sum of Regular hours" to R and "Sum of overtime hours" to O.

Picture of a monthly timesheet by project with overtime hours.

Recommended articles

• Monthly Timesheet Template
• Employee time sheet (weekly, monthly, yearly)
• Excel Timesheet templates

16. Change PivotTable data source using a drop-down list

In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down list. The tutorial workbook contains three different tables (Table1, Table2 and Table3) with identical column headers.

In this tutorial:

• Create a combo box (form control)
• Select input range
• Add vba code to your workbook
• Assign a macro to drop down list

Create a Combo Box (Form Control)

1. Go to the developer tab
2. Press with left mouse button on "Insert" button
3. Press with left mouse button on combo box (form control)
4. Create a combo box

Want to learn more about Combo Boxes? Read this article:

Recommended articles

Select input range

1. Press with right mouse button on on combo box
2. Press with left mouse button on "Format control..."
3. Go to "Control" tab
4. Select an input range (D2:D4)
5. Press with left mouse button on OK

Cell range D2:D4 contains the table names, you probably need to change these names.

Add VBA code to your workbook

1. Press Alt+F11.
2. Press with right mouse button on on your workbook in the project explorer window.
3. Press with left mouse button on "Insert".
4. Press with left mouse button on "Module".
5. Copy the code shown below and paste to code module, see image above.
6. Return to Excel.

Sub ChangeDataSource()

With ActiveSheet.Shapes(Application.Caller).ControlFormat
    ActiveSheet.PivotTables("PivotTable1").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    .List(.Value)
End With

End Sub

For this code to work you need to change pivot table name (bolded) in vba code: PivotTables("PivotTable1") to the name of your specific pivot table.

Assign a macro

1. Press with right mouse button on combo box
2. Select "Assign macro..."
3. Select ChangeDataSource macro
4. Press with left mouse button on OK!

17. Use hyperlinks in a pivot table

Answer:

The image below shows a very simple PivotTable containing a few hyperlinks, they look like they don't work if you press with left mouse button on them because they miss the blue underline.

Vba code

The following macro is an Event macro meaning it must be located in a sheet module. It runs when a selection changes on a worksheet. For example, every time you select a cell on sheet1 the macro is rund.

'The Event name must be Worksheet_SelectionChange(ByVal Target As Range), you can't change this.
Sub Worksheet_SelectionChange(ByVal Target As Range)

'Declare variables and data types
Dim ptc As PivotTable, Value As Variant, Rng As Range

'Check if only 1 cell is selected
If Target.Cells.Count = 1 Then

    'Iterate through each pivot table in active worksheet
    For Each ptc In ActiveSheet.PivotTables
        
        'If selected cell intersects with pivot table cell range
        If Not Intersect(Target, Range(ptc.TableRange1.Address)) Is Nothing Then

            'Does the cell contain a http or https link?
            If Left(Target.Value, 7) = "https://" OR Left(Target.Value, 8) = "https://" Then

                'Open hyperlink
                ActiveWorkbook.FollowHyperlink Address:=Target.Value, NewWindow:=True
            End If
        End If
    Next ptc
End If

End Sub

How to use Event code

1. Copy VBA code above.
2. Press Alt + F11 to open the Visual Basic Editor.
3. Double press with left mouse button on the sheet in project explorer that contains the Pivot Table.
   
4. Paste code to sheet  module.
5. Exit VBE and return to Microsoft Excel.

18. Normalize data - VBA

To be able to use a Pivot Table the source data you have must be arranged in way that a Pivot Table can handle. For example, the data set above in cell range B2:F5 has headers both horizontally and vertically. In other words, the data set is a two-dimensional table.

Months are arranged horizontally in row 2 and items are arranged vertically in column B, a Pivot Table can't work with data arranged in this way. The data must be arranged row by row meaning values on the same row belong together. Debra has a great post and video about normalizing data for excel pivot table.

This article describes and demonstrates a macro that normalizes data so you can use it in an Excel Pivot Table, meaning it rearranges a dataset layout from two dimensional to row by row. The following animated picture demonstrates the macro.

To view macros in your workbook simply press Alt + F8 to open the Macro dialog box, then press with left mouse button on NormalizeData macro and press with left mouse button on "OK" button to start the macro.

Now you will be prompted for a cell range that the macro can work with and rearrange the data, press with left mouse button on OK button when finished.

The macro creates a new worksheet and populates it with values from the cell range you selected.

Where to put the code

1. Copy VBA code below.
2. Press Alt+ F11 to open the Visual Basic Editor.
3. Press with right mouse button on on your workbook in the Project Explorer.
4. Press with left mouse button on Insert.
5. Press with left mouse button on Module to insert a code module to your workbook.
6. Paste code to module.
7. Exit VB Editor and return to Excel.

VBA code

The VBA code below contains comments so you know what each line does. A comment begins with a ' which is a single quote or apostrophe, you can safely remove the comments if you don't want them in your code.

'Name macro
Sub NormalizeData()

'Dimension variables and declare data types
Dim Rng As Range
Dim WS As Worksheet

'Enable error handling
On Error Resume Next

'Show inputbox so the user can select a cell range and save result to object Rng
Set Rng = Application.InputBox(Prompt:="Select a range to normalize data" _
, Title:="Select a range", Default:=ActiveCell.Address, Type:=8)

'Disable error handling
On Error GoTo 0

'Check if object variable Rng is empty
If Rng Is Nothing Then

'If object variable Rng is not empty then continue with the following lines
Else

    'Don't show changes on screen
    Application.ScreenUpdating = False

    'Add a worksheet and save it to object variable WS
    Set WS = Sheets.Add

    'Save value 0 (zero) to variable i
    i = 0

    'Iterate through 1 to the number of rows in the cell range the user selected
    For r = 1 To Rng.Rows.Count - 1

        'Go through 1 to the number of columns in the cell range the user selected
        For c = 1 To Rng.Columns.Count - 1

            'Save values from cell range to worksheet
            WS.Range("A1").Offset(i, 0) = Rng.Offset(0, c).Value
            WS.Range("A1").Offset(i, 1) = Rng.Offset(r, 0).Value
            WS.Range("A1").Offset(i, 2) = Rng.Offset(r, c).Value

            'Add 1 to variable i
            i = i + 1

        'Continue with next column 
        Next c

    'Continue with next row
    Next r

    'Adjust column widths
    WS.Range("A:C").EntireColumn.AutoFit

    'Show changes to the user
    Application.ScreenUpdating = True
End If

'Stop macro
End Sub

If your data is concatenated to a cell then this article may interest you:

Normalize data, part 2

19. Disable autofit column widths for Pivot table

I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is about Excel automatically making column widths too wide when using URLs in pivot tables. Stacey Armstrong demonstrates how to disable this setting.

Here are the steps to manually disable Excel resizing column widths automatically.

1. Press with right mouse button on on any cell in the Pivot table.
   
2. Press with mouse on "Pivot Table Options".
   
3. Press with mouse on the "Layout and Format" tab, then press with left mouse button on the box next to "Autofit column widths on update" to uncheck it.
4. Press with left mouse button on OK button to close the dialog box.

Lindsay Hughes commented:

I recommend that you create a personal macro workbook and save the following macros to that workbook. This will make it easier for you to change this setting automatically, simply run the macro to apply this setting to all Pivot tables in the active workbook.

If you use it really often then I recommend adding the macro to the Quick Access Toolbar located at the very top of Excel.

The following Event macro disables this setting for a Pivot Table named PivotTable1 in a worksheet named Sheet1. An event in Excel is a thing that happens that triggers something else to happen.

In this case, if any worksheet in the workbook is activated the code is rund.

 
'Event code is exectued when any sheet is activated
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 

'Save False to HasAutoFormat property for worksheet Sheet1 and PivotTable named PivotTable1
Worksheets("Sheet1").PivotTables("PivotTable1").HasAutoFormat = False
End Sub 

I can't change it to the default setting but the code below automatically disables this setting for all pivot tables on the active sheet.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

'Dimension variable and declare datatype
Dim pt As PivotTable

    'Iterate through pivottables in active worksheet
    For Each pt In ActiveSheet.PivotTables

        'Change property HasAutoFormat to False
        pt.HasAutoFormat = False

    'Continue with next pivot table
    Next pt
End Sub

This macro changes the setting for all pivot tables in all worksheets in the active workbook.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

'Dimension variables and declare datatypes
Dim pt As PivotTable
Dim WS as Worksheet

'Go through each worksheet in the worksheets object collection
For Each WS In Worksheets

    'Iterate through pivottables in active worksheet
    For Each pt In WS.PivotTables

        'Change property HasAutoFormat to False
        pt.HasAutoFormat = False

    'Continue with next pivot table
    Next pt

'Continue with next worksheet
Next WS
End Sub

The following macro disables the setting for pivot tables in all open workbooks

 
Private Sub Workbook_SheetActivate(ByVal Sh As Object) 

'Dimension variables and declare datatypes
Dim pt As PivotTable 
Dim WS as Worksheet 
Dim j As Single

'Go through all open workbooks
For j = 1 To Workbooks.Count
   'Go through each worksheet in the worksheets object collection 
   For Each WS In Workbooks(j).Worksheets 

       'Iterate through each pivot table
       For Each pt In WS.PivotTables 
           'Change property HasAutoFormat to False
           pt.HasAutoFormat = False 

       'Continue with next PivotTable
       Next pt 

       'Continue with next WorkSheet
   Next WS 

'Continue with next workbook
Next j
End Sub 

19.1 Where to put the code?

1. Copy above VBA code.
2. Press Alt+F11 to open the Visual Basic Editor.
3. Double press with left mouse button on your workbook to expand the object list.
4. Double press with left mouse button on Thisworkbook in the project explorer window to show the workbook module.
5. Paste VBA code to the workbook module.
6. Exit VB Editor and return to Excel.

Pivot table category

More than 1300 Excel formulas

Excel categories

Comments (61)