Secrets to Building Excel Dashboards based on Non-financial Data
ฝัง
- เผยแพร่เมื่อ 20 ก.ค. 2024
- How to build Excel Dashboards based on non-financial data.
🔻 DOWNLOAD the completed dashboard and supporting data here: www.myonlinetraininghub.com/n...
🎓 LEARN MORE: Take my comprehensive Excel Dashboard course: www.myonlinetraininghub.com/e...
Dashboard Video Playlist: • Interactive Excel Dash...
Cross Filter and Highlight in Excel like in Power BI: • Cross Filter and Highl...
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
💬 Please leave me a COMMENT. I read them all!
🧟♀️ CONNECT with Mynda on LinkedIn: / myndatreacy
🎁 SHARE this video and spread the Excel love.
Or if you’re short of time, please click the 👍
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
0:00 Introduction
1:24 Overview of Dashboard
2:59 Get Data with Power Query
10:00 Model Data with Power Pivot
12:44 Headline Figures & Icons
26:32 Crew Count - Bar Chart
34:31 Map Chart
37:07 Flights by Vehicle Class - Column Chart
38:50 Crewed Missions - Column Chart
43:02 People in Space - Column Chart
48:36 Human Days in Space - Stacked Area Chart
51:32 Slicer Connections
52:12 File Download Link - วิทยาศาสตร์และเทคโนโลยี
Perfection, as usual ! I couldn't find such great tutorials anywhere, thanks for your great job !
Thanks so much, Anais!
Absolutely loved it. A ton of different techniques used in one amazing presentation. Looks like I now have to go back to the drawing board and redo just about every file I'm sharing. Thanks for the extra work:)
:-) glad you found some useful ideas, Frank!
That's just awesome, I never found someone explaining so beautifully to make dashboards in non-financial data, for me it is life saver for qualitative data.
Thanks 😊
So glad it was helpful, Saba!
Yet another incredibly efficient and effective video, with great and clear explanation, and brilliant result ! Always a real pleasure and learning experience combined. Thank you Mynda :-)
Thanks so much!
Love the passion you display in your video's. Extremely knowledgeable and clear
Glad you like them!
That was a great video Mynda, made better by not being based on financials 👏🏻👏🏻👏🏻
Thanks so much, Graham!
Brilliant, congratulations. Amazing Dashboard! Thank you Mynda!
Thanks, Luciano!
Brilliant tutorial, I have learned more in a few weeks from your videos than 3 years of fumbling about by myself. I will definitely be taking a couple of your on-line courses
That's wonderful to hear, Steve!
This is so great. Your tutorias enlighten me! Thank you so much and hope a lot of good things will come on your way!
Thank you so much!
Awesomely awesome Mynda. Great Job. So inspiring for me as I embark on a Dashboard assignment. Very useful techniques. Cheers!!!
Glad it was helpful, George!
Great tips, Mynda! Lots of gems in here that will be put to good use in both non-financial & financial dashboards. 😃 Thanks!
Great to hear, Jim :-)
As always, informative and impressive. Thank you for sharing Mynda
Thanks for watching, Medhi!
Fantastic Mynda, love the summary bar, must look at adding those to my dashboards.
Thanks, Deirdre!
Awesome tutorial Mynda! So many great tips and techniques. Will study it in depth for further insights. Thanks for sharing :)) Thumbs up!!
Glad you enjoyed it, Wayne :-)
Thank you very much for the tutorial! I used to copy/paste the pivot tables instead of creating new ones to riduce the size, but, as you mention, given the amount of data that Power Pivot can handle, and its efficiency, probably it's not worth... A trick I use to format the text box after updating the reference is the format painter, which I find faster than change all setting from scratch...
Hi Fabio, copying and pasting PivotTables doesn't reduce the size, what it does is avoid the chance of you accidentally creating another Pivot Cache, which is a copy of your source data held in memory that the PivotTable references. With regular PivotTables it's possible to create multiple Pivot Caches and be unaware of it. With Power Pivot the data model is the Pivot Cache, and it's not possible to duplicate it.
Thanks for the format painter tip! I'll try that :-)
Hi Mynda!Great Tutorial,Really Cool Dashboard And Helpful Tips...Thank You :)
Thanks for watching, Darryl!
Very good video and very useful. I was always instructed to add all formatting after the data was prepared and presented, though the header was useful to have headline data added to after, so understand the rationale for that. Thank you. I can use a lot of this for my work.
Cheers, Mark! Yes, I agree, better to leave formatting to the end, but I knew what the end result was ;-) The reason for formatting at the end is because you can waste a lot of time with formatting constantly changing as you finalise the design of your report, so it's best to wait until you have all the charts in place before you format.
Thank you very much Mynda for sharing with us🙏
You are so welcome, Jawad!
Hi Mynda, Great video as usual, lots of information and great tips to take in.
Glad you enjoyed it, Pat!
Thank you, I track training and your instructions are always clear and concise.
Great to hear, Edward!
Sooo many thanks for this tutorial and all other video's from you. it's just...wow.. Currently i am creating a big dashboard for the company with your help. I will blow them away i guess.. thank you so much!
So pleased to hear that! Best of luck with your dashboard!
Fantastic video, with lots of techniques covered. 😊👏🙏
Glad you enjoyed it, Ian!
An useful tutorial. Thank you very much Mynda
My pleasure 😊
Interesting... Many new things to learn with this Dashboard. Thanks.
Glad it was helpful!
Always helpful videos!!! Keep it up pls! 🙂
Thank you! Will do!
Another great example - thank you Mynda
Thanks, Kim!😊
Such a clear thinker. Great Vid!
Glad you think so!
Thanks so much for a wonderful tutorial.
My pleasure 😊
Your work gives pleasure!
So thankful for that, Kalin!
Thank you for all your efforts 🙏🏻
My pleasure!
Thanks a million Mynda
😆
Pleasure 😊
Outstanding Mynda!
Cheers, Chris 😊
You are just amazing! Many thanks !
Thanks so much!
Thanks for your presentation ! ! !
My pleasure!
Great tutorial!!! Thank you
I have a question, it is related to Microsoft planner and the ability to use excel to create a dashboard for the projects.
I know power BI can be used to get visuals by downloading the data using excel.
I would like to know if you have a tutorial on how to use excel dashboards using the data downloaded from planner.
Thanks again for your videos and tutorials 😊
Thanks, Allie! I don't have any tutorials on Microsoft Planner or dashboards using that data.
Many thanks for this great tutorial as always...I just want to ask if it is a MUST for the data files to be in CSV. format before power query can be used to set up the processes.
No, it can be text file, excel file, excel table in the same sheet or a named range, SQL database etc. Lots of options. Go to data menu and get data from file, folder etc you'll see all the options.
Use format painter, align and group function. Much easier than fiddling around with formatting.
Going to watch all your dashboard videos. 👍🏻 Very informative. This video was right at my level of learning in Excel. I like dabbling in VBA, so I probably would have recorded a macro on my first text format change on the dashboard then made a temp button to apply it as needed. That’s just me though!
PS. Come to think of it... the format paintbrush does the same thing! 😄
Yes, Format Painter is a nice trick. I forgot about that one :-)
Thank you for your time
My pleasure :-)
HI I love to watch your youtube it's always so amazing how you create the dashboard.
Can you show how to have a calendar dashboard with event title and it's cost? Thank you
Glad you liked my video 😊 I don't have any calendar examples, sorry.
As usual Smart work
Thanks again, Mohammed!
Always Great!
Thanks, Chris 😊
Mynda stand up and take a bow again for the excellent series - you certainly know how to take rabbits out of your hat
Aw, thanks so much!
Mynda, this is so beautiful and I returned after 2 years to try it again. I want to make a similar Header above my Pivot Table. I do not see any timestamps for a Header. Thank you for always giving us the Exercise File to Download. I always come to your Videos First before I go to any other video. Thanks
Aw, that's wonderful to hear. So glad you found this helpful 🥰
Hi - thank you - your trainings are FANTASTIC. I do have one question - I am using 365. I have created a dashboard that has a bar chart with @100 values. The problem I am having is when a slicer is selected the chart does not reduce in size based on the filtered data. So I end up with bars that are huge. How do I have the chart resize based on the filtered data?
Thanks for your kind words, Danielle. There's no built-in way to auto resize the chart as you describe. They're designed not to resize because then your dashboard would be adjusting all over the place with each click of a Slicer. If you want to do this then you'd need to write some VBA code to do it, but I'd recommend leaving it as is.
I am having to completely re-educate myself. 15 years ago the majority of my logic,formatting and processing was achieved via events and VBA code. Granted some of this functionality is new but I suspect much of it I just ignored because I could code it. I pretty much kept up with video but now will watch again and note the bits I need to know more about and then dive into your other brilliant videos to re-enforce what I learnt today. Thank you so much (Note to self - use a measure to fill in missing weekend data for stock prices?)
Already watched the playlist 16 vids for beginners just to remove the top layer of rust on my Excel "skills". And a Power Query one.
Great to hear!
Hi, very useful videos. Thank you. Is it possible to create a project management dashboard which is linked to primavera p6 database? It would be very helpful and also new amazing topic.
Glad you found them helpful! I'm not familiar with Primavera, but I suspect you may be able to connect to it, or at least a download of the data with Power Query.
Great video!
Glad you enjoyed it :-)
Every time I see your excel video, I wonder if you were really born here on earth! :)
Aw, you're too kind :-)
Excellent Job
Many thanks :-)
you are the best, always
Thanks, Roberta!
Thank you👍
You're welcome :-)
I really like your video
Great to hear!
Loved it
Great to hear!
Mynda, have you create a dashboard with Google Data Studio (GDS) ?
Whether GDS is not complete enough of features than Excel and what are the pluses and minuses of the two applications..
No, I haven't used GDS.
Very useful
Great to hear 😊
Hi this is uma
Ur video are understanding easily
Can u do videos on dax functions
Glad you liked them, Priya! If you'd like to learn DAX, please consider my Power Pivot course: www.myonlinetraininghub.com/power-pivot-course
Hi Mynda, thanks for the video, great stuff. I got stuck on calculating measure for Spacecraft Mission. I am using exact formula, but still getting below error message, kindly can you help
"Calculation error in measure 'CrewedMissions'[Calculated field 1]: The function COUNT takes an argument that evaluates to numbers or dates and cannot work with values of type String"
formula = IF(ISBLANK(COUNT(CrewedMissions[Spacecraft])),0,
COUNT(CrewedMissions[Spacecraft]))
Sounds like you didn't set your data types to numbers for the Spacecraft column when you imported the data with Power Query.
Awesome dashboard. Beautiful, some would say (me)! Not sure if you just did it to save explaining another function but you can use variables when creating measures. Rather than copying the formula twice, just assign to a variable and then use: if(isblank(variable),0, variable). Believe it makes a marginal difference on calculation times.
Also, is there a reason that some of these graphs can't go direct to chart rather than creating the associated tables? Or is it just for illustration in the video?
Finally, how frustrating that the cube functions don't operate properly: GETTING_DATA!. They'd be so much better than having to create tables for every text box. Even better would be the ability to create a data label that accesses the data model. I've created a function for doing it in VBA using adodb, with a connection string of the workbook data model which works great, but would be much better if Excel provided us this.
Thanks, Rico! In regards to VAR, when I teach DAX I start out with examples that look a lot like Excel formulas to help people feel comfortable with them before moving on to things like VAR. I was already concerned that there were a lot of techniques in that video that people new to PQ and PP wouldn't be familiar with.
Not all versions of Excel can go straight from data model to chart, so for now I tend to build the PivotTable first so that everyone can benefit from the video.
I agree, it would be great if a text box could support a formula. I suppose you could use CUBE functions for the header values and then reference those cells in the text box, instead of building PivotTables, but again, CUBE functions are even less relatable than PivotTables :-) That doesn't mean you should stop using them though. However, from a teaching point of view I try to take people on a journey, so the content on our TH-cam channel is more likely to be targeted at a beginner/intermediate level.
Very impressive
Thanks, Alex!
Question would this be sufficient for a document control dashboard where we would need live access so we know what documents are live and require review? and also would this update as we updated the tables or does it require to be manually updated. if so would you have any tutorials for creating of a dashboard for qualitative data with review dates etc
Any reports built with PivotTables require refreshing to update them. If you build your reports using formulas, then they update automatically and are visible to all users when co-authoring as explained here: th-cam.com/video/XfgDfUEV0fM/w-d-xo.html
Hola Mynda. Fabuloso, como siempre. Pero tengo una consulta. Yo hago a veces tableros en los que tengo los datos cargados en Power Pivot. Después, los trabajo directamente en la hoja de Excel con gráficos dinámicos. No inserto previamente una tabla dinámica y después, sobre ésta, inserto el gráfico. Veo que, habitualmente, siempre utilizas una tabla dinámica para después insertar el gráfico. ¿Es mejor esta manera que insertar directamente el gráfico dinámico, como hago yo? Muchas gracias.
You can go straight to Pivot Chart if you prefer. I find it clearer to build the PivotTable and then the chart for teaching, as I think it's easier to see the data in a Table first, but there is no efficiency gained either way, so please do whatever is easiest for you 😊
Hola Mynda. Muchas gracias por la respuesta y su prontitud. Te seguiré viendo, siempre.
Hello, Mynda I have been watching your guidance and it's really great to understand. My request is to kindly guide me how to create dynamic links of various rows in a sheet to another sheet?
Example: in Sheet 1,
Row 1: 24(col1), 29(col2), 37(col3), 58(col4) #(shape)
Row 2: 65(col1), 31(col2), 35(col3), 54(col4) #(shape)
Now while clicking on # of each row, it will automatically fill up the corresponding data of that row into another sheet under concerned column title. And it will changes automatically while clicking # of another row of sheet 1.
Can we solve this without using VBA, please guide...
Thanks
Glad you enjoyed my video, Prasun! Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Any email id for attaching the excel file, I don't get it at the forum where to attached the .XLS.
there's a button on the forum to add attachments below where you type your question.
@@MyOnlineTrainingHub Ma'am I have posted in the forum, id : rayprasu
Mynda, I got stuck at 45:42 video time. In my case after I wrote that measure, it populates 0 for every column for Male and Female. In your case I noticed that there are other values also. What mistake do you think that I must have made? It is wonderful but, in my case, it didn't take an hour to make, it take entire day as I was making some mistakes. Thanks
Great to see you're giving it a go. The best thing to do is copy and paste my measure into a text editor like notepad and then paste your measure there and compare them character by character. It's probably something simple.
@@MyOnlineTrainingHub Thank you, Mynda! I tried to paste your code also, but it still populates '0' and there are some steps we are missing. I noticed which is weird Grand Total is shown like this 144 1,147. Where did these numbers come from? your numbers are also the same except in my case all values are 0 but Total is showing 144 1,147. These Totals are coming out of Blank and if uncheck blank then these values go away. Same issue with the next Chart. Some step is surely missing.
My Code is
IF(ISBLANK(
CALCULATE([Count of Crew Name],
USERELATIONSHIP(DimDestinations[Destination],Missions[Destination]))),0,
CALCULATE([Count of Crew Name],
USERELATIONSHIP(DimDestinations[Destination],Missions[Destination])))
I didn't see any difference between your code and my code. I am missing something. Can you please see what the issue is? I had used the name as DimDestinations and you used DimDestination. That is the only difference. Thanks
I'd say your relationships are back to front. If you're still stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum Note: I probably won't see any follow up replies to this thread as I get tons of comments every day and this one will end up being buried.
Wow!
Glad you liked it 😊
How do you use dashboards to show comparisons between different samples, including ANOVA statistics?
Often column charts with error bars are used to visualise ANOVA statistics. With dashboards you could integrate Slicers to allow filtering of the data based on different groups etc.
May I know what is the reason to link the relationship between the tables in power query?
The relationships in Power Pivot enable you to use fields from multiple tables when building the PivotTables.
With all respect you're amazing teacher and I made dashboard with you. But I'm unable to understand that coding you did to establish relations in NEW MEASURES and why you connected that table initially. I want to learn that everything. From where should I start. I know basics and formulas. But power pivot and dax is totally new for me...
Please share playlist where I can learn it from basic 🙏🏻
You can learn DAX in my Power Pivot and DAX course here: www.myonlinetraininghub.com/power-pivot-course
Can Excel do features from Intime software which is time series database. What is that?
I'm not familiar with that software, so can't say.
We want dashboard for wind energy Forecasting. If possible would love to learn the same.
If you have some data I can use then maybe I can build it as one of my demos.
@@MyOnlineTrainingHub Yes...I work on this technology only which is Globally a very new technology. What is the way to communicate to you plz drop me yr address. Let's work on this, it will be a learning lesson for me and for Yu a new zone.
You can email it to website at MyOnlineTrainingHub.com
Great as ever, is it possible to do a Map for just one country, Britain for instance?
Thanks, Simon. Yes you can create maps for a single country as explained here: www.myonlinetraininghub.com/excel-map-charts
Excellent, thank you, will it work with data gathered via a VLOOKUP?
Yes, the chart doesn't care what the underlying formulas are.
"To infinity, and beyond!!!" - Awesome :)
😊 thanks, Andrew!
I need help with a formula: in a pivot table I would like to get the value (# of staff) of the Office, but the cell keeps changing because each year it adds a center. How can I create a formula for that... I used IF function but not sure what else to add with that. Am I using the right function? Please advise. thanks!
2019
Center #of Staff
NCTR 14
OCC 9
OC-IO 1
OCS 4
OEA 3
OES 2 7
2020 #of Staff
Center 6
NCTR 2
OCC 23
OCE 7
OC-IO 1
OCPP 1
OCS 1
Thank you!!!!
Hi Jean, Please post your question and sample Excel file on our forum that illustrates what cell you're trying to get and we can help you further: www.myonlinetraininghub.com/excel-forum
Followed this perfectly until the crewed mission table. When I use the calendar for the rows the years show up but as soon as I add the other field s the years disappear what am I doing wrong!!!
I figured it out ...it all came down to making sure date formats were set correctly !
There's quite a useful button called 'Format Painter' 😃
Thanks for sharing, Gary!
Do you sell your courses on UDEMY or any other website? Thanks
No. You can get my courses from my own site. That's where I have my course members' support forum where you can ask me questions and get help when implementing the techniques in your own work.
@@MyOnlineTrainingHub Thank you, Mynda! On your site, they are expensive. BTW, I just started following this video so far, I have watched 16 minutes and it is so good. Thank you for sharing this.
Glad you're enjoying the video.
Online training hub team. You have used many pivot tables this will increase fuel size. How the file size is reduced for big data sets.
The PivotTables don't make a material effect on the file size when working with big datasets. The cells the PivotTables occupy are typically insignificant. Power Pivot has a sophisticated compression algorithm that stores the data in Excel much more efficiently than loading it to an Excel worksheet. This is why it's able to work with millions of rows of data.
@@MyOnlineTrainingHub Excellent Explanation
😍😍😍
Glad you liked it 😊
Hii this is amezing dashboard can you please give me a file of this dashboard
Thank you. The link is in the video description.
Please what is a dimension table??🙈
:-) there's a level of assumed knowledge with Power Pivot because I wanted to keep the video under 1 hour. A dimension table contains the values that you want to summarise your data (facts/fact table) by. An example in this file is the DimCrew table which contains further information about the astronauts, like their country and region, which I used to create the map chart i.e. I summarised the count of crew names by country.
please add me to you training participants, thanks for sharing knowledge
My pleasure 😊
Can't complete this course. Some steps are missing.
You see every step in the video. What steps do you think are missing?
@@MyOnlineTrainingHub Mynda, Is it possible that the file you used is different than the file I used since I spent 2 days and I kept getting zero. I tried it at least 6 to 8 times but nothing worked. Please look at the code I put in the reply to your previous comment. I tried with your code also, but it didn't work. The file size is so big that I could not even upload it on your Forum. I just gave up. Thanks
Please post your question in the forum where you can also share screenshots that show the relationships in your model, the PivotTable and field list and your measure. From there I can help you further.
first
Hope you enjoyed it ;-)
@@MyOnlineTrainingHub yes my father is a fan dad
Excel dashboards are a fucking kancer I want powerbi
There's no need for that language and attitude. Here's my Power BI playlist: th-cam.com/video/bwtVFfYl4Tc/w-d-xo.html
I can't make formula people in space I get in cell male & female = 0 , not like your video I can't know what is wrong?
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Thanks for the perfect Video.
I have this error
Expression.SyntaxError: Invalid literal.
={Number.From(#Date(01,01,1960))..Number.From(#Date(12,31,2020))}
the function is #date with a lower case 'd'. Also, it should be year, month, day. e.g. #date(1960,1,1)