❓What's one unique dataset you're eager to visualize using the techniques from this tutorial? Master Excel now with 20% off all courses until Feb 15, 2024: bit.ly/excelcourses130224
@@MyOnlineTrainingHubWould an And Function make sense here? Like AND($D10=Analysis!$A$10;Analysis!$A$12="") With this function the purple frames will only be shown, if you select a name from the slicer.
@@MyOnlineTrainingHub for some reason, when i update the data, this formating trick get modified and will only work for the first column. no idea why is that. Also when i dont have any name selected - its still showcasing the selection - while there is none.
Thanks Mynda, One tiny tiny remark : it would be better to drop the name in the filter section of the pivot table and use it for the conditionnal formatting instead of using another pivot table with names in the rows. bcz, if the prson didn't select any name from the slicer, we will still have "Alex" being highlighted in the table, but if we add a filter to the first Pivot table, we can use it as the base of our conditionnal formatting. The filter will either show "All"" or "the active name in the slicer"
Love that idea 👍 I sometimes build an error message into the dashboard if people select multiple names none of the charts display, but I was on a time constraint with this video 😉
Taking that idea a little further -- the conditional formatting after Refresh only shows it being applied the the name, not the other PT fields in the video. Sorry I haven't had time to play with the download yet but I've been bitten by that quirk before and so I was specifically looking for it. Would using the name as a filter instead address that also?
I have tried to solve this issue with the AND function in the Conditional Formatting. My second logical condition would be to see that A12 in the analysis sheet is empty, which happens only if you select one name. It works fine for me.
Very well-created Dashboard. Brilliant Colour Scheme. Excellent use of Data Bars and subtle use of Gradient to highlight figures. The Conditional Formatting to highlight the Sales Agent is fantastic. Simple, yet very effective. Thanks Mynda....,
Amazing! I didn't realize that such good looking and insightful dashboards can be built using Excel data. Thanks for making this YT video. Truly appreciate your time and efforts!👍
Thanks, Mynda You are too kind. I wish you all the best, now and always I just love learning from you, a plethora of knowledge that you impart in an impeccable fashion, easy to comprehend
Hi Mynda, I saw your video on how to make a dashboard in 3 steps: really very useful and educational. But I have a problem when I update the pivot table with formatting both with data bars and with icons (you know I tried to put the traffic lights ...) the formatting of the columns with the icons is lost. I use the Mac version 16.90. How can I keep this formatting even after updating the data? Many thanks for all the suggestions you want to give me. Best regards Antonio
Thanks, Mynda, for such a prompt response. Much appreciated. you are "THEEEE BEST" tutor for these courses. I do not see any other videos from anyone if I am serious about REALLY learning a concept. Mynda, I cannot see the sample file , after checking again. What am I doing wrong?
Hi Mynda, sorry to be so bold but I have content suggestion for 1 video which I think many people would find very useful. I would like to create a quick comparison spreadsheet for buying a car, where you compare costs of HP, PCP and PCH (leasing). I am not sure where to start. 1. I think just creating 3 separate tables perhaps. And then using charts 2. Another approach, maybe having 1 table and then use pivot charts. But for this task I don't think this approach is the most suited, probably 3 separate tables still best. 3. I am thinking if the What if analysis tools are suitable for this? I am a bit confused where to start with this. I think a video on this would be fantastic, as many people are faced with tough budgetary questions when buying a car.
Do you have any videos on exporting expiries from multiple sheets onto a dashboard. I’m looking for a traffic light system that summarises upcoming expiries, expired and current stock holdings
You can use Power Query to first append the data from the sheets together into one table, then you can extract the data you want with either Power Query or a PivotTable, and use Conditional Formatting for the traffic lights. I don't have one video covering all these steps, but I do have individual videos on these topics. Here's the Power Query video: th-cam.com/video/YOC-pEIuHpA/w-d-xo.html Here's the PivotTable video: th-cam.com/video/vQlFiLUaw4k/w-d-xo.html Here's the Conditional Formatting video: th-cam.com/video/Rzz9PyfwiVQ/w-d-xo.html
This was amazing! I’ve been really wanting to do something like this for my board reports, but didn’t realize it could be done so nicely in Excel. I was thinking we’d need another subscription to a different tool.
I like that name highlight trick using the conditional formatting and the slicer! I don't quite understand why picking that top cell makes it check the entire column for the name though. I'll have to look at that more closely.
Depends how many departments you have. If there's only a few, you could use a simple line or clustered column chart. If there's a lot, then maybe sparklines.
Hi Mynda, I'm wondering if you have dashboards, pivot tables, etc. that are used with screen readers and would be accessible for people with disability?
great question. I don't have anything specifically for this, sorry. I'm not sure how good Excel is at reading charts. You could always download one of my files and test it out.
Thanks for your reply, I sincerely appreciate it. We've recently had some internal training regarding accessibility and well it's complicated. Nothing we do on a day-to-day basis will currently work. More chances to learn tho 👍
Like this: th-cam.com/video/ANzRp-y-iW8/w-d-xo.html or a project management dashboard with Gantt chart like this: th-cam.com/video/5qtSioTE2wY/w-d-xo.html
Thanks you for sharing. I tried and faced a problem, It doesn't work when I cut the slicer and paste to another sheet. The slicer cannot paste on the dashboard sheet. Could you advise how to solve the issue?
That sounds very odd. I wonder if the dashboard sheet is protected? You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hello Mynda, I have come across a query and hope you can suggest some solution to it. I have a list of Stock names in a table (which obviously has Exchange & Stock Code in bracket at the end). I can't seem to get the text in a new cell (outside table) in anyway, tried, TEXT, SEARCH, LEN.. nothing seem to be working. Do you have any workaround? Thank You
Should be straight forward. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
What if the new data is in coulmn instead of row, means if new data will go to next columns? I am asking it as I deal with attendance and test performance of a college students having different subjects.
I would use Power Query to unpivot those columns because your data needs to be in a tabular layout. See this video on unpivoting: th-cam.com/video/-IMqkg35adA/w-d-xo.html
We need to know how to calculate a field. This is a great video but you skipped an important step = Values in the columns sections of pivot field. If you don't show that then no one is getting passed minute 7:50.
The PivotTable calculates/sums the field/column when you check the box to include the field in the PivotTable. This data is already in the source table you see at 8:00 (see the column headers of the table are the same that you see in the PivotTable field list at 8:08). As I check the boxes to include the 'Total Calls', 'Calls Reached' etc. the PivotTable knows these columns/fields contain numbers to it automatically adds them to the Value fields area and sums them for me. All this is shown in the video. There are no hidden steps I do outside of the video.
I’m making sure the outer text box is selected and doing everything you said but it’s not putting a value in the text box. It looks like it accepts the formula but the text box remains empty what am I doing wrong?
The text box will only accept a reference to another cell. You cannot put a formula in the formula bar, so maybe that's the problem...or maybe the text in the text box is white, and you cannot see it? If you're still stuck, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
📝 Summary of Key Points: 📌 The presenter demonstrates how to create an interactive dashboard in Excel using fictional outbound sales call data. They follow three steps: cohesive design elements, analysis and visualization of metrics, and adding interactive elements for data exploration. 🧐 The presenter sets up the dashboard canvas, changes the color theme, and adds headings. They insert shapes for key performance indicators (KPIs), icons for metrics, and a slicer to filter data based on salesperson names. 🚀 The presenter formats the data as an Excel table, creates a pivot table for analysis, and adds number formatting and slicers for filtering. They also insert text boxes to display KPI values and labels, and format them accordingly. 🚀 The presenter creates pivot tables and column charts to visualize the data. They format the charts, add titles, and adjust the axis settings. They also add conditional formatting to highlight selected salesperson names in the KPI table. 🚀 The presenter demonstrates how to update the dashboard with new data by copying and pasting it into the data table and refreshing the dashboard. 💡 Additional Insights and Observations: 💬 "The dashboard is designed to analyze and visualize key metrics from fictional outbound sales call data." 📊 The presenter emphasizes the importance of cohesive design elements and visually appealing charts to create an informative dashboard. 🌐 The video provides a step-by-step guide and practical tips for creating an interactive dashboard in Excel. 📣 Concluding Remarks: The video offers a comprehensive tutorial on creating an interactive dashboard in Excel. By following the presenter's three-step process, viewers can learn how to design, analyze, and visualize key metrics effectively. The use of fictional sales call data makes it easier to understand and apply the concepts demonstrated in the video. Overall, this tutorial is a valuable resource for anyone looking to create informative and visually appealing dashboards in Excel. Generated using TalkBud
Possibly, but AI is really only good for one off analysis. If you want to use the report again next month, it's more efficient and robust to build it in Excel or Power BI.
❓What's one unique dataset you're eager to visualize using the techniques from this tutorial?
Master Excel now with 20% off all courses until Feb 15, 2024: bit.ly/excelcourses130224
Nice ma'am
You have an amazing ability to adapt data to appear in this beautiful, harmonious and useful form.
Wow, thank you!
i followed your video step by step for 4 hours and i got it right the first time😊😊...thank you very much
That's awesome to hear!
@@MyOnlineTrainingHub appreciate you
Last conditional formatting trick was new to me, very handy indeed! Thanks for sharing...
Glad you discovered something new 😁
@@MyOnlineTrainingHubWould an And Function make sense here? Like AND($D10=Analysis!$A$10;Analysis!$A$12="") With this function the purple frames will only be shown, if you select a name from the slicer.
@@MyOnlineTrainingHub for some reason, when i update the data, this formating trick get modified and will only work for the first column. no idea why is that. Also when i dont have any name selected - its still showcasing the selection - while there is none.
@@liumahir yes, that function works better i think. fix the issue when you do not have anything selected in the slicer.
Thanks Mynda you made my day 😊
Glad I could help! 😊
Thanks Mynda,
One tiny tiny remark : it would be better to drop the name in the filter section of the pivot table and use it for the conditionnal formatting instead of using another pivot table with names in the rows.
bcz, if the prson didn't select any name from the slicer, we will still have "Alex" being highlighted in the table,
but if we add a filter to the first Pivot table, we can use it as the base of our conditionnal formatting. The filter will either show "All"" or "the active name in the slicer"
Love that idea 👍 I sometimes build an error message into the dashboard if people select multiple names none of the charts display, but I was on a time constraint with this video 😉
Taking that idea a little further -- the conditional formatting after Refresh only shows it being applied the the name, not the other PT fields in the video. Sorry I haven't had time to play with the download yet but I've been bitten by that quirk before and so I was specifically looking for it. Would using the name as a filter instead address that also?
I have tried to solve this issue with the AND function in the Conditional Formatting. My second logical condition would be to see that A12 in the analysis sheet is empty, which happens only if you select one name. It works fine for me.
Hi Mynda. I love your video editing.
Thank you so much! I must admit that I’ve outsourced it nowadays 🤫
@@MyOnlineTrainingHub Well, you picked a good editor. But look, the essence of the video is you, that's what's important.
Fantastic! Just what i was looking for!
Great to hear! 🙏😊
Out of all the tutorials I have been watching, this one is straight to the point and easy to understand. Thank you!
So glad it was helpful!
Very well-created Dashboard.
Brilliant Colour Scheme.
Excellent use of Data Bars and subtle use of Gradient to highlight figures.
The Conditional Formatting to highlight the Sales Agent is fantastic. Simple, yet very effective.
Thanks Mynda....,
Thanks so much 😊
Amazing work Mynda, thanks for all the effort ❣
Glad you enjoyed it! 🙏😊
Amazing! I didn't realize that such good looking and insightful dashboards can be built using Excel data. Thanks for making this YT video. Truly appreciate your time and efforts!👍
Glad you like it! 🙏
Great dashboard as always! I especially love the trick to conditionally format the selected name.
Glad you liked it! 🙏😊
I've been a huge fan! Thank you for making this video. :) God bless!
Glad you enjoyed it! 🙏
Awesome dashboard Mynda!
Thanks so much, Chris 😊🙏
Nice, Mynda! Thank You!
Glad you liked it 🙏
learned a lot, you're a star!
Awesome to hear 🙏😊
You are the one and only leader. You excel. Love it.
Thanks for your kind words, Scott!
Thanks, Mynda
You are too kind. I wish you all the best, now and always
I just love learning from you, a plethora of knowledge that you impart in an impeccable fashion, easy to comprehend
Thanks for your kind words!
So very awesome and thank you for sharing, this dashboard says a lot about your understanding of data and how to visualize them correctly.
Glad you enjoyed it! 🙏😊
I tried this in my excel and enjoyed the results. Thank you mam
Awesome to hear 😁
Struggling to really understand the dashboard . Thank for this video. I will try and practise and watch it over and over again
Glad it helped 😊
Simply amazing! Thank you for sharing this with us
Glad you enjoyed it! 😁🙏
Awesome video as usual.
Glad you enjoyed it 🙏😊
Excelsia is a Wizard 💚👌🏻
😁🙏
My boss is just doing a great job here, refreshing my head
I love your work! thank you so much!
So pleased it's helpful 🙏
Hi - thanks for this. Very very helpful walkthrough that I’ll be coming back to for future use!
Great to hear you found it helpful 😊
@@MyOnlineTrainingHub are dashboards and scorecards similar deliverables?
Yes.
Your creativity is TOP notch👍
Thank you so much 😀
That dashboard is................
........EPIC 😎
😅 wondered where you were going for a moment! Thanks so much 🙏😁
Precious. Thanks.
Thanks so much 🙏😊
Hi Mynda,
I saw your video on how to make a dashboard in 3 steps: really very useful and educational. But I have a problem when I update the pivot table with formatting both with data bars and with icons (you know I tried to put the traffic lights ...) the formatting of the columns with the icons is lost. I use the Mac version 16.90. How can I keep this formatting even after updating the data?
Many thanks for all the suggestions you want to give me.
Best regards Antonio
Hi Antonio, I answered this duplicate question on the blog page already. Please see my answer there.
awesome stuff again! greetings from the netherlands.
Welcome! Thanks for watching 😊
Thank you very much for the in-detail explanation. Best explanation on dashboard so far.
So glad you liked it 🙏
Thanks so much for this tutorial. 🥰
You are so welcome! 🙏
This is awesome! thank you for this
So glad you liked it! 🙏😊
Your mode of instruction is so easy to understand . Thanks, Mynda. Can we, please, get the sample Excel file to practice like the other videos.
Thank you. As usual, the file link is in the video description. 😉
Thanks, Mynda, for such a prompt response. Much appreciated. you are "THEEEE BEST" tutor for these courses. I do not see any other videos from anyone if I am serious about REALLY learning a concept.
Mynda, I cannot see the sample file , after checking again. What am I doing wrong?
I think the link was missing. It's here: bit.ly/3-step-dashboards
awsome job... simply superb
Thank you so much 😀
Good to see you fixed the account issue 👍
Yeah me too 😅
Top quality content as usual! Excellent dashboard.
Glad you like it! 🙏
Hi Mynda, sorry to be so bold but I have content suggestion for 1 video which I think many people would find very useful.
I would like to create a quick comparison spreadsheet for buying a car, where you compare costs of HP, PCP and PCH (leasing). I am not sure where to start.
1. I think just creating 3 separate tables perhaps. And then using charts
2. Another approach, maybe having 1 table and then use pivot charts. But for this task I don't think this approach is the most suited, probably 3 separate tables still best.
3. I am thinking if the What if analysis tools are suitable for this?
I am a bit confused where to start with this. I think a video on this would be fantastic, as many people are faced with tough budgetary questions when buying a car.
Do you have any videos on exporting expiries from multiple sheets onto a dashboard. I’m looking for a traffic light system that summarises upcoming expiries, expired and current stock holdings
You can use Power Query to first append the data from the sheets together into one table, then you can extract the data you want with either Power Query or a PivotTable, and use Conditional Formatting for the traffic lights. I don't have one video covering all these steps, but I do have individual videos on these topics. Here's the Power Query video: th-cam.com/video/YOC-pEIuHpA/w-d-xo.html
Here's the PivotTable video: th-cam.com/video/vQlFiLUaw4k/w-d-xo.html
Here's the Conditional Formatting video: th-cam.com/video/Rzz9PyfwiVQ/w-d-xo.html
This was amazing! I’ve been really wanting to do something like this for my board reports, but didn’t realize it could be done so nicely in Excel. I was thinking we’d need another subscription to a different tool.
Wonderful to hear you'll be able to make use of it and save a load of money 😁
Superb video.
Kindly share one for sales vs purchases and usage.
Thank you.
Thanks so much! Will keep your suggestion in mind.
You are simply amazing. Thank you!
Thank you too! 🙏
This is Awesome! thank you.
Glad you like it! 🙏😊
I like that name highlight trick using the conditional formatting and the slicer! I don't quite understand why picking that top cell makes it check the entire column for the name though. I'll have to look at that more closely.
Great to hear. This video explains conditional formatting in more detail and will answer your question: th-cam.com/video/Rzz9PyfwiVQ/w-d-xo.html
@@MyOnlineTrainingHubNice! Thanks for sharing that Mynda!
@@MyOnlineTrainingHubFollow up. Just watched the video you shared. You explained it perfectly, as usual. Thanks again Mynda!
Just super 😮
Glad you found it helpful.
Thanks so much
My pleasure 😊
SO good and very useful.🌹🌹🌹
Glad you think so! 🙏
How can I show month on month changes in staff absence for each department in my organisation in a nice chart? What would you recommend?
Depends how many departments you have. If there's only a few, you could use a simple line or clustered column chart. If there's a lot, then maybe sparklines.
Excellent!!!👍❤
Thank you! 😃
Thank you so much ❤
You're welcome 😊
Hi Mynda, I'm wondering if you have dashboards, pivot tables, etc. that are used with screen readers and would be accessible for people with disability?
great question. I don't have anything specifically for this, sorry. I'm not sure how good Excel is at reading charts. You could always download one of my files and test it out.
Thanks for your reply, I sincerely appreciate it.
We've recently had some internal training regarding accessibility and well it's complicated.
Nothing we do on a day-to-day basis will currently work. More chances to learn tho 👍
This is great, thanks Mynda.
Glad you liked it!
Amazing 😊
Thanks so much 😊🙏
Could you please create a simple project gantt chart. Thanks
Like this: th-cam.com/video/ANzRp-y-iW8/w-d-xo.html or a project management dashboard with Gantt chart like this: th-cam.com/video/5qtSioTE2wY/w-d-xo.html
Wow thanks!
Welcome! 😊
Thanks you for sharing. I tried and faced a problem, It doesn't work when I cut the slicer and paste to another sheet. The slicer cannot paste on the dashboard sheet. Could you advise how to solve the issue?
That sounds very odd. I wonder if the dashboard sheet is protected? You're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Can I have the data sheet to practice? Thanks a lot!
The link is in the video description.
Does this work if you are looking at median data for the month, not average?
You can apply these techniques to any data. As for summarising median values, you could find the min, max, average or median of the medians 😊
Hi dear can you do a video for simple book keeping
Thanks for the suggestion.
Please do an inventory management dashboard
Thanks for the suggestion.
How you wre able to do like dis !! This is incredible 💕
Thank you so much 😀
Hello Mynda, I have come across a query and hope you can suggest some solution to it. I have a list of Stock names in a table (which obviously has Exchange & Stock Code in bracket at the end). I can't seem to get the text in a new cell (outside table) in anyway, tried, TEXT, SEARCH, LEN.. nothing seem to be working. Do you have any workaround? Thank You
Should be straight forward. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
How can I put a map of the US in the dashboard and be interactive with a slicer?
I cover that scenario in this video: th-cam.com/video/McLQ3gEGbHQ/w-d-xo.html
What if the new data is in coulmn instead of row, means if new data will go to next columns? I am asking it as I deal with attendance and test performance of a college students having different subjects.
I would use Power Query to unpivot those columns because your data needs to be in a tabular layout. See this video on unpivoting: th-cam.com/video/-IMqkg35adA/w-d-xo.html
@@MyOnlineTrainingHub excellent video. But 1 thing is missing you didn't discuss multiple headers (more than two headers)
I cover that here: th-cam.com/video/-IMqkg35adA/w-d-xo.html
the interactive slicers does not seem to work when opened in the Mac version of Excel is there a fix for that
There’s no reason why the slicers wouldn’t work on a Mac 🤔 they won’t work on an iPad, but a Mac should be fine.
We need to know how to calculate a field. This is a great video but you skipped an important step = Values in the columns sections of pivot field. If you don't show that then no one is getting passed minute 7:50.
The PivotTable calculates/sums the field/column when you check the box to include the field in the PivotTable. This data is already in the source table you see at 8:00 (see the column headers of the table are the same that you see in the PivotTable field list at 8:08).
As I check the boxes to include the 'Total Calls', 'Calls Reached' etc. the PivotTable knows these columns/fields contain numbers to it automatically adds them to the Value fields area and sums them for me. All this is shown in the video. There are no hidden steps I do outside of the video.
I’m making sure the outer text box is selected and doing everything you said but it’s not putting a value in the text box. It looks like it accepts the formula but the text box remains empty what am I doing wrong?
The text box will only accept a reference to another cell. You cannot put a formula in the formula bar, so maybe that's the problem...or maybe the text in the text box is white, and you cannot see it? If you're still stuck, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
📝 Summary of Key Points:
📌 The presenter demonstrates how to create an interactive dashboard in Excel using fictional outbound sales call data. They follow three steps: cohesive design elements, analysis and visualization of metrics, and adding interactive elements for data exploration.
🧐 The presenter sets up the dashboard canvas, changes the color theme, and adds headings. They insert shapes for key performance indicators (KPIs), icons for metrics, and a slicer to filter data based on salesperson names.
🚀 The presenter formats the data as an Excel table, creates a pivot table for analysis, and adds number formatting and slicers for filtering. They also insert text boxes to display KPI values and labels, and format them accordingly.
🚀 The presenter creates pivot tables and column charts to visualize the data. They format the charts, add titles, and adjust the axis settings. They also add conditional formatting to highlight selected salesperson names in the KPI table.
🚀 The presenter demonstrates how to update the dashboard with new data by copying and pasting it into the data table and refreshing the dashboard.
💡 Additional Insights and Observations:
💬 "The dashboard is designed to analyze and visualize key metrics from fictional outbound sales call data."
📊 The presenter emphasizes the importance of cohesive design elements and visually appealing charts to create an informative dashboard.
🌐 The video provides a step-by-step guide and practical tips for creating an interactive dashboard in Excel.
📣 Concluding Remarks:
The video offers a comprehensive tutorial on creating an interactive dashboard in Excel. By following the presenter's three-step process, viewers can learn how to design, analyze, and visualize key metrics effectively. The use of fictional sales call data makes it easier to understand and apply the concepts demonstrated in the video. Overall, this tutorial is a valuable resource for anyone looking to create informative and visually appealing dashboards in Excel.
Generated using TalkBud
AI is amazing, isn’t it!
Can we do it by using jeda ai 😢
Possibly, but AI is really only good for one off analysis. If you want to use the report again next month, it's more efficient and robust to build it in Excel or Power BI.
@@MyOnlineTrainingHub Thank you so much 💓
3 steps?
1. Prepare your canvas
2. Analyse/visualise
3. Add interactive elements
👍😎✊
🙏😁
Broken link alert, i dont download
Sorry ok
Thought so 😉 hope you find it useful.
mynda plz marry me so i can become an expert like you😅
😝 Promo'SM
Awesome video lecture as always. Thank you. Also, will you marry me?
Glad you liked it 🙏 I’m already married 😉
This is awesome! thank you for this
Glad you liked it!
Thanks very much
You're welcome 😊