Pivot Tables and comparative Study

Pivot tables are powerful tools in Excel for summarizing and analyzing data. Comparing values side-by-side in a pivot table is a common and highly effective way to gain insights from your data. Here's how you can achieve this, along with various comparison types.

Adding Multiple Value Fields for Side-by-Side Comparison

The most straightforward way to compare values side-by-side is by adding the same value field multiple times to the "Values" area of your PivotTable. Each instance of the field can then be configured to display a different calculation or a different "Show Values As" option.

Steps to Add Multiple Value Fields:

1. Create your PivotTable: Select your data range, go to the "Insert" tab, and click "PivotTable." Choose where you want to place the PivotTable (new or existing worksheet) and click "OK."

   
   

2. Drag fields to areas:

   • Drag the field you want to analyze (e.g., "Sales," "Quantity," "Amount") to the "Values" area. This will typically default to "Sum of Sales" or "Count of Quantity," etc.

     
     

   • Drag the field you want to compare by (e.g., "Year," "Region," "Category") to the "Columns" area. This will create columns for each unique item in that field.

   • Drag any categorical fields you want to group your data by (e.g., "Product," "Employee") to the "Rows" area.

3. Add the same value field again: Drag the same value field (e.g., "Sales") from the "PivotTable Fields" pane to the "Values" area a second time. You'll now have two columns for your value, for example, "Sum of Sales" and "Sum of Sales2".

4. Configure the second value field:

   • Right-click on any cell within the second value column (e.g., "Sum of Sales2").

   • Select "Value Field Settings..."

   • In the "Value Field Settings" dialog box, you can:

     • Change the "Custom Name": Rename it to something more descriptive, like "Sales - % of Grand Total" or "Sales - Difference from Previous Year."

     • Go to the "Show Values As" tab. Here's where the powerful comparison options lie.

Common Side-by-Side Comparisons

Here are some common ways to compare values side-by-side using the "Show Values As" option:

1. Actual Values vs. Percentage of Grand Total

This comparison shows the absolute value alongside its contribution to the overall total.

• First Value Field: Keep it as "No Calculation" (Sum, Count, Average, etc.).

• Second Value Field:

  • Right-click and go to "Value Field Settings."

  • In the "Show Values As" tab, select "% of Grand Total".

    
    

2. Actual Values vs. Percentage of Column Total

This is useful for seeing how each item contributes to its respective column's total (e.g., how much each product contributes to sales in a particular region).

• First Value Field: Keep it as "No Calculation."

• Second Value Field:

  • Right-click and go to "Value Field Settings."

  • In the "Show Values As" tab, select "% of Column Total".

    
    

3. Actual Values vs. Percentage of Row Total

Similar to column total, but shows contribution to the row's total (e.g., how much each region contributes to a product's total sales).

• First Value Field: Keep it as "No Calculation."

• Second Value Field:

  • Right-click and go to "Value Field Settings."

  • In the "Show Values As" tab, select "% of Row Total".

    
    

4. Difference From (e.g., Year-over-Year Comparison)

This is excellent for trend analysis, showing the difference between a value and a "base" item (like the previous year).

• First Value Field: Keep it as "No Calculation" (e.g., "Sum of Sales").

• Second Value Field:

  • Right-click and go to "Value Field Settings."

  • In the "Show Values As" tab, select "Difference From".

    
    

  • Choose your Base Field (e.g., "Year") and your Base Item (e.g., " (previous)" for year-over-year, or a specific year like "2023").

    
    

5. % Difference From

Similar to "Difference From" but displays the percentage change.

• First Value Field: Keep it as "No Calculation."

• Second Value Field:

  • Right-click and go to "Value Field Settings."

  • In the "Show Values As" tab, select "% Difference From".

    
    

  • Choose your Base Field and Base Item as needed.

6. Running Total In / % Running Total In

Useful for cumulative analysis, showing the running total or running percentage total within a chosen field.

• First Value Field: Keep it as "No Calculation."

• Second Value Field:

  • Right-click and go to "Value Field Settings."

  • In the "Show Values As" tab, select "Running Total In" or "% Running Total In".

    
    

  • Choose the Base Field to define the order of accumulation (e.g., "Month" if you're tracking monthly sales).

    
    

Tips for Effective Side-by-Side Comparisons

• Descriptive Custom Names: Always rename the duplicated value fields with clear and concise names that reflect the calculation being performed (e.g., "Sales," "Sales % of Total," "Sales Diff YOY").

• Number Formatting: Format the number values appropriately (e.g., currency for sales, percentage for percentages, no decimals for counts). Right-click on a value cell, select "Number Format," and choose the desired format.

  
  

• Clarity in Row/Column Labels: Ensure your row and column labels are clear and organized to make the comparison easy to understand.

• Consider Data Structure: For complex comparisons involving different time periods or categories that are in separate columns in your source data, consider "unpivoting" or "stacking" your data first so that all values are in one column and a new column identifies the category (e.g., "Year" or "Scenario"). This makes it easier to use pivot table features for comparison.

• Conditional Formatting: Apply conditional formatting to highlight differences, trends, or outliers in your side-by-side comparison. For example, use data bars or color scales to visually represent values or differences.