Adding rich data labels to charts in Excel 2013

AppId is over the quota
AppId is over the quota

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. 

To illustrate some of the features and uses of data labels, let's first look at simple 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.

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.    

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

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.

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.

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

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.

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.  

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.

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.

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

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 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. 

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.

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).  

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.    

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.

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.    

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.

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:

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

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.

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

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.   

In this article, we used data labels with text, images, and shapes to help reveal the story behind the data. This example only scratches the surface of the many things you can do with data labels in Excel 2013. Have fun exploring and storytelling, and let us know in the comments how you're putting data labels to work for you!

View the original article here

Improved data grid for charts in Word and PowerPoint

AppId is over the quota
AppId is over the quota

This post is brought to you by Kevin Donovan, a Program Manager in the Graphics and Data Visualizations Team.

One of the overlooked improvements that we added to charting in the new Office applications has to do with the "data grid" shared between Excel and PowerPoint/Word.

We introduced the "data grid" in our Charting Overview for 2013 blog post, but we wanted to explain a few more details and benefits in this post.  One of the key objectives for this feature in Office 2013 was to make the grid easier to use, and strike a balance with exposing the right amount of Excel user interface that was necessary for when users are working with charts in PowerPoint and Word.  In the new Office, we focused on miniaturizing the grid and making it a lot easier to use.   Here are some details on how we achieved this.

