Lesson 7 - Pivot Tables for Generating Insights

Learn how to use pivot tables to group and summarize data for generating insights.

Estimated Read Time: 1 - 1,5 Hours

Learning Goals

In this lesson, you will learn:

Technical & Analytical:

Create and manipulate pivot tables to summarize and analyze large datasets efficiently.
Use advanced features like filters, slicers, grouping, and calculated fields to uncover hidden insights.

Business Impact:

Transform sales and customer data into actionable insights for strategic decision-making.

1. The Business Challenge: Making Sense of Rossmann’s Sales Data

One of the analyst’s main tasks is to summarize data — in other words, draw attention to the main points of interest. One way to do this is by organizing the data into meaningful groups.

Imagine you are presenting to Rossmann’s executive leadership team. They want to understand how sales performance varies across all stores – not just for the top store (store 57) rather all of them. This will require you to group the data by sales w.r.t. different dimensions like promotions, store type, holidays etc. — a task that becomes easy when you know how to use one of Excel’s most valuable features, the pivot table.

A pivot table is a statistical summary of a data set. Pivot tables organize data into groups based on the values’ sum, average, count, etc.

2. Why Pivot Tables Are the Analyst’s Best Ally?

PivotTables facilitate EDA by allowing analysts to dynamically summarize and explore data across various dimensions. They enable quick identification of trends, comparisons, and patterns, supporting data-driven decision-making.

Handling thousands of daily sales records across more than a thousand stores, manually calculating aggregates or writing nested formulas is inefficient and error-prone. In the context of Rossmann sales, pivot tables offer a robust solution by enabling you to:

Instantly aggregate sales and customer data without writing complex formulas.
Slice and dice data by different dimensions, such as store type or promotion status.
Dynamically update analyses as new data becomes available, saving time and reducing errors.
Create interactive reports that invite exploration and support executive decision-making.

Pivot tables are invaluable for creating clear, interactive summaries that managers and executives can easily understand, even if they lack technical expertise in SQL or other advanced tools.

3. How to Create a Pivot Table?

Let’s create a pivot table to calculate average sales per store for our Rossmann dataset. Feel free to follow along with the written instructions, or video walkthrough:

3.1 Inserting a Pivot Table

To create a new pivot table, select any cell inside the data set that you want to organize. On the Insert tab, select PivotTable.

Excel will select all the data in your worksheet by default. In the Create PivotTable dialog that appears, you’ll need to specify whether you’d like the pivot table to be placed in a new worksheet or an existing worksheet. If you check Existing Worksheet, you’ll also need to enter the range of cells where the new pivot table will be located; take care not to place the pivot table in a range that overlaps with your original data. Sticking with the default option of placing the pivot table in a new worksheet can help avoid this, as shown in Figure 2.

After inserting your pivot table, a list called PivotTable Fields will appear on the right side of your worksheet. From here, you can add, remove, and modify the elements in your pivot table.

You can add field names to either the rows or columns in your worksheet depending on how you want the summary of your data to be presented. For this example, let’s say you want to see the average sales per store. Since there are 100 stores in the dataset, let’s have one store information per row.

To add the required data to your pivot table, go to the PivotTable Fields list and drag Store from FieldName to the Rows area below. As a result, the rows of your pivot table will represent stores. To add the average sales of each store to your pivot table, drag Sales from FieldName to the Values area (shown in Figure 4). Your pivot table will now show the sum of Sales of each store. Since you’re interested in average (and not sum) of Sales, click the little (i) on the right of Sales and from the popup (Figure 5), choose Average instead of Sales. This will give you average Sales per Store (Figure 6).

3.2. Filtering with the Filters Pane

If you decide that you want to view the average Sales only for Open stores — and not for all stores — you can add a filter. You do this by dragging Open to Filter. This will add Open on the top of the pivot table where you can click on the dropdown (All) and de-select Open=0. As a result, your pivot table will show the average Sales taking into account only open stores.

3.3. Additional Columns

Aggregation is a useful technique for representing a set of numbers with a single value, for example, the sum, the average, the maximum, and so on.

If you also want to see the average Customer traffic per store, simply drag Customers to Values and change the Sum to Average. The pivot table will update to include average Customers per Store as well.

