«

»

Adding rich data labels to charts in Excel 2013

Here is a great tip from the official Excel Blog at Microsoft:

Adding rich data labels to charts in Excel 2013

by Excel Team

Storytelling is a powerful communication tool, and data is essential for many decision-making tasks. Together, they can be data visualization at its best: the science and art of transforming your data so that the most important points shine through. Sometimes a basic chart will do the trick.  But to make your visual message really pop, it’s often handy to add data and text to your chart. The rich data label capabilities in Excel 2013 give you tools to create visuals that tell the story behind the data with maximum impact.

The basics of data labels

To illustrate some of the features and uses of data labels, let’s first look at simple chart.

Excel 2013 Chart

This clustered column chart shows the sales (revenue) of drinks and snacks from a neighborhood lemonade stand during one week. If I want to turn on basic data labels on the blue data series (Drinks), there are a few ways to do that.  One familiar and simple way is just single click on any data value (or column, in this example) to select the entire data series that it belongs to.

Excel 2013 Chart

Above, I have clicked all of the blue columns. Once the series is selected, I can right-click any column to pull up the context menu, then click the Add Data Labels entry.

Excel 2013 Chart - Add Data Lables

When I click Add Data Labels, I get the following result.

Excel 2013 Chart - Add Data Lables

To reposition any single data label, all I have to do is double-click the data label I want to move, then drag it to the desired position on the chart. Here, I have selected only the Tue value of the blue Drinks series.

Excel 2013 Chart - Add Data Lables

Once selected, I can drag that label wherever I want it on the chart. If I drag the label far from its default location, a leader line appears by default to show what data point the data label is associated with.

Excel 2013 Chart - Add Data Lables

Basic formatting of data labels is simple to achieve by using the Font section of the Home tab on the Excel ribbon.

Excel 2013 Chart - Add Data Lables - Home Ribbon

Use the Formatting Task pane for advanced options

If you wish to go beyond basic text formatting and text box fills, many more formatting options are available on the Formatting Task pane. Though there are several ways to open the Formatting Task pane, the easiest is to double-click the data labels themselves. Here, I have double-clicked one of the data labels for the blue Drinks series.

Excel 2013 Chart - Add Data Lables - Format Data Labels

In the Formatting Task Pane, you can customize the way the data labels appear, change their size and alignment, change their text properties, and even add another data series for them to include. See Format and customize Excel 2013 charts quickly with the new Formatting Task pane for more discussion about the Formatting Task Pane in general.

Text in data labels

Often, the real story doesn’t lie in all the numbers in the chart, but it’s hidden in a few key data points. Let’s reapproach our example with that in mind.

First, I’ll delete the data labels that I already put in place. To delete all the data labels for a given series, click once on any data label in the series, and this will select them all.  If you press the delete key on your keyboard, all the data labels from that series will disappear from your chart. To delete any single data label, follow the same procedure, except click twice (and not too fast) on the individual data label you wish to delete.

Below, I have inserted just one data label and moved it to a roomy place in the chart. Next, I want to type custom text into the data label box to help tell the story behind the data.

Excel 2013 Chart - Add Data Lables - Screenshot

To make it easier to place an insertion point in the data label, I have found that it helps to zoom in on the chart. You can do this by adjusting the zoom control on the bottom right corner of Excel’s chrome.

Excel 2013 Chart - Add Data Lables - Screenshot - Zoom

Then, select the value in the data label and hit the right-arrow key on your keyboard.

Excel 2013 Chart - Add Data Lables - Screenshot - Select Data Value

The story behind the data in our example is that the temperature increased significantly on Wednesday and that appeared to help drive up business at the lemonade stand. So I type some text to emphasize that point while still leaving the data label intact.

Excel 2013 Chart - Add Data Lables - Screenshot -

Linked data embedded in data labels

