ChatGPT Prompts for Excel and Google Sheets Formulas

Any time you know what you want but not the formula.

I use [EXCEL OR GOOGLE SHEETS]. My data is in [RANGE, E.G. A1:F500] with headers in row 1: [LIST COLUMN HEADERS AND WHAT THEY CONTAIN]. Write a formula that [WHAT YOU WANT, E.G. TOTALS SALES FOR EACH REP IN MARCH]. Tell me which cell to put it in, explain each part in plain words, and mention any version of the app it will not work in.

For matching customer, product, or price data between sheets.

In [EXCEL OR GOOGLE SHEETS], I have [SHEET 1 DESCRIPTION] and [SHEET 2 DESCRIPTION]. I want to pull [VALUE TO RETURN] from sheet [SHEET NAME] into column [COLUMN] by matching on [MATCHING COLUMN]. Write the formula using XLOOKUP, and also an INDEX and MATCH version for older versions. Show what to return if no match is found: [FALLBACK TEXT].

For reports by customer, month, product, or status.

Write a formula for [EXCEL OR GOOGLE SHEETS] that [SUMS OR COUNTS] [COLUMN] where [CONDITION 1] and [CONDITION 2], for example only paid invoices from a certain client in a date range. My columns are: [COLUMN LETTERS AND HEADERS]. Make the conditions reference cells so I can change them without editing the formula.

For due dates, aging, scheduling, and monthly reports.

My sheet has dates in column [COLUMN] formatted as [DATE FORMAT]. Write formulas to: [TASKS, E.G. GET THE MONTH NAME, COUNT DAYS BETWEEN TWO DATES, FLAG INVOICES OVER 30 DAYS OLD, FIND THE NEXT WORKING DAY]. Use [EXCEL OR GOOGLE SHEETS] and explain how to fix dates that are stored as text.

For status labels, tiers, commissions, and grades.

In [EXCEL OR GOOGLE SHEETS], column [COLUMN] contains [VALUE TYPE]. I want column [OUTPUT COLUMN] to show [OUTCOME 1] when [RULE 1], [OUTCOME 2] when [RULE 2], and [OUTCOME 3] otherwise. Write it with IFS and with nested IF, and tell me which is easier to maintain.

For margins, growth rates, and targets.

Write formulas for [EXCEL OR GOOGLE SHEETS] to calculate [WHAT, E.G. MONTH OVER MONTH GROWTH, MARGIN PERCENTAGE, SHARE OF TOTAL] using these columns: [COLUMNS AND HEADERS]. Handle divide by zero errors cleanly, show how to format the result as a percentage, and give an example with made up numbers so I can check it.

For simple cash, inventory, or account tracking.

My sheet tracks [TRANSACTIONS, STOCK OR CASH] with dates in [COLUMN], money in in [COLUMN], and money out in [COLUMN]. Write a running balance formula for [EXCEL OR GOOGLE SHEETS] starting from an opening balance in [CELL]. Explain how to copy it down without breaking it and how to handle blank rows.

When imported data from forms or other apps is messy.

Column [COLUMN] in my [EXCEL OR GOOGLE SHEETS] file has messy data, for example: [PASTE 5 SAMPLE VALUES]. Write formulas to [TASKS, E.G. TRIM SPACES, FIX CAPITALS, REMOVE SYMBOLS, STANDARDIZE PHONE NUMBERS]. Then explain how to replace the original column with the cleaned values.

For names, addresses, and codes in the wrong shape.

In [EXCEL OR GOOGLE SHEETS], column [COLUMN] contains values like [EXAMPLE VALUES]. I want to [SPLIT INTO FIRST AND LAST NAME, SPLIT ADDRESS PARTS, OR COMBINE COLUMNS] into columns [OUTPUT COLUMNS]. Give me a formula approach and a no formula approach using built in tools, and warn me about rows that might not split correctly.

For customer lists and imported records.

My sheet has [NUMBER] rows of [DATA TYPE] in [RANGE]. Duplicates are rows where [COLUMNS THAT DEFINE A DUPLICATE] match. Give me a formula to flag duplicates first so I can review them, then the steps to remove them safely in [EXCEL OR GOOGLE SHEETS] while keeping the [FIRST OR MOST RECENT] entry.

For pulling codes, emails, or IDs out of longer text.

Column [COLUMN] contains text like [PASTE EXAMPLES]. Write a formula for [EXCEL OR GOOGLE SHEETS] that pulls out [WHAT TO EXTRACT, E.G. THE ORDER NUMBER, THE DOMAIN FROM AN EMAIL, THE TEXT BETWEEN BRACKETS]. Explain how it works and test it against each example I gave.

When a formula breaks and you cannot see why.

