Which Is Not True Of Filters In A Pivot Table

Go full admission to Excel Annoyances and 60K+ other titles, with free 10-solar day trial of O'Reilly.

In that location's also live online events, interactive content, certification prep materials, and more.

PIVOTTABLE ANNOYANCES

Learn TO Utilise PIVOTTABLES

The Annoyance:

I've heard Excel aces talk about PivotTables as if they were some great gift to spreadsheet users. Then, the owner of a chain of local pet stores asked me in a job interview if I knew how to make and manipulate the darned things. I didn't know and, rather than prevarication, I told him no, but that I was willing to acquire. He hired me, merely if I want to go past my probation period, I'm gonna take to learn how to employ them. So, I enquire: what the heck is a PivotTable, and how do I create i?

The Fix:

A PivotTable is a dynamic information table (sort of a report, actually) that yous can manipulate to emphasize different views of a information list stored in Excel. As an case, consider the worksheet shown in Figure 4-24.

Figure 4-25. This configuration emphasizes days over hours.

This PivotTable shows a sampling of the hourly sales for the departments in your boss's four pet stores. The rows are grouped by department and and so by day, while each column represents an hour of the twenty-four hour period. You could produce exactly the aforementioned worksheet without using the PivotTable feature, just then you wouldn't be able to alter it without rearranging all the data by paw to produce a different await, such as the ane shown in Figure 4-25.

The PivotTable configuration in Figure 4-25 shows each department'south sales, but instead of arranging it past day, it emphasizes sales during specific hours of the mean solar day (9:00 AM, 10:00 AM, and so on). That's the power of the PivotTable: you tin modify from one data arrangement to some other quickly, maybe every bit part of a presentation, and show how your sales break down by department, week, day, or even hour.

Figure 4-26. This configuration emphasizes hours over days.

To create a PivotTable, your data needs to exist arranged as a list, as shown in Figure 4-26. The order of the columns isn't of import, but it'due south easier to read your information if you arrange the columns in a logical order.

Figure 4-27. This data is ready to exist made into a PivotTable.

Although the order of your columns doesn't matter, your data list must follow a few rules before Excel can employ it to create a PivotTable:

• There tin be no blank rows and no blank columns in the list.

• Each cavalcade must have a unique proper noun.

• There should be no extraneous data in cells neighboring the list. That means y'all must accept either the left edge of the worksheet or a blank column adjoining the listing on either side, and y'all need at to the lowest degree ane bare row at the lesser.

• In that location can exist no duplicate keys.

Please annotation that each row denotes a unique flake of information. Every bit an example, consider the data in row 2 of Figure 4-26 (the row but below the column headers). This row provides the sales total for the Week (one), the Day (1), the Hour (9), and the Department (Cats). The next row provides the sales full for the Week (1), the Day (1), the Hr (x), the Section (Cats), and so on, row past row. Each row in the data list corresponds to a cell in the Pivot-Table.

It's admittedly vital that each row provides a unique data point. In this instance, the first four columns (Week, 24-hour interval, Hr, and Department) combine to course a unique value, or key, for each row in the column. It wouldn't make any sense to have the two rows shown in Figure 4-27.

Figure 4-28. These rows compete to see which value is used in the PivotTable. It's a fight no one will win.

Those rows attempt to set a different value for sales on Week: i, Day: 1, Hr: 9, and Department: Cats, and it'southward the sort of error that will bring the PivotTable Wizard to a screeching halt. The Sales column, which provides the information displayed in the body of the PivotTable, doesn't affair...Excel wouldn't intendance if every value in the Sales column were the same. What you can't accept are 2 or more rows in the listing where the nondata fields are an exact friction match.

CREATE A PIVOTTABLE

The Annoyance:

OK, I followed all those complicated rules and I idea my data list was ready to make into a PivotTable. And then, I chose Data → PivotTable Report in Excel 97 and tried to work my way through the PivotTable Magician, but I didn't understand some of the questions, and Excel didn't seem to recognize the data list I wanted to utilize. Help!

The Prepare:

To create a PivotTable in Excel 97, follow these steps:

1. Select any cell in your data list and choose Data → PivotTable Report.

2. Select the "Microsoft Excel listing or database" choice and click Side by side.

3. Verify that the proper data range appears in the Range field and click the Adjacent button to display the third page of the PivotTable Report Wizard, as shown in Effigy 4-28. (If the data range in the Range field is not right, click the Collapse Dialog button next to the Range field. Then select the cells from your worksheet, click the Expand Dialog button at the correct of the field, and click the Next button to brandish the 3rd page of the PivotTable Report Wizard.)

4. Drag the field headers to the desired positions in the PivotTable. The social club of the field headers determines how Excel will group the PivotTable's information. To duplicate the layout seen in Figure 4-29, drag the Week, Department, and Day fields (in that social club) to the Row expanse, the Hr field to the Column area, and the Sales field to the Data area. Click Next when y'all're done.

5. Verify that the New Worksheet pick is selected and click Finish.

Figure 4-29. Create the initial layout of your PivotTable hither.

To create a PivotTable in Excel 2000, 2002, or 2003, follow these steps:

1. Select whatsoever cell in your data list and cull Data → PivotTable and PivotChart Report.

2. Select the "Microsoft Excel listing or database" option, and and then the PivotTable option, and click Side by side.

3. Verify that the proper data range appears in the Range field, click Next, and then click the Layout button.

4. Drag the field headers to the desired positions in the PivotTable. The club of the field headers determines how Excel will group the PivotTable's data. To duplicate the layout seen in Figure 4-29, elevate the Week, Department, and Day fields (in that society) to the Row expanse, the Hour field to the Column area, and the Sales field to the Data area. When you're washed, click OK.

5. Make sure "New worksheet" is selected, and click Finish.

Tip

You can find out how to create PivotCharts in Chapter 5.

Pivot A PIVOTTABLE

The Annoyance:

I created a PivotTable, and it looks pretty good in its base configuration (shown in Figure 4-29), merely I approximate I missed the meeting in which they described how to rearrange my information on the fly. How exercise I alter its groupings to emphasize other aspects of the data?

Effigy 4-30. This is only one way to wait at your PivotTable data.

The Fix:

To pivot a PivotTable, choose Information → PivotTable Report (or Information → PivotTable and PivotChart Report, depending on your version), and elevate a field header to the new position in the PivotTable. When you lot elevate the field header over the row, cavalcade, or folio surface area, you lot'll see a gray I-bar appear, as in Effigy four-30. When you release the left mouse button, Excel regroups the PivotTable information.

Effigy 4-31. The gray bar tells you where your PivotTable header will finish up when you release the left mouse button.

FILTER PIVOTTABLE DATA

The Annoyance:

My PivotTable contains much more data than will fit on one screen. I see what announced to be filter arrows at the right edge of each field header. Tin can I utilise them to limit the data that appears in my PivotTable?

The Gear up:

To filter a PivotTable in Excel 2000 and afterward, click the filter pointer at the right edge of a field header and select the values y'all want to appear. If the Prove All box at the superlative of the listing is checked, unchecking it deselects all the values in the list; if the Show All box isn't checked, checking it selects every particular in the listing.

To filter an Excel 97 PivotTable, double-click a field header to brandish the PivotTable Field dialog box (shown in Figure 4-31). Select the values yous want to hibernate in the Hide Items drib-down list, and click OK. To redisplay the items double-click the field header and deselect the items.

Figure 4-32. PivotTables are based on data lists. Merely every bit you can filter a list, you can filter a PivotTable.

Filtering the information in your PivotTable won't affect the source data.

CREATE ADVANCED PIVOTTABLE FILTERS

The Annoyance:

I created a PivotTable, and I want to emphasize certain aspects of the data, such equally the summit x (or bottom 10) sales days for my auto dealership'southward service department. I know how to do information technology using an AutoFilter in a regular data list; is there a way to practice information technology in a PivotTable?

The Gear up:

Starting with Excel 97, you can apply the equivalent of the AutoFilter Elevation x characteristic to brandish the top or lesser values in your PivotTable's data field. The specific method you use to activate the filter has changed as PivotTables have evolved, but information technology'southward in that location if you know where to look. For the purposes of this fix, assume you lot're using the PivotTable in Effigy 4-32.

Figure 4-33. It's possible to find the top or lesser values in a PivotTable, only how you do information technology depends on the version of Excel that you're using.

To display a number of top or lesser values in an Excel 97 or 2000 PivotTable, follow these steps:

1. Click whatsoever prison cell in the PivotTable and cull Data → PivotTable Report (that's Information → PivotTable or PivotChart Report in Excel 2000).

2. Double-click the row or column field header you want to utilize to observe the pinnacle or bottom subset of data. The PivotTable Field dialog box appears. Click Advanced to brandish the PivotTable Field Advanced Options dialog box (shown in Figure 4-33).

   

   Effigy four-34. These avant-garde options include filtering your data.

3. Select the Automated option.

4. Select Elevation or Lesser from the Show pull-down list, and specify the number of items to display in the field just to the right.

This aforementioned technique I merely explained for Excel 97 and 2000 works perfectly well in Excel 2002 and 2003, but there'south a faster way to invoke the superlative/lesser x feature in these later versions of the program:

1. Click any cell in the field by which you want to sort. For example, if you lot wanted to sort the PivotTable in Effigy 4-32 by day, you could click jail cell C5 (or C6:11, etc.).

2. If necessary, right-click a bare spot on whatsoever toolbar and choose PivotTable to display the PivotTable toolbar.

3. Choose PivotTable → Sort and Meridian 10.

4. Select the On choice, and use the Show field to gear up the number of values at the peak or bottom of the list to display.

FILTER A PIVOTTABLE By Whatsoever FIELD

The Badgerer:

I created a PivotTable with all the fields I wanted to use to group and filter my data, just at present I'd like to filter my PivotTable using values from a field I didn't use in the grouping. For example, I'd like to create a PivotTable, such equally the one in Figure 4-34, that doesn't utilise the Week field values to group the data, but I'd nevertheless like to be able to use the Week field to limit the data shown in the PivotTable. Is there a way to practice that?

Figure iv-35. You don't demand to sort your data by week, only you might demand to filter it past week. No problem.

The Ready:

To filter a PivotTable using a field that isn't used to group the PivotTable data, move the field header to the Folio area in the PivotTable layout box, as shown in Effigy 4-35.

Figure 4-36. The Page surface area is your repository for fields you want to use just to filter your PivotTable data.

To display the layout box in Excel 97, click whatsoever jail cell in the PivotTable and choose Information → PivotTable Report. In Excel 2000 and subsequently choose Information → PivotTable and PivotChart Study → Layout. In Excel 2000 and afterward, yous also tin can drag a field header to the Drop Page Fields Here area of the PivotTable written report. Once the field header is in the Folio area, you can filter the PivotTable as if the header were in the Row or Column area.

Find PIVOTTABLE ADD-INS

The Annoyance:

I similar using PivotTables, merely I don't have time to larn how to practice everything. Are any add together-ins bachelor that can assist me do things such as fix the impress area, freeze the rows at the tiptop of the Pivot-Table, and so on?

Use AUTOFORMAT TO A PIVOTTABLE

The Badgerer:

My PivotTables look great, but when I try to add formatting, such as displaying a value in boldface or italic, and so pivot the table, the prison cell formats don't pin with the values. They stay in identify, which means they are applied to the wrong cells. I know you can utilise an AutoFormat to a table in an Excel worksheet. Is there a way to employ an AutoFormat to a PivotTable?

The Fix:

To utilise an AutoFormat to a PivotTable, click any cell in the PivotTable, choose Format → AutoFormat, and select the AutoFormat you want to employ. If you want to create your own PivotTable AutoFormats, you can apply the Pivot Table AutoFormat 40 add-in discussed previously.

Terminate PIVOTTABLES FROM PIVOTING!

The Badgerer:

I created several PivotTables in Microsoft Excel 2000 to analyze sales data, just changes I make to ane PivotTable seem to touch on the others I've created from the same information. What's going on?

The Fix:

The problem is that you followed Excel'south advice when you created the 2d (and subsequent) PivotTables based on the aforementioned information. When you create a second PivotTable from the same data source, Excel displays the advisory message Your new PivotTable volition utilize less memory if you lot base of operations it on your existing PivotTable [Work-bookName]SheetName!PivotTableName, which was created from the aforementioned source information. Do you want your new PivotTable to be based on the same data as your existing PivotTable? Information technology seems like a reasonable thing to do, but it is, in fact, a very bad idea.

When you create a PivotTable, Excel creates a enshroud that contains the data and structure (that is, field groupings) for the PivotTable. When y'all use 1 PivotTable as the data source for a second PivotTable, any changes to any of those PivotTables that share the same memory cache will touch all the other PivotTables. You can avoid this behavior by clicking No when Excel asks if you desire to base of operations your PivotTable on the same data as an existing Pin-Table. Then Excel volition create a separate retentiveness enshroud for the new PivotTable, and your PivotTables will pin independently.

PIVOTTABLE Data DISPLAYS As NUMBER SIGNS

The Annoyance:

I don't have a huge data list, so I created my PivotTable on the same worksheet as the original data. The trouble is that Excel now displays some of my source information as number signs (#####). What's going on, and how do I fix it?

The Fix:

What's happening is that Excel is reformatting the width of your worksheet columns to fit the contents of your PivotTable—and if there's no room in a cell to brandish the source data, Excel uses number signs. Yous can foreclose Excel from changing your columns' widths past turning off the Autoformat Table pick in the PivotTable Magician. The procedure you follow depends on your version of Excel.

To plough off the Autoformat Tabular array pick in Excel 97, follow these steps:

1. Select a cell contained in your PivotTable and choose Information → PivotTable Report.

2. Click Next to go to the fourth step of the PivotTable Wizard, click the Options button, and uncheck the "Autoformat tabular array" box.

In Excel 2000, follow these steps:

1. Select a prison cell contained in your PivotTable and cull Information → PivotTable and PivotChart Written report.

2. Click Next twice to advance to page 3 of the PivotTable and PivotChart Wizard.

3. Click Options, and then uncheck the "AutoFormat tabular array" box.

In Excel 2002 and 2003, right-click any jail cell in the PivotTable, choose Table Options, and uncheck the AutoFormat tabular array box.

"PIVOTTABLE IS Non VALID" Mistake

The Annoyance:

I support a big mail order retail sales house where the IT folks get the fancy software and we go very old versions of Office. I was so frustrated I brought in my own, unopened copy of Office 2000, only I'1000 the only person in my department that has anything that recent—everyone else uses Office 97. I created a PivotTable from something called an OLAP (Online Analytical Processing) cube in Excel 2000, went to our meeting room (which has a PC running Excel 97), and tried to use the PivotTable in a presentation. I got a "PivotTable is non valid" mistake bulletin. Because I couldn't observe the "Yes, it is!" push, I concluded up having to wave my hands instead of doing the absurd stuff I had planned. What happened?

The Fix:

Equally you probably guessed, Excel 97 simply tin can't handle PivotTables based on data in OLAP cubes. That functionality wasn't introduced until Excel 2000. Excel 97 will brandish the PivotTable in the country in which it was saved in Excel 2000, but you tin't pivot it or refresh its data. You can work around this problem in two ways. The first workaround is to create a new PivotTable for each configuration you desire to display on the Excel 97 computer. I recommend putting the PivotTables in split worksheets and renaming the worksheets to reverberate the emphasis of each PivotTable. The other workaround is to import the OLAP data into an Excel 2000 workbook, open the workbook in Excel 97, and create a new PivotTable.

PIVOTTABLE LOSES FORMATTING

The Annoyance:

When I created the worksheet I apply to track sales, I formatted the numbers in the Currency style, simply when I create a PivotTable from that data, Excel loses the formatting. Is at that place any way I can retain data formatting when I create the PivotTable, or at least add the formatting apace later on?

The Ready:

It's a lamentable fact of life that you lot lose your data field's formatting when you create a PivotTable. Microsoft Knowledge Base article #214021 offers a set up—yous're supposed to click Options on the next-to-last (Excel 97 and 2000) or last (Excel 2002 and 2003) PivotTable Wizard page, and cheque the Preserve Formatting checkbox—merely information technology didn't work for me in whatever version of Excel. What I can tell you is that there's a quick mode to format your data in every version since Excel 97. To format the data field of a PivotTable quickly, right-click any cell in the data area and cull Field (Excel 97 and 2000) or Field Settings (Excel 2002 and 2003) to display the PivotTable Field dialog box (shown in Effigy four-36).

Figure 4-37. Here's where you get to find the Format Cells dialog box for your PivotTable.

Once in the PivotTable Field dialog box, click the Number push button and use the Number folio of the Format Cells dialog box to define your information's format.

USE CALCULATED FIELDS IN A PIVOTTABLE

The Annoyance:

PivotTables are fine as far as they go, just I'd like to do more than with the data in the tabular array. For instance, three% of my company's revenue goes to a corporate overhead fund that pays the calorie-free bill, function supplies, and environmental cleanup for antifreeze spills. I'd like to accept a field riding alongside the torso of the PivotTable (every bit shown in Effigy 4-37) that lists the overhead deduction associated with the data in the table. Is there any way to do that?

Figure 4-38. This PivotTable answers your questions.

The Fix:

Excel 97 introduced the calculated field (a user-defined field that derives its value from a formula you create) and the calculated item (a user-defined field that derives its value from a detail entry in a column—e.thou., the "Cars" entry in the Department column). For example, if yous wanted to determine the amount of the 3% overhead fund deduction from hourly sales, you could create a calculated field with the formula =Sales * .03 to display the amount.

To add a calculated field to a PivotTable, follow these steps:

1. If it isn't already on, turn on the PivotTable toolbar by selecting View → Toolbars.

2. Select any cell in the PivotTable, and and so, on the PivotTable toolbar, choose PivotTable → Formulas → Calculated Field to display the Insert Calculated Field dialog box, shown in Effigy iv-38.

   

   Figure 4-39. Create additional fields based on whatsoever formula you want.

3. Blazon the proper noun of the calculated field in the Name field, type your formula (such as =Sales * .03) in the Formula field, click the Add button, and then click OK.

4. The calculated field appears in the PivotTable information area.

To delete a calculated field, follow these steps:

1. Brandish the PivotTable toolbar.

2. Cull PivotTable → Formulas → Calculated Field to display the Insert Calculated Field dialog box.

3. Open the Proper noun drop downward, select the calculated field you want to whack, click Delete, and then click OK.

Creating a calculated particular is similar to creating a calculated field, except you need to decide at which group level to create the calculated item. For instance, in the PivotTable shown in Figure 4-37 you lot could create a calculated item named NonCarSales that added sales from the Accessories and Service categories and included those results in the body of the PivotTable. You would demand to create the formula using the format =Column[Value1] + Column[Value2] so that Excel tin can identify which elements yous want to calculate. For instance, the formula to add Accessories and Service sales would be =Section[Accessories] + Section[Service] .

To add a calculated detail to a PivotTable, follow these steps:

1. Click the field header that corresponds to the field y'all want to use in your calculations (in this case, click Department).

2. On the PivotTable toolbar, cull PivotTable → Formulas → Calculated Item to brandish the Insert Calculated Particular dialog box, shown in Figure 4-39.

   

   Figure 4-forty. You lot also can create fields that pivot along with the rest of your PivotTable data.

3. Type a name for the calculated item in the Proper name field, type the formula for the adding in the Formula field, and so click Add.

CHANGE SUMMARY CALCULATIONS IN A PIVOTTABLE

The Annoyance:

I used a PivotTable in a presentation the other 24-hour interval, and 1 of my company's senior partners asked if there was any way to modify how the PivotTable data is summarized. Specifically, she asked if I could display average sales instead of total sales in a Pivot-Table. Is there any way to change how Excel summarizes PivotTable data?

The Fix:

You lot can, in fact, change the summary operation Excel uses in a PivotTable. To practice so, right-click any cell in the PivotTable'south data area and choose Field to display the PivotTable Field dialog box (shown in Figure 4-40).

Effigy 4-42. Sums and totals aren't your merely options when summarizing PivotTable information.

Select the summary calculation you want to use from the drib-down list, and click OK to apply it to the PivotTable. For instance, if y'all wanted to observe the average hourly sales over a calendar week, you would select Boilerplate. When you click OK, you'll come across the PivotTable shown in Figure 4-41.

Figure 4-43. Here's where you find what yous can do with your summaries.

HIDDEN Data DOESN'T Appear IN THE SUMMARY

The Annoyance:

My boss uses the PivotTables I create to show how much each department contributes on a daily and weekly basis to our bottom-line profits. One thing he doesn't like, however, is that Excel doesn't brandish a "filtered" total for a column when you filter it—it displays only the total for the visible values (equally shown in Figure 4-42). Is at that place whatsoever manner to have Excel brandish the total for all items, both visible and hidden?

Figure 4-44. You command whether a PivotTable bases its calculations on all the values, or just the visible ones.

The Set:

To have Excel include subconscious values in its subtotals, correct-click any cell in the PivotTable'due south data area, choose Tabular array Options (Options in Excel 97), and place a check in the "Subtotal hidden page items" checkbox.

"REFERENCE NOT VALID" Mistake

The Badgerer:

I used a data form to enter the values for my PivotTable'south information list, but now when I effort to create a PivotTable based on that information, I get an error message proverb "Reference is not valid." I know the reference is valid—it's simply a data listing, like dozens of data lists I've used before. Sheesh! One thing I did notice is that for some reason Excel is displaying the reference Database in the second page of the PivotTable Wizard (see Figure 4-43). I tin select the range before I run the Wizard, simply I'd rather let Excel detect the data listing automatically. What's going on?

Figure iv-45. The data list looks like it should work, and it should—except for one detail.

The Set:

The problem is that when y'all create a data entry form to enter information into a listing, Excel creates a named range chosen Database. What'south worse, the Database named range is invisible, and doesn't show up in the Define Name dialog box. To delete the Database named range, follow these steps:

1. Select any cell in the worksheet and cull Insert → Proper noun → Ascertain to display the Define Proper noun dialog box.

2. In the "Names in workbook" field, type Database.

3. Click Add, and so click Delete.

Excel will at present detect the data list in the usual manner.

DISPLAY PIVOTTABLE DATA As A Pct OF A TOTAL

The Annoyance:

I'd similar to display my sales data as a percentage of a total, non just as a value with a running total in the far-correct column of my PivotTable. For example, assuming the data in Figure 4-44, how can I display each day and hr as a percentage of a cavalcade and how do I change the display and so that I see percentages of days and weeks instead of a percent of the entire column?

Figure 4-46. If you tin't effigy percentages in your caput, you'll need to alter how Excel dislplays this data.

The Ready:

To display the values in an Excel 97 PivotTable every bit a percentage of a column total, follow these steps:

1. Click any cell in the PivotTable and choose Data → PivotTable Study.

2. On the third page of the PivotTable Wizard, double-click Sum of Sales and so, in the PivotTable Field dialog box, click the Options button (the result is shown in Figure four-45).

   

   Figure 4-47. Use the controls in this dialog box to change how Excel summarizes your PivotTable information.

3. Click the down arrow at the right of the "Show data equally" field and cull "% of Column" from the drop-downwardly list.

In Excel 2000 and subsequently, double-click the Sum of Sales field header in the body of the PivotTable to display the PivotTable Field dialog box and follow the aforementioned process. Then you lot can filter the data in the PivotTable, which volition cause Excel to update the percentage calculations (as shown in Effigy 4-46).

Effigy 4-48. The stop outcome answers your questions.

Go Excel Annoyances now with O'Reilly online learning.

O'Reilly members feel live online grooming, plus books, videos, and digital content from 200+ publishers.

Which Is Not True Of Filters In A Pivot Table,

Source: https://www.oreilly.com/library/view/excel-annoyances/0596007280/ch04s04.html

Posted by: brumfieldgince1938.blogspot.com

Share this post