How to make a graph in Excel taking data from multiple sheets

Excel offers us valuable elements that go far beyond a simple spreadsheet, one of them is inserting graphs with different functions. In this post you will learn how to make a graph in excel taking data from several sheets , in a simple way.

Excel is a program that is part of the famous Microsoft Office package, it is basically spreadsheet , similar to those used by accountants before the first computers. As in those days, the Excel spreadsheet can contain accounting data for companies and individuals.

A graph in Excel is a tool that allows us to easily show the values coming from in a group of cells. In this way, all the information that you want to present from a report, statistics, trend, or a data analysis can be explained in a visual way.

What are the types of charts in Excel?

The list of graphs available in Excel is extensive , some types of graphs have subtypes, in addition there are graphs that are only present from a certain version of Office; The main ones that we can mention from this list are the following.

For all versions of Office, it incorporates the same tools for creating graphs and changing or modifying their size (Of columns, lines, circular, rings, type bars, area, with XY or dispersion function, bubbles, for quotes, surface and radial).  Office 2013  includes combined chart (columns with lines).

Office 2016 version onwards , they added graphs (of rectangles, solar projection, with histogram, of boxes and whiskers, of waterfall and funnel). And Map graph.

If you want to know more details about the types of graphs that Office has, you can  simply open Excel and look at the insert tab, then right in the center, you find the list of Graph tools. Let’s move on to creating a graph in excel taking the data from several sheets.

How to make a graph in Excel taking data from several sheets?

  • The first step is to previously have the data that will be used for it. This action of linking one or more cells to a graph is called consolidating, so that the graph can automatically change its information.
  • With this in mind, we will use multiple sales tables from a multi-site company . These will have a column with the title Headquarters and another called Sales, the first will contain the name of the headquarters and the second the amount of annual sales (one for each year), and they will be located on different sheets.
  • We select the base cells for the chart (the oldest year), we press the “Insert” option .
  • We press the button of the desired type of graph, such as “Column in 2D”.
  • The graphic is immediately inserted in the center of the screen, which we can drag to the desired position.
  • We click on the graph, go to the option “Design> Select data> Add”.
  • In the “Modify series” window, we delete the content of the “Series values” field.
  • We click on the button next to it and on the other sheet we select the cells, we will only take the numerical ones (we ignore the titles and locations).
  • We delete the text from the “Series name” field in the “Modify series” window.
  • Click on the cell selection button next to it.
  • We select the title cell of the sales column.
  • Now we click on “OK” to return to the “Select data source” window.

The graphs are easy to read, so they are a very useful resource in Excel. There is a great variety (more than 18 different types) that we can insert into a spreadsheet. Learning to do them is undoubtedly very beneficial for our work and daily life .


Leave a Comment