Looks and can be tricky
but you can create an exact match with just 20 steps once you understand the mechanics.
Steps (Steps use a new workbook titled Book1):
1) Make the following Named Ranges for the following:
This is one of the tricky parts and a critical step:
.........PlotData = C6:c16 then hold
crtl key down and select d6:d16, all the way until and including n6:n16 - Note C6 and C16 are above and below the data this creates the breaks by month
.........PlotAvg = c21:c31 then hold
crtl key down and select d21:d31, all the way until and including n21:n31 - Note C21 and C31 are above and below the data this creates the breaks by month
.........PlotAvg = c34:c44 then hold
crtl key down and select d34:d44, all the way until and including n34:n44 - Note C34 and C44 are above and below the data this creates the breaks by month
2) Create a COLUMN Chart using C1:N2 as your data range - This will show the months without data since the columns = 0 value
3) Add a Series 2 to the chart series values =book1!PlotData
4) Move this Series 2 to the 2nd axis
5) Change Chart Type for Series 2 to a Line with Markers chart. (This will cause Your xaxis labels will look smashed together)
6) Show Secondary Horizontal Axis = Left to Right (Your x axis labels are now back in position)
7) Format Primary Vertical Axis to No Tick Marks and No Axis Labels (IMPORTANT - Do NOT DELETE but instead hide THE PRIMARY Vertical Axis as this will probably crash your Excel application - happened to me each time on 2 different machines)
8) Format Secondary Horizontal Axis to No Tick Marks and No Axis Labels AND Vertical Axis Crosses at Category Number 1
9) Format Lines series Line Color and Line Markers
10) Add a Series 3 to the chart series values =book1!PlotAvg
11) Change Chart Type = Line (no markers)
12) Format Lines series Line Color
13) Add a Series 4 to the chart series values =book1!PlotTrend
14) Change Chart Type = Line (no markers)
15) Format Lines series Line Color
16) Delete Legend
17) Add Chart Title
18) Format Plot Area
19) Format Chart Area
20) Add Text Box
See told you, just 20 steps
PS: I will create a blog post and video next week detailing each step.
Steve=True
www.exceldashboardtemplates.com
