Data Visualization in Google Sheets

Last Updated on February 24, 2024 by Jake Sheridan

Want to visualise your data in spreadsheets? In this tutorial you will all about data visualisation in Google Sheets.

Google Sheets Data Visualization

Why Use Google Sheets for Data Visualization?

Google Sheets is an excellent choice for a data visualization tool for your team or project.

Here are some key factors that make Google Sheets the perfect platform for visualizing your data:

Accessibility and Collaboration

Google Sheets is a cloud-based platform, allowing users to access their data from anywhere with an internet connection. Not only that, Google Sheets supports real-time collaboration, allowing multiple users to work on the same dataset simultaneously. This feature is particularly useful for teams working remotely or in different geographical locations.

Ease of Use

Google Sheets provides a user-friendly interface and intuitive tools for creating visualizations. Users with minimal technical experience can quickly learn how to generate charts and graphs, making data more understandable and engaging.

Integration with Google Suite

Google Sheets offers seamless integration with other Google Suite programs like Google Drive, Google Forms, and Google Data Studio. This ecosystem allows for easy data collection, storage, and further advanced analysis and visualization, creating a streamlined workflow from data collection to visualization.

If your team is already subscribed to a Google Workspace plan, choosing Google Sheets is the ideal option if you want to get started with data visualization.

Customization and Visualization Options

Google Sheets offers a wide range of visualization types, including bar charts, line graphs, pie charts, scatter plots, and more. The built-in chart editor also provides recommendations on what chart types to generate based on the type of data you’re working with.

Charts and graphs in Google Sheets can also be highly customized to suit specific needs, including adjusting colors, fonts, and axes.

Basic Terminology and Concepts

When starting your journey in data visualization in Google Sheets, you may be confused about a few terms or concepts.

Let’s go over a few basic concepts commonly encountered when creating graphs and charts in Google Sheets.

  • Range – In the context of Google Sheets data visualization, the range refers to the cells selected to provide data for the chart. A range can be a single row, a single column, or a larger block of cells across multiple rows and columns.
  • Chart Type – This refers to the style of the chart used to visualize the data, such as line charts, bar charts, pie charts, and so on. Each type serves different purposes and represents data in unique ways.
  • Axis – Most charts require at least one axis (X-axis for horizontal and Y-axis for vertical). Axes represent the scale of the data points shown in the chart, with the X-axis typically displaying categories and the Y-axis displaying values.
  • Data Series – A set of related data points that are plotted in a chart. Each series is usually represented by a unique color or pattern and corresponds to a row or column of data in your range. For example, if we were to set up a line chart to compare monthly sales figures for different products, each product’s sales would be represented by different data series.
  • Legend – Data visualizations work best when they’re easy to understand at a glance. Adding a legend helps explain the symbols, colors, or patterns used in the chart to represent data series. For example, adding a legend to a line chart with different colored lines will help users track down the movement of a specific data series.
  • Labels – Charts and graphs may be visual in nature but they certainly require some text elements as well. Labels are used to describe parts of the chart, such as axis labels, chart title, and data labels. Labels are crucial for providing context for the data presented.

Importing and Preparing Data for Visualization

Before creating a new chart in Google Sheets, we’ll need to properly import data into our spreadsheet.

Let’s look into how to import data from different sources into Google Sheets.

Importing Data from Different Sources

Manual Importing

Google Sheets allows you to import existing datasets into any spreadsheet document. For example, you may have a .csv file that you want to visualize using Google Sheets.

Here are the supported file formats you can import into a Google Sheets document.

  • .xls
  • .xlsx
  • .xlsxm
  • .ods
  • .csv
  • .tsv
  • .txt
  • .tab

To begin importing, head to the spreadsheet where you want to import a document.

Next, head to the File menu and click the Import option.

In the Import file pop-up, select the file you wish to import. You can choose to upload a local file such as an Excel document (.xlsx) or a .csv file. Alternatively, you can browse your Google Drive account and select an existing uploaded file.

In this example, we’ll upload a local .csv file containing the dataset we want to visualize in Google Sheets. To do this, we’ll go to the Upload tab and click the Browse button.

In the File picker dialog box, we’ll navigate to the folder containing the data source we want to import. In Windows, click on the file you want to import and click on Open. Click on OK to proceed.

After uploading the selected local file, we’ll be given various options on where to place the imported data– either as a new sheet, replacing the current sheet, or appending data to the current sheet. Other options include creating a new Google Sheets document altogether, replacing data at the selected cell, or replacing the current spreadsheet.

For this example, we’ll choose the option “Replace current sheet”.

By default, Google Sheets will try to detect the right separator to use. However, you can also manually set the separator by adjusting the value under Separator type. Also note that the Convert text to numbers, dates, and formulas option must be checked if you want your numerical data to be imported as numbers and not text.

Once you’ve set the right settings, click on Import data to proceed.

The data from the .csv should now be imported into a Google Sheets document.

Using Functions to Import Data

Google Sheets comes with multiple functions you can use to import data into your spreadsheet. Each of these functions differ from one another by the type of data they are designed to import.

In this section, we’ll go over five Google Sheets functions you can use:

  • IMPORTRANGE
  • IMPORTHTML
  • IMPORTDATA
  • IMPORTXML
  • IMPORTFEED

The IMPORTRANGE function in Google Sheets allows you to import a range of cells from a specified spreadsheet. It’s useful for consolidating data from multiple sheets into a single spreadsheet without manually copying and pasting.

For example, if you manage monthly financial reports in separate Google Sheets for each month, you can use IMPORTRANGE to compile all these reports into a single sheet for an annual overview, making it easier to visualize later into a chart.

Meanwhile, the IMPORTHTML lets you import data from a table or list within an HTML page. It requires the URL of the page and the type of data (table or list) you want to import, along with the index of the table or list on that page.

This function is handy for importing lists and tables from a webpage directly into Google Sheets. In the example above, we were able to use IMPORTHTML to import a table from a Wikipedia page.

The IMPORTDATA is used to import data into Google Sheets from a given URL. It specifically works with CSV or TSV (comma-separated or tab-separated values) files available online.

If there’s a regularly updated dataset published as a CSV file on a website, you can use IMPORTDATA to automatically pull this information into your spreadsheet for real-time analysis or tracking.

Next, the IMPORTXML function fetches data from a structured XML file or XML structured data embedded within an HTML page, using a specified XPath query to locate the data.

This is particularly useful for scraping and analyzing specific information from webpages, such as product details from e-commerce sites or metadata from web documents, that is not neatly organized in tables or lists but can be accessed via XML paths.

In the example above, we used the IMPORTXML function to return the heading from a specific link.

Lastly, the IMPORTFEED is designed to import an RSS or ATOM feed into a Google Sheet, allowing you to fetch feed data like titles, authors, dates, and summaries directly into your spreadsheet.

This function is ideal for tracking updates from news sites, blogs, or any content publishers that provide RSS or ATOM feeds. For instance, a marketing team can use IMPORTFEED to monitor industry news or competitor blog posts for content analysis and strategy planning.

Cleaning and Organizing Data

Now that you’ve imported your data into Google Sheets, you may still need to process your data to ensure that your desired visualization is accurate.

Here are some tips you can use to clean and organize your imported data in Google Sheets.

Remove Duplicates

Removing duplicate entries ensures the accuracy of your data. Duplicates can skew your analysis and lead to incorrect interpretations.

To remove duplicates, we can select our data and click Data > Data cleanup > Remove duplicates.

In the Remove duplicates pop-up, you’ll need to choose which columns to analyze. After selecting the appropriate columns, click Remove duplicates.

Google Sheets will then return a prompt stating how many duplicate rows were removed and how many unique rows remain.

If you prefer to remove duplicates on a case-to-case basis, you can use the Cleanup suggestions tool under Data > Data cleanup.

This tool will scan your table for duplicates and will return all duplicate rows on the Cleanup suggestions pane. You can choose to remove these duplicate rows or ignore them.

Handle Missing Values

When cleaning your data, you’ll need to decide how to handle missing values. You might choose to leave them as is, remove them, or impute them with average values or placeholders. The approach depends on how missing data affects your analysis.

To remove rows with missing data, we can apply a filter to the range to remove all rows where a particular field is blank.

If you want to replace missing values, you have several options. In some cases, it makes sense to replace missing values with a 0 or some other default value. In other cases, you may want to fill in the missing values with the average value of that specific field.

To replace missing values, you can create a new field that uses the ISBLANK formula to replace blank cells with your desired value.

Check for Consistency

Ensure that your data is consistent, especially in terms of naming conventions, date formats, and categorizations. Inconsistent data can cause sorting and filtering issues.

When handling fields with dates, ensure that the column is formatted as a date.

To flag inconsistent data, we can use data validation rules to ensure that a specific range follows a certain data type (boolean, date, text, and so on.)

Alternatively, you can use functions such as ISTEXT or ISNUMBER to retroactively check for the data types present in each field.

Sort Your Data

Sorting your data can help you quickly identify trends and anomalies. It can also make it easier to work with large datasets.

To sort your range, you can either use the built-in Sort sheet and Sort range options (accessible in the Data tab) or through a filter.

6 Data Visualization Techniques in Google Sheets

Here are six useful data visualization techniques to get you started!

1. Bar and Column Charts

Bar and column charts are both used to compare different categories of data visually, but they display information in slightly different orientations. A bar chart presents data horizontally, whereas a column chart displays data vertically.

This difference affects how data is read and can make one more suitable than the other depending on the data’s nature and the comparison’s context.

For instance, a bar chart is ideal for comparing the population sizes of different countries, as the horizontal bars can accommodate longer country names more comfortably.

On the other hand, a column chart might be better suited for showing monthly sales data. In a column chart, vertical columns clearly represent each month’s sales figures and reading the bars from left-to-right is conventional for chronological data.

Column and bar charts can also provide a way to display multiple data series for a side-by-side comparison.

In other cases, it makes sense to combine data series into a single figure. For these situations, we recommend using a stacked bar chart or stacked column chart.

2. Line and Area Charts

Line and Area Charts are closely related, both showing data points connected by lines over time or other continuous data. The key difference lies in the Area Chart’s shading under the line, emphasizing the volume beneath the curve, whereas the Line Chart focuses solely on the trend represented by the line itself.

A Line Chart is perfect for visualizing temperature changes over a year, highlighting trends without the distraction of volume.

With the line chart, we can add more data series, which corresponds to more lines in our final chart.

In contrast, an Area Chart could be used to visualize the cumulative sales over a specific time period. The shaded area below the line emphasizes the cumulative nature of the data.

Similar to bar and column charts, we also have the option to create stacked area charts to display how different categories contribute to the overall metric over time.

In the example above, we used a stacked area chart to show both the overall growth of sales over time and how both domestic and foreign sales contribute to each yearly total.

3. Pie and Donut Charts

Pie and Donut Charts are circular charts divided into slices to illustrate numerical proportions in a dataset, with each slice size corresponding to the quantity it represents. The main difference between them is that Donut Charts feature a central hole, turning the pie into a ring.

In the example above, we used a pie chart to show a breakdown of monthly expenses by different expense types. The pie chart gives the user a quick overview on which type of expenses contribute the most to the total.

Note that pie charts work best when there are relatively fewer components to work with. As the number of categories increase, it may be difficult to visualize their contributions in a single pie chart.

We can also use a donut chart to display the same information. An advantage with the donut chart is that the space in the middle can be filled with additional information.

4. Conditional Formatting and Heatmaps

Google Sheets allows users to set up conditional formatting rules which allow certain cells to be formatted a certain way if it fits a specific condition.

One way to use conditional formatting is creating a rule that applies a color gradient on a range. This creates a heatmap that can be used to spot patterns, trends and outliers.

Heatmaps could be employed to analyze sales data, with darker colors indicating higher demand in specific items or during a certain time period and lighter colors showing lower demand.

To create a heatmap, first select the range you want to convert into a heatmap.

Next, click Format > Conditional Formatting to access the Conditional format rules pane. Click Add another rule to set up a new conditional format rule.

In the Conditional format rules pane, click the Color scale tab.

You can select from several preset color-scale options or you can choose to set up your own by providing colors for the minpoint, midpoint, and maxpoint. Click Done to proceed.

Your selected range should now be converted into a heatmap. In our example, dark red areas show instances of relatively low sales and lighter areas show instances of relatively high sales.

5. Sparkline Chart

Sparkline charts are compact graphics that fit within a cell or text line to provide a simple, inline visual representation of data.

