Create an Excel Dashboard with AI: Define the Metrics First

Worked example

Choose the correct conversion-rate denominator

Illustrative example written for this guide. Model outputs vary.

The combined rate uses combined counts. Averaging the monthly percentages would give 7.5%, which is a different calculation.

Prompt to adapt

Design a dashboard for visits, purchases and purchase conversion rate. Define conversion as purchases divided by visits for the selected period. Show the source counts beside the rate. Flag zero denominators and explain how filters affect each metric.

Write a metric dictionary

For each KPI, specify the numerator, denominator, time period and exclusions. Decide which event date determines the reporting period. A purchase date and a payment settlement date answer different questions.

Define how missing or zero denominators should appear. A blank or “not available” can communicate more accurately than a misleading zero-percent result.

Choose charts for the question

Use a trend chart to show change over time and a ranked bar chart to compare categories. Keep units and date ranges visible. A single large number benefits from context, such as the previous comparable period.

Limit the first dashboard to a few decisions. For a campaign review, traffic, purchases and conversion can be more useful than a crowded screen of loosely related totals.

Test filters and refresh behavior

Apply one filter at a time and compare the displayed totals with the underlying rows. Check that all charts use the intended date range and that a percentage recalculates from the filtered counts.

Add a new reporting period and verify the dashboard includes it. Label the last refresh and document any manual steps so the next reviewer understands how current the view is.