Imagine now you want to see the store data separately when promotions are running vs when no promotions are running. You can drag the Promo field to Rows, and it will update the pivot table to segregate the Sales and Customer information for the two categories – Promo running (1) vs Promo Not Running (0).

Consider another example, where instead of promotions, you want to see distribution on a day-to-day basis. Remove Promo from Rows and drag DaysOfWeek to Rows instead. Now you’ll see the sales and customer information per day.

If you want to sort the data w.r.t. Sales from largest to smallest (instead of Store ID), you can right-click on any value in the “Average of Sales” column and a dropdown menu with various sorting options will appear. From here, you can sort the stores from smallest to largest based on the average Sales. (Note that you can also sort based on a specific day, in case you want to see which day of the week generates the most sales – but that’s not relevant for us at the moment).

3.4. Expanding and Collapsing Fields

When multiple variables are added to the Rows area (or the Columns area), a small gray box with a “-” in it (known as an “expand or collapse button”) will appear next to each row label in the pivot table, like the one next to Store Numbers.

Clicking the expand or collapse button will either display or hide data in the summary view; if you wanted to hide the data for “Store 35,” for example, all you’d need to do is click the expand or collapse button next to it. Go ahead and give it a try—you should see the following (Figure 14):

Now that the data for Store 35 is hidden, the “-” on the box has turned into a “+”. To restore the hidden data, click the “+” symbol next to Store

To collapse all the data for a particular category, right-click the expand or collapse button belonging to an item from the field that you want to collapse, click Expand/Collapse, then select Collapse Entire Field, as shown in Figure 15:

As you can see in Figure 16, the entire “DaysOfWeek” field has been collapsed, and all of its values 1, 2, … 7) are hidden from view. This would be useful if you wanted to focus on Store-level data rather than Sales on a particular day of week.

To restore the “DayOfWeek” field, right-click the expand/collapse button, select Expand/Collapse, then click Expand Entire Field.

3.5. Using Refresh to Update the Data in the Pivot Table

One of the great things about pivot tables is they let you manipulate data without changing the actual source data. If your raw data changes, however, the information in your pivot table will immediately become outdated. For this reason, you’ll need to refresh your pivot table whenever your source data changes.

To refresh the data, right-click any cell in your pivot table and select Refresh from the drop-down list.

4. Filtering with Slicers

A more dynamic and user-friendly way to filter the data in a pivot table is with slicers. Slicers are basically menus that let you click and select the data you want to include in your pivot table. They’re great if you’re planning on sharing your pivot table with someone who may want to add or remove filters themselves; for example, if you sent a report with sales figures from two Stores to a client, a slicer would allow them to toggle between the data for each store and view any related summaries or insights.

Note!
Slicers are not available in Excel versions 2011 and earlier. If your version of Excel doesn’t support slicers, try using Google Sheets to perform slicer tasks.

To get some hands-on experience with slicers, let’s explore Rossmann Sales per store w.r.t. StateHolidays and DayOfWeek. You can follow along with our video walkthrough or the instructions below:

Video 2: Filtering with Slicers

Let’s start by creating a new pivot table for sales per store. To add a slicer to your pivot table, go to the PivotTable Analyze tab and select Insert Slicer (Figure 18).

In the Insert Slicers dialog that appears, select the checkboxes for the variables you want to add a slicer to— e.g. DaysOfWeek and StateHoliday —then, click OK.

Two slicers will appear on your pivot table worksheet, one for “DaysOfWeek” and another for “StateHoliday,” listing every value associated with these two variables (Figure 20).

To add or remove an item from your pivot table, simply click on that item in the slicer; as an example, in the StateHoliday slicer, click a, b or c. This shows sales for entries representing a state holiday, Easter holiday or Christmas holiday.

While you’re filtering your pivot table to show only data with StateHoliday, see how certain days have been grayed out in the DaysOfWeek slicer? This is because these holidays are falling during the certain days only. Also notice how most of the sales values are zero during holidays. Feel free to play around with the different options (Figure 21). Finally, if you want to clear filter, select the cross button on the top right of the slicer.

If you want to select multiple values, click the Multiple Entries button on top (Figure 22) then double click the desired entries to select them. You can also select one value then hold Ctrl/Cmd (on Windows/Mac) while clicking more.

5. Counting Records

It’s often helpful in an analysis to know how many records (rows) exist for a data element. Pivot tables let you quickly count the number of records. So far, the primary reference point for your pivot table has been the sales — which appear in the left-most column of your pivot table. Suppose you wanted to see the total number of records you have for different state holidays. This could be useful if, say, you are planning availability of cashiers in your stores and accounting for their vacations.

Let’s start with a global count of how many holidays of each type exist. Recall: a is a public holiday, b is Easter holiday and c is Christmas holiday. Let’s create a fresh pivot table. You can follow the video walkthrough or the written steps below.

Video 3: Counting State Holidays

Go to your raw dataset, with StateHoliday reflecting the four entries: 0, a, b and c. Select the data and insert pivot table in a new sheet.

Next, drag StateHoliday to the rows. This will give you the Row Labels for each Holiday category. Finally, drag the StateHoliday to Values and you’ll see the counts of each holiday w.r.t. type (Figure 23).

Imagine you now want to know the StateHoliday counts per store. You can drag Store to rows. You’ll notice that by doing this the main category will still be the holiday with lots of stores underneath them. This is not what you want – you want the stores and major category and the holiday types within them. For this, simply drag the StateHoliday in the Rows pane and reorder it to lie below Stores. Consider Figure 24 for the before / after impact of reordering.

Let’s now filter out the entries with zeros as they indicate no Holiday. The final updated pivot table looks like in Figure 25.

6. Creating New Groups

There may be times when you want to analyze groupings that don’t exist in your source data; for instance, you might want to see average sales over weekdays vs weekends. Now, your raw data doesn’t have a “weekdays” (or weekend) group, but you can use your pivot table to build one. It would be useful for you create a new group from the ground up, so reset your pivot table by dragging any fields in the Rows and Values area to the Field Name area. Follow the Video 4 walkthrough or the steps below.

Video 4: Creating New Groups

Start by dragging DayOfWeek to the Rows and Sales to Values. Make sure that you calculate Average Sales instead of the Sum of Sales.

Next, select the days you want to include in your group. Hold down Command (Mac) or Ctrl (PC) and, in your pivot table, click “1”, “2”, “3”, “4” and “5”. Once you have the relevant rows highlighted, right-click and select Group from the drop-down list (Figure 26). In a similar manner, select “6” and “7” and form a group.

Remember, this new group won’t affect your raw data because it only exists in your pivot table. To give your group a more meaningful name, highlight the cell containing the group name (“Group1”) and type “Weekdays” in the formula bar. Similarly, for Group 2, type “Weekend” in the formula bar to rename (Figure 27).

7. Calculated Fields

Calculated fields let you add custom-defined variables to your pivot table. Suppose you wanted to know what is the average spending per customer for each store. There’s no “Average Spending Per Customer” variable in your data set, but you can build one with a calculated field. To do so, you can follow along with our video walkthrough or the instructions below:

Video 5: Calculating Fields

Let’s start fresh by creating a pivot table and adding the Stores as rows and Sales and Customers as Values. You can leave these to the Sum and don’t have to convert to average values.

On the PivotTable Analyze tab, select Fields, Items, & Sets (Figure 28).

A new dialog, Insert Calculated Field, will appear. In the Name box, you’ll see that Excel has, by default, called your new field “Field1.” Go ahead and change this name to something more meaningful like “Average Spending per Customer”. Right below Name is the Formula box—this is where you’ll enter the formula for your field. Below the Formula box is a list of available fields (Figure 29).

In this instance, you want to create a field that displays the average spending per customer, which you can calculate by dividing the Sales by Customers. Both the “Sales” and the “Customers” variables are listed in the Fields menu. In the Formula bar, you’ll see the default formula “=0.” Excel formulas always begin with “=”, so go ahead and keep this. The “0”, however, can be deleted as it won’t be included in the definition of your new calculated field.

Let’s build your new variable! In the Fields menu, select Sales by either double-clicking or select + “insert Field” and enter then the divisor (/) followed by Customers.

