Let's dive into a more detailed example using a sales dataset to create a pivot table and utilize all the steps we discussed in Previous Blog
Assume we have a sales dataset with the following columns:
"Product," "Region," "Year," "Quarter,"
"Month," "Revenue," and "Units Sold." We'll
create a pivot table using this dataset and explore various options and
settings.
1. Prepare the data:
Ensure that your sales data is organized in a tabular format with clear column headers and no blank rows or columns.
2. Select the data range:
Highlight the entire sales dataset, including the column headers and all relevant rows.
3. Insert a Pivot Table:
Go to the "Insert" tab, click on "PivotTable," and select the location for the pivot table (e.g., new worksheet).
4. Set up the Pivot Table:
In the PivotTable Field List pane, perform the following steps:
- Drag the
"Product" field to the Row area to group the data by products.
- Place the
"Region" field in the Filter area to analyze sales across different
regions.
- Drag the
"Year" field to the Column area to compare sales figures over
multiple years.
- Place the
"Quarter" field in the Filter area to further analyze sales by
quarters within each year.
- Drag the
"Month" field to the Filter area to examine sales on a monthly basis
within each year and quarter.
- Drag the
"Revenue" field to the Value area to calculate the sum of revenue for
each combination of row and column labels.
- Drag the "Units Sold" field to the Value area to calculate the total number of units sold for each combination of row and column labels.
5. Field Settings:
To customize the calculations and summary functions for your pivot table, you can access the Field Settings:
- Right-click on a field in the pivot table (e.g., "Revenue") and select "Value Field Settings." Here, you can change the calculation from the default sum to other options like average, count, maximum, minimum, etc.
- You can also format the number display, change decimal places, or apply custom number formats through the "Number Format" button in the Value Field Settings.
6. PivotTable Options:
Explore the PivotTable Options to further customize your pivot table:
- Right-click
anywhere inside the pivot table and select "PivotTable Options."
- In the
"Display" tab, you can choose to show or hide various elements like
grand totals, subtotals, and empty rows or columns.
- In the
"Totals & Filters" tab, you can specify whether you want to
display grand totals for rows, columns, or both.
- The "Layout & Format" tab allows you to customize the appearance of your pivot table, including gridlines, styles, and report filters.
7. Grouping data:
To analyze data at different levels of granularity, you can
use the grouping feature:
- Right-click on a
field containing dates (e.g., Product) and select "Group." This
allows you to group products.
- Similarly, you can group dates fields by right-clicking on the field and selecting "Group." This helps in analyzing data in ranges or specific intervals.
8. Conditional formatting:
Apply conditional formatting to highlight specific data
points or patterns in your pivot table:
- Select the range
of values in your pivot table.
- Go to the
"Home" tab, click on "Conditional Formatting," and choose
the desired formatting option. For example, you can highlight the top or bottom
values, apply color scales, data bars, or icon sets.
9. Refreshing data and updating the pivot table:
If your source data changes, make sure to refresh your pivot
table to reflect the latest information. Right-click on the pivot table and
select "Refresh." Additionally, if there are structural changes to
the data, such as adding or removing columns, you may need to update the pivot
table by going to the "PivotTable Analyze" tab and selecting
"Refresh" or "Change Data Source."
By incorporating these options and settings into your pivot
table usage, you can customize your analysis, format the data, and fine-tune
the appearance of your pivot table to meet specific requirements. The
flexibility and versatility of pivot tables allow you to dig deeper into your
data, uncover meaningful insights, and present them in a visually appealing and
informative manner.
In the next part we will cover some pro tips for advance users.
