As Seen on TechCrunch: Finofo Raises $1.65M CAD!

Excel Guide

How to Create a Pivot Table in Excel

Pivot tables are one of the most powerful features in Excel. They allow you to quickly summarize large amounts of data in a way that is easy to understand and use. Pivot tables can be used to answer questions such as "what is the average sales for each product?" or "how many units of each product were sold in each region?" Creating a pivot table in Excel is easy. In this article, we'll show you how to create a pivot table in Excel, step-by-step. We'll also show you how to use pivot tables to answer some common business questions.

Step 1: Select your data

The first step to creating a pivot table is to select the data that you want to use. Pivot tables can be created from data in a table or from data in a range of cells. For this example, we'll use a table of sales data.

To select the data, click on the cell in the upper-left corner of the data (A1 in this example), and then drag the mouse down to the bottom-right corner of the data. All of the data should be highlighted.

Once the data is selected, click the "Insert" tab on the ribbon, and then click "PivotTable" in the Tables group. This will open the "Create PivotTable" dialog box.

Step 2: Choose where to put the pivot table

The next step is to choose where you want to put the pivot table. You can put the pivot table on a new worksheet or on an existing worksheet. For this example, we'll put the pivot table on a new worksheet.

To do this, make sure that the "New Worksheet" radio button is selected in the "Choose where you want the PivotTable report to be placed" section. Then, click the "OK" button.

Step 3: Choose what data to use in the pivot table

The next step is to choose which data to use in the pivot table. In the "Select a table or range" section, make sure that the "Table" radio button is selected. This will ensure that the pivot table includes all of the data in the selected range.

Once you've made sure that the "Table" radio button is selected, click the "OK" button.

Step 4: Choose what to put in the pivot table

The next step is to choose what to put in the pivot table. In the "Choose fields to add to report" section, you'll see a list of all of the columns in the selected data. For this example, we'll put the "Region" field in the Row Labels area, the "Product" field in the Column Labels area, and the "Sales" field in the Values area.

To do this, simply click on the field that you want to use, and then click the "Add" button. For example, to add the "Region" field, click on the "Region" field, and then click the "Add" button. Repeat this process for the "Product" and "Sales" fields.

Step 5: Choose how to summarize the data

The next step is to choose how to summarize the data. In the "Choose how to summarize the data" section, you can choose from a number of options, such as sum, average, count, etc. For this example, we'll choose to sum the data.

To do this, simply click on the "Sum" option, and then click the "OK" button.

Step 6: Format the pivot table

The last step is to format the pivot table. You can format the pivot table by selecting a cell in the pivot table, and then using the "Format" options on the ribbon. For example, you can use the "Number Format" options to format the data as currency, percentages, etc.

You can also add subtotals and grand totals to the pivot table. To do this, simply click on the "Subtotals" or "Grand Totals" options in the "Totals & Filters" group on the ribbon.

Using Pivot Tables to Answer Business Questions

Now that you know how to create a pivot table, let's look at how you can use pivot tables to answer some common business questions.

Question 1: What is the average sales for each product?

To answer this question, we'll need to create a pivot table with the "Product" field in the Row Labels area, the "Sales" field in the Values area, and the "Average" option selected in the "Choose how to summarize the data" section.

Question 2: How many units of each product were sold in each region?

To answer this question, we'll need to create a pivot table with the "Region" and "Product" fields in the Row Labels area, the "Units" field in the Values area, and the "Sum" option selected in the "Choose how to summarize the data" section.

Question 3: What is the total sales for each region?

To answer this question, we'll need to create a pivot table with the "Region" field in the Row Labels area, the "Sales" field in the Values area, and the "Sum" option selected in the "Choose how to summarize the data" section.