Pivot tables are tabular representations of data sets. They typically summarize data in an accessible way and also come with additional analysis features such as count, total, or average calculations. PivotTable (one word) is Microsoft’s trademarked feature and is among Excel’s most powerful features as it tackles several problems of handling data at the same time.
First, PivotTables organize your data set by placing everything in a nice table format with bold column headers, and you can even add alternating row colors. We think this makes reading tables much easier.
Second, they give you many capabilities to analyze data quickly. On top of automatically adding slicers to every row, PivotTables also come with expressions and other basic operators to extract information. You can easily find the totals or counts of all columns or rows in no time.
PivotTables find extensive use in all database operations. Let’s assume we have a spreadsheet full of employee data, and we want to analyze the base salary rates. In this example, we’re going to find average base salaries by department to identify which ones rely more on additional compensation. In this guide, we’ll be using this data set to walk you through how to use PivotTables,
You could very well calculate all of this by using the Subtotal feature, or by typing a few AVERAGEIF functions. Subtotal feature allows user to summarize data by grouping. However, feature directly works with data itself, and can cause some headache. The data needs to be sorted by group (in our case, department), a data column can only be used in one result, and the structure of your data will be manipulated by Excel to add subtotals. Using functions and formatting, on the other hand, will require you to create distinct items lists for each group field by manually (again, department in our example). A PivotTable can instantly calculate and summarize the data in a way that's both easy to read and manipulate.
Begin by selecting your data table or the range containing the data. If your data is in tabular form, Excel can identify your entire table even if you select a single cell. Then, go to the Insert ribbon and select Pivot Table.
The Create PivotTable dialog box will walk you through table creation. Here, choose from settings whether you want to create the Pivot Table on a new worksheet or a specific range. You can now choose which fields to place on your table. There are 2 ways to do this. First is by checking the boxes next to the corresponding fields. Excel automatically determines where to put the data between ROWS and VALUES, based on data types (i.e. Text/Date values to ROWS and numeric values to VALUES). The second method, as shown below, is dragging and dropping the data into columns.
Excel will automatically pick an Expression for your data. To select a different expression, click the data field under Pivot Table menu, and select Value Field Settings.
Here, you can change the field name, expression, or its formatting. We used Average for expression and method. and Currency for the number format. When you’re done, click OK to save your settings.
Once the PivotTable is in place, you will see how easy it is to manipulate the data using options from the side menu. For instance, we can calculate “Average Base Salaries by Year” by replacing the Department field with Year.
Now, let’s try creating a tree layout for “Maximum Base Salaries of Departments by Year”. Add Year first, and then add Department into the ROWS section. To get the same results as below, also change the Base Salary expression to Maximum value.
Lastly, let’s display the total number of employees and calculate the “Standard Deviation of Base Salaries by State”. A count of the field Employee Names will give the headcount (can be done from the Value Field Settings). Standard deviation of salaries can be calculated by PowerPivot by again selecting it from the Value Field Settings menu when you click the data name.
Excel’s PivotTables give you pretty much all the tools you might need to transform a data set, and analyze it. By using this feature, data can be easily manipulated and its properties can be identified without additional work.