This formula in [EXCEL OR GOOGLE SHEETS] returns [ERROR, E.G. #N/A, #VALUE!, #REF!, OR WRONG RESULT]: [PASTE FORMULA]. The data it uses looks like this: [DESCRIBE OR PASTE SAMPLE ROWS]. Explain the most likely causes in order, show how to check each one, and give a corrected formula.

When you inherit a spreadsheet you do not understand.

Explain this formula in plain English, step by step, for someone who is not a spreadsheet expert: [PASTE FORMULA]. Tell me what each function does, what the cell references point to ([DESCRIBE SHEET LAYOUT]), and whether there is a simpler way to get the same result.

To stop typos before they spread.

I want column [COLUMN] in my [EXCEL OR GOOGLE SHEETS] file to only accept [ALLOWED VALUES OR RULE, E.G. A DROPDOWN OF STATUSES, DATES IN THIS YEAR, NUMBERS ABOVE ZERO]. Give me step by step instructions to set up data validation, a custom error message, and how to highlight existing entries that break the rule.

For monthly sales, expense, and job reports.

My data in [RANGE] has these columns: [COLUMN HEADERS]. I want a summary showing [WHAT, E.G. TOTAL SALES BY MONTH AND PRODUCT]. Give step by step instructions to build it as a pivot table in [EXCEL OR GOOGLE SHEETS], including grouping dates by month, and a formula alternative using SUMIFS or QUERY.

When you want key numbers at a glance.

I want a one page dashboard in [EXCEL OR GOOGLE SHEETS] for my [BUSINESS TYPE] showing [METRICS, E.G. MONTHLY REVENUE, TOP PRODUCTS, OUTSTANDING INVOICES]. My raw data is on a sheet named [SHEET NAME] with columns [COLUMNS]. Suggest the layout, the formulas for each number, the best chart for each metric, and how to keep it updating automatically.

So problems stand out without reading every row.

Write conditional formatting rules for [EXCEL OR GOOGLE SHEETS] that highlight [CONDITION, E.G. OVERDUE INVOICES, LOW STOCK BELOW REORDER LEVEL, DUPLICATE EMAILS] in range [RANGE]. Give the exact custom formula for each rule, the colors to use, and the steps to apply them.

For flexible reports in Google Sheets.

In Google Sheets, my data is in [SHEET NAME]![RANGE] with headers [COLUMN HEADERS]. Write a QUERY formula that [SELECTS, FILTERS, GROUPS, SORTS AS NEEDED, E.G. TOTAL HOURS BY EMPLOYEE FOR THIS MONTH, SORTED HIGHEST FIRST]. Explain each clause and how to make the filter values come from cells.

For live lists like open jobs or unpaid invoices.

In [EXCEL OR GOOGLE SHEETS], I want a separate area that automatically lists all rows from [RANGE] where [CONDITION], sorted by [COLUMN], updating as new data is added. Write it with FILTER and SORT and tell me what happens if no rows match and how to show a friendly message instead.

For reconciling customer lists, stock counts, or payments.

I have two lists in [EXCEL OR GOOGLE SHEETS]: [LIST 1 LOCATION AND CONTENT] and [LIST 2 LOCATION AND CONTENT]. Write formulas to show which items are in both, only in the first, and only in the second. Make it work even if spacing and capital letters differ.

Before you make a chart for a meeting or report.

I want to show [WHAT YOU WANT TO SHOW, E.G. SALES TREND OVER 12 MONTHS, EXPENSE BREAKDOWN, TARGET VS ACTUAL] to [AUDIENCE] using this data: [DESCRIBE DATA]. Recommend the best chart type in [EXCEL OR GOOGLE SHEETS], explain why, and give step by step instructions to build and format it clearly.

When you are starting a new tracker.

Design a [EXCEL OR GOOGLE SHEETS] spreadsheet for [PURPOSE, E.G. TRACKING JOBS, INVOICES, INVENTORY, OR EMPLOYEE HOURS] for a [BUSINESS TYPE]. Give me the tabs, the columns for each tab with data types, the formulas needed, dropdowns, and a summary tab. Keep it simple enough for staff with basic skills.

For simple quoting and invoicing without extra software.

Build an invoice template layout in [EXCEL OR GOOGLE SHEETS] for [BUSINESS NAME]. Include line items with quantity, unit price, and line total, a subtotal, tax at [TAX RATE], discount, and grand total. Give the formulas for each total, which cells to lock, and how to number invoices.

For repetitive tasks you do every week.

Write a [VBA MACRO FOR EXCEL OR APPS SCRIPT FOR GOOGLE SHEETS] that [TASK, E.G. COPIES ROWS MARKED DONE TO AN ARCHIVE SHEET, OR EMAILS ME WHEN STOCK FALLS BELOW A LEVEL]. My sheet layout is: [SHEET NAMES AND COLUMNS]. Comment each line, tell me exactly how to install and run it, and remind me to test it on a copy first.

For keeping spending on track month by month.

Create a monthly budget versus actual layout in [EXCEL OR GOOGLE SHEETS] for a [BUSINESS TYPE] with categories [CATEGORIES]. Include columns for budget, actual, difference, and difference percentage, conditional formatting for overspending, and a year to date total. Give every formula.

When a weekly task eats hours of copying and adding.

Every [FREQUENCY] I [DESCRIBE MANUAL PROCESS, E.G. COPY BOOKINGS FROM EMAILS AND ADD UP COMMISSIONS]. Suggest how to turn this into a [EXCEL OR GOOGLE SHEETS] workflow: the layout, the formulas, what still needs to be typed by hand, and what could be imported or automated.