Creating dashboard - general strategy

Camylarde

New Member
Joined
Feb 13, 2018
Messages
1
Hello,

I stand before a task to create a dashboard showing several metrics derived from a single large (expanding 100s rows x 300 fixed collums of data) table. I do have an idea which metrics I need to track, and how to individually get them from the data, but I struggle with defining a strategy to get all the graphs into one sheet, and have them all updated at all times when someone visits the sheet.

Because I do need to construct separate tables to calculate some metrics from the underlying data (thats separate sheets for sure and its ok), I am afraid that the expanding initial table, plus potentially expanding derived tables will be a major problem for me before I put everything together.

Any general tips, how these things may be constructed in excel? Is this all about using (the excel) tables, then linking pivot charts and having a macro making sure they stay updated?

Any tips toward dashboard building strategy, or the components to use would be greatly appreciated.

Thanks!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Keep in mind that the dashboard should be a one-screen view of what's important. It may have links to drill down into details of some broader measures, and it may have links to get to the data.

The dashboard should be free from clutter. It may have tabular data, or charted data, or both. In all cases, information should be presented cleanly, without excessive formatting.

The general structure is that the input data should go onto one or more worksheets, maybe one sheet for each table to allow room for expansion.

Calculations and manipulations (pivot tables, etc.) should go onto their own worksheets, linking back to the original data worksheet(s).

The dashboard worksheet should in turn link to the calculated/manipulated data. This is usually the only readily visible worksheet.

VBA can be used to make sure things update properly, and only the sheets that should be visible are visible.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top