This option displays thechange as a percentage of the base item. What Is a Pivot Table? You can use the % of Row option, as in Figure 3. His website hosts over 12 million page viewsannually. Alot of the websites that the books refer you to to complete lessons have not been re-registered by the domain that had them when this book was originally published. Whereas the sales figures for columns C, D, and E are closely aligned withthe headings, the Qtr1 heading in B8 appears far away from the sales figures in B9:B29. If the Banded Columns or Rows check box is selected and the thumb-nail in the gallery does not show the effect, you know to avoid that style. Forexample, he may want the heading Gross Margin to be split with Gross in row 1 and Margin in row2.
In Excel 2007, the first clue that you have a problem appears when you click the check boxfor Revenue in the Fields section of the PivotTable Field List. You are given a blank pivot table,as shown in Figure I. It is a large rectangular area below and to the rightof the headings. Heres how this feature works. The Regiondrop-down in cell B1would allow you to printthis report for one par-ticular region manager.
My sister, Barb Jelen, provides excellent back office support at MrExcel. In Excel 2007, this command is found in Data, DataTools,What If Analysis, Data Table. Whereas some of these limita-tions remain constant, others are highly dependent on available system memory. After you open the drop-down in the Fields section, you can see that all the really usefulsorting and filtering options are behind the hidden drop-down. Pivot Table Data Crunching is the book for you. Use the Advanced Filter command with Unique RecordsOnly see Figure I. Each row in your data source represents individual items in each column.
To alleviate this problem simply remove both text and blank values from the source column and refresh the Pivot Table. In the helpfiles, Microsoft has been calling these tabs PivotTable Tools Options and PivotTableTools Design. You can usemany powerful settings to tweak the information inyour pivot table. Columns E through I need to be reformatted as two columns. Secretly, the Sales department is considering a massive reorganization of the salesregions. At this point, you have created your first pivot table report! This book I would recommend without doubt if you are in need or want to learn pivot tables.
You can use the More Sort Options choice to access the Sort dialog box, as shown in Figure4. Creating a Report FilterOften, you may be asked to produce reports for one particular region, market, or product. In the TableOptions dialog box, select the Display tab and place a check next to Classic PivotTable Layout, as demonstrated in Figure2. Tips provide you with quick workarounds and time-saving techniques to help you do your workmore efficiently. Although there are limited applications in which you would want todifferentiate between having no sales and having net zero sales, this seems rare. Fill in as many blank cells in your data source as possible. It is helpful to manu-ally add a title above the pivot table to inform the readers what they are looking at.
If you select a cell in the innermost row field and click Expand Entire Field, Excel displays the ShowDetail dialog box, as shown in Figure 3. You can use one of several methods to applysorting to your pivot table: Using the Sorting buttons on the Options ribbon Using the hidden drop-down in the Fields section of the PivotTable Field List Using the manual method Sorting Using the Sort Icons on the Options RibbonThree icons appear in the Sort group of the Options ribbon. It is hard to follow and none of the examples in the book match up the examples that you are able to download from the website. The wording in this drop-down seems justa bit confusing. We dohave a User Services group, however, where I will forward specific technical questions related to the book.
C A U T I O N Some of the themes have contemporary fonts. This functionality is allowed only within the PivotTable Field List dia-log box dragging into drop zones. In some instances, you may prefer to print a different namein the pivot table. Note that the PivotTable Field List can bedocked on either the right or left side of the screen. As shown in Figure 3. If you need to produce a report that has two date fields, and you attempt to group both datefields by months and years, Excel arbitrarily names the first grouped field Years and thesecond grouped field Years2.
The Anatomy of a Pivot TableBecause the anatomy of a pivot table is what gives it its flexibility and, indeed, its ultimatefunctionality, truly understanding pivot tables would be difficult without understanding theirbasic structure. Saving Time with New Pivot Table ToolsMicrosoft has invested a lot of time and effort in the overall pivot table experience. Pivot table sorts are more powerful. Thepivot table is still formatted in the original style. You need to create subtotals that frequently include new additions. If you can drag a mouse, you cancreate a pivot table. When you look through a kaleidoscope at anobject, you see that object in a different way.
If you want to change the stripe color, click the Format button. You might want to suppress the subtotals for the Market andProduct Line fields. Thanks to William Brown at Waterside; Loretta Yates, Andy Beaster, ChuckHutchinson, and Kevin Howard at Que Publishing. Because these drop-down arrows are always visible, you might be more likely to open thesedrop-downs. Now, if your manager takes a look at the report and asks you to add Market to the analysis, you are nearly back at squareone and are looking at an additional 15 minutes to produce the new report. We do have a User Services group, however, where I will forward specific technical questions related to the book. The next choice allows you to remove the field from the pivot table.
Corporate and Government Sales1-800-382-3419corpsales pearsontechgroup. Years later, with the release of Excel 97, Microsoft offered users an enhanced pivot tablewizard and key improvements to pivot table functionality, such as the capability to add cal-culated fields. Now that you have your market names, its time to calculate the total dollar sales for each market. On the Options ribbon, click the Field Settings icon in the Active Group field. Every column has a heading. The person reading this worksheet would probably assume thatcells B10:B11 are in the New England market and cell A11 is in the North region.