Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles PIVOT TABLE Combining Pivot Tables with Macros in Excel

Combining Pivot Tables with Macros in Excel

When we consistently deal with the same Pivot Table format, we can use Macros to record that exact format so that we can create and edit our Pivot Tables in a lesser time. In simple illustrative steps, we will work through the ways a macro can be helpful in creating and editing our Pivot Tables.

Figure 1 – Creating Pivot Table Macro

Turning our Data into a Table

  • We will begin by turning our raw data into a simple table format. This ensures that we can easily update our tables (and the Pivot table) at any time.
  • We will click the Insert tab, and then, Table

Figure 2- Clicking on Table

  • Next, we will name our table by clicking on the Properties group in the Design tab.

Figure 3 – Turn Raw Data into a Table

Evaluating our Most Used Settings

  • Do we usually group our data, or do we always turn off subtotals?
  • Is there a definite pattern we create a new Pivot Table? We must figure this out because Macros would give us the flexibility to avoid extra clicks and save us a lot of time.

Create Pivot Table Macro

  • We must click on any cell in our data before recording our macro
  • We will start recording a macro by clicking View and then Record Macro

Figure 3- Click on Macros

Figure 4 – Recording Pivot Table Macro

  • We will click OK
  • We will click Insert and select Pivot table
  • We will add the usual changes we make in the Pivot Table dialog box, press OK and we will be presented with the Create Pivot Table dialog box
  • Now, we will make our favorite layout changes first BEFORE selecting our fields.

Figure 5 – Set Layouts Before Stopping Pivot Table Macro

  • These changes might include:
    • Turning Columns and ROWs headers off or on
    • Picking a Compact style pivot table
    • Changing the way Subtotals and Grand totals appear
  • After making the layout changes, we will STOP the macro.

Now, we can quickly style our Pivot table to meet specific needs. The next time we create a Pivot table, it will be handled swiftly, without having to make any additional clicks.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc