How-To Geek

How to make a bar chart in microsoft excel.

A bar chart is one of the simplest types of charts or graphs you can use in Microsoft Excel. If you want to make one, here's what you'll need to do.

Quick Links

Inserting bar charts in microsoft excel, formatting bar charts in microsoft excel.

A bar chart (or a bar graph) is one of the easiest ways to present your data in Excel, where horizontal bars are used to compare data values. Here's how to make and format bar charts in Microsoft Excel.

While you can potentially turn any set of Excel data into a bar chart, It makes more sense to do this with data when straight comparisons are possible, such as comparing the sales data for a number of products. You can also create combo charts in Excel , where bar charts can be combined with other chart types to show two types of data together.

Related: How to Create a Combo Chart in Excel

We'll be using fictional sales data as our example data set to help you visualize how this data could be converted into a bar chart in Excel. For more complex comparisons, alternative chart types like histograms might be better options.

To insert a bar chart in Microsoft Excel, open your Excel workbook and select your data. You can do this manually using your mouse, or you can select a cell in your range and press Ctrl+A to select the data automatically.

Example sales data, selected in a Microsoft Excel worksheet

Once your data is selected, click Insert > Insert Column or Bar Chart.

Various column charts are available, but to insert a standard bar chart, click the "Clustered Chart" option. This chart is the first icon listed under the "2-D Column" section.

Press Insert > Insert Column or Bar Chart > Clustered Chart to insert a standard bar chart into Excel

Excel will automatically take the data from your data set to create the chart on the same worksheet, using your column labels to set axis and chart titles. You can move or resize the chart to another position on the same worksheet, or cut or copy the chart to another worksheet or workbook file.

For our example, the sales data has been converted into a bar chart showing a comparison of the number of sales for each electronic product.

A sample bar chart in Microsoft Excel, showing the number of sales for a number of electronic products, with the data range beside it

For this set of data, mice were bought the least with 9 sales, while headphones were bought the most with 55 sales. This comparison is visually obvious from the chart as presented.

By default, a bar chart in Excel is created using a set style, with a title for the chart extrapolated from one of the column labels (if available).

You can make many formatting changes to your chart, should you wish to. You can change the color and style of your chart, change the chart title, as well as add or edit axis labels on both sides.

It's also possible to add trendlines to your Excel chart , allowing you to see greater patterns (trends) in your data. This would be especially important for sales data, where a trendline could visualize decreasing or increasing number of sales over time.

Related: How to Work with Trendlines in Microsoft Excel Charts

Changing Chart Title Text

To change the title text for a bar chart, double-click the title text box above the chart itself. You'll then be able to edit or format the text as required.

Double-click the chart title text box in an Excel bar chart to change the title text

If you want to remove the chart title completely, select your chart and click the "Chart Elements" icon on the right, shown visually as a green, “+” symbol.

From here, click the checkbox next to the "Chart Title" option to deselect it.

Tap the green, plus icon ("Chart Elements") next to your Excel chart, then press the "Chart Title" checkbox to add or remove the chart title.

Your chart title will be removed once the checkbox has been removed.

Adding and Editing Axis Labels

To add axis labels to your bar chart, select your chart and click the green "Chart Elements" icon (the “+” icon).

From the "Chart Elements" menu, enable the "Axis Titles" checkbox.

Press the green "Chart Elements" icon next to your chart, then click to enable the "Axis Titles" checkbox to add or remove axis labels

Axis labels should appear for both the x axis (at the bottom) and the y axis (on the left). These will appear as text boxes.

To edit the labels, double-click the text boxes next to each axis. Edit the text in each text box accordingly, then select outside of the text box once you've finished making changes.

Axis labels shown on an example Excel bar chart

If you want to remove the labels, follow the same steps to remove the checkbox from the "Chart Elements" menu by pressing the green, "+” icon. Removing the checkbox next to the "Axis Titles" option will immediately remove the labels from view.

Changing Chart Style and Colors

Microsoft Excel offers a number of chart themes (named styles) that you can apply to your bar chart. To apply these, select your chart and then click the "Chart Styles" icon on the right that looks like a paint brush.

To change the chart styles for an Excel bar chart, select the chart, then press the

A list of style options will become visible in a drop-down menu under the "Style" section.

Select one of these styles to change the visual appearance of your chart, including changing the bar layout and background.

Under the "Style" section of the "Chart Styles" menu, select one of the visual chart style options to apply it to your chart

You can access the same chart styles by clicking the "Design" tab, under the "Chart Tools" section on the ribbon bar.

The same chart styles will be visible under the "Chart Styles" section---clicking any of the options shown will change your chart style in the same way as the method above.

Excel chart styles are also visible by clicking the "Design" tab on the ribbon bar, with styles visible under the "Chart Styles" section

You can also make changes to the colors used in your chart in the "Color" section of the Chart Styles menu.

Color options are grouped, so select one of the color palette groupings to apply those colors to your chart.

Click the "Color" tab under the "Chart Style" options menu to change the colors used in your Excel bar chart

You can test each color style by hovering over them with your mouse first. Your chart will change to show how the chart will look with those colors applied.

Further Bar Chart Formatting Options

You can make further formatting changes to your bar chart by right-clicking the chart and selecting the "Format Chart Area" option.

You can make further formatting changes to an Excel bar chart by right-clicking the chart and clicking the "Format Chart Area" option

This will bring up the "Format Chart Area" menu on the right. From here, you can change the fill, border, and other chart formatting options for your chart under the "Chart Options" section.

The "Chart Options" section of the "Format Chart Area" menu, allowing you to make further formatting changes to an Excel chart

You can also change how text is displayed on your chart under the "Text Options" section, allowing you to add colors, effects, and patterns to your title and axis labels, as well as change how your text is aligned on the chart.

The "Text Options" section of the "Format Chart Area" menu, allowing you to make further text formatting changes to an Excel chart

If you want to make further text formatting changes, you can do this using the standard text formatting options under the "Home" tab while you're editing a label.

The "Home" tab on the Excel ribbon bar, with various text formatting options available

You can also use the pop-up formatting menu that appears above the chart title or axis label text boxes as you edit them.

The pop-up text box formatting menu in Excel

Excel Charting Basics: How to Make a Chart and Graph

By Joe Weller | January 22, 2018 (updated May 3, 2022)

  • Share on Facebook
  • Share on Twitter
  • Share on LinkedIn

Link copied

Organizations of all sizes and across all industries use Excel to store data. While spreadsheets are crucial for data management, they are often cumbersome and don’t provide team members with an easy-to-read view into data trends and relationships. Excel can help to transform your spreadsheet data into charts and graphs to create an intuitive overview of your data and make smart business decisions.

In this article, we’ll give you a step-by-step guide to creating a chart or graph in Excel 2016. Additionally, we’ll provide a comparison of the available chart and graph presets and when to use them, and explain related Excel functionality that you can use to build on to these simple data visualizations.

What Are Graphs and Charts in Excel?

Charts and graphs in Microsoft Excel provide a method to visualize numeric data. While both graphs and charts display sets of data points in relation to one another, charts tend to be more complex, varied, and dynamic.

People often use charts and graphs in presentations to give management, client, or team members a quick snapshot into progress or results. You can create a chart or graph to represent nearly any kind of quantitative data — doing so will save you the time and frustration of poring through spreadsheets to find relationships and trends.

It’s easy to create charts and graphs in Excel, especially since you can also store your data directly in an Excel Workbook, rather than importing data from another program. Excel also has a variety of preset chart and graph types so you can select one that best represents the data relationship(s) you want to highlight.

Tired of static spreadsheets? We were, too.

Excel vs. Smartsheet comparison video

Although Microsoft Excel is familiar, you were never meant to manage work with it. See how Excel and Smartsheet compare across five factors: work management, collaboration, visibility, accessibility, and integrations.  

Watch the full comparison

When to Use Each Chart and Graph Type in Excel

Excel offers a large library of charts and graphs types to display your data. While multiple chart types might work for a given data set, you should select the chart that best fits the story that the data is telling.

In Excel 2016, there are five main categories of charts or graphs:

  • Column Charts: Some of the most commonly used charts, column charts, are best used to compare information or if you have multiple categories of one variable (for example, multiple products or genres). Excel offers seven different column chart types: clustered, stacked, 100% stacked, 3-D clustered, 3-D stacked, 3-D 100% stacked, and 3-D, pictured below. Pick the visualization that will best tell your data’s story.

Excel column charts

  • Bar Charts: The main difference between bar charts and  column charts are that the bars are horizontal instead of vertical. You can often use bar charts interchangeably with column charts, although some prefer column charts when working with negative values because it is easier to visualize negatives vertically, on a y-axis.

Excel bar charts

  • Pie Charts: Use pie charts to compare percentages of a whole (“whole” is the total of the values in your data). Each value is represented as a piece of the pie so you can identify the proportions. There are five pie chart types: pie, pie of pie (this breaks out one piece of the pie into another pie to show its sub-category proportions), bar of pie, 3-D pie, and doughnut.

Excel pie charts

  • Line Charts:  A line chart is most useful for showing trends over time, rather than static data points. The lines connect each data point so that you can see how the value(s) increased or decreased over a period of time. The seven line chart options are line, stacked line, 100% stacked line, line with markers, stacked line with markers, 100% stacked line with markers, and 3-D line.

Excel line charts

  • Scatter Charts: Similar to line graphs, because they are useful for showing change in variables over time, scatter charts are used specifically to show how one variable affects another. (This is called correlation.) Note that bubble charts, a popular chart type, is categorized under scatter. There are seven scatter chart options: scatter, scatter with smooth lines and markers, scatter with smooth lines, scatter with straight lines and markers, scatter with straight lines, bubble, and 3-D bubble.

Excel scatter charts

There are also four minor categories. These charts are more use case-specific:

  • Area: Like line charts, area charts show changes in values over time. However, because the area beneath each line is solid, area charts are useful to call attention to the differences in change among multiple variables. There are six area charts: area, stacked area, 100% stacked area, 3-D area, 3-D stacked area, and 3-D 100% stacked area.

Excel area charts

  • Stock: Traditionally used to display the high, low, and closing price of stock, this type of chart is used in financial analysis and by investors. However, you can use them for any scenario if you want to display the range of a value (or the range of its predicted value) and its exact value. Choose from high-low-close, open-high-low-close, volume-high-low-close, and volume-open-high-low-close stock chart options.

Excel stock charts

  • Surface: Use a surface chart to represent data across a 3-D landscape. This additional plane makes them ideal for large data sets, those with more than two variables, or those with categories within a single variable. However, surface charts can be difficult to read, so make sure your audience is familiar with them. You can choose from 3-D surface, wireframe 3-D surface, contour, and wireframe contour.

Excel surface charts

  • Radar: When you want to display data from multiple variables in relation to each other use a radar chart. All variables begin from the central point. The key with radar charts is that you are comparing all individual variables in relation to each other — they are often used for comparing strengths and weaknesses of different products or employees. There are three radar chart types: radar, radar with markers, and filled radar.

Excel radar charts

Another popular chart is a waterfall chart, which is essentially a series of column graphs that show positive and negative changes over time. There is no Excel preset for a waterfall chart, but you can download a template to help make the process easier. For a full walkthrough, read How to Create a Waterfall Chart in Excel .

Waterfall chart template for Excel

‌ Download Waterfall Chart Template in Excel

Top 5 Excel Chart and Graph Best Practices

Although Excel provides several layout and formatting presets to enhance the readability of your charts, you can maximize their effectiveness with other methods. Below are the top five best practices to make your charts and graphs as useful as possible:

Make It Clean: Cluttered graphs — those with excessive colors or texts — can be difficult to read and aren’t eye catching. Remove any unnecessary information so your audience can focus on the point you’re trying to get across.

Choose Appropriate Themes: Consider your audience, the topic, and the main point of your chart when selecting a theme. While it can be fun to experiment with different styles, choose the theme that best fits your purpose.  

Use Text Wisely: While charts and graphs are primarily visual tools, you will likely include some text (such as titles or axis labels). Be concise but use descriptive language, and be intentional about the orientation of any text (for example, it’s irritating to turn your head to read text written sideways on the x-axis).

Place Elements Intelligently: Pay attention to where you place titles, legends, symbols, and any other graphical elements. They should enhance your chart, not detract from it.

Sort Data Prior to Creating the Chart: People often forget to sort data or remove duplicates before creating the chart, which makes the visual unintuitive and can result in errors.

How to Chart Data in Excel

To generate a chart or graph in Excel, you must first provide the program with the data you want to display. Follow the steps below to learn how to chart data in Excel 2016.

Step 1: Enter Data into a Worksheet

  • Open Excel and select New Workbook .
  • Enter the data you want to use to create a graph or chart. In this example, we’re comparing the profit of five different products from 2013 to 2017. Be sure to include labels for your columns and rows. Doing so enables you to translate the data into a chart or graph with clear axis labels. You can download this sample data below.

How to chart data in Excel

‌ Download Column Chart Practice Data

Step 2: Select Range to Create Chart or Graph from Workbook Data

  • Highlight the cells that contain the data you want to use in your graph by clicking and dragging your mouse across the cells.
  • Your cell range will now be highlighted in gray and you can select a chart type.

Creating a graph in Excel

In the following section, we’ll walk you through the specifics of creating a clustered column chart in Excel 2016.

How to Make a Chart in Excel

After you input your data and select the cell range, you’re ready to choose the chart type. In this example, we’ll create a clustered column chart from the data we used in the previous section.

Step 1: Select Chart Type

Once your data is highlighted in the Workbook, click the Insert tab on the top banner. About halfway across the toolbar is a section with several chart options. Excel provides Recommended Charts based on popularity, but you can click any of the dropdown menus to select a different template.

How to select chart type in Excel

Step 2: Create Your Chart

  • From the Insert tab, click the column chart icon and select Clustered Column .

How to select Excel chart

  • Excel will automatically create a clustered chart column from your selected data. The chart will appear in the center of your workbook.
  • To name your chart , double click the Chart Title text in the chart and type a title. We’ll call this chart “Product Profit 2013 - 2017.”

How to name a chart in Excel charts

We’ll use this chart for the rest of the walkthrough. You can download this same chart to follow along.

Column chart in Excel

‌ Download Sample Column Chart Template

There are two tabs on the toolbar that you will use to make adjustments to your chart: Chart Design and Format . Excel automatically applies design, layout, and format presets to charts and graphs, but you can add customization by exploring the tabs. Next, we’ll walk you through all the available adjustments in Chart Design .

How to make adjustments to your chart in Excel

Step 3: Add Chart Elements

Adding chart elements to your chart or graph will enhance it by clarifying data or providing additional context. You can select a chart element by clicking on the Add Chart Element dropdown menu in the top left-hand corner (beneath the Home tab).

How to add chart elements in Excel

To Display or Hide Axes:

How to display or hide axes in Excel charts

To Add Axis Titles:

Add chart elements in Excel

To Remove or Move Chart Title:

How to remove or move chart title in Excel

  • Click None to remove chart title.
  • Click Above Chart to place the title above the chart. If you create a chart title, Excel will automatically place it above the chart.
  • Click Centered Overlay to place the title within the gridlines of the chart. Be careful with this option: you don’t want the title to cover any of your data or clutter your graph (as in the example below).

Chart title in Excel

To Add Data Labels:

How to add data labels in Excel charts

To Add a Data Table: 

How to add a data table in Excel

  • None is the default setting, where the data table is not duplicated within the chart.

Excel chart with legend key

Note: If you choose to include a data table, you’ll probably want to make your chart larger to accommodate the table. Simply click the corner of your chart and use drag-and-drop to resize your chart.

To Add Error Bars:

How to add error bars in Excel

 To Add Gridlines:

How to add gridlines in Excel

To Add a Legend:  

How to add a legend in Excel

To Add Lines: Lines are not available for clustered column charts. However, in other chart types where you only compare two variables, you can add lines (e.g. target, average, reference, etc.) to your chart by checking the appropriate option.

To Add a Trendline:

Add a trendline in Excel

Note: You can create separate trendlines for as many variables in your chart as you like. For example, here is our chart with trendlines for Product A and Product C. 

How to make multiple trendlines on Excel chart

To Add Up/Down Bars: Up/Down Bars are not available for a column chart, but you can use them in a line chart to show increases and decreases among data points.

Step 4: Adjust Quick Layout

How to change layout of elements in Excel chart

Step 5: Change Colors

The next dropdown menu in the toolbar is Change Colors . Click the icon and choose the color palette that fits your needs (these needs could be aesthetic, or to match your brand’s colors and style).

Color palette for Excel charts

Step 6: Change Style

For cluster column charts, there are 14 chart styles available. Excel will default to Style 1, but you can select any of the other styles to change the chart appearance. Use the arrow on the right of the image bar to view other options.

Chart styles in Excel

Step 7: Switch Row/Column

Flip axis on excel chart

In this example, switching the row and column swaps the product and year (profit remains on the y-axis). The chart is now clustered by product (not year), and the color-coded legend refers to the year (not product). To avoid confusion here, click on the legend and change the titles from Series to Years .

How to flip axis on Excel chart

Step 8: Select Data

How to change range of data on Excel chart

Step 9: Change Chart Type

How to change chart type in Excel

You can also save your chart as a template by clicking Save as Template …

Create a chart template in Excel

Step 10: Move Chart

Moving an Excel chart

Step 11: Change Formatting

Change chart format in Excel

Step 12: Delete a Chart 

To delete a chart, simply click on it and click the Delete key on your keyboard.

How to Make a Graph in Excel

Because graphs and charts serve similar functions, Excel groups all graphs under the “chart” category. To create a graph in Excel, follow the steps below.

Select Range to Create a Graph from Workbook Data

Types of charts and graphs in Excel

Now you have a graph. To customize your graph, you can follow the same steps explained in the previous section. All functionality for creating a chart remains the same when creating a graph.

How to Create a Table in Excel

If you don’t need to visualize your data, you can create a table in Excel instead. There are two ways to format a data set as a table: manually, or with the Format as a Table button.

  • Manually: In this example, we manually added data and formatted as a table by including column and row names (products and years).
  • Use Excel’s Format as Table Preset: You can also input raw data (numbers without any column and row names).

How to format data as a table in Excel

Related Excel Functionality

Excel is one of the most widely-used tools across all industries and types of organizations. Charts and graphs are great tools to visualize your work, but there are many ways to elevate your data in Excel.

We’ve created a list of additional features that allow you to do more with your data:

  • Pivot Tables: A pivot table allows you to extract certain columns or rows from a data set and reorganize or summarize that subset in a report. This is useful tool if you only want to view a particular segment of a large data set, or if you want to view data from a new perspective.
  • Conditional Formatting : A powerful feature that allows you to apply specific formatting to certain cells in your spreadsheet. You can use conditional formatting to highlight key pieces of information, track changes, see deadlines, and perform many other data organization functions.
  • Dashboards: A powerful, visual reporting feature that pulls data from one or several datasets to display key performance indicators (KPIs), project or task status, and several other metrics. This gives the audience (team members, executives, clients, etc.) a snapshot view into project progress without surfacing private information.
  • Collaborative Charts: To avoid version control issues and allow multiple team members to edit a chart simultaneously, you’ll want to use a collaborative chart tool. The desktop versions of Excel do not support this, but you can use Excel for Office 365, Microsoft’s cloud-based web application, or several other online chart tools.
  • Data Series: A data series is any row or column stored in your workbook that you’ve plotted into a chart or graph. Once you’ve created your chart, you can add additional data series to it: Simply highlight the additional data you want to add and the chart will automatically update.

Make Better Decisions, Faster with Charts in Smartsheet

Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change. 

The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed. 

When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time.  Try Smartsheet for free, today.

Discover why over 90% of Fortune 100 companies trust Smartsheet to get work done.

  • Gotta Get It: Hot Tech Deals at Target
  • Hands On: Samsung Galaxy S24 Ultra

How to Create a Chart in Excel Using Shortcut Keys

Quickly add, modify, or delete a chart

What to Know

  • Create a chart on a new worksheet: Select the data you want to use in the chart, then press the  F11  key on the keyboard.
  • Add a chart to the current worksheet: Select the data, press and hold the Alt key (or the Option key on a Mac), then press the F1 key.
  • Change the chart style: Select the chart, go to the Chart Tools Design tab, select Change Chart Type , then select a chart type.

This article explains how to use keyboard shortcuts to make charts in Excel. Instructions apply to Excel 2019, 2016, 2013, 2010, 2007, Excel for Mac, and Excel for Microsoft 365.

Add a Quick Chart to Your Worksheet or Workbook

The quick chart keyboard shortcut adds a commonly used chart to the current  worksheet  or to a separate worksheet in the current workbook. Here's how to add a new chart to a new worksheet in an existing workbook:

Select the data you want to use in the chart.

Press the  F11  key on your keyboard.

A chart is created and added to a separate worksheet in the current workbook. If the default settings haven't been changed, a  column chart is created by pressing F11.

Mac users may need to hold down the Fn key while pressing any function keys, such as F1. Additionally, some functions, such as F11, may not work unless macOS hotkeys, such as Expose, are first disabled .

As well as adding a copy of the chart to a separate worksheet, that same chart can be added to the current worksheet by using different keyboard shortcut keys.

Here's how to add a chart to the same worksheet:

Press and hold the Alt key. If you are on a Mac, substitute the Option key.

Press the F1 key.

A new chart is created and added to your current worksheet, alongside your data.

Change the Chart Type

If pressing F11 or Alt+F1 produces a chart that isn't to your liking, change the chart with the help of built-in templates.

Here's how to change the chart type:

Select the chart.

Select the Chart Tools Design tab.

Select Change Chart Type .

Select the type of chart you'd like to use.

Select the style of the chart.

Select OK when you're done.

Create and Save Chart Templates

If you want use a predefined chart style, create a chart that uses the chart style, colors, scale settings, and font type you want. Then, save the chart as a template.

Follow these steps to save your new chart template:

Right-click on the chart to open a context menu.

Choose Save as Template to open the ​ Save Chart Template  dialog box.

Enter a name for the template in the File name text box.

Select   Save   to save the template and close the dialog box.

The chart template file is saved as a .crtx file in the C:\Documents and Settings\< username >\AppData\Roaming\Microsoft\Templates\Charts folder.

Delete a Chart Template

If you don't need a chart template you created, delete it to remove it from your list of templates in Excel.

To delete a chart template:

Select Change Chart Type to open the Change Chart Type dialog box.

Select Templates to see a list of your chart templates.

Select Mange Templates to open the file manager showing the saved chart templates.

Right-click on the chart template you want to delete and select Delete .

Get the Latest Tech News Delivered Every Day

  • The 12 Best Tips for Using Excel for Android in 2024
  • How to Make a High-Low-Close Stock Market Chart in Excel
  • How to Create a Column Chart in Excel
  • How to Use Excel Shortcuts to Add Worksheets
  • How to Create and Format a Pie Chart in Excel
  • How to Create an 8 Column Chart in Excel
  • How to Create Exploding Pie Charts in Excel
  • The 23 Best Excel Shortcuts
  • How to Make and Format a Column Chart in Excel
  • Create a Pie Chart Graphic in PowerPoint
  • Combine Chart Types in Excel to Display Related Data
  • Use Shortcut Keys to Add the Current Date/Time in Excel
  • How to Make a Box and Whisker Plot in Excel
  • What Is the Name Box in Excel?
  • How to Create a Dashboard in Excel
  • How to Use Charts and Graphs in Excel
  • Ablebits blog
  • Excel charts

Make a chart in Excel from two or more different sheets

Svetlana Cheusheva

A while ago we published the first part of our Excel charts tutorial that provides the detailed guidance for beginners. And the very first question posted in comments was this: "And how do I created a chart from multiple tabs?" Thanks for this great question, Spencer!

Indeed, when creating charts in Excel, the source data does not always reside on the same sheet. Fortunately, Microsoft Excel provides a way to plot data from two or more different worksheets in a single graph. The detailed steps follow below.

How to create a chart from multiple sheets in Excel

Supposing you have a few worksheets with revenue data for different years and you want to make a chart based on those data to visualize the general trend.

1. Create a chart based on your first sheet

Creating a chart based on the first sheet

2. Add a second data series from another sheet

Click on the chart you've just created to activate the Chart Tools tabs on the Excel ribbon, go to the Design tab ( Chart Design in Excel 365), and click the Select Data button.

Chart Filters button

Now we are going to add the second data series based on the data located on a different worksheet. This is the key point, so please be sure to follow the instructions closely.

Click the Collapse Dialog button next to the Series values field.

The Edit Series dialog will shrink to a narrow range selection window. Click on the tab of the sheet that contains the other data you want to include in your Excel chart (the Edit Series window will remain on-screen as you navigate between sheets).

Select data on the second sheet you want to add to your Excel graph.

And now, click the Collapse Dialog button to the right of the Series name field and select a cell containing the text you want to use for the series name. Click the Expand Dialog to return to the initial Edit Series window.

Add a name for the second date series.

As you see in the screenshot above, we've linked the series name to cell B1, which is a column name. Instead of the column name, you can type your own series name in double quotes, e.g. =" Second data series ".

The series names will appear in chart legend of your chart, so you might want to invest a couple of minutes in giving some meaningful and descriptive names for your data series.

The second data series from another sheet is added to the chart.

3. Add more data series (optional)

If you want to plot data from multiple worksheets in your graph, repeat the process described in step 2 for each data series you want to add. When done, click the OK button on the Select Data Source dialog window.

An Excel chart based on data in three different sheets

4. Customize and improve the chart (optional)

When creating charts in Excel 2013 and 2016, usually the chart elements such as chart title and legend are added by Excel automatically. For our chart plotted from several worksheets, the title and legend were not added by default, but we can quickly remedy this.

Adding the chart title and legend

Making a chart from the summary table

The solution demonstrated above works only if your entries appear in the same order in all the worksheets you want to plot in the chart. Otherwise, your graph is going not be messed up.

In this example, the order of entries ( Oranges , Apples , Lemons, Grapes ) is identical in all 3 sheets. If you are making a chart from large worksheets and you are not sure about the order of all items, it makes sense to create a summary table first, and then make a chart from that table. To pull the matching data to a summary table, you can use the VLOOKUP function or the Merge Tables Wizard .

For instance, if the worksheets discussed in this example had a different order of items, we could make a summary table using the following formula:

=VLOOKUP(A3,'2014'!$A$2:$B$5, 2,FALSE)

Creating a chart from the summary table

Modify an Excel chart built from multiple sheets

After making a chart based on the data from two or more sheets, you might realize that you want it to be plotted differently. And because creating such charts is not an instant process like making a graph from one sheet in Excel , you may want to edit the existing chart rather than create a new one from scratch.

In general, the customization options for Excel charts based on multiple sheets are the same as for usual Excel graphs. You can use the Charts Tools tabs on the ribbon, or right-click menu, or chart customization buttons in the top right corner of your graph to change the basic chart elements such as chart title, axis titles, chart legend, chart styles, and more. The detailed step-by-step instructions are provided in Customizing Excel charts .

And if you want to change the data series plotted in the chart, there are three ways to do this:

  • Select Data Source dialog
  • Chart Filters button
  • Data series formulas

Edit data series using Select Data Source dialog

Open the Select Data Source dialog window ( Design tab > Select Data ).

To change a data series , click on it, then click the Edit button and modify the Series Name or Series Values like we did when adding a data series to the chart .

Editing the data series using Select Data Source dialog

To hide a data series , simply uncheck it in the Legend Entries (Series) list in the left-hand side of the Select Data Source dialog.

Hide or show series using the Charts Filter button

To hide certain data , click on the Chart Filters button, and uncheck the corresponding data series or categories.

Editing a data series in the chart

Edit a data series using a formula

As you probably know, each data series in an Excel chart is defined by the formula. For example, if you select one of the series in the graph we created a moment ago, the series formula will look as follows:

The data series formula

Each data series formula can be broken up into four basic elements:

=SERIES([Series Name], [X Values], [Y Values], [Plot Order])

So, our formula can be interpreted in the following way:

  • Series name ('2013'!$B$1) is taken from cell B1 on sheet "2013".
  • Horizontal axis values ('2013'!$A$2:$A$5) are taken from cells A2:A5 on sheet "2013".
  • Vertical axis values ('2013'!$B$2:$B$5) are taken from cells B2:B5 on sheet "2013".
  • Plot Order (1) indicates that this data series comes first in the chart.

That's all for today. I thank you for your time and hope to see you on our blog next week!

You may also be interested in

  • How to create a pie chart in Excel
  • How to create a bar graph in Excel
  • How to add titles to Excel charts
  • How to rotate charts in Excel
  • How to create waterfall chart in Excel (bridge chart)
  • How to make a Gantt chart in Excel
  • How to make a heat map in Excel

Table of contents

Ablebits.com website logo

25 comments

worksheet or chart

Can you make a tutorial on how to make a line graph taking data from multiple tabs into one line graph? For example I have a spreadsheet with tabs of sales for apples, bananas, and coconut by month. I would like to graph total sales of apples for the year. Can I pull the totals from each tab to create one graph?

This may be confusing. I hope it made sense.

worksheet or chart

I have a similar question as regarding graph's. I Keep a daily log of my investment portfolios for 5 trading markets and wish to see the history plotted on a single chart to display the performanc.

worksheet or chart

How do you have the same chart in different tabs so that I only have to update 1 chart.

worksheet or chart

how to move chart

worksheet or chart

hi I have an excel with over 500 sheets. each sheet has a variable number of rows with historical descriptions of art objects (I know...). I was wondering if it would be possible to make a chart gathering the information from ALL the sheets and ALL the rows. I can prepare a column identifying each row by a specific definition or number, like, silver or 1, furniture or 2, ceramic or 3 etc. would it be possible for excel to make a chart with this...? I hope I'm explaining this well enough because I really need help because I have around 7.000 different pieces and it would be awful to count them one by one...! thanks!

worksheet or chart

Helpful tip to add: before the design menu will appear for use in step 1 (at least in some versions of excel), it's necessary to create a table (by selecting cells and insert tab > table).

worksheet or chart

The Design tab in Excel is context-sensitive. For a table, it shows table-specific features, and for a chart - chart-specific. To activate the tab, you just need to select a corresponding object, chart in our case.

To avoid confusion, in the current version of Excel 365, the tab is named "Chart Design".

worksheet or chart

Thanks for the information. It was useful.

worksheet or chart

Hi, I have four Excel tabs that I am working on and they are all full with numbers. I have (Tab: L1, L2, M1 & M2) I have to plot L1&L2 together in one sheet and M1 with M2 as well. Everytime I plot each tab separately all the graphs look good. I tried to plot L1 with L2 but it doesnt seem to be giving me the graph that I am expecting.

Can someone please help me with this matter step by step please? I am not sure what am I doing wrong.

Thanks a lot for this. Utterly valuable.

worksheet or chart

I wanna create progressive R&E after each month, each month's R&E will be in diff worksheet, i want amount to change in progressive R&e as i in put amount in monthly R&E. please guide me

worksheet or chart

bopsman: You can create a running total by entering a range formula which is part absolute reference and part relative reference. The range formula looks like this: $A$1:A1. Absolute with the "$", relative without. Where A1 is the first cell in the range. So if the cell on the far right of your Table that will hold the formula is in column "D" and the calculated cell is in column "E" the formula will look like this: =SUM($D$6:D6) formula in E6 =SUM($D$6:D7) formula in E7 =SUM($D$6:D8) formula in E8 after you've copied it down to row 6, 7 & 8. This will calculate the running total to row 8. OK, first I would create a monthly sheet for each of the the month's data they will contain. Like January 2018, February 2018, etc. Then I would create another sheet and name it Progressive Total. This sheet will be updated by a link from each of the monthly sheet's Total cells. So, in each of these sheets I would create a Table. Tables have several advantages over a simple data table. Svetlana has a very good article here in AbleBits that explains tables. The link can be found by entering " How to make and use a table in Excel" in the search box. When your table is ready enter the range as I have it above; absolute and relative references. Enter it in an empty cell on the far right cell in your table where you want to display the running total. Copy it down the column and each cell will display the running total. In the last cell of your table should display the Total. Again this can be done very simply with the Table. This is the cell you will link to the Progressive Total sheet. On the Progressive Total sheet create a table where each row corresponds to a total from a month. In the cell for each month you want to link the total from each month's total. Do this by entering "=" in the cell and then open up the appropriate monthly sheet and click the Total cell on that sheet. Then open the Progressive Total sheet and click Esc and Save. The link in the cell should show something like "January 2018!E35". Where the first part is the name of your monthly sheet and the second part is the address for the Total cell. You'll want to click "Esc" after this so that Excel will know you're finished with the linking procedure. Then, enter the range formula in the first cell that will hold the running total and copy it down the column. Because this Table is just like the others you can create a running total at the bottom which will be updated each time you update a monthly cell. So, create monthly sheets which contain Tables at the bottom of which are Total cells. Then, create a Progressive Total sheet on which is a Table that contains cells that are linked to the Total cells in your monthly sheets. At the bottom of this Table is a cell which displays the calculated running total.

I have a similar request. I can email a sample of my data. Have not been able to get this to work yet!

worksheet or chart

How can I select data in different rows, the rows are not below each other, there are other data in between, to show on the same line in the graph

worksheet or chart

Is it possible for some data only will be select for column and row will take a chart in excel

worksheet or chart

Is it possible to create a chart from multiple sheets that will automatically update when a new sheet of data is added?

worksheet or chart

I am trying to great a comparisons graph. However, I have slightly different axis labels for each comparison. How do I go about putting this into one comparison graph?

worksheet or chart

I am following the instructions but the add button is grayed out when trying to select another data source. Is there something I need to update to be able to add?

worksheet or chart

chart made in exel how to get details to click one particular name?

worksheet or chart

Is it possible to have in the command

the x/y-value ranges combined from different sheets, i.e. without collecting them in one summery-sheet?

I tried this but it did not work: =SERIES('2013a'!$B$1,'2013a'!$A$2:$A$5+'2013b'!$A$2:$A$5,'2013a'!$B$2:$B$5+'2013b'!$B$2:$B$5,1) It also did not work when replacing the "+" with "&" and ";". Is it just not possible or did I miss something?

worksheet or chart

if i have to select data from a database in other sheet what to do

power man 10 20 30 40 10 100 200 300 400

20 440 540 660 789

30 500 600 700 800

now in another sheet i have input power 20 and man 30 then corresponding 660 should come in that sheet for that what to do please suggest me.......

my mail id is [email protected] 40

worksheet or chart

What about this: Up top where you have 2014, 2015 and 2016 charts, what if you have 10 charts like that, but they're all spread across horizontally in one worksheet? Would there be a way to automate getting the information from each year into a summary?

worksheet or chart

Sir, I want to make a students performance evaluation sheet along with their performance graph on each subject. I have taken a filed as name rollno month subject skill grading remarks abc 11 april English reading 3 ddd 14 april English reading 2 abc 11 april pubjabi writing 3 abc 11 may English reading 2 ddd 14 may English reading 2

this way I have n number of data stored in sheet. I want to extract the performance evaluation bar graph on individual name or roll no wise and also on individual subject wise. how I will do it just by selecting name/roll no and subject and it will give the output in bar graph

worksheet or chart

This is the example I googled and used to do the charts. See what you can do with this information.

Post a comment

MrExcel Message Board

  • Search forums
  • Board Rules

Follow along with the video below to see how to install our site as a web app on your home screen.

Note: This feature may not be available in some browsers.

  • If you would like to post, please check out the MrExcel Message Board FAQ and register here . If you forgot your password, you can reset your password .
  • Question Forums
  • Excel Questions

Determine if worksheet is a chart

  • Thread starter CT Witter
  • Start date Jun 4, 2003

CT Witter

MrExcel MVP

  • Jun 4, 2003

Looking to determine if the workbook is a chart or is a worksheet through code. Thanks, CT  

Excel Facts

Jon peltier.

CT - Look up xlSheetType in the Classes pane of the Object Explorer in the VB Editor. Select Case ActiveSheet.Type Case -4167 MsgBox "I'm a Worksheet" Case -4169 MsgBox "I'm a Chart" Case Else MsgBox "I'm Confused!" End Select - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______  

Worked Great But my Charts are Identified as type 4 xlExcel4IntlMacroSheet. I'm not sure why? Is it because they are linked? CT  

The funny thing is that the help files say a chart is -4109. But I activated a chart sheet, and did this in the VB Editor's Immediate window: ?ActiveSheet.Type -4169 and this: ?ActiveSheet.Type = xlChart False To be safe, you could do something like this Code: If Not ActiveChart Is Nothing If ActiveChart.Name = ActiveSheet.Name then MsgBox "I'm a Chart Sheet After All" End If End If - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______  

tusharm

Don't know why Jon's mucking around with numbers. Use Typeof or Typename if typeof Sheets(1) is chart then... ... msgbox typename(activesheet) If you must use Type, use the predefined constants of XlSheetType: xlChart xlDialogSheet xlExcel4IntlMacroSheet xlExcel4MacroSheet xlWorksheet if activesheet.type=xlchart then...  

NateO

NateO said: Along the lines of Tushar's advice, I was going to throw-up the following: Click to expand...
tusharm said: Don't know why Jon's mucking around with numbers. Click to expand...
tusharm said: Use Typeof or Typename Click to expand...
tusharm said: If you must use Type, use the predefined constants of XlSheetType: xlChart xlDialogSheet xlExcel4IntlMacroSheet xlExcel4MacroSheet xlWorksheet if activesheet.type=xlchart then... Click to expand...
Jon Peltier said: These are unreliable (I read that help page, too). From the debug window: Code: ?typename(activesheet) Chart ?typeof activesheet is Chart True ?activesheet.type=xlchart False Click to expand...

Tushar - In 2000 and XP, you made clustered column charts, right? And I had made a scatter with lines in 97 and 2000. So ActiveSheet.Type and ActiveChart.Type returned 3 for you and -4169 for me. A stacked area chart returned 1 and a line with markers returned 4. So this isn't the SHEET type, and it isn't the CHART type. There's a hidden property Type in the Chart class in the object browser, and also a SubType (both defined as Longs). I bet these are some Excel 95 or earlier constants that won't go away. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______  

Similar threads

  • Sep 25, 2023

sijpie

  • Sep 13, 2023
  • Dec 29, 2023
  • Dec 4, 2023

Forum statistics

Share this page.

worksheet or chart

We've detected that you are using an adblocker.

Which adblocker are you using.

AdBlock

Disable AdBlock

worksheet or chart

Disable AdBlock Plus

worksheet or chart

Disable uBlock Origin

worksheet or chart

Disable uBlock

worksheet or chart

How to Move Chart to New Sheet in Excel? 2 Easy Ways!

By default, when you insert a chart in Excel, it’s created in the same worksheet where you have the source dataset.

But in many cases, you may want to move the chart from the sheet where it’s created to another worksheet (or a chart sheet – which has nothing but the chart).

This can be useful when you’re creating dashboards , where the data can be scattered throughout the workbook, but you can get all the important charts in the sheet that has the dashboard.

When it comes to moving a chart to another sheet, you can:

  • Move it as a chart object from one sheet to another
  • Move the chart to a chart sheet, where you have only the chart

In this tutorial, I will show a step-by-step process to move the chart to a new sheet in Excel.

So let’s get started!

This Tutorial Covers:

Chart Object vs Chart Sheet

Before I get into the steps of moving a chart to a new sheet in excel, let me quickly explain the difference between a chart object and a chart sheet .

When you create a chart in a worksheet that already has the data, what you get is a chart object (which is a chart that sits above your worksheet and you can move it around like an object).

On the contrary, a chart sheet is a completely different sheet that is dedicated to a single chart. So when you move an existing excel chart into a new chart sheet, you would only have the chart in that sheet (and there won’t be any cells or tables like a regular worksheet).

In this tutorial, I will show you how to move an existing chart into a new worksheet (as an object) or to a new chart sheet.

Moving Chart to a New Worksheet (or Another Existing Sheet)

Suppose you have the data set as shown below and you have created a chart using it.

Now, I want to move this chart to a different worksheet in the same workbook (this worksheet – to which I want to move the chart – should already exist in the workbook).

Below are the steps to do this:

  • Click on the chart object that you want to move
  • Click on the Chart Design tab (this is a contextual tab that only appears when you select any chart)
  • In the Location group, click on the ‘Move Chart’ icon
  • In the Move Chart dialog box, make sure ‘Object in’ option is selected.
  • From the drop-down, select the sheet where you want to move the selected chart. In this example, I am moving the chart to a sheet named ‘Summary’

The above steps would move the selected chart from the existing worksheet to the worksheet you selected in Step 5.

In case you want to move this chart to a completely new blank worksheet, you will first have to add that new worksheet and then repeat the process (so that the name of this new worksheet is shown in Step 5)

Note that the above steps would remove the chart from the source worksheet and move it to the destination worksheet.

In case you want to keep the chart in the source worksheet and get a copy of it in the new worksheet, you need to first create a copy of the chart (using a simple Control C and Control V), and then move one of these charts to the destination worksheet.

Move Chart by Copy Pasting

Another really quick way to move a chart to a new worksheet is by simply copying the chart and pasting it in the new worksheet.

Doing this would create a copy of the chart in the new worksheet. So if you want to completely move the chart and not have it where you have the data, you can simply keep the copy and delete the original chart (or use Cut-Paste instead of Copy-Paste).

Below are the steps to move a chart using simple copy-paste:

  • Create a new sheet where you want to move the chart (if you don’t have that already)
  • Activate the sheet where you have the chart that you want to move
  • Right-click on the chart and select Copy (or select the chart and use Control + C)
  • Go to the Sheet where you want to get a copy of the chart
  • Right-click and in the Paste Special option, click on the ‘Use Destination Theme’ icon (or use the keyboard shortcut Control + V to paste)

The above steps would create a copy of the chart in the current worksheet.

Moving a Chart to a New Chart Sheet

If you want to move a specific chart into its own chart sheet (which is meant to contain nothing else but a chart), you can use the below steps:

  • Click on the chart object that you want to move to a new chart sheet
  • Click on the Chart Design tab
  • In the Move Chart dialog box, select ‘New Sheet’
  • Give a name to the sheet where this chart would be moved (or keep the default Chart1 name)

The above steps would remove the chart from the current worksheet, create a new chart sheet, and move the chart to this new chart sheet.

All the customizations that you can do with a chart object can also be done with a chart that’s in the chart sheet (except resizing and moving around the chart).

Note that you can also move other charts into this newly inserted Chart Sheet, but the one that you inserted first would remain in the background and all the other charts would be treated as an object that floats over that chart.

Moving All the Charts in the Workbook to a New Sheet

While the manual way of moving a chart to another worksheet or chart sheet is quite easy, it could become quite tedious in case you have a lot of charts that you want to move.

Imagine 20 sheets with one chart in each sheet that you want to move to the summary or dashboard sheet.

In such a scenario, you can use a simple VBA code that would go through all the charts in your workbook and move these two into the specified worksheet.

Below is the VBA code that would move all the charts from all the worksheets to one destination worksheet (in this example, it’s the sheet with the ‘Dashboard’)

In the above code, I have hard-coded the name of the destination sheet where the charts would be moved (the destination sheet name used is ‘Dashboard’).

In case you want to move these charts to any other worksheet, just replace the word Dashboard with the name of that worksheet.

Below are the steps to run this VBA macro code in Excel:

  • Click the Developer tab in the ribbon (if you can’t see the Developer tab, read this guide to enable it )
  • Click on Visual Basic icon. This will open the VB Editor
  • In the Project Explorer, select any object of the workbook that has the charts (if you don’t see Project Explorer, click on ‘View’ option in the menu and then click on Project Explorer)
  • Click the ‘Insert’ option in the menu and then click on ‘Module’. This will insert a new module for the workbook
  • Copy and paste the above code in the Module code window (you can open the module code window anytime by double-clicking on the module name in the Project Explorer)
  • Select any line in the code
  • Press the F5 key (or click the green play button in the toolbar)

The above steps would move all the charts from all the sheets in the workbook into the sheet named ‘Dashboard’.

How do the Code works – A Simple Explanation

The above code uses a For Next loop to go through all the worksheets in the workbook. It then uses an if condition to check whether the name of the worksheet is ‘Dashboard’ or not.

If the name of the worksheet is ‘Dashboard’, nothing happens, and in case it’s not, then the code goes through each chart in that worksheet (this is again done using a For Next loop).

Each chart in the sheet is then moved to the dashboard worksheet (and also removed from the sheet where it originally existed).

Pro Tip : I strongly recommend you create a copy of the original file before you run this code (as the changes done by the VBA code can not be undone)

So these are some simple ways that you can use to move a chart to any specific sheet in the workbook (or to a new chart sheet).

I hope you found this tutorial useful.

Other Excel tutorials you may also like:

  • How to Save Excel Charts as Images (save as PNG, JPG, BMP)
  • How to Create a Dynamic Chart Range in Excel
  • How to Create Dynamic Chart Titles in Excel
  • How to Insert New Worksheet in Excel (Shortcuts)
  • How to Group Worksheets in Excel
  • How to Rename a Sheet in Excel (4 Easy Ways + Shortcut)
  • How to Copy Chart (Graph) Format in Excel
  • Move Pivot Table to Different Worksheet or Workbook

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Leave a comment cancel reply.

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Worksheet.ChartObjects method (Excel)

  • 6 contributors

Returns an object that represents either a single embedded chart (a ChartObject object) or a collection of all the embedded charts (a ChartObjects object) on the sheet.

expression . ChartObjects ( Index )

expression A variable that represents a Worksheet object.

Return value

This method isn't equivalent to the Charts property. This method returns embedded charts; the Charts property returns chart sheets. Use the Chart property to return the Chart object for an embedded chart.

This example adds a title to embedded chart one on Sheet1.

This example creates a new series in embedded chart one on Sheet1. The data source for the new series is the range B1:B10 on Sheet1.

This example clears the formatting of embedded chart one on Sheet1.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Was this page helpful?

Submit and view feedback for

Additional resources

Excel Off The Grid

Ultimate Guide: VBA for Charts & Graphs in Excel (100+ examples)

Charts and graphs are one of the best features of Excel; they are very flexible and can be used to make some very advanced visualization. However, this flexibility means there are hundreds of different options. We can create exactly the visualization we want but it can be time-consuming to apply. When we want to apply those hundreds of settings to lots of charts, it can take hours and hours of frustrating clicking. This post is a guide to using VBA for Charts and Graphs in Excel.

Adapting the code to your requirements

Understanding the document object model, chart objects vs. charts vs. chart sheets, writing code to work on either chart type, vba code examples, inserting charts, reference charts on a worksheet, reference chart sheets, basic chart settings, chart title, chart legend, chart series, data labels, data points, other useful chart macros, make chart cover cell range, export the chart as an image, resize all charts to the same size as the active chart, bringing it all together, using the macro recorder for vba for charts and graphs.

The code examples below demonstrate some of the most common chart options with VBA. Hopefully you can put these to good use and automate your chart creation and modifications.

While it might be tempting to skip straight to the section you need, I recommend you read the first section in full. Understanding the Document Object Model (DOM) is essential to understand how VBA can be used with charts and graphs in Excel.

In Excel 2013, many changes were introduced to the charting engine and Document Object Model. For example, the AddChart2 method replaced the AddChart method . As a result, some of the code presented in this post may not work with versions before Excel 2013.

It is not feasible to provide code for every scenario you might come across; there are just too many. But, by applying the principles and methods in this post, you will be able to do almost anything you want with charts in Excel using VBA.

The Document Object Model (DOM) is a term that describes how things are structured in Excel. For example:

  • A Workbook contains Sheets
  • A Sheet contains Ranges
  • A Range contains an Interior
  • An Interior contains a color setting

Therefore, to change a cell color to red, we would reference this as follows:

Charts are also part of the DOM and follow similar hierarchical principles. To change the height of Chart 1 , on Sheet1 , we could use the following.

Each item in the object hierarchy must be listed and separated by a period ( . ).

Knowing the document object model is the key to success with VBA charts. Therefore, we need to know the correct order inside the object model. While the following code may look acceptable, it will not work.

In the DOM, the ActiveWorkbook does not contain ChartObjects, so Excel cannot find Chart 1 . The parent of a ChartObject is a Sheet, and the Parent of a Sheet is a Workbook. We must include the Sheet in the hierarchy for Excel to know what you want to do.

With this knowledge, we can refer to any element of any chart using Excel’s DOM.

One of the things which makes the DOM for charts complicated is that many things exist in many places. For example, a chart can be an embedded chart on the face of a worksheet, or as a separate chart sheet.

  • On the worksheet itself, we find ChartObjects. Within each ChartObject is a Chart. Effectively a ChartObject is a container that holds a Chart.
  • A Chart is also a stand-alone sheet that does not have a ChartObject around it.

This may seem confusing initially, but there are good reasons for this.

To change the chart title text, we would reference the two types of chart differently:

  • Chart on a worksheet: Sheets(“Sheet1”).ChartObjects(“Chart 1”).Chart .ChartTitle.Text = “My Chart Title”
  • Chart sheet: Sheets(“Chart 1”). ChartTitle.Text = “My Chart Title”

The sections in bold are exactly the same. This shows that once we have got inside the Chart, the DOM is the same.

We want to write code that will work on any chart; we do this by creating a variable that holds the reference to a Chart.

Create a variable to refer to a Chart inside a ChartObject:

Create a variable to refer to a Chart which is a sheet:

Now we can write VBA code for a Chart sheet or a chart inside a ChartObject by referring to the Chart using cht :

OK, so now we’ve established how to reference charts and briefly covered how the DOM works. It is time to look at lots of code examples.

In this first section, we create charts. Please note that some of the individual lines of code are included below in their relevant sections.

Create a chart from a blank chart

In this section, we look at the methods used to reference a chart contained on a worksheet.

Active Chart

Create a Chart variable to hold the ActiveChart:

Chart Object by name

Create a Chart variable to hold a specific chart by name.

Chart object by number

If there are multiple charts on a worksheet, they can be referenced by their number:

  • 1 = the first chart created
  • 2 = the second chart created

Loop through all Chart Objects

If there are multiple ChartObjects on a worksheet, we can loop through each:

Loop through all selected Chart Objects

If we only want to loop through the selected ChartObjects we can use the following code.

This code is tricky to apply as Excel operates differently when one chart is selected, compared to multiple charts. Therefore, as a way to apply the Chart settings, without the need to repeat a lot of code, I recommend calling another macro and passing the Chart as an argument to that macro.

Now let’s move on to look at the methods used to reference a separate chart sheet.

Set up a Chart variable to hold the ActiveChart:

Note: this is the same code as when referencing the active chart on the worksheet.

Chart sheet by name

Set up a Chart variable to hold a specific chart sheet

Loop through all chart sheets in a workbook

The following code will loop through all the chart sheets in the active workbook.

This section contains basic chart settings.

All codes start with cht. , as they assume a chart has been referenced using the codes above.

Change chart type

Create an empty ChartObject on a worksheet

Select the source for a chart

Delete a chart object or chart sheet

Change the size or position of a chart

Change the visible cells setting

Change the space between columns/bars (gap width)

Change the overlap of columns/bars

Remove outside border from chart object

Change color of chart background

Charts have four axis:

  • xlValue, xlSecondary
  • xlCategory, xlSecondary

These are used interchangeably in the examples below. To adapt the code to your specific requirements, you need to change the chart axis which is referenced in the brackets.

All codes start with cht. , as they assume a chart has been referenced using the codes earlier in this post.

Set min and max of chart axis

Display or hide chart axis

Display or hide chart title

Change chart axis title text

Reverse the order of a category axis

Gridlines help a user to see the relative position of an item compared to the axis.

Add or delete gridlines

Change color of gridlines

Change transparency of gridlines

The chart title is the text at the top of the chart.

Change chart title text

Position the chart title

Format the chart title

The chart legend provides a color key to identify each series in the chart.

Display or hide the chart legend

Position the legend

The Plot Area is the main body of the chart which contains the lines, bars, areas, bubbles, etc.

Background color of Plot Area

Set position of Plot Area

Chart series are the individual lines, bars, areas for each category.

All codes starting with srs . assume a chart’s series has been assigned to a variable.

Add a new chart series

Reference a chart series

Set up a Series variable to hold a chart series:

  • 1 = First chart series
  • 2 = Second chart series

Referencing a chart series by name

Delete a chart series

Loop through each chart series

Change series data

Changing fill or line colors

Changing visibility

Changing line weight

Changing line style

Formatting markers

Data labels display additional information (such as the value, or series name) to a data point in a chart series.

Display or hide data labels

Change the position of data labels

Error bars were originally intended to show variation (e.g. min/max values) in a value. However, they also commonly used in advanced chart techniques to create additional visual elements.

Turn error bars on/off

Error bar end cap style

Error bar color

Error bar thickness

Error bar direction settings

Each data point on a chart series is known as a Point.

Reference a specific point

The following code will reference the first Point.

1 = First chart series 2 = Second chart series etc, etc.

Loop through all points

Point example VBA codes

Points have similar properties to Series, but the properties are applied to a single data point in the series rather than the whole series. See a few examples below, just to give you the idea.

Turn on data label for a point

Set the data label position for a point

In this section, I’ve included other useful chart macros which are not covered by the example codes above.

The following code changes the location and size of the active chart to fit directly over the range G4:N20

The following code saves the active chart to an image in the predefined location

The following code resizes all charts on the Active Sheet to be the same size as the active chart.

Just to prove how we can use these code snippets, I have created a macro to build bullet charts.

This isn’t necessarily the most efficient way to write the code, but it is to demonstrate that by understanding the code above we can create a lot of charts.

The data looks like this:

Bullet Chart Data

The chart looks like this:

Bullet Chart Completed

The code which achieves this is as follows:

The Macro Recorder is one of the most useful tools for writing VBA for Excel charts. The DOM is so vast that it can be challenging to know how to refer to a specific object, property or method. Studying the code produced by the Macro Recorder will provide the parts of the DOM which you don’t know.

As a note, the Macro Recorder creates poorly constructed code; it selects each object before manipulating it (this is what you did with the mouse after all). But this is OK for us. Once we understand the DOM, we can take just the parts of the code we need and ensure we put them into the right part of the hierarchy.

As you’ve seen in this post, the Document Object Model for charts and graphs in Excel is vast (and we’ve only scratched the surface.

I hope that through all the examples in this post you have a better understanding of VBA for charts and graphs in Excel. With this knowledge, I’m sure you will be able to automate your chart creation and modification.

Have I missed any useful codes? If so, put them in the comments.

Looking for other detailed VBA guides? Check out these posts:

  • VBA for Tables & List Objects
  • VBA for PivotTables
  • VBA to insert, move, delete and control pictures

Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere Start using Office Scripts and Power Automate to automate Excel in new ways.

2 thoughts on “Ultimate Guide: VBA for Charts & Graphs in Excel (100+ examples)”

Thank you for this great article. Very comprehensive and insightful.

Thank You 😁

Leave a Comment Cancel reply

Mom and More

10 Helpful Worksheet Ideas for Primary School Math Lessons

M athematics is a fundamental subject that shapes the way children think and analyze the world. At the primary school level, laying a strong foundation is crucial. While hands-on activities, digital tools, and interactive discussions play significant roles in learning, worksheets remain an essential tool for reinforcing concepts, practicing skills, and assessing understanding. Here’s a look at some helpful worksheets for primary school math lessons.

Comparison Chart Worksheets

Comparison charts provide a visual means for primary school students to grasp relationships between numbers or concepts. They are easy to make at www.storyboardthat.com/create/comparison-chart-template , and here is how they can be used:

  • Quantity Comparison: Charts might display two sets, like apples vs. bananas, prompting students to determine which set is larger.
  • Attribute Comparison: These compare attributes, such as different shapes detailing their number of sides and characteristics.
  • Number Line Comparisons: These help students understand number magnitude by placing numbers on a line to visualize their relative sizes.
  • Venn Diagrams: Introduced in later primary grades, these diagrams help students compare and contrast two sets of items or concepts.
  • Weather Charts: By comparing weather on different days, students can learn about temperature fluctuations and patterns.

Number Recognition and Counting Worksheets

For young learners, recognizing numbers and counting is the first step into the world of mathematics. Worksheets can offer:

  • Number Tracing: Allows students to familiarize themselves with how each number is formed.
  • Count and Circle: Images are presented, and students have to count and circle the correct number.
  • Missing Numbers: Sequences with missing numbers that students must fill in to practice counting forward and backward.

Basic Arithmetic Worksheets

Once students are familiar with numbers, they can start simple arithmetic. 

  • Addition and Subtraction within 10 or 20: Using visual aids like number lines, counters, or pictures can be beneficial.
  • Word Problems: Simple real-life scenarios can help students relate math to their daily lives.
  • Skip Counting: Worksheets focused on counting by 2s, 5s, or 10s.

Geometry and Shape Worksheets

Geometry offers a wonderful opportunity to relate math to the tangible world.

  • Shape Identification: Recognizing and naming basic shapes such as squares, circles, triangles, etc.
  • Comparing Shapes: Worksheets that help students identify differences and similarities between shapes.
  • Pattern Recognition: Repeating shapes in patterns and asking students to determine the next shape in the sequence.

Measurement Worksheets

Measurement is another area where real-life application and math converge.

  • Length and Height: Comparing two or more objects and determining which is longer or shorter.
  • Weight: Lighter vs. heavier worksheets using balancing scales as visuals.
  • Time: Reading clocks, days of the week, and understanding the calendar.

Data Handling Worksheets

Even at a primary level, students can start to understand basic data representation.

  • Tally Marks: Using tally marks to represent data and counting them.
  • Simple Bar Graphs: Interpreting and drawing bar graphs based on given data.
  • Pictographs: Using pictures to represent data, which can be both fun and informative.

Place Value Worksheets

Understanding the value of each digit in a number is fundamental in primary math.

  • Identifying Place Values: Recognizing units, tens, hundreds, etc., in a given number.
  • Expanding Numbers: Breaking down numbers into their place value components, such as understanding 243 as 200 + 40 + 3.
  • Comparing Numbers: Using greater than, less than, or equal to symbols to compare two numbers based on their place values.

Fraction Worksheets

Simple fraction concepts can be introduced at the primary level.

  • Identifying Fractions: Recognizing half, quarter, third, etc., of shapes or sets.
  • Comparing Fractions: Using visual aids like pie charts or shaded drawings to compare fractions.
  • Simple Fraction Addition: Adding fractions with the same denominator using visual aids.

Money and Real-Life Application Worksheets

Understanding money is both practical and a great way to apply arithmetic.

  • Identifying Coins and Notes: Recognizing different denominations.
  • Simple Transactions: Calculating change, adding up costs, or determining if there’s enough money to buy certain items.
  • Word Problems with Money: Real-life scenarios involving buying, selling, and saving.

Logic and Problem-Solving Worksheets

Even young students can hone their problem-solving skills with appropriate challenges.

  • Sequences and Patterns: Predicting the next item in a sequence or recognizing a pattern.
  • Logical Reasoning: Simple puzzles or riddles that require students to think critically.
  • Story Problems: Reading a short story and solving a math-related problem based on the context.

Worksheets allow students to practice at their own pace, offer teachers a tool for assessment, and provide parents with a glimpse into their child’s learning progression. While digital tools and interactive activities are gaining prominence in education, the significance of worksheets remains undiminished. They are versatile and accessible and, when designed creatively, can make math engaging and fun for young learners.

The post 10 Helpful Worksheet Ideas for Primary School Math Lessons appeared first on Mom and More .

Mathematics is a fundamental subject that shapes the way children think and analyze the world. At the primary school level, laying a strong foundation is crucial. While hands-on activities, digital tools, and interactive discussions play significant roles in learning, worksheets remain an essential tool for reinforcing concepts, practicing skills, and assessing understanding. Here’s a look […]

worksheet or chart

Dynamically display the contents of a cell or range in a graphic object

Because the cell or cell range is linked to the graphic object, changes that you make to the data in that cell or cell range automatically appear in the graphic object.

Dynamically display cell contents in a shape or text box on a worksheet

If you do not have an shape or text box in your worksheet, do the following:

To create an shape, on the Insert tab, in the Illustrations group, click Shapes , and then click the shape that you want to use.

To create a text box, on the Insert tab, in the Text group, click Text Box , and then drag to draw the text box in the text box size that you want.

On the worksheet, click the shape or text box to which you want to link the cell contents.

In the formula bar, type an equal sign ( = ).

Click the worksheet cell that contains the data or text that you want to link to.

Tip:  You can also type the reference to the worksheet cell. Include the sheet name, followed by an exclamation point; for example, =Sheet1 !F2 .

Press ENTER.

The contents of the cell is displayed in the shape or text box that you selected.

Note:  You cannot use this procedure in a scribble, line, or connector shape.

Dynamically display cell contents in a title, label, or text box on a chart

On a chart, click the title, label, or text box that you want to link to a worksheet cell, or do the following to select it from a list of chart elements.

Click a chart.

This displays the Chart Tools , adding the Design , Layout , and Format tabs.

On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want to use.

worksheet or chart

In the worksheet, select the cell that contains the data or text that you want to display in the title, label, or text box on the chart.

Tip:  You can also type the reference to the worksheet cell. Include the sheet name, followed by an exclamation point, for example, Sheet1 !F2

The contents of the cell is displayed in the title, label, or text box that you selected.

Dynamically display cell range contents in a picture

Click the arrow next to the toolbar, and then click More Commands .

Under Choose commands from , select All Commands .

In the list, select Camera , click Add , and then click OK .

Select the range of cells.

Click a location on a worksheet or chart where you want the picture of the cell range inserted.

The contents of the cell range is displayed in the picture.

To format the picture or do other operations, right click the picture and choose a command.

For example, you may want to click the Format Picture command to change the border or make the background transparent.

Need more help?

You can always ask an expert in the Excel Tech Community  or get support in  Communities .

Facebook

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

worksheet or chart

Microsoft 365 subscription benefits

worksheet or chart

Microsoft 365 training

worksheet or chart

Microsoft security

worksheet or chart

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

worksheet or chart

Ask the Microsoft Community

worksheet or chart

Microsoft Tech Community

worksheet or chart

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

IMAGES

  1. Printable Tally Chart Worksheets

    worksheet or chart

  2. chart and graph worksheets

    worksheet or chart

  3. Tally Charts Printable Worksheets for Grade 1

    worksheet or chart

  4. Graphing Worksheets 5th Grade

    worksheet or chart

  5. Reading Bar Charts Worksheet

    worksheet or chart

  6. Interpreting Graphs Worksheet High School

    worksheet or chart

VIDEO

  1. Which Chart will you use? #shorts #microsoft #excel

  2. Cube root

  3. Working with Large Worksheets, Charting, What If (pg 848-857)

  4. Creating Interactive Worksheet Using Top Worksheet

COMMENTS

  1. Create a chart from start to finish

    Windows macOS Web Create a chart Select data for the chart. Select Insert > Recommended Charts. Select a chart on the Recommended Charts tab, to preview the chart. Note: You can select the data you want in the chart and press ALT + F1 to create a chart immediately, but it might not be the best chart for the data.

  2. Difference between Sheets and Worksheets in VBA

    Chart - the sheet which contains a single chart DialogSheet - an Excel 5 dialog sheet. These are effectively defunct as they have been replaced by VBA UserForms Macro sheets - A sheet containing Excel 4 macros. These were replaced by VBA in 1995.

  3. How to make a chart (graph) in Excel and save it as template

    A chart, also known as graph, is a graphical representation of numeric data where the data is represented by symbols such as bars, columns, lines, slices, and so on. It is common to make graphs in Excel to better understand large amounts of data or relationship between different data subsets.

  4. How to Make a Graph in Microsoft Excel

    Choose Your Own Chart. If you would prefer to select a graph on your own, click the All Charts tab at the top of the window. You'll see the types listed on the left. Select one to view the styles for that type of chart on the right. To use one, select it and click "OK."

  5. How to Make a Bar Chart in Microsoft Excel

    To insert a bar chart in Microsoft Excel, open your Excel workbook and select your data. You can do this manually using your mouse, or you can select a cell in your range and press Ctrl+A to select the data automatically. Once your data is selected, click Insert > Insert Column or Bar Chart.

  6. How to Make Charts and Graphs in Excel

    Click Add Chart Element and click Data Labels. There are six options for data labels: None (default), Center, Inside End, Inside Base, Outside End, and More Data Label Title Options . The four placement options will add specific labels to each data point measured in your chart. Click the option you want.

  7. How to Create a Chart in Excel Using Shortcut Keys

    What to Know. Create a chart on a new worksheet: Select the data you want to use in the chart, then press the F11 key on the keyboard. Add a chart to the current worksheet: Select the data, press and hold the Alt key (or the Option key on a Mac), then press the F1 key. Change the chart style: Select the chart, go to the Chart Tools Design tab ...

  8. How to Create Charts in Office 365

    Excel: To move your chart to a different worksheet or create a new worksheet to hold your chart, go to the (Chart Tools) Design tab and click the Move Chart button. You see the Move Chart dialog box. To move your chart to a different worksheet, click the Object In option button, choose the worksheet in the drop-down list, and click OK.

  9. How to create a chart in Excel from multiple sheets

    1. Create a chart based on your first sheet Open your first Excel worksheet, select the data you want to plot in the chart, go to the Insert tab > Charts group, and choose the chart type you want to make. In this example, we will be creating the Stack Column chart: 2. Add a second data series from another sheet

  10. Determine if worksheet is a chart

    So ActiveSheet.Type and ActiveChart.Type returned 3 for you and -4169 for me. A stacked area chart returned 1 and a line with markers returned 4. So this isn't the SHEET type, and it isn't the CHART type. There's a hidden property Type in the Chart class in the object browser, and also a SubType (both defined as Longs).

  11. How to Move Chart to New Sheet in Excel? 2 Easy Ways!

    Click on the chart object that you want to move to a new chart sheet. Click on the Chart Design tab. In the Location group, click on the 'Move Chart' icon. In the Move Chart dialog box, select 'New Sheet'. Give a name to the sheet where this chart would be moved (or keep the default Chart1 name) Click OK.

  12. Worksheet.ChartObjects method (Excel)

    This method returns embedded charts; the Charts property returns chart sheets. Use the Chart property to return the Chart object for an embedded chart. Example. This example adds a title to embedded chart one on Sheet1. With Worksheets("Sheet1").ChartObjects(1).Chart .HasTitle = True .ChartTitle.Text = "1995 Rainfall Totals by Month" End With

  13. Ultimate Guide: VBA for Charts & Graphs in Excel (100+ examples)

    A Sheet contains Ranges A Range contains an Interior An Interior contains a color setting Therefore, to change a cell color to red, we would reference this as follows: ActiveWorkbook.Sheets ("Sheet1").Range ("A1").Interior.Color = RGB (255, 0, 0) Charts are also part of the DOM and follow similar hierarchical principles.

  14. Graph Maker

    Choose from 20+ chart types & hundreds of templates. Easily create your customized charts & diagrams with Canva's free online graph maker. Choose from 20+ chart types & hundreds of templates ... Free lessons, worksheets and templates, just for educators. LMS integrations ...

  15. VBA Guide For Charts and Graphs

    Excel charts and graphs are used to visually display data. In this tutorial, we are going to cover how to use VBA to create and manipulate charts and chart elements. You can create embedded charts in a worksheet or charts on their own chart sheets. Creating an Embedded Chart Using VBA. We have the range A1:B4 which contains the source data ...

  16. Link a chart title, label, or text box to a worksheet cell

    On a chart, click the title, label, or text box that you want to link to a worksheet cell, or do the following to select it from a list of chart elements. Click a chart. This displays the Chart Tools tabs. Note: The names of the tabs within Chart Tools differs depending on the version of Excel you are using.

  17. VBA Refer to worksheet vs chart sheet

    2 Answers Sorted by: 8 Use WBO.Sheets instead of WBO.Worksheets in the loop. Verify that WSO.Visible = xlSheetVisible to filter out hidden sheets. Share Improve this answer Follow answered Jul 23, 2011 at 0:35 GSerg 77.1k 17 165 352

  18. 10 Helpful Worksheet Ideas for Primary School Math Lessons

    Here's a look at some helpful worksheets for primary school math lessons. Comparison Chart Worksheets Comparison charts provide a visual means for primary school students to grasp relationships ...

  19. How to Make a Comparison Chart in Excel (4 Effective Ways)

    Step-09: Adding Slicer and Line Chart to New Worksheet. First, select the Slicer from the Support Sheet worksheet. After that, press CTRL+X. Next, go to the newly created worksheet and paste it here on cell B2 by pressing CTRL+V. Consequently, you will be able to see that the Name slicer has been added to the new worksheet.

  20. Dynamically display the contents of a cell or range in a graphic object

    To display the contents of a worksheet cell in a shape, text box, or chart element, you can link the shape, text box, or chart element to the cell that contains the data that you want to display. Using the Camera command, you can also display the contents of a cell range by linking the cell range to a picture.

  21. ENHANCING CHARTS WITH ILLUSTRATIONS Flashcards

    Insert. Illustrations must be inserted in a worksheet, not opened from the File tab. True. To size a picture or a clip art and keep it in proportion, size it from the _____. corner. _____ are graphics you can insert in a worksheet or chart. illustrations. To quickly delete an illustration, select the illustration and _____.

  22. PDF Website worksheet-status chart Updated 1.03

    WORKSHEET In the box to the right of each year, check box if you spent any part of that year in the United States on an F or J Visa. If you have ever switched from a F visa to a J visa or vice-versa, please email the Tax Clinic for assistance with determining your residency status. 1985 1998 2011 1986 1999 2012