Microsoft Excel: Create a picture-based dashboard report
Q. I have a large Excel workbook with many worksheets, and I’d like to summarize the key elements of this workbook into a single dashboard-type report. What’s the best way to do this?
A. There are many ways to create a dashboard in Excel, but my preferred method may surprise you. Excel’s Camera tool offers a great way to build a dashboard report because it allows you to copy and paste-link selected data (with the data formatting) into movable and resizable pictures. Thereafter, you can resize and move your camera pictures around to create a decent dashboard that updates automatically as your workbook changes. As an example, presented below is a dashboard I created from a lengthy projection workbook.
To create this dashboard page, I performed the following steps:
1. I first added the Camera tool to my Quick Access Toolbar by right-clicking my Quick Access Toolbar and selecting Customize Quick Access Toolbar. Under the Choose commands from option, I selected All Commands. I scrolled down to and selected the Camera tool, clicked the Add button, and then clicked OK. The Camera tool then appeared on my Quick Access Toolbar as pictured below.
2. Next, I inserted a new blank worksheet, with no gridlines, on which to build the dashboard.
3. I then navigated to the first crucial bit of information in my workbook, Source and Use of Funds in this example, highlighted the Source and Use of Funds (clicking Ctrl+A with your cursor positioned in a data range is a fast way to select all of the data), clicked the Camera tool (on my Quick Access Toolbar), navigated to the dashboard worksheet, and clicked on the worksheet (anywhere). As a result, the Camera tool displayed the Source and Use of Funds report on the dashboard worksheet as an image. This resulting image is linked to the original data, so it updates automatically whenever the original data or formatting changes. I then resized and repositioned this Source and Use of Funds picture and added a thick blue border (so you could better tell that it is an image, and not data in the cells).
4. I repeated the process explained in step 3 multiple times to also display pictures of the Statement of Cash Flow, Projected 12 Year Statements of Income, and several charts. Once the crucial information was pictured on the dashboard worksheet, arranging these images was simply a matter of resizing and repositioning them as desired to achieve a dashboard-type appearance.
The result is a dashboard worksheet that displays the crucial information I am most interested in viewing at a single glance. This dashboard worksheet can also be easily printed or captured digitally for sending through email, text, or other methods.
About the author
J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a conference presenter, and a JofA contributing editor.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
