How to Use Gemini in Google Sheets: Basic and Advanced Use Cases

Google Gemini can help you create content and calculations for Google Sheets. Gemini can serve as a smart list generator that prepares the content in a table for you to export to Google Sheets, or its responses can help you craft formulas and functions. These uses of Gemini provide capabilities that extend beyond the standard auto fill And improvement capabilities of sheets.

SEE: ChatGPT vs. Google Gemini: An In-Depth Comparison (TechRepublic)

For the actions below, you must be signed in to an active Google Account authorized to use both Google Sheets And Google Gemini. If you use a Google Workspace account for work or school, you may need to contact your administrator to request access to Gemini.

Once you have access, open Gemini in any modern browser to get started. Enter a natural language prompt, and the Gemini will respond. Reactions may vary – two people entering the same signal may not get the same reaction.

How to get content from Gemini for Google Sheets

with GeminiYou can prompt the system for various comparisons and lists. For example, a request to compare two or more products might generate a table with one column for each product and each row with a different comparison feature. You can also prompt Gemini to list people, places, or things. Since Gemini accepts sequence, you can indicate first:

List the 10 cities in the United States with the most annual rainfall.

Then, after receiving the initial response, prompt again:

Add a column for the population.

The tables that Gemini can generate in response vary greatly compared to the traditional autocomplete sequences available in Google Sheets with the tool. Autocomplete | Enable AutoComplete option.

In Google Sheets, you enter a predictable series of alphabetical letters, numbers, days of the week, months, or other standard patterns in two or more cells. Then select the cells and select the corner of the box and expand it to cover the entire range of cells to be filled. For example, if you type Mon in one cell and Tue in the next cell, you can select those two cells and then the other five cells with the remaining corresponding three-character descriptions of the days of the week in English. You can drag the point to fill it.

In contrast, the tables you might prompt Gemini to create can include a much wider range of indexable data.

SEE: Google Gemini Cheat Sheet: What is Google Gemini and how does it work?

Select Export to Sheets

When a response includes a table, select the Export to Sheets option at the bottom-right corner of the table. This exports the table to a new Google Sheet. The system will use your prompt as both the name of the newly created file and the starting sheet within the file. The contents of the table will be placed in the cells of the sheet, with the column headings in row A.

Hint “in a table”

Gemini will often automatically format comparisons and lists into a table. When the system doesn’t do this, add the phrase “in the table” to your request. The prompt listed above would be as follows:

In a table, list the 20 cities in the United States with the most annual rainfall.

View other drafts or reset chat

Sometimes Gemini provides either a partial response or a strangely formatted response. For example, try the prompt:

List all 50 U.S. states sorted by population.

Gemini returns a table of 40 states, which is an incomplete response. In the second example, a prompt for:

List of elements by name and atomic weight.

In response, Gemini produced a list, not a table, formatted as a code snippet. When a response is not what you expected, select the View Other Drafts button to access an alternative draft. Sometimes, one of those drafts will be formatted as a table instead of a code snippet.

Another option when this type of error occurs is to start a new chat and then try again with a retyped prompt. This may return a more complete or better formatted response.

SEE: How to Use Google Gemini: A Comprehensive Guide

How to Get Help from Gemini with Google Sheets Calculations

You can inspire Gemini to explain Google Sheets formulas and functions and provide examples. Unlike Google Sheets help pages, which provide a static set of descriptions and examples, you can ask Gemini for different examples and a detailed explanation of how a feature works.

For example, if you want to know a little more about one of these Google adds new functions to Sheets In March 2023, you can indicate:

How do I use the WRAPCOLS function in a Google Sheet? Can you give an example of how I might use it to group a list of employee names into groups of 4 people each?

Gemini’s response includes, in order:

• Description of how the function is used.
• A sample formula for grouping a list into groups of four.
• An example with demo names.
• Additional tips, such as how to handle extra rows or how to combine WRAPCOLS with other functions.

To explore additional Google Sheets functions, you can prompt:

Are there other Google Sheets functions that achieve something similar?

Gemini offers some other functions that help with related aspects of your initial work.

Additionally, Gemini can help you create the exact formula you need, but it may be a process of a bit of trial and error. For example, let’s say you wanted to analyze weather data to identify how many days the wind was predominantly from the west. First, try the initial detailed signal.

I have a Google Sheet with data in cells F2 through F367. The data is all numbers, from 0 to 359, and represents wind direction, with 0 being the north and 270 being the west. I would like a formula to indicate the percentage of days that the wind is from the west, where the value is anywhere between 240 and 270 degrees. Can you provide that?

The response returned a =COUNTIF formula which caused an error. After a quick review of the function, prompt again, essentially asking Gemini to try again:

I think the range indicator portion needs to be different. Maybe a logical AND not a text field?

This time, the response included the =COUNTIFS formula. Select the Copy Code button, switch to Google Sheets, navigate to the desired destination cell, and then click Edit | Select Paste to add the formula. This time, with a few changes, the code snippet worked as intended.

And that’s a good snapshot of how you have to work with a Gemini. If the initial response meets your needs, that’s great! But always take the time to check the results for accuracy and be prepared to revisit and prompt differently to generate a more relevant, useful, or accurate response.

Can Gemini create tables in Google Sheets?

Yes! As described above, if the Gemini app creates a table, it comes with an Export to Sheets button that opens the table in a spreadsheet.

Alternatively, while in Google Sheets, you can click the ‘Ask Gemini’ button at the top right of the screen, which appears as a white flash in a blue circle. A panel will then open with some tips, including a ‘Create Table’ button. After clicking it, you can replace the example text with your own text to create your own prompt. You can also write a prompt from scratch without using any suggestions.

By pressing the small diagonal arrow below the table, Gemini will generate it and move it into the spreadsheet.

How do I access Gemini in Google Sheets?

If you’re logged in to a Google account with Gemini enabled, you’ll see an ‘Ask Gemini’ button next to your profile picture at the top-right of the screen. Clicking will open a panel with a box where you can type a prompt related to your sheet.

Is Gemini available in all Google Sheets versions?

Gemini is a paid add-on for anyone with a Google Workspace account.

If you have a business version, it costs $24 per user per month if you pay monthly, and $20 per user per month if you pay annually.

If you have the Enterprise edition, it costs $36 per user per month if you pay monthly, and $30 per user per month if you pay annually.

This is paid on top of the price of your Google Workspace account:

• Business Starter: $7.20 per user, per month.
• Business Standard: $14.40 per user, per month.
• Business Plus: $21.60 per user, per month.
• Enterprise: Price from individual to organization

SEE: Gmail vs Google Workspace: Key differences for users and businesses

Can Gemini convert Google Docs content to Google Sheets?

No, Gemini cannot transfer data directly to Google Workspace apps. If you have a table in Google Doc and you want to move it to a Sheet, you can highlight it and copy it, click a cell in the Sheet, and then paste it. If the pasted data appears in one column, click ‘Data’ and ‘Split text into columns’ to split based on a delimiter such as comma or space. You can also use ‘Ask Gemini’ to generate a table in the same format describing the structure of the document.