How to Build an AI-Powered Dashboard From Messy Data — Using Just Claude

Most dashboards fail before they're even built. Not because of the tool. Not because of the design. Because of the data underneath them.

Anyone who has worked in finance, operations, or strategy knows the reality: the files you actually receive are nothing like the clean, structured datasets in tutorials. Tabs with headers buried at row five. Revenue in thousands in one sheet, full units in another. Three years of data with three different structures and four different spellings of the same brand name. That is the real starting point for most analytical work.

This post walks through a complete workflow for turning that kind of data into a fully interactive, AI-powered executive dashboard — using Claude and four prompts.

The below Excel file contains the actual sample data used in the accompanying video, so you can follow along exactly.

Video:

​

Excel Files (Guide + Datasets)

​Guide.xlsx​

​01_PnL_Data_RAW.xlsx​

​02_Brand_Market_Mapping.xlsx​

​03_Targets_ProductMix_RAW.xlsx​

Result:

​https://claude.ai/public/artifacts/8fab2e5f-8e65-4d65-bb0f-f04ce9aa8839​

The scenario

To make this concrete, the workflow uses a fictional conglomerate called Scoops Global Group — a holding company with six ice cream brands operating across 18 countries and three years of P&L history. The data arrives as three separate Excel files:

The first contains P&L data across three tabs, one per year. Each tab has a different structure: 2022 is in quarterly USD, 2023 is in semi-annual figures reported in thousands, and 2024 is in local currencies with FX conversion rates in a separate column. There are duplicate rows, a blank row in the middle, headers that don't start at row one, and brand names spelled inconsistently across all three tabs.

The second file is a brand and market mapping table. It holds the canonical brand names, parent entities, regional classifications, and market tiers — but the region names are inconsistent (Europe, EUROPE, Latam, LatAm), and one brand was discontinued in 2022 and needs to be excluded.

The third file contains budget versus actual targets alongside product mix percentages. The 2024 actuals are mostly missing because the year wasn't fully closed at the time of the export. Headers begin at row five. Notes from the finance team are buried at row 65.

This is not an artificially broken dataset. This is Tuesday.

Why sequence matters

The instinct when handed messy data is to jump straight to visualization. Resist it. A chart built on unreconciled data is not an asset — it is a liability that will surface at the worst possible moment, usually in front of someone important.

The correct sequence is: clean first, structure second, design third, intelligence last. Each layer depends on the one beneath it. Collapsing them produces fragile outputs. Keeping them separate produces work you can defend.

Prompt 1 — Clean the data

The first prompt does one thing: take three structurally incompatible files and produce a single, normalised dataset in USD.

You attach all three files and tell Claude exactly what is broken. You name the unit inconsistency between tabs. You flag the duplicate rows and tell it to filter on validated entries only. You specify that the discontinued brand should be excluded. You ask it to standardise brand names using the mapping file, align the region names, flag rows where 2024 actuals are still pending, and output the result as a JavaScript constant that can be used directly in the next step.

This is the most important prompt in the workflow. If the cleaning is wrong, everything downstream is wrong. The more precisely you describe the anomalies, the better the output. Claude cannot see what you have not described.

The result is roughly 54 rows — 18 markets across three years — with annual USD revenue, COGS, gross profit, gross margin percentage, EBITDA where available, and a boolean flag for pending 2024 figures.

Prompt 2 — Build the dashboard

The second prompt takes that clean dataset and scaffolds the full dashboard structure as a React artifact. You ask for KPI cards at the top showing total revenue, gross margin, EBITDA, and number of active markets. A grouped bar chart comparing revenue by brand across all three years. A trend line per brand. A country-level performance table with colour coding. A gross margin breakdown with target lines. And a filter bar for year, brand, and region.

At this stage you are building for completeness, not beauty. The output will be functional and adequate. That is the goal. Attempting to specify design details before the structure is solid is a reliable way to introduce bugs.

Prompt 3 — Improve the design

Before writing this prompt, spend five minutes on Dribbble. Search for dark analytics dashboard or fintech dashboard UI. You are not looking for something to copy — you are building visual vocabulary so you can describe what you want with precision.

The prompt specifies a deep navy background, a blue accent colour, KPI cards with gradient borders and delta indicators in green and red, a brand colour system across all six brands, rounded bar chart tops with hover tooltips, an editorial layout with larger numbers and smaller labels, and a pill-style filter bar rather than dropdowns.

The principle here is specificity over aspiration. Telling Claude to make it look premium produces mediocre results. Telling it the exact background hex, the card border treatment, the typography scale, and the filter style produces something you would not be embarrassed to open in a board meeting.

Prompt 4 — Add the AI layer

This is the prompt that changes what the dashboard actually is.

You add a chat panel that calls the Anthropic API with the full cleaned dataset passed as context in the system prompt. The system prompt tells Claude it is a financial analyst assistant for Scoops Global Group, that it has access to three years of P&L data across six brands and 18 countries, that it should flag pending 2024 figures in its responses, and that it should format numbers readably — $12.4M rather than 12400000.

When the user types a question, it fires a request to Claude with that context, streams the response back into the chat panel, and displays it in a styled bubble interface matching the dashboard theme.

The result is a dashboard where any stakeholder can type "which brand missed its gross margin target by the most in 2023" or "what is the revenue trend for Sakura Soft across all three years" and receive a grounded, data-specific answer in seconds — without needing to know how to use a pivot table.

This is not a demonstration feature. This is the feature. The difference between a static report and a tool that non-analysts can actually use independently is this panel.

A note on the data volume

This workflow handles 54 rows embedded directly in the system prompt context. The same approach scales comfortably to 200–300 rows. Beyond that, the correct architecture is a backend API that queries a database and returns relevant records rather than passing the entire dataset on every request. For most internal business dashboards, 54 to 300 rows covers the majority of real use cases.

What this workflow actually changes

The traditional path from messy data to executive dashboard runs through a data engineer, a BI developer, a designer, and a round of stakeholder revisions. It takes weeks and costs real money. The output is a static report that no one can interrogate without going back to the developer.

This workflow produces the same artifact — a clean, interactive, visually credible dashboard — in a single session, with one person, using four prompts. And it produces something the traditional path rarely does: a tool that answers questions.

The attached files contain all three source datasets exactly as described above. The video walks through every prompt live. Everything you need to replicate this is in those two places.

If you want the version of this workflow that runs entirely inside Excel — no browser, no artifacts, same result — reply to this email and I will prioritise that as the next piece.

That is around 1,100 words — long enough to be genuinely useful, short enough to hold attention in a newsletter format. The structure mirrors the video but stands alone completely, so readers who never watch it still get the full picture. The Excel file teaser at the end doubles as your next issue's hook.

Thanks for reading!

Christian Martinez

About Christian Martinez

Christian Martinez is a Finance Senior Manager and AI for Finance Professor.

He has trained over 20,000 CFOs, Fractional CFOs and other senior finance leaders in AI for Finance through his online courses across AI Finance Club, AI Finance Accelerator , LinkedIn Learning and YouTube.

He is also the author of Smart Finance: Leveraging AI for Enhanced Financial Planning and Analysis and an international conference speaker, having presented at the World Finance Forum, World Summit AI, and the EMEA FP&A Summit.

He has over 10 years of experience in FP&A, Finance Transformation, and Finance Analytics at multinational companies across The Netherlands, Australia, and Mexico. He leads a team of 40+ developers and data engineers building advanced analytics tools for finance professionals across 20+ markets worldwide.

Christian holds a Master’s Degree in Finance and Data Science, Lean Six Sigma Green Belt, and Product Owner certifications.