Excel Product Metrics Dashboard provides an overview of the sales team’s performance and supports business decisions.
Do you want to get spectacular results in a short time while using Excel Dashboard? Then, you are at the right place! 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.
Today we’ll show you how to create a product metrics dashboard in Excel. Then, we’ll apply user-friendly interface and gauge charts. Finally, to visualize results and marketing trends of 15 products interactively using many-many widgets.
In the focus and the center of the analysis will be the product. Using a dynamic list, we will display all related information as far as possible.
Let’s see the parts of the product metrics dashboard!
Product Metrics Dashboard – Using REPT formula
The REPT function in Excel is a swiss knife. In the example below, let us see how the REPT formula works.
The first parameter means the character that we will repeat. You can use text, numbers, or even a special character.
The second parameter will show how many times we will have the character in one cell: this is the main argument of the function.
If you use a symbol as a first argument of the REPT function, you can create a mini-chart! For example, the REPT (“X”,5) formula is XXXXX, so it is five times X character. Then, 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 as sparklines, they made diagrammatic representations in excel. The chart can be seen in column Graph is based on this Formula.
To highlight the selected product, we use conditional formatting. It is worth using this method to build similar dashboard templates.
Using linked pictures and speedometer
One of the most exciting parts of the product metrics dashboard is the interactive window on the right-hand side.
We have found out that we will indicate the attributes of that product for every chosen product in a bit of info panel.
The ‘database’ worksheet contains all the information we display on the main screen.
To display the correct data on the dashboard worksheet, use the named ranges.
Using the Formula then Name manager menu, we can assign the photographs on the Database worksheet with unique names.
It is worth learning the use of the NamedRange function because we often need it! It is the fastest method to name a cell, range, or array for meaningful names for everyone.
Download the practice file.