Power BI vs Excel Dashboards - And the winner is...

one of the most common questions i get is should i be using power bi to build dashboards instead of excel now if you ask a power bi fan this question they'll tell you you should definitely use power vi and if you ask an excel fan they'll tell you you should definitely use excel but to get an unbiased answer you need someone who knows both and i've taught thousands of people how to build dashboards in both power bi and excel and i believe they each have a time and place so in this video i'm going to cover the differences and pros and cons of each and you can be sure it's going to be a balanced view the first thing to consider is the learning curve required to build dashboards in each tool power bi requires some new skills in the form of power query to get and transform the data and powerpivot to model it then you need to know how to write measures using the dax formula language plus there's a whole new charting engine to learn as well as how to share reports and how to schedule their refresh as you can see with lots of features comes lots of learning excel on the other hand is relatively straightforward especially if you're already familiar with formulas pivot tables and charts because if you have these three skills then you already have what it takes to build dashboards in excel however if you want to benefit from the same power query and powerpivot tools available in power bi then you'll be pleased to know that excel has these exact same tools which means if you work with messy data you can use power query to automate the cleaning and gathering of that data and if you work with big data then you can use the powerpivot model in excel to handle millions of rows of data and write more advanced tax measures just like in power bi in terms of charting power bi has a huge selection of charts which are called visuals there are multiple integrated mapping visuals available you can create your own custom visuals or use a custom visual from the shared custom visuals gallery excel also has a huge selection of charts but it's light on mapping visuals so if this is important to you then keep that in mind there's no way to build your own custom chart in excel but because they're highly flexible there are workarounds for almost everything the first area where power bi shines above excel is the interactivity available but the customization options available in power bi are vast compared to excel let's take a look in power bi we can format slices to display as a list or a drop down or buttons like we have in excel and for date slices like this one we have between less than or equal to and greater than or equal to you can also nest fields and show them in a hierarchy then in the formatting pane we can set the slicer to allow the user to only select one item as well as many other formatting options whereas excel slicers are very limited in that you can have buttons and if you have date slices you can have a timeline but unlike power bi there's no setting option to prevent the user from selecting more than one item needless to say excel sizes need some love from the microsoft excel team the next amazing feature available in power bi is the ability to click on an element in a visual and have other visuals on the page cross filter and highlight that selected value here you can see as i click on a brand in the first visual the bars in the second visual highlight the data for the selected brand effectively the bars in the visual behave like a slicer in this example there's only two visuals but this functionality can be applied to all visuals in a dashboard or you can pick and choose which visuals respond to cross filtering and highlighting in excel you can mimic this behavior to a degree using slicers power pivot tax measures and some clever chart alignment but it's not as versatile or user friendly as it is in power bi because only the slicer responds to the clicks in power bi we can drill down to lower levels in visuals in the top left of the power bi visuals there are drill down buttons available when there's more than one field in the axis labels similarly in excel you can use the expand and collapse buttons to drill up and down when there's more than one field in the access labels and lastly power bi's drill through feature allows you to navigate to another view of your report and filter at the same time here you can see i'm right clicking on the column for the regular class of the catalog channel drill through takes me to another report which is now filtered for the catalog channel and regular class similarly i have another report set up for brands which i can drill through and filter on the supplier fabricum and automatically my brand report is filtered for fabricum because that's the supplier i chose to drill through on now unfortunately we don't have the equivalent of this feature in excel you could set up hyperlinks to navigate to reports on other sheets in your excel file but the filtering wouldn't get applied at the same time when it comes to analytics power bi has some amazing built-in visuals that allow you to understand the composition of your data and drivers of metrics but nothing beats the flexibility of excel when it comes to entering data and analyzing it with formulas and pivot tables let's take a look at what's available in power bi we have matrix tables which are similar to pivot tables available in excel but with less functionality power bi's decomposition tree lets you visualize data across multiple dimensions automatically aggregating data and enabling drill down into your dimensions in any order it's a valuable tool for ad-hoc exploration and conducting root cause analysis with insights you can tell power bi desktop to explain increases or decreases in charts you can see distribution factors and get fast insightful analysis about your data in this example power bi has found what's contributing to the increase in gross profit which pops up as a waterfall chart power bi's key influences visual helps you understand the factors that drive a metric you're interested in it analyzes your data ranks the factors that matter and displays them as key influences for example here i've asked it to rank what factors influences sales to increase excel on the other hand has far more advanced pivot table abilities including the ability to write custom dax measures with power pivot pivot tables excel's grid and formulas are vastly more flexible than power bi offers and this means excel is still the best tool for financial modeling budgeting and other ad hoc analysis excel also has some ai-powered tools including ideas which analyzes and provides high-level visual summaries that identify ranks trends and patterns as well as outliers you can also use natural language to ask excel questions of your data just like you can in power bi so you can see that power bi has some amazing tools but excel offers unparalleled flexibility one area where excel doesn't come close to power bi is with sharing of reports with power bi you can easily view reports on any device or even create your own app to view reports users are required to log into the power bi service which then checks their permissions and only displays the appropriate reports you can even build one report and then restrict what data each user can see based on their role for example if you have regional managers you can set reports to automatically filter the data to a specific manager's region upon opening of that report with excel the options for sharing are limited to emailing files saving them on shared network drives where others can open them or embedding reports in web pages using the web app the downside is there's no real way to secure your data even if you password protect the file a savvy user can still get to the data so if securing the data is critical your best option is power bi both excel and power bi have the power query tool for automating the getting and transforming of data power bi can also connect to real-time streaming data sources but then so can excel you just need to know how to set it up lastly for a limited number of data sources power bi has direct query which allows direct connection to the source that is there's no need to import data to power bi and this is great for working with very large data sets power bi and excel can work with millions of rows of data because both have the powerpivot model of course if you have a version of excel that doesn't have powerpivot then you're limited to the million also rows in the excel worksheet power bi has built-in refresh options which vary depending on the source of the data you can schedule a refresh or if the data is stored online the refresh is automatic now excel doesn't have any built-in refresh options for regular pivot tables but you can program it with vba or if your pivot tables are power pivot based you can schedule a refresh to get a power bi account you need a work email address gmail outlook hotmail etc aren't allowed however there are workarounds available to individual users where you can sign up for a trial office account which comes with a suitable email address installing the power bi desktop software and getting access to company databases typically requires its involvement excel on the other hand doesn't require any additional software or add-ins to build dashboards which means you can be up and running in no time probably one of the biggest barriers to using power bi is that there's a monthly subscription cost for both the creator of the reports and the consumers of your reports it currently starts at us 10 per person per month and while that doesn't seem like a lot it can quickly add up there are pricing plans for corporates that bring the cost per person down so if you're looking at a lot of users this may be an option excel of course is already available on nearly everyone's computer that requires reports so typically there's no additional cost involved now both power bi and excel have far more features than those i've covered here and you may find some of those other features sway your preference the purpose of this video was to compare these tools purely for building dashboards so i focused on those key features i hope you found this video useful if you liked it please give it a thumbs up and subscribe to my channel for more and why not share it with your friends who might also find it useful thanks for watching

Channel: MyOnlineTrainingHub

Views: 138,885

Rating: 4.972991 out of 5

Keywords: power bi dashboards, excel dashboards, excel vs power bi

Id: 0nAQ7gPO_6Y

Length: 11min 55sec (715 seconds)

Published: Mon Nov 09 2020