Images can now be included in Excel PivotTables which not only makes for better looking PivotTables but more interesting Slicers too.
A great contribution from Office Watch reader “AllanC” in New Zealand.
With pictures in a PivotTable you can easily make cute slicers like this to show one record (person) at a time
Here’s the source table for the Slicer. Nothing fancy, just a list of staff with pictures in, not over, their respective cells.
Picture – place in cell
For this feature to work, the images must be added to the source table using Pictures | Place in cell… on the Insert ribbon.
This is a new feature, previously described in Office Watch: Images in Excel Cells – at long last. Really, adding pictures to PivotTables is a logical extension to the ‘images in cells’ feature.
Picture data is handled just like any other PivotTable field which appears in the field list along with everything else but with one exception.
Images can’t be Slicers
Before you get too excited, images in a table can’t be used as a slicer. That would be nice, perhaps to choose a product from its retail box or company by its logo. Sadly, that’s not supported in Excel so far, here’s what happens if you try ‘Add Slicer’ from an image field.
Symbols (like currency symbols) or even Emoji can be used in Slicers – but not pictures.
PivotTable Slicer with pictures
The PivotTable is barebones, with all totals, calculations and buttons suppressed. It is controlled by a slicer to ensure that the details for only one person is displayed at a time. Clicking a name on the slicer switches the display to that person.
All fields in the pivot table have been added to the Columns area. Select Field Settings for each in turn and ensure that Subtotals are set to None.
Row and column Grand Totals, expand/collapse buttons and the filter drop downs are set to hidden on the PivotTable Options dialog.
A PivotTable Style can optionally be applied.
Make the Slicer
Right click Name in the top list of fields and, from its context menu, select Add as slicer.
To ensure a single selection, open Slicer Settings from the Slicer ribbon and ensure that Display header is not selected.
This does not actually prevent two or more names from being chosen (hold down the Ctrl key while clicking to do so) but it does make multiple selections less obvious.
A Slicer Style can optionally be applied.
Images in Excel Cells – at long last
Excel’s Stock / Currency data (kinda, sorta) gets auto-refresh
Investment Rate of Return is easy in Excel
