How to Edit a PivotTable in Microsoft Excel

Microsoft Excel is one of the most powerful and widely-used tools for managing, analyzing, and visualizing data. Whether you’re keeping track of expenses, analyzing sales, or preparing detailed reports, Excel provides various features that make handling large data sets more manageable. Among its many tools, PivotTables stand out as an essential feature for summarizing and analyzing complex data quickly and effectively.

What is a PivotTable?

A PivotTable in Excel is a dynamic tool that allows users to extract valuable insights from large datasets. By arranging data into meaningful summaries, PivotTables help users visualize patterns and trends. Whether you’re analyzing sales, profit margins, or customer demographics, PivotTables enable you to pivot data by rows, columns, filters, and values to get the most relevant information for your decision-making process.

In this blog, we will explain how to create and edit a PivotTable in Microsoft Excel using a practical example from a toy company.

How to Create a PivotTable in Excel

Let’s say you manage the sales data of a toy company. You have data on various products, including the total number of sales, number of returns, discounts on each product, and the profitability of each item. Here’s a step-by-step guide to creating a PivotTable based on this data.

Prepare the Data

Here’s an example of what your dataset might look like:

Product	Unit Price	Total Sales	Cost of Manufacturing	Marketing Expense	Total Revenue (USD)	Total Cost (USD)	Profit (USD)
Teddy Bear	20	500	5000	1000	10,000	6,000	4,000
Toy Car	15	350	3000	700	5,250	3,700	1,550
Doll House	50	200	4000	1200	10,000	5,200	4,800
Action Figure	25	400	6000	800	10,000	6,800	3,200
Building Blocks	30	300	4500	600	9,000	5,100	3,900

Create a PivotTable

a) Select the Range:

Highlight the range of data you want to analyze, including the new columns: Product, Unit Price, Total Sales, Total Cost of Manufacturing, Marketing Expense, Total Revenue, Total Cost, and Profit.

b) Insert PivotTable:

• Click on the Insert tab in Excel.
• Select PivotTable from the options.
• Choose where you want the PivotTable to appear: on a new sheet or an existing one.
• Click OK. A blank PivotTable field list will appear on the right side of the worksheet.

Building the PivotTable

a) Configure Fields:

• Drag the Product field into the Rows section.
• Drag Total Sales, Total Revenue, Total Cost, and Profit fields into the Values section.
  

Analyze by Categories:

If you want to explore profit margins or other aspects, you might want to include Unit Price or Marketing Expense. Drag these fields into the Values section if needed.

Summarize Data:

This setup will provide a summarized view of each product’s performance, including sales, revenue, costs, and profit, making it easy to compare different products and analyze profitability.

How to Edit a PivotTable

With your PivotTable set up, editing it to better suit new data or insights is crucial. Let’s continue with our toy company example and explore how to adjust your PivotTable.

Adding or Removing Fields

Add New Fields:

• To include additional data, such as analyzing profit margins or marketing expenses, go to the PivotTable Field List.
• Drag the Unit Price or Marketing Expense field into the Values section or any other relevant section to include these metrics in your analysis.

Remove Unnecessary Fields:

• To remove fields, such as Total Cost, if it’s no longer needed, simply uncheck the box next to Total Cost in the field list. The PivotTable will automatically update to reflect this change.

Changing the Calculation Type

Adjust Calculation Types:

• By default, PivotTables sum numerical data. To calculate averages of percentages, right-click any value in the PivotTable (e.g., Profit) and select Value Field Settings.
• In the pop-up window, choose the desired calculation type, such as Average or Percentage of Total, to get different insights.

Sorting and Filtering Data

Sort Data:

• To identify which product generated the highest profit, click the drop-down arrow next to the Product field.
• Choose Sort Largest to Smallest under the Profit column.

Filter Data:

• Apply filters to show specific data ranges or products. For example, to view only products with high marketing expenses, drag Marketing Expense into the Filters section and set your filter criteria.

Refreshing the PivotTable

Update Data:

• If your source data changes (e.g., additional sales data), click anywhere in the PivotTable.
• Go to the PivotTable Analyze tab and select Refresh to update the table with the latest data.

Formatting the PivotTable

Format Values:

• To make your PivotTable more readable, format the values. Change the number format of the values to currency for better clarity.
• Apply PivotTable styles by highlighting rows, adding borders, or using other formatting options to enhance visual appeal.

Conclusion

PivotTables are an incredibly powerful feature in Excel that allow you to analyze and summarize large datasets with ease. Knowing how to create and edit a PivotTable will make it much easier to draw insights and present data in a meaningful way. From sales figures to profit margins, you can use PivotTables to answer important business questions quickly and efficiently.

By following the steps above, you can create a PivotTable using data from your own business or projects, edit it as your needs evolve, and make the most of this versatile tool in Excel. With practice, you’ll be able to manipulate your data effortlessly and make informed decisions based on your findings.