12

I am not able to wrap the text of x-axis labels on Excel 2007 charts. As a result, the axis labels are overlapping onto each other. Does anyone know how can I wrap these labels?

psychonomics
  • 714
  • 4
  • 12
  • 26
soldieraman
  • 2,630
  • 7
  • 39
  • 52

3 Answers3

11

After a lot of googling we found CHAR(10) to be the solution of our problems. As long as you have the value like:

="First Line" & CHAR(10) & "Second Line"

it will break X-Axis, Y-Axis, Chart Header etc

nghauran
  • 6,648
  • 2
  • 20
  • 29
soldieraman
  • 2,630
  • 7
  • 39
  • 52
  • 2
    I should note that I found you only have to do this on one label (maybe your longest), and it seems that it then automatically wraps the other labels automatically. – rryanp Mar 14 '13 at 17:38
  • In some cases I've used `CHAR(13)` instead. – airstrike Jul 24 '17 at 21:38
7

Actually, the easiest way is to force a break in the spreadsheet cell with ALT+ENTER. This will carry over to the axis labels.

  • And if you are using Excel on Mac, do Ctrl + Cmd + Enter to add line break in the cell which will also be reflected in the chart. – Anupam Mar 27 '17 at 12:35
4

After a lot of frustration, here's how I tricked Excel:

  • Select the data for the x-axis in the spreadsheet.
  • Create a new custom format, entering CTRL+j where you want a line break. (it may appear as a box or a boxed question mark, which I'll refer to as [] and [?]).
  • On the chart, open the Format Axis dialog for the x-axis, and select Number from the list on the left.
  • Select Custom in the Category list, and your new custom format should be available in the Type list. Select it and close the Format Axis dialog.
  • Change the format in the spreadsheet back to whatever you'd like.

For example, I have datetime values on the x-axis of a scatter plot, and I want the x-axis labels to look like

Thursday
April 14
12:55 PM

So, following the steps above, I enter dddd[CTRL+j]mmmm d[CTRL+j]h:mm AM/PM as a custom format in the spreadsheet. It looks like dddd[]mmmm d[]h:mm AM/PM in the Type list, and Thursday[?]April 14[?]12:55 PM in the spreadsheet.

NOTE: if Wrap Text is set for the columns, the [?] will be replaced with a line break, but the column heights will not be automatically adjusted, so it hides all but the first row.

chezy525
  • 4,025
  • 6
  • 28
  • 41