Excel Pivot Table Guide — Go from Beginner to Expert

In Excel, Pivot Tables shine when it comes to summarizing tables.

Without them, you cannot, in any other way, generate dynamic summary reports from raw data.

At first sight, they look very complicated.

But don’t worry, this first sight feeling about Pivot Tables is a false alarm.

My goal in this guide is to open your eyes to it, with these simple Pivot Table tutorials.

But before that, let’s get a glimpse of what it is all about.

What is Excel Pivot Table?

Excel Pivot table is a feature in Excel use to quickly summarize a long list of data from a database.

The amazing thing about it is how you can transform a long list of data into meaningful reports, and do it so quickly that you yourself will be amazed.

To understand the concept of a Pivot Table, you need to see one in action.

The screenshot below has data with not less than 60 rows of records in 7 different columns.

Click Here to download the Example Workbook

Normally, Pivot Tables manages hundreds and thousands of rows.

Since we are learning here, I’ve reduced the quantity of data so that you can have a clear picture of what is really happening.

See screenshot:

Now, let’s see how Excel Pivot Table can help generate reports based on the above data:

• Using the Order Date column: you can find out on which month your company made the most revenue as well as profit.
• Using the Customer column: you can find out who’s making the most purchases — whether new or existing customers.
• Using the Country column: you can easily determine where the majority of your customers reside.
• Using the Products column: you can also find out how well a particular product is selling.

All of the above will otherwise be a painstaking task without the use of Pivot Table.

Of course, there are a couple of functions you could use to build the above summary reports.

However, it may take you hours of tedious work instead of just a minute.

And sometimes, management may demand quick reports to make important decisions.

For instance, below are questions that management can request answers for:

• Which month has the most sales?
• What is the total revenue generated from Product D?
• What’s the №1 selling product?
• In which country has more of the company’s customers.
• How much profit has the company made by new customers in January?

The questions can go on and on.

But how quickly can you answer these questions?

In short, using a Pivot Table!

With this amazing tool, you can analyze large amounts of data, and look at the same information in several ways to spot important trends.

Without further ado, let’s see how you can create a Pivot Table from the above data.

How to Automatically create a Pivot Table in Excel

At this point, you’ve theoretically learned the role Pivot Tables play in generating summary reports.

It’s time to practically learn how to actually create one yourself.

You can use Excel recommended Pivot Tables to quickly create one.

You can also create one from scratch.

In this section, let’s see how you can automatically create a Pivot Table using the above data.

The steps below will guide you through the steps to creating a Pivot Table in Excel:

• Step #1: Select any cell within the data set and
• Step #2 Go to Insert → Tables → Recommended PivotTables

The Recommend PivotTable dialogue will appear with thumbnails showing some predefined PivotTables you can select from.

Let’s assume that you want to generate a report on how much profit the company made from each product.

For this task, you need to select the Sum of Profit by Products.

See screenshot:

All the above Recommended PivotTables are based on your actual data, and there’s a high possibility that one of these recommendations will be exactly what you need — or at least close to what you are looking for.

• Step #4: Click OK to create the Pivot table on a new worksheet.

When you select any cell in the pivot table, Excel will display the PivotTable fields sidebar (or task pane).

Using this sidebar, you can make changes to the layout of the Pivot Table.

We’ll look at more on that later.

This approach of creating an Excel Pivot Table is very handy.

However, none of the recommended PivotTables may appease you.

In such a case, you have two options.

Option #1: Use the above approach and select a recommended Pivot Table that’s close to what you want, and then use the PivotTable fields sidebar to modify it.

Option #2: Manually create PivotTable from scratch to fit your needs.

Option #1 still involves using the Recommended PivotTable which we already discussed above.

So, in the next section, let’s look at how you can manually create a Pivot Table in Excel.

How to Manually Create a Pivot Table in Excel

The steps below will help you to manually create a new pivot table from scratch:

• Step #1: Select any cell in your data table or range

If you plan to add more rows to your source data, you should consider using an official Excel table instead of using a normal range.

That way, when you add new rows to the source data and refresh the Pivot Table, Excel will automatically update the Pivot Table with the newly added records.

• Step #2: Go to Insert→Tables→PivotTable.

However, there may be another approach to this step, only if you’re using a table you created using Insert→Tables→Table.

Just select any cell in your table and go to Design→Tools→Summarize with PivotTable.

Whether you used the designed tab or the insert tab, both approaches will fire up the Create PivotTable window.

See screenshot:

The above window is a Create PivotTable wizard.

This wizard needs two key information from you: a reference to where your raw data is, and where you want the result of your Pivot Table to be.

Automatically, in the field where you need to select a table or range, Excel IntelliSense will guess the range or table of your data and highlight it.

• Step #4: Choose where you want the PivotTable to be placed.

Select the “New worksheet” option to create a new sheet for your pivot table.

This is usually the easiest option.

However, you can choose the “Existing worksheet” option to create your PivotTable in the same worksheet as the source data.

If you choose to create in the same worksheet as the source data, you’ll need to specify the cell reference for the top-left corner of the PivotTable.

Beware, if there’s any data below the top-left cell you specified, Excel may overwrite it when the need arises.

Usually, the best option is to use a separate worksheet for your PivotTables.

• Step #5: Click OK

After specifying what the wizard asked for, click the Ok button.

Excel will then create an empty PivotTable and display a PivotTable Fields task pane (or sidebar).

See screenshot:

• Step #6: Now build your Pivot Table with the four regions.

When you used the Recommended PivotTables to create a Pivot Table, your report will be ready as soon as you select one of the recommendations.

However, when you use the manual option (as in this option), You’ll be presented with a blank pivot table.

It is now left for you to actually built your Pivot Table.

To do that, simply drag columns from the Pivot Table Fields pane (or right sidebar), and drop them into one of the four boxes (or regions) below.

As you drop the columns into these boxes, Excel builds your Pivot Table, updating it as you add, remove, or rearrange columns.

See screenshot:

As seen in the above illustration, you can simply build your summary reports by dragging and dropping the columns in the boxes beneath.

Practice makes man perfect. Right?

So, play with it more by trying to generate different reports with the drag and drop tools on the sidebar.

You can try to answer the questions we stated earlier:

• Which month has the most sales/Revenue? — To generate this report, drag and drop the Order Date column into the “rows” region and the Revenue column into the “values” region. You can also click the checkbox of these two columns to insert.

• What’s the №1 selling product? — To generate this report, drag and drop the Products column into the “Rows” region and Revenue into the values region.

• In which country has more of the company’s sales?

• How much profit has the company made by new/existing customers in January?

Try things out yourself and you’ll get a good understanding of what is happening.

You can change the summary category, say, Revenue for a particular month to another variable such as Profit just by dragging some buttons in the Pivot Tables fields.

The four PIVOT table regions

After creating your Pivot Table, you’ll see the PivotTable Fields task pane (or sidebar) at the right-hand side.

At the bottom of this pane are four divided areas or regions.

To understand how PIVOT table works, you need to understand what all the four regions are and how they work.

— The Values Region can handle fields that you can count, find the average of, subtotals and display in your Pivot Table. When you add a field to the values region, Excel will automatically determine a type of calculation that summarizes the data from the field added. For instance, if the field contains numeric information, you can build averages or subtotals. But if the field contains text, Excel will count the number of all unique items in that field.

— The Rows Region allows you to group your data into separate categories, and assign the information of each group into a single row. For instance, if you drag the Months field into the rows’ region, Excel will group all the data in months, and summarize the records for each month into a single row.

— The Columns Region allows you to subgroup your data several ways at the same time, similar to the rows’ region. However, with the Columns’ region, the groups are shown in separate columns.

— The Filter Region help you to filter the information the Pivot Table displays. For example, if you want to display a summary of profits for products, and want to consider old customers only, you could drag the Customers field into the Filters region to be able to filter your data accordingly.

The more you play around with these four regions, the more you understand how the whole Pivot Table thing works.

Thank you very much for reading!