Collaborate with Gemini in Google Sheets

You can use Gemini to generate formulas in Sheets.

1. On your computer, open a spreadsheet from Google Sheets.
2. At the top right, click Ask Gemini 👁 Image
   .
   1. You can also start from any cell and enter =.
   2. Then use a shortcut:
      • For Windows and Chrome OS: Ctrl + Alt + g
      • For Mac OS: ⌘ + Ctrl + g
3. In the side panel, create your own prompt using references from your sheet, or generic cell names. Examples of prompts:
   • “Create a formula that divides goals by games.”
   • “Create a formula to find cell C1 in range D:G and output value in column G.”
4. (Optional):
   • To add the formula to your spreadsheet, click the cell you’d like to insert this formula, click Insert 👁 Image
     .
   • To see a different version, click Retry 👁 Image
     .
   • To send feedback about the suggested table, click Good suggestion 👁 Image
     or Bad suggestion 👁 Image
     .

Fix formula errors with Gemini

You can ask Gemini to generate images in Sheets.

1. On your computer, open a spreadsheet from Google Sheets.
2. At the top right, click Ask Gemini 👁 Image
   .
3. On the side panel, create your own prompt.
   • Example: “Create an image of a dog with glasses.”
4. On the image that you like, you can hover over to:
   • Insert an image: Click Insert 👁 Image
     .
   • Find similar images: Click Generate more 👁 Image
     .
5. You can also:
   • Find more images: At the bottom, click Generate more 👁 Image
     .
   • Change your prompt: Click Edit prompt 👁 edit
     .
   • Send feedback about the generated images: At the bottom, click Good suggestion 👁 Image
     or Bad suggestion 👁 Image
     .

You can use Gemini to answer questions about the data in your sheet.

1. On your computer, open a spreadsheet from Google Sheets.
2. At the top right, click Ask Gemini 👁 Image
   .
3. In the side panel, create your own prompt with references from your sheet or generic cell names. Examples of prompts:
   • "Identify trends in this table."
   • "How can I show regression and prediction of this data?"
   • "What analysis can you help me perform with this sheet?"
   • "Help me understand month-to-month food prices."
4. Optional:
   • To check the analysis steps: Click Analysis steps 👁 Down arrow
     .
   • To export to a new Google docs: Click Export to Docs 👁 Image
     .

You can use Gemini to generate charts and graphs.

1. On your computer, open a spreadsheet from Google Sheets.
2. At the top right, click Ask Gemini 👁 Image
   .
3. In the side panel, create your own prompt with references from your sheet or generic cell names. Examples of prompts:
   • "Create a chart with date on the x-axis and total on the y-axis."
   • "Make a line chart."
   • "Create a graph that shows the percentage of expenses on the total stipend after tax."
   • "Make a chart analyzing my expenses for the month."
4. Optional:
   • To preview the chart: Click Preview 👁 Image
     .
   • To copy the chart: Click More options 👁 More
     👁 and then
     Copy chart 👁 Image
     .
   • To check the analysis steps: Click Analysis steps 👁 Down arrow
     .
   • To export to a new Google docs: Click Export to Docs 👁 Image
     .
5. To insert the chart into the sheet, click Insert 👁 Image
   .
   • When you insert a chart, it’s added to a new tab along with its underlying data.
   • At the bottom left, a message displays to confirm the insertion of a new tab. It also gives you an option to delete the newly inserted tab.
   • The chart is linked to the new tab’s data and is fully editable.

Tip: The generated chart doesn't link or respond to changes in the original data set that was used to generate it.

1. On your computer, open a spreadsheet from Google Sheets.
2. At the top right, click Ask Gemini 👁 Image
   .
3. To edit your spreadsheet through prompts, in the side panel, enter a prompt that describes the action you want to perform. Example of a prompt:
   • “Highlight values below 100”
4. Gemini generates an “Action preview card” with details of the proposed action. On the “Action preview card,” click Apply.
   • The preview card changes to a “Working on it…” dialog box.
5. Once the action is complete, the Settings 👁 Settings
   and Undo 👁 Undo
   buttons show.
   • Settings: If available, you can configure actions, like adjust pivot table parameters or conditional formatting rules, when you click Settings 👁 Settings
     .
     • This is only available for actions with configurable settings.
   • Undo: If the action doesn’t give the result you want, click Undo 👁 Undo
     .
     • This is available until you make further changes.

Ask Gemini to perform the following actions:

Apply conditional formatting

You can highlight cells based on specific criteria, like values below a certain number or duplicate entries.

In the side panel, enter your prompt. Examples of prompts:

• "Highlight rows where sales are greater than 500"
• "Add conditional formatting to highlight dates in the past"

Create new dropdowns

You can create dropdown lists in specified ranges or convert existing data to dropdown lists.

In the side panel, enter your prompt. Examples of prompts:

• "Create a dropdown in column A with options 'High,' 'Medium,' 'Low'"
• "Convert column B to a dropdown with values from column C"

Tip: When the location of the new dropdown list isn't specified, the new dropdown list created will be added to the rightmost empty column.

Create a new pivot table

You can create a pivot table to summarize data in a new sheet or the specified range.

In the side panel, enter your prompt. Example of a prompt:

• "Create a pivot table that shows the sum of sales by region"
• “Create a pivot with Status in column A, value as task counts, and columns for Priority”

Apply filters

You can apply filters to the active table context.
In the side panel, enter your prompt. Example of a prompt:

• “Filter for sales greater than $1,000”

Sort your spreadsheet

You can sort the active table context.

