Google Sheets (21) – Explore

In this post, we’re going to look at the “Explore” tool, which looks at your data and automatically produces charts and analysis from it. Plus, it’s also possible to ask natural-sounding questions to interrogate your data. The beauty of this is that the user doesn’t have to know how to make charts or how to write formulas to analyse their data.

Here, we have a set of data showing costs involved for different products.

In the bottom-right of the screen you’ll see the Explore icon. Sometimes you will see this highlighted in green or sometimes it will be in grey. If it’s in grey, it means that you will have to select the data you want Explore to analyse. If it’s green, it has already noticed there is data on the sheet and has a data range already selected, but you can still go in and manually select the range you want.

If necessary select the data on your sheet, then click on the Explore icon.

The Explore sidebar will open and it will contain automatic analysis of your data. It will contain common calculations, such as SUM and AVERAGE. Below it will give you the opportunity to use the Google AI and ask it questions about the data. It also gives you possible questions you may want to answer. It allows you to add row colours with Alternating colours, without having to go into the menus.

Then it will display various charts and further analysis of your data.

Insert a chart

If you find a chart that you want, hover the mouse over it and you’ll see two icons. The first lets you insert the chart to your sheet.

Here, it adds the chart and you’ve not had to set up the chart manually.

The second one allows you to view the chart (and the others in side bar) in full view. If you like the look of it, just click “Insert chart”.

Editing the data range

At the top of the sidebar you’ll see the data range that the charts are made from. To edit it, just click “Edit”.

Then either edit the range, or click the grid to select a new one.

You can also state which rows or columns are the headers and which should be included in the data part of the chart.

Click “1 row” and select one of the options.

Asking questions about your data

In the answers section, in theory it’s possible to type in a natural sounding question about your data in the box and “Explore” will return a chart or some analysis based on that question and your data. In practice, sometimes it works and it’s wonderful see such simple input produce sometimes quite complicated analysis without having to know formulas or how to set up a chart. But sometimes, it doesn’t understand the question or returns something different. AI will only get better over time and will be accurate.

Under the box, you will see question suggestions. Let’s look at that first.

Here, I’ve clicked on the first suggestion, “Average of Labour”. It states the question then underneath as you can see, it’s correctly found the average of the labour costs in my table.

Under the result, you can also see what formula it’s used to get the result, just click “See formula”.

We can see it’s used the AVERAGE function. You can drag or copy it into your sheet.

This time let’s type in our own question. I’ve typed it into the box and it’s found which product has the highest total cost, plus it states that cost. You may have noticed that the original question had a typo but it still found the correct answer.

Let’s look at the formula it used this time. It’s used a QUERY function to find the answer, which for the average person is way beyond their spreadsheet skills. See my post of the QUERY function, if you’d like to learn more.

As I mentioned above, sometimes it struggles with what you want. I typed in “highest labour cost” and it wasn’t sure what I wanted, but if we look in the suggestions, it does actually suggest a correct possibility.

Clicking on “Top labour” finds the result I was after.

Quickly formatting your data

In the formatting section, you can colour your rows like you can via choosing “Alternating colours” from the Format menu, see this post for details. Click on the set of colours you want.