Highlight best week & month in a trend chart [tutorials]
When analyzing business data like sales, shop visits or productivity, one of the questions managers always ask is,
What is the best month / week ?
To answer this question, we need to make a chart that looks like this:
How to highlight best week or best month in a chart?
Today, lets learn how to highlight portions of such charts that correspond to best week or best month.
First, an important rule
To highlight data: If you have all the numbers for this chart in a range A1:A100, and you want to highlight the maximum value (or top 10 values), you use conditional formatting.
But with charts: there is no such thing as conditional formatting. So we must imitate the effect. This is done by creating extra series of data (for best week, best month etc.) and formatting it accordingly.
So the rule is To highlight a portion of chart, we need to create another series for that portion and format it the way we want.
Step 1: Create a regular line chart from your data
Lets assume our original data is like this:
Select it and create a line chart to depict the trend of values.
Step 2: Calculate Weeknum
Weeknum will have the week number for each date. This is calculated by =weeknum(date)&”-“&year(date)
Step 3: Calculate Best week portion
For our analysis, lets assume that best week is the week with highest total sales. To do this:
- Add one more column, lets call it weekly total. In this, sum up the total for each week. The formula =SUMIF(weeknum, current-weeknum, values) will give this.
- Now, find the maximum of this column using =MAX(weekly-total)
- Add one more column – best week. This will have NA() for all values except the maximum week. The formula for this would be =if(weekly-total=max-weekly-total,value,na())
Step 4: Add the best week series to chart
Copy the best week column and paste it in your original chart.
At this stage, our chart has 2 series:
- Original line corresponding to all dates
- Best week line corresponding to only best 7 dates
Format this new series in any way you want. And your chart highlights best week.
Step 5: Follow similar process for Best month
To highlight best month, you need to calculate month, monthly total, max-monthly-total & best month values. Once they are ready, just add the best-month values to the chart and you are done!
See this illustration to understand how the calculations for best week & month work.
Download Example Workbook
Click here to download the example workbook & play with it. The workbook contains 2 charts.
- Best week & month highlighted
- Best week & month highlighted along with drop lines
Examine the formulas & resources section of download file to learn more.
Do you highlight portions of charts?
Highlighting a portion of chart is very useful to draw user’s attention. I do this all the time in my dashboards & reports. Unfortunately, there is no automatic way to do this. So we resort to techniques like this.
What about you? Do you highlight portions of charts? What techniques do you use? Please share your ideas & tips using comments.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Dressing Financial Statements – What Motivated Mr. Bean to Defraud Latte?||Can you find that pattern? [Homework] »|