Software

10 cool new charting features in Excel 2013

Excel 2013 includes a healthy assortment of new charting features that will save you a lot of time and help you produce better results.

Excel 2013 charts can be completed in a few minutes. Charts that used to require specialized knowledge and a lot of time and experimentation have been reduced to a few clicks. The emphasis is on generating quick and appropriate charts, from the get-go. Once you have figured out the best chart type, Excel 2013 offers new interactive options for tweaking your chart until it's perfect.

We'll step through 10 of Excel 2013's new charting features, updating a chart as we go. In the end, there's no guarantee you'll like the results. But what I can promise is that you won't spend much time creating new charts once you're familiar with these new features. You'll generate charts much quicker and spend less time regretting choices -- it's just too easy to undo them and start over!

1: Recommended Charts

Choosing the appropriate chart type to represent your data's story and applying that choice requires knowledge (sometimes experience) that many users don't have. Recommended Charts takes a bit of the pain out of this process. To use this new intuitive feature, click inside the data range you want to chart, click the Insert tab, and click Recommended Charts. Excel will display a thumbnail collection of suggested chart types, as shown in Figure A. You can click each one to preview your data presented in that chart type. Figure B shows the data as a stacked column chart -- the second choice for charting the data. Going this route helps you quickly zero in on the right chart type for your data. Double-click your choice to embed it.

Figure A

Figure A

Use Recommended Charts for a quick start on generating effective charts.

Figure B

Figure B

Choosing the right chart is as simple as a double-click.

This new feature is so smart that you'll want to check it out even if you're a charting expert. At the very least, it will save you time by starting with the most appropriate charts, which you can then tweak as necessary.

2: Better organization

Earlier versions of Excel offered three contextual tabs for working with charts. Figure C shows the two new contextual charting Ribbon tabs in Excel 2013: Design and Format. The Layout tab from the earlier Ribbon versions is missing. That seems like a bad thing, but it isn't—the options are better organized so finding them is easier.

Figure C

Figure C

Many charting options are on these two tabs.

3: Chart Elements

The new Chart Elements icon, shown in Figure D, replaces the Layout tab from earlier versions (#2). This new interface tool is probably the most versatile -- the one you'll turn to the most often. To use this icon, just click it. Then, check or uncheck the chart element you want to add or delete, respectively. Figure E shows the result of removing the gridlines and adding an axis title.

Figure D

Figure D

Use these icons for quick access to charting options that were layers deep in earlier versions.

Figure E

Figure E

Quickly add and remove chart elements to customize your chart. 4: Chart Styles

The Chart Styles icon will help you change the way a chart looks by applying predefined styles. They've always been available, but you had to manually combine the different formats to get the final product. Now, they're available with a quick click. Figure F shows the new Chart Styles list and the result of applying one of the styles -- that's a lot of change for a simple click. (Okay, so it isn't perfect; but that gives an opportunity to explore quick fixes!)

Figure F

Figure F

Apply a predefined style to reduce your formatting chores.

5: Chart Filters

The last icon, Chart Filters, lets you quickly add and remove values. In this case, it makes sense to remove the totaling rows and columns. To do so, click the Chart Filters icon and start unchecking items from the list shown in Figure G. Figure H shows the resulting chart after removing the Totals and Commissions values.

Figure G

Figure G

Use the filters list to quickly remove (or add) a series.

Figure H

Figure H

Removing the totaling values took just a few clicks.

6: Flexible data labels

