Learn how to use the Report Layout Options in MS Excel 2007. The video explains how to navigate the Report Layout options and its different uses to suit the taste of the user.


More DIY videos at 5min.com

Video Transcription

Let me add the products back to our pivot table. And, anyone who has been using pivot table since Excel 2003 is probably screaming and saying what exactly is this new view of the data? Look at column A, you will notice that column A now contains both customers and products. That is unlike anything we ever had in the last five versions of Excel. How can it be that Excel is putting two different fields in that same column? This is a brand new view and it is called the Compact View, Microsoft likes this view so much that they made it be the default view for all new pivot tables. While I admit that this a pretty cool view, it might drive you crazy if you are hoping to take this data from a pivot table later and use it in a new analysis. Once the pivot table is selected, we have two new ribbon tabs, the Pivot Table Tools Action and the Pivot Table Tools Design. In order to change from the Compact View to one of the traditional views, we need to go to the Design Ribbon. Along the left hand side, you will see icons in the Layout group. The Report Layout icon is actually a drop down and there are three layouts. Compact Form is the view that we see right now, Outline Form is the view that you are familiar with from Excel 97 to Excel 2003 and Tabular form is the other although a bit more obscure view that was available in prior versions of Excel. Let us take a look at the three. Currently, we have Compact Form which shows us both customers and products in column A. If I choose Outline Form, then we have Customers in column A and Products in column B. This is the view that you are probably used to. If we choose Report Layout in tabular form, you will notice in tabular form that the totals move to the bottom of each section. We actually have control in Compact Form, Outline Form whether the totals appear above or below the data set but in tabular form. Tabular form is sort of the older form and it always has to have the totals at the bottom of the form. Other new options that we have in the layout group, we can choose whether or not the subtotal should appear at the top or the bottom of each group. We can also choose whether or not there should be a blank row after each section. Blank rows are only available in Compact Form and Outline Form. I choose Outline Form and then Blank Rows, insert blank rows after each line. You will notice that between each customer, there is a blank row. This blank row kind of helps the eyes to follow from one section of the report to the other. I want to go back to the Compact View. There must be a reason that Microsoft made this view the default view. There must be some really cool things, some things that caused them to completely change from the same view that we had for the last ten years to the new view. A Report Layout, if I go back to Compact Form and I am going to go back to the options tab of the pivot table tools, I need to choose one of the customers so I am going to click on cell A5. You will notice that we have a couple of new icons, then expand entire field and collapse entire field that show up in the active field group of the actions ribbon. Watch what happens when I say they collapse the entire field. We get a beautiful view that just shows us Customer Totals. I can imagine having two projectors and being in a meeting showing this work book, some will say, “Well, we want to see the detail behind one particular customer.” You can simply click the plus and expand the view for that customer. The plus changes into a minus if you need then to collapse, click the minus sign and you can hide the detail. I agree that this is kind of a cool view and I appreciate the fact that Microsoft allowed us to discover by making it be the default view in pivot tables. We have to be aware that if you do not like this view, just stop by the Design Ribbon to choose one of the old legacy views. One row for the blank rows and that wraps our look at the new views and options in the layout group of the pivot table tool bar.