The figure below illustrates an Excel magic trick I created for an Excel Dashboard, using 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
In this article 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 Excel-Friendly 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 in sheet D, 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.
For example, the first figure below shows the top-left corner of the worksheet D, the first Figure-Data Support sheet.
The formula in cell A4 of this sheet 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 Excel-Friendly Database, as I describe in the Excel-Friendly 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, which is supported in sheet E, 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 X-axis 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 F
The formulas in sheet F, which 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 F5key to go to that range.
Setting Up the Camera Object
To begin the Camera object, I went to the empty Report worksheet, and then I…
- 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, 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 multi-cell 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. An Excel bug can make Camera objects 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, in How to Use Slicers to Control Settings in Your Reports and Analyses, I finish the explanation about the workbook by explaining the Slicer.