Data labels are more manageable now because Excel 2013 offers more formatting and shape options. One clever option lets you easily connect them using a leader line. To see all the new possibilities, select a data label and click Chart Elements. Then, click the drop-down to the right of Data Labels and choose More Options, as shown in Figure I. (Applying a predefined style in #4 automatically added data labels to the example chart. To manually add data labels, click the Chart Elements icon and check Data Labels.) Figure J shows the options pane where I changed the format used to display the data labels' values. Specifically, I reduced the number of values and added the M character to represent millions. You can even use freeform text and anchor a label to a specific cell now.

Figure I

Figure I

Quick access and new options makes managing data labels easier than ever.

Figure J

Figure J

With just a few clicks you can reformat the values.

To learn more about the custom format used in this example, read Use a custom format in Excel to display easier to read millions.

7: Formatting task pane

For quick and easy access to an element's properties, double-click it; Excel will display that element's properties in the Formatting task pane. Figure K shows options for the chart's legend. Use the element selector (circled) to gain quick access to other elements, especially those small, hard-to-select elements.

Figure K

Figure K
Every element's formatting options are just a double-click away.

8: Combo charts

Combo charts require specialized knowledge in older versions. Now, they're a simple choice. After selecting your data and choosing Insert Chart, click the All Charts tab and select Combo.

We can work with our example chart, but first it needs a series that warrants a combo chart. Figure L shows a modified data range. I added a column of MAX() functions to return the largest value in each region. To add the new values to the chart, do the following:

  1. Select F2:F6 and press [Ctrl]+C to copy the new values to the Clipboard.
  2. Select the chart and press [Ctrl]+V to add the values to the chart as a new series. (You've always been able to add a new series this way.)

Figure L

Figure L

Add a new series to the chart.

Now, you can create a combo chart using the new values in column F, as follows:

  1. Select the chart, if necessary, click the contextual Design tab, and click Change Chart Type in the Type group.
  2. Click the All Charts tab and choose Combo.
  3. In the bottom pane, you'll probably have to reset the chart type for each series from clustered column to stacked column using the Chart Type drop-down for each series.
  4. Using the Chart Type drop-down for the Max series, choose Line (if necessary), as shown in Figure M.
  5. Click OK to see the combo chart in Figure N.

Figure M

Figure M

Select the appropriate type for each series.

Figure N

Figure N

This chart displays the totals as a line.

9: More dynamic data labels

 The example looks a bit cluttered with all those data labels, so it's time to rethink the chart's purpose. (Aren't you glad that you didn't spend a lot of time adding them?) Let's suppose that you want to emphasize the $10,000 loss for Michaels in the West region. To do so, delete all the data labels except for that one, as shown in Figure O, as follows:

  1. Your first step is to move the data label you want to keep. Select it -- not using a quick double-click, but two slow clicks. Doing so will select only that data label.
  2. Drag the single data label to the top of the plot area, out of the way.
  3. Select each series of data labels and press [Del]. When you come to the series that contains the -$10,000 value, you'll have to select each individually (using two slow clicks) to delete them without deleting the data label for -$10,000.

Figure O

Figure O

Retain only one data label for emphasis.

At this point, you can't read the label, so reformat it by double-clicking it and changing the font color to black as follows:

  1. Click the Fill & Line icon (top of the Format task pane).
  2. Change the label's font color. Perhaps the quickest way is to simply select it as you would any text and use the Font Color tool on the Home tab (in the Font group).

To truly emphasize your point, add some meaningful text. Position the cursor inside the data label and start typing. Figure P shows the addition of a simple but effective message. This technique isn't exactly new and renders your label static. If you change the value in cell D6, the label won't update accordingly.

Figure P

Figure P

Make a specific point using text.

There's a fix for that now. Position the cursor in the data label where you want the value to appear, right-click, and do the following:

  1. Choose Insert Data Label Field, as shown in Figure Q.
  2. In the resulting dialog, select [Cell] Choose Cell, as shown in Figure R.
  3. Identify the cell that contains the value (D6) and click OK.
  4. At this point, you'll have the previously formatted value and the formatted cell value. Delete the previously formatted value (in the data label) and keep the new one, because that field is now linked to the cell D6. After linking the data field to the cell, you can update the source value and the data label will also update – automatically. The data label isn't dynamic; the data field inside the data label is dynamic (Figure S).

Figure Q

Figure Q

Right-click the data label to make the value dynamic.

Figure R

Figure R

Link to the source cell.

Figure S

Figure S

The data field in the lone data label is dynamic.

At this point, the chart looks pretty good. It isn't finished -- you might want to check out a few more predefined styles and add a chart title (we never did that, but that's nothing new). The stacked bars for the West region present an interesting problem, as there's little distinction between the personnel. But depending on your point of view, that might be exactly the look you need. Producing this chart took no specialized knowledge other than knowing how to find options via the new interface features.

10: Quick Analysis charts

Quick Analysis offers a second easy route to quick charts that are appropriate for the data. Simply select the data and click the Quick Analysis icon at the bottom-right, as shown in Figure T. Click the Charts tab to view the most appropriate chart types for your data. If you don't see the Quick Analysis icon, check your settings as follows:

  1. Click the File tab and choose Options from the left pane.
  2. Choose General in the left pane (the default).
  3. In the User Interface Options section, the Show Quick Analysis Options On Selection option should be checked. Enabled is the default, so you should see this icon unless someone has changed this setting.
  4. Click OK.

Figure T

Figure T

Use Quick Analysis to generate appropriate charts quickly.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

77 comments
abbasishariq2
abbasishariq2

Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.

budgeting
abbasishariq2
abbasishariq2

philippines

I recently came across your blog and have been reading along. I thought I would leave my first comment. I don’t know what to say except that I have enjoyed reading.

abbasishariq2
abbasishariq2

Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.crazybulk uk

abbasishariq2
abbasishariq2

When CMM Automotive Mechanics carries out a major service we check, test, replace and repair the required parts to make your car feel like new again. Cipto Junaedy

abbasishariq2
abbasishariq2

botanique luxury condo This content is written very well. Your use of formatting when making your points makes your observations very clear and easy to understand. Thank you.

abbasishariq2
abbasishariq2

hide ip software

I feel really happy to have seen your webpage and look forward to so many more entertaining times reading here. Thanks once more for all the details.

abbasishariq2
abbasishariq2

Hello ! I just wish to give an enormous thumbs up for the nice info you’ve got right here on this post. I will probably be coming back to your weblog for more soon what to text your ex girlfriend to get her back.

sell car for scrap Sheffield
abbasishariq2
abbasishariq2

best smartphone order app

Hello ! I just wish to give an enormous thumbs up for the nice info you’ve got right here on this post. I will probably be coming back to your weblog for more soon what to text your ex girlfriend to get her back.

abbasishariq2
abbasishariq2

    • mobile order app The post is written in very a good manner and it entails many useful information for me. I am happy to find your distinguished way of writing the post. Now you make it easy for me to understand and implement the concept.

spotlightseo
spotlightseo

I also benefit from learning the assessments, but learn that alot of people ought to stay on essay to try and add worth in the direction of the authentic weblog release. C programming

nolievipp
nolievipp

You know, when you get charged with a crime – ‘You have the right to an attorney. If you can’t afford an attorney, one will Desentupidora

spotlightseo
spotlightseo

Great post full of useful tips! My site is fairly new and I am also having a hard time getting my readers to leave comments. Analytics shows they are coming to the site but I have a feeling “nobody wants to be first matka chart

spotlightseo
spotlightseo

The post is written in very a good manner and it entails many useful information for me. I am happy to find your distinguished way of writing the post. Now you make it easy for me to understand and implement the concept. Resin bond diamond/CBN wheels

spotlightseo
spotlightseo

Thanks for making such a cool post which is really very well written.will be referring a lot of friends about this.Keep blogging. Web Design

Olivia_P
Olivia_P


Good post. I learn some thing tougher on distinct blogs everyday. Most commonly it really is stimulating to learn to read content material from other writers and exercise a specific thing there.

ladybugs for sale

abbasishariq2
abbasishariq2

I really appreciate that you wrote this article and shared some really good information on this specific topic. I was in vital need to get some information on this topic and thanks to you, I've got that! Thanks, once again!

 south east gas training schools

abbasishariq2
abbasishariq2

Hello ! I just wish to give an enormous thumbs up for the nice info you’ve got right here on this post. I will probably be coming back to your weblog for more soon what to text your ex girlfriend to get her back.

wire coating

abbasishariq2
abbasishariq2

I discovered your site ideal for me. It consists of wonderful and useful posts. I've read many of them and also got so much from them. In my experience, you do the truly amazing.Truly i'm impressed out of this publish.LeedsBradford asbestos company

spotlightseo
spotlightseo

We are really grateful for your blog post. You will find a lot of approaches after visiting your post.  Spy Whatsapp

spotlightseo
spotlightseo

A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one. massachusetts moving

johnandro68
johnandro68

Its a great pleasure reading your post.Its full of information I am looking for and I love to post a comment that "The content of your post is awesome" Great work.Japanese Cars Exporter

abbasishariq2
abbasishariq2

Got some information and would like to give it a try. Applaud your work and keep sharing your information.
indian matka

abbasishariq2
abbasishariq2

You will find a lot of approaches after visiting your post. I was exactly searching for. Thanks for such post and please keep it up. Great work.

mumbai matka

Olivia_P
Olivia_P

Thank you, I've been seeking for info about this subject matter for ages and yours is the best I have discovered so far.

web danfe

spotlightseo
spotlightseo

Hi there,  I found your blog via Google while searching for kinda Such informative post and your post looks very interesting for me.fix laptop delray beach

antoncostel
antoncostel

Thank you for a good article. Very helpefull i can say!

asid33
asid33

Thanks a lot for sharing this amazing knowledge with us. This site is fantastic. I always find great knowledge from it.frozen games download

calvinbrock735
calvinbrock735

I agree with your blog and i will be back to check it more in the future so please keep up your work. michael kors outleti love your content & the way that you write. 
buy gifts uk

calvinbrock735
calvinbrock735

So luck to come across your excellent blog. Your blog brings me a great deal of fun.. Good luck with the site.  Be Your Own Boss

johnpatinson345
johnpatinson345

This is a very good tips especially to those new to blogosphere, brief and accurate information… Thanks for sharing this one. A must read article.transzform wraps

johnpatinson345
johnpatinson345

It was great to see the old printshop and everyone who works there again. I am excited to see a printing business still operating and growing, great job guys start a blog

asid33
asid33

Undeniably believe that which you stated. Your favorite justification appeared to be on the internet the simplest thing to be aware of. Alaska Jobs

Editor's Picks