TIP # 1: How to Create Mini Pie Chart Overlays on Your Dashboards
Adding mini chart overlays to your dashboard allows you to associate the numbers in your dashboard to a visual display. Here is a birds eye view of the steps required to do this. For more details be sure to checkout the video above.
STEP #1: In this dashboard, you want to display the total percentage of sales for the top or bottom customer against the total sales for the month selected by the user. To do this, automate your data calculations based on what month is selected by the user. Calculate the highest sales and lowest sales by using the max and min values respectively and then by dividing each by the total sales for the month selected. Once you’ve got that calculation, in another cell subtract the percentage calculated by 1 and this will give you the remaining percentage for each the highest/lowest sales.
STEP #2: Now to create the graph, select the cell with the percentage of total sales for the highest value sold and select the cell that contains the value of 1 – that percentage. Go to insert and then insert a pie chart graph.
STEP # 3: Select the chart background and remove the outline and background by selecting no fill or no color. Next you can alter and adjust the pie chart. The value remaining percentage should be set to also have a no fill background but you’ll include an outline. For the percentage of total sales displayed, you’ll choose a background fill and make the outline the same color as the other percentage.
TIP # 2: How to Add Data Callouts to Your Excel Dashboard Charts for High/Low Values
Adding data callouts to your dashboard chart to show the highest and lowest values is a great way to quickly point out in what months of the year you’ve performed well or poorly. Let’s look at the step by step process for doing this.
STEP #1: You’ll need 2 additional columns or series that will be included on your dashboard chart. These will be added to your calculations tab of the worksheet.
STEP #2: Use the IF formula to display #N/A (using the NA() formula) if the associated value for the sales for the given month is not the highest (under the highest column data set). The IF formula will use the rank formula to right of your data set and will use the highest rank value to find this value (see video). Apply this same logic to the lowest value sold column. Note that the NA() formula is what is going to ignore those data points on your chart instead of putting a value of zero in.
STEP # 3: Add the lowest and highest value sold data series’ to your dashboard chart. We will use a line chart again for the type but we will only display the point. Select the data points and go to add data callout. You can the adjust and change the mark fill and the callout fill and color to be green and red depending on what value you are display (i.e. lowest or highest).
TIP # 3: How to Add and Use Excel Data Validation in Your Dashboards
Using data validation in your dashboards is a great way to identify whether a certain condition is met or not. In this dashboard, I want to know whether or not my raw data set is including more than 12 months of data. If it is, I want to display a red X and if it isn’t, I want to display a green check mark. Here’s how to do it.
STEP #1: In a cell on your calculations tab, go to insert and then symbol. Find the check mark and the X under the wingdings 2 font. Once you’ve added them, you can the change the font in the cell to get the standard font characters that will need to be used on the front end dashboard.
STEP #2: Use the IF formula to determine if our list of months name range contains more than 12 months of data. If the count is greater than 12 then display Yes, if not display No.
STEP # 3: Create a separate IF formula that uses the no or yes value in the last cell to display the wingdings X character if the value is Yes and the check mark if the value is No.
STEP # 4: Pull the formatting over to your dashboard cells where you want this displayed using name ranges for the cells on the calculations tab. Now you can change the color of the Wingding 2 font display by using conditional formatting on the cell. If the value in the cell refers to a check mark, display the value in green and if it’s an X, display the character in red.
The Excel Sales Trend Dashboard is a great way to capture your company performance in a minimalist way. It helps you understand where you’ve performed well and poorly and at the same time allows you to identify your best and worst customers quickly and efficiently. Downloading the dashboard is a great way to help you also learn some of the techniques used in the dashboard to get yourself acquainted with some useful Excel techniques. Whether you’re looking to learn or looking to cut time off your current processes, this dashboard is for you.
Trackbacks/Pingbacks