How to Create a Pivot Table for Data Analysis in Microsoft Excel

Microsoft Excel’s pivot tables are a powerful tool you can use to compare large datasets.

You can use the pivot table as an interactive data summarization tool to automatically condense large datasets into a separate, concise table. You can use it to create an informative summary of a large dataset or make regional comparisons between brand sales.

Pivot tables do not have to be intimidating—far from it—and we are here to help you unlock one of the real hidden power tools of Excel.

What Is a Pivot Table?

A pivot table is a method for arranging large datasets into easier to manage statistics. You can use the pivot table feature to “pivot” your data to uncover new trends and links between data.

The primary method for data entry in Excel is a regular flat table, like so:

The flat table contains columns and rows containing data. You can discern some basic trends through the data, especially with a small dataset.

However, if you have a dataset containing thousands or even millions of entries, you cannot understand the data at a glance. In that instance, you can create a pivot table to sort and link the data using the categories available (typically taken from the data range names).

One of the best things about a pivot table is the speed with which you can rearrange data. You don’t have to understand complicated comparison formulas, and you don’t need to become an Excel pro to work with a pivot table. Better still, you can reset your pivot table whenever you want and begin afresh.

How to Create a Pivot Table in Excel

Microsoft Excel has an excellent integrated pivot table option. You should note that Excel is far from the only spreadsheet program that uses pivot tables. Microsoft Office alternatives such as Libre Office and Google’s G-Suite all have support for pivot tables in their spreadsheet programs.

Anyway, back to pivot tables in Excel. The following tutorial uses Microsoft Excel for Office 365. Furthermore, I’m using a generic generated dataset of UK names, addresses, and so on. If you would like to use the same practice data, the file is available for download below.

• Pivot Tables example data file via Media Fire

To create a pivot table, head to the Insert tab and select PivotTable. Select the table or data range you want to include in the pivot table. In the case of the example, the entire data range forms the selection.

As you can see, there are options to connect an external data source (i.e., MS Access), or place the pivot table within the existing worksheet. For the latter option, be sure to check that your new pivot table will not obscure or break your existing data (though you can always hit CTRL + Z to undo if something terrible does happen!).

Sorting Pivot Table Data

Once you hit OK, your pivot table will open in a new worksheet. The worksheet begins blank. On the right, you will see the PivotTable Fields panel. This panel contains the data ranges from the example dataset, such as names, addresses, sales, and so on.

From here, you have two options for adding data to your pivot table:

1. Drag and Drop: The pivot table fields listed on the right-hand side can be dragged into the four areas below (Filters, Columns, Rows, and Values). You can select specific data items that you wish to cross-reference against any number of differentials.
2. Add to Report: Clicking individual dataset fields will add them directly to the waiting report table. Use this method to rapidly build, analyze, collate, and modify the available data.

While both methods deliver similar outcomes, Add to Report allows you to simply select your preferred data field and marvel in wonder as your report table transforms. As a bonus, each time we click a new data field, it is automatically added to the area Excel deems correct, while reminding us that we cannot add a data’ field with more than 16384 items to the column area.’

Selecting Pivot Table Data Ranges

The pivot table example we are using is basic. It contains a few sample data points that are easy to understand. For this example, we’re going to look at regional sales of the various products sold.

From the PivotTable Fields list, select County, Sales Volume, Sales Total, and Product. The example pivot table should now look like this:

You can now glance at your pivot table data and analyze data for any trends. If you want to see an overview of each section, right-click and select Expand/Collapse > Collapse Entire Field.

Filtering Your Pivot Table Data

Feel free at this point to play around with the different fields, learn which varieties of data produce concise correlation, which data fields need tweaking to reveal their secrets and to comprehend the value actions of filter settings:

1. Hover over the desired data field, note the small black arrowhead to the right of the text
2. Selecting the black arrowhead reveals a drop-down ‘Filter’ menu – extremely useful for isolating data within a single field, or comparisons between similar data ranges across multiple data sources.
3. Select the County filter menu.
4. By default, all data ranges are selected. Unselect all (by hitting select all), followed by reselecting a few different counties. This isolates the data for these countries, filling our pivot table with concise, comparative data we are able to begin to analyze for potential trends.

Modifying the filters of each available field will almost always directly alter the data presented in the pivot table and is a sure-fire method of unlocking the true potential of your data.

Use Pivot Tables to Uncover Data Trends

You can use a pivot table to work your data into new angles, helping you to uncover new trends and statistics for your work, business, or otherwise. Pivot tables work best when they have a large amount of data to work with, multiple data fields, and some duplicate data fields, too.

That’s not to say you cannot use them with a smaller data sample. The example data for this article was just 500 rows and 14 columns, three of which were in use to manipulate the data.

If you’re looking to up your Excel powers, check out these Excel formulas that do amazing things!

Read the full article: How to Create a Pivot Table for Data Analysis in Microsoft Excel

Read Full Article