Get "ALL" Option in Excel Data Validation with this Crazy Trick 💡

Recently, I had to show "ALL" as an option in my data validation dropdowns. I used a simple but effective trick to get All items as an option. In this video, let me share the trick.
📁 Sample File - chandoo.org/wp/wp-content/upl...
💥 More AMAZING Excel Tricks - • Excel Tips & Shortcuts
👩‍💻 Want even more? Get my Excel School course and zoom past the rest - chandoo.org/wp/excel-school-p...

[@swethakavuri4263]

Very useful and interesting thanks a lot

[@MichaelBrown-lw9kz]

The way you used the VSTACK function was brilliant. I don't think I would have thought of that.

[@klauscosmin]

it's a good trick if you are forced by some reason not to add an "all" row into the table

[@Naranerun]

That's interestingly simply awesome, Chandoo!! Thanks for sharing this super cool trick🤩

[@chandoo_]

My pleasure 😊

[@user-bh4od9zs3t]

u r just awesome sir..I was following you way back with your website..but your youtube content are mind-blowing. Your personality is also worth inspiring.

[@siddhantmitra8133]

Amazing Chandoo, but I think I have done this already with a Pivot chart. But never thought of this method. I'll try this and shall check for any further awesome things we can do this way too!👌🏼

[@la7061]

Great as usual Chandoo!!! thanks for sharing this

[@ahmed007Jaber]

Thank you for this chandoo Due to changing circumstances and complexities in data i have been capitalising on pivot tables for such thinks

[@the_pest_the_best]

Thank you ❤. Your voice tone with mouse click sound matches and enter our brain 😅

[@rcasturi55]

Great teacher . Thanks Chandoo!

[@arbazahmad7177]

Excellent trick to show all.. 👌.. Thanks for sharing 🎉

[@mahdibhuiyan8951]

you are the ultimate guru

[@shreeshvishwakarma2409]

Indeed a very useful trick n thanks for sharing it 👍

[@johnborg5419]

Thanks Chandoo. Very interesting.

[@muveeshkumar]

Really an useful insight💡

[@pandharinathjoshi6565]

Excellent idea sir

[@alexrosen8762]

A great and useful trick 👌

[@unnikrishnansanthosh]

great one , thank you for sharing

[@user-od5fq9kq8e]

Thanks for the Information.

[@the_pest_the_best]

Your look changed from year on year sir. Thanks for these Excel tricks

[@abushahma9239]

Very Useful and interesting

[@irudi]

Very useful! I was wondering if there was a way to do it without the IF statement, but sometimes simpler is better!

[@benignoguilarte2806]

Great Chandoo!!

[@srikrishnanrengarajan6109]

Thanks Sir very informative

phenomenal 🎉

[@TRAININGTUBER]

yes i find it helpful Chandoo

[@ankushsinghthakurofficial8333]

Thank you chandoo sir

[@chrism9037]

Very nice Chandoo, thanks!

[@chandoo_]

Glad you like it!

[@adepukrishna]

Super bagundi Anna😊

[@MsEmir86]

chandoo you're the best

[@mohammedasifshaikh622]

Very Nice !

[@theluckcoach4592]

Very nice. May I ask why i cant just add another line below the table and label it as "ALL" (which totals every line above it)? Thanks!

[@andreasandoval5039]

I was also wondering that.

[@salmaalaa6158]

very perfect good job

[@sivab3875]

Excellent

[@jayceeharb1305]

very nice!!

[@canirmalchoudhary8173]

Nice trick. Yet, I can check Pivot table option where I get multiple selection option with slicer or filter.

[@surajbhardwaj428]

That Awesome🎉 We do this with the use of Sciler as well ?

[@yogeshindulkar320]

Awesome

[@taizoondean689]

Thanks Sir

[@MotiveEdge777]

Hi chandoo sir, Please do videos on interview questions for data analyst roles in tools like excel, powerbi, tableau etc.

[@ajithkumar4904]

Nice

[@JanoTemuko]

Wena wena, me gustó, gracias

[@user-ob4zc3qj6b]

Nice examples of xlookup and vstack use! Is there any downside to bypassing all of this and just creating a pivot chart with the appropriate categorization? Thanks as always!

[@chandoo_]

You can of course use slicers + pivot charts to get similar effect. In this case, my intention is to showcase the usage of VSTACK.

[@joukenienhuis6888]

And another advantage is that if the numbers are changed, the result updates too without pressing the refresh button

[@Drama_theatre]

Sir saw your videos on Power BI, can you explain more about dax

[@tenchuZsenran]

Newbie here 😊 Is there any reason we should not just add an ALL row and be done with it? Like is it not good practice to do so? Also could this not be done with pivot/slicer?

[@DavidNEVERFOREVER]

I'm curious about this as well, seems to be much easier to just get a Total row and reference that instead of all these

[@chandoo_]

In this specific data set, adding a total row gives you same result. But many times, when you are creating a complex dashboard or report, you can't just go back to source data and add rows. You can't also introduce total rows in to the calculation grid either. That is where this approach really helps. Imagine having a dynamically changing list of products and you want to always prefix ALL (or something like Top 5 products) as an item to that list? In such a case, VSTACK based approach gives you the flexibility..

[@Dear.bi610]

Awesome as awesome choclate

[@user-ul3tx5wu7c]

Hello. please tell me, my task is there is a file there, data in. table., and network folders, there are a lot of files, and I need to take information from these files so that it is remembered in the main table. and every time they create a folder and upload a file, when updating, the data with embedded information is immediately pulled up in the main file. this can be done using query. or register a macro. Thank you

[@michaelt312]

Is there a way to do this in older versions of Excel? I am working with a hospital that is using 2016. Even Power Query would work.

[@praveenm4319]

Whats the use of hash in the source ?

[@baylee1791]

Pivot can do this right ?

[@nazmulferdous6139]

Please share filtering option that's cell any data type automatically filter value

[@DeepakSharma-wm4md]

In which version "VSTACK" is available?

[@renatorosco325]

Instead of the extra "IF" formula, one could configure XLOOKUP differently: parameter 0 for search option looks vmfor an exact match, and the parameter of what to return if no match occurs should contain the sum.

[@lovableameer]

Ply help me to make legends inside column chart?

[@randomsurfer007]

Just wondering, can't you add a total row and call it "ALL"?

[@amarojusaikumar3]

Hii bro . I have masters in chemistry department. But i have passion about IT sector. And i have good knowledge on python basics, Sql. If im eligible for data analyst role . Please tell me bro.?

[@bhmsupra]

Chandoo likes Almond Choco

[@joukenienhuis6888]

It is a great trick, but why not summarize the numbers and call the total row All ?

[@adityanandakumar428]

All I would have done is a sum all in the last or first row, named that as "All" and then same data validation would have worked. How this touching nose like this helpful?

[@chandoo_]

That works too... what if you don't have control over the size of data? Like a dynamic listing of products based on geography. Then adding all on top is tricky. That is where the vstack option helps.

[@adityanandakumar428]

Then too. Just keep the sum row on top and keep additional rows in sum formula. But ya, Vstack usage and can be explored further

[@BhaskarReddy-bv3ls]

How to download and install ms office 365 for free . Explanation please

[@Excelambda]

You trick somebody to pay for it.

[@user-bv4hn4wp7g]

Bro telugu lo kuda cheppu evi anni

[@ddp2049]

This is a puls

[@GunjanMeher]

Awesome