Home > Articles > Business & Management > General Business

This chapter is from the book

Using a Chart to Communicate Effectively

A long time ago, in a past job, a McKinsey & Company team investigated opportunities for growth at the company where I was employed. I was chosen to be part of the team because I knew how to get the data out of the mainframe.

The consultants at McKinsey & Company knew how to make great charts. Every sheet of grid paper would be turned sideways, and they would build a landscape chart that was an awesome communication tool simply by using a pencil. After drawing the charts by hand, they would send off the charts to someone in the home office who would generate the charts on a computer. This was a great technique. Long before touching Excel, someone would sit down to figure out what the message should be.

You should do the same thing: Even if you have data in Excel, before you start to create a chart, it's a good idea to analyze the data to see what message you are trying to present.

The McKinsey & Company group used a couple of simple techniques to always get the point across:

  • To help the reader interpret a chart, include the message in the title. Rather than use an Excel-generated title such as "Sales," you can actually use a two- or three-line title such as "Sales have grown every quarter except for Q3, when a strike impacted production."
  • If the chart is talking about one particular data point, draw that column in a contrasting color. For example, all the columns might be white, but the Q3 bar could be black. This draws the reader's eye to the bar that you are trying to emphasize. If you are presenting data on screen, use red for negative periods and blue or green for positive periods.

The following sections present some Excel trickery that allows you to highlight a certain section of a line chart or to highlight a portion of a column chart. In these examples, you will often be spending some time up front in Excel, adding formulas to get your data series looking correct before creating the chart.

Using a Long, Meaningful Title to Explain Your Point

If you are a data analyst, you are probably more adept at making sense of numbers and trends than are the readers of your chart. Rather than hoping that the reader will discover the message you are trying to make, why not add the message as the title of the chart?

Figure 3.25 shows a default chart in Excel. Both the legend and the title use the "Market Share" heading from cell B71. You certainly don't need those words on the chart twice.

Figure 3.25

Figure 3.25 By default, Excel uses an unimaginative title taken from the heading of the data series.

You follow these steps to remove the legend, add data labels, and add a meaningful title:

  1. From the Layout ribbon, choose Legend, None and then choose Data Labels, Outside End.
  2. Click the title in the chart. Click again to put the title in Edit mode.
  3. Backspace to remove the current title. Type Market share has improved, press Enter, and type 13 points since 2002.
  4. To format text while in Edit mode, you would have to select all the characters with the mouse. Instead, click the dotted border around the title. The border becomes solid. You can now use the formatting icons on the Home ribbon to format the title.
  5. On the Home ribbon, choose left-justified and then click the Decrease Font Size button until the title looks right.
  6. Click the border of the chart title and drag it so the title is in the upper-left corner of the chart.

The result, shown in Figure 3.26, provides a message to assist the reader of the chart.

Figure 3.26

Figure 3.26 You can tell the reader the point of the chart with the title.

Resizing a Chart Title

When you click a chart title to select it, a bounding box with four resizing handles appears. At least they look like resizing handles; actually, they are not. You do not have explicit control to resize the title. It feels like you should be able to stretch the title horizontally or vertically, as if it was a text box, but you cannot. The only real control you have to make a text box taller is by inserting carriage returns in the title, but you can only type carriage returns when you are in Text Edit mode.

The first click on a title selects the title object. A solid bounding box appears around the title. At this point, you can use most of the formatting commands on the Home ribbon to format the title. You click the Increase/Decrease Font Size buttons to change the font of all of the characters. Excel automatically resizes the bounding box around the title. If you do not explicitly have carriage returns in the title where you want the lines to be broken, you are likely to experience frustration at this point.

When you have the solid bounding box around the title, you could carefully right-click the bounding box and choose Edit Text. However, simply left-clicking a second time inside the bounding box also puts the title in Text Edit mode, as indicated by a dashed line in the bounding box. In Text Edit mode, you can select specific characters in the title and then move the mouse pointer up and to the right to access the mini toolbar and the formatting commands available there. You can edit specific characters within the title in order to create a larger title and a smaller subtitle, as shown in Figure 3.27.

Figure 3.27

Figure 3.27 By selecting characters in Text Edit mode, you can create a title/subtitle effect.

You cannot move the title when you are in Text Edit mode. To exit Text Edit mode, you right-click the title and choose Exit Edit Text or simply left-click the bounding box around the title. When the bounding box is solid, you can click anywhere on the border except the resizing handles and drag to reposition the title.

Deleting the Title and Using a Text Box

If you are frustrated that the title cannot be resized, you can delete the title and use a text box for the title instead. The title in Figure 3.28 is actually a text box. Note the eight resizing handles on the text box instead of the four resizing handles that appear around a title.

Thanks to all these resizing handles, you can actually stretch the bounding box horizontally or vertically.

Figure 3.28

Figure 3.28 Instead of a title, this chart uses a text box for additional flexibility.

To create the text box shown in Figure 3.28, you follow these steps:

  1. Delete the original title by choosing Chart Title, None from the Layout ribbon. Excel resizes the plot area to fill the space that the title formerly occupied.
  2. Select the plot area by clicking some whitespace inside the plot area. Eight resizing handles now surround the plot area. Drag the top resizing handle down in order to make room for the title.
  3. On the Insert ribbon, click the Text Box icon.
  4. Click and drag inside the chart area to create a text box.
  5. Click inside the text box and type a title. Press the Enter key to begin a new line. If you don't press the Enter key, Excel word-wraps and begins a new line when text reaches the right end of the text box.
  6. Select the characters in the text box that make up the main title and use either the mini toolbar or the tools on the Home ribbon to make the title 18 point, bold, and Times New Roman.
  7. Select the remaining text that makes up the subtitle in the text box and use the tools on the Home ribbon to make the subtitle be 12 point, italics, Times New Roman.

Microsoft advertises that all text can easily be made into WordArt. However, when you use the WordArt drop-downs in a title, you are not allowed to use the Transform commands found under Text Effects on the Drawing Tools Format ribbon. When you use the WordArt menus on a text box, however, all the Transform commands are available (see Figure 3.29).

Figure 3.29

Figure 3.29 Using a text box instead of a title allows more formatting options.

Because a text box is resizable and that you can use WordArt Transform commands, a text box works perfectly: You can move or resize the chart, and the text box moves with the chart and resizes appropriately.

Highlighting One Column

If your chart title is calling out information about a specific data point, you can highlight that point to help focus the reader's attention on it. While the tools on the Design ribbon don't allow this, you can easily achieve the effect quickly by using the Format ribbon.

To create the chart in Figure 3.30, you follow these steps:

  1. Create a column chart by choosing Column, Clustered Column from the Insert ribbon.
  2. Click any of the columns to select the entire series.
  3. On the Format ribbon, choose Shape Fill, White. At this point, the columns are invisible. (Invisible bars are great for creating waterfall charts, discussed in Chapter 4, "Creating Charts That Show Differences.") However, in this case, you want to outline the bars.
  4. From the Format ribbon, choose Shape Outline, Black. Choose Shape Outline, Weight, 1 point. All your columns are now white, with black outline.
  5. Click the Friday column in the chart. The first click on the series selects the whole series. A second click selects just one data point. (If you accidentally click outside the series, you might inadvertently deselect the series.) If all the columns have handles, click Friday again.
  6. From the Format ribbon, choose Shape Fill, Black.
  7. On the Layout ribbon, turn off the legend and the gridlines.
  8. Type a title, as shown in Figure 3.30, pressing Enter after the first line of the title. On the Home ribbon, change the title font size to 14 point, left-aligned.
  9. Right-click the numbers along the vertical axis and choose Format Axis. Change Major Unit to Fixed, 500.
Figure 3.30

Figure 3.30 The column for Friday is highlighted in a contrasting color, and it is also identified in the title.

The result is a simple chart that calls attention to Friday sales.

Replacing Columns with Arrows

You can use columns shaped like arrows to make a special point. For example, if you have good news to report about consistent growth, you might want to replace the columns in the chart with arrow shapes in order to further indicate the positive growth.

You follow these steps to convert columns to arrows:

  1. Create a column chart showing a single series.
  2. In an empty section of the worksheet, insert a new block arrow shape. From the Insert ribbon, choose Shapes, Arrows, Up Arrow. Click and drag in the worksheet to draw the arrow.
  3. Select the arrow. Press Ctrl+C to copy the arrow to the Clipboard.
  4. Select the chart. Click a column to select all the columns in the data series.
  5. Press Ctrl+V to paste the arrow. Excel fills the columns with a picture of the block arrow.
  6. If desired, choose Format Selection from the Format ribbon. Reduce the gap setting from 150% to 75% to make the arrows wider.

The new chart is shown in the bottom half of Figure 3.31. After creating the chart, you can delete the arrow created in step 2 by clicking the arrow and press the Delete key.

Figure 3.31

Figure 3.31 Arrows further emphasize the upward growth of sales.

Highlighting a Section of Chart by Adding a Second Series

The chart in Figure 3.32 shows a sales trend over one year. The business was affected by road construction that diverted traffic flow from the main road in front of the business.

Figure 3.32

Figure 3.32 It would be best to highlight the road construction months in the chart to further emphasize the title.

The title calls out the July and August time period, but it would be cool to actually highlight that section of the chart. You follow these steps to add an area chart series to the chart:

  1. Begin a new series in column C, next to the original data. To highlight July and August, add numbers to column C for the July and August points, plus the previous point, June. In cell C7, enter the formula of =B7. Copy this formula to June, July, and August.
  2. Click the chart. A blue bounding box appears around B2:B13 in the worksheet. Drag the lower-right corner of the blue bounding box to the right to extend the series to include the three values in column C. Initially, this line shows up as a red line on top of a portion of the existing blue line.
  3. On the Layout ribbon, use the Current Selection drop-down to choose Series 2. This is the series you just added.
  4. While Series 2 is selected, choose Design, Change Chart Type. Select the first area chart thumbnail. Click OK. Excel draws a red area chart beneath the line segment of June through August.
  5. On the Format ribbon, use the Current Selection drop-down to reselect Series 2. Then choose Shape Fill, White, Background 1, Darker 25%.

The top chart in Figure 3.33 shows the gray highlight extending from the horizontal axis up to the data line for the two line segments.

Figure 3.33

Figure 3.33 A second series, with only three points, is used to highlight a section of the chart.

Alternatively, you could replace the numbers in column C with 70,000 in order to draw a gray rectangle behind the months, as shown in the bottom chart in Figure 3.33.

Changing Line Type Midstream

Consider the top chart in Figure 3.34. The title indicates that cash balances improved after a new management team arrived. This chart initially seems to indicate an impressive turnaround. However, if you study the chart axis carefully, you see that the final Q3 and Q4 numbers are labeled Q3F and Q4F to indicate that they are forecast numbers.

Figure 3.34

Figure 3.34 It is not clear in the top chart that the last two points are forecasts.

It is misleading to represent forecast numbers as part of the actual results line. It would be ideal if you could change the line type at that point to indicate that the last two data points are forecasts. To do so, you follow these steps:

  1. Change the heading above column B from Cash Balances to Actual.
  2. Add the new heading Forecast in column C.
  3. Because the last actual data point is for Q2 of 2007, move the numbers for Q3 and Q4 of 2007 from column B to column C.
  4. To force Excel to connect the actual and the forecast line, copy the last actual data point (the 7 for Q2) over to the Forecast column. This one data point—the connecting point for the two lines—will be in both the forecast and actual columns.
  5. Change the last two labels in column A from Q3F to just Q3 and from Q4F to just Q4.
  6. Click the existing chart. A bounding box appears around B2:B9. Grab the lower-right blue handle and drag outward to encompass B2:C9. A second series is added to the chart as a red line.
  7. On the Layout ribbon, choose Legend, Legend at Right.
  8. Click the red line. In the Format ribbon, you should see that the Current Selection drop-down indicates Series "Forecast."
  9. Select Format, Shape Outline, Dashes and then select the fourth dash option. The red line changes to a dashed line.
  10. While the forecast series is selected, choose Design, Change Chart Type. Choose a chart type that does not have markers.
  11. Because the chart title indicates that a new management team arrived, but it does not indicate when the team arrived, change the title to indicate that the team arrived in Q3 of 2006.
  12. On the Insert ribbon, select Shapes, Line. Draw a vertical line between Q2 and Q3 of 2006, holding down the Shift key while drawing to keep the line vertical.
  13. While the line is selected, on the Format ribbon, choose Shape Outline, Dashes and then select the fourth dash option to make the vertical line a dashed line. Note that this line is less prominent than the series line because the weight of the line is only 1.25 point.

The final chart is shown at the bottom of Figure 3.34.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020