Learn how to use the Repot Filters Feature in MS Excel 2007.
Tags:excel advance training,feature,microsoft excel 2007,microsoft windows,pivot table,report filters,total training
Grab video code:
Well, so far, we have talked about three of the drop zones of the pivot table. The column labels, the row labels and the sum values, we have not looked at the report filter drop zone at all. The report filters are great featuring pivot tables. If you have been using pivot tables in earlier versions of Excel, it used to be called the Page Fields Area. We need to reset this pivot table a little bit. So, I go back to the Options Tab and say that I want to expand the entire field. That way, we can see both the customers and products that exist. I am going to take the customer field and move it from the row labels area up to the report filter area.
Now, you see that we have a simple pivot table with products going down the left hand side and regions going across the top at the intersection of each product and region are the revenue. We have a new drop down that appears in cell B1 right next to the customer heading. This drop down initially says all but when we open the drop down, we can choose one specific customer and now we see the revenue by region and product for that one particular customer. This feature has been in pivot tables for a long time but Excel has dramatically improved it in Excel 2007. I am going to choose the drop down again and I am going to expand this dialog box so we can see all of our options. The expand indicator are the three dots from the lower right hand corner of the dialog box. If I click those and drag out, I will be able to enlarge this dialog box.
This is a great new feature in Excel 2007. Before, we are always stuck with the tiny little drop down boxes. Now, you will notice that we have a list of all of our customers. If I want to see just one customer just as in Excel 2003, I would click that customer but we have a new option, the lower left hand corner called Select Multiple Items. Once I have selected multiple items; I can either choose to show two, three, four or five customers or all customers by clicking the all button. Let us say that for some reason I need to see the first five customers in the list, Excel now shows me the totals for those five customers. One complaint that I have is that in cell B1, they have indicated that I have selected multiple items but they have not told me which items I have selected. I appreciate that it is a great feature that I can choose multiple customers. Let us say that they are a division of a company, I needed to choose all of those divisions. The problem is that it is very hard if I print this report to understand what items I have selected. I might even want to put a little note somewhere. It has a footnote saying that these are the customers that are included in the pivot table. There is another great use for the Report Filter Fields. I have seen people build executive information systems where they actually take all of their text fields and move them to the Report Filter, let us do that. Then take of the numeric field and add them to the sum values area, we have now created a very simple add hack reporting tool.
Anyone, even the Vice President down the hall could open this workbook and select a particular combination of region of product and customer and see that the total that was sold to that combination. Now, of course, you and I know that the VP is never going to do this. They are still going to call you but at least the concept is there, let us try it. I will choose all customers and then say that I just want to see the north region for the A354 product. The pivot table automatically redraws to show us the Revenue, Quantity, Cost of Goods Sold and Profit for that combination. I could do this all day choosing different combinations of Customer, Region, Product and Date. It makes a great add hack reporting tool using the report filters just as we had in several versions of Excel. Now beyond this, Microsoft has added a filtering feature to fields that are in the row labels and in the columns labels area. We need to rearrange our pivot table in order to see this. So, I am going to unselect Quantity, Cost of Goods Sold and Profit. I am going to move the Cus