It is a basic requirement in making a Product Metrics Dashboard that is should support important business decisions.
You want to reach spectacular result under short period of time while using Excel Dashboard?
Then this is the place for you! After some simple steps you can make a report like this too.
We have talked many times about the key performance indicator’s definition but it is always good to revise a subject many times. If you are further interested in the subject, then you can find more information here.
Today we will show you the making of a product metrics dashboard with a very user-friendly interface with additional gauge chart.
We will visualize in an interactive manner results and trends regarding the marketing of 15 products using many-many widgets.
In the focus and the center of the analysis will be the product. Using a dynamic list we will try to display all related information as far as possible. Let’s see the parts of the excel dashboard!
Product Metrics Dashboard – Using REPT formula
The REPT formula is in Excel tools for a long time now, we don’t even know how long it is part of the program.
Let’s say a couple of words about the operation of this little formula.
The first parameter means the character that we will repeat. This can be text, number or even a special character.
The second parameter will show how many times we will have the above mentioned character in one cell. The latter is a very important characteristic of the REPT formula. We can say that with a use of only one cell we create a mini-chart!
For example the result of REPT (“X”,5) formula is XXXXX, so it is 5 times X character. Using the “ALT + 219” combination we display the ‘black square’ symbol, and by writing it in one cell we get the diagram shown in the picture.
When there were no such tools than sparklines, they were making diagrammatic representation in excel. The chart can be seen in column Graph is based on this formula.
To be able to well distinguish the currently highlighted product form the others, we use conditional formatting. It is worth to use this method when we make similar dashboard templates.
Apply Pictures and Speedometer
One of the most interesting parts of the product metrics dashboard is the interactive window on the right hand side.
We have found out that for every chosen product we will indicate the attributes of that product in a little info panel.
These are the following: name of the product, the manager of the product and his or her photograph, customer satisfaction of the product on a scale from 0 to 100. By using excel sparklines feature we will put the information of the last 12 months under the picture.
The ‘database’ worksheet contains all the information we have to display on the main screen.
On order for us to display the correct data on the dashboard worksheet we use the “Named Range” tool.
Using the ‘Formulas’ then ‘Name manager’ menu we can assign the photographs on the ‘Database’ worksheet with unique names.
It is worth to learn the use of the ‘Named Range’ menu because we often need it! It is a very quick method to name a cell, range or array for meaningful names for everyone.
The KPI dashboard here demonstrated downloadable here.