Make a clickable report in Excel with this *CRAZY* trick in just 4 minutes!
ฝัง
- เผยแพร่เมื่อ 5 ก.พ. 2025
- Boss wants a quick report? NOW? Well, I got you covered.
Use this simple but effective Excel trick to make a clickable report in just 4 minutes.
We are going to use the subtotal & outline function of Excel to do this. And then we will put a cherry on top with conditional formatting 🍒
📗 Sample file:
=============
Get the sample file from chandoo.org/wp... and practice this idea.
⏲ Got more time? Learn these tricks:
===============================
Need something more? Check out below Excel tricks:
Make 8 Pivot Tables in 10 minutes - • Learn Excel Pivot Tabl...
5 Amazing conditional formatting tricks - • 5 Conditional Formatti...
Top 5 chart with Excel - • Create a Top 5 Dynamic...
#excel
I've been following you for two years, because of you I have learned a lot in excel. You mad my life richer with talent, all credit goes to you. keep going, Thanks Sir.
Is it..if learn Excel from this channel will i albe to be a data analytics? Or can I do freelancing and earn?
Thank you so much for the love and appreciation Azhar :)
Very true -- ♥ -- Love from Pakistan Mr. Chandoo ♥
I have been using Chandoo's Excel forum and tutorials from 2014 till now. Learnt a lot. The way you explain things is as clean and neat as your dashboards. You are AWESOME 👍
Wow. Always a pleasure to hear from long time fans such as yourself. Thanks Srikanth for being with me all this while. Wish you many more years of Power ⚡
You are always an inspiration Chandoo.
I watch all your videos as I get a notification.
Keep up the great job.
Thank you so much Khawaja.
I agree with = >
Srikanth Narayanan [I have been using Chandoo's Excel forum and tutorials from 2014 till now. Learnt a lot. The way you explain things is as clean and neat as your dashboards. You are AWESOME 👍 ]
You are welcome Aias.
Great chandu
You are welcome Rana.
For Chando , The door of Legend is always Open..
Chando Forever
As usual a great video. Thank you for making me awesome in Excel Chandoo. Love from Chennai, India
My pleasure!
This is a special video that deserves new playlist in my yt library. Very innovative it shoes what pivot tables are missing
Your all videos are helpful and awesome
Another fantastic video, thanks Chandoo!
You are welcome Chris.
That one very very handy and also the execution of the data for reporting excellent and to me that is your greatest strength. Thank you once again.
I am glad you enjoyed this Arvind.
Great video as usual ; Thank you
Thankyou sir for giving such knowledge 🙏🙏🙏🙏
First comment and first view sir😁😁
Thank you Vikash...
@@chandoo_ sir
I just wanna ask few things with you
I'm new learner and I got inspired from your videos towards data analytics and excel
I've been learning excel now from you, could you please guide me how I can be strong in Excel and in data analytics??
If there any roadmap so please guide me
I'll be waiting for your reply
May god bless you sir♥️
Thanks a lot Chandoo! I am using Subtotals feature, but you added more to my knowledge as usual! Specially, the conditional formatting part that stays between selection of levels! Cool! 🙂
You are welcome Vijay. This report came in handy the other day at a client's place and I turned it into a video.
Sort -Table Headers - Subtotal - Conditional Formatting - Data Bars - Color Scales and Done
Awesome chandoo oo
Thank you Devidas.
I think this video is the reply to my suggestion given sometime ago. Thanks :)
Could be. But I had an opportunity to try something similar at a client's place recently and converted it to the video.
HI, great video, again :)
Thank you for this video
You are welcome Shanmukha.
You are the Best ✅
Thank you very much Sir. Very useful information 👍
Most welcome
Thank you so much
You're most welcome
You helped me improved my standard of work 🧡
Awesome to hear that Nokulunga. 😎
Great Video Sir
Thank you 😀
Chandoo nice
Thank you Tofik.
Awesome video. Thanks
You are welcome Mayank.
Nice sir
😍
Sir I want a help for filtering data ..as I takes more time for filtering
Hi, I would first like to congratulate you for the content you offer, and I would like to know if you plan to post videos about forecasting on Excel and thank you.
Hi E. Thank you. Yes, I do have some videos on forecasting and more will be added for sure. See this for how to do forecasting in Excel - th-cam.com/video/MIzy-aCYG_I/w-d-xo.html
Great Info
😎😎😎
Thank You ❤️❤️This is really a new lesson for me.
I am glad you liked this Varghese.
Learnt Back to back subtotal functionality
Thank you, is this called grouping or different from grouping Mr.Chandoo, could you help to understand
Grouping and outlining is similar features.
Hi Chandoo, i have a dataset which has sub-total level calculation. Any idea how to undo that. By default when i downloaded the data, it had that subtotal row, which i dont want.
Thanks 👍
You are welcome Faisal.
have you made any video as to how to sort the columns in pivot table . Basically sorting tricks for pivot table that has date fields.?
I do have an advanced pivot table tricks playlist with many ideas. See th-cam.com/video/0HU8Hej6ong/w-d-xo.html
How can we get data in excel power pivot i.e. distinct count of last six months (DAX) by slicer selection please help.
Hi chandoo does your last SQL video enough for data analyst interview?
It depends. That video *is* a great starting point. I would certainly learn more about other concepts and practical applications before an interview.
Hello sir. Can u show a vba code that does a vlookup to get cell contents together with the comments attached to that cell please
Love the data bars. How does it work with income and expenditure?
Thanks for sharing your knowledge 😀
The databars can go to negative direction too (plus they show up a different color.) Give it a try.
@@chandoo_ ah cool 😎 thank you so much Chandoo
Dear Chandoo kindly teach us Excel for small manufacturing company. i.e Cashbook, inventory sheet, cashflow, income statement and balance sheet linked together.
I don't work in such situations, so it is hard for me to imagine how such a workbook would be setup. I suggest looking up Randy Austin's Excel for Freelancers TH-cam channel
Happy teacher's day.
Hi chandoo I have been following you for quite some time now and your videos are amazing.
I have a Special requiremnt where I have three colums repeating till 575columns.the total nber of rows is 62k
Now I need to make a report on this sheet where the output should the repeating columns. How can we do this.
Thanks for the lovely comment :)
I am not sure I understand what you are planning to do. Can you explain?
My Excel sheet has say Four colums : share path, ad group, owner and owner'dept. Now since the share path has multiple groups on it so column 5,6,7 are again ad group, owner and owner'dept and like this the information is there upto column number 570.
I canot have this info edited row wise because the currently there are abt 62k rows.
How do I generate a report which gives the owner's dept.
You can use Power Query to reshape the data and then generate the report you want.
Hi Buddy
I tried but was unable to do. Could you help me out if I send you a sample data.
Hi chandoo, i am not able to import your SQL files even after following all the instructions. Can you plz hepl me with that
Hi Chandoo, how do you create a macro for a leave tracker such that if you move from month to month on the template within the sheet, the data of the previous month will disappear so you can enter data for the current month you are on. Thanks
It is tricky to explain in a comment. I suggest learning a bit more about VBA and figuring it out yourself. See my getting started with VBA series here - th-cam.com/video/5k-KpSkrROw/w-d-xo.html
Can we apply subtotals in horizontally? this is vertical application
While you can add "horizontal" grouping with Data > Outline > Group option, it seems there is no easy way to add "automatic subtotals" at column level.
You can use Pivot tables for such things though.
@@chandoo_ Thanks for your kind revert
I like it ,,
Awesome.
Hello sir, I want to become data analyst, I'm from non IT background please show me the path how to start and from where to start. Thank you
Sir i want to purchase your course but it is expensive as it is in dollars . I am from India and i want to learn Excel from you . Will you have any discount coupons so i could purchase it . I am a student .
Hi Priyanka... I offer 25% discount to full-time students. Please email me so I can share the discount code.
Please make living appartments collection.expence and pending of various head like lift charge festivel etc collection sheet and yearly balance sheet report auto updateing last year pending in each flat wise
I have jaw issues becoz of that my communication skills is not that enough ,i stammer while speaking, can i become data analyst
Does communication skills matters in this role
Hi Mr Chandoo:
a quick question Salary of 5000/- from Jan to Sep and then it increments from Oct to till March to 7500/- and then it increments from April to 10000/- and so on
Using IF condition savings percentage moves from 10% if salary is < 7000/-, 20% if salary is
I am not sure. 🤷♂️
Why don't u make a full course on Excel & advanced excel for data analysis ? Paid or free...or charge a minimal amount for that..as Guru dakshina 😊
Hi Ashish.. I do have such a course. See chandoo.org/wp/excel-school and sign up
👍👍👍👍👍👍👍👍👍👍👍👍
Sir I watch your videos on and off and have gained enough knowledge... But this video little blur.. can't grasp easily .. 😐🥺
Better still, if you want to do reports stop using Excel and start using Power BI
Disagree. While Power BI is a popular option, Excel is still the go to choice for data analysis and reporting for millions of people.