When you create a chart from data that might have missing dates, you might want Excel to plot the missing dates along the horizontal axis. In Episode 591, we take a look at why Excel sometimes chooses to use a time scale and sometimes does not.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the Mr. Excel netcast. I'm Bill Jelen.
Today we have a question that was sent in if you have a question for the podcast.
Please feel free to either drop me a voicemail or an email and we'll get to you on a future podcast.
Today's questions is pretty interesting.
It came in during one of my power excel seminars.
They were talking about charting data and what to do with missing dates.
So he had a series of dates here August 15th, August 18th, August 20th.
and when he created the chart It's not leaving blanks along the horizontal axis for the missing dates.
He says is there any way to fill that in?
Well excel will do that for you automatically, provided your data is actually formatted as a date.
So the big problem here is that when we go from 8/31 to 9/5, there's four or five days gap there but the chart just makes it seem like that gap is the same as all of the other gaps and the simple solution really is that we're going to take those dates and we're going to convert them from text to date.
You can do that with "Data" "Text to columns" or you can just simply add 0 to everything and we'll use "Data" "Text to Columns" Here, "Data" "Text to columns" I'm going to click next and go to the third step where I'll say that my information is a date in month day year format.
Click finish.
It actually feels in the 2007, the current year for you and as soon as that data contains dates instead of text excel will change the x axis to show us a time scale instead of the absolute information.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Today we have a question that was sent in if you have a question for the podcast.
Please feel free to either drop me a voicemail or an email and we'll get to you on a future podcast.
Today's questions is pretty interesting.
It came in during one of my power excel seminars.
They were talking about charting data and what to do with missing dates.
So he had a series of dates here August 15th, August 18th, August 20th.
and when he created the chart It's not leaving blanks along the horizontal axis for the missing dates.
He says is there any way to fill that in?
Well excel will do that for you automatically, provided your data is actually formatted as a date.
So the big problem here is that when we go from 8/31 to 9/5, there's four or five days gap there but the chart just makes it seem like that gap is the same as all of the other gaps and the simple solution really is that we're going to take those dates and we're going to convert them from text to date.
You can do that with "Data" "Text to columns" or you can just simply add 0 to everything and we'll use "Data" "Text to Columns" Here, "Data" "Text to columns" I'm going to click next and go to the third step where I'll say that my information is a date in month day year format.
Click finish.
It actually feels in the 2007, the current year for you and as soon as that data contains dates instead of text excel will change the x axis to show us a time scale instead of the absolute information.
Hey, thanks for stopping by. We'll see you next time for another netcast from MrExcel.
