Creating dashboard - general strategy

[Camylarde]

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!

 

[Jon Peltier]

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.