![]() ![]() 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 |