In the side panel, enter your prompt. Example of a prompt:

• “Sort by date in descending order”

Add checkbox

You can insert a checkbox into a specific cell or range of cells to track tasks with two-options states like "yes/no.”

In the side panel, enter your prompt. Example of a prompt:

• “Add a checkbox at the beginning of the table and name it ‘WATCHED’.”
• “Insert a checkbox in cell B5 and make sure it's unchecked by default.”

Find and replace

You can automate the process of searching for a specific text or value within a dataset and replacing it with a new one.

In the side panel, enter your prompt. Example of a prompt:

• "Find all instances of 'Grass' in the 'Data converted into Sheet Format' sheet and replace them with 'Grass court’.”
• "In column I, find every occurrence of the word 'Clay' and replace it with 'Clay court'."

Delete dimension

You can remove a column or a row from a dataset.

In the side panel, enter your prompt. Example of a prompt:

• “Delete the 'TOURNAMENT_SURFACE' column from the 'Data converted into Sheet Format' sheet."
• "Remove the row with the year ‘2021’."

Fill range

You can fill a specified range of cells with a single value or a repeating pattern.

In the side panel, enter your prompt. Example of a prompt:

• “Fill the cells in the 'YEAR' column from A2 to A22 with the value '1'."
• "Copy the value from cell H17 and apply it to the entire range from H18 to H22."

Insert dimension

You can create a new empty column or row at a specified location.

In the side panel, enter your prompt. Example of a prompt:

• "Insert a new column named 'ATP_RANKING_DIFF' between the 'RUNNER-UP_ATP_RANKING' and 'WINNER_LEFT_OR_RIGHT_HANDED' columns."
• "Add a new row at the bottom of the table."

Clear filter

You can remove any filters currently applied to a dataset to view all data entries.

In the side panel, enter your prompt. Example of a prompt:

• “Remove all filters from the 'TOURNAMENT_SURFACE' to view the complete data set."
• "Clear the filter that's currently applied to the 'NATIONALITY' column."

Format as table

You can apply a table style to a selected range of data.

In the side panel, enter your prompt. Example of a prompt:

• “Format the data range from J2 to J22 with comma and dollar sign."
• "Apply a table style to the selected data to make it easier to read."

Set number format

You can display numerical values in a cell or range of cells.

In the side panel, enter your prompt. Example of a prompt:

• "Change the number format for the 'WINNER_PRIZE' column to display currency with two decimal places."

Freeze dimension

You can lock a specific row or column in place to make it visible on the screen even when you scroll through the rest of the spreadsheet.

In the side panel, enter your prompt. Example of a prompt:

• "Freeze the top row of the ‘Data converted into Sheet Format’ sheet to make the headers visible."
• "Lock the first two columns of the 'Data converted into Sheet Format' to keep the 'YEAR,' 'TOURNAMENT,' and 'WINNER' visible while scrolling to the right."

Perform multi-step tasks

To handle complex requests and complete multiple tasks at the same time, combine several actions in a single prompt.

In the side panel, enter your prompt. Examples of prompts:

• "Clean up this tracker: make the status and priority columns into dropdowns. Add new columns at the end of the table: a new formula column for "Days until due date" (formatted as a number) and a checkbox column for Completed. Finally, highlight due dates in the next two weeks!"
• "Delete rows where the notes show the seller moved to a new team, highlight in light green sellers achieving more than 120% of quota, then add a new "Notes" column."
• "Delete rows where status is archived. Add a new "Team" column between task name and assignee and populate it with a dropdown of Animal themed team names."

You can use Gemini to reference emails you sent to generate responses.

1. On your computer, open a spreadsheet from Google Sheets.
2. At the top right, click Gemini in Sheets side panel 👁 Image
   .
3. In the side panel, provide your prompt asking Gemini to reference Gmail email(s).
   • Example: “Catch me up on the latest Monthly Review emails.”
4. To check which files were used, below the response, click Expand 👁 Image
   👁 and then
   Sources.
5. (Optional):
   • To add the text to your spreadsheet, click Insert 👁 Image
     .
   • To generate a new suggestion, click Retry 👁 Image
     .
   • To send feedback about the suggested text, click Good suggestion 👁 Image
     or Bad suggestion 👁 Image
     .

Important: Not all languages support Gems. Learn more about supported languages for Google Workspace with Gemini.

1. On your computer, at the top right, click Ask Gemini 👁 Image
   .
2. On the right, in the side panel, click Gems.
3. Click the Gem you want to use.
4. Select a custom or premade Gem.
   • To start a conversation with the Gem, type a question or prompt at the bottom.

Tip: To create a Gem, go to gemini.google.com. You can then use your Gem in the Gemini app and Gemini side panel. Learn how to use Gems in Gemini Apps.

You can use the Optimization problem solver to solve operations and logistics tasks, such as allocating budgets, scheduling staff, or optimizing supply chains.

1. On your computer, open a spreadsheet in Google Sheets.
2. At the top right, click Ask Gemini 👁 Image
   .
3. In the side panel, describe your optimization problem. Example prompts:
   • "I want you to solve a capital budgeting problem. Your main goal is to select the best portfolio of projects to maximize the total Net Present Value."
   • "I need to minimize total supplier spend. Adjust the order quantities for each supplier to meet my total demand of 1,000 units. Make sure no single supplier provides more than 40% of the total volume."
   • "I need help with a staffing problem. Determine the minimum number of employees to hire for each of my 7 shift patterns to minimize my total weekly payroll. I must meet my daily staffing requirements, and each employee costs $1,200 per week."
4. Press Enter.