How to Prepare Data for Charts in Google Sheets

Published on

Creating charts in Google Sheets is straightforward if your data is arranged properly. Most chart types require specific data structures. In this guide, you’ll learn how to prepare data for various popular charts in Google Sheets.

Additionally, you’ll receive a free chart template with sample data tailored for these chart types.

Introduction

Before creating charts in Google Sheets, ask yourself this important question: What would you like to show?

To create an effective chart in Google Sheets (or any other application), you must thoroughly understand your data. Consider whether you’re highlighting comparisons, distributions, or relationships. If you’re unsure, refer to this tutorial: Choose Suitable Chart for Your Spreadsheet Data – How To.

Once you’ve identified your goal, review the chart types below to see how to prepare data for each.

1. Line Chart

Use a Line chart to visualize trends in data over time intervals.

Line chart example showcasing data trends over time

How to Prepare Data for Line Charts in Google Sheets

Prepare your data as shown below:

  • The first column contains data for the x-axis (e.g., dates, days, months, years, or time intervals).
  • Subsequent columns contain data for the y-axis.
Preparing sample data for creating line charts in Google Sheets

Creating the Line Chart

  1. Select the entire dataset (e.g., A1:D9).
  2. Go to Insert > Chart > Line Chart.
  3. In the Chart Editor, ensure the following options are checked:
    • Use Row 1 as Headers
    • Use Column A as Labels

2. Area Chart

The Area chart is similar to the Line chart but emphasizes magnitude by filling the space below the line with color.

Example of an area graph created in Google Sheets

Preparing Data for Area Charts

The data format for Area charts is identical to Line charts.

Creating the Area Chart

Follow the steps for Line charts, but choose “Area Chart” in the Chart Editor.

3. Bar Chart

Bar charts represent categorical data with horizontal rectangular bars proportional to the values they represent. Use Bar charts to compare data among categories.

Example of a bar graph in Google Sheets

How to Prepare Data for Bar Charts in Google Sheets

  • Column A: Categories (x-axis labels).
  • Subsequent columns: Values (y-axis).
Preparing data for a bar chart in Google Sheets

Creating a Bar Chart

  1. Select your dataset.
  2. Go to Insert > Chart > Bar Chart.
  3. Ensure the following options are checked:
    • Use Row 1 as Headers
    • Use Column A as Labels

4. Column Chart

Column charts are similar to Bar charts but with vertical bars.

Example of a column graph in Google Sheets

How to Prepare Data for Column Charts in Google Sheets

The data format is identical to Bar charts.

Creating a Column Chart

  1. Highlight the dataset (e.g., A1:D11).
  2. Go to Insert > Chart > Column Chart.

5. Pie Chart

Pie charts visualize numerical proportions as slices of a circle.

Example of a pie graph in Google Sheets

How to Prepare Data for Pie Charts

  • Column A: Category names (e.g., “United States,” “Russia”).
  • Column B: Corresponding values (e.g., population, sales).
Data structure for creating pie charts in Google Sheets

Creating a Pie Chart

  1. Highlight the range (e.g., A1:B11).
  2. Go to Insert > Chart > Pie Chart.
  3. Check:
    • Use Row 1 as Headers
    • Use Column A as Labels

6. Scatter Chart

Scatter charts help identify relationships between two numerical variables.

Example of a scatter chart in Google Sheets

How to Prepare Data for Scatter Charts

  • Column A: x-axis values (independent variable).
  • Column B: y-axis values (dependent variable).
Preparing data for a scatter chart in Google Sheets

Creating a Scatter Chart

  1. Select your dataset.
  2. Go to Insert > Chart > Scatter Chart.
  3. Enable:
    • Use Row 1 as Headers
    • Use Column A as Labels

7. Bubble Chart

Bubble charts are three-dimensional, adding a “z” variable (bubble size) to the Scatter chart’s “x” and “y.”

Example of a bubble chart in Google Sheets

How to Prepare Data for Bubble Charts in Google Sheets

  • Column A: x-axis values.
  • Column B: y-axis values.
  • Column C: Categories (optional) that determine the color. Include this column and leave “-” in each row if you want all bubbles to have the same color.
  • Column D: Bubble size.
Structured data for bubble charts in Google Sheets

Creating a Bubble Chart

Select the dataset and go to Insert > Chart > Bubble Chart. Enable “Use Row 1 as Headers.”

8. Geo Chart

Geo charts display data on a map, such as sales by region or population by country.

Example of a geo chart in Google Sheets

How to Prepare Data for Geo Charts

  • Column A: Locations (e.g., country/state names or ISO codes).
  • Column B: Values (e.g., sales or percentages).
Data preparation for a geo chart in Google Sheets

