![]() If you want a grand total column but no grand total at the bottom, choose On For Rows Only, as shown at the top of Figure 3-18. However, if you remove Sector from the report, you have only Customer in the row fields, and no blank rows appear (see Figure 3-17).įIGURE 3-18 The wording is confusing, but you can toggle off the grand total column, row, or both. As shown in Figure 3-16, the blank row after each sector makes the report easier to read. You see a blank row after each group of items in the outer row fields. Blank rows are not added after each item in the inner row field. This setting applies only to pivot tables with two or more row fields. The Blank Rows drop-down menu offers the choice Insert Blank Line After Each Item. These 27 rows are a summary of the 500+ rows in the original data set, but they also are suitable for exporting to other systems.Ĭontrolling blank lines, grand totals, and other settingsĪdditional settings on the Design tab enable you to toggle various elements. The result is a solid block of summary data. One way to select the entire pivot table is to use the Select drop-down menu on the PivotTable Analyze tab and choose Entire PivotTable. If you no longer need the original pivot table, select the entire pivot table and press the Delete key to clear the cells from the pivot table and free up the area of memory that was holding the pivot table cache. The report is now a contiguous solid block of data, as shown in Figure 3-14.įIGURE 3-15 Use Paste Values And Number Formatting to create a static version of the data. Rename both fields using the Current Field box on the PivotTable Analyze tab. Because both fields are numeric, they move to the Values area and appear in the pivot table as new columns. Drop this field between Sector and Customer in the Rows area.Ĭheck Profit and Cost in the top of the PivotTable Fields list. Select Design, Subtotals, Do Not Show Subtotals.ĭrag the Region tile from the Columns area in the PivotTable Fields list. To make these changes, follow these stepsįrom the Design tab, select Grand Totals, Off For Rows And Columns. This would allow you to add Cost and Profit as new columns in the final report.įinally, you want to convert from a live pivot table to static values. And, depending on your future needs, you might want to move the Region field from the Columns area to the Rows area. You don’t need the Grand Total at the bottom. You don’t need the Sectors totals in rows 7, 14, 18, and so on. Say that you want to convert the pivot table shown in Figure 3-13 to be a regular data set that you can sort, filter, chart, or export to another system. ![]() ![]() Choosing to repeat the item labels causes values to appear in cells A6:A7, A9:A14, as shown in Figure 3-11.įigure 3-11 shows the same pivot table from before, now in Outline form and with labels repeated.Ĭase study: Converting a pivot table to values This alleviated a lot of busy work because it takes just two clicks to fill in all the blank cells along the outer row fields. The Excel team added the Repeat All Item Labels option to the Report Layout tab starting in Excel 2010. When you do this, you will want each field in its own column. Soon, you will find out how to convert a pivot table to values so you can further sort or filter. This is a small price to pay for allowing each field to occupy its own column. The pivot table shown in Figure 3-11 is one column wider, with revenue values starting in C instead of B. When you select Design, Layout, Report Layout, Show In Outline Form, Excel puts each row field in a separate column. FIGURE 3-10 When you attempt to expand the innermost field, Excel offers to add a new innermost field.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |