Home > Articles > Home & Office Computing > Microsoft Applications

This chapter is from the book

Communicate Effectively with Charts

A long time ago, 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 was turned sideways, and a pencil was used to create a landscape chart that was an awesome communication tool. After drawing the charts by hand, they sent off the charts to someone in the home office who generated the charts on a computer. This was a great technique. Long before touching Excel, someone figured out what the message should be.

You should do the same thing today. 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. Instead of using 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 a portion of a column chart. In these examples, you will spend 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 the readers of your chart. Rather than hoping the reader discovers your message, why not add the message to the title of the chart?

Figure 3.21 shows a default chart in Excel. Both the legend and title use the "Market Share" heading from cell B71. These words certainly do not need to be used twice on the chart.

Figure 3.21

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

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

  1. From the Layout tab, select Legend, None, and then select 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 2007.
  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. When the border becomes solid, you can use the formatting icons on the Home tab to format the title. Alternatively, right-click the title box and use the Mini toolbar to format the title.
  5. On the Home tab, select the icon for Align Text Left, 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.22, provides a message to assist the reader of the chart.

Figure 3.22

Figure 3.22 Use the title to tell the reader the point of the chart.

Resizing a Chart Title

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 tab to format the title. 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, carefully right-click the bounding box and select Edit Text. Alternatively, you can left-click a second time inside the bounding box to also put the title in Text Edit mode. Note that the dashed line in the bounding box indicates the title is in Text Edit mode. Using 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 available formatting commands. You can edit specific characters within the title to create a larger title and a smaller subtitle, as shown in Figure 3.23.

Figure 3.23

Figure 3.23 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, right-click the title and select 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.24 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.

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

  1. From the Layout tab, delete the original title by choosing Chart Title, None. Excel resizes the plot area to fill the space that the title formerly occupied.
    Figure 3.24

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

  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 to make room for the title.
  3. On the Insert tab, 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 do not 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 tab 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 tab 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 tab. When you use the WordArt menus on a text box, however, all the Transform commands are available (see Figure 3.25).

Figure 3.25

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

A text box works perfectly because it is resizable and you can use WordArt Transform commands. If you move or resize the chart, 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 as shown in Figure 3.26. Although the tools on the Design tab do not allow this, you can achieve the effect quickly by using the Format tab.

Figure 3.26

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

To create the chart in Figure 3.26, follow these steps:

  1. Create a column chart by selecting Column, Clustered Column from the Insert tab.
  2. Click any of the columns to select the entire series.
  3. On the Format tab, select Shape Fill, White. At this point, the columns are invisible. Invisible bars are great for creating waterfall charts, which is discussed in Chapter 4, "Creating Charts That Show Differences." However, in this case, you want to outline the bars.
  4. From the Format tab, select Shape Outline, Black. Select 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 all the columns have handles, click Friday again.
  6. From the Format tab, select Shape Fill, Black.
  7. On the Layout tab, turn off the legend and the gridlines.
  8. Type a title, as shown in Figure 3.26, pressing Enter after the first line of the title. On the Home tab, change the title font size to 14 point, left aligned.
  9. Right-click the numbers along the vertical axis and select Format Axis. Change Major Unit to Fixed, 500.

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

Replacing Columns with Arrows

Columns shaped like arrows can be used 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 to further indicate the positive growth.

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 tab, select Shapes, Blck 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, select Format Selection from the Format tab. Reduce the gap setting from 150 percent to 75 percent to make the arrows wider.

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

Figure 3.27

Figure 3.27 Arrows can be used to emphasize the upward growth of sales.

Highlighting a Section of Chart by Adding a Second Series

The chart in Figure 3.28 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.28

Figure 3.28 Highlight the road construction months in the chart to emphasize the title further.

The title calls out the July and August time period, but it would be helpful to actually highlight that section of the chart. 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 July and August.
  2. Click on a blank area inside of 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 tab, use the Current Selection drop-down to select Series 2, which is the series you just added.
  4. While Series 2 is selected, select 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 tab, use the Current Selection drop-down to reselect Series 2. Open the Shape Fill drop down. Choose a grey fill color. The 4th row, 1st column offers a tooltip f White, Background 1, Darker 25% and is suitable.

The top chart in Figure 3.29 shows the gray highlight extending from the horizontal axis up to the data line for the two line segments. Alternatively, you can replace the numbers in Column C with 70,000 to draw a gray rectangle behind the months, as shown in the bottom chart in Figure 3.29.

Figure 3.29

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

Changing Line Type Midstream

Consider the top chart in Figure 3.30. 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.30

Figure 3.30 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 change the line type at that point to indicate that the last two data points are forecasts. To do so, 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 2011, move the numbers for Q3 and Q4 of 2011 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 tab, select Legend, Legend at Right.
  8. Click the red line. In the Format tab, 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, select Design, Change Chart Type. Select a chart type that does not have markers.
  11. The chart title indicates that a new management team arrived, but it does not indicate when the team arrived. To fix this, change the title to indicate that the team arrived in Q3 of 2010.
  12. On the Insert tab, select Shapes, Line. Draw a vertical line between Q2 and Q3 of 2010, holding down the Shift key while drawing to keep the line vertical.
  13. While the line is selected, on the Format tab, select 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.30.

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.

Overview


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.

Surveys

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.

Newsletters

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.

Security


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

Children


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

Marketing


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.

Choice/Opt-out


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.

Links


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