Adding Labels with Multiple Lines to the Bubbles in an Excel Bubble Chart

Excel

To make your chart one you can make decisions from it may need more information than normally shows in an Excel chart. With the following trick you can add two or more lines of valuable information to each label. (Remember, don’t create cluttered charts. You have to promise to only use this to add data needed for decision making.)

Dynamic Four Quadrant Matrices use quadrants that change size to reflect a changing mid-point such as an average of the data points. They are valuable in seeing relationships.

Labels with multiple lines of text make the data more intelligent.

 

To use this technique you’ll use concatenation formulas to join together content from multiple cells. (Weird word, but concatenation is easy to use.) You’ll also use a special character to make the text wrap to multiple lines, even within a bubble label. The worksheet for this example looks like this,

Adding labels with multiple lines takes a little trick.

Adding labels with multiple lines takes a little trick.

If you want to see the multiple line labels in the worksheet as shown in column I you need to format column I to allow Word Wrap. They will show as multiple lines in the chart even if you don’t format the cells for word wrap, but it’s a good cross-check to see the multi-line formula working in the worksheet.

The & (ampersand), collocated on the 7 key on the US keyboard, is a shortcut  for the CONCATENATE function that joins text together. In this case & is joining the cell contents from Prod Name and Revenue cells. The formula in I6 is,

=G6&CHAR(10)&TEXT(H6,”$#,###”)

This takes the product name in cell G6 and joins it with a line break, the CHAR(10) function, and then displays the revenue in H6 with US dollar and thousands comma format.

CHAR(10) is the ASCII character for a line feed. Normally a program would send this to the printer to tell the printer to go to the next line before printing. In this case the CHAR(10) is being concatenated (joined) with other text to create a new  line within the cell.

TEXT(reference,format) is a function that formats the numeric or date contents of a cell reference and turns it into text so it can be joined with other pieces of text. The format parameter uses the same formatting codes as a custom number or date format.

Format the cells I6:I12 for Word Wrap so you can see the multiple lines in the worksheet cells. Now, as you enter these formulas in I6:I12 you will see the product name on one line and the formatted US dollar revenue on the second line.

ALERT! In testing multiple line labels with Excel 2010 and Excel 2013 I’ve learned that the multiple line labels used in the Static Four Quadrant – Matrix Model sometimes disappear when the charts are moved between the different versions. This seems to be a bug between the two Excels.

To add these multi-line labels to your bubble chart, just repeat the process for adding custom labels to an Excel 2007/2010 or Excel 2013 bubble chart. You’ll see in the chart the same multiple line labels that are in the cells.

Share the power...