This inserts a carriage return (or line feed, whichever) at that point in the cell’s text.
To do this, put the cursor at the beginning of some of the cells, and hold Alt while pressing the Enter key. Now at least the last month’s label is visible, even if you’re only showing half of the axis labels.įinally, you can show all axis labels, but prevent overlapping them, if you stagger them, showing some higher and others lower.
Make your chart as before with the full set of month abbreviations, set it to show every horizontal label, then delete every other month starting with January.
You can also show alternating month labels, ending with December, as follows. (A single M shows a one- or two-digit numerical month, 1 for January and 12 for December a double MM shows a two-digit numerical month, 01 for January and 12 for December and a quadruple MMMM spells out the entire name of the month.) Quick Number Format Tip #1: If you have a date in a cell, you can display just the three-letter abbreviation if you use a custom number format of MMM, and you can show the one-letter abbreviation if you use a custom number format of MMMMM. Sure, there are 3 J’s, 2 M’s and 2 A’s, but within the context of the entire year, there’s little confusion about this, and it is done frequently. Here I’ve used one-letter abbreviations for the month names. In addition to using Patricia’s workaround above, and using smaller font sizes and shorter axis labels as I’ve done from the start, there are a few other tricks you can try. Showing both December’s gives insight into a year-over-year comparison, at least for December. We still miss every second label, but seeing the last one anchors the visual more effectively. I’ve added the prior December’s data below, and now that we start the alternating axis labels with last December, this December also appears. Patricia mentioned in her article that you can show 13 months of data to make the last month’s label visible. This isn’t terrible, but I find partially rotated text as distracting as fully rotated text, and hardly easier to read. If you show all of the axis labels, they really don’t overlap (right chart below). How about a 45° degree tilt to the labels? Starting with the first chart, if you change to 45° labels, Excel still decides to show only every second label (left chart below). You can format the axis so that it shows every label (in other words, it uses a label interval unit of 1), but now we see that the labels are beginning to overlap (right chart below). This is what Patricia was talking about, but when she said Excel hides the last label, I didn’t realize she meant Excel was hiding alternating axis labels. You can format the labels so they are horizontal, but Excel uses another of its favorite tricks: it only shows alternating labels, starting with the first (left chart below). They fit fine, but they are harder to read than horizontal labels. When I shrink the chart a bit further to 2.5 inches in width, Excel realizes that the axis labels won’t all fit, and it uses one of its favorite tricks: Excel has rotated the axis labels 90° (see chart below). It helps that I’ve used the three-letter abbreviations for the month names, and I’ve shrunk the axis label font size from 9 to 8 points (and I’ve also shrunk the chart title from a ridiculous default of 14 points down to 9 points). The month labels all appear in these charts.
The figure below shows three charts, each 1.5 inches tall, at widths of 5, 4, and 3 inches. The default chart in US versions of Excel is 5 inches wide and 3 inches tall. I’ll illustrate by showing larger charts and a really simple data set. When you shrink the chart, and the labels get close together, Excel does little things to prevent axis labels from overlapping. Excel doesn’t drop off the last category axis label on a chart. She mentions that plotting 13 months of data will avoid this problem. If you make a 12-month chart, as you shrink the chart, for example to fit it into a dashboard, Excel drops off the last month’s axis label. My colleague Patrica McCarthy, the Excel Diva, wrote in 12 Months Data – 13 Months Data in a Chart last week about a problem with Excel charts.