Removing the Date Gaps
We’re going to tackle the date issue first. The secret to Excel charting is that it always tries to guess what sort of chart you need based on how the data is formatted. Standard date formats get turned into a date series axis in Excel, we we’re going to trick the program into thinking they’re not dates. We’re going to do this by converting the dates to text so Excel can’t tell how to sort them. We’ll keep the original dates available for us to use in sorting, etc.
Insert a new column in between the Date and Percentage. I’m calling mine Date Text. We’re going to use the TEXT() function to convert the numbers to text in Excel, which has the following syntax:
The value in this case is the date field from column A.
The format_text is the format of the number before it is converted to text. In our case, we’re going to use an abbreviated Month Day format.
In column B, insert the following formula in cell B2:
We are taking the date in the A column and using standard Excel date formatting to specify that we want 9/4/2012 to become “Sept. 4”. You can drag the formula down to cover all the rest of the date rows.
Excel will read these “dates” as text and put them in a nice row in the chart:
Things are looking up already…
Get the latest Excel tips and tricks by joining the newsletter!
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.
Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!
Other posts in this series...
- Save a Broken Chart with Switch Columns/Rows in Chart Tools
- Build a Better, Cleaner, More Professional Line Chart
- How To Add an Average Value Line to a Bar Chart
- How to Add a Vertical Line to a Horizontal Bar Chart
- How to Add Totals to Stacked Charts for Readability
- How to Show Percentages in Stacked Bar and Column Charts
- Building Charts with Multiple Series and Custom X-Axis
- How to Create Waterfall Charts in Excel