Power BI for Excel Users: Two Steps to Your First Dashboard

If you live in Excel, you have had this moment. Somebody asks for “the numbers by region, by month, and can we also see it by product?” and you go build three more pivot tables, three more charts, and a tab named Summary_v4_FINAL. Then next month the file shows up with one extra column and the whole thing falls apart.

That is exactly the problem Power BI for Excel users is built to solve. Excel is not finished — I still build plenty of client reporting in it. But there is a line where a workbook stops being a spreadsheet and starts being a database with a spreadsheet stapled to the front of it. Past that line, Power BI does the same job with a fraction of the monthly rework.

Here are two steps that take you from “I have heard of Power BI” to a real, refreshable dashboard. Both are things I walk people through in class, and both are doable this afternoon in Power BI Desktop, which is a free download.

Step One: Power BI for Excel Users Starts in Power Query, Not on the Canvas

The biggest mistake I see is importing a messy sheet and then trying to fix it with visuals. Clean it on the way in instead.

In Power BI Desktop, go to Home > Get data > Excel workbook, pick your file, tick the sheet or table in the Navigator window, and then click Transform Data — not Load. That one button is the whole tip. It drops you into the Power Query Editor before anything is imported.

Once you are in there, the four moves that handle most real-world files are:

• Home > Remove Rows > Remove Top Rows to strip off the logo, the title line, and the blank row somebody left under it.
• Home > Use First Row as Headers, so your columns are named instead of Column1, Column2.
• Click the little ABC/123 icon on each column heading and set the real data type. Dates stored as text will quietly break every time-based visual you build later.
• Select your month columns — the ones running Jan, Feb, Mar across the sheet — and use Transform > Unpivot Columns. Twelve wide columns become two tall ones, Attribute and Value. This is the step that makes everything after it easy, and it is the step almost nobody does on their own.

Then Home > Close & Apply. Every one of those steps is saved in the Applied Steps list on the right, so next month you press Refresh and Power BI redoes all of it in order. No re-cleaning.

The gotcha: Power Query hard-codes the full path to your source workbook. Move the file, rename the folder, or let somebody save it to their desktop, and Refresh fails with “we couldn’t find the file.” Fix it under Home > Transform data > Data source settings > Change Source. Better yet, park the source file in one fixed folder or OneDrive location on day one and leave it there.

Step Two: Build the Dashboard Once, Then Let Slicers Do the Work

Back in Report view, resist dragging raw fields onto charts. Start with one measure. Go to Home > New measure and type:

Total Sales = SUM(Sales[Amount])

Format it once — Measure tools > currency, zero decimals — and it looks right in every visual you drop it into. Fields you drag in directly get summed implicitly, and you end up re-formatting the same number in six places.

From there, build three things: a Card visual with Total Sales in it, a Clustered column chart with Region on the X-axis and Total Sales in Values, and a Slicer with Region or your date field on it.

Here is the payoff, and it is what sells Power BI to Excel people in about four seconds: click one region in that slicer and every visual on the page filters at once. No Report Connections dialog, no wiring each slicer to each pivot table and forgetting one. Ctrl+click selects several at a time, and hovering the slicer gives you an eraser icon to clear it.

The gotcha: sometimes you want a visual to ignore the slicer — a company-wide total card sitting next to the filtered charts, for example. Select the slicer, go to the Format ribbon > Edit interactions, and small icons appear above every other visual. Click the circle-with-a-slash on the card and it stops responding. Second one worth knowing: bar charts sort by the measure, largest first, so your months come back in a nonsense order. Select your Month Name column, then Column tools > Sort by column > Month Number, and they behave.

Plenty more on the calendar

If you want to watch all of this happen live instead of reading it, I am teaching Microsoft Power BI for Beginners & Excel Users on Tuesday, September 15. It is aimed at people who know Excel well and have never opened Power BI — getting data in, cleaning it in Power Query, building your first visuals, and publishing so other people can see them.

The full current schedule is on the front page of PCWebinars.com, and new dates go up as they are confirmed. Register for this one, or have a look through the rest and find the session that matches what is on your plate right now.

About your trainer

I am Tom Fragale. I have been a computer trainer and consultant for more than 30 years, and I have taught well over 30,000 people how to get more out of the software they already own — Excel, Access, Power BI, Word, Outlook, and now the AI tools that sit on top of all of it: Claude, ChatGPT, and Microsoft Copilot. My background is applied, not theoretical. Alongside teaching, I build real databases and reporting systems for clients in Excel, Access, and Power BI, so the examples I use in class are the problems I ran into last week, not textbook exercises. I also publish free tutorials on YouTube and longer courses online.

If any of this was useful, there is a lot more of it — along with the full live schedule — waiting for you at PCWebinars.com.