Excel is fantastic tool for number crunching. PivotTable, it is arguably the most powerful tool in Excel. There’s simply no better way to quickly make sense of a large set of data. And because PivotTables are extremely versatile, you can find opportunities to use them in any business setting. PivotTables are fast, flexible, and extremely accurate. Once you learn how they work, they will change the way you look at data analysis in Excel forever. Thus, learning PivotTables is a great career move, and this collection of tips will help make sure you’re getting the most out of PivotTables as you begin to use them to analyze data in Excel.
#1 You can make a PivotTable in about 15 seconds
Many people worry that creating a PivotTable is time-consuming, but if you have well-organized source data, you can create a PivotTable in a snap. Here, we have sales data for a speciality chocolate business. Note that each column has a unique name and there are no empty rows or columns.
To create a PivotTable, just select any cell in the source data. Then, on the Insert tab of the ribbon, click the PivotTable button.
In the Create PivotTable dialog box, make sure the range matches your data.
Then click OK to place a PivotTable in a new worksheet.
You’ll see the empty PivotTable appear in a new worksheet. Just add fields to start analyzing your data. Here we’ve added the field Product as a Row Label and the field Sales as a Value, which creates an instant breakdown of sales by product.
#2 You can use a table for your PivotTable
Tables and PivotTables are a great match. When you use a table as source data for a PivotTable, Excel will automatically expand and shrink the table as you add or remove data, so your PivotTable will always stay in sync with your data. To create a Table, select any cell in the data.
Next, click the Table button on the Insert tab of the ribbon.
You’ll need to confirm the location of the data and indicate that it has headers.
When you click OK, your table will be created. Note: as long as your data is in good shape, you can create a table in one step using the keyboard shortcut Ctrl-T. Now that you have a table, click the Summarize with PivotTable button (TableTools > Design)
When you click OK, you’ll get a blank PivotTable linked to your table, ready to work with.
#3 You can count almost anything with a PivotTable
You might think you have to be working with numbers to use a PivotTable, but by default, a PivotTable will count any text field. For example, suppose you have a list of employees and want to get a count by department.
Just create a PivotTable normally, then add Department as a Row Label, and the field First as a Value. Because First contains names (text), the PivotTable will count entries in that field.
As a bonus, this is a useful way to quickly get a list of every unique value that appears in a field. For example, this PivotTable clearly shows the 5 departments that appear in a list of 300 employees. It could easily do the same with a list of 30,000 employees.
#4 You can remove the data from a PivotTable
When you’ve created a PivotTable from data in the same worksheet, you can remove the data if you like and the PivotTable will continue to function. Each PivotTable has a pivot cache that contains an exact duplicate of the data used to create the PivotTable. Assuming your data is in its own worksheet, first refresh the PivotTable to make sure the pivot cache is up to date (PivotTable Tools > Refresh).
Then delete the worksheet that contains the data: just right-click, and select Delete.
After you remove the data, you can still work with the PivotTable normally. Here we’ve rearranged the PivotTable (after removing the data) to show a count of employees by first name.
#5 You can group a PivotTable manually
Although PivotTables can automatically group data in many ways, you can also group items manually. Here’s a PivotTable that shows a breakdown of employees by department.
Suppose you want to group the Engineering, Fulfillment, and Support departments into one group, and Sales and Marketing into another group? It’s easy. First, select each item in the first group, holding down the control key to allow multiple selections.
Then right-click one of the items and choose Group from the menu.
You’ll now have your first manual group, automatically named “Group1.”
To finish grouping, select Marketing and Sales in column B, and group them as above.
The result is a PivotTable with two manual groups, as you see below.
Also Read: How to Analyze Data Using Excel PivotTables
#6 You can group a PivotTable by numbers
One of the most powerful features of PivotTables is the ability to group data by numbers. You can use this feature to group by age range, price range, or any numerical range that makes sense in your data. For example, let’s assume you have a list of voting results that includes voter age, and you want to summarize the results by age group.
First, create your PivotTable normally. Add Age as a Row Label, Vote as a Column Label, and Name as a Value. In the example below, we also renamed Grand Total to Total. Although this PivotTable is interesting, it’s not very useful, since a summary by actual age is too granular.
To group results by age, right-click any value in the Age field and choose Group.
When the Grouping dialog box appears, enter any interval that makes sense in the “By:” input area. For this example, we’ll group by 10 years.
When you click OK, you’ll see your data neatly grouped by age at 10 year intervals.
#7 You can rename fields
When you add fields to a PivotTable, the PivotTable will display the name that appears in the source data. Value field names appear with “Sum of “ or “Count of” at the start.
This PivotTable summarizes sales by product. Note that the Sales field is named “Sum of Sales” by default.
However, you can easily change field names. Just select the field name in the PivotTable and type a new name. Below, we have renamed “Sum of Sales” to “New name”.
One quirk you may run into is that you can’t use the same field name that appears in the source data for a value field (i.e. you can’t use the name “Sales” for the field “Sales” when sales is a Value). If you try, Excel will complain that the field already exists.
As a simple workaround, just add a space at the end of the field name. At a glance, you can’t tell the difference, and Excel won’t complain.
#8 You can add a field more than once
It may seem counterintuitive, but you can add the same field to a PivotTable more than once. In fact, there are many situations where you’ll want to do just that. For example, here is a PivotTable that summarizes employees by department.
Suppose you want to see the breakdown by both a count and a percentage? This is easily done by adding the field again as a Value. Like the first instance of First, the second instance will be counted by default.
Now, right-click the field and change Show Values As from “No Calculation” to “% of Grand Total”.
You’ll then see the second instance of the field expressed as a percentage of total.
Here we’ve tidied things up a bit by renaming the percentage field to “%” and adjusting the number format to show fewer decimals.
#9 You can automatically format value fields
Any time you add a value field to a PivotTable, make sure you set the number format on the field itself. For example, this PivotTable shows a breakdown of sales by Region. By default, values in the Sales field appear in the General number format.
You could just select the cells in the PivotTable directly and apply a currency format. But a much better way to set the number format in a value field is to use field settings. To do this, first right-click on the Sales field and select Value Field Settings from the menu.
Then click the Number Format button and set the format as desired.
In the screen below, Sum of Sales has been formatted using Accounting format and zero decimals. By setting a number format on the field itself, the number format will be applied consistently to the entire PivotTable, no matter how much it changes.
#10 You can drill down to see the data behind any subtotal
Any time you see a subtotal or grand total in a PivotTable, you can easily get to the exact data that makes up that value using the “drill down” feature built in to all PivotTables. For example, suppose you want to see the data behind the subtotal of 50 Engineers in this PivotTable:
Just double click directly on the number 50, and Excel will add a new sheet to your workbook that contains the exact data used to calculate 50 engineers. In this case, this is the subset of employees in that are in the Engineering department.
Remember these tips and Pivot like a pro
So there you have it — 10 tips to get more out of the mighty PivotTable. Try these out on your own projects, so you become comfortable setting up and manipulating PivotTables. You’ll find that PivotTables have useful applications in many areas, including finance, sales, marketing, manufacturing, education, quality assurance, customer support, information technology, engineering, and more.
In addition, PivotTables are well-aligned with the increasing availability of high quality data. As access to data, especially so-called “big data”, becomes more prevalent, there will be a growing need for people with the skills to analyse this data quickly and accurately. By mastering PivotTables now, you’ll be in a great position to take advantage of new opportunities as they arise.
Also Read: Advanced PivotTables: Combining Data from Multiple Sheets
Courtesy: skilledup.com