Excel AI

Act as a Free Excel AI assistant for formula generation and debugging. I am using Excel and need help solving a spreadsheet formula problem. My dataset has these columns: [describe columns, e.g., Date, Region, Sales Rep, Product, Units Sold, Revenue, Cost]. My goal is: [describe goal, e.g., calculate profit margin by product, find duplicate invoices, create a dynamic total by region]. Please generate the best Excel formula for my task, explain how it works in simple terms, identify any assumptions, provide an alternative formula if my Excel version does not support newer functions, and show how to test whether the formula is correct. Return the answer as a JSON array with objects containing: problem, recommended_formula, explanation, assumptions, compatibility_notes, validation_steps, and alternative_formula.

[{"problem":"Calculate profit margin by product using a free Excel AI-generated formula","recommended_formula":"=IFERROR((SUMIFS(RevenueRange,ProductRange,A2)-SUMIFS(CostRange,ProductRange,A2))/SUMIFS(RevenueRange,ProductRange,A2),0)","explanation":"Calculates total revenue and cost for each product, then divides profit by revenue to get margin.","assumptions":"Product name is in A2 and named ranges are available or replaced with actual cell ranges.","compatibility_notes":"Works in most modern Excel versions; structured table references can be used for cleaner formulas.","validation_steps":"Manually check one product total against filtered rows and confirm the margin percentage format.","alternative_formula":"Use a PivotTable with Revenue and Cost summarized by Product, then add a calculated field for Profit Margin."}]

Act as a Free Excel AI data-cleaning assistant. I have an Excel sheet with messy data that needs to be cleaned and standardized. The columns are: [list columns]. Current issues include: [examples: inconsistent date formats, extra spaces, mixed capitalization, duplicate rows, missing values, numbers stored as text, combined name fields]. Please create a step-by-step Excel workflow to clean the data using formulas, Power Query, or built-in Excel features. Include exact formulas or transformation steps, explain why each step is needed, and recommend quality checks before analysis. Return the response as a JSON array with objects containing: cleaning_task, issue_detected, excel_method, formula_or_steps, expected_result, quality_check, and risk_note.

[{"cleaning_task":"Standardize messy customer and transaction data","issue_detected":"Extra spaces, inconsistent dates, duplicate rows, missing values, and numbers stored as text","excel_method":"Use TRIM, CLEAN, DATEVALUE, Remove Duplicates, IF blanks checks, and Power Query type conversion","formula_or_steps":"Apply =TRIM(CLEAN(A2)) for text fields, use =DATEVALUE(B2) for date conversion where needed, convert numeric text with =VALUE(C2), then use Data > Remove Duplicates or Power Query Remove Rows > Remove Duplicates.","expected_result":"A consistent dataset ready for formulas, pivot tables, and reporting.","quality_check":"Compare row counts before and after cleaning, scan blank fields, and validate date and number formats.","risk_note":"Do not remove duplicates before confirming which columns define a true duplicate record."}]

Act as a Free Excel AI business analyst. I have an Excel dataset for sales performance analysis with these columns: [Date, Region, Product, Sales Rep, Revenue, Cost, Units Sold, Customer Segment]. I want to create a quick executive summary that includes total revenue, profit, profit margin, top-performing regions, top products, monthly trends, anomalies, and a simple forecast for the next period. Please recommend the Excel formulas, pivot table layout, charts, and forecasting approach I should use. Include natural-language insights I can paste into a report. Return the output as a JSON array with objects containing: analysis_goal, required_calculation, excel_formula_or_method, visualization_recommendation, insight_summary, forecast_method, and validation_tip.

[{"analysis_goal":"Create an executive sales summary from Excel data","required_calculation":"Revenue, profit, margin, regional performance, product ranking, trend analysis, anomaly detection, and forecast","excel_formula_or_method":"Use SUM, SUMIFS, PivotTables, calculated fields for Profit = Revenue - Cost, Profit Margin = Profit / Revenue, and FORECAST.ETS for time-series forecasting.","visualization_recommendation":"Use a line chart for monthly revenue trend, bar chart for revenue by region, column chart for top products, and KPI cards for revenue, profit, and margin.","insight_summary":"Revenue is strongest in the highest-performing regions and products; investigate months with unusual spikes or drops before final reporting.","forecast_method":"Aggregate revenue by month and apply =FORECAST.ETS(next_month, revenue_values, month_values).","validation_tip":"Check that dates are continuous, outliers are explained, and forecast results are compared against recent historical averages."}]