In this article we will go in depth on data visualization in google sheets and show you how you can create charts and dashboards. Simply follow the steps below.
You can simply select the data through click-and-drag method, as shown in the animation below.
data visualization with google sheets using charts, select data to visualize
You can find the Insert option along the main menu.
data visualization with google sheets using charts, click insert, select chart
The Chart editor sidebar will appear on the right side of Google Sheets. By default, the Setup tab is selected.
The first drop-down box in the sidebar is labeled Chart type. Click it.
data visualization with google sheets using charts, select chart type
Select the chart type that is best for the data you are presenting.
data visualization with google sheets using charts, chart type replaced
Important in visualizing data using charts is to label them properly so the user can easily analyze it. To do so, click the Customize tab in the Chart editor sidebar.
data visualization in google sheets, customize chart via chart editor
Click Chart & axis titles.
data visualization in google sheets, set chart title
By default, the options are set for the Chart title. Type the title of the chart in the textbox below the label Title text.
data visualization in google sheets, chart title changed
Click the drop-down box, then select the Horizontal axis title for the label along the x-axis and the Vertical axis title for the label along the y-axis.
data visualization in google sheets, chart and axes titles changed
You can use more of the settings available in the Chart editor, such as changing the color of the bars, the location of the legend, etc. You can also add or remove entries through the Setup tab.
After some customization actions, we have the following chart:
data visualization in google sheets, chart customized
To move the chart to its intended position, left click it then drag it while holding the left-click on your mouse.
To resize the chart, click on one of the small blue squares in its corners, then drag it until you get the desired size.
data visualization in google sheets, chart position modified
These are just the general steps in visualizing data in Google Sheets using the charts.
You can not only create a single chart in Google Sheets, but also a dashboard! If you already have a layout in mind, easily implement it in Google Sheets by following these steps:
A dashboard needs a consistent source of data to analyze and visualize. You can use our large library of tutorials available here. Search the platform you want to import, then follow the steps. It’s that simple!
You can store the raw imported data in a different sheet and then import portions of it using the following features and functions:
And many more! You can check our library of tutorials for specific tutorials.
For our example, we want to create a dashboard for a one month record of sales of our products. The basic data for the products are stored in the sheet named items. The aggregate daily sales data are stored in another sheet named dataperday.
data visualization google sheets, data stored in different sheets
Some of the data are summarized in the dashboard sheet:
We intentionally left spaces here because we will insert charts and KPIs here later.
Analysis means processing the raw data that you have to get meaningful KPIs. There are several KPIs you can use, and they all depend on the type of dashboard you want to use. Some of them are the following:
You can check our guide to different dashboards here for more details.
For our dashboards, we want to calculate the average order value for each day and for each item. We add a column for each of the two tables in the dashboard, giving us the AOV each day and the AOV for each product.
data visualization google sheets, data summarized in dashboard
Using the steps in the previous section, visualize the data by using the built-in charts in Google Sheets. You can visualize using either the built-in charts or the SPARKLINE function that sets mini-charts within cells.
For our example sheet, we plot the daily sales, profit, and AOV for the last 30 days.
google sheets data visualization, dashboard in google sheets
We now have a simple dashboard that imports data and visualizes it!
This article seems straightforward, but you may find difficulty in importing data from different platforms.Your Google Sheets may also load more slowly because of it! If that is your concern, how about you try Lido? It is essentially Google Sheets but with built-in ability to import from a wide variety of eCommerce and marketing platforms.
Data visualization using Lido
You don’t need to even touch any code at all!
We hope this article has helped you and given you a better understanding of how to use data visualization in Google Sheets. You might also like our articles on how to use data validation in Google Sheets and how to search in Google Sheets.
To optimize your workflow, we recommend reading our guide on how to send an email from Google Sheets based on a cell value and trying our software for payment due reminders.
