A lot of users have been confronted with an issue with the display of a column chart when label names are excessively long. You have probably seen how chart labels will flip vertically if the labels are over a certain amount of charactors. When this occurs, the data area of the chart becomes significantly smaller making the chart difficult to read.
There are several workarounds that can be used to make the data area of the chart more readable. The simplest solution is to expand the chart.
However, this is not always an option due to the canvas size or by other components occupying space on the canvas.
Another quick option is to change the font size of the labels. This too may present a problem since the labels may be difficult to read.
A better solution may be to word wrap the labels.
Most charts are generated dynamically from the data coming in from a connection. This makes editing the label names less of a usable option. Here, I will display how to word wrap labels by adding a line break between label names by using a formula. There are a number of ways to do this and this is just one example. In this example, we are assuming the labels are comprised of multiple words delimited by a space. However, the same method can be used for other delimiters as well.
Let's start with the basics of the dashboard. In this example, we are populating the chart from data coming in from a connection and displaying the labels and data.
In this example, we will split the labels words into two separate cells and then concatenate them back together along with a line break between each word.
We can add columns to flow the individual words, or map the separated words to some unused cells within the spreadsheet.
Start by highlighting the labels column cells. and click on the DATA tab withing the excel spreadsheet. We will be using the Text to Columns button to do this, but this can also be done using an excel formula. I find the Text to Columns method to be the easiest method.
Click on the Delimited option if it isn't automatically selected and click Next.
Since our labels are space delimited, ensure the Space option is selected. You can see from the preview how the labels will be split. Click Next to continue.
Choose a destination cell for the Label words to flow into. This is a single cell that will be the first cell used for the Labels. Click Finish to complete this Text to Columns step.
Upon completing this step, you will see the new cells populated with the Labels.
Now we will create a formula to concatenate the cells back with a line break between each cell. To do this, we will use the Excel CONCATENATE() function along with the CHAR() function.
Select a cell for the formula. I suggest you use a cell next to the original cells.
Note: Char(10) is the code for a line break on a Windows machine.
Copy the formula down the entire range of Labels.
The final action in this stage is to format the cells to be word wrapped. Highlight the column of concatenated cells right click with your mouse. Select Format.
On the format dialog box, click on the Alignment tab and select Wrap Text. Click finish to apply the formatting.
The final step is to remap your chart to use the new Labels.
Here again is our final result.
These steps are provided as just one possible solution. Hopefully, this can spawn other ideas that can be shared.
- SAP Managed Tags:
18 Comments
