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