ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to Make Bar Charts and Graphs in Excel

Updated on May 10, 2013

Page Views & Earnings graph.

I created this graph by adding earnings to the table, changing the graph type for earnings to line, and then adding a secondary vertical axis.
I created this graph by adding earnings to the table, changing the graph type for earnings to line, and then adding a secondary vertical axis.

What is a bar graph?

A bar graph is used to display a comparison of the amounts of something compared to a certain characteristic of the data. What? The standard description sounds very complicated – it is actually easier to explain by given examples. Imagine you grow apples and you want to find out the frequency of production of apples per month – you would create a simple bar graph that records the total production of apples per month – you’d then create a bar for each month that represents the number of apples.

Before the advent of computers the bar graph was always hand drawn and very time consuming, especially if you made in error with the graph. However, as technology has infiltrated our lives, it’s becoming easier to present data graphically, and also be able to instantly change the data – today, you can create a bar graph using excel or any other spreadsheet program quickly and easily – the graphs are versatile and customizable and can really spice up a presentation.


Why create a bar graph using Excel?

Graphing with excel is very easy; Microsoft Excel has excellent tools to help you produce decent graphs very quickly; there are dozens of types of graphs, and each graph type is fully customizable allowing you to create a graph that not only presents the data you want, but also allows you to format the graph is hundreds of different ways.

How to make a bar graph using Excel

Before you make a bar graph in excel you have to decide what data you are going to present in bar graph format. For the purposes of this example I am going to create a bar graph that shows my page views on all my articles over time. I’ve collected the data from different sources – the table below gives you the raw data:


Month
Page Views
Jan-10
7149
Feb-10
6735
Mar-10
26905
Apr-10
55723
May-10
47666
Jun-10
75462
Jul-10
58130
Aug-10
47518
Sep-10
45450
Oct-10
72596
Nov-10
75555
Dec-10
81162
Jan-11
63716
Feb-11
52330
Mar-11
50899
Apr-11
35424
May-11
35600
Treansferring the raw data into a simple table within Microsoft Excel 2007
Treansferring the raw data into a simple table within Microsoft Excel 2007

STEP 1 – setting up the data table

I have the raw table already and it’s simply a case of transferring this data into a worksheet on Microsoft Excel.

  • I transferred the data directly into an empty worksheet on Excel.
  • I added headings that were relevant (Month, Page Views).
  • I formatted the Month column so that it is viewed as MMM-YY – to do this, highlight the entire range where the dates are going to be by clicking in the top cell, holding the left mouse button and dragging the outline down. Right click on this range and select format cells – on the Number tab, click on Date in the Category and move down the Type until you find Mar-01 – highlight this and select OK
  • In each cell under Month I entered the first day of the month – for example I entered 1/1/10 for January 10; in each cell under Page Views I entered the actual page views I received for that month.


Choosing the Graph type in Excel 2007
Choosing the Graph type in Excel 2007

STEP 2 – creating a graph

Now that I have the data table setup I can create the graph; creating a graph in excel is very easy as long as you have the data table setup correctly:

  • Highlight the data table – click on the top left cell, click and hold the left mouse button and drag the box to the last cell in the data table.
  • Using the menus – click on Insert
  • You now see some submenus – as we are creating a Bar chart, click on the small down arrow underneath Bar – this presents you with various options relating to bar charts – hovering over each chart will give you a brief explanation of the chart. I wanted a vertical bar chart so I selected the Column rather than Bar graph and chose cluster column chart. Clicking on the cluster column chart Excel creates a bar chart based on your data.


Using the Design Menu for Graphs in Excel 2007
Using the Design Menu for Graphs in Excel 2007
Using the Layout Menu for Graphs in Excel 2007
Using the Layout Menu for Graphs in Excel 2007
Using the Format Menu for Graphs in Excel 2007
Using the Format Menu for Graphs in Excel 2007

STEP 3 - customizing the bar chart

The method above gives you a pretty decent chart already, but I want to format the chart by changing colors etc:

  • When I inserted the chart, I note that there is a border around the chart with some dots on the edge and corner – I can click on these (and hold the left mouse button down) to change the shape and size of the graph. (If you lose this border, simply click on the edge of the chart again).
  • When you select the chart, you are given additional menu items; design – this allows you to amend the style of the chart; layout – this allows you to amend the detail of the chart such as headings, legends, labels etc; format – this allows you to amend the text and other formatting within the graph

I’ll start by amending the design.

  • In the Chart Layouts section under the Design menu – this gives you several predefined layouts to choose from – I chose Layout 9.
  • In the Chart Styles section under the Design menu – this allows you to change the color and format of the bars – I chose style 31;
  • Having amended the design I noticed that there was additional ‘default’ text on the graph – I simply clicked on each text item and amended accordingly

Next I’ll amend the Layout

I’m not going to cover too much in this section as there are a lot of things you can change – suffice to say the best thing you can do is to mess around with each item and see what happens. I’ve decided to add a trend line:

  • In the Analysis section of the Layout menu – this allows you to add different graphical analytical tools – I clicked the down arrow under Trendline and chose Two Period Moving Average

Finally I’ll amend the Format

This is where you can have most fun and really customize the look of your chart.

  • In the shape styles section of the format menu – this allows you to change the background settings of the graph – I chose subtle effect accent 1. You can use the Shape Fill, Shape Outline and Shape Effects tool to further customize the look.
  • In the WordArt Styles section of the format menu – this allows you to change the font of the text in the graph – I choose Gradient fill, accent 4, reflection; again there are other tools in this section that allow you to fully customize the text.


Amending the data using Excel 2007 - the graph automatically updates.
Amending the data using Excel 2007 - the graph automatically updates.
Final updated graph using Excel 2007
Final updated graph using Excel 2007

STEP 4 – changing the concept

I decided that I wanted to directly compare 2010 figures against 2011 figures. So I amended the data table – as I did this the graph automatically updated for my changes. What it now shows is the data for 2010 only. I need to add the 2011 data:

  • If you look at the data table when the graph is selected there are several colored boxes around the data – these represent certain areas such as data, column headings an row headings; by manipulating these boxes you can automatically amend the graph.
  • To extend the graph to include the 2011 data – hover over the corner of the data (blue box) until the cursor turns into a double arrow-left click the corner and extend the box over the 2011 data – I note I also moved the box so that it no longer covered the blank area below the data.
  • The graph automatically adds the new data – the trend line now only represents a trend for 2010 so I clicked on this line and pressed the delete key to remove it;

And there you are – you’ve amended your graph to compare 2010 data v 2011 data.


Conclusion

This is a very simple demonstration of using a bar graph in Microsoft Excel. The graphing tool within Excel is very powerful and can let you compare different data – for example you could plot your page views and earnings


Comments

    0 of 8192 characters used
    Post Comment

    • Kailua-KonaGirl profile image

      June Parker 

      3 years ago from New York

      Great tutorial! I pinned it to my Excel & Word Tutorials Board (https://www.pinterest.com/konagirl/excel-word-tuto...

    • profile image

      Addison 

      6 years ago

      Love it!!!!

    • profile image

      Wooded 

      7 years ago

      This is really helpful. I create graphs quite often and was not aware that it was so easy in Excel. Thanks!

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)