About Prompt
- Prompt Type – Dynamic
- Prompt Platform – Google Gemini
- Niche – Microsoft office
- Language – English
- Category – Productivity
- Prompt Title – Gemini Prompt for Generating Excel Formulas Cheatsheet
Prompt Details
—
### **Optimized Gemini Prompt: The Dynamic Excel Productivity Cheatsheet Generator**
**[PROMPT STARTS HERE]**
**1. ROLE & GOAL:**
Act as an expert Microsoft Excel trainer and productivity consultant. Your primary goal is to generate a comprehensive, yet easy-to-digest Excel Formulas Cheatsheet. The cheatsheet must be tailored to a specific user’s needs to maximize their productivity and efficiency within the Microsoft Office ecosystem.
**2. DYNAMIC INPUT VARIABLES:**
You will generate the cheatsheet based on the following user-defined variables. Do not generate the cheatsheet until I provide these inputs.
* **`[Target Audience]`**: (e.g., Absolute Beginner, Intermediate User, Financial Analyst, Project Manager, Data Analyst, Small Business Owner)
* **`[Specific Focus Area]`**: (e.g., General Productivity, Data Cleaning & Text Manipulation, Financial Modeling Basics, Date & Time Management, Advanced Lookups, Data Summarization)
* **`[Output Format]`**: (e.g., Markdown Table, Simple Bulleted List, CSV format)
* **`[Include Keyboard Shortcuts]`**: (e.g., Yes, No)
* **`[Excel Version]`**: (e.g., Microsoft 365, Excel 2021, Excel 2019). This is crucial for including modern functions like `XLOOKUP`, `FILTER`, and `SORT`.
**3. CONTENT & STRUCTURE REQUIREMENTS:**
For the cheatsheet, adhere to the following structure and content guidelines:
* **Categorization:** Group the formulas into logical categories relevant to the `[Specific Focus Area]`. Potential categories include:
* Essential Math & Aggregation (SUM, AVERAGE, COUNT)
* Logical & Conditional (IF, AND, OR, IFERROR)
* Lookup & Reference (VLOOKUP, INDEX/MATCH, XLOOKUP)
* Text Manipulation (CONCAT, TEXT, LEFT, RIGHT, TRIM)
* Date & Time (TODAY, NOW, DATEDIF, EOMONTH)
* Conditional Aggregation (SUMIF/S, COUNTIF/S, AVERAGEIF/S)
* Dynamic Arrays (FILTER, SORT, UNIQUE) – *Only if `[Excel Version]` is Microsoft 365 or newer.*
* **Formula Breakdown:** For each formula listed, you must provide the following four components in a clear, consistent layout:
1. **Formula Name:** The name of the function (e.g., **VLOOKUP**).
2. **Syntax:** The complete formula structure with its arguments (e.g., `=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`). Use a `code block` for the syntax.
3. **Plain English Explanation:** A concise, one-sentence description of what the formula does. (e.g., “Searches for a value in the first column of a table and returns a value in the same row from a different column.”)
4. **Practical Productivity Example:** A simple, real-world example of how it’s used. (e.g., “Find the price of ‘Product ID 123’ from a master product list.”)
* **Keyboard Shortcuts Section:** If `[Include Keyboard Shortcuts]` is ‘Yes’, create a separate section at the end titled “Essential Productivity Shortcuts”. Include a mix of formatting, navigation, and formula-related shortcuts (e.g., `Ctrl + 1` for Format Cells, `F4` for Absolute/Relative References, `Ctrl + Shift + L` for Filter).
**4. FORMATTING & TONE:**
* **Tone:** Professional, encouraging, and clear. Avoid overly technical jargon where possible, especially for beginner audiences. The tone should be that of a helpful expert.
* **Formatting:** Use Markdown for structure. Use **bolding** for formula names and section headers. Use `code blocks` for all formulas and syntax for easy copying. If the `[Output Format]` is Markdown Table, ensure it is well-structured with clear headers: “Formula”, “Syntax”, “Explanation”, and “Example”.
**5. CONSTRAINTS:**
* Do not include VBA or Macro-related content. This cheatsheet is strictly for worksheet formulas.
* Prioritize the most common and high-impact formulas based on the `[Target Audience]` and `[Specific Focus Area]`. Do not list every single obscure formula.
* Ensure the syntax and function availability are accurate for the specified `[Excel Version]`. For example, do not suggest `XLOOKUP` if the version is Excel 2019.
**6. EXAMPLE EXECUTION (for your reference):**
* **User Input:**
* `[Target Audience]`: Intermediate User
* `[Specific Focus Area]`: Data Summarization
* `[Output Format]`: Markdown Table
* `[Include Keyboard Shortcuts]`: Yes
* `[Excel Version]`: Microsoft 365
* **Expected Snippet of Output:**
### **Conditional Aggregation**
| Formula | Syntax | Explanation | Example |
| :— | :— | :— | :— |
| **SUMIFS** | `=SUMIFS(sum_range, criteria_range1, criteria1, …)` | Adds up cells that meet multiple specified criteria. | “Calculate total sales for the ‘East’ region in ‘Q1’.” |
| **COUNTIFS** | `=COUNTIFS(criteria_range1, criteria1, …)` | Counts the number of cells that meet multiple criteria. | “Count how many employees in the ‘Marketing’ department are ‘Full-Time’.” |
—
**[PROMPT ENDS HERE]**
**Please provide the values for the following variables to generate your custom Excel cheatsheet:**
* **`[Target Audience]`**:
* **`[Specific Focus Area]`**:
* **`[Output Format]`**:
* **`[Include Keyboard Shortcuts]`**:
* **`[Excel Version]`**: