What does a button indicate to the left of a row heading in an outline?

Pivot tables are a reporting engine built into Excel. They are the single best tool in Excel for analyzing datawithout formulas. You can create a basic pivot table in about one minute, and begin interactively exploring your data. Below are more than 20 tips for getting the virtually from this flexible and powerful tool.

1. You lot can build a pivot table in about one minute

Many people think edifice a pivot table is complicated and time-consuming, but it's but non truthful. Compared to the fourth dimension it would take you to build an equivalent report manually, pivot tables are incredibly fast. If you have well-structured source data, you can create a pivot table in less than a minute. Start by selecting any cell in the source information:

Example source data

Adjacent, follow these four steps:

1. On the Insert tab of the ribbon, click the PivotTable button
2. In the Create PivotTable dialog box, bank check the data and click OK
3. Drag a "label" field into the Row Labels area (e.chiliad. customer)
4. Drag a numeric field into the Values area (e.one thousand. sales)

A bones pivot table in nearly 30 seconds

The pivot table higher up shows total sales by production, simply you can can hands rearrange fields to show total sales by region, by category, past month, and so on. Watch the video below for a quick demonstration:

Video: How to speedily create a pivot table

two. Make clean your source data

To minimize problems down the road, brand sure your information is in good shape. Source data should have no blank rows or columns, and no subtotals. Each column should have a unique name (on one row but) and represent a field for each row/record in the information:

Perfect data for a pivot table!

You might sometimes need to add together missing information. See this video for tips:

Video: How to quickly make full in missing data

3. Count the data starting time

When yous first create a pivot tabular array, use it to generate a uncomplicated count starting time to make sure the pivot table is processing the information as you expect. To practise this, but add any text field equally a Value field. You'll see a very small pivot table that displays the full record count, that is, the total number of rows in your data. If this number makes sense to you lot, yous're practiced to go. If the number doesn't make sense to you, it's possible the pivot table is non reading the data correctly or that the data has not been defined correctly.

300 first names ways we have 300 employees. Cheque.

4. Plan before you build

Although it'southward a lot of fun dragging fields effectually a pin table, and watching Excel churn out yet another unusual representation of the data, you can observe yourself going downwardly a lot of unproductive rabbit holes very easily. An hour later, it's non so fun anymore. Before you showtime building, jot down what you are trying to measure or understand, and sketch out a few uncomplicated reports on a notepad. These simple notes will help guide y'all through the huge number of choices you have at your disposal. Keep things simple, and focus on the questions y'all need to answer.

five. Use a table for your data to create a "dynamic range"

If you utilise an Excel Tabular array for the source data of your pin table, you get a very overnice benefit: your data range becomes "dynamic". A dynamic range will automatically expand and shrink the table as you lot add or remove data, then won't have to worry that the pivot table is missing the latest data. When you use a Table for your pin table, the pivot table volition always be in sync with your data.

To use a Tabular array for your pivot table:

1. Select any cell in the data use the keyboard shortcut Ctrl-T to create a Table
2. Click the Summarize with PivotTable push (TableTools > Design)
3. Build your pivot table normally
4. Profit: information you lot add together to your Table will automatically appear in your Pivot table on refresh

Video: Employ a tabular array for your adjacent pivot table

Creating a elementary Table from the information using (Ctrl-T)

Now that we have a table, we tin can use Summarize with a  Pivot Tabular array

Still need inspiration on why you should learn pivot tables? See my personal story.

half-dozen. Use a pin table to count things

By default, a Pin Table will count any text field. This can be a really handy feature in a lot of general business situations. For example, suppose you have a list of employees and desire to get a count by department? To become a breakdown by department,  follow these steps:

1. Create a pivot tabular array usually
2. Add the Department as a Row Label
3. Add together the employee Name field as a Value
4. The pivot table will display a count of employee by Department

Employee breakdown by department

7. Show totals as a percentage

In many pivot tables, yous'll desire to show a percentage rather than a count. For example, mayhap you desire to prove a breakdown of sales by production. But, rather than bear witness the total sales for each production, yous want to show sales every bit a percentage of the total sales. Bold you lot accept a field called Sales in your data, just follow these steps:

1. Add Product to the pivot table equally a Row Label
2. Add Sales to the pin table as a Value
3. Right-click the Sales field, and fix "Show Values As" to "% of G Total"

Run across the tip beneath "Add a field more once to a pivot tabular array" to learn how to bear witness total sales and sales every bit a percent of total at the same fourth dimension.

Irresolute value display to % of total

Sum of employees displayed equally % of total

eight. Use a pivot table to build a list of unique values

Because pivot tables summarize data, they can exist used to find unique values in a field. This is a practiced way to quickly meet all the values that announced in a field and as well find typos, and other inconsistencies. For example, suppose you have sales data and you want to see a listing of every product that was sold. To create a product list:

1. Create a pivot tabular array normally
2. Add together the Product equally a Row Label
3. Add any other text field (category, customer, etc) as a Value
4. The pivot table volition bear witness a list of all products that appear in the sales data

