Steps to Label Specific Excel Chart Axis Dates. The trick here is to use labels for the horizontal date axis. We want these labels to sit below the zero position in the chart and we do this by adding a series to the chart with a value of zero for each date, as you can see below: Note: if your chart has negative values then set the ‘Date Label. Mar 29, 2018 With an XY chart you must use the Excel date number. Suppose in the chart in the second snip you want the min to be 1 Jan 2018. Type this date into a cell (suing what ever date format is normal to you), format that cell as number to see 43101; copy and paste that number to the Min box or just type that number in the box.

How to change date format in axis of chart/Pivotchart in Excel?

In general, the dates in the axis of chart or Pivot Chart are shown as '2014-02-15'. In some cases it may require to ignore the Year in the dates such as '2/15', or only keep the Month in the dates like 'Feb', do you have any idea to get it done? This article has provided two methods to change date format in axis of chart or Pivot Chart in Excel.

• Change date format in axis of Pivot Chart in Excel
• Change date format in axis of normal chart in Excel

Change date format in axis of Pivot Chart in Excel

Supposing you have created a Pivot Chart as below screen shot shown, and you can change the date format in the axis of this Pivot Chart as follows:

1. In the Pivot Chart, right click the Date filed button and select Field Settings from right-clicking menu.

Note: In Excel 2007, you can't find out the field button in the Pivot chart, but you can click the Date Filed in the Axis Fields (Categories) section of PivotTable Field List pane and select Filed Settings from drop down list (See below screen shot). And this method also works well in Excel 2010 and 2013. See screenshots above.

2. In the coming Field Settings dialog box, click the Number Format button.

3. Now you get into the Format Cells dialog box, click to highlight Custom in the Category box, and then type the format code into the Type box, and click the OK button. (Note: If you want to show 2014-01-03 as 1/3, type m/d into the Type box; if you want to show 2014-01-03 as Jan, type mmm into the Type box.)

4. Click the OK button in Field Settings dialog box. Then you will see the dates in the axis of Pivot Chart are changed to specified format at once. See below screen shots:

Change date format in axis of chart in Excel

For example there is a chart as below screen shot shown, and to change the date format in axis of normal chart in Excel, you can do as follows:

1. Right click the axis you will change data format, and select Format Axis from right-clicking menu.

2. Go ahead based on your Microsoft Excel version:
(1) In Excel 2013's Format Axis pane, expand the Number group on the Axis Options tab, enter m/d or mmm or others into the Format Code box and click the Add button.
(2) In Excel 2007 and 2010's Format Axis dialog box, click Number in left bar, type m/d or mmm or other codes into the Format Code box, and click the Add button.

Note: If you enter m/d into the Format Code box, the dates in selected axis will be changed to the format of 1/1, 2/1,…; if you enter mmm into the Format Code box, the dates in axis will be changed to the format of Jan, Feb, ….

3. Close the Format Axis pane/dialog box. Then you will see the dates in the chart axis are changed to the specific format at once.

Demo: Change date format in axis of chart or Pivotchart in Excel

• 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.

How To Plot Excel Chart With Date Axis Excel

• 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!

• To post as a guest, your comment is unpublished.
  Thank you so much! Couldn't find the solution anywhere else
• To post as a guest, your comment is unpublished.
  Number format doesn't exist in the field settings in Excel 2016/O365.

  • To post as a guest, your comment is unpublished.
    Hi Ben, In Excel 2019, 2016, or 365 you can also change the number format of Y axis in a PivotChart.
    If you cannot find out the Date field button in the PivotChart, you can click the PivotChart to enable the PivotChart Fields pane, and then click the arrow right to the Date field, and select Field Settings from the drop-down list, and then change the number format in the popping out Field Settings dialog (same as that in Step 2 of method 1)
    

    • To post as a guest, your comment is unpublished.
      In the Field Settings there is no 'Number Format' button in Excel 2016.
      
• To post as a guest, your comment is unpublished.
  Great stuff!!! Really useful and helpful.
• To post as a guest, your comment is unpublished.
  I have Excel 2013 and need to change the y-axis date format from MM/DD/YY to MM/YY.
  The y-axis is a date which Excel forces this date to be auto-calculated.
  My units are set to 30.5 increments to increment by month.
  The date does not always show the first day of the month due to the days variation (29, 30, 31).
  If I can change the date format from mm/dd/yy to mm/yy the auto-calculate date will work for the graph.
  Due to the Excel capability of the end-user of this graph, I need to avoid using VBA or macros.
  I cannot find a way to change an auto-generated number format in Excel 2013.
  Help?
• To post as a guest, your comment is unpublished.
  Lovely... Trick and Its a great tip.. !

Date Axis Bounds Excel

You’ve built your chart, it all makes sense and suddenly you look at your horizontal axis, and the date axis is wrong. This happens a lot. You expect to see the dates you have in the data but different dates are showing, normally the beginning of month date instead of the end of month date.

Table of contents

YouTube Date Axis in Excel Chart is wrong

Date Axis in Excel Chart is wrong- the issue

Format Date Axis Excel Chart

As shown below, the data clearly shows month end dates (31 Jan 2016) but the chart is showing the beginning of the month (1st Jan 2016) on the horizontal axis of the chart. This may seem like an error but it is actually a feature. The key is to understand that if Excel sees a valid date (e.g. the 31 Jan 2016) it will open up new options in some of its tools and charts are one of them.

If you right click on the horizontal axis and choose to Format Axis, you will see that under Axis Type it has 3 options being Automatic, text or date. As we have entered valid dates in the data the Automatic chooses dates and therefore you get the option in the second box. If Excel sees valid dates it will allow you to control the scale into days, months or years.

Date Axis Excel 2016

So if, instead of having the Base unit as Months, I change it to Days the Chart will adjust the axis to show the data points on a chart that has a daily scale. So below you can see the gaps in days between the points.

If I choose years it collapses the time period into years.

How to force Excel to use your typed in dates in a chart

Although this feature is useful, sometimes you just want Excel to show the dates you typed.

In order to do this you just need to force the horizontal axis to treat the values as text by

• right clicking on the horizontal axis,
• choose Format Axis
• Change Axis Type to be Text

Note that you immediately lose the scaling options and the date scale puts in exactly what is in the data, onto the horizontal axis.

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Related