Google Gemini can help you create content and calculations for Google Sheets. Gemini can act as a smart list generator that produces content in a table that you can export to a Google Sheet, or its responses can help you design formulas and functions. These uses of Gemini offer features that go beyond the standard autofill and correction ability 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 that is 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 an administrator to request access to Gemini.

Once you have access, open Gemini in any modern browser to get started. Enter a natural language message and the Gemini will respond. Answers may differ — two people entering the same prompt may not get the same answer.

How to get content from Gemini for a Google Sheet

With Geminiyou can ask the system about various comparisons and lists. For example, a request to compare two or more products might produce a table with one column for each product and each row with a different comparison function. You can also ask Gemini for lists of people, places or things. Since Gemini accepts sequences, you can first ask:

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

Then, after you get the first answer, ask again with:

Add a column for the population.

The tables that Gemini can generate in response vary much more than conventional auto-complete sequences available in Google Sheets with Tools | Autocomplete | Enable the Autocomplete option enabled.

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-and-extend the corner of the box to cover the entire range of cells to be filled. For example, if you type Mon in one cell and Tues in an adjacent cell, you can highlight those two cells and then drag the dot to fill five more cells with the remaining three-character descriptions of the days of the week in English.

However, the tables you can ask Gemini to create can cover a much wider range of listable data.

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

Select Export to sheet

When a response contains a table, select the Export to Sheet option in the lower right corner of the table. This will export the table to a new Google Spreadsheet. The system will use your prompt as both the name of the newly created file and the initial sheet in the file. The contents of the table will be placed in the cells of the sheet, with the headings of the columns in row A.

Query “in a table”

Gemini often automatically formats comparisons and lists in a table. When the system does not do this, add the phrase “in a table” to your request. This would make the prompt listed above become:

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

View other drafts or reset the chat

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

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

Gemini returned a table of 40 states, which is an incomplete response. In another example, a call for a:

List of elements by name and atomic weight.

In response, Gemini produced a list formatted as a snippet, not a table. When an answer is not what you expect, select the Show Other Drafts button to access alternative drafts. Sometimes one of these drafts will be formatted as a table rather than a snippet.

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

SEE: How to Use Google Gemini: A Comprehensive Guide

Here’s how to get help from Gemini with a Google Sheets calculation

You can ask Gemini to explain and give examples of formulas and functions in Google Sheets. Unlike Google Sheets help pages, which provide details and a static set of examples, you can ask Gemini for different examples and a detailed description of how a function works.

For example, if you want to learn a little more about one of them new features that Google added to Sheets in March 2023 you can ask:

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?

The response from Gemini included in sequence:

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

To explore additional Google Sheets features, you can ask:

Are there other Google Sheets functions that achieve something similar?

Gemini tends to offer some other features that help with related aspects of your first task.

Also, Gemini can help you create the exact formula you need, but it can be a bit of a trial and error process. For example, say you want to analyze weather data to identify how many days the wind was predominantly from the west. Try an initial detailed call to action first.

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 that returned an error. After a quick review of the feature, ask 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 a =COUNTIFS formula. Select the Copy Code button, switch to Google Sheet, navigate to the intended destination cell, and then select Edit | Paste to add the formula. This time, with a little tweaking, the snippet worked as intended.

And that’s a good snapshot of how you need to work with Gemini. If the first answer meets your needs, great! But always take the time to check the results are correct and be prepared to ask again – and ask differently – to elicit a more relevant, useful or correct answer

Can Gemini create tables in Google Sheets?

Yes! As described above, if the Gemini app creates a table, it is accompanied by an Export to Spreadsheet 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 lightning bolt in a blue circle. A panel will then open with some suggestion prompts, including the “Create a Table” button. Once you’ve clicked on this, you can replace the sample text with your own to create your prompt. You can also write a prompt from scratch without using a suggestion.

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

How do I access Gemini in Google Sheets?

If you’re signed in to a Google Account with Gemini enabled, you’ll see the “Ask Gemini” button next to your profile picture in the top right of your screen. Clicking will open a panel with a box where you can type a prompt related to your sheet.

Is Gemini available in all versions of Google Sheets?

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

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

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

This is paid in addition to the price of your Google Workspace account:

• Business starters: $7.20 per user per month.
• Business Standard: $14.40 per user per month.
• Business Plus: $21.60 per user per month.
• Business: Price individually to organization

SEE: Gmail vs Google Workspace: Key Differences for Users and Businesses

Can Gemini convert content from Google Docs to Google Sheets?

No, Gemini cannot directly move data between Google Workspace apps. If you have a table in a Google Doc and you want to move it to a spreadsheet, you can select and copy it, click a cell in the worksheet, and then paste it. If the pasted information is displayed in a single column, click “Data” and “Split text into columns” to split based on a delimiter, such as a comma or space. You can also use “Ask the Twins” to create a table in the same format as the one in the document by describing its structure.