When you have a massive Excel workbook overflowing with data, trying to make sense of it can feel like a tough task at times. Be it sales data, client names, product segments, dates, or geographical areas, valuable insights are hidden within all these elements. Here is where Excel pivot tables step in to extract data, which can help you avoid getting lost in endless rows and columns. Whether you are a business owner, startup founder, marketer, sales manager, or even a student, here’s how you can transform your data into actionable insights by using pivot tables.

What is a pivot table, and why should you create one?

Before we dive into pivot tables in Excel, let’s have a quick word about it. A pivot table is a powerful tool in Excel to summarize and analyze your vast database in different ways. You can basically pick different fields from your spreadsheet, create a separate table out of them, then analyze patterns, trends, and relationships in your data.

A pivot table is a must-have tool for power Excel users. It lets you see the bigger picture, where you can make sense of large amounts of information quickly and easily. If you are still of two minds about using a pivot table, check out the top reasons to use one.

• It lets you transform chaos into clarity by displaying key trends.
• It will help you uncover hidden insights.
• It will reveal critical answers to your questions.
• It provides the information you need to create dynamic reports.
• It ultimately saves you time when dealing with large databases.

Essential tips for data formatting

Before you create a database in your Excel workbook, keep several tips in mind which will help you get the desired results.

• You should use clean tabular data for best results.
• Give all your columns the required, relevant headers. Use a single row of unique, non-blank labels for each column.
• Make sure to organize your data down the columns under the relevant header, and not across rows.
• Format your data as an Excel table (more on that later).

Create a table and insert data

Now that you are aware of the basics of pivot tables, let’s create one. In the example below, we have created a database for salespeople with relevant details like store region, product, number of units sold, overall sales, and profit.

1. Open a new Excel workbook and create relevant columns with data.
2. Select Insert at the top and click Table (just a regular table first).
3. Click My table has headers and select OK.
4. You can format it using the Format as table menu to give it a colorful look.

Excel can recommend pivot tables

Once your dataset is ready, you have a couple of options for creating a pivot table. You can either use Excel’s recommended ones or insert a custom table with an even more specific range.

1. Go to your Excel database and select Insert at the top. Click Recommended PivotTables and check all the suggestions from the sidebar.
2. In our case, Excel displayed ten options, such as "Units Sold by Store Region," "Units Sold by Sales Person and Laptop," "Units Sold by Laptop and Store Region," "Units Sold by Sales Person and Store Region," and more.
3. There is an option to insert a pivot table on a new sheet or an existing one. We recommend adding pivot tables to another sheet to avoid any confusion.

As mentioned, you can also create a custom pivot chart based on specific conditions. In the example below, we will create a pivot table to see how many units each salesperson sold in a given month and how much sales revenue they generated.

1. Open your main database. Select Insert > Tables > PivotTable > From Table/Range.
2. Give your new sheet a relevant name, select New Worksheet, and click Ok.
3. Select the check mark beside the data points you want to examine, such as Month, Sales Person, Units Sold, and Sales.
4. Your pivot table is ready for analysis. Head to Insert > Charts and add one of the relevant charts to your Excel sheet. Let’s add a line chart here.
5. Now you have a clear view of how each salesperson did in a specific month, with all variables included. You can right-click on a chart to change the fill and outline color to give it a unique look.

You can now click on Month, Sales Person, or Sales at the bottom and check insights for each criterion. For example, you can expand Sales Person and select only Jim to see how he performs every month. Check out the updated graph below.

You can right-click on any column to give it a custom name, generate average results (for specific data types), and make other changes.

Other pivot table tips

Add a timeline slicer to a pivot table

Timeline slicers are quite handy when you have pivot tables that can utilize dates to filter data. You need to have a pivot table that has a date range. We have created a pivot table based on the number of units sold on a specific date. Let's walk through how to add a timeline slicer.

1. Once your pivot table is ready, click PivotTable Analyze at the top. Select Insert Timeline.
2. Click Date and select OK.
3. You'll be able to see your added timeline slicer. You can then select multiple date ranges and glance over the relevant data in no time.

You can right-click on it, and you'll have the option to customize the appearance of your timeline slicer if you'd like.

Refresh or change the data source of your pivot table

If you have updated the original database with new details, you need to refresh the generated pivot tables. You can open a pivot table sheet, select PivotChart Analyze at the top, and select Refresh to update the data.

You can also expand Change Data Source, pick a different database (table), and replace the existing pivot table with a new one.

Tame your data

In the examples here, we have highlighted some useful pivot table functions in Excel. The secret to becoming a true pivot table pro is practice. You need to experiment with different datasets, explore various features, and get creative with your data to unlock its full potential. You can also get valuable insights from your massive database using Copilot. Check out our dedicated post to up your Excel game with Microsoft’s AI assistant.