Gemini data analyst — from messy data to clean insights, formulas, and the right chart

Paste a table (or describe your sheet) into Gemini:

'You are a data analyst who starts with clean data and ends with clear insights. Here's my data:
[PASTE TABLE OR DESCRIBE THE COLUMNS AND A FEW SAMPLE ROWS].

STEP 1 — CLEAN:
Before analyzing, check for and flag: duplicate rows, inconsistent formatting (e.g., dates in mixed formats, 'N/A' vs blank vs '0'), outliers that might be data entry errors, and missing values. Tell me what you found and how you'd handle each issue.

STEP 2 — ANALYZE:
1. Summarize the 3 most important trends or outliers, with specific numbers.
2. For each trend, tell me: is this statistically meaningful or could it be noise? (If the dataset is small, say so — 'with only N data points, this trend is suggestive but not conclusive.')
3. If the data has categories, generate a pivot table summary (rows = [suggested dimension], columns = [suggested breakdown], values = [suggested metric]).

STEP 3 — FORMULAS:
Give me the exact Google Sheets formulas to calculate [the metrics I care about, e.g. month-over-month growth, totals by category, running average, percentage of total].
For each formula:
- The exact formula, copy-ready (assume data starts in row 2)
- Which cell to put it in
- One line explaining what it does
If the dataset is large (100+ rows), provide ARRAYFORMULA or QUERY versions.

STEP 4 — VISUALIZE:
Recommend the single best chart type and explain WHY that type (not another):
- Bar chart: for comparing discrete categories (never for time series)
- Line chart: for trends over time (need at least 5+ time periods to be meaningful)
- Scatter plot: for showing correlation between two variables (add a trendline if r² > 0.5)
- Pie chart: only if showing parts of a whole with ≤5 segments (otherwise use bar)
- Stacked bar: for composition across categories
Tell me: chart type, X axis, Y axis, any grouping/coloring, and the one insight the chart should make obvious.

STEP 5 — DIG DEEPER:
Suggest 2 questions this data raises that I should investigate, and what additional data I'd need to answer them.

Keep all formulas copy-ready. Use plain language for insights.'

Tips: paste the headers plus 5-10 representative rows so it understands the structure; if you have a specific hypothesis ('I think sales dropped because of X'), state it and ask Gemini to test it against the data; for large datasets, describe the columns and row count instead of pasting everything; add 'also generate the Apps Script to automate this analysis monthly' if this is a recurring report.