Filtering Excel Pivot Tables With the Report Filter and Slicers

As soon as you might be used to creating Pivot Tables out of your knowledge, chances are you’ll be searching for extra management over the knowledge you show. You could solely have an interest within the knowledge for one particular salesperson, or product. It’s simple to filter your Pivot Desk.NOTE: I’m utilizing the 2010 model of Excel for this text. Different variations might look somewhat totally different. The uncooked knowledge I’m speaking about contains fields for: salesperson, product, date, month, yr, and gross sales.I’ve created a Pivot Desk with salesperson within the row labels, product within the column labels and gross sales within the values space. To filter the salespeople or product:1. Above your record of salespeople you will note ‘Row Labels,’ or the identify of your area with a drop-down arrow to the proper of it. (The phrases you see rely upon the report structure you might be utilizing. This may be adjusted by going to Pivot Desk Instruments Design tab, Format group, Report Format command.)2. Click on on the drop-down arrow to see Excel’s typical filter field. From right here you may filter on quite a lot of label or worth choices. The identical filter field is out there for the ‘Column Labels.’

This can be good for the knowledge you are attempting to extract, however what if you wish to filter by a area that is not at the moment getting used for the Pivot Desk, just like the yr? On the Area Record pane (on the proper of your display screen in case your Pivot Desk is energetic) you will note an space known as ‘Report Filter.’You’ll be able to drag any of your area headings into this space so as to add a area that you could filter by. If you happen to drag the yr area right here after which have a look at the cells above your desk, you will note ’12 months, (All) and a drop-down arrow.’ If you happen to click on on this arrow it’s going to record all of the years included in your knowledge. You’ll be able to select one yr, or go to the underside of the record and select ‘Choose A number of Objects.’ If you happen to even have ‘month’ as considered one of your fields, this too may be added to the Report Filter field within the Pivot Desk Area Record. A second filter will seem above your desk and you may select the months you need to see knowledge for. The order of the filters within the ‘Report Filter’ of the Area Record pane is the order these will seem above the desk.It is a nice function for filtering, however there was a typical grievance from customers in variations of Excel earlier than 2010. If you happen to printed the Pivot Desk or shared it with somebody who wasn’t comfy with this instrument, they did not at all times know what the information had been filtered by. If you happen to have a look at the filters above the desk you will note a phrase in brackets to the proper of the sector identify: All, A number of, or a selected yr or different worth. A number of is the issue, as a result of all meaning is a couple of worth was chosen to filter by (a number of salespeople for instance). If you wish to know which particular values have been chosen you must go to the drop-down and take a look. Not everybody was comfy doing this.Microsoft listened to this grievance and launched Slicers within the 2010 model of Excel. Slicers may be inserted onto the pivot desk sheet and are designed to show what the Pivot Desk has been filtered by. They can be used to alter the filters reasonably than going to the drop-down arrows.To insert a slicer:

1. Be certain your Pivot Desk is active2. Click on on the Pivot Desk Instruments Choices tab3. Click on on the highest half of the Insert Slicer command within the Type & Filter group4. Select the sector or fields you need to have a slicer forSlicers are objects, so you may transfer them by dragging their edges, or delete them by choosing and urgent the delete key in your keyboard. When a slicer is energetic you’re going to get a brand new ribbon of Slicer Instruments that mean you can change the way in which they give the impression of being.You probably have inserted a slicer, you will note an inventory of all of the values that exist in that area. Click on on one of many values and the Pivot Desk might be filtered by that worth. If you wish to select a number of values, maintain your Ctrl key down once you click on along with your mouse. Even when somebody is not used to Pivot Tables they will simply use the Slicers to filter the desk.This is only one extra fabulous function that exists on the planet of Excel Pivot Tables. Discover some uncooked knowledge and see what you may create. I promise that you just will not be disenchanted.