A dashboard is “an easy to read, often single page, real-time user interface, showing a graphical presentation of the current status (snapshot) and historical trends of an organization’s key performance indicators (KPIs) to enable instantaneous and informed decisions to be made at a glance.” https://en.wikipedia.org/wiki/Dashboard_(management_information_systems)
We create Dashboards in Excel to present your organization’s KPIs calculated using data stored in Excel file itself or in external Excel/CSV/Access files or in remote MySQL/SQL databases or on web sites (actual currency rates or stock prices).
Depends of your business goals and specific of your data different types of pivot tables, charts, drawings may be included into a dashboard, for example:
- clickable maps (region map with clickable countries or map of USA with clickable states and so on) – when a country/state clicked all dependent tables and charts automatically being refreshed with data specific for the country/state selected)
- clickable drawings – when any element of a drawing clicked dependent dashboard being automatically refreshed
- clickable charts and clickable tables – when any cell (age range) on the left top table selected or any bar on histogram chart clicked data in dependent tables and charts being automatically refreshed. if a cell selected then this cell and linked bar are being highlighted with color, when a bar clicked then the bar and linked cell are being highlighted
- ribbon controls for a dashboard – ribbon drop downs, buttons and toggle buttons can be used to control a dashboard. Table and charts are being refreshed when user change values in list boxes or toggle any “formula” button. Hide button hides specific part of the dashboard.
To be really interactive dashboards have to work very fast. Other developers use Excel Pivot Tables and it may take minutes to updated a dashboard or it even may freeze or crash Excel. We use SQL to prepare data for dashboards even if data source is an Excel/CSV/Access file. It takes just a moment to refresh a dashboard based on dozens of raw data sheets with millions rows of data and with multiple filters applied. In some cases additional macro with SQL queries may clean raw data and/or build additional data layer to improve dashboard performance.