Sparklines are perfect for displaying stock price trends next to each company’s name in a financial report, offering a quick glance at performance without overwhelming detail.

To add a sparkline chart in a cell, we can use the SPARKLINE function.

The basic syntax of the SPARKLINE is as follows:

=SPARKLINE(data, [options])

Let’s look at a simple example of a sparkline chart.

In the example above, we have a table comparing the price trends of three different stocks over four years. Using the SPARKLINE function, we’re able to insert a simple line chart summarizing the general trend of each price.

To create the sparkline graph in cell F2, we used the following formula:

=SPARKLINE(B2:E2,{“charttype”,”line”})

6. Combining Multiple Charts with the Combo Chart

Combo charts allow you to combine two or more chart types into a single visualization.

For example, you may want to use a column chart to represent revenue figures for each quarter and a line chart to depict the growth rate.

Google Sheets includes a built-in combo chart option that allows you to visualize a data source using both lines and bars.

In the example above, we visualized the revenue and net income data as columns and the profit margin data as a trend line. Note that we’ve made the profit margin data series follow a different axis from the prior two series.

Customizing and Enhancing Your Visualizations

Styling and Formatting Options

After creating a new chart in Google Sheets, you can take advantage of various styling and formatting options available. Some of these options improve readability and can help users better interpret the trends and patterns in your data.

Data Series Color and Style

Changing the colors, line styles (for line charts), or fill patterns (for bar/column charts) of your data series can improve visual distinction between different sets of data. This is particularly useful in complex charts with multiple series, helping to quickly draw attention to specific trends or points of interest.

To change the style of a particular data series, right-click on the chart and select the Series option. Under Series, click on the data series you want to change the style of.

In this example, we’ll change the style of our Domestic Sales data series.

You should now see multiple options in the Chart editor that you can modify to change the style of the data series. What options are available will depend on the chart type.

For this example, we’ll change the line color from blue to yellow and include visible points in our line element.

Gridlines and Axes

Adjusting the appearance of gridlines and axes can enhance the chart’s readability. You might choose to lighten gridlines or remove them altogether for a cleaner look, or perhaps adjust the axis scale for better data representation.

We can edit the gridlines by expanding the Gridlines and ticks section in the Chart Editor.

We can access the Vertical axis and Horizontal axis sections to make changes to how are axes are used in the chart. Ensuring the axes are properly scaled and labeled can significantly improve understanding of the chart’s data points.

Chart style options

Modifying the background color of a chart or plot area can help emphasize the data or make the chart align with a specific theme or presentation style. However, it’s important to ensure that these adjustments do not detract from the data’s readability.

You can also set up a specific border color for your chart. This can be helpful if you want to draw attention to a particular chart or to create a clear distinction between spreadsheet data and the chart in question.

Adding Labels, Legends, and Titles

Adding labels, legends, and titles to charts in Google Sheets serves distinct purposes, each enhancing the chart’s clarity, interpretability, and informative value.

Adding Legends

Legends serve as a key to the chart, explaining what various colors, patterns, or symbols within the chart represent. When a chart contains multiple data series or categories, legends help differentiate between them, making the chart easier to interpret.

For instance, in a line chart showing the change in monthly average temperature over time, a legend would clarify which line corresponds to which year.

Adding Labels

Labels are used to directly annotate data points, axes, or categories within the chart. Their primary purpose is to provide specific information, such as numerical values, percentages, or names, directly on the chart, making it easier for viewers to understand the exact value or significance of a particular data point or axis without having to refer back to the dataset.

For example, adding labels to each point in a line chart allows viewers to see the exact value each point in the line represents.

Adding Titles

Titles provide a concise overview of what the chart is about. A well-chosen title helps set the context for the chart’s data, guiding the viewer’s understanding of what they are looking at. Titles can convey the chart’s main message or the specific aspect of the data being highlighted, making it clear at a glance what the chart aims to communicate.

For example, a title like “Monthly Average Temperature (2020 vs 2021)” immediately informs viewers of the chart’s time frame and subject matter.

Interactive Features

Google Sheets offers various features that, while not making charts interactively dynamic in the same way as some advanced visualization tools, can significantly enhance the interactivity and flexibility of data analysis and visualization within the platform.

