Choose Certain Columns as Output in Excel | CHOOSECOLS & FILTER Combo

Sometimes you might want to choose only certain columns from your filtered dataset. For example you could apply a FILTER function on your entire data but only have selected columns from this dataset in the output. With a combination of Microsoft's new CHOOSECOLS function together with FILTER you can do just that.

Requires Microsoft 365. The CHOOSECOLS function will not be available in Excel 2021 and earlier versions.

🚩Let’s connect on social:

👉 This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#Excel

[Shubharwal]

That is something i’ve been wanting to know. Thanks a lot. This really helped me out😊

[chrism]

Love it thanks Leila!. Combining the two functions is incredibly powerful

[yin]

choosecol function is really incredible!!!

[esthercraft]

This is what I was hunting for thank you for doing a short on this

[richardhay]

I use this construction almost daily. Its a great feature. It is a limitarion that the column indicies are numeric not alphabetic . But I use ta row immediately above the headers (jnsering a row if necessary) and then number the columns with SEQUENCE(, , COLUMNS(ref col headers)).This puts the nimbers 1, 2, 3... directly under A, B C... CHOOSECOLS can be used both omit columns b but also reorder them. A quick and easy solution. The previous method was CHOOSE but its is a pain by comparison.

[margaretfarquhar-xbnk]

Fantastic help, and very clearly explained, thank you!

[vanistajames]

Thank you so much sir❤.. Because of you, I am always learning something new.. May God bless you abundantly😊

[elizabethburger-njkc]

I just used this and it helped me so much!

[Ahmed]

it's really a great function, thanks a million

[arkimphiri]

Thanks Leila, I was looking for this very thing. Thank you so much 🥰🥰🥰

[ICDLRAH]

Thanks very much.
Your posts very me us.
Be succes in your career and your life🙏🙏🙏

[dolittlediehard]

Wow I'm always learning something new ❤

[frederickfan]

You are my love ❤, learning so much from you.

[krishnasayeee]

Hi Leila,
Is there any way to show blanks as blanks or blanks as "NA"/specified text in the Choosecols function?

[sivareddy]

Hi Akka (syster), this formula is very cool.

[vipersorc]

Beats writing it as an array {1, 0, 1}, I wonder, can you match your columns into an array for larger sets for choose cols 🤔

[dolittlediehard]

Office 365 & office 2021 only right? I'm using office 2019 and it doesn't recognise either of these functions.

[amjan]

You can do the same thing with filter. Defining the columns 1 and 3 would look like this: {1/0/1}

[clarencelucas]

Is it possible to use sumifs and filter for multiple criteria and conditions to spill multiple months of data across same number of columns?

[UtuDudas]

Is there a way to keep the both the name column and the salary one, and do calculations in the salary one?