Organizations cannot continue to spend 80% of their time collecting, copying, pasting, and reformatting data, 10% weeding out errors, and then realize in the remaining 10% that the resulting data is not structured in a way that allows it to be analyzed. They cannot rely on undocumented macros and formulas when no one remembers how to maintain them.
Let us focus on the two worst side effects of using Excel for reporting or any kind of Dashboards
Spend enormous time building it
No documentation on the Macros and Formulas
The hard truth is that most organizations are not equipped with managerial information that is properly structured to make prudent business decisions.
Excel is a very flexible tool, but to create a dashboard you can’t just paste some data and add a few charts, can you? The dashboard must be maintained and updated and if you want to minimize the cost associated with that tasks you must impose some structure to your data.
How do you achieve the seperation between Data and Presentation?
Most spreadsheet users have a widely held misconception that their spreadsheet application is some sort of database. It’s not. It is a calculator effective at manipulating and viewing data, but it is not particularly good at storing and managing it in a reliable, secure, scalable way.
What do you get when you use Excel for Data and Presentation?
Big, Bulky and bloated spreadsheets
Hidden worksheets, Hidden Cells, Hidden Rows
A Spider worksheet – linking to multiple files across the network
Undocumented Macros and Functions – Impossible to delegate
Non -Scalable solution
100% chance of mis-representation or missing information
How could you live with false representation or missing information when you cannot control what data is fed to the pretty Pie Chart?
Here are some Best Practices for building dashboards using Excel
- Seperate Data and Presentation layer
- Organize your Excel file into a single worksheet if your data is flat and very less number of columns
- If you are extracting data from a database and want to create quick Prototypes, then pull individual tables into seperate worksheets so it maintains the relational integrity.
- Having seperate worksheets will benefit parameter lookups on your dashboard
- Use the Excel file only as a Data-source
So how do you build a dashboard?
Use a dedicated Dashboard Designer tool that can connect to your Excel Data over ODBC.
You could try one of the Dashboard Builders listed below
- Business Objects (Xcelsius)
- Excel Dashboard Designer
Check out Excel Dashboard Tool if you are looking for the following things
- Auto-Refresh – Build the dashboard and set it on auto-refresh
- Web Based Excel Dashboard – Put the Excel file on the webserver and connect it over http to Excel
- Visual Development – Zero Coding or Macro Development Knowledge needed
- Centralized Presentation – Excel data file on webserver or common file share Drive
- Common Parameters for Dashboard Charts – Wire all objects in the dashboard together
- Unlimited Drilling – Drill from Summary to Detail – No Coding needed
- Build Speedometer, Gauges, Dials, Thermometer charts for your Excel Data
- Other standard charts include, Bar,Pie,Stacked Bar, line, Bubble,Scatter, Polar, Waterfall and many more
- Dashboard Mockups – Build Quick Prototypes or Mockups of dashboards