Google Gemini can help you create content and calculations for Google Sheets. Gemini can act as a smart list generator, generating tabular content for export to Google Sheets, and its responses can help you devise formulas and functions. This use of Gemini provides functionality beyond Sheets’ standard auto-fill and edit capabilities.
See: ChatGPT vs. Google Gemini: An in-depth comparison (TechRepublic)
To perform the tasks below, you must be logged in to an active Google Account where you are authorized to use both Google Sheets and Google Gemini. If you use a Google Workspace account at work or school, you may need to contact your administrator to request access to Gemini.
Once access is granted, open Gemini in a modern browser to get started. Type in natural language prompts and Gemini will respond. Responses may vary. Two people who enter the same prompt may not receive the same response.
How to get content for Google Sheets in Gemini
Gemini allows you to ask the system for a variety of comparisons and lists. For example, a request to compare two or more products might produce a table with a column for each product and each row with a different comparison function. You can also ask Gemini for a list of people, places, or things. Gemini allows sequences, so you can display the next message first.
List the 10 cities in the United States with the most annual rainfall.
Then, after receiving the initial response, prompt again using:
Add a column for the population.
The tables that Gemini can generate in response can be found in Google Sheets under Tools | Autocomplete | Enable autocomplete option is enabled.
In Google Sheets, you enter a predictable series of alphabetic characters, numbers, days of the week, months, or other standard patterns into two or more cells. Then select the cells and select and expand the corners of the box to cover the entire range of cells to be filled. For example, if you type Mon in one cell and Tue in an adjacent cell, you can select those two cells and then drag the dot to fill the other five cells with the remaining three-character descriptions of the days of the week in English.
In contrast, the tables Gemini can lead you to create can consist of a much wider range of listable data.
See Also: Google Gemini Cheat Sheet: What is Google Gemini and How Does It Work?
Select Export to Sheet.
If your response includes a table, select the Export to Sheet option at the bottom right of the table. The table will then be exported to a new Google Sheet. The system uses the prompt as the name of the newly created file and the initial sheet within the file. The contents of the table are placed in cells on the sheet, along with the column titles in row A.
“At table” prompt
Gemini often automatically formats comparisons and lists in tables. If your system does not do this, add the phrase “in a table” to your request. This will cause the prompt listed above to look like this:
In a table, list the 20 cities in the United States with the most annual rainfall.
View another draft or reset the chat.
Sometimes Gemini gives partial or strangely formatted answers. For example, try this prompt:
List all 50 U.S. states sorted by population.
Gemini returned 40 main tables, which is an incomplete response. In another example, the following prompt is displayed:
List of elements by name and atomic weight.
In response, Gemini produced a list in code snippet format rather than a table. If the response is not what you expected, select the View another draft button to access an alternate draft. Sometimes one of these drafts is formatted as a table rather than a code snippet.
Another option when you encounter this kind of error is to start a new chat and then try again with the reworded prompt. This may return a more complete or better formatted response.
See also: How to Use Google Gemini: A Comprehensive Guide
How Gemini can help you with your Google Sheets calculations
You can ask Gemini to explain and provide examples of Google Sheets formulas and functions. Unlike the Google Sheets help pages, which provide detailed information and a static set of examples, you can ask Gemini for a variety of examples and a detailed explanation of how the feature works.
For example, if you want to learn more about one of the new features Google added to Sheets in March 2023, you can display this message:
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 included the following, in that order:
- This is an explanation of how to use the function.
- Sample formula to group a list into four sets.
- Examples of demo names.
- Additional tips like how to handle extra rows or how to combine WRAPCOLS with other functions.
To explore additional Google Sheets features, you may be prompted:
Are there other Google Sheets functions that achieve something similar?
Gemini tends to come with a few other features to help with relevant aspects of your initial work.
Gemini can also help you create the exact formula you need, but it can be a bit of a trial and error process. For example, let’s say you want to analyze weather data to determine how many days the wind was predominantly from the west. Try the initial detailed prompt 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 the =COUNTIF formula in error. After a quick review of the features, you’ll be prompted 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 target cell, and then select Edit | To add a formula, paste it. This time, with a few tweaks, the code snippet worked as desired.
This is a good snapshot of how to work with Gemini. If the initial response meets your requirements, great! But always take the time to check the accuracy of your results, and be prepared to re-prompt and prompt differently to evoke more relevant, useful, and accurate responses.
Can Gemini create tables in Google Sheets?
yes! If the Gemini app creates a table as described above, it comes with an Export to Sheet button that opens the table in a spreadsheet.
Alternatively, you can do this in Google Sheets by clicking the ‘Ask Gemini’ button in the top right corner of the screen. This button is indicated by a white flash within a blue circle. This will open a panel with some suggested messages, including a ‘Create table’ button. After clicking this button, you can configure the prompt by replacing the example text with your own text. You can also create a prompt from scratch without using suggestions.
If you press the little diagonal arrow below the table, Gemini will create it and take you to the spreadsheet.
How do I access Gemini from Google Sheets?
If you’re signed in to a Gemini-enabled Google account, you’ll see an ‘Ask Gemini’ button next to your profile picture in the top-right corner of your screen. Clicking it opens a panel with a box where you can write a prompt related to the sheet.
Does Gemini work with all versions of Google Sheets?
Gemini is a paid add-on for anyone with a Google Workspace account.
The Business version costs $24 per user per month if paid monthly or $20 per user per month if paid annually.
The Enterprise version costs $36 per user per month if paid monthly or $30 per user per month if paid annually.
This is paid in addition to the price of your Google Workspace account.
- Business Starter: $7.20 per user per month.
- Business standards: $14.40 per user per month.
- Business Plus: $21.60 per user per month.
- enterprise: Individual pricing
See also: Gmail and Google Workspace: Key differences for you and your business
Can Gemini convert Google Docs content to Google Sheets?
No, Gemini cannot move data directly between Google Workspace apps. If you have a table in a Google Doc and want to move it to a sheet, simply highlight the table, copy it, then click in a cell on the sheet and then paste. If the pasted data appears in one column, click Data and Split text into columns to split it based on a delimiter, such as a comma or space. ‘Ask Gemini’ also allows you to generate tables in the same format as in the document by describing their structure.
