Excel's New Slicer
It slices and dices.
Q: We maintain a large amount of data for more than 100 restaurants for which we analyze sales and expenses daily, weekly and monthly by location, city, state and territory and even by type of restaurant. Overnight, the results of operations for the previous day feed into a database and we analyze the data in Excel using PivotTables. Despite preparing dozens of reports, my superiors desire more-detailed reports and analysis. Can you offer a good solution?
A: Excel 2010’s new Slicer functionality may be the answer. Slicer is an enhancement to Excel 2010’s PivotTables that inserts filter boxes that your superiors can click to display precisely the reports they desire. To use slicer, position your cursor anywhere in a PivotTable report and, from the Insert tab, select Slicer from the Filter group. This action will open the Insert Slicers dialog box shown below.
Place a checkmark in the box for each slicer you want to display and click OK. Excel will insert Slicer dialog boxes containing filter buttons into the worksheet for each field name you select, as shown in the example below.
Simply click the various filter buttons to display different views of your data. For example, the second report in the next column summarizes revenue for those “Dine In” and “Drive Thru” restaurants managed by “Caroline” and “Madison” in the “Atlantic” and “Great Lakes” regions. (Holding down the Ctrl key allows you to make multiple selections within a Slicer dialog box.) The selected filter buttons are highlighted and the non-selected filter buttons are grayed out, allowing you to see which filters have been applied to the report.
In the example shown above, the reader could produce up to 720 views of this one report (9 States × 4 Types × 4 Territories × 5 Sales Reps). In your situation, you could add three similar PivotTable reports summarizing your data by week, month and quarter and e-mail the entire workbook to your superiors. This would limit the total number of reports you would need to prepare to just four, while providing your superiors the ability to view the data thousands of ways, according to their preference.
Advanced tip: A slicer can be shared with multiple PivotTable reports so that when filters are applied in one slicer, multiple Pivot Table reports are filtered. To share a slicer, click on the slicer to be shared to display the Slicer Tools, Options tab.
This article has been excerpted from the Journal of Accountancy. View full article here.