Creating a Geo Chart

  1. Select the range and go to Insert > Chart > Geo Chart.
  2. Enable “Use Row 1 as Headers.”
  3. Under the Customize tab, select the desired region by clicking Geo.

9. Waterfall Chart

Waterfall charts visualize how initial values are affected by positive or negative changes.

Example of a waterfall chart in Google Sheets

How to Prepare Data for Waterfall Charts

  • Column A: Categories (e.g., salary, expenses).
  • Column B: Values (+ve or -ve).
Data preparation for a waterfall chart in Google Sheets

Creating a Waterfall Chart

  1. Select the range (e.g., A1:B8).
  2. Go to Insert > Chart > Waterfall Chart.

10. Histogram

Histograms display data distribution across bins (ranges).

Histogram chart example in Google Sheets

How to Prepare Data for Histograms

  • Column A: Labels (optional).
  • Column B: Numerical data.
Data structure for creating a histogram in Google Sheets

Creating a Histogram Chart

Select only Column B, as Column A is not required for the histogram chart. Then insert a chart by going to Insert > Chart > Histogram.

Customize Bin Size for Histogram Graph in Google Sheets:

  • Check the box for “Use row 1 as headers”.
  • In the chart editor’s Customization tab, adjust the Bin/Bucket size as needed by selecting the “Histogram” drop-down.

11. Radar Chart

Radar charts (Spider charts) compare variables across multiple categories.

Example of a radar chart in Google Sheets

How to Prepare Data for Radar Charts

  • Column A: Categories.
  • Column B/C: Values for comparison.
Preparing data for a radar chart in Google Sheets

Creating a Radar Chart

Go to Insert > Chart > Radar Chart.

12. Gauge Chart

Gauge charts resemble speedometers, displaying performance within a range.

Example of a gauge chart in Google Sheets

How to Prepare Data for Gauge Charts

  • Column A: Labels.
  • Column B: Values.
Data setup for a gauge chart in Google Sheets

Creating a Gauge Chart

Data preparation for a Gauge chart is simple, but chart customization is key to visually interpreting the performance score.

As usual, select the entire data range (e.g., A1:B1) and go to the Insert menu, then click on Chart. From there, select Gauge chart.

Navigate to the Customize tab in the chart editor. Click on Gauge and enter the minimum and maximum values for each gauge level that falls within the gauge range.

13. Candlestick Chart

Candlestick charts are used for financial data, such as stock price movements.

Candlestick chart example in Google Sheets

How to Prepare Data for Candlestick Charts

