Interactive Reporting
Excel Interactive Magic with Slicers
Here's a great way to use Slicers rather than Validation Lists
to set up interactive figures in your dashboard reports.
by
Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

This
figure illustrates an Excel magic trick I created in
Excel 2016.
The key trick is the method I used to display three different
figures so easily. To do so, I used what Microsoft now calls
a Picture Link or a Linked Picture. But originally, it was
called the Camera tool, which is what I'll call that feature in this
article.
The other trick is that I used a Slicer to control features
that have nothing to do with Pivot Tables or Tables, which is how
Slicers typically are used
Rather than creating this workbook from scratch,
you can
download the workbook at this link.
Then, in this and in
How to Use Slicers to Control Settings in Your Reports and
Analyses, I explain key areas of
that workbook.
The Workbook Structure
The workbook has seven worksheets organized into four logical
groups...
 The Report sheet, which you see in the figure above.
 The Data sheet, which contains an Excel Table, which is
the first step in my
ExcelFriendly Database Strategy.
 The Selection sheet, which contains a tiny, but
important Excel Table.
 The three Figure Data Support (FDS) sheets, which
contain the figures that the Report sheet displays.
When I created the workbook, I started with the state
population data, which I
downloaded from the FRED
Database, maintained by the Federal Reserve Board of St.
Louis. They now have more than 500,000 data series from 87
sources, and it's all FREE.
I arranged the data into the three columns you can see in the
workbook's Data sheet, and changed the name of the Table to TblPop, for
Population Table.
I started the name with "Tbl" so that my Tables would be
grouped together alphabetically in the Name Manager dialog,
which you can launch by pressing Ctrl + F3. And
I used "Tbl" rather than "Table" to keep my formulas short.
Next, I set up each FDS sheet. I always label my FDS sheets
D, E, F, and so on because I often have many of them, and using
single letters allows me to see the tabs easily. I usually start
with D, because Excel treats formulas that reference sheets
named C and R differently. Excel does this so that it doesn't confuse sheets
named C and R with its R1C1 references.
The Table Figure and Its Formulas in FDS Sheet D
Before
I explain the table figure's formulas, take a closer look at the
top of that figure in the Report worksheet.
Notice that the table has six evenly spaced columns: one
column for the year and five columns for the states.
Also notice that the worksheet that contains this figure has
only four columns, which aren't all evenly spaced.
This illustrates another benefit of using the Camera figure
to display tables of data in your reports. That is, using Camera
objects allows your report to show several tables, one above the
other, without having to worry about the columns in each table
lining up with each other, or lining up with the columns in your
report worksheet.
Now, with regard to the formulas...
The
formula in cell A4 uses one of my favorite new worksheet
functions, which uses this syntax:
=EDATE(start_date, months)
The formula is:
A4: =EDATE(A5,120)
This formula returns the value of the month that's 10 years
(120 months) before the date in cell A5.
All of the dates in column A have a similar formula, except
for the date in cell A14, which has the date value 1/1/2016.
The formula in cell E4
returns a result from the TblPop Table, the top of which is
shown here. This Table is serving as my ExcelFriendly Database,
as I describe in the
ExcelFriendly Database Strategy.
The formula uses another favorite worksheet
function, which has this syntax:
=SUMIFS(sum_range, criteria_range,criteria, ...)
And the formula in cell E4 is of the previous table with the
green fill is:
E4: =SUMIFS(TblPop[Value],
TblPop[State],E$3, TblPop[Date],$A4)
Here's what this formula says: Return a sum from the Value
column of the Population Data Table for all values where the Table's
State column is equal to the text in cell E3 in the worksheet,
and where its Date column is equal to the date in cell A4.
Essentially, the formula in cell E4 is a multidimensional
lookup formula, because the Table can contain only one value
that satisfies both the date and state criteria. The other
formulas in the table in sheet D use similar formulas.
I named the area with the formatted table in sheet D Fig_Table.
To select that area, press Ctrl + G or the
F5 key, choose Fig_Table, and
then press Enter.
The Formulas in FDS Sheet E
Most of the formulas that return data by state and year for the line chart
are about the same as for sheet D.
Column B, however, contains formulas that convert the dates to the text
shown. For example, the first formula is:
B3: ="'"&TEXT(A3,"yy")
The chart's Xaxis labels display this column of date text.
After setting up the line chart, I named the figure
Fig_LineChart. You can use Ctrl + G or the
F5 key to go to that
range.
The Formulas in FDS Sheet E
The formulas that return 2016 data by state for the column
chart are the same as for the line chart.
The first formula is:
B3: =SUMIFS(TblPop[Value],
TblPop[State],B$2, TblPop[Date],$A3)
After setting up the bar chart, I named he figure
Fig_BarChart. You can use Ctrl + G or the
F5 key to go to that
range.
Setting Up the Camera Object
To begin the Camera object, I went to the empty Report
worksheet, and then...
 selected any cell, say cell C10,
 entered any value in the cell,
 pressed Ctrl + C to copy the cell,
 selected any other cell, and then,
 chose Home, Clipboard,
