In this article:

How to Create a Chart with Multiple Ranges of Data in 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:

Annual sales data, one row per branch


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:


Annual sales data, one column per branch


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. 

Chart editor, Customize selected, Chart title selected, Title format, Center align

Your chart title is now centered!

Annual sales chart, title centered, legend on top, no y-axis label

The Chart Editor tab also contains more customizable settings to improve the chart. For instance, you can do this:

Annual sales chart, title centered, legend on bottom, labels complete
As always, feel free to explore the Chart Editor tab to find the best chart design for your data!

Related Articles

-Add Title and Label Legend Google Sheets Chart

-Add Trendline Google Sheets

-Finding the Slope in Google Sheets

-Flipping X and Y Axes

-Line of Best Fit Google Sheets

Schedule a free automation consult
Learn more

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started