There are several ways you can build dashboards in Excel. This only one of them. But a really easy one, that few people know about!
This tip involves adding the “Camera” button to the quick access toolbar (QAT).
The camera button is not among the commands in the ribbon.
Let’s say there are parts of worksheets that you like and you would like to mash them up in one dashboard.
For just a few cells, you’d probably use formulas to create a live connection to the original cells, and paste formats.
But if you are interested in mashing up several different parts in one “canvas” things get complicated very quickly!
Not with the camera button!
Check the example:
I have a table in Sheet2
I selected some cells
and click the camera button
then I select some other sheet (Sheet1) and mark a rectangle where the live picture will be
Now go back to Sheet2 and edit anything in the group of cells you “captured” and then go back to Sheet1
Get the idea??!!
Select the “picture” and look at the “formula bar”
You can edit the formula bar reference and the edits will be reflected in the “picture” !
Practice with this technique, and start mashing up different parts of a spreadsheet, different sheets, …
You can even right click on the picture and edit the picture format
I changed the transparency of picture 2, and edited the range that it refers to…
Maybe rotate the picture, add a hyperlink or assign a macro…
Double click the picture, and it will take you to the source cells.
Sky is the limit!
(Read Part 2)