Power BI Service - Going From an Excel Spreadsheet to a Proper Report

Every organisation has that person who runs half the business from an Excel spreadsheet. Maybe it's a finance manager with a workbook tracking monthly revenue. Maybe it's an ops lead with a spreadsheet of supplier data going back five years. The data is good. The format is a nightmare. Rows of manually-coloured cells, merged headers, formulas referencing cells three tabs away.

The question we get asked most often in our Power BI consulting work is some version of "how do I turn this Excel file into something people can actually interact with?" Microsoft has made this specific workflow - Excel to Power BI report - genuinely straightforward. But there are a few things nobody tells you about the process that trip up first-timers.

The Basic Flow

Here's the path Microsoft has built out. You start in the Power BI service (the web version, not Desktop), upload an Excel file, and Power BI creates a semantic model from your data. You then build visuals on top of that model using the browser-based report editor, pin those visuals to a dashboard, and share the whole thing with your team - including directly into Microsoft Teams.

The official tutorial from Microsoft walks through this with a sample financial dataset, and it makes it look easy. To be fair, when your data is clean and well-structured, it genuinely is easy. The problems start when your data isn't.

Getting Your Excel Data Ready

This is where 80% of the real work happens, and it's the step most people want to skip.

Power BI wants your data in a flat table. One row per record, one column per field, a header row at the top, no merged cells, no subtotal rows mixed in with data, no blank rows used as visual separators. If your Excel file looks more like a formatted report than a database table, you've got prep work to do.

The specific steps are:

1. Strip out any merged cells. Unmerge them and fill down the values.
2. Remove any subtotal or grand total rows. Power BI will calculate these for you.
3. Make sure your header row is in row 1, or at least that there's no junk above your actual data.
4. Format your data range as a proper Excel Table (Home > Format as Table). This is the single most important step - Power BI handles named Tables much more reliably than random cell ranges.
5. Check your data types. Dates should be actual dates (not text that looks like dates). Numbers should be numbers. Currencies should be consistent.

I know this sounds tedious. It is. But I've lost count of the number of times a client has said "Power BI keeps getting my data wrong" and the issue was that their Excel file had three different date formats across 10,000 rows. Fix it once in Excel and everything downstream works properly.

Uploading and Building the Report

Once your data is clean and formatted as a Table, the upload process is quick. Sign into the Power BI service, go to your workspace, select New Item > Report, choose Excel as your source, and upload the file. Power BI runs it through Power Query, lets you select which tables to import, and creates your semantic model.

From there, you're in the report editor. The experience is drag-and-drop. Your columns show up in the Data pane on the right. Drag a numeric field onto the canvas and Power BI creates a chart. Drag a date field onto that chart and it breaks down by time. Drag a geographic field and it'll create a map.

A few things worth noting here:

Power BI detects field types automatically. Numeric fields get a sigma symbol, geographic fields get a globe icon, dates are recognised as dates. This usually works well, but occasionally it gets things wrong. A column of postcodes might be treated as numbers (and summed, which is obviously nonsensical). Check the field types in your model and fix any that are wrong before you start building visuals.

The web editor is good enough now. Two years ago I would have told you to use Power BI Desktop for anything serious. The web-based report editor has caught up significantly. For the Excel-to-report workflow, you can do everything in the browser. You only need Desktop for more advanced data modelling or custom visuals.

Start simple. I've watched people upload their data and immediately try to build a complex multi-page dashboard. Resist the urge. Build one chart. Check that the numbers match what you expect from your Excel file. Then build the next one. Catching a data issue after you've built twelve visuals is much more annoying than catching it after one.

Building Useful Visuals

The temptation with Power BI is to use every visual type available. Don't. The best reports we've built for clients use three or four visual types consistently.

Column and bar charts for comparisons. Sales by region, revenue by product, headcount by department. These are reliable, easy to read, and everyone understands them.

Line charts for trends over time. Monthly revenue, quarterly growth, weekly ticket volumes. Power BI handles date hierarchies well - you can drill from years down to months down to days without creating separate charts.

Maps for geographic data. If you've got data broken down by state, city, or country, maps make the distribution immediately visible. Just make sure your location data is clean - "NSW" and "New South Wales" in the same column will give you duplicate bubbles.

Cards and KPIs for headline numbers. Total revenue, average deal size, number of active customers. Put these at the top of your report so people can see the summary before diving into the detail.

The formatting options in Power BI are surprisingly deep. You can adjust colours, fonts, shadows, backgrounds, and spacing for every visual. My advice: pick a consistent style and stick with it. Matching your organisation's brand colours goes a long way toward making a report look professional rather than like a default template.

Pinning to Dashboards and Sharing

Once your report is built, you can pin individual visuals to a dashboard. A dashboard in Power BI is a single-page overview that pulls in visuals from across multiple reports. Think of it as your executive summary.

The sharing options are where things get interesting for teams:

Direct link sharing works like any other cloud document. Send someone a link, they open it in their browser. They need a Power BI Pro or Premium Per User licence to view it, though, which is where some organisations hit a wall. If you've got 200 people who need to see the report, licensing costs add up.

Teams integration is genuinely useful. You can share a report or dashboard directly into a Teams channel or chat. The recipient sees a preview inline and can click through to the full interactive report. For organisations already running Microsoft 365, this fits naturally into how people actually work.

Export to PowerPoint is the fallback. Plenty of executives still want a static deck they can flip through. Power BI can export your report pages as PowerPoint slides, though you lose the interactivity.

Common Mistakes We See

After helping dozens of Australian businesses move from Excel to Power BI, there are patterns in what goes wrong.

Not refreshing data. When you upload an Excel file, Power BI takes a snapshot. If your Excel file changes, the report doesn't update automatically unless you set up a refresh schedule or use a gateway. People build a beautiful report, present it to leadership, then come back a month later and the data is stale. Set up refresh from day one.

Overcomplicating the first report. Start with a single-page report that answers one or two questions. "What were our sales last month?" is a better starting point than "Give me a complete view of all operational metrics across every department." You can always add pages and complexity later.

Ignoring the semantic model. The semantic model is the thing that sits between your data and your visuals. If you get the model right - proper data types, clear naming, good relationships - building reports on top of it is fast. If you skip model cleanup, you'll fight with every visual you create. Spend the extra thirty minutes getting the model right.

Not thinking about mobile. A surprising number of people access Power BI reports on their phones. The default report layout is designed for desktop screens. Power BI has a mobile layout editor - use it. Otherwise your carefully designed report looks like a mess on a phone screen.

When to Graduate Beyond This Workflow

The Excel-to-service workflow is great for getting started quickly. But there's a ceiling. When you find yourself needing multiple data sources joined together, complex calculated columns, row-level security, or performance optimisation for large datasets, it's time to move to Power BI Desktop and a more structured data pipeline.

For many Australian businesses, that transition point comes somewhere around the six-month mark. The initial reports prove the value, leadership wants more, and suddenly you need a proper data strategy. That's a conversation we have regularly as part of our business intelligence consulting work.

If you're just getting started with Power BI, or you've got Excel workbooks that need to become proper reports, our Power BI consultants can help you through both the quick wins and the longer-term planning. There's no shame in starting with an Excel upload - some of the most valuable dashboards we've built for clients started exactly that way.

The key is just to start. That finance manager's spreadsheet isn't getting any simpler on its own.