{"id":416,"date":"2017-09-25T15:45:18","date_gmt":"2017-09-25T20:45:18","guid":{"rendered":"https:\/\/www.onlc.com\/blog\/?p=416"},"modified":"2017-09-25T15:45:18","modified_gmt":"2017-09-25T20:45:18","slug":"creating-excel-pivottables-analyze-data","status":"publish","type":"post","link":"https:\/\/www.onlc.com\/blog\/creating-excel-pivottables-analyze-data\/","title":{"rendered":"Creating Excel PivotTables to Analyze Data"},"content":{"rendered":"

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\u2019s look at how to create <\/span>Excel PivotTables<\/span><\/a> as well as how we can use them to greatly enhance and speed up data analysis.<\/span><\/p>\n

Creating a PivotTable<\/span><\/h2>\n

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. <\/span><\/p>\n

PivotTable Fields and Values<\/span><\/h2>\n

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.<\/span><\/p>\n

Rows<\/b><\/b><\/h3>\n

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.<\/p>\n

Values<\/b><\/b><\/h3>\n

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\u2019re 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.<\/p>\n

Columns\u00a0<\/b><\/b><\/h3>\n

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.<\/p>\n

Filters<\/b><\/b><\/h3>\n

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.<\/p>\n

PivotTable Data Types<\/span><\/h2>\n

The most useful data types in PivotTables can be text, numeric or DateTime fields. Columns containing mixed data type values can\u2019t 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.<\/span><\/p>\n