ChatGPT Prompts for Google Sheets Formulas & Automation

Create a VLOOKUP formula that matches customer IDs from Sheet1 column A with their purchase amounts in Sheet2 and returns the highest purchase. Handle cases where no match is found with a default value of 0.

Write an IF statement combined with COUNTIFS that counts how many times each product appears in a sales list, then marks it as "High Volume" if it appears more than 5 times, otherwise "Standard".

Generate a formula that calculates the percentage change month-over-month for revenue data in column B, accounting for months with zero revenue to avoid division errors.

Create a SUMPRODUCT formula that multiplies quantities in column B by unit prices in column C, then applies a tiered discount: 10% off if total > $1000, 15% off if total > $5000.

Write an INDEX-MATCH formula to pull employee performance ratings from a lookup table based on both employee name (column A) and review period (column B).

Generate a formula using ARRAYFORMULA that automatically calculates running totals for a sales column, updating as new data is added.

Create a script that automatically removes duplicate rows from a Google Sheet based on column A, keeping only the first occurrence and logging how many duplicates were removed.

Write a formula-based solution that splits full names in column A into first names (column B) and last names (column C) using text functions.

Generate a process to automatically validate email addresses in column A, flagging invalid formats with a helper column that marks them as "Valid" or "Invalid".

Create an automated data cleanup solution that trims extra spaces from all cells in a range, removes leading/trailing whitespace, and converts text to proper case.

Write a formula that extracts domain names from email addresses in column A and lists them with a count of how many emails use each domain.

Generate an automation that consolidates data from multiple sheets into a summary sheet, deduplicating entries and maintaining data integrity.

Create a dashboard template that tracks key metrics: total revenue, average transaction value, customer count, and month-over-month growth rate, with conditional formatting for trends.

Write formulas that segment customers by purchase frequency (1-time buyer, repeat, loyal) and calculate lifetime value for each segment.

Generate a weekly performance report that pulls data from raw transaction logs, calculates key metrics by day, and highlights top performers and underperformers.

Create a cohort analysis sheet that groups customers by signup month and tracks retention, repeat purchase rate, and revenue contribution over time.

Write a formula system that calculates marketing ROI by campaign, comparing ad spend against attributed revenue, with variance analysis from forecast.

Generate an automated inventory tracking sheet that monitors stock levels, flags items below reorder points, and projects when replenishment is needed based on usage velocity.

Create an automated expense tracking sheet with categories, approval workflows, and a summary that flags expenses exceeding budget by department.

Write a formula-based project management template that calculates task completion percentage, identifies overdue items, and provides a timeline view of project health.

Generate an automated employee timesheet system that calculates hours worked, applies overtime rules (1.5x after 40 hours), and totals labor costs by department.

Create a lead scoring system in Google Sheets that weights multiple factors (engagement level, company size, budget) and automatically ranks leads by conversion probability.

Write an automated invoice generation tool that pulls customer information, itemizes charges based on a pricing table, and calculates totals with applicable taxes.

Generate a customer feedback aggregation sheet that collects responses from multiple sources, categorizes sentiment, and identifies top issues for resolution.

Create a complex nested formula that calculates commission tiers: 5% on first $10K, 8% on $10K-$50K, 12% on amounts over $50K, all from a single sales amount.

Write a formula combining FILTER, SORT, and conditional logic to automatically rank sales by region, filter out underperformers, and sort by revenue descending.

Generate a scenario planning template where changing assumptions in one section automatically updates financial projections and variance analysis.

Create a formula that simulates different pricing strategies and shows the impact on margin, volume, and revenue for sensitivity analysis.

Write a dynamic filtering system using FILTER and QUERY functions that lets users filter data by multiple criteria without creating separate sheets.

Generate a recursive-style formula structure that builds organization hierarchies, showing reporting relationships and team sizes at each level.

Create a Google Sheets integration that pulls real-time data from an external API using IMPORTDATA and refreshes automatically every hour.

Write a script that syncs data between Google Sheets and a CRM system, pushing updates in one direction and logging all changes for audit purposes.

Generate a form-to-sheet automation that captures form responses, deduplicates submissions based on email, and assigns them to team members for follow-up.

Create a Slack notification system triggered by Google Sheets that alerts the team when specific thresholds are crossed (e.g., inventory runs low).

Write a Gmail integration that creates a ticket in Google Sheets whenever an email arrives with a specific label, extracting key information automatically.

Generate a trigger-based workflow that exports data from Google Sheets to multiple destinations (CSV files, email summaries, other sheets) on a daily schedule.