Creating Charts and Visualisations in Google Sheets to Analyse Data

Charts and visualisations are powerful tools for transforming raw data into meaningful insights. Google Sheets makes it easy to create a wide range of charts, from simple bar graphs to more advanced visualisations like scatter plots and pie charts. This guide will walk you through how to create and customise charts in Google Sheets to better understand and analyse your data.

If the numbers you want to chart live in Google Analytics, first export your GA4 data to Google Sheets, then build your charts here.

Step-by-Step Guide to Creating Charts in Google Sheets

1. Organise Your Data Before creating a chart, ensure your data is well-organised in rows and columns. Each column should represent a different data category, such as “Month,” “Sales,” or “Product Type.” For example:MonthSalesProfitJan1000200Feb1200300Mar1500350Having clear and structured data makes it easier to create accurate and informative charts.
2. Select the Data Range To begin creating a chart, you need to select the data you want to visualise: Click and drag to select the cells containing your data.
3. Ensure that both the labels (e.g., “Month,” “Sales”) and the data points are included in the selection.
4. Insert a Chart Once you’ve selected your data, it’s time to insert a chart:Go to the Insert menu at the top of the screen.Click on Chart. This will insert a default chart into your Google Sheet.
Google Sheets will automatically generate a chart based on the data you selected, but you can customise the type and style of the chart to suit your needs.
5. Choose the Right Chart Type The default chart type may not always be the best fit for your data. To change the chart type:Click on the chart to open the Chart Editor on the right-hand side.In the Setup tab, under Chart type, you can choose from a variety of chart types:Line Chart: Ideal for showing trends over time, such as sales growth.Bar Chart: Useful for comparing different categories side by side, like product sales across regions.Pie Chart: Best for displaying proportions, such as market share distribution.Scatter Plot: Helps identify relationships between two variables, such as price and sales volume.
Select the chart type that best represents your data and the insights you want to communicate.
6. Customise Your Chart To make your chart more meaningful and visually appealing, you can customise it in several ways: Change Axis Labels: In the Chart Editor, go to the Customise tab and click Chart & axis titles. Here, you can edit the chart title, as well as the labels for the X and Y axes.
7. Modify Colours and Fonts: Under Series, you can change the colours of the data points, bars, or lines in your chart. You can also adjust font styles, text sizes, and alignment to match your presentation or branding.
8. Add Data Labels: If you want the actual values to appear on your chart, go to Series and check the box for Data labels. This makes the chart easier to read at a glance.
9. Adjust the Chart Layout You can adjust the overall layout of your chart to make it easier to interpret: Legend Position: Move the legend to the top, bottom, or side of the chart to make the most of your available space.
10. Gridlines and Ticks: In the Gridlines and ticks section, you can control how many gridlines appear in the background of your chart. Gridlines help viewers understand the scale and compare data points more easily.

Chart Types in Google Sheets, Explained

Google Sheets offers dozens of chart types, but you will use a handful most of the time. Here is when to reach for each:

Bar and column charts

Best for comparing values across categories – sales by product, visitors by page, votes by option. Columns (vertical) suit a few categories; bars (horizontal) suit long labels or many categories.

Line charts

Best for showing a trend over time, such as monthly revenue or daily traffic. Use one line per series to compare a few things at once.

Pie and doughnut charts

Best for parts of a whole – market share, a budget split – but only with a few slices. Too many segments become impossible to read, so a bar chart is often clearer.

Scatter charts

Best for showing the relationship between two numbers, such as price against sales. Add a trendline to see whether they move together.

Combo charts

Best for two related measures on different scales, such as revenue (columns) and conversion rate (line) on one chart.

Advanced Charting Features in Google Sheets

1. Create a Combo Chart A Combo Chart allows you to combine two different chart types into one, such as a bar chart and a line chart. This is useful when you want to compare two related datasets that have different units of measurement or are best visualised using different chart styles. To create a combo chart, select Combo Chart from the chart type options in the Chart Editor.
2. In the Series section, you can assign one data set to the bars and another to the line, making it easy to compare two variables like revenue and profit.
3. Use a Scatter Plot for Correlation Analysis If you’re trying to identify correlations or relationships between two sets of data, a Scatter Plot is an excellent tool. It displays individual data points and shows trends, such as whether higher prices correlate with higher sales: Select your data and choose Scatter Plot from the chart options.
4. In the Customise tab, you can add a Trendline to the scatter plot, which visually indicates the correlation between the variables.
5. Add a Secondary Axis When your chart compares two data series that have different scales, adding a Secondary Axis can help improve clarity. For example, if you’re charting both website traffic (in thousands) and conversion rates (in percentages), a secondary axis ensures both data sets are accurately represented:In the Chart Editor, go to Series, select the data series you want to add to the secondary axis, and check the box for Axis > Right axis.
This allows both data sets to be displayed on the same chart, even if they have different units of measurement.
6. Embed Dynamic Charts into Reports You can embed dynamic charts from Google Sheets into presentations or Google Docs, allowing your visualisations to update in real time as your data changes. To do this: Click on the chart and select the three vertical dots in the top-right corner.
7. Choose Copy chart.
8. Paste it into a Google Doc or Google Slides presentation. The chart will automatically update when the underlying data in your Google Sheet changes.

Sparklines and Heatmaps for Quick Visuals

Not every insight needs a full chart. For compact, at-a-glance visuals inside your table, two features are especially handy:

• Sparklines: tiny in-cell charts that show a trend without taking up space. Type =SPARKLINE(B2:M2) for a mini line chart of a row, or =SPARKLINE(B2:M2, {"charttype","column"}) for a small bar version. They are ideal for showing each product or keyword’s trend right next to its numbers.
• Heatmaps: colour a range so high and low values stand out instantly. The quickest route is a conditional formatting colour scale – our guide to conditional formatting in Google Sheets walks through the colour rules step by step.

Both work well on a dashboard tab, letting you scan dozens of rows and spot the outliers before you build a single chart.

Visualising Data for Better Insights

Creating charts and visualisations in Google Sheets is a powerful way to turn data into actionable insights. Whether you’re tracking sales trends, analysing survey results, or comparing campaign performance, charts help communicate the story behind the data more effectively. By choosing the right chart type, customising it for clarity, and using advanced features like combo charts and secondary axes, you can transform your raw data into valuable visual insights.

Mastering these techniques will not only make your reports and presentations more impactful but will also help you and your team make data-driven decisions with ease.