Multipart article

Excel Charting Basics: How to Make a Chart and Graph

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 elevate your data by providing an easy-to-understand visualization of numeric values.  While the terms are often used interchangeably, they are slightly different. Graphs are the most basic way to represent data visually, and typically display data point values over a duration of time. Charts are a bit more complex, as they allow you to compare pieces of a data set relative to the other data in that set. Charts are also considered more visual than graphs, since they often take a different shape than a generic x- and y-axis.

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.


When to Use Each Chart and Graph Type in Excel

Excel offers a large library of chart and graph types to help visually present your data. While multiple chart types might “work” for a given data set, it’s important to select a chart type that best fits with the story you want the data to tell. Of course, you can also add graphical elements to enhance and customize a chart or graph. 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 look and readability of your chart, using them won’t ensure that you maximize the effectiveness of your chart. Below are the top five best practices to make your chart or graph as clear and useful as possible:

  1. 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.

  2. 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.  

  3. 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).

  4. 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.

  5. 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 Excel with data to pull from. In this section, we’ll show you how to chart data in Excel 2016.

Step 1: Enter Data into a Worksheet

  1. Open Excel and select New Workbook.
  2. 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

  1. Highlight the cells that contain the data you want to use in your graph by clicking and dragging your mouse across the cells.
  2. 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

Once you input your data and select the cell range, you’re ready to choose your chart type to display your data. 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

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

How to select Excel chart 

  1. Excel will automatically create a clustered chart column from your selected data. The chart will appear in the center of your workbook.
  2. 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:

  1. Select Axes. Excel will automatically pull the column and row headers from your selected cell range to display both horizontal and vertical axes on your chart (Under Axes, there is a check mark next to Primary Horizontal and Primary Vertical.)How to display or hide axes in Excel charts
  2. Uncheck these options to remove the display axis on your chart. In this example, clicking Primary Horizontal will remove the year labels on the horizontal axis of your chart.How to remove display axis in Excel charts
  3. Click More Axis Options… from the Axes dropdown menu to open a window with additional formatting and text options such as adding tick marks, labels, or numbers, or to change text color and size.Axis dropdown menu in Excel 

To Add Axis Titles:

  1. Click Add Chart Element and click Axis Titles from the dropdown menu. Excel will not automatically add axis titles to your chart; therefore, both Primary Horizontal and Primary Vertical will be unchecked.Add chart elements in Excel
  2. To create axis titles, click Primary Horizontal or Primary Vertical and a text box will appear on the chart. We clicked both in this example. Type your axis titles. In this example, the we added the titles “Year” (horizontal) and “Profit” (vertical).Create axis titles in Excel

To Remove or Move Chart Title:

  • Click Add Chart Element and click Chart Title. You will see four options: None, Above Chart, Centered Overlay, and More Title Options.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:

  1. 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.How to add data labels in Excel charts
  2. The four placement options will add specific labels to each data point measured in your chart. Click the option you want. This customization can be helpful if you have a small amount of precise data, or if you have a lot of extra space in your chart. For a clustered column chart, however, adding data labels will likely look too cluttered. For example, here is what selecting Center data labels looks like:Add specific labels in Excel charts

To Add a Data Table: 

  1. Click Add Chart Element and click Data Table. There are three pre-formatted options along with an extended menu that can be found by clicking More Data Table Options:How to add a data table in Excel
  • None is the default setting, where the data table is not duplicated within the chart.
  • With Legend Keys displays the data table beneath the chart to show the data range. The color-coded legend will also be included.Excel chart with legend key  
  • No Legend Keys also displays the data table beneath the chart, but without the legend.
    Excel chart with no 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:

  1. Click Add Chart Element and click Error Bars. In addition to More Error Bars Options, there are four options: None (default), Standard Error, 5% (Percentage), and Standard Deviation. Adding error bars provide a visual representation of the potential error in the shown data, based on different standard equations for isolating error.How to add error bars in Excel
  2. For example, when we click Standard Error from the options we get a chart that looks like the image below. Standard error in Excel charts

 To Add Gridlines:

  1. Click Add Chart Element and click Gridlines. In addition to More Grid Line Options, there are four options: Primary Major Horizontal, Primary Major Vertical, Primary Minor Horizontal, and Primary Minor Vertical. For a column chart, Excel will add Primary Major Horizontal gridlines by default.How to add gridlines in Excel
  2. You can select as many different gridlines as you want by clicking the options. For example, here is what our chart looks like when we click all four gridline options.
    Gridline options in Excel charts

To Add a Legend:  

  1. Click Add Chart Element and click Legend. In addition to More Legend Options, there are five options for legend placement: None, Right, Top, Left, and Bottom.How to add a legend in Excel
  2. Legend placement will depend on the style and format of your chart. Check the option that looks best on your chart. Here is our chart when we click the Right legend placement.Where to add 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:

  1. Click Add Chart Element and click Trendline. In addition to More Trendline Options, there are five options: None (default), Linear, Exponential, Linear Forecast, and Moving Average. Check the appropriate option for your data set. In this example, we will click Linear.Add a trendline in Excel
  2. Because we are comparing five different products over time, Excel creates a trendline for each individual product. To create a linear trendline for Product A, click Product A and click the blue OK button.Add a linear trendline in Excel
  3. The chart will now display a dotted trendline to represent the linear progression of Product A. Note that Excel has also added Linear (Product A) to the legend.How to display linear progression in Excel chart
  4. To display the trendline equation on your chart, double click the trendline. A Format Trendline window will open on the right side of your screen. Click the box next to Display equation on chart at the bottom of the window. The equation now appears on your chart.How to format a trendline in Excel chart

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

  1. The second dropdown menu on the toolbar is Quick Layout, which allows you to quickly change the layout of elements in your chart (titles, legend, clusters etc.). How to change layout of elements in Excel chart
  2. There are 11 quick layout options. Hover your cursor over the different options for an explanation and click the one you want to apply.Different layout options for chart in Excel

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

  1. Click the Switch Row/Column on the toolbar to flip the axes. Note: It is not always intuitive to flip axes for every chart, for example, if you have more than two variables.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

  1. Click the Select Data icon on the toolbar to change the range of your data.
    How to change range of data on Excel chart
  2. A window will open. Type the cell range you want and click the OK button. The chart will automatically update to reflect this new data range.Changing data range in Excel chart

Step 9: Change Chart Type

  1. Click the Change Chart Type dropdown menu.How to change chart type in Excel
  2. Here you can change your chart type to any of the nine chart categories that Excel offers. Of course, make sure that your data is appropriate for the chart type you choose.Different types of charts in Excel
  3. You can also save your chart as a template by clicking Save as Template

  4. A dialogue box will open where you can name your template. Excel will automatically create a folder for your templates for easy organization. Click the blue Save button.Create a chart template in Excel

Step 10: Move Chart

  1. Click the Move Chart icon on the far right of the toolbar.
    Moving an Excel chart
  2. A dialogue box appears where you can choose where to place your chart. You can either create a new sheet with this chart (New sheet) or place this chart as an object in another sheet (Object in). Click the blue OK button.How to move an Excel chart

Step 11: Change Formatting

  1. The Format tab allows you to change formatting of all elements and text in the chart, including colors, size, shape, fill, and alignment, and the ability to insert shapes. Click the Format tab and use the shortcuts available to create a chart that reflects your organization’s brand (colors, images, etc.).Change chart format in Excel
  2. Click the dropdown menu on the top left side of the toolbar and click the chart element you are editing.How to change chart formatting 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

Although graphs and charts are distinct, Excel groups all graphs under the charts categories listed in the previous sections. To create a graph or another chart type, follow the steps below and select the appropriate graph type.

Select Range to Create a Graph from Workbook Data

  1. Highlight the cells that contain the data you want to use in your graph by clicking and dragging your mouse across the cells.
  2. Your cell range will now be highlighted in gray.Creating a graph in Excel
  3. Once the text is highlighted you can select a graph (which Excel refers to as chart). Click the Insert tab and click Recommended Charts on the toolbar. Then click the type of graph you wish to use.Types of charts and graphs in Excel How to choose the type of chart or graph 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 make a data visualization, you can also create a table in Excel using preexisting data. There are two ways to format a data set as a table:

  • 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).
  1. To format data as a table, click and drag your mouse across the cells with the data range, click the Home tab, and click the Format as Table drop-down menu on the toolbar.How to format data as a table in Excel
  2. Click New Table Style… (You will also see an option to use PivotTables. This feature is outside the scope of this how-to, but the concept is explained in the following section).Table style options in Excel
  3. A dialogue box opens and you can choose which aspects of the selected range to include in your formatted table. Click the blue OK button.How to create a table style in Excel

 


Related Excel Functionality

Excel is one of the most widely-used tools across any industry or type of organization. Charts and graphs are a great way to get started adding visualizations to your work, but there are several other ways to elevate your data in Excel. Below is a list of common features to create more “bang” 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

With the amount of data available today, making sure the right information is accessible and actionable to all stakeholders can be challenging. That’s why finding a tool that enables you to manage and visualize your data in real time is essential to your ability to communicate complex data and make quick decisions.

Smartsheet is an enterprise work management platform that fundamentally changes the way teams, leaders, and businesses get work done. Over 70,000 brands and millions of information workers trust Smartsheet as the best way to plan, track, automate, and report on work.

Smartsheet dashboards with charts gives you real-time visibility into work progress to make better decisions and keep your teams on the same page every step of the way. The configurable, widget-driven dashboards enable users to highlight the information that’s most relevant to their business - without the need for technical support. Know the status of your business at a glance, gain insights, and accelerate your team’s innovation all in one platform.
 


Discover how charts in Smartsheet will help your team make better-informed decisions, fast.

Learn More About Smartsheet Dashboards with Charts


Excel Charting Basics: How to Make a Chart and Graph

Try Smartsheet for Free