Make Interactive Excel Dashboard in Just 12 Minutes
ฝัง
- เผยแพร่เมื่อ 10 ก.พ. 2025
- Get the dataset here for free:
docs.google.co...
☕ Buy Me Coffee: buymeacoffee.c...
Build an Interactive Excel Dashboard in just 12 minutes. In this video, we will build an automated excel dashboard from scratch using the sample data provided below. First, we prepare the base of our dashboard by removing the gridlines and adding color on the entire sheet. Then we will outline the overall structure of the dashboard using shapes. Next, we will connect our metrics using text box, the formula bar, and the metric value on our pivot tables. After that, we will create charts as our visualizations - stacked column chart for Sales vs Target per month, smooth line chart for total customers per month, and bar chart for profit per region. Since our charts is based on Pivot Tables, by default, messy Field Buttons appear on our charts. We will also show how to remove these buttons. Then we will customize our dashboard and finally add slicers. The slicers make our dashboard automated by providing buttons that we can click to filter data at once.
📂 Online store (complete dashboard and templates files - edit and customize as you like!): idata-analytic...
iDATA Analytics Hub provides free tutorial videos for Data Visualization, Data Storytelling and Data Analytics.
EXCEL BEGINNERS LEVEL 1: • EXCEL TUTORIAL FOR BEG...
EXCEL BEGINNERS LEVEL 2: • EXCEL TUTORIAL FOR BEG...
EXCEL INTERMEDIATE LEVEL: • EXCEL FOR INTERMEDIATE...
EXCEL ADVANCED LEVEL: • EXCEL FOR ADVANCED USERS
TABLEAU BEGINNERS LEVEL: • Tableau
Hi everyone! This is for those who want the Pivot Tables.
Since google drive turned the Pivot Tables into normal tables, we uploaded another copy of the Excel file here for only $1 (minimum price allowed). Free refund if Pivot Tables not available. Hope this helps!
idata-analytics-hub-shop.fourthwall.com/products/excel-file-with-pivot-tables
Thank you for this!
Too Fast
Hi. Thank you for your feedback. If it helps, viewers find it helpful to slow down the video by using the gear button on the bottom right of the screen.
For those who are finding a few places hard to see exactly what's being clicked on or done, (as I did, lol) you can slow the vid down using the gear button on the bottom right of the screen. Click on Playback Speed and you'll have choices to slow it down at the hard-to-catch spots.
That aside, first time watching a video on your channel. Super helpful, will be back. Cheers!
Thank you for sharing your tips to the community! We're glad you were able to create the dashboard. Congratulations!
This is the best dashboard tutorial I have ever seen. EVER. I was able to follow every step. Thank you!
We are glad to that the tutorial video is helpful for you. More tutorials in our channel :)
Thanks!
Omg thank you so much for the Super Thanks sir!!! It really means a lot to us 🙏❤️❤️❤️
Hi, very nice Excel tutorial, i was looking for a video that explain nicely how to pivot table and create dashboard, finally after watching this i managed to create my own excel dashboard
Hi! Thank you so much for sharing this. We are so happy to know that you have created your own excel dashboard and that our video is helpful. ❤️
A very clear guide for me. This is what i have been looking for -connecting several charts to the slicers date. Thank you so much!!
Hi! First, congratulations on successfully connecting several charts to a single slicer! 💯 reading comments like this really inspire us to create more tutorial videos. Thank you!!! ❤️
Thank you for this guided project. I made my first dashboard too.
You are very welcome! Congratulations on creating your first dashboard! 🎉 Keep going and we can't wait to see what awesome projects you'll make next. Let us know if there's anything else you'd like to learn! 💙
Loved the tutorial. Speed was normal and could be understood easily. I will surely try this. Thanks for sharing Ideas.
Thank you and we are so happy to hear that you like this tutorial. If you have any questions while trying this, feel free to let us know. We'll be glad to be of guide. :)
Simple, direct and rich in content as all tutorial should be. Thanks for sharing this great video!
Glad the video is helpful! Thank you also for taking the time to indicate what you particularly like about the tutorial. This helps us create more helpful tutorials. 💙
WOW! This is the best dashboard tutorial I have watched ❤
Comments like this really inspire us. Thank you! ❤️
Gosh! Your video here is mind blowing...absolutely fantastic! 🎉👏👏👏👏👏
Thank you so much! I'm so glad you like the video 🥹❤️
Great presentation! Thank You😀👍
Glad the video is helpful! ❤️ if you have any question, just let us know and we'll be happy to assist 😀
Next level ❤😊
Hope it helped! 💙
Very easy to understand steps to create a basic dashboard. Thank you 🤗❤️
Thank you! We're so glad that you find the video easy to understand, just like how we wanted it ♡
This tool is extremely useful for many people , thanks for sharing knowledge
You're welcome! Just let me know if you have questions
This is really helpful. Thank you!
We are glad to know that. Thank you!
I learned and refer here alot! thanks!
That's so nice to hear. We're glad the video is helpful. Thank you! ❤️
Really outstanding work and design. Well done. Particularly to lay out the data and metrics first. Lots of people tey to do this as they go and it can rwally slow one down.
Glad the video is helpful! Thank you for sharing the particular parts which were helpful. This guides us on creating more tutorials. ❤️
Great content. Thanks a lot. But can you tell me how to avoid moving percentage labeling within the progress chart bar? Once I refresh the data the percentage is wanderung around.
Thank you! Glad you found our content great. To avoid moving the percentage labeling within the progress chart bar, you can change its properties. Below is a step by step guide on how to do this:
1. Right-click on the percentage label.
2. Select “Size and Properties”.
3. In the “Format Shape” dialog box, navigate to the “Properties” section.
4. Choose the option “Don’t move or size with cells”.
5. Click “Close” to apply the changes.
By selecting this option, your percentage label will remain fixed in its position regardless of cell movement, resizing, or refreshing of data. Hope this helps!
It works. Thanks a lot, again.
You're welcome! ❤️
Useful and fun to watch. The vid callouts and % done helped.
Glad the video is helpful and fun to watch. Thank you!
❤absolutely brilliant video❤
Thank you so much...I really Loved this video...to the point video thank you 🤗💕
Glad the video was helpful! This is really inspiring ❤️
Thanks for sharing knowledge 🙏
Glad the video is helpful! 💙
Thanks for sharing this great video.
Glad the video helpful! 💙
thanks for sample file
I can follow step by step your video
thank you very much
Glad the sample file and video are helpful! If you have any questions, we'll be glad to help 💙
Really like it bcs it is simple and on point. Compared to the latest kind of dahsboard which includes query, power pivot etc.
Great 👍😊
Glad it helped! 💙
This is my favorite tutorial. Thank you!
Glad it is helpful! ❤️
i'll try it!
THANK YOU!
Thank you ❤
Glad it helped! 💙
A very clear tutorial for me. Direct to the point. Thank you!
Glad the tutorial is clear. Thank you!
Very useful, thanks a lot
Glad the video is useful! If you have any question, we'll be glad to help :)
Great tutorial, Thank you 🤩
We're glad the tutorial is helpful! ❤️ If you have any questions, feel free to reach out. :)
Im working as a senior MIS Officer. In Sales Team it will Help me Lot ❤.
That's so great! Congratulations on your job as a Senior MIS Officer! Being a senior officer says a lot about your dedication and passion. We're glad to help and please feel free to let us know if you have any request and suggestions. ❤️
@@idata.analyticshub hey hi.
My request is the same as how to make Last Month VS This Month comparing the report dashboard .
Hi! You may check out our latest video, we have charts to compare values this month vs last month vs last year: th-cam.com/video/-qt_nFudQkY/w-d-xo.htmlsi=NcjR0_uR5edaQw3c
@@idata.analyticshub thanks 🙏👍
Hey everyone, we heard you! 💙💙
Here's a detailed, step-by-step guide on how to make the PivotTables used in this video: th-cam.com/video/JYfJwuVM7Kk/w-d-xo.htmlsi=ctSpwox-F505LPVu
hello sr, can u make a video to how to make povit table like u did for sales, customers, sales, competition, etc
Hi. Here's our tutorial video on how to make pivot table:
th-cam.com/video/qqZmwt9eSm8/w-d-xo.htmlsi=ii_nEdO1DX3dhxdM
WOW! This is amazing!
Thank you! Hope it helps!
When I try to make the slicer in minute 7:35 all I get in the box is (value)and(column1) how come? It didn’t show me the days or months and now it says there is no connection in the workbook
WOW! I've been looking for a tutorial and this is the BEST ONE out there. I finally able to create my own dashboard. Thank you!
Congrats on making a dashboard! We're glad to be of help. :)
@@idata.analyticshub can I send you my dashboard? I want to know your comments about it.
Sure! We'd love to see your dashboard and give our comments and recommendations. You can send over a screenshot to our email idataanalyticshub@gmail.com
I will give it a try.
Best of luck!
Nice one - small remark - for example slicer box was formatted from Jan - Sep - think of future use too - if this is going to be used for data of one year then format this immediately for Jan - Dec buttons and so on ;-) nothing is then more annoying when a new month is added and your dashboard formatting falls apart because of this (or you need to scroll around)
Thank you for sharing your insights to the community! It's definitely helpful to format the month slicer for one year. But if your data only contains a few months, you can still use a slicer. Then when you add more months to your data, the slicer would automatically update when you refresh. :)
Interesting and great presentation, can you please tell me how you calculate the ** average of sales completion
** Average of profit completion rate
** sales completion
** profit completion
Please?
The data you copy into your metrics, comming from columns D and E (headers profit, sales, customers), Is that a dynamic copy of your pivot tables in columns A and B? In other words.. do these values change along when you apply filters? And how did you manage to get those dynamically? My challenge is to keep data dynamically when chaning the pivot tables. Thanks in advance, hope my questions are clear.
Hi. Yes, your questions are clear. And yes, the metrics used in the video updates when you apply filters. The important step here is to use your pivot tables as a reference to make your metrics dynamic. Tip: Consider using the GETPIVOTDATA formula to reference specific cells within the pivot table. Hope this clears things up!
@@idata.analyticshub Thanks! I will look this up. If you refer to a cell in the pivot table, does this automatically refresh when you apply other things too in the pivot table (choosing from the list on the right)? (in other words, when the set-up of the pivot table changes due to adding more parameters for example). Anyway, thanks a lot for your response, this really helped to get further :D
Yes. When the pivot table changes (like adding parameters or applying filters), the metric also updates. You may also watch on the metrics part of the video how to properly connect it to pivot table. Hope this helps! :)
At 2:52. How did the donut chart fill in the plot area? At 2:51, it was not with lots of white space around the donut, and just by dragging it upward, it is now filling the entire plot area! Any suggestions?
Hi. When you click that donut chart, do you see 2 boxes? Use the inner box to adjust. Drag the inner box to fill in the plot area. Hope this helps!
Wow! That was amazing!
Thank you for the kind words! Really appreciate it ❤️
Wonderful
Glad the video is helpful!
Now its time to link this whole thing with microsoft query for next month. Would you?
Why the need to link to microsoft query?
I need Operational Efficiency KPIs ad 3 additional KPIs dashboard. Can you point out which one in your store ca be adapted to suit my needs?
Hi. The 'Dashboard FULL COPY' suits your needs. It has Sales, Profit, and & Number of Customers KPI. Additionally, it has Monthly Customers, Actual vs Target Sales, and Profit by Country. It already has Slicers or Filters as well. This is an adaptable file since it contains all the needed tabs:
Dashboard
Data
Pivot Tables
Contact Us
Great help.
We're glad you found the video helpful. Thank you for the kind words. We really appreciate it. 🖤
Nice one, but one thing all of you should consider is a proper naming of the pivot tables 😊
That's correct. Thank you!
strong!
Glad it's helpful! 💙
Please tell us how to make that original table with percentage of sales and profit
Hi. It's a pivot table using the Sales and Profit field. When you use Sales in the pivot table, the default function is SUM of Sales. Just changed it to AVERAGE of Sales. Same thing with Profit. Hope this helps!
Which version of excel are you using?
The latest one.
Wow super Love it 😻
Thank you! Glad the video helped 😊
Im your new subscriber ❤
Thank you! We really appreciate it ❤️ more videos like this coming soon
This is super, thank you. I have some newbie questions. How do you append the data table to see the changes when new data comes in? I’m assuming that you keep the pivot table the same for the dashboard? In other words, don’t change it or manipulate it
Hello! Nice to have you here. That's right, I keep the pivot table the same for the dashboard. I created pivot tables so that when new data is added, the dashboard automatically updates.
Sharing a tutorial on Pivot Tables below. Hope this helps
Pivot Tables in 2023: th-cam.com/video/qqZmwt9eSm8/w-d-xo.htmlsi=Ik1CoZINHB8BIMiLHope this helps.
Hi i have one concern, this dashboard have to build every time? Like when we change some data in data sheet. It has to change right?.
Hi. Yes, when you change data in data sheet, the dashboard should automatically update. No need to build the dashboard every time. :)
Thank you😮😮
You're welcome! 😊
I'm having problems with the doughnut chart to respond with the slicer
Hi. Make sure that the Slicer is connected to the source of doughnut chart. You can check this on Slicer Connections.
What software did you use to edit this video please?
Hi. I mainly use canva.
I love this asmr tutorial
Thank you so much! We're glad you love the video
Hi! Awesome vid. When you're adding your metrics, how did you create the table that referenced your grand total of sales? I'm having problems trying to formulate it without getting incorrect info when I try to filter and the pivot shrinks. Thanks
Hi! I created a normal table (not pivot or anything). Then on one cell on the table, I put "=" then selected the total sales (pivot table). Hope this helps!
I think what happened is you put the formula "=A2", for example. It should be "=GETPIVOTDATA()"
I can't add any formula in the text area, I write and it's working and formula symbol is not working. if I do tha't down in a cell it works but not in a text box :( I really. needed that. Thanks in advance!
Hi! Here’s how you can do it:
1. Click the Text Box
2. Go to the Formula Bar (while the text box is still selected)
3. Type the Formula "="
4. Select the Metric (Cell) You Want to Show (Example: ='Pivot Tables'!E3)
5. Press Enter
The value from the referenced cell will now appear inside the text box. Hope this helps!
I really want that you can tell me the way about how metrics change following to the slicer. Please instruct me the way to do this because my number cannot change if I use the slicer. Please answer me this question.
Hi! Yes, I'd love to guide you through this. First, have you checked the Connections of your slicer? Make sure the the slicer is connected to all necessary PivotTables so that when you click the slicer, the metrics would also update.
In Metrics couldn't add formula = its disabled by defaul, any solution?
Hi. To fix the problem, you can try the following steps:
1. Select the text box where you want to put the metric.
2. In the formula bar, type the formula =.
3. Press Enter on your keyboard to apply the formula.
This should resolve the issue. Hope this helps!
how can I customize the structure ? if I want to use it for defect inventory as per week and month ?
Hi. You can add a weekly and monthly inventory chart. The video shows how to create a monthly chart. For the weekly chart, you can follow the same steps but use the "Week" field instead of "Month".
@idata.analyticshub and the sales with defects and customers with the products, correct?
Yes that's correct. It would also be helpful if you have the specific type of defect on your data. And you can show it in the dashboard as well to highlight what is the most common defect.
@@idata.analyticshub thank you so much
You're welcome! You're dashboard seems to be very interesting. If you have data for the location/source of the defects, that would also be very helpful to show in the dashboard. That way your superiors would be able to be informed which location/source needed help.
Really helpful
Thank you for the kind words! Really appreciate it 🫶
The percentage numbers from sales profit and number of customers keep changing position whenever I use the slicers. Can someone explain to me how can i prevent that. And how can i return to the overall view after using the slicers? Thank you!
Hi. Hope this helps! th-cam.com/video/4EOYhvaa16o/w-d-xo.htmlsi=81gJepPYfe7zqy4U
can you please explain the last step "Slicer"?
Certainly! In the last step, I created Slicers that would interactively filter data in my dashboard. First, I clicked anywhere in the PivotTable where I want to create a slicer. Then, I went to "PivotTable Analyze" tab. Then I clicked "Insert Slicer". A dialog box appeared and I just selected the checkbox for the fields that I want to create slicers for. Then I clicked "OK" and the slicers for each selected field appeared. Hope this helps!
How can I make the bar graph into a stacked bar of two countries sales totals. For example, if I sorted to Peru & columbia... and wanted to show each part of the bar as Peru + Columbia sales
Hi! To make the bar graph to a stacked bar, you can follow these steps:
1. Select the data range that you want to use for your chart.
2. Go to the Insert tab in the Excel ribbon.
3. Click on the Bar Chart button in the Charts group.
4. Choose the Stacked Bar option.
Hope this helps!
How to get this files, your website is not working
Hi. Upon checking, the link is still accessible. You may also send us an email to request for the file.
Sir please clear my doubt
Copilot 365 soon available in Excel & other ai tools are there
So is it still important to learn ms Excel deeply all functions and advance formulas??
Hello! This is a very excellent question.
We are now approaching the era of AI and while most of us are thrilled with the advancements that come with AI, some people have a valid concern of getting replaced by AI.
AI tools are based and trained on data that was originally created by humans. So while AI can automate certain tasks, it cannot replace humans. It simply does not have the human intelligence, creativity, and social understanding.
So to answer your question directly, yes it is still relevant to learn Excel. Even more important now more than ever to learn Excel deeply, to be be able to control, maintain, and update AI tools.
What is the meaning of sales completion and incompletion?
I had the same question at first. I think it's how close to the target sales
You copy pasted the chart ?
Hi. I cut pasted the charts on the video. If you prefer, you can also copy paste them. Although you won't be needing duplicate charts on the dashboard and data sheets.
@@idata.analyticshub I tried but I was not able to put the slicers in that.
So was facing difficulty as not all the charts are getting filtered properly
I understand. The missing step is connecting the slicer to ALL the charts, instead of on only 1. Here's how:
1. Click on the slicer to activate it.
2. On the Slicer tab, click Report Connections (or PivotTable Connections in older Excel versions).
A window will appear showing all the pivot tables in the workbook.
3. Check the boxes for all pivot tables and pivot charts you want to connect to the slicer.
4. Click OK.
Hope this helps!
Will try this. Thanks!
Hahaha background music of bhanga dance what’s the correlation with excel tutorial. Wait let me dance first !!!
Hi. Sorry if the music did not correlate. If you have any music in mind that would be more fitting to the video, please let me know. I would love to hear your recommendation. 💙
Or cut pasted the chart?
Hi. Yes, I cut pasted the chart on the video
@@idata.analyticshub so is it always that we need to cut paste or copy paste will also work
Copy paste also works :)
your data set is without Dashboard, Slicer and pivots.. kindly share the full dashboard as you explained in Video.. thanks in advance
Hi. The dataset was shared to help you practice creating your dashboard through the guidance of the video. Hence, there's no readily made dashboard.
You may use the dataset and follow the steps as explained in the video. Please let us know if we can help you with anything more :)
so it is must to have pivot tables
Yes! The file already contains the needed pivot tables. :)
Hi! The data used for this tutorial is available on the attached file with readily structured Pivot Tables. Sharing a tutorial on Pivot Tables below:
Pivot Tables in 2023: th-cam.com/video/qqZmwt9eSm8/w-d-xo.htmlsi=Ik1CoZINHB8BIMiL
Too difficult to understand because u are too fast
@@ezegloria8196 hi! Thank you for providing your feedback and we're sorry to hear that this video is too fast. Perhaps our FULL EXCEL DASHBOARD COURSE is a better fit for you. Link: th-cam.com/video/zk0_MUuCpYw/w-d-xo.htmlsi=XtgZxa_tVqtarHeg
I like this video and you? www.youtube.com/@phandongexcel❤❤❤❤❤
@@ezegloria8196I suggest going into settings and changing the playback speed to .75 - I find that useful when I’m following tutorials. That, along with the ability to stop and replay, is quite useful.
Hello how to make trading journal forex trading long short lot pair profit or loss and feedback 😢😢😢
Hi! Would you mind sharing what charts or visualizations are you looking for in this trading journal?
@@idata.analyticshub like tradezella
What is that?
hello! i want to ask that how you added sales completion rate plzz reply
Hello! I added sales completion rate at 2:27.
Hope this helps!
How you add in data in percentage?
@@JayandKeval For data in percentage of sales, I created a PivotTable and used the value Sales Completion Rate.
The default calculation of values is SUM. So I just changed it to AVERAGE. You should be able to get 86% for sales completion rate.
Sharing our tutorial here as well on How to Create a PivotTable. I hope this helps :)
th-cam.com/video/qqZmwt9eSm8/w-d-xo.htmlsi=O_vM_IXT-zntoOr-
Thank you so much 😊😊
How do you get the Sales Figures into the text box? I can't seem to put them in there due to it being a shape / text box gives me this formula is missing a range or a defined name error
Hi! The error "this formula is missing a range or a defined name error" means that the text box can't read the number inside a pivot table.
To fix this, what I did is that I put the number in a normal cell. Then I pointed my text box to that cell. Hope it helps!
But if you do it that way you have to manually change that position for every month and the slicer loses its point ?@@idata.analyticshub
how can you put formula inside a text box?
To put a formula inside a text box, you can follow these steps:
1. Create a text box.
2. Click inside the text box and then click inside the formula bar.
3. Type ‘=’ and click on the cell which contains the value that you want to show. In this video, we clicked on the cell on the pivot table.
4. Press ENTER button.
I hope this helps! Let me know if you have any other questions.
How to connect yearly and monthly slicer together???
To connect slicers together, you can use the “Report Connections” feature. First, select the slicer you want to connect to multiple pivot tables. Then, activate the “Slicer” tab and click on the “Report Connections” icon in the Slicer group. In the “Report Connections” dialog box, select all the pivot tables that you want to connect with the selected slicer. Finally, press OK to complete the connection. Do these steps to both Yearly and Monthly slicers.
I hope this helps!
Which version is this excel?? Please reply
Hi! We're using the latest version
@@idata.analyticshub is this the paid one??
Hi. It's the free one
@@idata.analyticshub actually I have 2010 ,, I am not finding some of the vlookup featured so asking ,, not finding combined option in in it ,, please show us
"Supper se Upper"
Thank you!
wow i can download the full file?
Yes! You can download it in the description :)
Why I can't put the matrix in text box
Hi. Have you tried using the formula bar? You can click the text box first, then place the formula for matrix inside the formula bar.
Excuse me, can you example (Overview, data, and Pivot Tables)? ( Details )
Hi! Sure, we would love to create a detailed tutorial on overview, data, and Pivot Tables on our next video.
While waiting, do you have any specific parts that you'd like to see on our tutorial? Perhaps how to clean data?
@@idata.analyticshubplease share details on pivot analyze tab about how to do it and how to enable it.
I need this with full disborad
Hello! Sharing our full dashboard course. Hope this helps: th-cam.com/video/zk0_MUuCpYw/w-d-xo.htmlsi=9jRLa92javuXfOzp
I dint have the data thing tab, did y’all find it
Hi. What tabs do you see?
Thnak You
You're welcome! Hope the video is helpful
great
Glad it helped! 💙
I'm unable to use slicer 😢
Hello! I'd be happy to guide you. How did you try to use a slicer? :)
where can i get the file?
Hi. You can get the file on the description box :)
thanks bro
Happy to help bro. Feel free to let us know if you have questions
@@idata.analyticshub thanks, i doesnt have any question
A kind of Power BI solution but pls explain what you do, music will not teach
Thank you for sharing your suggestions. We appreciate all suggestions to help us create better and more helpful tutorials.
You tube par jyda beginners hi video dekhte hai agar unko hi na samjh aaye to fayda kya banake video
In english please
nice!
Thanks dude!
Demo is very fast. Nothing can be understand
Thank you for sharing your insight. This is noted.
Play the video in slow motion bro. I think it will help you.😊
Thank you so much for sharing this amazing tip! 😊