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, dont create cluttered charts. You have to promise to only use this to add data needed for decision making.)
Labels with multiple lines of text make the data more intelligent.
To use this technique youll use concatenation formulas to join together content from multiple cells. (Weird word, but concatenation is easy to use.) Youll 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.
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 dont format the cells for word wrap, but its 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,
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 Ive 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 theprocess for adding custom labelsto an Excel 2007/2010 or Excel 2013 bubble chart. Youll see in the chart the same multiple line labels that are in the cells.
Click to share on LinkedIn (Opens in new window)
Click to share on Facebook (Opens in new window)
Click to share on Pinterest (Opens in new window)
Click to email this to a friend (Opens in new window)
Click to print (Opens in new window)
Get 27 Top Chart Tips to Save You Time!
Save time while creating awesome Excel charts. Save time while creating awesome Excel charts. And get free quick, actionable business success tips.
Advanced Excel Training and Support for Free
Awesome Excel Basic and Intermediate Training for Free
10 Top Tips for Creating an Excel Budget or Excel Budget Template
Top 26 Best Excel Conditional Formatting Tips and Tutorial