📊 Excel & Spreadsheets Prompts

Act as a financial analyst. Create a detailed monthly financial report template in Excel with: revenue breakdown by category, operating expenses with SUM formulas, net profit calculation, month-over-month comparison using percentage change formulas, and a summary dashboard row. Use SUMIF for category totals and IF statements for conditional formatting rules.

Create an Excel weekly sales report template that includes: daily sales by product/service in rows, auto-calculated weekly totals using SUM, average daily sales using AVERAGE, best and worst performing days using MAX/MIN, percentage of weekly target achieved. Include instructions for creating a column chart comparing daily sales and a line chart showing the trend.

Build an Excel attendance tracking spreadsheet for a team of 20 employees. Include: employee names in column A, dates across row 1 (for one month), dropdown validation for P (Present), A (Absent), L (Late), H (Holiday). Add formulas to auto-calculate: total present days, absent days, attendance percentage per employee, and a summary row showing daily attendance rate. Use conditional formatting: green for P, red for A, yellow for L.

Create an Excel inventory management template with columns: Product ID, Product Name, Category, Unit Price, Quantity in Stock, Reorder Level, Total Value (auto-calculated). Add formulas: total inventory value using SUMPRODUCT, items below reorder level using COUNTIF, average stock level per category using AVERAGEIF. Add conditional formatting to highlight items where Quantity < Reorder Level in red.

Design an Excel project budget tracker with: budget categories (Labor, Materials, Software, Travel, Misc), planned budget column, actual spend column, variance column (auto-calculated), percentage spent. Include a SUMIF-based summary that totals by category, a grand total row, and conditional formatting where over-budget items turn red and under-budget items turn green.

Explain how to use VLOOKUP in Excel to match data between two sheets. Scenario: Sheet1 has employee IDs and names, Sheet2 has employee IDs and salaries. Write the exact VLOOKUP formula to pull salary data into Sheet1 based on matching employee IDs. Include: the formula, explanation of each parameter (lookup_value, table_array, col_index_num, range_lookup), common errors and how to fix them (#N/A, wrong results), and when to use FALSE vs TRUE for the last parameter.

Show me how to use INDEX MATCH in Excel as a more powerful alternative to VLOOKUP. Create examples for: basic left-to-right lookup, reverse lookup (looking up values to the left), two-criteria lookup using INDEX MATCH MATCH, and case-sensitive lookup. For each example, provide the exact formula, explain how it works step by step, and show sample data.

Write a nested IF formula in Excel that assigns letter grades based on numeric scores: 90-100 = A, 80-89 = B, 70-79 = C, 60-69 = D, below 60 = F. Provide: the exact formula, a version using IFS function (Excel 365), explanation of the logic flow, and how to add plus/minus grades (A+, A, A-, etc.) with more conditions.

Explain SUMIFS in Excel with 3 practical examples: 1) Sum sales where region is East AND month is January, 2) Sum expenses where amount is greater than 1000 AND category is Marketing, 3) Sum hours where employee name matches AND date falls within a specific range. For each example, provide the formula, sample data layout, and explain the difference between SUMIF (single criteria) and SUMIFS (multiple criteria).

Give me the most useful Excel date formulas for business: 1) Calculate age from birthdate using DATEDIF, 2) Find the last day of any month using EOMONTH, 3) Calculate business days between two dates using NETWORKDAYS, 4) Add 30 business days to a date using WORKDAY, 5) Extract year, month, day separately using YEAR/MONTH/DAY, 6) Calculate days until deadline. Provide exact formulas and sample usage for each.

Show me essential Excel text formulas with examples: 1) Extract first name from full name using LEFT+FIND, 2) Extract domain from email using MID+FIND, 3) Combine first and last name with CONCATENATE or ampersand, 4) Clean extra spaces with TRIM, 5) Convert text to proper case with PROPER, 6) Remove non-printable characters with CLEAN, 7) Extract numbers from mixed text using VALUE+SUBSTITUTE. Provide the exact formula for each with sample data.

Guide me step by step to create a pivot table in Excel for sales analysis. My data has columns: Date, Product, Region, Salesperson, Quantity, Revenue. Show me how to: 1) Create the pivot table, 2) Set up rows as Product, columns as Region, values as Sum of Revenue, 3) Add a filter for Date (by month), 4) Sort by revenue highest to lowest, 5) Add calculated field for average revenue per unit, 6) Create a pivot chart from the results.