Pivot tables are one of the most powerful tools in Microsoft Excel for data analysis. They can turn hundreds (or even thousands) of rows of spreadsheet data into meaningful, actionable insights. In this guide, I’ll show you how to create a pivot table in Excel, even if you’ve never done it before. We’ll begin with a simple data set and take it all the way to generating useful charts. By the end, you’ll know how to manipulate your data in smart ways—without the headache.

Here’s everything you need to know to get started.

Setting Up Your Data in Excel

Before we dive into creating a pivot table, you need to make sure your data is set up for success. In this example, we’ll use a sales data spreadsheet for Mel’s Fruit Company that includes information such as:

  • Location
  • Invoice Number
  • Customer Name
  • Item Description
  • Amount

Each of these categories has its own column, and we have roughly 200 rows of data to work with. If your spreadsheet is similar—organized with column headers and consistent data patterns—you’re ready to start.

Inserting a Pivot Table

To begin creating your pivot table:

  1. Click on the Insert tab in the Excel ribbon.
  2. Select Pivot Table.
  3. Choose From Table or Range.

The box that pops up will automatically include all of your data. If you see that your data has been pre-selected (without the header row), Excel is good to go.

Next, you’ll be asked where to place your pivot table. You can either place it in a new worksheet or an existing worksheet. I usually opt for a new worksheet, especially if I’m creating multiple charts later on.

Once you’ve made your choice, hit OK.

This will open a new sheet with a blank pivot table ready to be built in Microsoft Excel.

RELATED TOPIC: How to Create a Dynamic Organizational Chart in Excel

Setting Up Your Pivot Table

With your blank pivot table ready, take a look to the right. You’ll notice a section called PivotTable Fields. This is where you’ll structure your table based on what data you want to analyze.

Here’s a breakdown of how it works:

  • Filters: These help you narrow down your data. In this case, I’d like to filter by Item Description and Customer Name. You can drag those fields into the Filters box.
  • Rows: This is what will show up on the left-hand side of your pivot table. For our purposes, I’ll drag Location here. This will give us analysis based on the company’s different locations.
  • Values: This is where the real magic happens. This is the data you want to calculate or tally. In this case, I want to add up the Quantity and Amount, so I’ll drag both of these into the Values section.
  • Columns: You can add columns here if needed. In most cases (like this one), your values will determine the columns, so it’s not always necessary to add anything extra to Columns.

Now, if you want to move or reorder fields, it’s as simple as clicking and dragging to rearrange them. You’ll start to see your data populate in different configurations.

Dynamic Data Updates in Pivot Tables

Here’s one of the best parts about pivot tables in Microsoft Excel—if you add new data to the original spreadsheet, your pivot table can be updated to reflect those changes.

To see this in action:

  1. Add a new row to your original data.
  2. Return to the Data menu.
  3. Click Refresh All.

Your pivot table will automatically pull in the new data. This is especially helpful if you are working with ongoing data updates.

RELATED TOPIC: How to Perform Calculations in a Pivot Table in Excel

Fixing Blank Areas in Your Pivot Table

Sometimes, you might see blank rows or fields in your pivot table. These blanks could indicate missing or corrupted data.

One way to spot them in larger datasets is by keeping an eye on any “(blank)” fields that appear in your table. To fix the issue:

  1. Go back to your original data.
  2. Look for blank rows or missing fields and fix or delete them.
  3. Return to your pivot table and Refresh it.

This simple refresh will update your table, removing any of those potential mistakes like blank cells.

Customizing Pivot Table Properties

Now that the pivot table is up and running, you can dig into customization options.

To access more settings:

  1. Right-click on any field in your pivot table.
  2. Select Value Field Settings.

From here, you have options to change how the data shows up, like switching from Sum to Count or Average.

Most users stick with Sum, especially for financial data, since it’s typically what you’ll want to calculate—whether that’s sales amount or item quantity.

RELATED TOPIC: How to Create a Pivot Table from Multiple Workbooks in Excel

Creating Charts from Your Pivot Table

Once your pivot table is complete, you can unlock its potential further by turning it into a chart.

  1. Go to the Insert menu again and select Charts.
  2. You’ll be given a variety of chart choices—clustered column charts, bar charts, line charts, etc.

For this tutorial, I recommend starting with a clustered column chart because it’s one of the cleanest, most straightforward visualizations for comparing categories. But feel free to experiment with others.

After selecting your chart type, Microsoft Excel will generate the chart based on your pivot table data. You can modify the chart’s formatting by:

  • Adding a title.
  • Changing axis labels.
  • Adjusting the color scheme under Format Plot Area and Format Data Series.

For our example, let’s name the chart Sales Data 2023, and use blue tones to visually distinguish between the quantity and amount.

Customize Axis for Cleaner Charts

If you have large data ranges, particularly with sales amounts in the thousands, you’ll want to adjust the numbers showing on your vertical axis for readability.

To modify these settings:

  1. Right-click on the axis.
  2. Choose Format Axis.
  3. Adjust the Major Units and Minor Units under the Units section.

Changing the units will clean up large increments, making your chart easier to read.

If you want to take it a step further, applying a slight angle to your labels can avoid clutter. I find a -40° works well to orient data without crowding the horizontal space.

Syncing Data and Charts Together

Here’s a really cool aspect of using pivot charts: they stay synced with your pivot table.

If you filter your pivot table, the chart will automatically adjust. For example, if you’re looking at sales in the USA only, the chart will instantly limit itself to U.S. data.

Similarly, if additional sales data is added down the road, a simple Refresh All will update both your pivot table and your attached chart. This ensures your visualizations always stay current.

Here’s how you refresh:

  1. Go to Data > Refresh All.

Any changes you’ve made in the table will be reflected right in your chart.

RELATED TOPIC: How to Use the Quick Analysis Tool in Excel

Conclusion

Creating pivot tables in Excel doesn’t have to be intimidating. You now know how to set one up, update it with new information, customize its properties, and even translate it into a meaningful chart. With a little bit of practice, you’ll be generating pivot tables like a pro.

Excel’s pivot tables and charts make it easier to understand your data and share those insights with others. Dive in and start exploring!

If this guide helped you, feel free to share your thoughts in the comments, and don’t forget to subscribe for more Excel tips.

Ready to take your Excel skills further? Explore related Excel tutorials, or subscribe for weekly tips and tricks.

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR