How to create a chart with date and time on X axis in Excel?

In Excel, we usually insert a chart to better describe the data. But in some cases, when you create a column/bar/line chart based on a series of date and time, the X axis of the chart may be shown as below screenshot. And in this article, I introduce the way for how to show the date and time on X axis correctly in the Chart.

Create a chart with date and time on X axis correctly

Create a chart with date and time on X axis correctly

To display the date and time correctly, you only need to change an option in the Format Axis dialog.

1. Right click at the X axis in the chart, and select Format Axis from the context menu. See screenshot:

2. Then in the Format Axis pane or Format Axis dialog, under Axis Options tab, check Text axis option in the Axis Type section. See screenshot:

If you use the excel version later than 2010, you can see a Format Axis pane pops out, and check Text axis option under Axis Type in the Axis Options group.

3. Click Close or go back to the chart, then then date and time data has been correctly display in the X axis. See screenshot:

Create a speedometer chart with two steps！

Sometimes, you may want to use a speedometer chart to display the pricessing of a project, but in Excel, to create a speedometer chart needs many steps which is complex and wasting time. Here, the Speedometer chart tool in Kutools for Excel can help you create a standard speedometer chart by two simplesteps. Download this tool with free 30-day trial now.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...

16 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest ( Sign Up? )

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
ab · 4 months ago
Excel menus have changed - this is an out of date article - please update for 2020 .. e.g where is AXIS TYPE now?
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 4 months ago
  
  I did not found Excel 2020, just 365 2019, which menus are the same as 2013,2016 version.
  
  Reply
To post as a guest, your comment is unpublished.
TJ Roberts · 1 years ago
How do I remove days that don't have data in them. Example 8/1-8/31 but not data was captured on 8/3 or 8/4, how do I remove those dates from my graph? They are not in my data table so why is it pulling them?
- Reply
- To post as a guest, your comment is unpublished.
  R · 10 months ago
  
  Open the Excel file that holds the chart from which you want to exclude dates. Click the X-axis, located underneath the chart, so that it becomes surrounded by a small box. Right-click inside the box and choose "Format Axis" from the popup menu. Click "Axis Options" on the left side of the "Format Axis" window. Locate the three "Axis Type" options in the middle of the screen on the right side of the window. Click the radio button next to "Text axis."
  
  Reply
- To post as a guest, your comment is unpublished.
  Sunny · 1 years ago
  
  Hi, TJ Roberts, this tutorial may help you https://www.extendoffice.com/documents/excel/4374-excel-skip-blanks-in-chart.html
  
  Reply
To post as a guest, your comment is unpublished.
Agassi · 1 years ago
Hi, I have an issue here. When I opened "Format Axis" and "Axis Options", there is no "Axis Type" choice here
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 1 years ago
  
  Hi, Agassi, which Excel version you use?
  
  Reply
To post as a guest, your comment is unpublished.
Rafael · 1 years ago
Easy, useful, but i didn't know. Thanks a lot
- Reply
To post as a guest, your comment is unpublished.
KIWI · 1 years ago
But it doesnt show them in right relation to eachother. For example 2 hours and 6 hours are same in chart..
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 1 years ago
  
  Hi Kiwi, maybe you can paint a chart to show me what you want the chart show.
  
  Reply
To post as a guest, your comment is unpublished.
Vilmos · 1 years ago
This is not OK. It does not show correct order by time just puts the timestamps there as a text in equal distances.
- Reply
- To post as a guest, your comment is unpublished.
  Jeff · 1 years ago
  
  I'm having the same issue. It would be better for the x-axis to show the time not as evenly spaced category labels, but in actual proportion to the durations between the time values. Example: Here's data on world population by year (note that the intervals between years are not evenly spaced):
  
  Year: Population
  1804: 1,000,000,000
  1927: 2,000,000,000
  1960: 3,000,000,000
  1989: 5,000,000,000
  2000: 6,000,000,000
  2017: 7,500,000,000
  
  An Excel line chart depicts this data on a fairly even slope (I'd post a picture, but this interface only allows text); however, the generally declining time intervals SHOULD yield a graph that looks much more exponential.
  
  I was able to "fake" it by inserting rows in the data table for each year with no data (e.g., 122 rows for the years between 1804 and 1927 exclusive, 32 rows for the years between 1927 and 1960, etc.), so my graph looks more proportional now, but this has its own disadvantages. There are MANY empty rows and much wasted table space. I'd love to learn a better way to get Excel to treat time values in the x-axis as actual time values, rather than mere category labels
  
  Reply
- To post as a guest, your comment is unpublished.
  Sunny · 1 years ago
  
  Hello, Vilmos, create the chart after you order the time data, then the chart will display the time in order.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  help · 1 years ago
  
  Nope, you didnt' understand him. It's not proportional.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  ahmad · 1 years ago
  
  exactly.
  i am looking desperately for that.
  do you know any other software to create that graph?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  Nicole · 1 years ago
  
  I did it in Google Sheets, and it was pretty straight forward.
  
  It was able to graph using a column with DateTime values for the x-axis, with appropriate spacing, and without having to resort to treating that column as text. It worked with the cells in that column formatted as either a date, or in datetime format. There weren't many options for the spacing on the x-axis lables, but at least you can hover over the trends and see the x-axis value for a given point, in whatever way the cells are formatted. Frustrating that there doesn't seem to be a way in Excel.
  
  Reply