# How to make a graph in excel 2010

Patterns and relationships between numbers are often easier to see when displayed in chart form. Our beginner’s guide provides step-by-step directions for creating charts in all versions of Excel.

We recommend reading the complete tutorial, but section links are provided below. For related tutorials, see our Chart & Graph Information Center.

## Tutorial Sections

• Choosing the Chart Type
• How to Select Data for a Chart
• How to Create a Chart in Excel
• Delete, Resize, Move, and Copy Charts

## Choosing the Chart Type

Before selecting the data you wish to graph, it’s helpful to have an idea of what chart type would best display your data. The type of data often determines the chart type.

For detailed descriptions of all chart types available in Excel, see our tutorial Excel Chart Types: Pie, Column, Line, Bar, Area, and Scatter Charts. The four most popular types of charts are briefly discussed below.

Type Image Description When to Use
Pie Chart Displays the percentages of a whole for each member in a series. Excellent chart for comparing values in a single series as percentages of a whole.
Column Chart Using vertical columns, displays values for one or more series over time or other category. Especially effective in comparing values for multiple series. The 3-D Column chart displays multiple series over three axes (X, Y, and Z).
Bar Chart Displays values for one or more series using horizontal columns. Though useful for single or multiple series, this chart type especially effective in comparing large quantity of values in a single series.
Line Chart Displays values as equally spaced points connected with a line. This chart is most useful displaying trends over time or other ordered category for single or multiple data series.

When you chart in Excel, you chart one or more data series. A data series is a row or column of numbers that are entered into the worksheet for graphing.

A pie chart can only chart a single data series. If multiple data series are selected, Excel will ignore all but the first. Most other chart types can handle multiple data series. In the worksheet below, we have outlined three data series which form a cell range.

Non-adjacent rows or columns can be selected, but they must form a rectangle.

## How to Select Data for an Excel Chart

To select the cells to chart, click and drag the cursor from the top left cell to the bottom right cell of your range, including column and row headings. Columns or rows containing totals should not be selected in most cases.

Non-contiguous rows and columns can be selected by pressing and holding the Ctrl key while selecting each data series. In the worksheet example below, we selected column headings for the four quarters, and just the data for “Trees.”

## How to Create a Chart in Microsoft Excel

After you’ve chosen your chart type and selected the data series (rows or columns) to chart, follow the directions below to insert the chart into your spreadsheet.

### Create a Chart in Excel 2007, 2010, 2013, 2016, and 2019

Note: In the new versions of Excel, hover the cursor over a chart type or sub-type on the Insert ribbon to display a description of the chart.

• Click the Insert tab .
• Click the chart type from the Charts section of the ribbon (see image). The sub-type menu displays.
• Click the desired chart sub-type. The chart appears on the worksheet.
• If you want to create a second chart, click somewhere in the worksheet to “deselect” the current chart first, or the new chart will replace the current chart.

### Create a Chart in Excel 2003, 2000, and 98

Note: In older versions of Excel, click the chart type or sub-type in the Chart Wizard to display a description of the chart.

• Click Insert | Chart . The Chart Wizard appears.
• Step 1: Click the desired chart type in the left column, and click one of the chart sub-types in the right column. Click Next .
• Step 2: Excel assumes you wish to keep the series data in rows. You may click “Columns” to see how the chart changes. When finished, click Next .
• Step 3: Type a chart title. If you wish to add a title for the axes, do so. Then click Next .
• Step 4: Excel assumes you want the chart placed on the worksheet. If you would like the chart placed in a new sheet, click the radio button, type a sheet name, and click Finish .

To select an existing chart, click on its border, or click in an empty space inside the chart. When selecting a chart, be careful not to click on an element inside the chart or that element will be selected instead.

## How to Delete a Chart

To delete a chart that has just been created, click the Excel Undo button. To delete an existing chart, select the chart by clicking on its edge, and press the Delete key, or right-click and select Cut.

## How to Resize a Chart

To resize a chart, select the chart and drag any of the chart’s corners.

## Moving and Copying Charts

A chart can be moved inside a worksheet by selecting the chart and dragging it to its new location. Charts can also be moved to a different or new worksheet in the same workbook. See How to Move a Chart to Another Worksheet.

Charts cannot be moved to a different Excel workbook, but can be copied. The process varies by version, so see How to Copy a Chart to Another Worksheet for more information. To copy an Excel chart to other Microsoft Office applications, see our Chart & Graph Information Center for a tutorial list.

For best results, keep the Excel chart simple and uncluttered. It is better to use multiple charts to express patterns and relationships between data than to use one chart that is too busy and over-complicated.

A colorful chart is ideal for online presentations or for printing on a color printer. But shades of gray are best for monochrome printing of charts.

## Charts

A chart is a visual representation of numeric values. Charts (also known as graphs) have been an integral part of spreadsheets. Charts generated by early spreadsheet products were quite crude, but thy have improved significantly over the years. Excel provides you with the tools to create a wide variety of highly customizable charts. Displaying data in a well-conceived chart can make your numbers more understandable. Because a chart presents a picture, charts are particularly useful for summarizing a series of numbers and their interrelationships.

## Types of Charts

There are various chart types available in MS Excel as shown in the below screen-shot.

Column − Column chart shows data changes over a period of time or illustrates comparisons among items.

Bar − A bar chart illustrates comparisons among individual items.

Pie − A pie chart shows the size of items that make up a data series, proportional to the sum of the items. It always shows only one data series and is useful when you want to emphasize a significant element in the data.

Line − A line chart shows trends in data at equal intervals.

Area − An area chart emphasizes the magnitude of change over time.

X Y Scatter − An xy (scatter) chart shows the relationships among the numeric values in several data series, or plots two groups of numbers as one series of xy coordinates.

Stock − This chart type is most often used for stock price data, but can also be used for scientific data (for example, to indicate temperature changes).

Surface − A surface chart is useful when you want to find the optimum combinations between two sets of data. As in a topographic map, colors and patterns indicate areas that are in the same range of values.

Doughnut − Like a pie chart, a doughnut chart shows the relationship of parts to a whole; however, it can contain more than one data series.

Bubble − Data that is arranged in columns on a worksheet, so that x values are listed in the first column and corresponding y values and bubble size values are listed in adjacent columns, can be plotted in a bubble chart.

Radar − A radar chart compares the aggregate values of a number of data series.

## Creating Chart

To create charts for the data by below mentioned steps.

Select the data for which you want to create the chart.

Choose Insert Tab » Select the chart or click on the Chart group to see various chart types.

Select the chart of your choice and click OK to generate the chart.

## Editing Chart

You can edit the chart at any time after you have created it.

You can select the different data for chart input with Right click on chart » Select data. Selecting new data will generate the chart as per the new data, as shown in the below screen-shot.

You can change the X axis of the chart by giving different inputs to X-axis of chart.

You can change the Y axis of chart by giving different inputs to Y-axis of chart.

Synopsis: In this article we look at how to create charts in Excel 2010.

The ability to create informative and good looking charts is one of the great strengths of Excel 2010. Recent versions of MS Excel have not only made this process easier, but have added powerful additional design and formatting features.

A chart is a visual representation of some data. Look at this worksheet of sales data for 2011.

The numbers corresponding to the individual sales and the various totals are included, but it is not easy to see any underlying trends or message. A chart can do this for you.

If you wanted to present a visual message about these sales figures you may want to show the sales for an individual, for a team, for a region or for the whole company. So, for example, you may want to show how Bill Armstrong’s sales figures for 2011 are looking.

The first thing to do is to select Bill’s figures. Here I’ve selected Bill’s name and the twelve numbers corresponding to his monthly sales.

Make that selection, select the Insert tab, then click on the arrow at the bottom of the Line button in the Charts group.

This lists the available line charts (also called line graphs) that are available. They are shown as small icons that give you an idea of what each will look like. Based on the icon you can choose a line chart type. However, if you make a choice and decide later it wasn’t the right one it’s not a problem as you can easily change the type later. Choose the top left one, and a line chart is created.

As you can see Excel 2010 has successfully worked out that “Armstrong, Bill” – which is in the first selected cell – is actually a good title for the chart. It has also treated the month numbers as the ones to use on the x-axis (category axis or horizontal axis). However, it doesn’t know what these numbers represent and it doesn’t know what the numbers on the y-axis (value axis or vertical axis) mean either. They’re just shown as numbers on the chart without any explanation. You can add some of this information manually if you need to, but some of it can be done by a better selection of data.

Let’s start again.

This time, select the data we did before and in addition select part of the row above that shows the month names.

Now select the Insert tab, then click on the arrow at the bottom of the Line button in the Charts group and choose the first option again. This time, this is how the chart looks.

Excel 2010 has used the data in the additional selected row and now the category axis is labeled with the month names.

Let’s look at a very different type of chart.

This time we’re going to compare the sales for the members of the North Region sales team for the month of January 2011. First, select cells A3:B7.

Now select the Insert tab. In the Charts group click on the arrow at the bottom of the Pie button.

Select the first option in the 3-D Pie section. This is the resulting pie chart.

This pie chart provides a simple comparison of sales for the five team members for that month.

Excel 2010 has many types of chart, and they are almost all created using the approach we’ve covered in this article. First you select the data, then you choose the chart type and Excel does the rest. Once Excel has created a chart, there are many options for you to improve its design and formatting.

##### Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course – the rest, as they say, is history!

This Excel tutorial explains how to create a basic column chart in Excel 2010 (with screenshots and step-by-step instructions).

## What is a Column Chart?

A column chart is a graph that shows vertical bars with the axis values for the bars displayed on the left side of the graph.

It is a graphical object used to represent the data in your Excel spreadsheet.

You can use a column chart when:

• You want to compare values across categories.

## Steps to Create a Column Chart

To create a column chart in Excel 2010, you will need to do the following steps:

Highlight the data that you would like to use for the column chart. In this example, we have selected the range A1:C7.

Select the Insert tab in the toolbar at the top of the screen. Click on the Column button in the Charts group and then select a chart from the drop down menu. In this example, we have selected the first column chart (called Clustered Column) in the 2-D Column section.

Now you will see the column chart appear in your spreadsheet with rectangular bars to represent both the sales and the expense numbers. The sales values are displayed as blue vertical bars and the expenses are displayed as red vertical bars. You can see the axis values on the left side of the graph for these vertical bars.

Finally, let’s add a title for the column chart. By default, your chart will be created without a title in Excel 2010.

To add a title, select the Layout tab under Chart Tools in the toolbar at the top of the screen (Chart Tools will only appear when you have the chart selected). Click on the Chart Title button in the Labels group and then select “Above Chart” from the drop down menu.

Now you should see a title appear at the top of the chart area. Click on the title and it will become editable. Enter the text that you would like to see as the title. In this tutorial, we have entered “Sales and Expenses” as the title for the column chart.

Congratulations, you have finished creating your first column chart in Excel 2010!

If you are looking for a quick and easy way to display trend data for a period of time, like stock prices, the best chart to use is the line chart and its variants. Excel comes the option to chart data using various line charts that allow you to customize the look and feel of the chart based on your needs. The tools to customize charts are too many and will be covered in more detail in upcoming how-to guides. In the meantime review this how-to so that you can learn how to create line charts in Excel.

I am attaching the Excel spreadsheet I used for this tutorial so that you can follow along.

Note: I used Excel 2010 in this tutorial, but these instructions will apply to Excel 2007 as well.

## Create a Line Chart in Excel

1. Click on the Row of data you wish to include in the line chart.

2. Click on the Insert tab in the Office Ribbon.

3. Click on the Line button in the Charts group.

4. Click on the Line Chart type you wish to use.

When you click on the Line Chart type a chart representing the selected data will appear. The chart is sitting in the same sheet the data is listed, letâ€™s move the chart to itâ€™s own sheet.

## How to Move the Chart to Another Location

Moving the chart to its own sheet helps you keep the data and the charts organized. It also helps when you are printing just the charts for handouts, etc.

1. Right-click on the chart you just created.

2. Click on Move Chart from the menu.

3. Select New sheet:

4. Name the new sheet.

5. Click OK to complete the move.

The chart should now be in its own sheet.

### Customize the Chart

When you click on the chart you will notice that the Office Ribbon changes and now displays Chart Tools in Green. Under Chart Tools you will find three tabs that are specific to customizing the chart.

1. Click on the chart to make sure the Chart Tools are visible.

2. In the Design tab, click on the a Chart layout in the Chart Layouts group.

Use the rest of the tools to customize the look and feel of your chart.

Pie charts are an effective way to analyze data sets visually. And since most people love pie, it’s a widely accepted format to present data to customers or management. All jokes aside, it might seem difficult to get started with a pie chart, but Microsoft Excel is an excellent tool for producing them with just a few simple clicks.

## Make a Pie Chart in Microsoft Excel 2010 or 2007

Update: If you are running modern versions of Excel in the Microsoft 365 (formerly O365) subscription suite, read how to make pie charts in Excel.

First, you’ll need to write your list of data in Excel. This is pretty easy, one column is your description of the numbers, and the other column is your numbers. You can make it look snazzy by styling it from the Home ribbon, but this won’t affect our pie chart.

Select the data that you want to include in the pie chart. This literally means holding down the left mouse button and dragging a box around the data. Be sure not to select the cells where you totaled things up.

Once everything is selected, click the Insert ribbon and then click Pie and select whether you want a 2-D or a 3-D pie; and which template.

Now your pie chart should show up on your spreadsheet. The next thing we’re going to do is customize the chart to make it more appealing.

When you have the chart selected (click on the chart), Chart Tools will appear on the ribbon. This includes a total of three different tabs: Design, Layout, and Format.

Each tab from the Chart Tools ribbon area will let perform several different customizations. The only way to really learn what you can do is to play around with the different settings for a while. But, as an example, I’ve customized my chart using the tools outlined in the three screenshots below.

Now you have a spiffy-looking pie chart in your Microsoft Excel spreadsheet. When it comes to making pie charts this attractive, it doesn’t get much easier than this. Do you have a favorite tip or trick for making pie charts in Excel? We’d love to hear it in the comments below.

## Paras Doshi’s Blog on Analytics, Data Science & Business Intelligence.

• Blog Home
• Archives
• Contact Paras
• View Paras.Doshi.Blog’s profile on Facebook
• View paras_doshi’s profile on Twitter
• View doshiparas’s profile on LinkedIn

600+ blogposts
2M+ Visitors
5K+ followers

These are my personal views and are meant for Informational purpose only. Please verify the Information via Professional help or via Official references before acting upon the information provided in this Blog.

Based on a work at parasdoshi.com.

Permissions beyond the scope of this license may be available at http://parasdoshi.com/contact/.

Thanks,
Paras Doshi

In this post, I’ll show you how you can add secondary axis in a chart in Excel 2010:

#### First up, Why do you need secondary axis?

Look at the Data and the chart that I just created:

Notice something? Column A is not “visible” – Is it? So Now let’s see the effect how adding a Secondary Axes:

#### Looks better? Here are the steps:

1. Select the chart

2. can you see charts tool options in the menu bar? Yes? Great!

3. Switch to Format Tab

4. select the column that you want to put on a secondary axes:

5. After selecting the column, click on Format Selection

6. In the dialog box, select secondary axes:

click on close.

7. Can you see the secondary axis now? Great!

8. Let’s do one more thing!

Let’s change the chart type of the secondary axis. Here are the steps:

a. Make sure the column is selected from the format Tab

b. Switch to the Design Tab under the charts Tool

c. Click on change chart type and select the chart of your choice. I select a column chart and this is how it looks:

#### Conclusion:

In this post, we saw how to add a secondary axis. We also saw how to change the chart type of the secondary axis.

Supposing you have created a chart to track the daily sales based on a range of data in your workbook. But you need to change or edit the number of the data every day, in this case, you have to update the chart manually so it includes the new data. Are there any quick tricks to help you auto update a chart when you add new data to an existing chart range in Excel?

#### Auto update a chart after entering new data with creating a table

If you have the following range of data and column chart, now you want the chart update automatically when you enter new information. In Excel 2007, 2010 or 2013, you can create a table to expand the data range, and the chart will update automatically. Please do as this:

1. Select the data range and click Table under Insert tab, see screenshot:

2. In the Create Table dialog box, if your data has headers, please check My table has headers option, then click OK. See screenshot:

3. And the data range is formatted as a table, see screenshot:

4. Now, when you add values for June, and the chart will be updated automatically. See screenshot:

Notes:

1. Your new entering data must be adjacent to the above data, it means there is no blank rows or columns between the new data and the existing data.

2. In the table, you are able to insert data between the existing values.

#### Auto update a chart after entering new data with dynamic formula

But sometimes, you don’t want to change the range to table, and the above method is not available in Excel 2003 or earlier version. Here I can introduce you a complex dynamic formula method. Take the following data and chart for example:

1. First, you need to create a defined name and a dynamic formula for each column. Click Formulas > Define Name.

2. In the New Name dialog box, enter Date into the Name box, and choose current worksheet name from Scope drop down list, and then enter =OFFSET(\$A\$2,0,0,COUNTA(\$A:\$A)-1) formula into the Refers to box, see screenshot:

3. Click OK, and then repeat the above two steps, you can create a dynamic range for each series using the following range names and formulas:

• Column B: Ruby: =OFFSET(\$B\$2,0,0,COUNTA(\$B:\$B)-1);
• Column C: James: =OFFSET(\$C\$2,0,0,COUNTA(\$C:\$C)-1);
• Column D: Freda: =OFFSET(\$D\$2,0,0,COUNTA(\$D:\$D)-1)

Note: In the above formulas, the OFFSET function refers to the first data point, and the COUNTA refers to the entire column of data.

4. After defining the names and formulas for each column data, then right click any column in your chart, and choose Select Data, see screenshot:

5. In the Select Data Source dialog box, from Legend Entries(Series) section, click Ruby and then click the Edit button, in the popped out Edit Series dialog, enter =Sheet3!Ruby under Series values section, see screenshots:

6. And then click OK to return to the Select Data Source dialog, repeat the step 5 to update the remaining series to reflect their dynamic ranges:

• James: Series values: =Sheet3!James;
• Freda: Series values: =Sheet3!Freda

7. After setting the left data, now you need to click Edit button under Horizontal (Category) Axis Labels to set this option, see screenshots:

8. Then click OK > OK to close the Select Data Source dialog, after finishing these steps, you’ll find that the chart updates automatically when you add new data to the worksheet.

Notes:

• 1. You must enter new data in a contiguous manner, if you skip rows, this method will not work as expected.
• 2. If you enter new column data, this method will not take effect.

Tip.If you want to quickly export range contents from sheet to a graphic, please try to use the Kutools for Excel’s Export Range as Graphic as shown in the following screenshot. It’s full function without limitation in [module 745> days, please download and have a free trial now.

I have been looking online tirelessly for a solution to the following problem but to no avail. I am trying to create a seasonal plot for the table below. My goal is to plot sales against quarter, classified by year.

Any help would be much appreciated! Thank you.

Do these look like something you want to get?

I used fake data just over 3 years.

Each years is represented by a line.

Each line spans over 4 quarters.

To make this you select:

1) Series are years => 3 years = 3 series, Give each a name (e.g. 2003) and input values so for year 2003 my values are =Sheet1!\$C\$1:\$C\$4

2) Then on the right edit your X-axis the values there are =Sheet1!\$B\$1:\$B\$12

EDIT

For this you’ll have to combine year and quarters as shown e.g. 2003 Q1 I guess you can’t create a 2-dimensional chart using all 3 inputs

Make the following modifications to your data.

1. Clear the cells above the years and the quarters.
2. Remove all but the first instance of each year in the first column.

Select the data range and insert your chart. You will get a category axis with two levels, quarter close to the axis and year below that. This is the technique in my tutorial Chart with a Dual Category Axis.

I’ve illustrated this with a subset of your data (note blank cells with faint yellow shading). You can make a column or line chart directly from this data.

Up to Microsoft Excel 2003, there was a in-built Custom Chart Type called the 2-Axis Chart. This was a pretty useful chart type, in which I used to display very small numbers and very large numbers – all on the same chart. You can see in this chart below, the numbers on the Primary Y Axis on the left are from 0 to 16. The numbers on the Secondary Y Axis (on the right side of the chart), are from 20,000 to 180,000.

2 Axis Chart in Microsoft Excel

But this 2 Axis chart type magically disappeared from Excel 2007, Excel 2010 & even Excel 2013. So today, I will show you how to create a 2 Axis chart in any version of Excel – whether you are still using Microsoft Excel 2007 or Microsoft 2010, or the latest Excel 2016, 2019 or Office 365 (Cloud version of Microsoft Office).

## What is a 2 Axis Chart?

The default Bar Chart or a Column chart of Excel has One X Axis (The Horizontal side), and One Y Axis (represented Vertically). This is generally useful most of the time.

However, once in a while, when you want to display 2 different sets of numbers in the same chart, and one set of numbers (Quantity) comprises of very small numbers, and the other set of numbers (Sales figures) are very large – in Thousands.

If you plot a normal Column chart, the Sales figures are so high that the bars are quite long, and the scale is set in Thousands. So the Quantity figures are too small to be even seen in the Column Chart.

A 2 Axis chart is slightly different from a normal, standard Column Chart.

The 2 Axis Column Chart uses TWO Y Axis in the same chart. The one on the Left of the chart is used to display one scale (Small Numbers), and another Y Axis is added on the Right Side, which represents the (Large Numbers) in Thousands.

In this way, both small numbers and very large numbers are seen in the same chart – using both the Primary Axis and the Secondary Axis.

However, do note that the Columns of the Chart will overlap each other, and it will be difficult to see one set of values. To overcome this situation, we convert one of the bars into a Line Chart.

In this way, even if the scale is different, both axis can be seen clearly, and a useful 2 Axis chart is created easily in Microsoft Excel.

This Excel tutorial explains how to create a basic bar chart in Excel 2010 (with screenshots and step-by-step instructions).

## What is a Bar Chart?

A bar chart is a graph that shows horizontal bars with the axis values for the bars displayed on the bottom of the graph.

It is a graphical object used to represent the data in your Excel spreadsheet.

You can use a bar chart when:

• You want to compare values across categories.
• The category text is long and difficult to display in a column chart.
• You want to show duration in a graph.

## Steps to Create a Bar Chart

To create a bar chart in Excel 2010, you will need to do the following steps:

Highlight the data that you would like to use for the bar chart. In this example, we have selected the range A1:C5.

Select the Insert tab in the toolbar at the top of the screen. Click on the Bar button in the Charts group and then select a chart from the drop down menu. In this example, we have selected the first bar chart (called Clustered Bar) in the 2-D Column section.

Now you will see the bar chart appear in your spreadsheet with horizontal bars to represent both the shelf life and restock time for each product. The shelf life (in days) is displayed as red horizontal bars and the restock time (in days) is displayed as blue horizontal bars. You can see the axis values on the bottom of the graph for these horizontal bars.

Finally, let’s add a title for the bar chart. By default, your chart will be created without a title in Excel 2010.

To add a title, select the Layout tab under Chart Tools in the toolbar at the top of the screen (Chart Tools will only appear when you have the chart selected). Click on the Chart Title button in the Labels group and then select “Above Chart” from the drop down menu.

Now you should see a title appear at the top of the chart area. Click on the title and it will become editable. Enter the text that you would like to see as the title. In this tutorial, we have entered “Product Life (in Days)” as the title for the bar chart.

Congratulations, you have finished creating your first bar chart in Excel 2010!

Updated April 23, 2022

A picture is worth of thousand words; a chart is worth of thousand sets of data. In this tutorial, we are going to learn how we can use graph in Excel to visualize our data.

## What is a chart?

A chart is a visual representative of data in both columns and rows. Charts are usually used to analyse trends and patterns in data sets. Let’s say you have been recording the sales figures in Excel for the past three years. Using charts, you can easily tell which year had the most sales and which year had the least. You can also draw charts to compare set targets against actual achievements.

We will use the following data for this tutorial.

Note: we will be using Excel 2013. If you have a lower version, then some of the more advanced features may not be available to you.

Item 2012 2013 2014 2015
Desktop Computers 20 12 13 12
Laptops 34 45 40 39
Monitors 12 10 17 15
Printers 78 13 90 14

## Types of Charts in MS Excel

Different scenarios require different types of charts. Towards this end, Excel provides a number of chart types that you can work with. The type of chart that you choose depends on the type of data that you want to visualize. To help simplify things for the users, Excel 2013 and above has an option that analyses your data and makes a recommendation of the chart type that you should use.

The following table shows some of the most commonly used Excel charts and when you should consider using them.

## The importance of charts

• Allows you to visualize data graphically
• It’s easier to analyse trends and patterns using charts in MS Excel
• Easy to interpret compared to data in cells

## Step by step example of creating charts in Excel

In this tutorial, we are going to plot a simple column chart in Excel that will display the sold quantities against the sales year. Below are the steps to create chart in MS Excel:

• Open Excel
• Enter the data from the sample data table above
• Your workbook should now look as follows

To get the desired chart you have to follow the following steps

• Select the data you want to represent in graph
• Click on INSERT tab from the ribbon
• Click on the Column chart drop down button
• Select the chart type you want

You should be able to see the following chart

### Tutorial Exercise

When you select the chart, the ribbon activates the following tab

Try to apply the different chart styles, and other options presented in your chart.

## Summary

Charts are a powerful way of graphically visualizing your data. Excel has many types of charts that you can use depending on your needs.

Conditional formatting is also another power formatting feature of Excel that helps us easily see the data that meets a specified condition

Line charts are used to display trends over time. Use a line chart if you have text labels, dates or a few numeric labels on the horizontal axis. Use a scatter plot (XY chart) to show scientific XY data.

To create a line chart, execute the following steps.

1. Select the range A1:D7.

2. On the Insert tab, in the Charts group, click the Line symbol.

3. Click Line with Markers.

Note: only if you have numeric labels, empty cell A1 before you create the line chart. By doing this, Excel does not recognize the numbers in column A as a data series and automatically places these numbers on the horizontal (category) axis. After creating the chart, you can enter the text Year into cell A1 if you like.

Let’s customize this line chart.

To change the data range included in the chart, execute the following steps.

4. Select the line chart.

5. On the Design tab, in the Data group, click Select Data.

6. Uncheck Dolphins and Whales and click OK.

To change the color of the line and the markers, execute the following steps.

7. Right click the line and click Format Data Series.

The Format Data Series pane appears.

8. Click the paint bucket icon and change the line color.

9. Click Marker and change the fill color and border color of the markers.

To add a trendline, execute the following steps.

10. Select the line chart.

11. Click the + button on the right side of the chart, click the arrow next to Trendline and then click More Options.

The Format Trendline pane appears.

12. Choose a Trend/Regression type. Click Linear.

13. Specify the number of periods to include in the forecast. Type 2 in the Forward box.

To change the axis type to Date axis, execute the following steps.

14. Right click the horizontal axis, and then click Format Axis.

The Format Axis pane appears.

15. Click Date axis.

Conclusion: the trendline predicts a population of approximately 250 bears in 2024.

In this article, you will learn How to Overlay Charts in Excel and easily compare two sets of data in one graph, like actual sales vs target sales results, actual rating vs target rating, etc.

If you try to plot Actual vs Target values in form of a Clustered Column in Excel, it will look like this:

Even though, it will get the job done it will be slow to read and difficult to interpret by the users. Comparing the right of each bar will take time and may be prone to errors.

Whereas, by just changing the format of the Clustered column we can convert it to overlay chart. Overlay Chart will help you to interpret the data at a glance.

This overlay of graphs may seem like a difficult exercise but it is a very easy process.

### Keep reading to learn how to overlay charts in Excel!

In the example below, we have both actual and planned sales amount for each month and you need to plot it on a graph for easy comparison.

Watch How to overlay two graphs in Excel on YouTube and give it a thumbs-up!

Follow the step-by-step tutorial on how to overlay graphs in Excel and download this Excel workbook to practice along:

STEP 1: Select all the cells in the table.

STEP 2: Go to Insert Tab > In the Charts Group, click on the Clustered Column Chart icon.

A clustered column chart will appear next to the data table.

STEP 3: Click on the Plan Value Bars.

STEP 4: Right-click on the bar and select Format Data Series.

STEP 5: In the Format Data Series dialog box, select Series Overlap as 100%.

STEP 5: Under Fill Tab, Select No Fill.

STEP 6: Select Solid Line as Border, Dark Blue as Color, and 2pt as Width.

STEP 7: Right Click Actual Value Bar and Select Format Data Series.

STEP 8: Select Solid Fill and Light Blue as color.

STEP 9: Select Gridlines and Press Delete.

STEP 10: Select Chart Title and Press Delete.

### Conclusion

In summary, this is how to superimpose graphs in Excel:

First, you need to edit your “Plan” graph by clicking on its series and pressing the CTRL+1 shortcut. Then within the Format box, you need to choose:

FILL: NO FILL
BORDER COLOR: SOLID LINE & DARK
BORDER STYLE: 2pt WIDTH

Then you need to edit your “Actual” graph by clicking on its series and pressing the CTRL+1 shortcut. Then within the Format box, you need to choose:

FILL: SOLID FILL & LIGHT COLOR
SERIES OPTIONS: 100% OVERLAPPED
GAP WIDTH: 60%

So, just by changing the format of the chart you can interpret the data easily.

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

You can learn more about how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables, Power Query, and Macros & VBA!

For example, if you have two indicators (e.g., volume and price), which you want to see in one chart:

To create one chart for this data, follow these steps:

1. Create the chart with that data range:

For example, on the Insert tab, in the Charts group, choose the Column button:

Choose Clustered Column. You can see not informative chart because of too different amounts:

2. Excel use primary axis in charts, to change which axis – primary or secondary – can uses for the data series, do the following:

Right-click in the second (price) data series, in the popup menu select Format Data Series. to open the Format Data Series dialog box:

3. To change a format type of the second data series, right-click in the second (price) data series and choose Change Series Chart Type. :

You can choose another chart type, for example, a line.

How to add next data series with another axis, see Combining several charts into one chart.

### Creating a Candlestick Stock chart

• Pinterest
• Privacy
• Terms
• Contact

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

When you create a chart to analyze the trend of the data, it is useful to add a horizontal Average Line across the chart, so that you can compare the data clearly and easily. In this tutorial, I’ll show you how to add an Average Line to Column Chart in Excel 2010.

## Add a New Average Value Column

In this example, we have a group of sales data (range A1:B7). You may copy A1:C7 to a new Worksheet to start this tutorial.

We want to display the average sales for the group in our chart, so we need to insert a new Helper Column that we called Average. We’ll use Average function to calculate the average of entire group. In Column C2, type this formula:

then drag down to C7.

## Create Chart

1. Select range A1:C7.
2. Under the Insert menu tab, in the Charts group, click the Column button and choose Clustered Column in 2-D Column.
3. Right-click any of the columns in the Average series on the chart, In the quick menu, click Change Series Chart Type…
4. In the Change Chart Type dialog, choose a basic Line chart.
5. click OK.

## Formatting Series “Average”

1. Select the chart, this displays Chart Tools.
2. On the Layout tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click Series “Average”.
3. Press CTRL+1.
4. On the Series Options tab, under Plot Series On, click Secondary Axis, then Click Close.

## Formatting Secondary Horizontal Axis

1. On the Layout tab, in the Axes group, click Axes.
2. Click Secondary Horizontal Axis, and then click Show Left to Right Axis.
3. Select Secondary Horizontal (Category) Axis.
4. Press CTRL+1. The Format Axis dialog box is displayed.
5. On the Axis Options tab, In Major tick mark type, select None. In Axis labels, select None. under Position Axis, click On tick marks, then Click Close.
6. On the Line Color tab, under Line Color, click No line.
7. Click Close.

## Formatting Vertical (Value) Axis

1. Select Vertical (Value) Axis.
2. Press CTRL+1. The Format Axis dialog box is displayed.
3. On the Axis Options tab, under Axis Options, in Minimum, click Fixed, input value 0, in Maximum, click Fixed, input value 6000, in Major unit, click Fixed, and input value 1000.
4. Click Close.

## Formatting Secondary Vertical (Value) Axis

1. Select Secondary Vertical (Value) Axis.
2. Press CTRL+1. The Format Axis dialog box is displayed.
3. On the Axis Options tab, under Axis Options, in Minimum, click Fixed, input value 0, in Maximum, click Fixed, input value 6000, in Major unit, click Fixed, and input value 1000. In Major tick mark type, select None. In Axis labels, select None.
4. On the Line Color tab, under Line Color, click No line.
5. Click Close.

## Formatting Average Point Label

1. Select Series “Average”.
2. Click Series “Average” Point “Robin” (the last point).
4. Press CTRL+1. The Format Data Point dialog box is displayed.
5. On the Marker Options tab, under Marker Type, select Built-in, then select a type, in this example we select Round.
6. Click Close.

At this point, all the major steps completed, your chart will looks like this:

You may add the Chart Title or delete Major Gridlines and Legend to make it more beautiful. If you use Excel 2013, all the steps will be clearly and easily, just do it.

## Excel Stock Chart

Stock chart in excel is also known as high low close chart in excel because it used to represent the conditions of data in markets such as stocks, the data is the changes in the prices of the stocks, we can insert it from insert tab and also there are actually four types of stock charts, high low close is the most used one as it has three series of price high end and low, we can use up to six series of prices in stock charts.

### How to Create Stock Charts in Excel? (Step by Step)

In order to create this chart, we need to have proper data on stock prices on daily-wise. We need to have an opening price, a high price in the day, a low price in the day, and what is the close price in the day. So for our demonstration purpose, I have created the below data of stock prices.

Select the data in the worksheet.

Go to INSERT > Stock Chart > Open-High-Low-Close.

Now, we will have a chat like below.

Select the vertical axis and press Ctrl + 1.

In the format data series box, make minimum as 50 and Maximum as 65. Major to 1.

Now enlarge the chart vertically and horizontally. We will have a chart below.

In this chart, we can see for each date. We have the box with upward and downward arrows. Upward and downward arrows represent opening and closing stock prices for each date, but we don’t know exactly which is opening and which is closing stock price. The reason why we cannot say which is opening and which is closing because there are situations where an opening is more and closing is less and vice-versa.

So, how do we identify which is the opening price and which is the closing price?

Some boxes have filled color, and some don’t. In order to identify which is open and which is close, we need to rely on these boxes.

“If the box is non-filled, then the opening price is a downward arrow,” i.e., the stock is profitable for the day.

“If the box is filled, then the upward arrow is the opening price,” i.e., the stock is under loss for that day.

The continuous line of an upward arrow indicates the high price for the day, and the continuous line of a downward arrow indicates the low price for the day.

Like this using Stock Chart, we can analyze the charts and make some interpretations. We have four kinds of stock charts available with excel, below are types.

“High – Low – Close,” “Open – High – Low – Close,” “Volume – High – Low – Close,” and “Volume – Open – High – Low – Close.”

Based on the structure of the data, we can choose the appropriate one to show the numbers in the graph.

### Recommended Articles

This has been a guide to Stock Chart in Excel. Here we learn how to create a stock chart in excel with an example and downloadable excel template. You can learn more about excel functions from the following articles –

To create a simple waterfall chart, do the following:

1. Add three columns with “Y empty” data, “Y plus” data and “Y minus” data (you can add a column for empty data and a new column with independent data for every unique color in you waterfall chart), where:

C: = IF (B5 > B4, B4, B5), D: = IF (B5 > B4, B5 – B4, 0), and E: = IF (B5 > B4, 0, B4 – B5)

2. Add rows with empty “Y” data, if necessary (in this example 5, 7, 8, 11, 13, 14 and 16) and then add two columns for continuous line (see in step 7).

3. Select the data range (in this example C4:E17).

4. On the Insert tab, in the Charts group, choose the Column button:

Choose Stacked Column. Remove the legend.

5. Right-click in the any of first series column and choose Format Data Series. :

6. In the Format Data Series dialog box, on the Fill tab, select No fill option and then click OK:

You can then make any other adjustments to get the look you desire.

7. To add the continuous line, do the following:

Right-click in the chart area and choose Select Data. in the popup menu:

### Creating a glossy thermometer chart

• Pinterest
• Privacy
• Terms
• Contact

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Hi, I want to make a comparison chart in Excel 2010. (Comparison of Admin & Development Expenses with Revenue). The admin expenses have their own dates, development expenses have their own dates & revenue have their own dates.

I want to make a bar chart for it, where 1 bar will represent Expenses (Both Admin & Development) and the Other Bar will represent Revenue.

Please tell me, How can I make a comparison chart for it?

• Status: Open
• Question Views: 3059
• Vote Up 0 Vote Down
• Question Category: MS Excel

## How to Create Comparison Bar Chart in Excel 2010?

• Vote Up 0 Vote Down
• Posted on – 10/25/2012
• Question Category: MS Excel

## How to Create Comparison Bar Chart in Excel 2010?

This work is really an easy one. But for that you must have to know the tabs and few silly works. Now please follow the instructions below.

1. Open a new workbook.

2. Copy the data from every column and paste them into the new workbook.

3. Select insert tab, in the middle of the submenu you will find chart.

4. Now select the columns from the new workbook.

5. Click on the chart icon and you will get the chart you are looking for.

You can easily show the comparison in the bar charts. I hope this will help you in your work.

If youâ€™ve ever tried making sense of a lot of trend data in Excel youâ€™ve usually found that plotting the data using a line chart or bar graph helps you make sense of it all. However what happens when you have more than one data set and all you need is a quick preview? Rather than generating a line chart for each data set or plotting all of the data sets with one chart you can use one Excelâ€™s newest features, a Sparkline. Sparklines are tiny charts in a worksheet cell that provide you with a visual representation of data in a worksheet.

As you can see in the screenshot above, I have a monthly data set for about 100 keywords. I used Sparklines to give me a visual representation of keywordâ€™s trend over the past 6 months (a small chart per keyword). To learn how to use Sparklines in Excel 2010, please follow the instructions below.

If you have any comments or questions please use the comments form at the end of the article.

Note: I used Excel 2010 for this guide.

1. Click on the cell you would like to insert the Sparkline into.

2. Click on the Insert tab on the Office Ribbon.

3. Click on one of the Sparklines listed. In this guide I plan on using the Line Sparkline because it will show me a linear trend which is what Iâ€™m looking for.

You will have the option to use one of the three following options:

Line

Column

Win/Loss

Use the one that best suits your data set.

4. Click on the Data Range button to select the data range.

5. Click OK to save the range.

A. You will notice that the Sparkline you created will appear in the selected cell.

B. You can edit the Sparklineâ€™s colors, format, and more using the Sparklines > Design Tab on the Office Ribbon.

6. If you wish to copy the Sparkline to other cells using the same formatting simply place the mouse cursor on the corners edge of the cell containing the Sparkline and click.

a. While holding the left mouse button drag down to fill the other cells. Let go of the mouse button when you are done.

Creating charts in Excel is quite easy: Select the data and choose your desired chart type on the ‘Insert’ ribbon. But when it comes to combining two chart types – for example a column chart with a line on top – many users suddenly struggle. But actually, it’s almost as simple as inserting a normal chart. Let’s have a look at how to do it and how to further adjust your chart.

## Steps for combining two chart types

Creating a chart with two different chart typesВ is comparativelyВ simple in newer versions of Excel (the numbers are corresponding with the following picture):

1. Prepare you data. In thisВ case, youВ have the data (column B, ‘Month’) for the x-axis in the first column. The second column (C, ‘Revenue’) contains the data for the columns of the chart. The last column (in thisВ case the revenue per customer) has the data for the line graph which you want to show above the column chart.
2. Click on Insert and click on Combo (on the charts section).
3. Click on “Clustered Column – Line on Secondary Axis”.
4. The result looks like no. 4.

## If there is no ‘Combo’ chart type button…

Sometimes the combo chart type is not available, for example in Office for Mac. In such case you have to create the combination of the two chart types manually:

• First set up a normal stacked column chart with all the data in it (also the data for the line chart).
• Next, click on the column in the chart which contains the values for the line chart.
• Right click on it “Change Chart Type” and select the desired chart type.
• Right click on the data series again and click on “Format Data Series”. The formatting bar should appear on the right hand side.
• Now you can further determine on which axis the line should be shown now: Select the secondary axis.

These stepsВ work universally – also if you have a stacked or clustered column chart and want to display one series as another chart type.

## Steps under Excel 2016

T he chart menu has changed a little bit in Excel 2016. There are also new chart types available. The basic steps for creating combo charts are similar to before. Just follow the steps as shown above or this animation:

The gif above was too fast? Here are the steps in detail:

Benchmark Charts or Budget Charts are a great way to show your actual sales versus your benchmark in a graphical way, highlighting the strong v weak months.

I will show you how to do this using Using Excel 2010.

STEP 1: Enter your data into three columns, the month, the actual sales and the benchmark sales

STEP 2: Click inside your data and go to the ribbon and choose Insert > 2-D Clustered Column

STEP 3: Select the Benchmark series chart and Right Click and choose the Change Series Chart Type

STEP 4: This will bring up the Change Chart Type dialogue box and you will need to select Line and Line with Markers

STEP 5: Click on the line chart and press CTRL+1. This will open up the Format Data Series dialogue box

STEP 6: Select the Marker Options and choose Built In, select the horizontal line type and increase the Size to 10. You can also format the color of the line.

STEP 7: Select the Line Color and choose No Line and press OK!

With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage. Read more.

Charts are helpful for visual displays of your data. They can make viewing and analyzing data easier, especially for your audience. So for geographical data, why not use the map chart type in Microsoft Excel?

Whether you want to display populations in several countries for your sales team or abbreviations for the United States for your elementary school class, you can create a map chart easily in Microsoft Excel. You can then format it with labels, a legend, and an appropriate color scheme.

Like with other chart types in Microsoft Excel, it’s best to start with populated data. This allows you to select the cells containing that data and simply insert the chart for it.

Select a single data type for the map. Like the examples listed above, this can be the population or abbreviation that you can obtain from the built-in geography data type in Excel. Or, alternately, it might be your sales amounts, number of stores, average operating costs, or other data you have already prepared in your spreadsheet.

While you can use the chart for smaller areas, such as cities, the map won’t be as effective. You’ll only see dots representing those types of locations. The map chart in Excel works best with large areas like counties, states, regions, countries, and continents.

## Create the Map Chart

When you’re ready to create the map chart, select your data by dragging through the cells, open the “Insert” tab, and move to the “Charts” section of the ribbon. Click the drop-down for “Maps” and choose “Filled Map.”

## Format the Map Chart

The next steps include formatting your map to include useful components and a color scheme. You can add a title, some labels, a legend, and a style.

If your map contains the default “Chart Title,” you can simply click that text box on the map and type in your own title. If the map does not include a title, you can add one easily as well as changing its position.

Select the chart and click “Chart Elements” (“+” icon) on the right. Check the box for “Chart Title,” then enter the title in the text box that appears on the map.

Hover your cursor over the arrow to the right of “Chart Title” in the Chart Elements box and choose a different position for the title if you like.

You can also select “More Title Options,” which will display a sidebar on the right. In this spot, you can choose the position, use a fill color, or apply a text outline.

### Include Data Labels

If the data you’re displaying on the map is small enough, you may want to include data labels. These will show the data directly on each map location.

Select the chart and click “Chart Elements” on the right. Check the box for “Data Labels.”

To adjust the text and options for the labels, select “More Data Label Options.” Like with the chart title, the right-hand sidebar will open with formatting features for the labels.

### Insert a Legend

One more helpful element for almost any chart is a legend. This useful tool is a key to the data that displays on the chart.

Select the chart and click “Chart Elements” on the right. Check the box for “Legend.”

Hover your cursor over the arrow to the right of “Legend” in the Chart Elements box and choose a position for the legend.

You can also select “More Legend Options” to open the sidebar and format the text and additional options.

### Apply a Style

The map chart is pretty basic in its color, but you can spruce it up with a theme or color scheme.

Select the chart and click “Chart Styles” (found on the paintbrush) on the right. Click “Styles” to scroll through premade themes or “Color” to pick a colorful or monochromatic scheme.

Whichever you choose, you’ll see your map update immediately.

## Move, Resize, Edit, or Delete the Map

You can easily move the map chart anywhere you want on your sheet. Just select and drag it to its new spot. If you want to prevent changes, you can lock the Microsoft Excel chart in place. And to resize the chart, drag one of the corners or edges.

To edit the data that displays on the map, for example, if you add more locations, right-click the chart and click “Select Data.” Alternatively, you can go to the “Chart Design” tab and click “Select Data” in the ribbon.

Enter the cells in the “Chart Data Range” box at the top, or drag your cursor through the cells. Click “OK” when you finish.

If you decide that you don’t want to use the map chart after all, you can remove it easily. Select the chart and hit your Delete key or right-click and pick “Cut.”

If you want to display geographical data on your Excel sheet, the map chart is a terrific visual tool. For other types of data, take a look at how to make a bar chart in Microsoft Excel.

When you have data that involves time information, you may need some special tricks to get Excel to help you create useful charts. Here are some tips for editing time and date data in an Excel chart. These features apply to Excel 2007-2013, though the specific steps will vary based on your version. Images were taken using Excel 2013 on the Windows 7 OS.

Use an XY – Scatter Chart

By far, the easiest way to chart time data is to use a scatter chart. Scatter charts automatically take date or time data and turn it into a time-scale axis. When you select a date or time range, and the data associated with it, Excel will take its best guess at organizing the information in the chart with the time-scale on the x-axis.

However, Excel’s best guess might not be as useful as you need it to be. In this example, we want to see how, or if, our series data are affected by the time of day. The resulting scatter chart does a nice job of plotting the series data, but the timeline defaults to what seems to be random units of time.

To adjust how the x-axis time-scale is displayed:

1. Click on the chart to open the Format Chart Area Pane.
2. Click on Chart Options and select Horizontal (Value) Axis.
3. Click the Axis Option Icon.
4. Open the Axis Options dropdown triangle.
5. Make changes to the Bounds, Units, and so on to adjust the time-scale to display the chart in the manner you wish.

You may have to play with the Units settings to get your scale to show the time increments you want. In this example, we wanted our unit markers to appear every hour and the chart covered 24 hours of data. So, our Unit setting equals 1 / 24 or .04167. A setting of .25 would show Unit markers at 12:00 a.m., 6:00 a.m., 12:00 p.m. and 6:00 p.m.

The results are a clean, uncluttered chart that plots series data evenly and clearly across a 24 hour period.

Make sure Axis Type is set to Date Axis

When you are creating a line, column or bar chart, Excel will automatically treat date data as a “Date axis”. This means that each data point will be plotted on the x-axis based on linear time, rather than equal distance from each other.

AB–Text shows the x-axis set to Text axis and the data points are equally spaced across the chart, even though the dates are not even throughout the month.

AB–Date shows the x-axis set to Date axis and the data points are further or closer together based on when the data was recording during the month. If AB represented bank account withdrawals, you can more easily see in AB-Date that more transactions take place in the middle and end of the month. Follow the steps above to open Axis Options to set your x-axis to Date Axis if Excel’s default chart does not do so.

Hint: If your date data is entered as text instead of the Date format, then the Date Axis option will not work. Change your data to the Date format to take advantage of Date Axis.

What about Time Data on Line Charts?

Sadly, as of Excel 2013, the Date Axis feature does not work on Time data in Line, Column and Bar charts. If you try, you get something like this:

If you need to plot information over time, the easiest solution is to use an XY Scatter Chart and add Lines and Markers to resemble a Line Chart.

What kinds of Time Series Charts do you create in Excel?

#### PRYOR+ 7-DAY FREE TRIAL

Experience 7-days of unlimited learning, then reap the benefits from a year of training at one low price. No credit card. No commitment. Individuals and teams.

## PICK Charts for Risk/Reward Assessments

Most decisions boil down to an assessment of risks and rewards. For example, anyone who has invested in mutual funds or bonds knows that each investment is rated according to their expected level of return versus the risk of the loss of the principal invested. The same decision-making analysis is used in project portfolio management in selecting and prioritizing projects along the risk/reward continuum.

A PICK chart is used to rank projects considering their level of difficulty (the risk of committing valuable resources) and the level of payoff (the reward). The quadrants of the PICK matrix reflect the four dominant risk/reward scenarios.

P****ossible (Lower Left Quadrant) Easy to execute, but with a low payoff, which in business language means take if nothing better comes along.

Implement (Upper Left Quadrant) Easy to execute with a high payoff. In other words, grab this one fast before it gets away.

C****hallenge (Upper Right Quadrant) Difficult or (challenging) to execute but with a high rewards payoff. These projects are for individuals who get their job satisfaction from seeing their “hard work payoff.”

K****ill (Lower Right Quadrant) Hard to execute with a low payoff. The only question left on this one is where is the trash can?

## Steps to Create a PICK Chart in Excel

MS Excel 2010 already has templates to create a four quadrant matrix, which will save you a lot of time in your initial setup of a PICK chart. To create a PICK chart in the Excel 2003, the user needed to start from scratch by formatting groups of cells to create the appearance of the matrix.

Inserting the Matrix

Select the Insert Tab on the ribbon and then choose SmartArt.

Click on Matrix and select the Titled Matrix and this standard matrix will appear.

Labeling the Matrix

Label each quadrant clockwise as follows: Challenge, Kill, Possible, and Implement by typing the names in the left hand box.

Label the overlap area as PICK + Year, which will serve as the title of your matrix. You can adjust the size of the matrix and text labels by expanding and shrinking the size of the matrix manually.

Creating and Labeling the X and Y Ax****es

The next step to create the PICK chart is to design and label the X axis for the degree of difficulty and the Y axis for the level of payoff by selecting the Insert Tab, then Shapes, and then click on Block Arrow Up for the Y Axis and Block Right Arrow for the X axis. Then drag the arrow along the cells. You can use your cursor to adjust the size of your arrows.

After you have dragged the arrow to the last cell, the Drawing Tools Format Tab with appear automatically on the ribbon. Here you can also adjust the Height and Width and add other features such as Shape Fill and Outline Features. In the example PICK chart, the arrows are blue-filled with a black outline and a width of .62 inches. The X axis arrow has a length of 4.98 inches and the Y axis has a height of 3.3 inches.

To enter text, click inside the Arrow and begin typing using the Enter or Space bars to create spacing between words. The type in the example PICK chart uses the font Calibri 14 point, bold, centered. You now have your labeled PICK matrix.

## Selecting a Color Scheme for the PICK Matrix

The final step to create the PICK chart is to choose a color scheme. You can choose from either the preset multi-colored matrices or create your own using different colors and gradients by right clicking on each quadrant and selecting Format Shape and FIll Feature.

The example PICK matrix uses the preset green color gradient for the Implement quadrant and then adjusts the color for the rest of the quadrants.

To change the color, click on each of the three Gradient Stops in the bar and then select the desired color using 60%, 40%, and 25% shading respectively.

To reflect the conviction of the decision, click on the Direction in the Fill Feature, and for each quadrant select the choice that has the darkest shading in the corner that has the most payout and will be the easiest to execute.

You can also change the fill color of your title with a right click and then choosing a Fill Color.

The results should look similar to this design.

## Placing Projects within the PICK Chart

To place projects within the PICK chart, first create a text box for each project.

Select Insert Tab and then Text Boxes on the ribbon and insert a text box in a cell outside the PICK chart. Choose No Fill and No Line Color and set the font to Calibri 14 point, bold and then type the name of the project.

Move each text box to its risk/return location based upon your assessment until you have placed all of your competing projects.

From the example PICK chart, a project manager exercising his or her role as a planner can easily see the superiority of Project 1 and the inferiority of Project 4, but may need to further assess Project 2 and Project 3 which are comparable but differ slightly on the commitment of resources and the project’s rewards.

Now that you know the fundamentals how to create a PICK chart in Excel, you can begin prioritizing your projects. You can also download the example PICK chart from the Media Gallery here and adapt it to your own use.

Screenshots taken by Ginny Edwards.

## Introduction: How to Create a Graph in MS Excel 2016

These step-by-step instructions explain how to create a graph using Microsoft Excel. Any user with a set of data and access to the Excel program will be able to produce a graph by closely following these instructions. Each written instruction is accompanied by a picture to provide a visual aid. The set of data used in this example is number of M&Ms in a bag, but any quantitative data can be used to produce a graph by following these instructions.

## Step 1: Startup Excel 2016

While this tutorial will work for most modern versions of Excel, we will be using the most recent 2016 edition.

For OS X:

2. Open Excel
3. Create a blank workbook

For Windows:

1. Press start button
2. Type Excel
3. Open Excel
4. Create a blank worksheet

## Step 2: Record and Input Data

While you can use any simple quantitative data, for this example we will be counting the ratio of different colors of M&Ms.

For both OS X and Windows:

1. Enter the categories of data into column A, beginning with the label in box A1. In this example, the label “colors” goes in box A1, and the colors of M&Ms are in boxes A2 – A6.
2. Enter the corresponding data into column B, beginning with the label in box B1. In this example, the label “Number of M&Ms” goes in box B1, and the amounts of each corresponding color of M&Ms are in boxes B2 – B6.

## Step 3: Produce the Graph

For OS X:

1. Highlight the data, including the labels for each column by clicking and dragging while holding down the left mouse button.
2. Click on the “Charts” tab at the top of the program.
3. Choose the type of graph you wish to produce by clicking on one of the options under the heading “Insert Chart”. For this example, we have chosen a pie chart.
4. Choose the display style of your graph on the drop down menu that appears.

For Windows:

1. Highlight the data, including the labels for each column by click and dragging while holding down the left mouse button.
2. Click on the “Insert” tab at the top of the program.
3. Choose the type of graph you wish to produce by clicking on one of the options under “recommended charts”
4. Choose the display style for your graph on the menu that appears

## Step 4: Add a Title and Labels

For OS X:

1. Click anywhere within the boundaries of your chart to highlight it.
2. Click on the “Chart Layout” tab at the top, then choose “Chart Title”. Select a title style from the dropdown menu.
3. Edit your title by clicking once on the actual chart title to highlight the text box, then again to allow the cursor to appear.
4. Choose “Legend” (under “Chart Layout”). Select a legend style from the dropdown menu.
5. Choose “Data Labels” (under “Chart Layout”). Select a label style from the dropdown menu.

For Windows:

1. Simply double click on any of the pregenerated titles or labels to edit them.
2. To add new labels, click the ‘Add Chart Element’ tab at the top left corner

## Step 5: Edit Color Scheme

In this example, we will be matching the colors in the graph to the colors of M&Ms represented.

For OS X:

1. Click on the “Format” tab, next to “Chart Layout” at the top.
2. Click once on the pie graph to highlight the whole pie, then click on one “piece” of the pie to highlight that piece.
3. To change the color of that piece, click on the drop down arrow next to the word “Fill”, then choose a color.
4. Repeat steps 2 and 3 for each piece of the pie.

For Windows:

1. Click on the “Format” tab right of the “Design” tab.
2. Double click on the part of the graph you want to change.
3. Select “Shape Fill” in the top-middle-right and choose the desired color
4. Repeat steps 2 and 3 for each piece of the pie

## Step 6: Save the Graph

For OS X:

1. Right click (or two-finger click) anywhere within the boundaries of the graph.
2. Choose “Save as Picture” from the drop-down menu that appears.

For Windows:

1. Right click anywhere within the boundaries of the graph.
2. Choose “Save as template” from the drop-down menu that appears
3. Select the location you want to save the graph.

## Be the First to Share

Did you make this project? Share it with us!

Tornado charts are a special type of Bar Charts. They are used for comparing different types of data using horizontal side-by-side bar graphs. They are arranged in decreasing order with the longest graph placed on top. This makes it look like a 2-D tornado and hence the name.

### Creating a Tornado Chart in Excel:

Step 1: Open Excel and Prepare your Data table. Start writing your data in a table with appropriate headers for each column. Once this is done, make sure you select either of the columns and set all of its values to negative i.e., if the original value in Header Xyz is 2000, then change it to negative two thousand or “-2000” as shown below.

Step 2: Select the entire table using L-Shift and the arrow keys. Then, go to the Data section from the top bar and select “Sort.”

Now, set the “Sort by” value to the Header of your Negative column and set the “Order” as “Largest to Smallest” as shown below, and click OK.

Sorting the data

Step 3: Representing the above data in Bar Chart

Now, select the entire table. Go to “Insert” and select Bar. Under “Bar”, select the Stacked bar option and wait for your tornado chart to open up.

### Converting a Tornado Chart to a Butterfly Chart:

To convert the above Tornado Chart into a Butterfly chart, as shown above, add a third column with constant values and place it between the Negative and the Positive columns.

Notice the Gap Column

Now, repeat the process for the Tornado Chart and then select the Gap column and fill it with the color White. The Butterfly chart is ready.

Note: This article has been written based on Microsoft Excel 2010, but all steps are applicable for all later versions

This post and video answers a question on how to resize the plot area of a chart to prevent the axis titles and labels from overlapping. If you can’t read the titles or labels this tip will help cleanup your chart to make it more presentable.

## The Chart Area and Plot Area Are the Same Size

Sometimes the plot area can fill up the entire chart area. This makes it difficult to distinguish the difference between the two areas of the chart.

The plot area also resizes with the chart area. So if you select the outside border of the chart and resize it, the plot area will also resize proportionally.

In the case of Tony’s chart in the video, he was having trouble seeing the axis titles and labels because the plot area was too large. Therefore, the plot area needs to be smaller than the chart area to fit the axis labels, and titles outside the chart.

When creating a chart in Excel, Excel will default to inserting your new chart on the same worksheet that contains the data you created it from. This lesson shows you various options for moving or resizing your chart so it looks how you want it to, where you want it to be. It assumes that you’ve already created a chart, and that chart is sitting as an object on your worksheet.

Moving and Resizing a chart can be necessary if you need to fit more than one chart onto your worksheet, or if Excel inserted the chart in the wrong place. When you create a new chart, Excel will automatically insert the chart into the middle of your Excel screen. It will do this regardless of where you are in your worksheet, and what else might be there at the time. Yes, that means that Excel will insert your chart on top of existing data if there is data in the center of your Excel window at the time you insert your chart.

## Move your Excel chart to a different location within your worksheet

• Simply float your mouse over the outside edge of the chart, or inside the chart on an empty space.
• The cursor will change to look as it does in this picture:

## Resize your Excel chart within the current worksheet

• Select your chart by clicking on it.
• Float your mouse over one of the corners of the chart, or in the middle of one of the sides. The cursor will change to a resize handle like this:
• Click and drag the mouse to change the size of the chart.
• If you are dragging one of the corners, you can hold the SHIFT key to resize the chart without changing the proportions of the chart. This doesn’t work if you drag one of the sides.
• Note that if you float over the mid-point of one of the sides, the resize handle will be either horizontal or vertical rather than diagonal as shown here.

## Move your Excel chart to another worksheet

• One way to do this is to simply copy and paste the chart to another worksheet.
• Another option is to right-click on an empty area of the chart, or along one of the edges.
• A pop-menu will appear with a number of options for changing the current chart.
• Choose Move Chart. The following dialog box will appear:
• You can now choose where you want to move your chart.
• Excel will default to moving it as an object to another worksheet. In this case it is showing the current worksheet (Good chart data example) but you can click the dropdown to see a list of the worksheets in the current spreadsheet. Note that you cannot move it to a worksheet in another Excel workbook – this option simply isn’t available.

## Move your Excel chart to a dedicated chart sheet:

• You can place your chart on a dedicated chart sheet if you wish. This can be useful if you are printing your spreadsheet and want to include a full-page version of your chart within the printout.
• When you do this, Excel creates another tab at the bottom of your worksheet that sits alongside your other worksheet tabs.
• To move your chart to a dedicated chart tab, follow the instructions above for moving your chart to another worksheet, but choose the New Sheet option instead and, optionally, name your new chart sheet.

## Quick Tip – Create a new chart on a dedicated chart sheet

As a quick tip, you can create a new column chart in a chart sheet by selecting your data and pressing F11 on the keyboard. This is a great way to create a chart quickly. It’s also a good way to check whether your chart data has been set up correctly for charting.

• This automatically creates a column chart on a new chart sheet. You don’t have to do anything else.
• You can then change the chart type and other options to suit your requirements.

## Summary

Moving or resizing a chart in Excel is a necessary skill to learn if you do anything with charts in Excel. This lesson covered the main options available to you. If you have any questions about moving or resizing charts, feel free to ask them in the comments below.

## Account Information

Two ways to build dynamic charts in Excel

### Two ways to build dynamic charts in Excel

Users will appreciate a chart that updates right before their eyes. In Microsoft Excel 2007 and Excel 2010, it’s as easy as creating a table. In earlier versions, you’ll need the formula method.

Editor’s note: In the video, Brandon Vigliarolo uses Microsoft Office 365 and walks through the steps of building dynamic charts in Excel. The steps are very similar to the following tutorial by Susan Harkins.

If you want to advance beyond your ordinary spreadsheet skills, creating dynamic charts is a good place to begin that journey. The key is to define the chart’s source data as a dynamic range. By doing so, the chart will automatically reflect changes and additions to the source data. Fortunately, the process is easy to implement in Excel 2007 and 2010 if you’re willing to use the table feature. If not, there’s a more complex method. We’ll explore both.

## The table method

First, we’ll use the table feature, available in Excel 2007 and 2010–you’ll be amazed at how simple it is. The first step is to create the table. To do so, simply select the data range and do the following:

1. Click the Insert tab.
2. In the Tables group, click Table.
3. Excel will display the selected range, which you can change. If the table does not have headers, be sure to uncheck the My Table Has Headers option.
4. Click OK and Excel will format the data range as a table.

Any chart you build on the table will be dynamic. To illustrate, create a quick column chart as follows:

1. Select the table.
2. Click the Insert tab.
3. In the Charts group, choose the first 2-D column chart in the Chart dropdown.

Now, update the chart by adding values for March and watch the chart update automatically.

## The dynamic formula method

You won’t always want to turn your data range into a table. Furthermore, this feature isn’t available in pre-ribbon versions of Office. When either is the case, there’s a more complex formula method. It relies on dynamic ranges that update automatically, similar to the way the table does, but only with a little help from you.

Using our earlier sheet, you’ll need five dynamic ranges: one for each series and one for the labels. Instructions for creating the dynamic range for the labels in column A follow. Then, use these instructions to create a dynamic label for columns B through E. To create the dynamic range for column A, do the following:

1. Click the Formulas tab.
2. Click the Define Names option in the Defined Names group.
3. Enter a name for the dynamic range, MonthLabels.
4. Choose the current sheet. In this case, that’s DynamicChart1. You can use the worksheet, if you like. In general, it’s best to limit ranges to the sheet, unless you intend to utilize them at the workbook level.
5. Enter the following formula: =OFFSET(DynamicChart1!\$A\$2,0,0,COUNTA(DynamicChart1!\$A:\$A))
6. Click OK.

Now, repeat the above instructions, creating a dynamic range for each series using the following range names and formulas:

• SmithSeries: =OFFSET(DynamicChart1!\$B\$2,0,0,COUNTA(DynamicChart1!\$B:\$B)-1)
• JonesSeries: =OFFSET(DynamicChart1!\$C\$2,0,0,COUNTA(DynamicChart1!\$C:\$C)-1)
• MichaelsSeries: =OFFSET(DynamicChart1!\$D\$2,0,0,COUNTA(DynamicChart1!\$D:\$D)-1)
• HancockSeries: =OFFSET(DynamicChart1!\$E\$2,0,0,COUNTA(DynamicChart1!\$E:\$E)-1)

Notice that first range reference starts with row 2. That’s because there’s a row of headings in row 1. The second set of references refers to the entire column, enabling the formula to accommodate all values in the column, not just a specific range. The addition of the -1 component eliminates the heading cell from the count. The first formula (for the labels in column A) doesn’t have this component.

It’s important to remember that you must enter new data in a contiguous manner. If you skip rows or columns, this technique won’t work as expected.

You might be wondering why I added the Series label to each range name. Using the name, alone, will confuse Excel. The series headings in row 1 are also names. Because the chart defaults will use the label headings in each column for each series name, you can’t use those labels to name the dynamic ranges. Don’t use the same labels for both your spreadsheet headings and your dynamic range names.

Next, insert a column chart, as you did before. If you enter new data, the chart won’t yet reflect it. That’s because the chart, by default, references a specific data range, DynamicChart1:A1:E3. We need to change that reference to the dynamic ranges we just created, as follows:

1. In the chart, right-click any column.
2. From the resulting submenu, choose Select Data.
3. In the list on the left, select Smith and then click Edit. (Remember the naming conflict I mentioned? Excel uses the column heading (cell B1) to name the series.)
4. In the resulting dialog, enter a reference to Smith’s dynamic range in the Series Values control. In this case, that’s =DynamicChart1!SmithSeries.
5. Click OK.

Repeat the above process to update the remaining series to reflect their dynamic ranges: DynamicChart1!JonesSeries; DynamicChart1!MichaelsSeries; and DynamicChart1!HancockSeries.

Next, update the chart’s axis labels (column A), as follows:

1. In the Select Data Source dialog, click January (in the list to the right).
2. Then, click Edit.
3. In the resulting dialog, reference the axis label’s dynamic range, DynamicChart1!MonthLabels.
4. Click OK.

You don’t have to update February; Excel does that for you. Now, start entering data for March and watch the chart automatically update! Just remember, you must enter data contiguously; you can’t skip rows or columns.

This formula method is more complex than the table method. Be careful naming the dynamic ranges and updating the series references. It’s easy to enter typos. If the chart doesn’t update, check the range references.