The data grid in the new Office offers a more concise and simple integration experience between the chart and the data brokered by Excel. As with prior versions, you have two ways to enter data for your chart. If you insert the chart from within the application, you can edit its data within a small window that we place adjacent to the chart (either above or below, depending on your app's proximity to the edge of your monitor)--this is the data grid. Or you can actually open Excel and perform more complex operations there.

If you insert a chart in PowerPoint or Word, the grid appears automatically. If the user closes the grid, there are two ways to get it back.

1. You can right-click anywhere in the chart area (which is what the screen shot above shows).

2. You can use the ribbon button, which looks like this:

When you see the data grid, what we’ve done is spun up an Excel background process, but without chrome or add-ins, which is why it loads so quickly. So, even if you already have Excel running when you edit chart data in Word or PowerPoint, the data grid will run as a separate process. Incidentally, if you choose the “Edit Data in Excel 2013” option, we open up a new Excel window with the chart data pre-populated, but we won’t start another Excel process. Instead, we’ll just open a new application window because it allows users to take advantage of Excel’s richness, such as copying and pasting formulas between workbooks.

When you select the “Edit Data” option, we provide a very simple window that shows your data, along with a few menu options that help you to navigate things:

You’ll also notice that we use the colored outlines (sometimes called colored foils) to indicate which data represents your chart. And while those foils aren’t necessarily new to Office 2013, combining them with the data grid really makes it easy to change series and category selections. You simply grab the corner and either select or de-select as necessary.

Of course, you can use the data grid to add or delete values, series, and categories to/from your chart as well – without having to go back to Excel. If you need to go to Excel to work with the data more directly (e.g. create formulas, etc.), you can select the Excel icon in the top of the data grid. This will open Excel and allow you to edit or create data in very precise ways:

Once you are finished working with the values in Excel, simply close that Excel instance, and the entered value will appear in your chart. And, again, getting the grid back is really simple: either right click in the chart, and choose, Edit Data, or select the same option in the ribbon.

For users who have been using charts in PowerPoint and Word for a long time, you know that Excel services the data behind them, but getting to, and changing, that data has never been easier than it is in 2013. As always, we value your feedback and really believe that you will agree that the changes we made to the data grid will make your charting work much simpler.

--Kevin Donovan, Program Manager, Office Graphics and Visualization Team

View the original article here

Format and customize Excel 2013 charts quickly with the new Formatting Task pane

AppId is over the quota
AppId is over the quota

The new Excel offers a rich set of charting capabilities that make creating and customizing charts simpler and more intuitive. One part of the fluid new experience is the Formatting Task pane.  

Until now, precise adjustments to chart elements were made in the Format dialog box. The box sometimes obscured a portion of the chart, changes entered in the box were not visible until you closed it, and you had to select the exact element on the chart in order to see the options that were the best fit for the job.

In the new Excel, the Format dialog box is replaced by the Formatting Task pane. The pane aligns neatly with the right or left side of the screen, so it's less likely to obscure the chart, and changes happen in real time, so you can immediately see how your choices affect the chart. The Formatting Task pane also offers an element selector so you can jump quickly between different elements without having to select one to modify.

The new Formatting Task pane is the single source for formatting--all of the different styling options are consolidated in one place. With this single task pane, you can modify not only charts, but also shapes and text in Excel.

The fastest way to open the Formatting Task pane is to double-click a chart element. You can also use the keyboard shortcut CTRL+1 while a chart element is selected. There are two other ways to open the task pane:

The first way: On a chart, select an element. On the Ribbon, select the Chart Tools Format tab, then click Format Selection.

The second way: On a chart, select an element. Right-click, then select Format where is the axis, series, legend, title, or area that was selected.

Once open, the Formatting Task pane remains available until you close it. Since it always stays on the right or left side of the screen, the pane remains unobtrusive as you concentrate on other tasks. The options in the Formatting Task pane will change based on which element is currently selected. For example, if you select a legend, the Formatting Task pane offers layout, fill, and effect options uniquely tailored to the legend element. With this more intuitive experience, you can feel free to leave the Formatting Task pane open while you format different elements of your chart.

The element selector in the Formatting Task pane allows you to jump quickly between different elements without selecting them in the chart itself. Since some chart elements are small and perhaps difficult to select, the element selector is a great alternative to the "hunt and peck" approach.

The element selector is in the top left of the Formatting Task pane. The selected element is displayed, along with a down arrow. Clicking the down arrow opens a dropdown menu that shows all elements of the selected chart. When you choose an element from this menu, the Formatting Task pane displays options uniquely tailored to this element, and it also selects this element in the chart.

You can also find the element selector on the Ribbon on the Chart Tools Format tab.

Chart options in the Formatting Task pane are in two categories: Size & Properties and element-specific.

Size & Properties deals with sizing, alignment, and miscellaneous properties such as alt text and locks.

The Chart Area element is unique in that its Size & Properties options affect the chart as a whole. Size, scale, and aspect ratio can be adjusted only on the Chart Area element, from which all other chart elements derive their inherent size and scale. The Chart Area element also controls the alt text of the chart, whether the chart moves and/or sizes with its underlying cells, and whether the chart is printable and locked.

Other chart elements can adjust their alignment from the Size & Properties options, which dictate vertical alignment, text direction and angle, and margins.

There are unique, element-specific options for axis, legends, and series:

Axis Options. Axis options allows you to adjust the axis bounds and units, the placement and interval of its labels, and other options including tick marks and number format.

Legend Options. Legend options allows you to specify the position of the legend, and whether or not it overlaps the chart.

Series Options. Series options allows you to specify whether the series should be plotted on the primary or secondary axis, the spacing and width of the columns on a bar or column chart, and the angle and explosion (separation) of the slices of a pie chart.

In addition to chart options, the Formatting Task pane allows you to fine-tune the visuals of each chart element by offering a multitude of line, fill, and effect styles. These styling options are available for shapes as well.

Line options allow you to adjust the styling of the lines and borders of each chart element. There are two line types: solid and gradient. A solid line uses one color throughout, while a gradient line changes smoothly from one color to another along its path. Regardless of the line type you choose, you can adjust a line's thickness, transparency, dash type, and endpoint settings.

Fill options allow you to choose how the inner portion of each chart element is filled. There are four fill types: solid, gradient, pattern, and picture. A solid fill uses only one color, while a gradient fill smoothly blends multiple colors along its filled region. A pattern fill tiles the inner fill region with preset imprints such as crosshatches and tiles, and a picture fill uses preset textures or a picture that you specify.

Please note that certain chart elements, such as the series of a Line Chart, do not have an inner fill region to color or tile. Fill options are disabled for these chart elements.

Effect options offer a powerful set of effects that add visual impact to your charts. Effects include shadows, glows, soft edges, and 3-D formatting.

Shadow. This effect adds a shadow either outside or inside the chart element.

3-D Formatting. This effect adds a bevel to the chart element to give it a 3-dimensional relief.

In addition to modifying the look and feel of charts and shapes, you can also style text inside these objects.

Text options are in the top right of the Formatting Task pane. Clicking Text Options toggles the task pane into text styling mode. You can toggle back to the former chart and shape styling mode at any time by clicking the element selector's title.

In text styling mode, you can modify the look and feel of your text using the same line, fill, and effect options that are available for charts and shapes.

The new Formatting Task pane is just one of many ways to customize and refine the look and feel of your charts. The on-object buttons featuring the new Chart Elements and Chart Styles provide quick access to the most common chart customizations, but when you need detail-level control, now you can get it with the Formatting Task pane. Please try it out, and give us your thoughts and feedback on the experience--we'd love to hear what you think!

View the original article here

Webinar: Organization charts in PowerPoint and Visio

AppId is over the quota
AppId is over the quota

Need to create an org chart? Unsure of whether to use PowerPoint or Visio? In this week's webinar, we'll show you both ways and the advantages of each depending on your audience, plus we'll share tips and best practices for creating organization charts. A video of the webinar will be posted shortly after the webinar.

What you will learn at Tuesday's webinar

How to decide between using PowerPoint or Visio How to make an org chart in PowerPoint (or any Office program really) How to make an org chart in Visio

References for this webinar

Learn how to join us each week or watch previous webinars at http://aka.ms/offweb.

--Doug Thomas

View the original article here

Our eight best tutorials on Excel charts

AppId is over the quota
AppId is over the quota

Excel spreadsheets are all about numbers. But tons of numbers are often not the most effective way to communicate. 

That's where charts come in handy. Charts can display numeric data in a graphical format, making it easy to understand large quantities of data and the relationships among data.

If you want to learn about Excel charts, you've come to the right place. We've pulled together out best tutorials and articles on charts to get you started.

Create a chart from start to finish

This tutorial covers all the basics, including, 1) getting to know the elements of a chart, 2) modifying a basic chart to meet your needs, 3) applying a predefined chart layout and chart style for a professional look, 4) adding eye-catching formatting to a chart, and  5) reusing charts by creating chart templates.

 Free training: Take the next steps in growing your Excel skills (Lesson 2)