Your calculated field should appear at the right-hand side of your pivot table.

To make further sense of the data, let’s sort the pivot table according to this new field. This will allow us to discover which stores perform best with respect to average spending per customer and which ones need support (Figure 31).

Combining data to create new variables often reveals insights that were hidden in the raw data. Calculated fields are a powerful tool to achieve this.

Summary

In this lesson you learned:

how to use pivot tables to group, summarize, and add value to your data
expanding and collapsing fields
filtering with slicers
counting records
creating new groups
creating new variables with calculated fields.

Exercise

Estimated Time to Complete: 1-2 hours

Dataset: walmart-sales.xlsx

Task 1 – Comparing Store Sales

Business Need:
Walmart executives want a clear picture of which stores consistently bring in the highest weekly sales.

Task:
Create a PivotTable that shows average Weekly_Sales per Store, and sort the results from highest to lowest.

Reflection:
What does the ranking reveal about store performance? Are there standout leaders or laggards worth investigating further?

Task 2 – Holiday Effect on Sales

Business Need:
The Finance team wants to understand whether holiday weeks truly boost sales, and by how much.

Task:
Build a PivotTable that compares total Weekly_Sales in holiday vs. non-holiday weeks. Add Store as rows, and use a slicer for IsHoliday to toggle the view.

Reflection:
Do holiday weeks consistently generate higher sales across all stores, or do the effects vary significantly by location?

Exercise 3 – Sales Efficiency by Department

Business Need:
The Merchandising team is looking for patterns in department performance to decide where to allocate promotions and inventory.

Task:
Create a PivotTable with Department (Dept) as rows and Store as columns, showing average Weekly_Sales. Then:

Add a calculated field to estimate Sales per Department per Week.
Group together any low-performing departments to simplify the view.

Reflection:
Which departments appear to drive the most consistent value across stores, and which ones may need attention or rethinking?

Submission Guidelines

Submit your solution as an Excel workbook:

Workbook:

appropriate worksheets with analysis
add a reflections sheet

Filename Format:

YourName_Lesson7_Walmart_PivotTables.xlsx

When you’re ready, submit your completed exercise to the designated folder in OneDrive. Drop your mentor a note about submission.

Important: Please scan your files for viruses before uploading.

Submission & Resubmission Guidelines

Initial Submission Format: YourName_Lesson7_…
Resubmission Format:
- YourName_Lesson7_…_v2
- YourName_Lesson7_…_v3
Rubric Updates:
- Do not overwrite original evaluation entries
- Add updated responses in new “v2” or “v3” columns
- This allows mentors to track your improvement process

Evaluation Rubric

Criterion	Exceeds Expectation	Meets Expectation	Needs Improvement	Incomplete / Off-Track
Exercise 1: Store Sales Comparison	PivotTable correctly shows average Weekly_Sales per Store, sorted clearly; formatting (bold headers, clean layout) makes results easy to read. Reflection links results into a short, meaningful story about store performance.	PivotTable correctly shows average Weekly_Sales per Store and is properly sorted; output is accurate and professional.	PivotTable created but with errors (wrong aggregation, missing sort, messy layout).	No usable PivotTable submitted or shows misunderstanding of task. Plagiarised or AI-generated submission
Exercise 2: Holiday Effect on Sales	PivotTable correctly compares holiday vs. non-holiday sales; slicer applied effectively; formatting highlights differences clearly. Reflection tells a concise story about how holiday impact varies across stores.	PivotTable correctly compares holiday vs. non-holiday sales and includes slicer; output is accurate and complete.	PivotTable present but missing slicer, misused fields, or unclear comparison.	No attempt at holiday vs. non-holiday analysis. Plagiarised or AI-generated submission
Exercise 3: Department-Level Efficiency	PivotTable built with Dept × Store layout, calculated field applied correctly, low-performing departments grouped logically; formatting aids readability. Reflection captures patterns in department performance as a business narrative.	PivotTable built with Dept × Store layout, calculated field applied, grouping used appropriately; output is accurate and complete.	PivotTable built but missing calculated field or grouping, or setup errors reduce clarity.	No usable department-level PivotTable submitted. Plagiarised or AI-generated submission