Skip to content

Excel 2013 Recommended Charts, Secondary Axis, Scatter & PivotCharts

    Chart with Secondary Axis

    Excel 2013 includes a brilliant new option for creating charts called Recommended Charts. Recommended Charts is able to look at the nature of your data and present you with a choice of the most suitable chart types it thinks you should use. In other words it will recognise whether you data will be best displayed as a bar chart, a pie chart, a combination chart with a secondary axis, a scatter chart or a PivotChart. There are of course other chart types available but these are the ones we are going to concentrate on in this tutorial.

    One big difference that Excel 2007 and 2010 users will find is the absence of the LAYOUT tab in the Ribbon Chart Tools. This tutorial will show you where the layout options can now be found.

    The tutorial includes a video, a step by step written guide as well as an Excel file that you can use to practise your new found knowledge and skills.

    Creating and Formatting a Basic Chart Using Recommended Charts

    Let’s start with how to create a basic chart using the Recommended Charts option. First of all I will click somewhere in the data that I want to present as a chart.

    Basic chart data

    Then I click the INSERT tab on the Ribbon. In the Charts group I go for the Recommended Charts button.

    Recommended Charts button

    The Insert Chart dialog in this example gives three recommended charts that are suitable for this data: a column chart, a bar chart and a pie chart.  This does make sense for the simple set of data I am using.

    Insert Chart Dialog

    Let’s choose the pie chart option for now.

    Pie Chart Chosen

    Now the pie chart has been created I can play around with layout and formatting fairly easily using the three tools that appear to the top-right of the chart.

    5-Pie Chart Created

    The plus button lets me choose which chart elements I want to display: elements would include titles, data labels, trend lines and legends. In the example below, I have chosen to include Data Callouts in my pie chart.

    Adding elements to a chart

    Here’s how it looks now…

    Pie chart with Data Callouts

    The second button (the paintbrush) allows me to easily format the chart using built-in styles and colour palettes. In the example below, I have chosen Style 7 which gives the chart a dark background.

    selecting a style for a chart

    If I want to change the colours applied to the different segments in my pie chart I can click on the COLOR tab at the top of the menu. In the example below I have chosen the Color 2 palette.

    changing the colour applied to a chart

    Here’s how the chart looks now…

    Formatted Pie Chart

    The third button (the funnel button) allows me to filter which series, categories and names are displayed in my chart. My chart has only one series – the sales data, so I can’t filter on series but I might want to exclude categories – in this example that would be the individual salespeople.

    Changing Names in Charts

    To exclude a salesperson from the chart I would just need to uncheck against the relevant name and then click the Apply button.

    Using the Filtering Tool to Exclude Sub Totals in Your Chart

    One practical use for the Filtering tool is where data includes sub-totals.  For example look at the data below which includes sub-totals for each quarter.  I definitely do not want to include these sub-totals in my chart.

    Chart Data Showing Subtotals

    Here I have used the filter tool to exclude the quarter sub-total categories.

    Deselecting Categories in a Chart

    Chart before I filtered sub-totals

    Chart with Subtotals

    Chart after I filtered sub-totals

    Chart without Subtotals

    Creating Combo Charts with a Secondary Axis using Recommended Charts

    To show the power and usability of Recommended Charts I am going to use data that won’t display properly on a single axis.  This is a common problem with data that includes year-to-date calculations or percentages alongside regular numeric data for example.

    In the spreadsheet below the first two rows of data include sales figures for Bob and Bill.  The third row is a comparison of Bob’s sales against Bill’s expressed as a percentage.

    Data that requires a secondary axis

    Because the percentage values are so much smaller than the sales values, the percentage figures will need to be shown on a separate axis.  Now this has always been possible in Excel, but with Recommended Charts the process of creating a chart displaying data on a secondary axis has become a whole lot easier.

    When I use Recommended Charts the first chart type it offers me is combination chart where the percentage values are placed on the secondary axis. This is perfect for my chart data.

    Chart with Secondary Axis

    I can however customise how the data is displayed.  For example I may want to change the type of chart that is being used for the percentage data – at the moment it’s a line graph, but maybe an area graph would work better for me.

    To customise the chart, I need to click on the All Charts tab at the top of the dialog box.

    Customising a Combo Chart

    I now need to choose the Combo chart type down the left side where I can customise how the data is displayed and which series is displayed on the secondary axis. In the example below I have opted to show the percentage data as an area graph, and checked the option to show the data on the secondary axis.  The chart preview shows me how the chart will look.

    I could of course swap around which series appear on the secondary axis if I really wanted to.

    Customised Combo Chart showing Area Graph

    Creating Scatter Graphs using Recommended Charts

    Scatter graphs show the relationship between two sets of values and is another type of graph that Recommended Charts is easily able to identify based on the data you give it. In my example my data shows ice cream sales on each day of the month but it also records the maximum daytime temperature on each of those days. I want my scatter graph to explore the relationship between how warm the day is and the number of ice cream sales.

    Here’s my data.

    Data for Scatter graph

    I use Recommended Charts to ask Excel to create the most appropriate chart for my data. The second suggestion it comes up with is the one to go for.

    Selecting the Scatter Graph Type

    Having confirmed that this is the chart type I want to use (by clicking OK), I now need to format the horizontal value axis so that it starts at 15 rather 0.  To format any part of a chart, double-click on it: this will open a task pane displaying all the relevant formatting options for that part of the chart.  When I double-clicked on the horizontal axis – the following task pane is displayed on the right of my screen.

    I have put in my new minimum value of 15.

    Changing the Minimum value on the horizontal axis

    With this new setting my scatter graph looks like this:

    Scatter Graph

    To add a trendline, I click on the plus button to add chart elements and check the trendline option.

    Add a Trendline to a Scatter Graph

    Here’s how my chart looks with a trendline included.

    Scatter Graph with Trendline

    Creating a PivotChart using Recommended Charts

    Sometimes you need to summarise large amounts of data in a chart – say thousands of rows of data. Now showing each row as a separate category or series is going to result in a fairly meaningless mess but summarising the data with sub-totals will help you to analyse your data.  This is where PivotCharts come in handy. If you have not used PivotCharts before do not worry, Recommended charts will do all the work for you.  The PivotChart will create sub-totals for your data based on categories that it can identify.

    In the example below the data includes 1000 records.  Each record gives details of a sale, including date, branch, product group, customer type and revenue. Recommended Charts will easily create sub-totals for each category in either branch, product group or customer type, based on matching values. Let’s have a go!

    Data for Pivot Chart

    Using Recommended Charts this is what Excel comes up with.  The second recommended chart suggests a summary of revenue by product type, the third a summary of revenue by branch.  There are quite few others including sum, average and count calculations.

    Choosing a Pivot Chart

    When you create a PivotChart is this way, the chart ends up on a separate sheet unlike the other chart types that you can create.

    A Pivot Chart

    You will need to know a little bit about Pivot Tables to understand how to go any further with your chart, but otherwise you have created a meaningful analyse of your data.

    Using the Quick Analysis Tool to Create Charts

    As well as the Recommended Charts tool, Excel 2013 includes another quick way of creating charts called the Quick Analysis Tool.  This tool is so good it deserves a separate video which you will find here.

    Leave a Reply