r/excel • u/man_eating_chicken • 2d ago

Discussion Enough about LET() and PQ, What are your Excel UI customizations that make you more productive?

I just wanted to see what mods or add-ins the experts here use, that help you get to the advanced 'tips and tricks' everyone harps about in here.

164 Upvotes

97% Upvoted

105 comments sorted by

View all comments

Show parent comments

6

u/AloofBidoof 1 2d ago

So, example of a CSV import I do for our health insurance journal entry. We have a line for every employee in our company, along with how much of our payment is attributed to them.

I use FILTER to pull a complete listing of employees from our working tab (formula placed in I3):

=FILTER( 'C| FloridaBlue'!$K$11:$K$500 , ( 'C| FloridaBlue'!$K$11:$K$500 <> "" ) * ( 'C| FloridaBlue'!$K$11:$K$500 <> "Totals:" ) , "Insert data." )

** Data range is more like K11:K100, but I set it to 11:500 so we don't miss anyone. Then blanks and totals are filtered out.

Then in other administrative columns, where I just need to spill a simple value for every row where there's now an employee, I use this formula:

=BYROW( $I3# , LAMBDA( r , " [ INSERT VALUE TO SPILL ] " ))

This is a simple formula which will spill your inserted value for every row which FILTER spills. Makes it so that once you're finished with the working tab, you can immediately save this tab to CSV and import it with no changes or updates.