Excel’s New Slicer—It Slices, It 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 nonselected 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. In the Slicer group, select PivotTable Connections and place a checkmark next to the PivotTables you want to share the slicer as shown below.
Hint: You can resize and reposition your slicers on the worksheet, and apply matching styles to both the PivotTables and slicers to produce professional-looking results.
More from the JofA:
