How to Create a Chart with Multiple Ranges of Data-Google Sheets

If you have to analyze financial data, chances are that you will have to analyze multiple ranges of data to find the trends that exist over time. To easily visualize them, you would need to create charts that show multiple trends.

The first thing you need to know is that Google Sheets require you to adjust the data sheets such that a single range of data is in a column, like in this way:

Annual sales data, one column per branch

This is quite unusual, as we often write the data in this way:

Fortunately, the following steps are easier than what a Google search might tell you! Let us now learn how to create charts with multiple columns.

Flip Columns and Rows

You may first need to format the data so Google Sheets can create a proper multi-range chart. To flip columns and rows, do the following steps:

Step 1: Look for an empty space in the spreadsheet, and select a cell in that area.

Annual sales data, one row per branch, more space below

Step 2: At that selected cell, type the following:

=transpose(<range of="" rows="" and="" columns="">)</range>

Annual sales data, space below has =transpose(A1:H5 covering the data — Even if you just type *=transpose(*, you can then select the cells that you want to flip!

Step 3: Press enter.

Annual sales data, one row per branch above, another copy with one column per branch below

You could either hide the original rows or delete the original rows. But, if you delete the original rows, the transposed rows will also be gone! To keep the transposed rows, you can follow our tutorial here.

Create a Chart with Multiple Ranges of Data

For this portion we will learn how to create a line chart with multiple ranges of data in google sheets.

Let us start with the same data set, but with a few adjustments to make it more readable:

Now, here are the steps:

Step 1: Format your data into rows and columns so a chart can be created

The TRANSPOSE function can be very useful here if you want to move columns and rows.

Step 2: Select the data to be included in the chart, with the headings included

Annual sales data, one column per branch highlighted

Step 3: Click Insert in the file menu, then click Chart.

Annual sales data, one column per branch highlighted, Insert clicked, Chart highlighted

‍

Voila! A line chart now appears. We still need to improve it. As you can see, a Chart Editor Tab appears on the right corner.

Annual sales data, one column per branch, with line chart

‍

To change the title, you can do the following:

Step 4: Format your Newly Created Chart in the Chart Editor

‍Click on the text box below the Title text in the Chart Editor tab on the right. Then type the title of the chart.

Chart editor, Customize selected, Chart title selected, Title changed to Annual Sales

‍

‍The chart title is left-aligned, by default. To center it, click on the Align symbol under Title format (that would be the button with four lines and an arrow pointing downward). Then click on the Center symbol.