How to Create Pivot Tables and Charts in Google Sheets (Sales Analysis Example)

Pivot tables are one of the most useful tools in Google Sheets for summarizing and analyzing data. If you’ve ever worked with a dataset that feels too large or repetitive, pivot tables help you make sense of it quickly.

In this tutorial, I’ll show you how to use pivot tables and pivot charts in Google Sheets to analyze a sales dataset. We’ll focus on a simple but practical example: total sales by category.

By the end of this guide, you’ll be confident creating your own pivot tables and charts for basic data analysis.

You can follow along by [downloading the practice dataset here]

📌 If you prefer learning by watching, you can check out the video version of this tutorial below:

Understanding the Dataset

Before creating a pivot table, it’s important to understand what we’re working with. In this dataset, the key columns we’re interested in are:

• Category

• Sales

If you look closely at the Category column, you’ll notice that the same categories appear multiple times. For example, Furniture shows up more than once because products may have been sold at different times.

What are we trying to do? 

Our goal is to:

1. Group all similar categories together.

2. Calculate the total sales for each category (Furniture, Office Supplies, Electronics).

To do this efficiently, we’ll use a pivot table.

---

What Is a Pivot Table?

A pivot table is simply a summary of a larger dataset. Instead of working with hundreds (or thousands) of rows, a pivot table allows you to:

• Group similar data.

• Perform calculations (like totals or averages).

• View insights without changing the original dataset.

Think of it as a clean, organized snapshot of your data.

---

How to Create a Pivot Table in Google Sheets

Step 1: Insert a Pivot Table

You can either highlight the entire dataset or click inside any cell within the dataset. Then:

1. Go to Insert.

2. Click Pivot table.

3. Choose New sheet. (I usually select a new sheet because it keeps things clean and reduces clutter).

Step 2: Set Up the Pivot Table

Once the pivot table is created, you’ll see the Pivot table editor on the right. For this analysis, we’ll use Rows and Values.

• Add Categories to Rows: Click Add under Rows and select Category. This groups all identical categories together.

• Add Sales to Values: Click Add under Values and select Sales. By default, Google Sheets calculates the SUM, which is exactly what we want.

Step 3: Remove Grand Totals

To make the table easier to read, uncheck Show totals under the Category section in the editor. This gives us a cleaner summary in our pivot table.

---

Creating a Pivot Chart

Now that the pivot table is ready, we can visualize the data.

Step 1: Insert the Chart

Click anywhere inside the pivot table and go to Insert → Chart. A chart will appear automatically.

Step 2: Choose the Right Chart Type

If Google doesn't choose a column chart by default:

1. Open the Chart editor on the right.

2. Under Chart type, select Column chart.

Understanding the Chart:

• Horizontal axis (X-axis): Categories

• Vertical axis (Y-axis): Total sales

---

Interpreting and Formatting the Chart

The height of each column shows the total sales, helping us quickly spot the highest and lowest performers. To make this even clearer, we’ll add Data Labels.

Steps to Add Data Labels

1. In the Chart editor, go to Customize.

2. Click Series.

3. Scroll down and check Data labels.

Format the Numbers: To make the values easier to read for an audience, change the number format in your pivot table to Currency. The chart will update automatically to show dollar signs.

---

Key Findings: Total Sales by Category

From our chart, we can see that:

• Furniture has the highest total sales: 165,770

• Electronics has the lowest total sales: 160,398

Cleaning Up: To make the chart look professional:

• Change the chart title to “Total Sales by Category”.

• Remove unnecessary axis titles to save space.

• Center and bold the chart title.

---

Conclusion

In this tutorial, you learned how to move from raw numbers to clear, meaningful analysis. We covered how to:

• Define a clear analysis goal from a raw sales dataset.

• Summarize data efficiently using pivot tables.

• Master data storytelling by visualizing results with pivot charts.

• Improve presentation by interpreting results and formatting for clarity.

Choosing a column chart over a pie chart, for example, made it much easier to compare our categories at a glance. These small technical choices are what separate a basic spreadsheet user from a true Data Analyst.

Ready to try it yourself? [Access the dataset here] and start building your own pivot tables.

👉 Your Next Step: Take the Challenge!

If you’ve mastered total sales by category, try using this same dataset to answer these three business questions:

1. Regional Performance: Create a pivot table showing Total Profit by Region. Which region is the most profitable?

2. Efficiency Check: Find the Average Discount given per Customer Segment. Are we giving too much away to one specific group?

3. Product Trends: Build a Line Chart showing Sales over Time (Date) to see which months had the biggest spikes.

I'd love to hear from you!

• Did you manage to find the most profitable region?

• Do you want me to make a follow-up video on how to calculate "Profit Margins"?

• Are you stuck on a specific step or feeling like a Pivot Table pro?

If you found this guide helpful:

• Watch the full video tutorial above for more detail.

• Subscribe to my YouTube channel for beginner-friendly data tips.

• Drop a comment below with your progress or any questions you have. I'm here to help you get the most out of your data!

Thanks for reading, and happy analyzing! 📊