Pivot Tables in Excel

What is a Pivot Table?

A pivot table is a powerful tool in Excel that allows you to summarize and analyze large datasets by creating custom views of your data. It enables you to rotate, aggregate, and filter data to gain insights and make informed decisions.

How to Create a Pivot Table

To create a pivot table in Excel, follow these steps:

Select the cell range that contains the data you want to analyze.

Press Control+T on your keyboard (this creates a table), click ok This is important if you append or will add any columns to your source data, pivot tables will not see the addition of the columns or rows unless it is a table. Generally I prefer tables as the formulas are cleaner too (e.g. column names istead of cell).

Go to the "Insert" tab in the ribbon and click on the "PivotTable" button.

In the "Create PivotTable" dialog box, select a cell where you want to place the pivot table and click "OK".

You now have a clean pivot table and you will see all of the headers from the source table.

Basic Pivot Table

Lets create a basic analysis. In the "PivotTable Fields" pane (right side), drag the Date to the columns, Region to Rows, and Total Sales to Values.

You will will see the Date is expanded in the Columns to include Years, Quarters, Months, and Date. This is because Excel detects these are dates and gives you the ability to craete views and filtering on these additional parameters. Drag the year over to Filters, click on the Quarter and click remove, also the same for Date.

Your PivotTable Fields should look like this:

Now in the top left, You'll see Years. Click that and select "2024".

And your data shold look like this.

This is a solid starting pivot, but it still needs formatting. Select all of the values, then click the "Comma" button at the top in the Numbers section. This will format the values with numbers to have thousands separators and 2 commas.

Great, but its messy, so click the decrease decimal, twice.

Now you have a pivot table that is filtered for 2024, formatted with thousand separator, and has the months as columns.

Additional Views

Move the region to columns and the months to rows:

Move the quarters to columns and product category to rows:

Advanced Pivot Tables

Advanced pivot tables can help you analyze data from different angles, making it easier to spot trends, patterns, and correlations. Here are some examples of advanced pivot table techniques:

• Using Multiple Data Sources: You can use multiple data sources in a single pivot table by dragging fields from each source into the same sections.
• Creating Custom Calculations: You can create custom calculations using formulas to perform complex operations on your data.
• Using Date and Time Fields: You can use date and time fields to analyze data over time, such as sales data or website traffic.
• Grouping and Filtering: You can group and filter large datasets by creating multiple levels of granularity, making it easier to spot trends and patterns.

For example, let's say you have a dataset with sales data for different products, customers, and regions. You could create an advanced pivot table that analyzes this data by:

• Using the "Region" field as a row label
• Using the "Product" field as a column label
• Calculating the total sales revenue using the "Sales Amount" field
• Filtering the data to only show sales from a specific date range

Here is an example of what an advanced pivot table might look like:

Note: we will update with screenshots shortly.

Pivot Table Features

A pivot table can display data in various formats, including:

• Data as Columns: By dragging the "Row Labels" field to the "Values" section, you can create a pivot table where each row represents a unique value in that column.
• Data as Rows: By dragging the "Column Labels" field to the "Rows" section, you can create a pivot table where each row represents a unique value in that column.
• Tabular Format: You can create a tabular format by selecting the "Tabular Form" option in the "PivotTable Fields" pane.
• Compact Format: You can also use the compact format to summarize large datasets, such as sales data or customer information.

In terms of values, you can use:

• Minimum Value: Use the "Min" function to display the minimum value in a field.
• Maximum Value: Use the "Max" function to display the maximum value in a field.
• Average Value: Use the "Avg" function to display the average value in a field.
• Sum of Values: Use the "Sum" function to display the total sum of values in a field.

These pivot table features can be used to create visual charts, such as:

• Column Charts: To display data as columns and show trends over time.
• Bar Charts: To display data as rows or columns and compare different groups.
• Line Charts: To display data as rows or columns and show changes over time.

For example, you can use a pivot table to create a bar chart showing the sales revenue for each product by region. By dragging the "Product" field to the "Columns" section and the "Region" field to the "Rows" section, you can create a pivot table that displays the data in a tabular format.

You can then use this pivot table to create a visual chart, such as a bar chart or column chart, to show the sales revenue for each product by region.