Creating Metrics in Microsoft Excel
Tracking metrics is critical for the success of every manager, One of the most powerful, fairly easy to design and great data visualization improvement skills are based on excel dashboards. In excel one may decide to virtually design any dashboard according to the way they prefer it to look or how their users wish it could look.
Good thing about it is that as you visualize the way you want it to be, you are at liberty to decide and implement everything according to your imagination. Once you decide what metrics you want to bring on board, there are major areas that you must consider, namely; how your data may be brought into the spreadsheets, ways to manage your data and how to link it to dashboard objects including charts and tables and eventually ways to design dashboard report.
Bringing Data into Excel Dashboard
Excel might be a flexible tool but data entry cannot be done by just pasting and adding a few charts to it. Usually data is not entered directly to the spreadsheet but by connecting the spreadsheet to the data source. The way to communicate to the external database is referred to as the ODBC. If the connection is well established, it’s easy for data in the spreadsheet to change in accordance with the change in source data on refreshing. But this practice requires that minimum amount of data is maintained, any unwanted data may not be used.
Data is imported in two ways namely: the flat style and the pivot table rules. Every user needs to test which of these works best for them because the pivot table makes calculations faster but creates larger files while flat file suffers complex formulas data selection even though it has smaller easy files. Considering these factors, the best dataset has to be large, therefore the best option is to synchronize pivot tables that use same data source as slicers.
Ways to Manage Data and Bring it to Dashboard Objects
Those users who prefer using pivot tables, they can extract data using GETPIVOTDATA function while the flat file users may consider any formula from the DSUM, DGET, VLOOKUP, MATCH, INDEX, SUM, SUMIF, and SUMPRODUCT. But it’s good to consider the fact that many formulas and calculations are dangerous. Less formulas are safe, reliable and easier to maintain. The reduction of formulas can be done using pivot tables.
Documentation Using Named Ranges
The task of documenting ones work may be boring. But this task can be simplified using named ranges which not only assists in making formulas look cleaner but also manage the data in excel.
The named ranges ensures the user’s ability to explore dashboard while building his/her knowledge using available data. A good user considers implementing interaction thus enabling creation of static reports. Creation of interaction charts requires dynamic ranges which may be done using OFFSET0 function. Basically all a user needs is to record a macro, then add the FOR NEXT or the FOR each loop to make simple changes in the cell.