Here’s how data validation, pivot tables, and slicers can be used to create a more interactive visualization experience:

Data Validation

Data validation in Google Sheets is a feature that allows you to control the type of data or the values that users enter into a cell. You can set up dropdown lists, checkbox options, or specific data criteria (like dates, numbers, and text) to ensure consistency and accuracy in data entry.

With data validation, you can allow users to choose from a list of options that affect the dataset being visualized. The dataset may already be filtered using a function like QUERY or FILTER.

Your chart should now dynamically update based on the selection, effectively allowing users to explore different subsets of the data through a single chart.

Pivot Tables

Pivot tables are a powerful tool in Google Sheets for summarizing, analyzing, exploring, and presenting your data. They allow you to reorganize and group your data in a way that helps you extract useful information and insights without altering your original dataset.

Pivot tables can be directly linked to charts, making your visualizations dynamically update as you manipulate the pivot table.

To create a pivot table from your data, select your dataset and click Insert > Pivot table.

Next, choose whether you want to insert the pivot table in a new sheet or in an existing sheet. For now, let’s add our pivot table into a new sheet.

Using the Pivot table editor, we can choose what fields to include in our pivot table.

We can then use this pivot table as the data source of a new chart.

By changing the fields in our pivot table, we can also quickly generate a new chart to visualize our data.

Slicers

Slicers in Google Sheets are visual filtering tools that you can use with charts. They let you quickly filter the data in a more user-friendly way than traditional filtering, as they provide a simple, clickable interface that can be used to include or exclude data from your visualization or analysis.

When used with charts (especially those generated from pivot tables), slicers enhance interactivity by allowing users to filter what data is displayed in the chart.

To add a slicer, click Data > Add a slicer.

Next, select the column from your dataset that you want to slice with. In our example, let’s select the region column from our revenue dataset.

With the slicer element, we can now have a convenient way to choose which region to focus on and visualize.

Sharing and Publishing Your Visualizations

Once you’ve created charts from your data, you may want to share your work with team members or even publish the chart online.

Google Sheets includes several ways for you to share and publish your chart!

Collaborating on Visualizations

Google Sheets comes built-in with collaborative capabilities that provide a significant advantage for teams looking to streamline their workflows and enhance productivity.

Here’s how Google Sheets facilitates collaboration in data visualization:

Access Controls and Sharing

Sheets provides flexible sharing options, allowing owners to set different permission levels (view, comment, edit) for each collaborator.

This ensures that sensitive data remains protected, while still enabling collaboration among authorized users.

Real-time Collaboration

Google Sheets allows multiple users to access and edit the same spreadsheet simultaneously from different locations. This real-time collaboration ensures that team members can work together on data visualization projects without the need to send files back and forth, reducing the risk of version conflicts or data discrepancies.

Commenting and Chat Features

Users can add comments to specific cells, ranges, or charts, providing feedback, asking questions, or suggesting changes directly within the context of the data or visualization.

There’s also a built-in chat feature for live discussion, enabling team members to communicate and make decisions quickly while working on the spreadsheet.

Integration with Google Drive

Being part of the Google Workspace, Google Sheets integrates seamlessly with Google Drive, making it easy to organize, share, and access spreadsheets alongside other related documents.

With Google Drive, you can share an entire folder of documents and files to specific users. This integration simplifies project management and ensures that all related materials are accessible in a single place.

Publishing and Embedding Charts

Publishing and Embedding Charts

Publishing and embedding charts created in Google Sheets allows your data visualizations to be shared widely or integrated into various digital platforms.

To publish a Google Sheets chart, simply click on the options icon in the upper-right corner of a chart. Select the Publish chart option.

In the dialog box that appears, choose how you want to publish the chart. You can publish it to the web or embed it in a website or blog.

If you’re creating a public link, you can also choose to share an interactive chart or a simple image of the current chart.

You can then copy the link to your clipboard for sharing.

Users who open the link will be redirected to a page containing the published chart.

If you’re embedding the chart, just follow these steps:

  • Select the “Embed” tab and copy the HTML code provided.
  • Access the HTML source code area of your platform. This process varies depending on the content management system or platform you’re using.
  • Paste the copied HTML code into the desired location in your HTML source.
  • Save or publish your changes on the platform to make the embedded chart visible to your audience.