Excel Charts – Do not show empty dates

When we create a new chart in Excel, if the X axis labels are dates, Excel will assume “Automatic” on the Primary axis Category. Please check image below.

altOn this example I have dates 1, 2, 5, 6 and 7 of October. Only this dates have data on it.
altWith the option Category X axis set to Automatic, Excel will produce a chart like Chart 1:


As you can see, Excel has added the dates that where missing between 2 and 5 of October. This dates are not a part of our original table and don’t have value to be charted so I don’t want to display them. This happens because Excel assumes that this is a Time-scale category chart. To remove this dates, go to Chart Options and set the Primary Axis-Category X axis to “Category”. This way we get a chart like Chart 2:


16 comentários:

Andy said...

Cool thanks for this hot tip! This will help people to create more visually pleasing charts. Keep up the great posts!

For more Office information head to http://www.facebook.com/office

MSFT Office Outreach Team

Anonymous said...

Great tip, just what I was looking for! Thanks! :)


Jamal said...


Jennifer Douma said...

This is the info I need but I have Excel 2010 and can't seem to find where/how to make these adjustments. Can you provide updated information?

jppinto said...

I Jennifer,

Thanks for your comment.
On Excel 2010, you need to right click on the chart axis labels and select the option "Format Axis". It will open a dialog box where you should go to the "Axis Options" tab and on the "Axis Type" option, instead of choosing "Automatically select based on data" option you should choose "Text axis" option. This will avoid the empty dates on the chart.

João Pinto

Anonymous said...

Thanks for the great tip, just what I needed! Tried multiple answers and this was by far the best.

Roski Deluge said...

Thank you!

Natalia said...

Thanks you so much João Pinto
for the great tips on Excel 2010!! I was having so much trouble trying to figure it out, thanks for posting! Much appreciated!!

skiwiscott said...

Hi, any chance you know how to do this on Excel 2013? In Axis Options there is no Axis type. Thanks for your time.

Anonymous said...

Cool, just what I was looking for! Thanks :)

Omar said...

Just what I needed, great tip, thanks

Anonymous said...

If i change the axes to text rather than date, it messes with the date order & legend order :(

Tips Excel said...

graph yang menarik
ini mudah dipahami
terima kasih infonya

D Entertainment said...

người đều là Đấu tôn.

- hừ, người của công hôi luyện dược sư thì sao, chẳng lẽ Vạn kim Môn và Cuồng Sư Môn không bằng các ngươđồng tâm
game mu
cho thuê nhà trọ
cho thuê phòng trọ
nhac san cuc manh
số điện thoại tư vấn pháp luật miễn phí
văn phòng luật
tổng đài tư vấn pháp luật
dịch vụ thành lập công ty trọn gói
chém giói?

Nam Cung Lạc Nhan cất tiếng nói.

- Chúng ta đi!

Lê Tiêu Dao cất tiếng nói, sau đó không vui nhìn Dương Thiếu Phong, trong chớp mắt đã tiến lên phía trước.

- Hóa ra là người của Vạn kim Môn và Cuồng Sư Môn.

Nhìn thấy Lê Tiêu Dao và Nam Cung Lạc Nhan rời đi xa, Dương Thiếu Phong cũng không có cơ hội ngăn cản n

Skawen said...

Thank you was very helping!

Harz said...

I had been using MarketXLS for this and it's great.
Stock Charts in Excel