When you have data that involves time information, you may need some special tricks to get Excel to help you create useful charts. Here are some tips for editing time and date data in an Excel chart. These features apply to Excel 2007-2013, though the specific steps will vary based on your version. Images were taken using Excel 2013 on the Windows 7 OS.
Use an XY – Scatter Chart
By far, the easiest way to chart time data is to use a scatter chart. Scatter charts automatically take date or time data and turn it into a time-scale axis. When you select a date or time range, and the data associated with it, Excel will take its best guess at organizing the information in the chart with the time-scale on the x-axis.
However, Excel’s best guess might not be as useful as you need it to be. In this example, we want to see how, or if, our series data are affected by the time of day. The resulting scatter chart does a nice job of plotting the series data, but the timeline defaults to what seems to be random units of time.
To follow using our example, download excel time chart
To adjust how the x-axis time-scale is displayed:
- Click on the chart to open the Format Chart Area Pane.
- Click on Chart Options and select Horizontal (Value) Axis.
- Click the Axis Option Icon.
- Open the Axis Options dropdown triangle.
- Make changes to the Bounds, Units, and so on to adjust the time-scale to display the chart in the manner you wish.
You may have to play with the Units settings to get your scale to show the time increments you want. In this example, we wanted our unit markers to appear every hour and the chart covered 24 hours of data. So, our Unit setting equals 1 / 24 or .04167. A setting of .25 would show Unit markers at 12:00 a.m., 6:00 a.m., 12:00 p.m. and 6:00 p.m.
The results are a clean, uncluttered chart that plots series data evenly and clearly across a 24 hour period.
Make sure Axis Type is set to Date Axis
When you are creating a line, column or bar chart, Excel will automatically treat date data as a “Date axis”. This means that each data point will be plotted on the x-axis based on linear time, rather than equal distance from each other.
To follow using our example, download excel time chart.xlsx, Line Charts Sheet
AB–Text shows the x-axis set to Text axis and the data points are equally spaced across the chart, even though the dates are not even throughout the month.
AB–Date shows the x-axis set to Date axis and the data points are further or closer together based on when the data was recording during the month. If AB represented bank account withdrawals, you can more easily see in AB-Date that more transactions take place in the middle and end of the month. Follow the steps above to open Axis Options to set your x-axis to Date Axis if Excel’s default chart does not do so.
Hint: If your date data is entered as text instead of the Date format, then the Date Axis option will not work. Change your data to the Date format to take advantage of Date Axis.
What about Time Data on Line Charts?
Sadly, as of Excel 2013, the Date Axis feature does not work on Time data in Line, Column and Bar charts. If you try, you get something like this:
If you need to plot information over time, the easiest solution is to use an XY Scatter Chart and add Lines and Markers to resemble a Line Chart.
What kinds of Time Series Charts do you create in Excel?
Unit setting equals 1 / 24 or .0416 really saved me.
Thanks for tip
I just wanted to thank you for this tip! I have been struggling with this for far too long and found lots of sites that were not helpful at all before bumping into your page. This was a lifesaver! thank you 🙂
Is there a practical way to plot Date AND Time? Thanks nice article
The word “practical” may be stretched here but without reconstructing the entire chart creation, the most direct means to plot Date AND Time is to apply the article’s method to plot a date and then allow the column, itself, to contain both date AND time. Excel already regards Time and Date as the same thing. Even if we only care about the time and only display that, the data itself contains a date (even if we don’t see it). For instance, if we put a time in, by itself, Excel merely assumes a “default date” and attaches it.
Format your data column as a date but allow it to display time as seen below.
Be sure to include the time AND date when the data is entered (if we don’t, Excel assumes the date is 01/01/1900).
Format your Axis the same way but remember that
01/01/2015 01:00 PM is less than 01/01/2015 05:00 PM
and 01/01/2015 11:00 PM is less than 01/02/2015 05:00 AM
Your scatter chart will expand, significantly but it will still accurately represent the data.
Thank you for the tips. You help me to show properly my glucose values to my doctor! 🙂
Fantastic. I would’ve gotten there eventually but first time trying to graph date and time for multiple events. Saved me much headache and time(!)
I was searching for this for a long time.
Thank you very much.
Exactly what I was looking for. I wish your page was at the top of my search results.