Excel 2013 also lets you put numbers from your spreadsheet into your data labels – that is, numbers that are not directly associated with the data point.  Here is a quick example. Let’s say that I want to add a further annotation about the temperature on Wednesday and I want to include a data value with that annotation. Here’s how I would do it:

First, I select my data label and I type some additional text to give context to the new number I’m about to add to the data label. Then, I right-click the data label to pull up the context menu. Note the Insert Data Label Field menu item.

Excel 2013 Chart - Add Data Lables - Screenshot - Insert Data Label

When I click Insert Data Label Field, Excel 2013 opens a dialog that gives me a few options to choose from. I want to pull in a data value that is calculated on my worksheet, so I select Choose Cell.

Excel 2013 Chart - Add Data Lables - Screenshot - Insert Data Label

The Choose Cell option opens a familiar type of dialog that allows me to go back into my worksheet and select the cell with the value that will be shown in my data label. In this example, I select a cell that contains the value that shows how many days it has been since the temperature was this warm (26 days, in this case).

Excel 2013 Chart - Add Data Lables - Screenshot - Cell Options

When I click OK, the value from the cell I selected (D39, here) appears in my data label. To finish it off, I type the rest of my statement and end up with a very rich data label.

Excel 2013 Chart - Add Data Lables - Screenshot

Data label callouts

The data labels up to this point have used numbers and text for emphasis. Putting a data label into a shape can add another type of visual emphasis. To add a data label in a shape, select the data point of interest, then right-click it to pull up the context menu. Click Add Data Label, then click Add Data Callout.  The result is that your data label will appear in a graphical callout.  In this case, the category Thr for the particular data label is automatically added to the callout too.

Excel 2013 Chart - Add Data Lables - Screenshot Data Label Callouts

Excel 2013 Chart - Add Data Lables - Screenshot Data Label Callouts

In the image below, I clicked inside the data callout, backspaced over the Thr entry, and then typed a bit of information that explains what is behind this anomalous data point.

Excel 2013 Chart - Add Data Lables - Screenshot Data Label Callouts

If you want to change the shape of a data callout, you can do so by right-clicking the data label to pull up the context menu, or by selecting the data label, then clicking Change Shape in the Format tab in the ribbon.

Excel 2013 Chart - Add Data Lables - Screenshot Data Label Callouts

In this case, let’s say that for the Snacks value on Thursday, I don’t really want to show the value in the data label, but I’d like to make my point with something a bit more whimsical. For this, I turn to the rich formatting options in the Formatting Task pane we talked about earlier. Below, I have double-clicked the data label to pull up the Formatting Task pane for the data label.

Then, I clicked the Fill and Line symbol:fill and line symbol

I then selected Picture or Texture Fill, and clicked on Online.

Excel 2013 Chart - Add Data Lables - Screenshot Data Label Callouts

Let’s say that on Thursday the lemonade stand ran out of donuts, which were the main selling item in the snacks section. I can search Office Art on the web for an image of a donut to serve as the background in my data label.

Excel 2013 Chart - Add Data Lables - Screenshot Insert Pic

I pick an image from the results, and it’s automatically inserted into the background of my data label.

Excel 2013 Chart - Add Data Lables - Screenshot Insert Pic

Excel 2013 Chart - Add Data Lables - Screenshot Insert Pic

We’re almost there. The donut image is good, but it’s too small to convey the message. Also, I want to use a text comment instead of showing the data value for this point. So I re-size the data point’s bounding box, select the data value, and replace it with some clarifying text.  I can adjust the text color and font size using the Font controls on the Home tab in the ribbon.

Excel 2013 Chart - Add Data Lables - Screenshot Insert Pic

About Advanced Network Consulting:

Looking for a local IT Support company providing same day onsite service?  Advanced Network Consulting serves small and medium size Southern California businesses.  From cabling, network support, and large scale hardware and system upgrades, ANC provides your business with professional, prompt, personalized service.  To schedule a complimentary onsite evaluation, please call 562.903.3992.  We look forward to servicing your business.