10 Prompts for Excel and Google Sheets: Formulas, Macros, Dashboards

# 10 Prompts for Excel and Google Sheets: Formulas, Macros, Dashboards **Unlock the full potential of your spreadsheets with ready-to-use AI prompts. Save hours of manual work and build professional dashboards in minutes.** Spreadsheets are the backbone of modern business analytics, yet many professionals spend up to 30% of their workweek on repetitive data manipulation tasks (McKinsey Global Institute, 2023). While Excel and Google Sheets have powerful built-in functions, writing complex formulas, debugging macros, or designing interactive dashboards often requires deep expertise. Enter AI-powered prompts: by describing your goal in natural language, you can generate precise formulas, VBA code, or dashboard layouts—instantly. This guide provides 10 copy-paste-ready prompts for Excel and Google Sheets, each with a clear use case, a working example, and practical tips. Whether you're a data analyst, marketer, or small business owner, these prompts will streamline your workflow and help you avoid costly errors. --- ## How to Use These Prompts Effectively Before diving into the prompts, keep these best practices in mind: - **Be specific**: Include column names, sheet names, and the exact logic you need. Vague prompts produce generic results. - **Provide context**: If you're working with a large dataset, mention the range (e.g., `A1:D100`) and any edge cases (e.g., blank cells, duplicates). - **Iterate**: If the first output isn't perfect, refine your prompt—add constraints or ask for alternatives. - **Test on a copy**: Always test generated formulas or macros on a sample of your data to avoid unintended changes. --- ## 1. Prompt for Complex Nested Formulas (e.g., IF with VLOOKUP, INDEX-MATCH) **Task**: Create a formula that returns a discount rate based on customer tier and purchase amount, using nested `IF` and `XLOOKUP`. **Prompt**: > "Write an Excel formula for cell C2 that looks up the discount percentage from Sheet2 based on the customer tier in A2 and the purchase amount in B2. Tiers are 'Gold', 'Silver', 'Bronze' in column A of Sheet2. If the amount is over $500, apply a 5% bonus discount. If the tier is not found, return 'No discount'." **Example Output**: ```excel =IFERROR(XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B) + IF(B2>500, 0.05, 0), "No discount") ``` **Explanation**: This formula first retrieves the base discount for the customer tier using `XLOOKUP`, then adds a 5% bonus if the purchase exceeds $500. If the tier is missing, `IFERROR` returns a custom message. --- ## 2. Prompt for Pivot Table Automation (Google Sheets) **Task**: Automatically create a pivot table that summarizes sales by region and product category. **Prompt**: > "In Google Sheets, generate a pivot table from the sheet 'SalesData' (columns: Date, Region, Product, Quantity, Revenue). Place the pivot table starting at cell F1. Rows: Region, Columns: Product, Values: Sum of Revenue. Also show a filter for Date range." **Example Output** (manual steps described): 1. Select `SalesData` range. 2. Go to **Data > Pivot table**. 3. Set rows to 'Region', columns to 'Product', values to 'Revenue' (sum). 4. Add 'Date' as a filter. **Pro Tip**: For repeated reports, record a macro while creating the pivot table, then reuse it. --- ## 3. Prompt for Conditional Formatting Rules (Highlight Duplicates, Top 10%) **Task**: Apply conditional formatting to highlight duplicate entries in a column and also mark the top 10% values. **Prompt**: > "Give me the conditional formatting rules for Excel to highlight duplicate values in column A (light red fill with dark red text) and also highlight the top 10% values in column B (green fill). Apply to the range A1:B100." **Example Output**: - **Rule 1 (Duplicates)**: Select `A1:A100` → Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values → choose 'Light Red Fill with Dark Red Text'. - **Rule 2 (Top 10%)**: Select `B1:B100` → Home > Conditional Formatting > Top/Bottom Rules > Top 10% → choose 'Green Fill with Dark Green Text'. **Explanation**: Conditional formatting rules are applied in order; you can manage their priority in the Conditional Formatting Rules Manager. --- ## 4. Prompt for VBA Macro to Clean Data (Remove Blanks, Trim Spaces) **Task**: Create a VBA macro that cleans the active worksheet by removing blank rows, trimming spaces, and converting text to proper case. **Prompt**: > "Write a VBA macro named 'CleanData' that does the following on the active worksheet: (1) Delete all rows that are completely blank, (2) Trim leading/trailing spaces from all cells in columns A-C, (3) Convert all text in column B to proper case. Show the full code." **Example Output**: ```vba Sub CleanData() Dim rng As Range Dim cell As Range ' Delete blank rows On Error Resume Next ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete On Error GoTo 0 ' Trim spaces in columns A-C Set rng = Range("A1:C" & ActiveSheet.UsedRange.Rows.Count) For Each cell In rng If Not IsEmpty(cell) Then cell.Value = Application.Trim(cell.Value) End If Next cell ' Proper case in column B Set rng = Range("B1:B" & ActiveSheet.UsedRange.Rows.Count) For Each cell In rng If Not IsEmpty(cell) Then cell.Value = Application.Proper(cell.Value) End If Next cell MsgBox "Data cleaned!" End Sub ``` **Usage**: Press `Alt+F11` to open the VBA editor, insert a new module, paste the code, then run `CleanData` from the Macros menu. --- ## 5. Prompt for Google Apps Script to Send Email Alerts **Task**: Create a script that sends an email alert when a cell value in a Google Sheet exceeds a threshold. **Prompt**: > "Write a Google Apps Script function 'sendAlert' that checks the value in cell B2 of sheet 'Dashboard'. If B2 is greater than 1000, send an email to 'user@example.com' with subject 'Alert: threshold exceeded' and body 'Value is ' + B2. Also add a custom menu item to run the script." **Example Output**: ```javascript function sendAlert() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dashboard"); var value = sheet.getRange("B2").getValue(); if (value > 1000) { var recipient = "user@example.com"; var subject = "Alert: threshold exceeded"; var body = "Value is " + value; MailApp.sendEmail(recipient, subject, body); } } function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu("Custom") .addItem("Send Alert", "sendAlert") .addToUi(); } ``` **Pro Tip**: You can trigger this script on a time-driven schedule (e.g., every hour) using the Google Apps Script triggers. --- ## 6. Prompt for Dynamic Dashboard Layout (Charts + KPIs) **Task**: Design a dashboard layout with KPIs, a line chart for monthly trends, and a table for top products. **Prompt**: > "Design a one-page Excel dashboard layout for sales data. Include: (a) top row with 3 KPI cards: Total Revenue, Avg Order Value, and Conversion Rate (use formulas), (b) a line chart showing monthly revenue for the current year, (c) a table listing top 10 products by quantity sold. Use a clean blue-and-white color scheme." **Example Output**: - **KPI formulas**: - Total Revenue: `=SUM(SalesData[Revenue])` - Avg Order Value: `=AVERAGE(SalesData[Revenue])` - Conversion Rate: `=COUNTIF(SalesData[Converted],"Yes")/COUNTA(SalesData[Converted])` - **Chart**: Insert a line chart with months on the X-axis and revenue on the Y-axis. - **Table**: Use `=SORT(SalesData[[Product]:[Quantity]], 2, -1)` to get top 10. **Pro Tip**: Use named ranges and Excel Tables (Ctrl+T) to make formulas automatically expand as data grows. --- ## 7. Prompt for Data Validation Drop-Down Lists (Dependent) **Task**: Create dependent drop-down lists where selecting a category (e.g., 'Fruits') limits the second list to items (e.g., 'Apple', 'Banana'). **Prompt**: > "Create a dependent data validation list in Excel. Sheet1 has a category list in A1:A3 (Fruits, Vegetables, Dairy). Sheet2 has two columns: Category and Item. When a user selects 'Fruits' in cell A1 of Sheet1, cell B1 should show only fruits (Apple, Banana, Orange). Use the INDIRECT function." **Example Output**: 1. On Sheet2, create named ranges: `Fruits = Sheet2!$B$2:$B$4`, `Vegetables = Sheet2!$C$2:$C$4`, etc. 2. On Sheet1, cell A1: Data Validation → List → Source: `=Sheet2!$A$1:$A$3`. 3. Cell B1: Data Validation → List → Source: `=INDIRECT(A1)`. **Explanation**: The `INDIRECT` function converts the text in A1 into a valid range reference, dynamically changing the options. --- ## 8. Prompt for Power Query (Get & Transform) to Combine Multiple Sheets **Task**: Combine data from multiple sheets (or files) into one table using Power Query. **Prompt**: > "Write step-by-step instructions for using Power Query in Excel to combine all sheets from a workbook named 'Sales_2026.xlsx' into a single query. Each sheet has the same columns: Date, Product, Revenue. Load the result to a new worksheet." **Example Output**: 1. Go to **Data > Get Data > From File > From Excel Workbook**. 2. Select the file and click **Import**. 3. In the Navigator, select the first sheet and click **Transform Data**. 4. In Power Query Editor, go to **Home > Append Queries > Append Queries as New**. 5. Add all other sheets from the dropdown. 6. Click **Close & Load** to load the combined data into a new worksheet. **Pro Tip**: Use `Table.Combine` in the Advanced Editor for more control, especially when dealing with dozens of sheets. --- ## 9. Prompt for Array Formulas (e.g., SUMIFS with Multiple Criteria) **Task**: Calculate total sales for a specific product in a specific region using `SUMIFS`. **Prompt**: > "Write an Excel array formula (or SUMIFS) that sums the Revenue column (column D) where Product (column B) equals 'Widget A' and Region (column C) equals 'North'. Use the dynamic range D2:D100, B2:B100, C2:C100. Also show how to make it dynamic with structured references." **Example Output**: ```excel =SUMIFS(D2:D100, B2:B100, "Widget A", C2:C100, "North") ``` **For dynamic ranges** (using Excel Tables): ```excel =SUMIFS(Table1[Revenue], Table1[Product], "Widget A", Table1[Region], "North") ``` **Explanation**: `SUMIFS` is more efficient than array formulas (no need to press Ctrl+Shift+Enter) and works with both static and table ranges. --- ## 10. Prompt for Error Handling in Formulas (IFERROR, IFNA) **Task**: Handle `#N/A` errors from `VLOOKUP` gracefully by showing 'Not Found' instead. **Prompt**: > "Write an Excel formula that uses VLOOKUP to find a product price in Sheet2!A:B, but returns 'Not Found' if the product ID in A2 does not exist. Also handle cases where the price cell is blank by returning 'Price missing'." **Example Output**: ```excel =IFNA(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), IF(VLOOKUP(A2, Sheet2!A:B, 2, FALSE)="", "Price missing", "Not Found")) ``` **Simpler version**: ```excel =IFERROR(IF(VLOOKUP(A2, Sheet2!A:B, 2, FALSE)="", "Price missing", VLOOKUP(A2, Sheet2!A:B, 2, FALSE)), "Not Found") ``` **Pro Tip**: Use `IFNA` (available in Excel 2013+) instead of `IFERROR` if you only want to catch `#N/A` errors and let other errors (like `#VALUE!`) surface for debugging. --- ## Choosing Between Excel and Google Sheets for Automation Both platforms offer robust automation, but they differ in key areas: | Feature | Excel | Google Sheets | |---------|-------|---------------| | Scripting language | VBA (requires local execution) | Google Apps Script (cloud-based) | | Real-time collaboration | Limited (co-authoring in 365) | Native multi-user editing | | Data size limits | ~1 million rows per sheet | 10 million cells per spreadsheet | | Power Query | Yes (desktop only) | No native equivalent (use Apps Script) | | API access | Via VBA or Office Scripts | Built-in REST API and triggers | For most small-to-medium business tasks, Google Sheets offers easier sharing and scripting via Apps Script. Excel remains superior for heavy data processing (Power Query) and complex financial models. ASI Biont supports connecting to both Excel and Google Sheets via API, enabling automated data syncing and reporting workflows—learn more at asibiont.com/courses. --- ## Frequently Asked Questions **Q: Can I use these prompts with any AI assistant?** A: Yes—all prompts are designed to work with ChatGPT, Claude, Gemini, or Copilot. Some AI tools may have context length limits; keep prompts concise. **Q: Where can I find more advanced VBA examples?** A: Microsoft’s official VBA documentation (learn.microsoft.com/en-us/office/vba) and the Excel MVP community (excelmvv.com) are excellent resources. **Q: How do I debug a generated macro?** A: Use the VBA editor’s debug tools: set breakpoints (F9), step through code (F8), and use `Debug.Print` to output values to the Immediate window. --- ## Conclusion Mastering spreadsheet automation doesn’t require memorizing hundreds of functions or learning to code from scratch. With the right prompts, you can generate complex formulas, macros, and dashboard layouts in seconds—freeing up time for analysis and decision-making. Start with the prompt most relevant to your current task, adapt it to your data, and iterate. Over time, you’ll build a personal library of prompts that can handle 80% of your repetitive spreadsheet work. **Ready to take your automation further?** Explore how ASI Biont can connect your spreadsheets to external APIs and automate reporting workflows. Visit asibiont.com/courses for hands-on tutorials.

10 Prompts for Excel and Google Sheets: Formulas, Macros, Dashboards

Comments

Recent articles

JavaScript for Web Development Course: Master In-Demand Skills with AI-Powered Learning on Asibiont

German with AI: The Future of Language Learning Is Already Here (2026 Update)

Neurohacking and Cognitive Optimization: How an Overloaded Brain Works at 100% Again

Mastering IT Legal & Compliance: Your AI-Powered Path to GDPR, Data Privacy, and IP Law in 2026

6 Security Settings Every GitHub Maintainer Should Enable This Week

5 Things That Surprised Me About Marketers at Connections 2026

React — Modern Frontend: How the Course on Asibiont Helps You Master React 19 and Next.js Faster and More Effectively

5 Android Phones You Should Buy Instead of the Moto G Power (2026)

15 Battle-Tested Prompts for Machine Learning: From Preprocessing to Model Training with Scikit-learn, XGBoost, and CatBoost