9

I have a worksheet that contains a graph with daily sales data. I have a another worksheet that contains a summary of that data and comparison to goals.

What I would like to do is create a dashboard type text box that contains summary data (about a dozen rows, two columns). This box would hover above the worksheet containing daily data, remain anchored to the upper right, and dynamically update as values changed. Here's an oversimplified mockup.

enter image description here

One thing I forgot to add to the mockup: there are already 3 very dense lines of frozen headers, and while it's easy enough to add another line, I really want to get this to stand out for the user.

Excel pipe dream? Or does anyone have any ideas?

4
  • This is a cool feature of Apple's Numbers that I wish Excel supported :(
    – Dai
    Apr 16, 2015 at 6:17
  • @Dai what is this feature called in Apple Numbers?
    – George C
    Jan 11, 2018 at 19:22
  • @GeorgeC in Number you have have objects over others. So it would just be another table which you can put another table. The spreadsheet doesn't spread infinitely unless you want it to. So you initial table will be sized accordingly and have another table that displays the floating data.
    – user14492
    Jun 17, 2019 at 16:20
  • @user14492 the original question stated "This box would hover above the worksheet containing daily data, remain anchored to the upper right…", however adding another table to a sheet does not make it hover, nor will it retain an absolute position on the sheet. It would move around as you scrolled the sheet.
    – George C
    Jun 18, 2019 at 17:47

3 Answers 3

17

You can use a dynamic image instead of a text box. You can build the contents of the "Text Box" on a separate sheet, in regular worksheet cells, using formulas to display the desired text. Then select the cells, copy them as picture (the copy drop-down on the home ribbon) and paste the picture into your dashboard sheet. This produces a static screenshot of the selected cells. To make the image dynamic, select the pasted image, then hit F2 to activate the formula bar. Type a = sign and then select the cells where the original data lives. Hit enter.

Now the image updates every time the original data changes.

The steps above are for 2007 and later. For Excel 2003 and earlier use the camera tool instead of "Copy as Picture". The other steps are the same.

As for anchoring the image at the top, select the cell below the picture, move the selection to column A in the same row and then use View > Freeze Panes.

3
  • +1 Wow. I have never noticed the "Copy as picture" option. That works really slick. I would have thought for sure this would require VBA to complete. The one part missing though is that it scrolls with the cells. The OP asked if it could remain anchored.
    – techturtle
    Feb 6, 2013 at 23:18
  • Thanks. I've added some details for freezing panes to keep the image at the top.
    – teylyn
    Feb 6, 2013 at 23:23
  • Frozen panes were the least preferable, but I did find this... mrexcel.com/forum/excel-questions/… ...which is a neat VB trick to do what I want.
    – dwwilson66
    Feb 11, 2013 at 15:11
1

I found this on another site. Credit to Tom Urtis, Microsoft MVP - Excel.

Maybe Watch Window will help? Select your table, then hit Alt+T+U+W and add that selection to watch. It will float and be visible even if you select other sheets, and you can double-click its window pane to go directly to that sheet and range from anywhere in the workbook.

0

No, it's not a pipe dream. Don't know what version of Excel you are using...some versions make it easier. It's not likely anyone will easily just do for you on SU.

Here is a good source you can start learning how to build dashboards; Excel Dashboards – Tutorials, Templates, & Examples. I found this site to be a very helpful resource for many different projects.

2
  • Excel 2010. I'll dork around with the dashboard site & see what I can dig up. Just looking for a direction. Is this definitely a VB somethin-somethin?
    – dwwilson66
    Feb 6, 2013 at 20:53
  • No always. There are a lot of charting and formatting options available in 2010. You might find some features you desire require some VBA.
    – CharlieRB
    Feb 6, 2013 at 20:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .