MS Excel 2010: How to Create a Bar Chart
This Excel tutorial explains how to create a basic bar chart in Excel 2010 (with screenshots and step-by-step instructions).
What is a Bar Chart?
A bar chart is a graph that shows horizontal bars with the axis values for the bars displayed on the bottom of the graph.
It is a graphical object used to represent the data in your Excel spreadsheet.
You can use a bar chart when:
- You want to compare values across categories.
- The category text is long and difficult to display in a column chart.
- You want to show duration in a graph.
If you want to follow along with this tutorial, download the example spreadsheet.
Steps to Create a Bar Chart
To create a bar chart in Excel 2010, you will need to do the following steps:
Highlight the data that you would like to use for the bar chart. In this example, we have selected the range A1:C5.
Select theInsert tabin the toolbar at the top of the screen. Click on the Bar button in theCharts groupand then select a chart from the drop down menu. In this example, we have selected the first bar chart (calledClustered Bar) in the 2-D Column section.
Now you will see the bar chart appear in your spreadsheet with horizontal bars to represent both the shelf life and restock time for each product. The shelf life (in days) is displayed as red horizontal bars and the restock time (in days) is displayed as blue horizontal bars. You can see the axis values on the bottom of the graph for these horizontal bars.
Finally, let’s add a title for the bar chart. By default, your chart will be created without a title in Excel 2010.
To add a title, select theLayout tabunder Chart Tools in the toolbar at the top of the screen (Chart Tools will only appear when you have the chart selected). Click on the Chart Title button in theLabels groupand then select "Above Chart" from the drop down menu.
Now you should see a title appear at the top of the chart area. Click on the title and it will become editable. Enter the text that you would like to see as the title. In this tutorial, we have entered "Product Life (in Days)" as the title for the bar chart.
Congratulations, you have finished creating your first bar chart in Excel 2010!
How to Make a Bar Chart in Excel
Bar charts are useful for comparing discrete groups of data such as frequency, amount, duration, or units. They are also used to illustrate patterns. The type of bar chart you choose to make depends on how you plan to use it to tell the data’s story.
To create a bar chart, you’ll need a minimum of two variables: theindependent variable(the one that doesn’t change, such as the name of a brand), and thedependent variable(the one that changes, like sales and percentage).
What Is a Bar Chart?
Abar chart(also called abar graph) is a great way to visually display certain types of information, such as changes over time or differences in size, volume, or amount. Bar charts can be horizontal or vertical; in Excel, the vertical version is referred to ascolumn chart. Here are some examples using fabricated data.
The key aspects of a bar chart are highlighted below:
Different Kinds of Bar Charts
Excel provides variations of Bar and Column charts. Here’s a quick summary of each:
- Stacked:A chart that shows the dependent variables stacked on top of each other. This chart is also called segmented.
- Clustered:A chart that displays a group of dependent variables, also called grouped. A double graph is a clustered graph that has two dependent variables.
- 3D:A chart that shows the dependent variables in a 3D format.
What Are Bar Charts Best Used For?
The bar chart was created in the 1700s by William Playfair, an engineer and economist from Scotland. He is also credited with creating the pie chart. Playfair’s first published use of the bar chart was in a book about imports and exports between Scotland and other countries.
Today, bar charts are a useful tool for illustrating patterns or for comparing discrete groups of data. There are both horizontal and vertical layouts available. Use a horizontal layout when comparing duration. If the data labels are long, this layout makes them easier to read. Use the vertical layout for comparisons of height, to show growth, or if some of the values are negative.
In the finance world, vertical bar charts are often used to represent the trading price of a security for a day. The top of the bar is the highest price, the bottom the lowest, and the opening and closing prices are also indicated.
How to Make Bar Chart in Excel
In this section, we’ll provide steps and images to create a bar chart in Excel 2011 for Mac. Any differences in Microsoft-supported versions (2010, 2013, 2016 for Windows), or 2016 for Mac are called out in the text below. Previous versions of Excel included a chart wizard, but that was removed after the 2007 release. The term “right-click” is used throughout this article. If you don’t have a mouse, use a two-fingered tap on the trackpad, or presscontrol+tapon your keyboard.
Step 1:Enter your data into Excel columns.
Step 2:Click and drag your mouse across the data that will appear in the chart.
Confirm the highlighted columns contain one independent variable and one dependent variable (multiple dependent variables are discussed in the next section), and the column headers if desired (Excel will make one of the headers as the chart title).
Step 3:From the ribbon, clickChart, click theBaricon, and then click2-D Clustered Bar(with a single dependent variable as we are using here, the results will be the same no matter which option you choose).
Other versions of Excel:Click theInserttab, clickBar Chart, and then clickClustered Bar(in 2016 versions, hover your cursor over the options to display a sample of how the chart will appear).
The chart will appear on the same page as the data. Your default might be to include the legend — you can remove it by clicking it and pressing the delete key on your keyboard.
Create a Chart with Multiple Dependent Variables
Unlike data with a single dependent variable, the type of chart chosen (clustered or stacked) makes a difference for data with more than one dependent variable. For this example, we’ll use clustered. The legend that Excel creates will also be valuable.
Follow the same steps as above to select at least two columns of dependent variables.
Moving or Copying a Chart
To move a chart on the same sheet, click an edge of the chart and drag it where you want it to appear on the Excel workbook sheet. To move your chart to another sheet in the same workbook, right-click on the chart and clickMove Chart…. Select the desired sheet, or create a new sheet, and pressOK. To add the chart to any other program, clickCutorCopyfrom the same menu.
If the chart is kept in the same workbook, any changes made to the data will be reflected in the chart. If you paste the chart into a document in another Microsoft Office program, the same is true if the documents stays on the same computer. If you paste the chart into a non-Microsoft program, the chart won’t update if the data changes. Read “How to Make a Spreadsheet in Excel, Word, Google Sheets, and Smartsheet for Beginners” to learn more about creating and sharing data across different platforms.
Excel Chart Formatting Options
Once created, you can customize charts in many ways.Themesare preset color and shape combinations available in Excel. Changing the theme affects other options, as well as any other charts created in the future. If you only want to change the current chart, use theChart Stylesoption.
To change the theme, click theHometab, then clickThemes, and make your choice.
Other versions of Excel:Click thePage Layouttab, clickThemes, and then make your choice.
To change the style, clickCharts, and scroll through the options underChart Styleson the same ribbon.
Other versions of Excel:ClickChart ToolsorChart Designtab, and clickLayoutto scroll through the options underChart Styles. If you have aChart Designtab, the different layouts will appear in the ribbon, similar to the image above.
If the data presented in the chart isn’t quite clear, a title can help. Titles aren’t needed for charts with a single dependent variable.
Click onChart Layout, clickChart Title, and click your option. Using the overlap/overlay option may cover part of the chart, so be sure the title doesn’t cover key information.
Other versions of Excel:ClickChart Toolstab, then clickLayout, clickChart Title, and click your option.
If the categories in the horizontal or vertical axis need a title, follow the steps above. However, selectAxis Titlesinstead, and then choose the horizontal axis or vertical axis.
To change the font and appearance of titles, clickChart Titleand then clickMore Title Options. Additionally, in some versions of Excel, you can click on the title in the chart and a side menu will appear with options to customize the text.
To reword a title, just click on it in the chart and retype.
To adjust the horizontal or vertical axis, you can resize by clicking on a square in the corner and dragging an edge.
For finer adjustments, right click the axis and chooseFormat Axis…
For a numeric axis, you can change the the start and end points, as well as the units displayed. Simply change the numbers in the boxes to make the starting and ending point the minimum and maximum, respectively.
To format the copy, right-click any text in the chart and clickFormat Text(orFormat TitleorFormat Legend, etc. — depending on your version of Excel and the area of the chart you wish to change). From this menu, you can change the font style and color, and add shadows or other effects.
Adding Data Labels
Data labels show the value associated with the bars in the chart. This information can be useful if the values are close in range. To add data values, right-click on one of the bars in the chart, and clickAdd Data Labels. This will create a label for each bar in that series. For clustered charts, one of each color will have to be labeled.
Moving the Legend
Click and drag the legend to a new location on the chart, or click on it and press the delete button on your keyboard to remove it completely.
The items will appear in reverse order from the spreadsheet. Rather than changing the order there, it’s easier to right-click the axis, clickFormat Axis…, and then click the box next toCategories in Reverse Order. This change will also affect the order of the data clusters, if that was the chart format chosen.
In some versions of Excel, you can also change the data order by selecting one of the bars and editing the formula bar.
Adjusting Axis Text
If the text on an axis is long, pivot it on an angle to occupy less space. Right-click the axis, clickFormat Axis, clickText Box, and enter an angle.
You can also opt to only show some of the axis labels. Right-click the axis, clickFormat Axis, then clickScale, and enter a value in theInterval between labelsbox. A value of 2 will show every other label; 3 will show every third.
If you want to create a cleaner, less cluttered chart, hiding some labels is a good option. But the context of the hidden text is still obvious.
Changing Chart Values
Update the spreadsheet and the values in the chart will update, too. But remember, if the chart has been copied to a non-Microsoft Office document, it won’t update — in this case, copy the updated version and replace it in the document.
Changing the Look of the Bars
Right-click a bar, then clickFormat Data Series… and make adjustments. In addition to changing the color, you can also add a gradient or pattern, as well as many other effects.
For clustered bar charts, any changes will only affect the bars associated with the same dependant variable of the selected bar. Repeat to update all the bars in the chart.
If bars don’t look right, select the chart, right-click the chart and clickChange Chart Type. In addition to the 2D bars demonstrated in this tutorial, there are options for 3D bars/columns, cylinders, cones, and pyramids.
Changing the Background of the Chart and Plot Area
To change the background, right-click in a blank area of the chart and clickFormat Chart Area… or right-click the plot area and clickFormat Plot Area…. Like the bars, you can change the color, add a gradient or pattern, adjust the color and size of lines, as well as other effects.
Adding a Data Table
Adata tabledisplays the spreadsheet data that was used to create the chart beneath the bar chart. This shows the same data as data labels, so use one or the other.
To add a data table, click theChart Layouttab, clickData Table, and choose your option. If the legend key option is chosen, you can remove the legend as demonstrated in the image below.
Changing Chart Orientation
To swap the vertical and horizontal axes, right-click on the chart and clickChange Chart Type. If you chose a column chart, chose bar chart instead, and vice versa. There are other ways to do this, but this is the simplest.
Advanced Formatting Options
Trend linesshow the overall trend of your data (falling or rising) if the numbers are somewhat scattered. Trend lines are only available on column charts.
To add a trend line, click theChart Layouttab, and clickTrendlines.
Unlike most of these options, trendlines will accumulate rather than being replaced if you change the chosen type.
Error barsshow the margin of uncertainty in data. You’ll have to add them to each data series separately.
To add an error bar, click theChart Layouttab, and clickError Bars.
To customize the error bars, clickError Barsand clickError Bar Options….
Minor grid linescan help show fine differences in the data.
To add grid lines, click theChart Layout taband clickGridlines.
Other versions of Excel:For the three options above, click theChart Toolstab, clickLayout, and choose the option. Depending on your version, you can also clickAdd Chart Elementin ribbon on theChart Designtab.
You can add a different color fornegative valuesto make them stand out. After you create your chart, right-click one one of the bars, clickFormat Data Series…, clickFill, and check the box next toInvert if negative.
You may need to select a solid color for the data series bars in order for the change to take effect.
How to Make a Bar Chart Easier to Read
You can improve the readability of charts by using some of the formatting options mentioned above (like adding data labels or minor grid lines). Many categories can make it difficult to read the labels, so apply some of the formatting options mentioned earlier (like skipping some labels or putting the labels at an angle) to alleviate this problem.
How Do I Make a Comparison Graph in Excel?
A comparison graph is another name for a line chart. Creating a comparison graph is identical to creating a bar chart, except you chooseLineinstead ofBarorColumn. There are a number of versions available, so you can experiment with which option looks best.
How to Make a Percentage Graph in Excel
Apercentage graphis another name for a pie chart. To create one, follow the steps to create a bar chart, but choose thePieoption. Pie charts are best for comparing the parts of a whole. There are a number of versions available, so you can see which works best for your needs.
What’s the Difference Between a Bar Chart and Other Charts
Different chart styles are useful for displaying different information. Here’s a summary.
Make Better Decisions, Faster with a Bar Chart 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.
How to Create Custom Charts for Better Excel Spreadsheets
Updated September 18, 2017, 2:08pm EDT
The world today has way too much data, but very little information. In today’s article we will show you how to extract information from your spreadsheet’s data and present them in custom charts.
Here at How-To Geek we’re always working really hard to produce great articles for our readers, and so we keep an eye on the number of articles and pageviews for each month. For example, here’s a list of the top 10 articles for October:
|HTG Explains: Why Do So Many Geeks Hate Internet Explorer?||137255||thegeek|
|Remove Complex Backgrounds from Images in Photoshop||80187||EricGoodnight|
|Make Your PC Shut Down at Night (But Only When You’re Not Using It)||36851||thegeek|
|What’s the Difference Between HDMI and DVI? Which is Better?||30548||matthewguay|
|The 10 Cleverest Ways to Use Linux to Fix Your Windows PC||25712||thegeek|
|Today is 10/10/10 – the Answer to Life, the Universe, and Everything||21941||thegeek|
|What’s the Difference Between JPG, PNG, and GIF?||21711||EricGoodnight|
|How to Create a Shortcut That Nukes Every Running Windows App||21448||thegeek|
|What is Cloud Computing and What Does This Stupid Buzzword Mean?||16995||thegeek|
We’re assuming that you’ve got some background with Microsoft Excel and how to use charts, but if you need a primer be sure and check out our article covering how to create charts in Excel.
Custom Charts in Excel
Our Chief Geek likes to keep a close watch on how well his writers perform, so to show him how productive we are, we prepared some excel charts courtesy of Juice Analytics.
Juice Analytics gives a wide range of custom, ready-to-use Excel charts that we can download as an Excel spreadsheet or as a Powerpoint slide.
The Excel spreadsheet comes with two components, the chart data and the chart itself, that we can modify to suit our needs.
The template itself is quite easy to work with—we just have to key in our monthly page views data into the “chart data” section and the chart will automatically update itself to display our data.
This is how the spreadsheet looks after we key in the page view data and put a little bit of cosmetic formatting to make the table look prettier. The chart definitely shows how productive the writers have been the past 2 months.
We chose a different type of column chart to give a report of our daily page views, called the stacked column chart—it is basically the same chart, except it stacks the charts on top of one another and sums the data to give a better picture of how many page views each author’s article gets on a day-to-day basis.
We start keying in the author’s name, dates, and page views into the chart data section…
We’re also adding a total of the daily page views using the standard “sum” function.
The “sum” function aggregates each writer’s page views under the “Total” column.
Our newer articles have been steadily gaining attention for the past 5 days and hopefully for the rest of this month.
These two charts are not the only charts that we can create. Juice Analytics has a lot of different custom charts that we can use to present our data.
We can see our Chief Geek smiling when he sees that we have been working hard producing better articles for How-To Geek.
If you’d like to learn more about how you can use Excel, be sure and check out some of our other articles about using data in our spreadsheets:
Do you have any other tips for your fellow readers who might be trying to use charts? Share them in the comments.
Excel 2010 – Working with Charts
Lesson 17: Working with Charts
Achartis a tool you can use in Excel tocommunicate data graphically. Charts allow your audience to see themeaning behind the numbers, and they make showingcomparisonsandtrendsmuch easier. In this lesson, you’ll learn how toinsertcharts andmodifythem so they communicate information effectively.
Excel workbooks can containa lot of data, and this data can often be difficult to interpret. For example, where are the highest and lowest values? Are the numbers increasing or decreasing?
The answers to questions like these can become much clearer when data is represented as achart. Excel has various types of charts, so you can choose one that most effectively represents your data.
Optional: You can download this example for extra practice.
Types of charts
Click the arrows in the slideshow below to view examples of some of the types of charts available in Excel.
Excel has a variety of chart types, each with its own advantages. Click the arrows to see some of the different types of charts available in Excel.
Column charts use vertical bars to represent data. They can work with many different types of data, but they’re most frequently used for comparing information.
Line charts are ideal for showing trends. The data points are connected with lines, making it easy to see whether values are increasing or decreasing over time.
Pie charts make it easy to compare proportions. Each value is shown as a slice of the pie, so it’s easy to see which values make up the percentage of a whole.
Bar charts work just like column charts, but they use horizontal instead of vertical bars.
Area charts are similar to line charts, except the areas under the lines are filled in.
Surface charts allow you to display data across a 3D landscape. They work best with large data sets, allowing you to see a variety of information at the same time.
Identifying the parts of a chart
Click the buttons in the interactive below to learn about the different parts of a chart.
Thehorizontal axis, also known as thex axis, is thehorizontalpart of the chart.
In this example, the horizontal axis identifies thecategoriesin the chart, so it is also called thecategory axis. However, in abarchart, thevertical axiswould be the category axis.
Thelegendidentifies whichdata serieseach color on the chart represents. For many charts it iscrucial, but for some charts it may not be necessary and can be deleted.
In this example, the legend allows viewers to identify the differentbook genresin the chart.
Thedata seriesconsists of therelated data pointsin a chart. If there are multiple data series in the chart, each will have a different color or style.Piecharts can only have one data series.
In this example, the green columns represent theRomancedata series.
Thetitleshould clearly describe what the chart is illustrating.
Thevertical axis, also known as they axis, is theverticalpart of the chart.
In this example, acolumnchart, the vertical axis measures theheight—orvalue—of the columns, so it is also called thevalue axis. However, in abarchart, thehorizontal axiswould be the value axis.
To create a chart:
- Select thecellsyou want to chart, including thecolumn titlesandrow labels. These cells will be thesource datafor the chart.
Once you insert a chart, a set ofchart toolsarranged into three tabs will appear on the Ribbon. These are only visible when the chart is selected. You can use these three tabs tomodifyyour chart.
To change chart type:
- From theDesigntab, click theChange Chart Typecommand. A dialog box appears.
To switch row and column data:
Sometimes when you create a chart, the data may not be grouped the way you want. In theclustered column chartbelow, the Book Sales statistics are groupedby Fiction and Non-Fiction, with a column for each year. However, you can alsoswitch the row and column dataso the chart will group the statisticsby year, with columns for Fiction and Non-Fiction. In both cases, the chart contains thesame data—it’s just organized differently.
- Select thechart.
- From theDesigntab, select theSwitch Row/Columncommand.
To change chart layout:
- Select theDesigntab.
- Click theMoredrop-down arrow in theChart Layoutsgroup to see all of the available layouts.
Some layouts includechart titles,axes, orlegend labels. To change them, place theinsertion pointin the text and begin typing.
To change chart style:
- Select theDesigntab.
- Click theMoredrop-down arrow in theChart Stylesgroup to see all of the available styles.
To move the chart to a different worksheet:
- Select theDesigntab.
- Click theMove Chartcommand. A dialog box appears. The current location of the chart is selected.
Keeping charts up to date
By default, when you add more data to your spreadsheet, the chart may not include the new data. To fix this, you can adjust thedata range. Simply click the chart, and it will highlight the data range in your spreadsheet. You can then click and drag thehandlein the lower-right corner to change the data range.
If you frequently add more data to your spreadsheet, it may become tedious to update the data range. Luckily, there is an easier way. Simply format your source data as atable, then create achart based on that table. When you add more data below the table, it will automatically be included in both the table and the chart, keeping everything consistent and up to date.
Watch the video below to learn how to use tables to keep charts up to date.
Benchmark Chart in Excel 2010
Benchmark Charts or Budget Charts are a great way to show your actual sales versus your benchmark in a graphical way, highlighting the strong v weak months.
I will show you how to do this using Using Excel 2010.
STEP 1:Enter your data into three columns, the month, the actual sales and the benchmark sales
STEP 2:Click inside your data and go to the ribbon and chooseInsert > 2-D Clustered Column
STEP 3:Select the Benchmark series chart andRight Click and choose the Change Series Chart Type
STEP 4:This will bring up theChange Chart Typedialogue box and you will need to selectLineandLine with Markers
STEP 5:Click on the line chart and pressCTRL+1. This will open up theFormat Data Seriesdialogue box
STEP 6:Select theMarker Optionsand chooseBuilt In, select the horizontal line type and increase the Size to 10. You can also format the color of the line.
STEP 7:Select theLine Colorand chooseNo Line and press OK!