Highlight best week & month in a trend chart

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:

1. 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.
2. Now, find the maximum of this column using =MAX(weekly-total)
3. 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:

1. Original line corresponding to all dates
2. 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!

Calculations Explained:

See this illustration to understand how the calculations for best week & month work.