Enter historical stock data in columns A to E, with dates in column A (format as text by clicking Format > Number > Text) and the low, open, close, and high values in the next columns.

    Proper data formatting for a candlestick chart in Google Sheets

    If you use the GOOGLEFINANCE function, such as:

    =GOOGLEFINANCE("ticker", "all", "01/08/2018", "31/08/2018")

    You will get data in the column order: Date, Open, High, Low, Close, and Volume, where the first column contains timestamps. However, the date must be formatted as text. Therefore, you should create a new range from this data with the required format.

    Creating a Candlestick Chart

    • Click Insert > Chart and select Candlestick Chart.
    • Make sure to check the option Use row 1 as headers in the Setup tab.

    14. Organizational Chart

    Organizational charts display employee hierarchy.

    Example of an organization chart in Google Sheets

    How to Prepare Data for Organizational Charts

    • Column A: Employee names.
    • Column B: Reporting officers.
    Preparing data for an organization chart in Google Sheets

    Creating an Organizational Chart

    Go to Insert > Chart > Organizational Chart.

    15. Treemap Chart

    The Treemap chart is used to display hierarchical data as nested rectangles, representing a tree structure.

    Treemapping is particularly effective for comparing proportions within a hierarchy. A common example is showing patient wait times across various departments in a clinic.

    Example of a Treemap chart in Google Sheets

    Each category in a Treemap is represented with a unique color, making it easy to visualize data. Larger rectangles indicate longer wait times, while smaller ones represent shorter wait times.

    How to Prepare Data for Treemap Charts

    The data structure for a Treemap chart in Google Sheets typically includes hierarchical data arranged in three columns:

    Data setup for a Treemap chart in Google Sheets
    1. Hierarchy or Categories:
      • In column A, enter the hierarchical levels, such as categories and subcategories.
      • For example, “Department” as the category and “General Medicine” or “General Surgery” as subcategories.
    2. Parent Category:
      • In column B, specify the parent category for each subcategory.
    3. Numeric Values:
      • In column C, enter numeric values corresponding to each subcategory to determine the size of the rectangles.
      • Include the total of the subcategories against the parent category in the same column.

    The sample data should show at least one category and its associated subcategories. Repeat the structure to include more categories and subcategories.

    How to Create a Treemap Chart

    1. Select the data range, such as A1:C, formatted as described above.
    2. Go to the Insert menu and choose Chart.
    3. From the Chart Editor, select Treemap as the chart type.

    The chart will automatically visualize your hierarchical data.

    16. Timeline Chart

    The Timeline Chart (previously known as the Annotated Timeline Chart) in Google Sheets visually represents data points in chronological order.

    Example of a Timeline chart in Google Sheets

    This chart includes a zoom option and a date range selector, allowing users to focus on specific portions of the timeline.

    You can add important milestones to the chart as annotations. Previously, these annotations were fully functional. However, currently, they only appear as letters (alphabets) on the chart, while the note section remains blank. This issue could be a bug or an intentional change by the developers.

    How to Prepare Data for Timeline Charts in Google Sheets

    To create a Timeline Chart, you can use a data structure similar to that of a line chart. However, you can include annotations between each y-series.

    Preparing the data for a Timeline chart in Google Sheets

    For example:

    • Enter the categories (x-axis) in Column A.
    • Add corresponding y-axis values in Columns B and D.
    • Use Columns C and E for annotations related to the respective data points.

    How to Create a Timeline Chart

    1. Select the data range you prepared.
    2. Go to the Insert menu, then choose Chart, and select Timeline.
    3. In the Chart Editor, ensure the options Use row 1 as headers and Column A as labels are selected, similar to creating a line chart.

    17. Table Chart

    The Table Chart is a versatile tool for creating visually appealing and interactive dashboards in Google Sheets. It transforms long lists of data into a paginated and sortable chart, making it easier to display and navigate large datasets within a limited space.

    Example of a Table chart in Google Sheets

    This chart provides the following features:

    • Sortable columns: Click on a column header to sort the data in ascending or descending order.
    • Page navigation: Easily navigate through pages of data using the controls at the bottom of the chart.

    How to Prepare Data for Table Charts in Google Sheets

    There are no specific requirements for data preparation. You can use your existing table or dataset as is.

    How to Create a Table Chart

    1. Select your table or dataset.
    2. Go to the Insert menu, then select Chart.
    3. In the Chart Editor, choose Table Chart from the chart type options.

    The Table Chart is an excellent choice for showcasing large datasets in a compact and interactive manner, especially in dashboards. It enhances readability and usability without the need for additional formatting.

    Sample Sheet with Structured Data and All Charts

    Can you provide a sheet containing the above charts and formatting?

    Of course! Here it is: All Charts

    The sheet includes an index page for easy navigation between the charts and their corresponding data.

    Additional Reading: Create a Gantt Chart Using Sparkline in Google Sheets.

    Prashanth KV
    Prashanth KV
    Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

    Cycle Highlights in Google Sheets – Rotate Highlights Daily

    Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

    Filter Rows Containing Multiple Selected Values in Google Sheets

    This tutorial explains how to filter rows in a column containing multiple selected drop-down...

    Two-Way Lookup with XLOOKUP in Google Sheets

    When you need to look up one search key vertically and another horizontally, you...

    How to Filter by Total in Google Sheets Pivot Tables

    Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

    More like this

    Cycle Highlights in Google Sheets – Rotate Highlights Daily

    Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

    Filter Rows Containing Multiple Selected Values in Google Sheets

    This tutorial explains how to filter rows in a column containing multiple selected drop-down...

    Two-Way Lookup with XLOOKUP in Google Sheets

    When you need to look up one search key vertically and another horizontally, you...

    5 COMMENTS

    1. I did the same thing; changed my sheets to the UK locale and it worked. When I switched it back I got the same error as before. It did not continue to work.

    2. I tried the candlestick formula and it would not work. I then copied and pasted the formula from your spreadsheet that has the working candlestick graph, and it still will not work.

      I also copied the formula (without the arrayformula portion of the formula, and did the ‘shift-ctrl-enter’ move, and while it autocompleted the ‘arrayformula with curly brackets’, it still did not produce the table.

      The error I get is ” Function Array_ROW parameter 2 has mismatched row size. Expected 2. Actual 1.”

        • No problem. Here is the link. Thanks for replying so quickly.

          Note: Link removed by the admin.

          I’m not an expert with Google Sheets, but is it something simple like missing an add-on? This is such a strange problem since everyone basically has the same version of google sheets, unlike Excel which has quite a few versions.

      • Hi,

        I think it has something to do with your spreadsheet’s regional setting.

        I removed the formula in your sheet and then changed the locale (File menu) to the UK to match with my Sheet’s settings. Then again applied the formula. It started working.

        I have converted back the spreadsheet locale to the US and still, I think, it’s working.

        Thanks.

    LEAVE A REPLY

    Please enter your comment!
    Please enter your name here

    This site uses Akismet to reduce spam. Learn how your comment data is processed.