Every production that appears in the data is listed (including a typo)

9. Create a self-contained pivot table

When you've created a pin tabular array from data in the same worksheet, you can remove the information if you like and the pivot tabular array will continue to operate normally. This is because a pivot table has a pivot cache that contains an exact duplicate of the data used to create the pivot table.

1. Refresh the pivot table to ensure cache is up to engagement (PivotTable Tools > Refresh)
2. Delete the worksheet that contains the data
3. Utilize your pivot table commonly

Video: How to brand a self-contained pivot table

ten. Group a pivot table manually

Although pin tables automatically group data in many ways, you can also group items manually into your own custom groups. For case, assume you lot have a pin table that shows a breakdown of employees by section. Suppose y'all want to further group the Applied science, Fulfillment, and Support departments into Group 1, and Sales and Marketing into Group ii. Group 1 and Grouping ii don't appear in the data, they are your ain custom groups. To group the pin table into the ad hoc groups, Group ane and Grouping two:

1. Control-click to select each item in the first grouping
2. Right-click one of the items and choose Grouping from the menu
3. Excel creates a new group, "Group1"
4. Select Marketing and Sales in column B, and group every bit to a higher place
5. Excel creates another group, "Group2"

Starting to group manually

Half way through transmission grouping - Grouping 1 is washed

Finished group manually

11. Grouping numeric data into ranges

One of the nearly interesting and powerful features that every pivot table has is the ability to group numeric data into ranges or buckets. For example, assume you lot have a listing of voting results that includes voter historic period, and you desire to summarize the results by age grouping:

1. Create your pivot table ordinarily
2. Add together Age as a Row Label, Vote as a Column Label, and Name as a Value
3. Right-click any value in the Historic period field and cull Grouping
4. Enter ten as the interval in the "By:" input area
5. When y'all click OK, y'all'll see the voting data grouped by historic period into 10-year buckets

Video: How to grouping a pivot table by age range

The source data for voting results

Grouping the historic period field into 10 year buckets

Done group voting results by age range

12. Rename fields for better readability

When you lot add fields to a pivot table, the pin table will brandish the proper name that appears in the source information. Value field names will appear with "Sum of " or "Count of" when they are added to a pivot table. For instance, you'll come across Sum of Sales, Count of Region, and and then on. Notwithstanding, you tin can simply overwrite this name with your own. Just select the prison cell that contains the field yous desire to rename and type a new name.

Rename a field past typing over the original name

13. Add together a space to field names when Excel complains

When you try to rename fields, you lot might run into a problem if you endeavor to employ exactly the same field name that appears in the data. For case, suppose you have a field chosen Sales in your source data. Every bit a value field, it appears equally Sum of Sales, but (sensibly) you want it to say Sales. However, when you try to apply Sales, Excel complains that the field already exists, and throws a "PivotTable field proper noun already exists" error message.

Excel doesn't like your new field name

Equally a simple workaround, just add a space to the end of your new field name. Yous can't come across a difference, and Excel won't complain.

Adding a space to the name avoids the trouble

xiv. Add a field more than once to a pivot tabular array

There are many situations when it makes sense to add the same field to a pivot table more than once. Information technology may seem odd, but you can indeed add the same field to a pivot table more than than once. For example suppose you have a pivot tabular array that shows a count of employees past department.

The count works fine, just y'all also want to prove the count every bit a pct of total employees. In this case, the simplest solution is to add together the same field twice every bit a Value field:

1. Add a text field to the Value area (eastward.g. Outset proper noun, Proper name, etc.)
2. By default, you'll become a count for text fields
3. Add the same field again to the Value area
4. Right-click the second instance, and change Bear witness Values As to "% of Thousand Total"
5. Rename both fields as as you wish

Setting a field to show percentage of total

The Name field has been added twice

xv. Automatically format all value fields

Any time you add a numeric field as a Value in a pivot table, y'all should prepare the number format directly on the field. You lot may exist tempted to format the values you see in the pivot table directly, but this is not a adept idea, because it's not reliable as the pivot table changes. Setting the format directly on the field will ensure that the field is displayed using the format you want, no matter how big or small the pivot table becomes.

For example, assume a pivot table that shows a breakdown of sales by Region. When yous first add together the Sales field to the pivot tabular array, it will be displayed in Full general number format, since information technology's a numeric field. To apply the Accounting number format to the field itself:

1. Right-click on the Sales field and select Value Field Settings from the bill of fare
2. Click the Number Format push in the Value field settings dialog that appears
3. Ready the format to Accounting and click OK to exit

Setting format directly on a value field

16. Drill down to see (or extract) the information behind whatever total

Whenever you encounter a full displayed in a pin tabular array, yous tin easily see and excerpt the data that makes upward the total by "drilling down". For example, assume you are looking at a pivot table that shows employee count by section. Yous can encounter that there are 50 employees in the Engineering department, simply you want to see the actual names. To encounter the l people that make up this number, double-click straight on the number 50 and Excel will add a new sheet to your workbook that contains the exact data used to calculate 50 engineers. You can apply this same approach to see and extract data backside totals wherever you lot encounter them in a pin table.

Double click a total to "drill downwardly"

The l Enginners, extracted into a new canvas automatically

17. Clone your pivot tables when you need another view

Once you lot have one pin table prepare up, you lot might want to see a unlike view of the same data. You could of grade simply rearrange your existing pivot tabular array to create the new view. Merely if you lot're building a written report that you lot plan to employ and update on an on-going basis, the easiest thing to practice is clone an existing pivot table, and then that both views of the data are e'er available.

There are two piece of cake ways to clone a pivot table. The first style involved duplicating the worksheet that holds the pivot table. If yous accept a pivot table prepare in worksheet with a title, etc., you can just right click the worksheet tab to copy the worksheet into the aforementioned workbook. Another way to clone a pivot table is to copy the pivot table, and paste it somewhere else. Using these approaches, you can make every bit many copies equally you like.

When you clone a pivot tabular array this manner, both pin tables share the aforementioned pivot cache. This means that when you refresh whatsoever one of the clones (or the original) all of the related pivot tables will be refreshed.

Video: How to clone a pivot table

18. Unclone a pivot table to refresh independently

After you've cloned a pin tabular array, you might run into a state of affairs where you lot really don't want the clone to be linked to the same pivot cache equally the original. A common example is afterward you've grouped a date field in ane pivot table, refresh, and discover that you've as well accidentally grouped the same date field in another pivot table that yous didn't intend to change. When pin tables share the aforementioned pin enshroud, they also share field group equally well.

Hither's one way to unclone a pivot table, that is, unlink it from the pin cache it shares with other pivot tables in the aforementioned worksheet:

1. Cut the unabridged pivot table to the clipboard
2. Paste the pivot table into a brand new workbook
3. Refresh the pivot table
4. Copy it again to the clipboard
5. Paste information technology back into the original workbook
6. Discard the temporary workbook

Your pivot table volition now utilize it's own pivot cache and will not refresh with the other pin table(s) in the workbook, or share the same field grouping.

19. Get rid of useless headings

The default layout for new pivot tables is the Compact layout. This layout volition brandish "Row Labels" and "Column Labels" as headings in the pivot tabular array. These aren't the well-nigh intuitive headings, especially for people that don't often use pivot tables. An easy way to get rid of these odd headings is to switch the pivot table layout from Compact to Outline or Tabular layout. This will cause the pivot table to display the actual field names every bit headings in the pivot tabular array, which is much more sensible. To get rid of these labels birthday, await for a button called Field Headers on the Analyze tab of the Pivot Table Tools ribbon. Clicking this button will disable headings completely.

Note the useless and disruptive field headings

Switching the layout from Compact to Outline

Field headings in Outline layout are much more sensible

xx. Add a footling white infinite around your pivot tables

This is just a simple blueprint tip. All good designers know that a pleasing blueprint requires a little white space. White space but means empty space set aside to give the layout breathing room. After you create a pin table, insert an extra column to the left and an actress row or two at the top. This will give your pivot table some breathing room and create a better looking layout. In virtually cases, I likewise recommend that you turn off gridlines on the worksheet. The pivot table itself will nowadays a strong visual filigree, and then the gridlines outside the pivot table are unnecessary, and will simply create visual noise.

A little white space makes your pivot tables look more polished

21. Get rid of row and cavalcade grand totals

Past default, pin tables show totals for both rows and columns, only you tin easily disable one or both of these totals if y'all don't desire them. On the Pivot Table tab of the ribbon, just click the Totals push button and choose the options you want.

You tin remove grand totals for both rows and columns

22. Format empty cells

If you have a pin table that has a lot of blank cells, you tin command the character that is displayed in each blank cell. By default, empty cells will display nothing at all. To set your own character, right-click inside the pin table and select Pivot Tabular array options. And then brand sure that "Empty cells equally:" is checked, and enter the graphic symbol you lot want to see. Continue in mind that this setting respects the applied number format. For example. if yous are using the bookkeeping number format for a numeric value field, and enter a zero, you'll meet a hyphen "-" displayed in the pin table, since that'southward how aught values are displayed with the Accounting format.

Empty cells set up to display 0 (zero) and Accounting number format gives you hyphens

23. Turn off AutoFit when necessary

By default, when yous refresh a pivot tabular array, the columns that contain data are adjusted automatically to all-time fit the data. Normally, this is a skillful thing, but information technology can drive y'all crazy if y'all have other things on worksheet along with the pivot table, or if yous have carefully adjusted the column widths manually and don't want them changed. To disable this feature, right-click inside the pivot tabular array and choose PivotTable Options. In the start tab of the options (or the layout tab on a Mac), uncheck "AutoFit Column Widths on Update".

Pivot table cavalcade autofit option for Windows

Pivot table cavalcade autofit option for Mac

Need to larn Pivot Tables? We have solid video grooming with do worksheets.

snydergothys.blogspot.com

Source: https://exceljet.net/pivot-table-tips

Share this post