In Lesson 2 of the Excel Skills Builder, you learn about how to create a chart and other ways to visualize your data. (The Excel Skills Builder is a training series of short videos (typically under 5 minutes each) that give you an overview of a task or feature in Excel.)

Go beyond the basic chart type

Excel comes with lots of chart types, including column, line, pie, and so on. However, many of you asked for more information about other kinds of charts. This post covers how to work with floating column charts, Gantt charts, combination charts, and organization charts.

Can't find the Chart Wizard? No worries

It's true that Excel 2007 and Excel 2010 didn't include the Chart Wizard. For those of you using or upgrading to Excel 2010, this post explains where to find the chart functions on the Ribbon.

Use sparklines to show data trends

A sparkline is a tiny chart in a worksheet cell that provides a visual representation of data. Use sparklines to show trends in a series of values, such as seasonal increases or decreases or to highlight maximum and minimum values. This tutorial explains 1) what sparklines are, 2) why you should use them, 3) how to create one, and 4) how to customize them.

 Line or scatter chart?

That's a good question! Choosing the wrong chart type for your data can easily happen when it comes to line and scatter charts. They look very similar, especially when a scatter chart is displayed with connecting lines, but there is a big difference in the way each of these chart types plots data along the horizontal and vertical axes.

Format column sparkline charts using the date axis and cell merging

Sparkline charts are great, but there may be times they need to be visually enhaced to maximize their usefulness. This post shows you one way to do that.

Combining Chart Types, Adding a Second Axis

Often, you'll find it is useful to create charts which compare and analyze different types of data. For example, you might want to compare share price with trading volume. In Excel, you can make a chart easier to understand by using different chart types in the same chart, and by using a secondary vertical axis to plot values that are in a different value range. You can read this post to learn how, or you can watch this video: Combine two or more chart types in a chart

If haven't found what you're looking for, leave a comment, and we'll see if we can write another blog post.

View the original article here