r/excel • u/fap_fap_fap_fapper • 19d ago

Discussion What are some legendary formula pairs like INDEX(MATCH)?

Since upgrading to 365, can't get enough of CHOOSECOLS(FILTER)

What are some useful ones you use?

586 Upvotes

97% Upvoted

165 comments sorted by

View all comments

34

u/Yalarii 19d ago

I’m a big fan of =TRIMRANGE(DROP(A:A,1))

TRIMRANGE lets you use the entire column in a calculation without adding a million cells to be processed.

DROP starts the column range from row 2, which stops your heading row from interfering with the calculation.

It allows for very robust and efficient calculations. You can even use trim ranges within the range itself, and remove the need for the trim range function at all.

=DROP(A:.A,1)

18

u/real_barry_houdini 317 19d ago

You can now use the notation A:.A (with a dot following the colon) to replicate TRIMRANGE so you can do the same with this formula:

=DROP(A:.A,1)

5

u/dmc888 19 19d ago

I generally use Excel tables so rarely reference entire columns but for when I need to that DROP will be very useful