Sep 21, 2015

Being able to analyse all the data in your worksheet can help you make better business decisions. But sometimes it is hard to know where to start, especially when you have a lot of data. Excel can help you by recommending and then automatically creating PivotTables, which are a great way to summarise, analyse, explore, and present your data. Pivot Tables will allow the user to insert several fields in the Filters area. You can filter the Pivot Table by clicking on the arrow next to Company Name (All), ticking the Select Multiple Item options and selecting the companies you want to display in the filter. Once you filter the Pivot Table, the (All) option next to Company Name label changes to (Multiple Items). Unless you display the drop down list of all Company names, the user of the report does not know which company names are displayed in the report filter. You can insert a slicer for a quick and effective way to show the filtered data or to add/remove filter items quickly. To insert a slicer:

1. Go to PivotTable Tools Analyse tab
2. Go to Filter group
3. Click the Insert Slicer button
4. Tick the Company Name option
5. Click OK and the slicer displays.

The problem at this point is, no matter how you resize the slicer, the user cannot fit all the Company Names on it, so not all selected names are displayed and once again, not all filtered items are displayed. The good news is, you can display the slicer on multiple columns to show all selected Company Names. Perform the following steps to show the slicer on multiple columns:

1. Right click on the slicer
2. Click Size and Properties
3. Expand Position and Layout option
4. Under Layout heading, increase the Number of columns to 5
5. Resize the slicer

The slicer displays all the company names in one view and therefore you can see all the selected items. Hold down the CTRL key on your computer keyboard and click other companies names you want to add to the filter. For more information, take a look at New Horizons’ Excel training courses.