Turn a boring Excel into an interactive Power Bi dashboard in 3 steps
For years Microsoft Excel is my faithful companion in transforming and visualizing data. It's a fantastic product! Only the graphs have hardly changed graphically over the years. Moreover, it can be quite complex. In this article I describe how you can transform a classic Excel file into an interactive dashboard in just 3 steps thanks to Power Bi.
Microsoft: strong in data visualization for many years
Given the importance of data, it makes sense that interactive tools – such as Power Bi – to visualize this data are gaining popularity. Gartner - a renowned and globally active research agency - crowned Microsoft in March 2022 as the absolute 'Leader' in Analytics & Business Intelligence Platforms.
With this, Microsoft consolidates its strong position in the data visualization market. The American company can be found in the top right of this research for 15 years.
Excel as the basis for Power Bi dashboard
Now we come to the essence of this article: 'How can I visualize my data from Excel in Power Bi?' For this assignment, I chose a random data file provided by the Belgian government via 'Open Data' made available. Namely 'Given names of total population by municipality‘.
Below I show you how to use Excel data to create analyses in Power Bi in just 3 steps.
Step 1: Power Bi Desktop vs Power Bi service
Microsoft gives you two possible ways to use Power Bi: Power Bi service or Power Bi Desktop . Power Bi service is the web version of Power Bi that Microsoft makes available for free. That is why this version is also popularly referred to as Power Bi Online. Power Bi Desktop, on the other hand, is a free application to install and use for your PC. You can see the exact differences in the visualization below:
Both platforms are suitable for visualizing data from an Excel. Although I personally find the desktop version just a bit more pleasant to work with.
Step 2: Get data
A logical next step is to collect your data. Which in my eyes is child's play. Via 'Retrieve data' you will be guided step by step in retrieving your data.
Tip: in the web version you first have to click on 'sources'more options' before you can select a local file (e.g. Excel document).
Step 3: Visualize data in Power Bi
Voila, now that your data has been loaded into Power Bi, you can start creating a 'report canvas'. Choosing the right 'visuals' is of course crucial. You can choose from, among others:
- Stacked bar or column chart
- Clustered bar or column chart
- 100% stacked bar or column chart
- Line or area chart
- Waterfall Chart
- Scatter chart
- Pie or donut chart
Tip: keep the Excel file at hand (possibly on your second screen). This helps to keep the overview.
It may take some searching the first time, but below is an example report that I made in less than 5 minutes, based on the above Excel file:
Frequently Asked Questions (FAQ)
To conclude, I would like to answer some frequently asked questions about the use of 'Power Bi' below:
1. What are the alternatives to Power Bi?
Looking for an alternative to Power Bi? Then you can consider using tools such as Looker Studio – formerly Data Studio – (Google), Tableau (Salesforce) or Qlik Sense (Qlik) to try out. You can also use both Tableau (Public Edition) and Datastudio completely free of charge. The latter is – because it is also from Google – highly recommended for the visualization of data from Google Analytics, Google Ads and/or Google Search Console.
2. Can I also connect Power Bi to other sources?
Yes! With Power Bi you can connect to data from many different sources. In the Get Data dialog box (see screenshot above), data types are grouped into the following categories:
- File: Excel, Text/CSV, XML, JSON, SharePoint folder, …
- Database: SQL, Google BigQuery, ...
- Power Platform: Dataverse, Power Bi datasets, …
- Azure: Azure SQL Database, Azure Blob Storage, …
- Online Services: SharePoint Online list, Exchange Online, Dynamics 365, Google Analytics, Salesforce, LinkedIn Sales Navigator, …
- Others: Google Sheets, Survey Monkey, Microsoft Teams Personal Analytics, ...
Want to know more? Full list of Power Bi data sources.
3. Is Power Bi free? Or what license do I need?
Yes and no. At the moment there are 4 possible formulas: Free, Pro, Premium (per user) and Premium (per capacity). Below I explain the possibilities per type of license:
|Power Bi Free||Power Bi Pro||Power Bi Premium|
|Price per month *||Free||€ 8,40||€ 16,90|
|Refresh data||Manually||8x a day||48x a day|
|max. Data set||1GB||1GB||100GB|
* Prices per month per user. Click here for the most current prices.
4. Is there a mobile app available?
Absolute! Personally, I think the Power Bi mobile app is an additional reason to visualize your data in it. At any time of the day, you can consult your dashboards from any device.
Tip: When creating your report, choose 'Mobile layout', so that you can generate a mobile-friendly version of your Power Bi dashboard. The adjustments you make here will not affect the desktop view.