Charts, Dashboards, and Data Visualization with AI

Numbers in a spreadsheet tell you what happened. Charts tell you what it means. A well-designed dashboard tells you what to do about it.

Most people skip visualizations because chart configuration feels overwhelming. With AI, you describe the story you want to tell, and it builds the chart.

🔄 Quick Recall: In the previous lesson, you created data summaries with QUERY formulas and pivot tables. Those summaries become the data source for the charts and dashboards you’ll build now.

Choosing the Right Chart Type

Before asking AI to create a chart, you need to know what story your data tells. Here’s the decision framework:

AI prompt for chart selection:

“I have monthly revenue data for 4 regions over 12 months. I want to show how each region’s revenue trends over time and compare regions to each other. What chart type should I use and how should I set it up?”

AI recommends the chart type AND explains the setup — often a multi-series line chart with one line per region.

✅ Quick Check: If you want to show what percentage each product category contributes to total sales, which chart type works best? (Answer: Pie chart or donut chart — they’re designed to show parts of a whole.)

Creating Charts with AI

In Google Sheets

Prompt for step-by-step guidance:

“I have data in A1:C13 — column A is months (Jan-Dec), column B is online sales, column C is in-store sales. Walk me through creating a column chart in Google Sheets that shows both sales channels side by side for each month, with a legend.”

AI provides the exact Insert → Chart steps, chart type selection, and customization options.

Formula-Based Chart Data

For more complex charts, ask AI to prepare the chart data first:

Prompt:

“I have raw transaction data in columns A through F. Create a summary table suitable for charting: rows = months (from Date in column A), columns = total revenue, order count, and average order value. This summary will feed a dashboard chart.”

This creates a clean data range that makes charting simple.

In Excel with Copilot

If you have Excel with Copilot, chart creation is even more direct:

Prompt (in Copilot):

“Create a line chart showing monthly revenue trends from the data in this sheet.”

Copilot generates the chart directly in your spreadsheet.

✅ Quick Check: Why is it helpful to create a summary data table before building a chart? (Answer: Charts work best with clean, aggregated data. A summary table with pre-calculated totals and averages gives you a reliable, simple data source instead of pointing the chart at thousands of raw rows.)

Building a KPI Dashboard

A dashboard isn’t just charts — it’s a decision-making tool. The best spreadsheet dashboards have three layers:

Layer 1: KPI Summary (top of sheet)

Key numbers at a glance. Ask AI:

“Create formulas for these KPI cells: Total Revenue this month, Revenue vs. last month (% change), Total Orders, Average Order Value, and Top Product by revenue. Data is in Sheet1, columns A through F.”

AI generates one formula per KPI. Arrange them in a row at the top of your dashboard sheet.

Layer 2: Charts (middle of sheet)

Pick 2-3 charts maximum. More than that creates visual clutter.

Good dashboard chart combinations:

• Revenue trend line chart + Category comparison bar chart
• Monthly performance column chart + Top 10 products horizontal bar
• Regional pie chart + Time series area chart

Layer 3: Detail table (bottom of sheet)

A filtered summary table for drill-down. Use the QUERY or SUMIFS formulas from the previous lesson.

Formatting Tips from AI

AI can also help with chart formatting:

Prompt:

“I’ve created a column chart in Google Sheets. How do I: (1) remove gridlines for a cleaner look, (2) add data labels on top of each bar, (3) change the colors to use blue for online sales and green for in-store sales, (4) set the y-axis to start at 0?”

These small formatting choices make the difference between a chart and a professional dashboard.

Conditional Formatting for Visual Cues

Conditional formatting turns cells into visual indicators without charts:

Prompt:

“Set up conditional formatting in Google Sheets for column D (% change): green background for values above 0, red background for values below 0, and yellow for exactly 0. Also add a color scale for column E (revenue) from light blue (lowest) to dark blue (highest).”

This creates a heat map effect that highlights patterns instantly.

Practice Exercise

Build a mini-dashboard from scratch:

1. Create or use a dataset with 50+ rows of sales data (Date, Product, Category, Amount)
2. Ask AI to create 4 KPI formulas (total sales, order count, average value, top category)
3. Ask AI to prepare a summary table for charting (monthly totals)
4. Create one line chart (trend) and one bar chart (comparison)
5. Add conditional formatting to highlight the best and worst months

Key Takeaways

• Match chart types to data stories: trends → line, comparison → bar, proportion → pie
• Build dashboards in three layers: KPIs at top, charts in middle, detail tables at bottom
• Ask AI to both create chart data and guide chart configuration
• Formula-based dashboards auto-update — no manual refreshing needed
• Limit dashboards to 2-3 charts maximum for clarity

Up Next

In the next lesson, you’ll explore the AI features built directly into your spreadsheet tool — Gemini in Google Sheets and Copilot in Excel — and learn what they can do that external AI tools can’t.