For example, you see the first three dates: What I'm looking for is a chart laying out the dates on the x axis but to scale with actual time. I'm wondering if there's a way to make Excel a bit smarter when it comes to dates. Label Chart Minimum and Maximum Points - Automate highlighting the minimum and maximum in an Excel chart to help focus your readers’ attention.As you can see, the y axis is made from a row of dollar amounts and the x axis is made from dates in the row above it. We can help our user by labelling events to explain those blips or patterns revealed in the data. Labelling Events in Excel Charts - Plotting data over time can reveal patterns and trends, but often blips in the data require further explanation. More Ideas for Highlighting Periods in Excel Charts Step 8: Apply further formatting as desired.įor example, a chart title, legend, remove gridlines etc.: Go to the Tick Marks and Labels settings and set to ‘None’: Set the secondary axis maximum to 1 so that the columns to go to the top of the chart: Here set the gap width to 0%Ĭhange the column series fill colour to something subtle: Select the column series “Shade Month” > CTRL+1 to open the format pane.
The series for shading should be a clustered column on the secondary axis: Set the series you want to plot as a line, in my example it’s the temperatures. Right-click the chart > Change Chart Type… Select Combo. Select the data in columns C:F and insert a line chart. The formula returns TRUE for even months and when plotted in a chart the TRUE is equivalent to 1. I’ve used formulas to list only the first day of the month in column C and every 7 th date in column D to avoid the axis getting cluttered.Ĭolumn F contains a formula that specifies which months are shaded in my example the even months are shaded. Notice the horizontal axis uses helper columns C and D for the month and day which will form a nested axis. I’ll step through the first chart example and you can download the Excel file and or watch the video to learn how to create the others. The secret to this technique is to plot another series on the secondary axis for the highlighting/shading. Step by Step Highlighting Periods in Excel Charts xlsx file please ensure your browser doesn't change the file extension on download.
0 kommentar(er)
