Creating Excel PivotTables to Analyze Data

PivotTables are a powerful feature included in Microsoft Excel that allows you to quickly extract the meaning from very large data sets. They do this by including tools and functions that analyze large amounts of data. Let’s look at how to create Excel PivotTables as well as how we can use them to greatly enhance and speed up data analysis.

Creating a PivotTable

You will generally want to create a PivotTable in a new worksheet inside the spreadsheet containing your data set. You can do this by clicking on any cell inside your data set, but an easy starting point can be cell A1. Click the Insert menu on the toolbar at the top of the screen. Next, click the PivotTable menu item to start the PivotTable designer. The PivotTable designer will provide you a list of all of the columns in your data set.

PivotTable Fields and Values

Below the list of fields, you will have four boxes that you can drag these fields to in order to build your PivotTable. The four areas are rows, values, columns and filters. The functionality that is performed by dropping fields into these boxes is different for each box.

Rows

When you drop a field into the Rows box, this becomes the first column in your PivotTable. It contains all of the unique values contained in that field.

Values

Dragging a field into this box will count or sum all of the values in that field. If the field is numeric, then the values will be summed. If they are blank or text-based, then the count will be calculated for that field. The calculation can be changed by selecting other functions later on if these are not the desired numbers you’re looking for. The values will also be grouped according to the rows you have selected. So, now you have performed a calculation on a field and it is grouped into categories that correspond to the values in another field. This is a very powerful query that was performed in a matter of seconds.

Columns 

The columns box is similar to the Rows box, but it lists the unique values of a field across the top of the worksheet instead of down the left side like Rows do. By using both the Rows and Columns feature you can further segment your data because now you have two related groupings. For example, you could summarize the total population of states by county. This is a perfect data set with which to build a graph because now you have two axes of data that can be graphed.

Filters

The last query method is Filters. This lets you further constrain the data to present an exact result. Using the example above from the Columns section, you could easily in a matter of minutes report the total population by county of each state for a specific year by specifying the year contained in a field that contains the date when the data was collected. You simply drag that field into the Filters box and then select which value you wish to summarize. In this case, it would be the specific year you are interested in knowing the total population of each count in each state.

PivotTable Data Types

The most useful data types in PivotTables can be text, numeric or DateTime fields. Columns containing mixed data type values can’t be summed in the Values box when creating your PivotTable because all of the values have to be numeric. The organization of your data is very important. Here are some data organization tips.

• All fields need to have clearly defined data types.
• They also need to have unique, easily understood field titles or headers.
• Avoid blank cells so your formulas won’t get corrupted.
• Remove any formulas in your columns as they could be included in any field calculations.

PivotTables Versus Spreadsheets

Spreadsheets are containers for data. PivotTables can be used to summarize that data. It’s better to have clean, organized data in your spreadsheet so that the PivotTable will have a pure data set on which to perform its magic. Spreadsheets are great for performing calculations and obtaining facts and figures as well as performing statistical analysis and charting. PivotTables help you quickly draw out the meaning of the data and can be easily manipulated to extract different pieces of information from larger data sets.

A simple ad-hoc PivotTable can be created quite easily by someone with only minimal knowledge of Excel and the basic organization of data. However, if you really want to become an expert at using PivotTables, you can obtain training. There’s no easier or better way to get that valuable training than using an online training provider such as ONLC. Contact us today to begin your voyage to becoming a data analysis wizard.