I made the SAME Dashboard in both Excel & Power BI - Here is how they compare...

let's settle this power bi or excel i'm going to show you the same report made using both excel and power bi and we will compare what is good with each tool so that you can understand how these two tools differ when it comes to data analysis and reporting let's jump in we will take a look at the sales dashboard and then we are going to compare both excel and power bi on seven parameters first let's take a look at the dashboards here is my power bi dashboard excel dashboard excel power bi excel no wait that's our dog excel power bi excel power bi let's start with the power bi dashboard here what we are showing is some of the key metrics that is total sales total cost profit profit percentage number of customers and sales per customer as of year to date totals as well as trend in the year to date we are also showing the sales amount broken down by country and we are showing some summary of the employee performance as well as product performance you can see all of this report at an overall level or go into an individual team so for example i can drill down to delhi steam and i can see who the members are what is their total sales how it is broken down by country how it is looking at the product level i did the same with excel here we are showing the total sales expenses profit profit percentage customers and sales per customer i've kind of slightly changed the format of some of these charts so for example total sales by country i've also included profit information as an extracts extra series so that it gives you more context and then our people information is in a table and then product information again slight change in format where i am showing by category and product so that it kind of looks a two level table and again i can go to delish to see what is happening within the dallas team let's go back to the power bi report here and quickly observe some of the finer aspects of this so the obvious things that are differing from excel are number one it has these pictures of the employees where i can actually not only just read the name but also have a picture of the staff so that i can understand who that person is and it makes it a more human report whereas within excel we are only listing the names so it's not obvious as to who is who the second thing with power bi is everything is interactive so if i'm looking at this report for delish team and i just want to understand how daily steam did within usa i can click on this column and it drills down into the usa data for the daily steam so again this table is rearranged based on the profit percentages within usa the totals have also changed now looking at just the usa proportion and all of the product sales have also adjusted this gives you so much more powerful insight without having to prepare multiple reports you just have one report but you can drill down you could do the same with any product also for example i can drill down to the raspberry chocolates and then see how that amount is coming 108 000 whereas within excel we are not able to do such a detailed drill down without adding some extra slicers but it does give you everything that we are seeing on the power bi report plus a little more because of the flexibility and the layout of excel so for example here for the key metrics not only i'm showing the totals but also i'm showing month to month increase or decrease from the previous month so we are right now reporting up to june so i'm showing you total sales are 3.3 million for the dallas team and they are down 10 and again the ytd trends are using the native excel sparklines so they they look a little better than these area charts so now that we have seen both of these reports let's go and compare them on the seven parameters the seven parameters are data preparation calculations visuals interactions sharing updating and security when it comes to making dashboards for business reporting i believe these seven parameters are critical for you to decide which tool or which software to use the first one is data preparation as mentioned earlier for both dashboards i'm using the same data set the data came from a an excel file that has all the tables but it could have come from a database or or a cloud source as well it would still work i'm using power query both within power bi as well as in excel to connect to this data source fetch the data and adjust any mistakes or problems with the data so within excel i'm using go to data and then get data option to get the data so all of these queries are connected to that excel file and if that excel file changes so for example next month we add some new sales information i can just refresh my query and the dashboard would automatically update same with power bi it is connected to that excel file so if there is new information added in that excel file i just refresh my power bi and it will fetch that data so with respect to data preparation i think we can give one point for both power bi and excel because they are equally capable of processing the data the next one is calculations in order to make this dashboard there needs to be a lot of numbers calculated like your total sales total profit profit percentage sales per customer etc and i'm using the dax measures which is part of power bi to calculate these numbers so as you could see from here i have got my measures on profit percentage profit target sales per customer etc and these measures are very very simple and easy to create and once you create them you can put them against any combination and it will work beautifully within excel also i am using the excel's data model and power pivot capabilities to calculate things so here i'm calculating the same total cost total amount total profit profit percentage and showing those measure values there in the pivot tables but within excel we have a little more flexibility because the calculations directly feed the pivot table i can use pivot table to do some additional calculations on the top of it so for example here i'm calculating month on month changes as an outside value in my pivot table and then using this information to enhance my dashboard we could theoretically do this within power bi as well but that requires adding more dax because there is no other way to add such ad hoc calculations within power bi i'm going to give excel 1.2 simply because it is a little more flexible when it comes to doing the calculations and power bi will give it one point for calculations the third criteria is visuals how well can we design these visuals in both excel and power bi report when it comes to excel visuals they look very polished this is simply because excel charts have such a rich legacy and they have evolved over time so nowadays to format and present charts it is a really simple task that said they lack some of that visual finish like that pop or wall factor that is obviously present in the power bi visual so for example i could enhance my visual with some pictures make it more colorful and interactive despite all the visual flair that is in power bi i find that working with visuals within power bi is really tricky and hard the power bi visuals have not quite evolved and they customizing or working with them can be a little bit tricky from time to time for that reason let's give power bi one point and excel visuals 1.2 the next aspect is interactions power bi is interactive all over the interface so i can click on anything and it will automatically drill down or filter the data to show me what is happening with respect to that sales person or country whereas our excel dashboard is not that interactive we could only pick a team and then the entire report will change for that team as i mentioned early on we could add a few more slicers to filter by sales person or product category or country but that would clutter up our dashboard and there will be less space to show the actual information for this reason i'm going to give power bi one point for interaction and excel 0.75 the next aspect is sharing once you create this power bi dashboard you can share it in a few different ways you can email this file to your colleagues so that they can read this report alternatively you can publish this report and then send them a link to the report so here i have published it on the web so anybody going to this particular web page can read my report without having access to my original data this report is still interactive so they can go into an individual country like india or an individual employee to understand what's happening with them how those combinations work as far as our excel dashboard is concerned you can only share this by emailing the files but they will also have access to your pivot tables and maybe underlying data as well if you have excel online or onedrive or some other solution like sharepoint you can theoretically publish this dashboard on the web so they can go to your web page and read it but not everything that you create here can be seen or used equally well on the web excel this is simply because web excel is usually a few steps behind the traditional desktop excel as far as sharing is concerned you need to keep in mind some of the costs as well if you want to share your excel file with a colleague or a client you can just email the file to them and in all likelihood they already have excel installed so they will be able to right away read the information that you have presented to them on the other hand when you share the files with clients through power bi in order for them to read it on the web or on the power bi apps you will either have to go with the paid program within power bi so the cost is a little bit more with power bi but as you could clearly see both platforms have their own ups and downs when it comes to sharing but i find that sharing within power bi is slightly better because of the way it is set up so i'm going to give power bi one for sharing in excel 0.75 for sharing the next aspect is updating this dashboard updating the dashboard in either excel or power bi is a simple matter of changing your original data and clicking the refresh buttons within excel i can go to my data tab and refresh and that will go and fetch the new data and update it here within power bi we can again trigger the refresh from the power bi desktop or we could even set up some rules that will automatically fetch the data and refresh it so i'm giving one point for updating for both excel and power bi the last aspect is a security how secure your data is when you are sharing this information within power bi you could be pretty sure that whatever data you are sharing your audience will only be able to see it if you let them see it so for example i can set up a rule within power bi when a manager reads this dashboard they will only see their employee information they will not be able to read the sales data of other teams whereas my excel report is visible for everybody once you share this file anyone can read it and understand the information they can even go back to the original data if they have access to the files and go and even change some of the things so excel doesn't really create a very secure platform a very private platform for you to publish confidential data or make confidential calculations for that reason i'm going to give one point for security with power bi and 0.5 square excel so there you go the total scores are 7 for power bi and 6.4 for x so what is the conclusion if you don't need a highly interactive report or if you don't have to worry about security because you're only sharing among colleagues and you have another way of ensuring the security of the data then just use the excel dashboards because they are more flexible you can add calculations as you need it and it will give you more options to work within the visual space on the other hand if you need to share information with colleagues or clients that are spread across the world or multiple places and you want to make sure that the interactions are polished the information is presented in a more dynamic updating manner then go with power bi because it gives you that flexibility to work in that such a dynamic environment if you want to know how to make dashboards using power bi check out my video that is linked up there and if you would like to learn how to make dashboards using excel check out this video thank you so much and have a beautiful day my friend

Channel: Chandoo

Views: 32,506

Rating: 4.9358287 out of 5

Keywords: chandoo, chandoo.org, Excel, spreadsheets, excel vs power bi, excel or power bi for dashboards?, which is better - excel or powerbi, powerbi vs excel, compare excel with power bi, same dashboard made in both excel and power bi, dashboard capabilities of Power bi, advantages of power bi, disadvantages of powerbi, power bi

Id: OKSIVfMF0QI

Length: 13min 43sec (823 seconds)

Published: Thu Sep 16 2021