10 Helpful Excel PivotTable Tips for Quick and Efficient Data Analysis

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.

00 organized PivotTable source data
To create a PivotTable, just select any cell in the source data. Then, on the Insert tab of the ribbon, click the PivotTable button.

01 PivotTable button

In the Create PivotTable dialog box, make sure the range matches your data.

02 PivotTable dialogue box

Then click OK to place a PivotTable in a new worksheet.

03 new PivotTable

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.

04 source table

Next, click the Table button on the Insert tab of the ribbon.

05 table button

You’ll need to confirm the location of the data and indicate that it has headers.

06 select data range

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)

07 summarize with PivotTable

When you click OK, you’ll get a blank PivotTable linked to your table, ready to work with.

08 blank PivotTable

#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.

09 PivotTable 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.

SNAG-0479

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).

10 PivotTable refresh

Then delete the worksheet that contains the data: just right-click, and select Delete.

11 PivotTable 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.

12 table 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.

13 table by dept

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.

14 pivot group support

Then right-click one of the items and choose Group from the menu.

15 group 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.

16 marketing sales

The result is a PivotTable with two manual groups, as you see below.

17 manual groups


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.

18 age data

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.

19 too granular

To group results by age, right-click any value in the Age field and choose Group.

20 chose 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.

21 group by years

When you click OK, you’ll see your data neatly grouped by age at 10 year intervals.

22 group 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.

23 sum of sales

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”.

24 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.

25 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.

26 add a space

#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.

27 employees by dept

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.

28 adding fields

Now, right-click the field and change Show Values As from “No Calculation” to “% of Grand Total”.

29 percent of grand total

You’ll then see the second instance of the field expressed as a percentage of total.

30 percent final

Here we’ve tidied things up a bit by renaming the percentage field to “%” and adjusting the number format to show fewer decimals.

31 few 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.

32 general # 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.

33 value field settings

Then click the Number Format button and set the format as desired.

34 number format

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.

35 reformatted

#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:

36 engineers

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.

37 subtotals data

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

 

Show Comments

One Response

  1. nagaraju

Leave a Reply