one of the most common questions I get is should I be using power VI to build dashboards instead of excel now if you ask a powerbi fan this question they'll tell you you should definitely use power VII 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 powerbi 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 powerbi requires some new skills in the form of power query to get and transform the data and power pivot 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 power pivot tools available in powerbi 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 power pivot model in Excel to handle millions of rows of data and write more advanced Dax measures just like in powerbi in terms of charting powerbi 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 work around for almost everything the first area where powerbi shines above Excel is the interactivity available but the customization options available in powerbi are vast compared to excel let's take a look in powerbi we can format slices to display as a list or a dropdown 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 slices are very limited in that you can have buttons and if you have date slices you can have a timeline but unlike powerbi 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 powerbi 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 slices power pivot Dex measures and some clever chart alignment but it's not as versatile or user friendly as it is in powerbi because only the slicer responds to the Clicks in powerbi we can drill down to lower levels in visuals in the top left of the pobi 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 filled in the axis labels and lastly powerbi 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 fabricom and automatically my brand report is filtered for fabricom 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 powerbi 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 powerbi we have Matrix tables which are similar to Pivot tables available in Excel but with less functionality powerbi 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 powerbi desktop to explain increas inrees or decreases in charts you can see distribution factors and get fast insightful analysis about your data in this example pobi has found what's contributing to the increase in gross profit which pops up as a waterfall chart powerbi 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 influencers for example here are I've asked it to rank what factors influence the 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 powerbi 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 powerbi so you can see that powerbi has some amazing tools but Excel offers unparalleled flexibility one area where Excel doesn't come close to to powerbi is with sharing of reports with powerbi you can easily view reports on any device or even create your own app to view reports users are required to log into the powerbi 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 pulso protect the file a Sava user can still get to the data so if securing the data is critical your best option is powerbi both Excel and powerbi have the power query tool for automating the getting and transforming of data powerbi can also connect to realtime 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 powerbi has direct query which allows direct connection to the source that is there's no need to import data to powerbi and this is great for working with very large data sets powerbi and Excel can work with millions of rows of data because both have the power pivot model of course if you have a version of excel that doesn't have power pivot then you're limited to the million or so rows in the Excel worksheet powerbi 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 powerbi 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 powerbi desktop software and getting access to company databases typically requires its's 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 powerbi 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 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 powerbi and Exel 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 [Music] watching