Back
Excel

Inserting Charts in Excel 2013

image

 

This blog post is brought to you by Nick Chiang a Program Manager on the Office Graphics team.

 

As Scott mentioned in his Charting Overview post, users have always struggled with picking the right chart type to represent their data. Unless you have a good understanding of the different chart types available and the types of data they work for, many users have trouble choosing the right chart type to properly represent their data, and often fallback to choosing something familiar. Even worse, sometimes users ended up choosing chart types that misrepresent their data, changing the message they’re trying to present. So as a part of the data visualization effort for Excel 2013, we focused on simplifying the process for making charts, and helping users easily and quickly make great looking charts that are appropriate for their data.

clip_image002

The improvements begin with the Insert Ribbon, and the new Recommended Charts button. This new recommendation feature presents a list of different chart types generated by an algorithm based on your selected data. The algorithm looks for different patterns in how your data’s arranged to determine a list of what the most suitable chart types are. In past versions of Excel, you would’ve had to insert the chart at this point to see what it looks like with you data plotted. In Excel 2013, we’ll instead show you a live preview of the chart already with your data in it. That way, you’ll know you’re making the right decision without having to guess what the output will look like. This feature can be found both in the new Insert Chart dialog, as well as the new Quick Analysis feature (this feature will be covered in a later blog post). Let’s take a look at a couple different examples of these recommendations, and see the new Insert Chart dialog and Quick Analysis features in action (these examples will be available in a file attached at the bottom of this post).

Example 1 – Understanding your Data

clip_image004

Here we have a fairly simple table of data containing some statistics from the past few Winter Olympics. Selecting this dataset and clicking on the Recommended Charts button will present us with the new Insert Chart dialog:

clip_image006

Looking at the dataset, the algorithm has determined that the first row looks like a set of years, and has recommended a line chart as the top recommendation (line charts are great for showing changes over time). The other chart types that get recommended provide useful alternate views of the data in other chart forms.

Example 2 – Different Ways to Map your Data

clip_image008

In this example, we have values that plot out some basic mathematical formulas. Let’s try the Quick Analysis feature this time. With your data selected, you’ll notice a new icon pop up at the lower right corner of your selection. Clicking this icon will bring up the Quick Analysis feature:

clip_image010

The chart type recommendations presented here are the same ones the show up in the Insert Chart dialog. If you hover over the chart type icons with your mouse, you’ll see a live preview of the chart with your data plotted:

clip_image012

You’ll notice here that there are actually multiple scatter charts being recommended. In this case, the algorithm is presenting multiple ways in which the data could be mapped to a scatter chart – of the four columns in the dataset, there may be two sets of x and y values (as is the case here), or there may be a single column of x values, followed by three separate sets of y values. The algorithm will present multiple different mappings so that the user may select the correct one for their dataset. From here, clicking on the chart type will insert the chart directly onto your worksheet.

Example 3 – Pivot Chart Recommendations

clip_image014

Sometimes what you want to chart isn’t necessarily the values in a dataset, but rather the aggregate (like the sum or count) of a set of values. This would be useful in situations when you want to tally up or summarize the results from some data, like a survey for example. Working with the new Pivot Recommendations feature (which will also be covered in a later blog post), our algorithm is also be able to suggest pivot charts based off of your data:

clip_image016

In this example, the first recommendation is a chart that shows a count of the number of participants that responded to each of the Pet types. The other two recommendations each show a slightly different interpretation of the user’s dataset (the second counting the number of choices by Colour, and the third by Fruit).

Pivot chart recommendations are differentiated by the pivot icon in the upper right of the thumbnails:

clip_image017

Selecting one of these suggestions will automatically insert a pivot chart and a pivot table on a new sheet in your workbook, allowing you to further explore your data.

clip_image019

For our Power Users

Despite the focus on improving the chart creation experience for novice users, we wanted to deliver these features in a way that did not obstruct the workflows of our more advanced chart users. We’ve left in the individual chart type buttons on the Ribbon, to allow quick access to all of the available chart types in Excel without having to enter the dialog.

All of the chart types are also available in the Insert Charts dialog under the All Charts tab. In the screenshot below, you’ll notice that the selected chart type is accompanied by multiple live previews of the user’s data. These options represent different ways your data may be mapped to the selected chart type. In the example below, the two mappings show the chart plotting the user’s data either by rows or by columns.

clip_image021

Final Thoughts

These examples demonstrate just a small portion of the changes we’ve made to make it easier for users to create great looking charts in Excel. One thing to note is that not every chart type available in Excel will be recommended through the algorithm. You’ll never see a 3D chart recommended, nor will you see some of our more specialized chart types, such as surface charts. As mentioned earlier, these chart types are still available through both the individual chart type button on the Insert Ribbon and on the All Charts tab in the Insert Charts dialog.

Our hope is that these new features will help make charts much more accessible and easier to create for everyone. We’re constantly looking to improve our recommendations, so we look forward to hearing your comments and feedback on this experience. Thanks!

Nick Chiang

Join the conversation

2 comments
  1. The whole way this functionality works is a great step in the right direction for helping users make more purposeful choices. This may do me out of a job teaching people how to choose and use the right charts in Excel! (but at least I can then focus on the more advanced techniques).
    For me the single best bit of news with this whole thing is "You’ll never see a 3D chart recommended". Maybe in Excel 2015 we can just drop them altogether and pretend the 1990’s nightmare of the 3D exploded pie charts never happened…

  2. Still want the x,y scatter with variable markers (circle with radius per point).

Comments are closed.