Multiple Pivot Tables Excel

Advanced Pivot Table Techniques: Multiple Tables and Dynamic Analysis

Pivot Tables represent one of Excel's most powerful analytical tools, and mastering their advanced features can dramatically enhance your data analysis capabilities. This comprehensive tutorial explores sophisticated techniques covered in our NYC Excel classes. For those outside New York, explore and compare the best Excel classes near you or join our live online Excel classes to develop these critical business intelligence skills.

Today's business environment demands rapid, multi-dimensional data analysis. Let's explore how to leverage multiple Pivot Tables and advanced value calculations to transform raw data into actionable insights. We'll begin by creating a foundation Pivot Table using properly structured table data.

Starting with your organized data table, navigate to the Insert tab and select Pivot Table. The Create Pivot Table dialog appears, offering placement options. For this advanced analysis, we'll create our Pivot Table on a new worksheet to maintain data separation and organization. Click OK to proceed, then immediately rename the sheet to "pivot 2" for clear identification—a best practice when managing multiple analytical views.

This systematic approach to worksheet naming becomes crucial when building comprehensive analytical dashboards with multiple interconnected Pivot Tables.

Leveraging Multiple Value Fields for Comprehensive Analysis

Now we'll implement a sophisticated technique that many Excel users overlook: utilizing the same numerical field multiple times within a single Pivot Table to create different analytical perspectives.

Begin by adding your categorical data: first drag Category to the Rows area, followed by Product. When you simply check the boxes for text fields, Excel intelligently places them in the Rows section, creating a logical hierarchy with Product nested under Category.

Next, add Total Sales to the Values area by checking its box. Since this is numerical data, Excel automatically aggregates it as a sum. Here's where the advanced technique begins: drag the Total Sales field into the Values area two additional times. This creates three separate value columns: "Sum of Total Sales," "Sum of Total Sales 2," and "Sum of Total Sales 3."

This approach allows you to analyze the same data through multiple lenses simultaneously—for instance, viewing totals, averages, and percentages side by side for immediate comparison and insight generation.

Important Note: Mac users may need to manually drag fields into appropriate areas rather than using the checkbox method, as the interface behavior differs slightly between platforms.

Begin customizing these columns for maximum analytical value. First, rename the initial column to "Total Sales" for clarity. To modify the second column for average calculations, access the Value Field Settings through any of these methods: click the dropdown arrow next to the field name in the Values area, use the Field Settings button on the PivotTable Analyze ribbon tab, right-click the column header and select "Value Field Settings," or simply double-click the column header.

In the Value Field Settings dialog, change the function from Sum to Average and update the custom name to "Average Sales." This immediate transformation provides both total and average sales data in a single view, enabling quick identification of high-volume versus high-value categories.

Implementing Percentage Analysis for Strategic Insights

The third column will showcase one of Pivot Tables' most powerful analytical features: automatic percentage calculations. Access Value Field Settings for the third column and navigate to the "Show Values As" tab—this is where Excel's built-in analytical calculations reside.

Select "% of Grand Total" from the dropdown menu. This instantly transforms raw numbers into percentage distributions, revealing each category and product's contribution to total sales without requiring manual calculations. Rename this column to "Percentage of Sales" and adjust column widths for optimal viewing.

This tri-column approach—totals, averages, and percentages—provides a comprehensive analytical foundation that addresses the most common business questions: What sells the most? What performs best on average? What represents our biggest opportunities?

Traditional filtering methods, while functional, often create confusion when multiple criteria are involved. When you filter by individual sales representatives like Adams or Byron, the interface shows "Multiple Items" without clear indication of current selections. Let's implement a more sophisticated solution that enhances both analysis and presentation.

Remove fields from the Filters area and instead leverage Excel's Slicer functionality. Navigate to PivotTable Analyze > Insert Slicer, then select the fields you want to filter by—in this case, Region and Sales Rep. Click OK to create interactive filter controls.

Slicers provide immediate visual feedback and intuitive operation. Single-click any name to filter instantly, or drag across multiple names for multi-selection. Use Ctrl+click to select non-adjacent items. The clear visual indicators show exactly which filters are active, eliminating the guesswork inherent in traditional dropdown filters.

For enhanced presentation, optimize slicer layout by accessing the Slicer tab and adjusting the Columns setting from 1 to 2. This creates a more compact, professional appearance that maximizes dashboard real estate while maintaining functionality.

Creating Interconnected Analytical Dashboards

Professional analysts often require multiple perspectives on the same dataset simultaneously. Let's create a complementary Pivot Table that works in concert with our primary analysis.

Navigate back to Insert Pivot Table, but this time select "Existing Worksheet." Click in the Location box (ensure you see a flashing cursor), then click the "pivot 2" sheet tab and select cell H4 for placement. This positions the second table adjacent to your slicers for optimal workflow.

Create a streamlined table with Sales Rep and Total Sales only. This secondary view provides individual performance metrics alongside your categorical analysis, but initially remains unaffected by your existing slicers.

To create true dashboard functionality, connect your slicers to multiple Pivot Tables. Select any slicer, navigate to the Slicer tab, and click "Report Connections." Check the box for your second Pivot Table (likely named "PivotTable5"). Repeat this process for all slicers.

Now your filtering actions simultaneously update both analytical views, creating a comprehensive dashboard that reveals both categorical trends and individual performance with every filter adjustment. This interconnected approach transforms static reports into dynamic analytical tools that adapt to changing business questions in real-time.

Professional Applications and Strategic Value

These advanced techniques represent fundamental skills for data-driven decision making in today's business environment. The ability to create multiple analytical perspectives, implement sophisticated calculations without formulas, and build interactive dashboards distinguishes proficient Excel users from power users.

Whether you're analyzing sales performance, operational metrics, or financial data, these methods provide the flexibility to answer complex business questions efficiently. The combination of multiple value fields, percentage analysis, and interconnected filtering creates professional-grade analytical tools using Excel's built-in capabilities.

Master these techniques to transform your approach to data analysis, moving from static reporting to dynamic business intelligence that adapts to evolving analytical needs and supports strategic decision-making at every level of your organization.