and then clicked on the Paste icon's down arrow, and in the
Other
Paste Options section, I clicked the Linked Picture icon.
After taking these steps, I had a linked picture to the cell
that contained my randomly chosen value. Now, I needed to point my linked
picture (Camera object) to more interesting data.
To do so, I needed to set up two range names.
First, in the Selection sheet, I entered the
number 1 into a cell below row 7, say cell B10. Then I named the
cell FigNum.
Second, in a nearby cell, I entered this formula:
=CHOOSE(FigNum, Fig_Table, Fig_LineChart,
Fig_BarChart)
The formula gave me a #VALUE! error, because the formula was
trying to return the multicell Fig_Table range into one cell.
But the error value didn't matter.
After I had the formula set up, I selected it in my formula
bar, pressed Ctrl + X to cut it, and then pressed
Escape to return
to the Ready mode.
Next I pressed Ctrl + Alt + F3 to launch the
New Name dialog, typed Figure as the new name,
and then, in the Refers to box, I pressed Ctrl + V
to paste my CHOOSE formula into the box. Then I pressed
OK.
With this setup, the name Figure would reference the range
Fig_Table,
Fig_LineChart, or Fig_BarChart, depending on whether the value
in the FigNum
cell was 1, 2, or 3, respectively.
Finally, to complete the Camera object, I clicked on the
initial object I had set up in the Report sheet. Then, in the
formula bar, I replaced a reference like =$C$10 with
=Figure. And when I pressed Enter, the Camera object
returned a picture of my Fig_Table range.
And then, when I entered 2 in the FigNum cell in the Selection sheet, my Camera object showed the
Fig_LineChart range...and so on.
In my next article, I'll explain how I set up the Slicer to
provide a more elegant way to choose which figure the report
displays.
Problems with the Camera Tool
I warn you that the Camera Tool does have some problems. But you can help a lot with
them, if you care to do so.
The Camera tool has at least five problems...
1. It has a low resolution. When you return a picture of an
area with a small font for example, or slanted lines in a chart,
, the
picture will have jaggies that you don't see in the original. if
you choose a larger font, the jaggies are still there, but less
noticeable.
2. The Camera object recalculates every time you press
Enter, even when you set Excel's manual calculation mode. As a
consequence, if you use "too many" Camera objects in open
workbooks, your
keyboard becomes sluggish. The definition of "too many" depends
on many factors, but you might notice the problem if you have
somewhere between ten and twenty Camera objects in use.
3. Camera objects can become badly distorted. The easiest way
I've found to deal with that issue is to close and reopen
my workbook.
4. I recently discovered that in some cases, when a Camera
object is on the same sheet as a validation list control, the
arrow icon doesn't appear when you select the cell with the list control.
5. And most importantly, Microsoft has no current plans to
improve the Camera's performance. So that's where you can help.
I've set up a
request at
Excel
User Voice
for Microsoft to improve the Camera tool. Please click on the link and vote in favor of
improving the Camera tool. And also, please ask your friends who use Excel to
vote for updating Camera tools, as well. The Camera is a
powerful tool, as the image at the top of this page illustrates.
Microsoft does pay attention to the votes at Excel
User Voice, but we'll
need many hundreds of them, if not thousands, to encourage
Microsoft to act.
Again, you can
download the sample
workbook here. And in
How to Use Slicers to Control Settings in Your Reports and
Analyses, I finish the explanation about
the workbook by explaining the Slicer.
