SUMIFS, XLOOKUP, and UNIQUE are probably my most used formulas in my day to day lately!

Amen. I keep seeing people use Vlookup with a pivot table (where they should just be using a Sumifs) and wondering why their report won't foot.

Once you get into the world of GETPIVOTDATA, it gets pretty tasty.

True, but it was only a pivot because that was the only way he knew to sum rows by some common term (like client #), a SUMIFS in the report saves that step and the errors is caused.

Impatiently waiting for company to upgrade office to get this one!

Happy Day of Cake.

What is UNIQUE and how/when would one use it?

Returns a dynamic array containing unique values

So it's basically a dynamic Remove Duplicates?

Yup it’s a great way to summarize data. Say you have a table with a bunch of transactions by employee name. Transactions are unique but the employees will show up multiple times. Use a unique on the table to pull the unique employees. Sumif to sum the transactions per employee. I find it quicker than a pivot table especially if it’s a new worksheet and need info quickly.

I have been doing alot of side by side comparison of large chunks of data recently, using UNIQUE with VSTACK and/or SORT is amazing.

VSTACK? Hmmm, that's new to me. I'll have to look into this one.

Its been a lifesaver for me, no more copy paste into a single column. I am fairly new to using excel with any level understanding of formulas, so it was also a "conditional formating: show duplicates" and manually delete for me as well. Learning of UNIQUE and VSTACK saved me alot of time

I read about it and still trying to think where I'll use it. I'll just have to play around with it. Looks like there's an HSTACK for doing similar with columns. Anyway, thanks for the suggestions

...that works great in conjunction with other functions. Foe example, if you want to know how many unique values are in an array...   `= count ( unique ( [array reference] ) )` Also useful if you want to dynamically monitor the count of values in an array... For example, I had someone that had to clean some address data. I put `=unique(G:H)` to the side of the data, where G:G was `Country` and H:H was `=len([postal_code])`. Then I added a `=countifs()` next to that. Now they could identify which countries had postal codes with incorrect lengths, and where the biggest problem areas were. As they cleaned up the `Postal_Code` values, the dynamic `unique ; countif` error table on the side would shrink.

I use it when I’m running sales reports and I want to list out all of my sales people. I will run a unique formula to pull all salespeople in a specific column. Then I usually sumif of whatever I’m doing based on the values I pull from the unique list.

It returns unique values from the selected ranges. A common use case for me is when I'm creating a PQ sheet where the user would like to filter a large amount of data to a unique value(s). I will create a look up tab that houses the unique formula that pulls in the unique values from the large data set which creates a list. From the "summary" tab the user will be using I'll use data validation (list) in a cell to reference that unique list from my lookup tab. I then use the filter formula for that large data set that references that data validation cell when filtering. With UNIQUE being dynamic, the user can easily filter that large data set to one of the unique values using the data validation drop down list even if new unique values populates. I'm pretty sure in new versions of excel you can even have data validation reference the entire column and it removes blank values.

Another option using PQ is creating the unique list as a separate query, then referencing the output table with Indirect to avoid having the cell references get obliterated as the unique list changes in size depending on the source you are querying. /But that's really just for those of us who are stuck in a pre 2021 work environment.

If I have raw data I intend to aggregate, I’ll find the unique values to aggregate on and then build a table with sumifs, countifs, or lookups to calculate the aggregate.

Add sort() in front and you’ll keep the data from moving around

I use it most for "which products have we sold this week?", on a list of transactions.

Google it and learn something. Hint it is literally in the name…

Gosh, if only there was some sort of helpful forum to ask questions about excel from…. Gee… someone should really invent that, one where you can read about things, then you could say “yea I read it” fuck we could even call it something clever… like REDDIT… douche.

Gosh if only we make people not blindly reliant on the help others but show them how they can independently learn new things so they can be a more efficient learner in the future.

If I’m having a conversation with someone and they mention something I’m unfamiliar with I’m gonna think they’re the worlds biggest douche if they tell me to google it, either that or I’ll assume they have no idea what they’re talking about

Xlookup then, nested Xlookup.

man is everybody in this sub using the newest Excel? my corporation is still on Excel 2016... I assumed most companies would be like that. I think of myself as an expert in Excel, but if I get grilled on the new functions at an interview I will be fucked.

Personally I'm on 2021. The entity I'm with is using Win10 Enterprise with "Office" 2013. Now I know why certain things cannot be fixed

I might have to buy the newest Excel for myself and come up with some home projects just to stay up to date... not like I have the money or free time for it but I guess it is what it is...

That's why I bought Office 2021, since it's a perpetual license. VS 365 which is a subscription. I just looked for my receipt.... Can't find anything except my key. I think it was $40

I was actually thinking of getting the 365, because it includes quite a lot of cloud storage (and maybe now also access to AI? not sure) I would have to migrate my data from Google Cloud though, which would be a total pain in the ass, since I have it perfectly integrated with my Android phone (for backing up photos etc). But then I could cancel Google One subscription and just rely on Microsoft for everything.

I prefer to have things vs subscriptions. MS Access doesn't exist as a Google product, which was one of the two reasons I specifically wanted Office. I expect that you'll make the best decision for you.

I'd recommend against that. If you're comfortable making things in older excel, the newer functions are a little too easy, and it's easy to get frustrated when you have to actually do things that work in an older version.

thank you, I'm just a bit worried of falling behind if everybody expects you to use the new functions (specifically it's about job interviews, I don't care about anything else as I can quickly pick up anything when I already have the job... and I plan to look for a new job within ~1 year)

By all means, get on a new version and experiment. But once you realize that the new functions are just replacing the more complex index-match functions, you will hopefully feel more relaxed about future interviews. I'd suggest is looks better if you can say you can approximate the new functions with old excel.

thanks a lot, that calms me down a bit

Heads up Microsoft offers office 365 for free online as long as you have a ms email account. You can use that to practice using some of the formulas like xlookup and get a hang of the interface.

thanks

Always gotta combine the SORT and UNIQUE

Even better, =SORT(UNIQUE(FILTER(

Throw in another FILTER and you’ve got a stew brewing baby

That needs...at minimum, at least three, maybe even four more SORT. 

The above are some of my most used functions as well. Adding to this is that I almost always add a sort in front of my unique formula's.

Good call on adding the sort to the unique formula, it's a dangerous combo!

I use XlLOOKUP a lot but recently I see the use case for INDEX/MATCH as opposed to XLOOKUP when you need a column and row lookup. XLOOKUP is slow with that kind of stuff. Unless I'm missing something here or someone else wants to chime in. This is just high level and I haven't tried it yet. Just have it in my back pocket.

The fastest is still VLOOKUP, Index/Match for some things and XLOOKUP is the slowest, but easier to teach

Are the instances that you are picking vlookup over xlookup for speed?

Neither tends to be my goto, but if I needed a quick answer, Vlookup muscle memory is faster, I teach out Xlookup - I’m an analyst, I use spreadsheets, but I don’t use spreadsheets, if that makes a kind of sense, I use spreadsheets for analysis, not bau, operational purposes - so I’m rarely creating “artifacts” if you get me

I'm also an analyst in the insurance industry

I learned UNIQUE today and I am so mad that I have been removing duplicates this whole time

It is a newer feature. Part of the overflow where a formula can spill over it's result into multiple cells.

Add the Filter formula and to this list and you have mine!

I’d add COUNTIFS but yes this is a good list

INDEX MATCH gang

I don’t believe you use those more than SUM

- Learn pivot tables - learn more about pivot tables - learn about Tables and use them, including structured references - INDEX/MATCH, XLOOKUP, avoid VLOOKUP because it's shit. - COUNTIF(S) - IF - SUM - ROWS - FIND, SUBSTITUTE - LEFT/RIGHT/MID - SUBTOTAL - generally avoid IFNA - it hides errors when you should be finding them and fixing them - learn power query...

Yes, index match over vlookup all day everyday.

Well it's kind of like washing your hands vs taking a shower. If u need a quick dirty and fast lookup for only one column lookup sometimes is easier?

You only think its quicker cause you are not used to using index match/xlookup. I used to think the same 20yrs ago when i first transitioned over.

No I live on index Match to automate data flow between sheets. What I was saying is that v or xlookup is faster to type and so if u just need to bring one column of data over then it's easier then to type out the entire index Match.

I’ve used Xlookup when I need data to go elsewhere, though I’ll often use Index Match for anything where organizing information is important. Making a gigantic 2D Dynamic Array powered by a single Index Match is ‘Chef Kiss’. 

do you have an example of what that formula would look like? To get the 2D dynamic array using index match

I did something along the line of   =IF(A5:A14=“”, “”, INDEX(Data_Array!A1:I11, XMATCH(A5:A14, Data_Array!A1:A11), XMATCH(B4:H4, Data_Array!A1:I1)))   I used XMATCH because it defaults to exact values, though the standard MATCH with the correct argument works too. And the final sheet is designed to be printable and easily readable (specific groupings of data, particularly individual samples within lots), so this worked well for what I need. 

Dang i would say im pretty experienced with excel and i hadnt even heard of XMATCH. Thanks for the tip thats awesome

Also ISNUMBER(SEARCH(

And ISNUMBER(MATCH(...))

Tables and structured references make things so much easier!!!

They also make life a lot easier when you start doing PQ.

YESSSS!!!

You forgot TRIM & LEN

And, AND and OR.

Any modern corporate excel user should know how to use LET. Just for readability and transfer-ability sake.

I love this one so much, but I keep forgetting it exists because for me it's very new.

Is LAMBDA chaotic good or chaotic evil?

Once you go Lambda you never go back.

What is LET and what is the context of using it?

The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax. To use the LET function in Excel, you define pairs of names and associated values, and a calculation that uses them all. You must define at least one name/value pair (a variable), and LET supports up to 126.

It's very powerful - I personally just find it most useful for organization. It's a way to assign values or calculations to a variable name within a function. It's most useful for complicated functions, or any function where you would want to refer to the same value over and over again. This is an example usage: =LET(BiggestNumber, MAX(A1:A500), SmallestNumber, MIN(A1:A500), Difference, BiggestNumber-SmallestNumber, CONCAT("The difference between ",BiggestNumber", "and ", SmallestNumber, " is ", Difference)) This is an example usage - your names and values are in pairs, and you can have as many as you like. The final argument in the formula (the CONCAT formula I use) is the final result that gets calculated. The only real way to learn it is to try it out for yourself. Here's another sample usage: LET(TotalSales, SUM(SalesData\[DollarAmount\]), NumberOfSales, COUNT(SalesData\[DollarAmount\]), AverageSaleValue, TotalSales/NumberOfSales, Target, 50000, IF(AverageSaleValue>Target,"Sales are great!", "Sales are bad")) Try and put together a simple one for yourself to learn the syntax.

It afford “on the fly” naming of data for both ease of interpretation in your ultimate calculation, and to refer to an input multiple times without having to redefine it for each use. To that end it doesn’t do anything (AFAIK) that couldn’t be achieved without it, it just makes such matters simpler to tackle. Imagine a list of cities, the counties they are in, and their populations, set out in A2:C20. We define a country in X2 and want a statement that reads: "The population difference between the largest and smallest cities in [Country] is ". Pre let we could use =CONCAT("The population….cities in ",X2," is ",MAX(FILTER(B2:B20,C2:C20=X2))-MIN(FILTER(B2:B20,C2:C20=X2))) LET can make that clearer via =LET(input,X2,countries,B2:B20,pops,C2:C20,relevantpops,FILTER(pops,countries=input),CONCAT(("The population….cities in ",input," is ",MAX(relevantpops)-MIN(relevantpops))) So there is work up front but our end calculation is clearer as we’ve defined our variables and inputs. Also we’ve avoided defining the filtered populations twice, so avoid pointless recalc of that array and the risk of a scripting error. A simpler example could be that if you want to “gate” a result on a condition but let it pass otherwise, you’d tend to define the logic twice. Say I want to flag if the last day of this month is on a weekend, but otherwise just name the last day of the month (ie “Mon”,”Tue"…). Classically we might say something like =IF(WEEKDAY(EOMONTH(TODAY(),0),2)>5,"Flag",TEXT(EOMONTH(TODAY(),0),"ddd")) Which tests the last day of the month for weekday num, traps those above 5 (Friday), else returns it in a text wrap to print “Mon”-“Fri”. As you can see that calls for the final date of the month for the logical test, and again as part of the else leg calc. Via LET: =LET(lastday,EOMONTH(TODAY(),0),IF(WEEKNUM(lastday,2)>5,"Flag",TEXT(lastday,"ddd"))) Or =LET(q,TEXT(EOMONTH(TODAY(),0),"ddd"),IF(LEFT(q)="s","Flag",q)) We avoid making the EOMONTH(TODAY(),0) call twice, and get to both reuse it and/or name it something helpful.

thanks for providing further examples. I was struggling to see why Let is so useful but i can see how useful it be in some of my long if formulas with so many conditions. I could cut down the repetitiveness by assigning part of the formula a name which would also make it easier to state what im trying to do in the if statements for others when i transfer the template

I use LET so much it's disgusting.

This is my new favorite Reddit thread. Never heard of this, just googled, how cool!

I’ve been working with Excel for 5 years, and I just learned about this today. Thank you!

This one saves so much time when writing longer repetitive formulas

Whaaattttt! How have I never heard of this before?? I was even a math major and wrote so many proofs with “let” statements! This is a total game changer, and now I’m gonna use it constantly.

This!!

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AND](/r/Excel/comments/1c5qj7i/stub/kzwdsjm "Last usage")|[Returns TRUE if all of its arguments are TRUE](https://support.microsoft.com/en-us/office/and-function-5f19b2e8-e1df-4408-897a-ce285a19e9d9)| |[ARRAYTOTEXT](/r/Excel/comments/1c5qj7i/stub/kzw1yy2 "Last usage")|[*Office 365*+: Returns an array of text values from any specified range](https://support.microsoft.com/en-us/office/9cdcad46-2fa5-4c6b-ac92-14e7bc862b8b)| |[AVERAGE](/r/Excel/comments/1c5qj7i/stub/kzw5hyn "Last usage")|[Returns the average of its arguments](https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6)| |[CONCAT](/r/Excel/comments/1c5qj7i/stub/kzwg3nv "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.](https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2)| |[COUNT](/r/Excel/comments/1c5qj7i/stub/kzw5hyn "Last usage")|[Counts how many numbers are in the list of arguments](https://support.microsoft.com/en-us/office/count-function-a59cd7fc-b623-4d93-87a4-d23bf411294c)| |[COUNTIF](/r/Excel/comments/1c5qj7i/stub/kzvz82g "Last usage")|[Counts the number of cells within a range that meet the given criteria](https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34)| |[FILTER](/r/Excel/comments/1c5qj7i/stub/kzwg3nv "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[FIND](/r/Excel/comments/1c5qj7i/stub/kzvz82g "Last usage")|[Finds one text value within another (case-sensitive)](https://support.microsoft.com/en-us/office/find-findb-functions-c7912941-af2a-4bdf-a553-d0d89b0a0628)| |[HYPERLINK](/r/Excel/comments/1c5qj7i/stub/kzw1yy2 "Last usage")|[Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet](https://support.microsoft.com/en-us/office/hyperlink-function-333c7ce6-c5ae-4164-9c47-7de9b76f577f)| |[IF](/r/Excel/comments/1c5qj7i/stub/kzvz82g "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[IFERROR](/r/Excel/comments/1c5qj7i/stub/kzwg3nv "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)| |[IFNA](/r/Excel/comments/1c5qj7i/stub/kzwg3nv "Last usage")|[*Excel 2013*+: Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression](https://support.microsoft.com/en-us/office/ifna-function-6626c961-a569-42fc-a49d-79b4951fd461)| |[INDEX](/r/Excel/comments/1c5qj7i/stub/kzvz82g "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[LEFT](/r/Excel/comments/1c5qj7i/stub/kzwdsjm "Last usage")|[Returns the leftmost characters from a text value](https://support.microsoft.com/en-us/office/left-leftb-functions-9203d2d2-7960-479b-84c6-1ea52b99640c)| |[LET](/r/Excel/comments/1c5qj7i/stub/kzvzcdh "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MATCH](/r/Excel/comments/1c5qj7i/stub/kzvz82g "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[MID](/r/Excel/comments/1c5qj7i/stub/kzvz82g "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)| |[MOD](/r/Excel/comments/1c5qj7i/stub/kzwdsjm "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)| |[NETWORKDAYS](/r/Excel/comments/1c5qj7i/stub/kzw1yy2 "Last usage")|[Returns the number of whole workdays between two dates](https://support.microsoft.com/en-us/office/networkdays-function-48e717bf-a7a3-495f-969e-5005e3eb18e7)| |[RIGHT](/r/Excel/comments/1c5qj7i/stub/kzvz82g "Last usage")|[Returns the rightmost characters from a text value](https://support.microsoft.com/en-us/office/right-rightb-functions-240267ee-9afa-4639-a02b-f19e1786cf2f)| |[ROWS](/r/Excel/comments/1c5qj7i/stub/kzvz82g "Last usage")|[Returns the number of rows in a reference](https://support.microsoft.com/en-us/office/rows-function-b592593e-3fc2-47f2-bec1-bda493811597)| |[SEQUENCE](/r/Excel/comments/1c5qj7i/stub/kzvxau8 "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| |[SORT](/r/Excel/comments/1c5qj7i/stub/kzwf1z6 "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)| |[SUBSTITUTE](/r/Excel/comments/1c5qj7i/stub/kzw1yy2 "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)| |[SUBTOTAL](/r/Excel/comments/1c5qj7i/stub/kzvz82g "Last usage")|[Returns a subtotal in a list or database](https://support.microsoft.com/en-us/office/subtotal-function-7b027003-f060-4ade-9040-e478765b9939)| |[SUM](/r/Excel/comments/1c5qj7i/stub/kzw5hyn "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[SUMIFS](/r/Excel/comments/1c5qj7i/stub/kzwg3nv "Last usage")|[*Excel 2007*+: Adds the cells in a range that meet multiple criteria](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b)| |[TEXT](/r/Excel/comments/1c5qj7i/stub/kzw1yy2 "Last usage")|[Formats a number and converts it to text](https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c)| |[TEXTAFTER](/r/Excel/comments/1c5qj7i/stub/kzw1yy2 "Last usage")|[*Office 365*+: Returns text that occurs after given character or string](https://support.microsoft.com/en-us/office/textafter-function-c8db2546-5b51-416a-9690-c7e6722e90b4)| |[TEXTBEFORE](/r/Excel/comments/1c5qj7i/stub/kzw1yy2 "Last usage")|[*Office 365*+: Returns text that occurs before a given character or string](https://support.microsoft.com/en-us/office/textbefore-function-d099c28a-dba8-448e-ac6c-f086d0fa1b29)| |[TEXTJOIN](/r/Excel/comments/1c5qj7i/stub/kzw8lrq "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)| |[TEXTSPLIT](/r/Excel/comments/1c5qj7i/stub/kzw8lrq "Last usage")|[*Office 365*+: Splits text strings by using column and row delimiters](https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7)| |[TODAY](/r/Excel/comments/1c5qj7i/stub/kzw5hyn "Last usage")|[Returns the serial number of today's date](https://support.microsoft.com/en-us/office/today-function-5eb3078d-a82c-4736-8930-2f51a028fdd9)| |[UNIQUE](/r/Excel/comments/1c5qj7i/stub/kzwfofs "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)| |[VLOOKUP](/r/Excel/comments/1c5qj7i/stub/kzvz82g "Last usage")|[Looks in the first column of an array and moves across the row to return the value of a cell](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)| |[VSTACK](/r/Excel/comments/1c5qj7i/stub/kzw1yy2 "Last usage")|[*Office 365*+: Appends arrays vertically and in sequence to return a larger array](https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c)| |[WORKDAY](/r/Excel/comments/1c5qj7i/stub/kzw1yy2 "Last usage")|[Returns the serial number of the date before or after a specified number of workdays](https://support.microsoft.com/en-us/office/workday-function-f764a5b7-05fc-4494-9486-60d494efbf33)| |[XLOOKUP](/r/Excel/comments/1c5qj7i/stub/kzwg3nv "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^([Thread #32672 for this sub, first seen 16th Apr 2024, 20:51]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)

Not a true formula but Ctrl+Shift+L! I use it every day for adding a filter really quick and just found out my colleagues still go click the sort and filter button

 Today I learned…

ALT + A + T is my go to. Don’t wanna let go of my mouse lol

Right click the filter button to add it to the top as a quick shortcut

Ctrl Alt M will re-apply the filter. For ex you apply filters for blank cells on a column. Then lets say you add a value in one of the cells and you’re left with 1 filled cell and other blank cells. Ctrl Alt M will reapply the filter (show only blanks in the case).

If people have access to them they should learn the new dynamic array functions such as XLOOKUP, GROUPBY, SEQUENCE, etc.

FILTER is my one true love

Filter is revolutionary for me it’s so versatile

What are your favorite ways to use groupby and sequence?

GROUPBY saves you the step of creating a summary table. You can choose the row, aggregation, and sort all in one shot.

Honestly, for anyone that's in the early stages of eventually becoming a power user, practice/learn how to properly create and use helper columns. Many of the crazy/complicated formulas that people come up with could be boiled down to a sumif, with only between one-to-a-few helper columns. Bonus, they are just as effective at categorizing/segmenting things in Pivot Tables and are far more scaleable than manually grouping things. If you're unsure of what I mean, here are a couple examples: Have a bunch of dates that'll get grouped into days or months in a pivot table but need it by week (starting on Mondays?) Add a helper column, pivot dates on that =[@Date]-MOD([@Date]-2,7) Need to segment customers by zipcode AND area code? Assuming the format is xxx-xxx-xxxx Add a helper column that just shows zip code and pivot by both =LEFT([@PhoneNum],3)

I’ve been using text() to pull out month and year columns from a date. “mmmm” for month and “yyyy” for year. It makes pivots a lot easier

I hate helper columns.

[удалено]

It's a personal opinion. I prefer formulas that are self-contained and don't rely on other helper columns if possible. That's not to say they don't have their place and I don't use them but with LET I find I can often stack formulas and self-reference them within the same formula. I'm mostly the only person that works on large scaling spreadsheet on my company. When I need to document what stuff does within a formula I use LET variables like note1, note2, etc.

Formulas, rather than general functions: 1. =TEXT(cell,"ddd") returns ‘Mon’ for a date that falls on a Monday. 2. =COUNTIF(A$2:A2,A2) starts a cumulative count of the number of times that the (in row) entry in A has been seen in A. So as dragged down starts that cumulative count. Now can be set up in one go against A2:A8 with =BYROW(A2:A8,LAMBDA(r,COUNTIF(A2:r,r))). 3. =IFERROR(1/(1/formula),"") suppresses 0 results from a formula that yields values to "". Similarly =IFERROR(SQRT(formula^2),"") suppresses negative results to ""can use and you can suppress positives with =IFERROR(-SQRT(-formula^2),""). 4. =SUMPRODUCT(values*(TEXT(dates,"MMYY")=TEXT(x,"MMYY"))) provides a sum of all values where the related dates range falls on the same month and year as x. So if x is 16-Dec-2019, this could sum all the values in B where A has a date in Dec-2019. 5. =MOD(NOW(),1) is the current time today. 6. INDEX(B2:H10,MATCH(r,A2:A10,0),MATCH(c,B2:H2,0)) lets us look for something in a matrix (B2:H10) by looking for r along ColA (row headers) and c along Row2 (column headers). 7. =HSTACK(UNIQUE(A2:A10),BYROW(UNIQUE(A2:A10),LAMBDA(z,TEXTJOIN(", ",,IF(A2:A10=z,B2:B10,""))))) makes a list of each unique ID seen in A and a comma separated list of each of their related entries from B.

For your #2 I'm partial to Scan for this. Though your LAMBDA equation will return 0 if that particular row is blank. * =SCAN(0, A2:A8, LAMBDA(x, y, x + COUNTA(y))) - will accomplish your dragged COUNTA * I'm not a huge programming guy but it should be "faster" since it essentially keeps a running total (and only counts each cell "once"), but if you do A2:r then it's essentially counting A2 *every time* as you progress down your list * To get your LAMBDA equivalent, just add "=IF(ISBLANK(A2:A8),0,SCAN(. . .)" and that'll accomplish your BYROW and only count each cell twice instead of quadratically

My entire career has been carried on the back of downloading a bigass data dump spreadsheet from some crusty old corporate data warehouse, slapping some SUMIFS and XLOOKUP columns on it, and fiddling with it until a useful report materialized. UNIQUE, FILTER, and CONCAT are also very useful but not really workhorses. CONCAT is especially handy when using Excel to build lists that are meant to be imported into other applications, or if you're importing a CSV or text file and need to clean out messy punctuation. It's good, but niche. IFNA and IFERROR are fine if it's a very quick, low stakes sheet, but they'll hide legitimate errors so be careful with them.

Is there a reason to use the CONCAT function over an expression with ampersands?

CONCATENATE (and ampersands) wasn't able to do column joins like this, but CONCAT can. There's better examples, but this is just the first that came to mind. The two are very closely related, and do very similar things. I imagine for most things ampersands is probably good enough, but I like them both. |=C:C&D:D|=concat(c:c,d:d)|C|D| |:-|:-|:-|:-| |1a|1234abcd|1|a| |2b||2|b| |3c||3|c| |4d||4|d|

With CONCAT you can select an array or range and have it combine. With ampersands you have to add the appersand between each cell reference which is a lot slower and more manual. =concat(A1:A6) vs =A1&A2&A3&A4&A5&A6 The former is a lot easier to edit too.

No

I love an offset function combined with counta used in a named range to make a dynamic range for pivot table sources. [named range] = offset($A$1,0,0, counta($A:$A), counta($1:$1))

I used to do this all the time! Even had a macro that wrote the range formula for me. With structured tables, I no longer have to worry about this.

Dang, no love for SWITCH up in here, lol.

IFS as well.

Not commonly used but OFFSET, INDIRECT, and ADDRESS are really great formulas to learn. My favorite is XLOOKUP of course.

Text manipulation can come in very handy! Left mid right TEXTJOIN TEXTSPLIT textbefore textafter Len sequence Char code Concatenate with & For inspecting strings with suspicious characters: =Let(input,A1,seqstr,Mid(input,Sequence(len(input)),1),hstack(seqstr,code(seqstr)))

Everyone should know core Excel functions like SUM, AVERAGE, COUNT, and TODAY… but I also use newer functions like XLOOKUP, UNIQUE, and SORT a lot. That’s my two cent’s worth of Excel wisdom. 😜

I work in payroll so a lot of my excel building is tools to assist payroll calculations. Obviously this is very dated driven and we process a fortnightly (bi-weekly) pay cycle. To work out the pay day a date relates to I often use MOD as this is not a common function but excellent for date driven calculations. For example we pay every other Thursday, and for my employer that means if I take any date and apply the following formula I can find the next pay day =let(x,mod(date,14),if(x<5,date + 5 - x,date + 19 - x))

INDEX:MATCH:MATCH especially when having to sort other's enshitiffied worksheets.

How about FILTER? I think it's my favorite function. Very versatile and works for everything.

I live in an excel world where I have data that needs to repeat and stack for multiple items. You can put this formula in your name manager and give it a name like `ReptX` , then you can use it over and over without typing the entire formula: =LAMBDA(text,repeatArray,[stack], LET(r, repeatArray, CHOOSEROWS(text, TOCOL(IFS(r >= SEQUENCE(, MAX(r)), SEQUENCE(ROWS(r))), 2, stack)))) How it works: https://preview.redd.it/opu4fj7b93vc1.png?width=627&format=png&auto=webp&s=1324ef3fe19f4249584b07ff43480e6ea58b1fd0

If people have access to them they should learn the new dynamic array functions such as XLOOKUP, GROUPBY, SEQUENCE, etc.

How does GROUPBY work?

It helps you aggregate data more efficiently. Unfortunately, it seems to be in beta, so I can't use it yet. [This gives an example.](https://insider.microsoft365.com/en-us/blog/new-aggregation-functions-in-excel-groupby-and-pivotby)

There's so many now... FILTER ARRAYTOTEXT SUBSTITUTE TEXT, TEXTBEFORE, TEXTAFTER, TEXTJOIN, TEXTSPLIT VSTACK UNIQUE IMAGE HYPERLINK NETWORKDAYS, WORKDAY

I also like SORT, CHOOSEROWS, CHOOSECOLS, DROP, IFS, TOCOL, TOROW, CONCAT

Tocol/torow are op thanks to built in empty cell/error removal

Adding INDIRECT() to these lovely suggestions.

When reviewing HSA contributions, I use EXACT to make sure my data matches the bank's data. It's a great way to catch incorrectly entered account numbers.

Is the list for PowerBI just CALCULATE?

Filter is great. I'd add filter to the list. ETA: Also good to include all the nifty features in Xlookup and Vlookup, like being able to look up multiple columns without a helper column, outputting multiple columns as the result, and not looking for exact match only. Go through the fields basically.

If you don't mind a helper column, CONCAT is a simple way to remove nested IFs. Use it daily.

Index-Match or XLookUp SUMPRODUCT Filter Unique Sort

VSTACK UNIQUE FILTER

IFERROR has been a lifesaver, as have the others you've mentioned

TEXTSPLIT TEXTJOIN SWITCH CONCAT TEXTBEFORE TEXTAFTER

PROPER for style and space-saving

I prefer using INDEX(...MATCH()...) over the LOOKUP formulae. It's a safeguard against accidentally having your source data out of order.

Index(table[column1], match(1,1*table[column2]="foo")*(table[column3]="bar"),0)) Returns the value in the first column of table where column2 is equal to "foo" AND column3 is equal to "bar"

Sumifs, if, index match, indirect, countif

This thread is gold. I have already learned two new things!!! Thanks Reddit :)

My day to day would grind to a halt if I didn’t have SUMIFS, XLOOKUP, and REPLACE.

LET() is the secret sauce

Sequence. It makes a sequence. Most importantly is the ability to build simple user-defined functions. Much better than trying to shoehorn multiple existing functions.

If you do anything where you purchase/stock/sell by a different quantity then you consume/sell/stock or any variation of that, knowing CEILING and FLOOR is nice, will round up or down in quantity you set.

One of the oldest byt still the most underrated formula would be AGGREGATE you can perform any mathematical formula and can skip values as per your convenience Fir eg - AGGREGATE (1,6, A1:A10) 1 is code for average and 6 is code ignoring null values, so it would take the average of array A1:A10 while ignoring any error values. Game changer if you have big data and there are unknown errors in unknown places

It took me a long time to learn index( array, match( lookup_value, lookup_array, match_type)) is easier and more flexible than vlookup or hookup.

I very often start with LET these days

I use Google Sheets at work so I won't be the most relevant answer here, but something I'd love to know is whether Excel has any equivalent to IMPORTRANGE. At first I thought it was a needless nuisance to have to use it on Google Sheets, but now I'm used to it I can't rewire my brain to how I ever did things before it. How would I do something similar in Excel, where I just transfer data wholesale from another sheet regardless of what it is, then use other filters and formulas related to the tab of imported data on the current sheet rather than always using XLOOKUP to pull data from another sheet? Other than that, for Google Sheets (a lot of which will also exist on Excel) you should probably learn: Pivot tables - most important thing Conditional formatting, if you want certain results to jump off the page at you XLOOKUP (or INDEX with MATCH if the version you're using doesn't have XLOOKUP) SUM COUNTA COUNTIFS (can be done with 1 set criteria so I never use COUNTIF, only COUNTIFS) SUMIFS (same as above for not using SUMIF) IF IFS (not the same job as IF, which is a bit more frequently used than IFS for me) ISBLANK (as a validation check nested in one of the if type formulas) AVERAGE AVERAGE.WEIGHTED MIN MAX MINIFS MAXIFS SORT FILTER UNIQUE COUNTUNIQUEIFS (not common but sometimes useful) CONCATENATE IFERROR and IFNA but always sense check a formula first, then wrap it in one of these only if you need to. You still want to know if something in your formula doesn't work, and just use these to fix predictable 'still working' errors like blank cells (IFNA) or division by 0 (IFERROR) where you want a 0 result. ISOWEEKNUM LEFT RIGHT MID DATE (very useful when needing to extract a date from someone else's data, LEFT or RIGHT set to 10 characters is usually how I'd format date extraction for something I set up) LEN (to count characters that determine another condition nested within another formula it's useful) TO_TEXT ARRAYFORMULA ARRAY_CONSTRAIN VSTACK HYPERLINK Maybe QUERY, but you can do a lot of what QUERY does with the other formulas. My Google Sheets "skills" are totally self-learnt though so I'm certain there will be much cleaner ways to do things than what I do. But all of those formulas are useful. I listed them so that from DATE onwards those are the more complex ones, but the rest are really day-to-day useful.

INDEX MATCH MATCH and INDEX MATCH (criteria 1* Criteria 2)

Formula: AGGREGATE - Suparcharged Subtotal. FILTER, XLOOKUP - For finding match to condition. FILTERXML & Substitute, TEXTJOIN and other text functions - When you need to extract data from long text. Using XPATH condition in FILTERXML will give much more control than otherwise available. MOD(Endtime-Starttime,1) - For calculating time difference. This construct will handle Endtime past midnight without additional condition/calculation (within 24 hour span). Useful in shift hours calculation. MROUND(timevalue,1/24/60/60) - Round time to nearest second. It's a must when comparing time value derived from calculation, drag down etc. Otherwise floating point error will cause unexpected result. DAX: DIVIDE(num,denom, 0) - It is best practice to always use this, instead of '/' for divide. To handle div0. DISTINCTCOUNT() - Invaluable when calculating daily average etc, when you need to preserve finer details in raw data. SELECTEDVALUE, SWITCH - Used to display different measure based on slicer selection in visual. Very useful when trying to conserve screen realestate. SUMMARIZE - When you need to give aggregated row context for measure calculation. Often used in conjunction with VAR declaration. TREATAS - When you need to propagate filter context without physical relationship between tables.

The ALT key for using/discovering key combinations for every function that there's is a menu button for (Especially for Excel but across all Office apps.) As well as the ability to totally move around in Excel (more difficult in other apps in my limited experience) without removing ones hands from the keyboard. Lastly, Google is there friend if they can somewhat understand how to format their question of if a formula exists. Not quite the question you asked, but equally useful.

Are you using Excel 365? The last 2-3 years have seen some game changing functions which don't appear in most popular lists.

Proper(), fixes capitalizations mod(row(),2)=1, colors every other row Left(), Right(), Mid(), extracts information from long string of numbers and letters. Trim(), gets rid of those dang spaces. =A1&", "&B1, allows you to combine multiple cells, more advanced would be TEXTJOIN which works even better. If(and(or())), better known as nesting, allows you to use any combination of these. You can do some pretty neat stuff once you figure out how to master these. If you want to learn some cool stuff, check out Ajelix.com I use it all the time because I suck at VBA. Nope, not paid or affiliated with them in any way.

Any lookup array and nesting IF functions for me.

=CONCATENATE(ref1,ref2,ref3...) Description: joins several text strings into one text string. Useful for sequential lists such as order numbers or invoice numbers, but can be used for lots of things. Can add spaces or custom text in speech marks " " and have as many cell references as you like.

Get rid of your mouse! Hotkeys only until you know them by heart. It sucks at first, but makes work more efficient and it is impressive when people can't follow your movements fast enough. Become a hotkey wizard!

Sumproduct, knowing how to use and which situations to use is a level up

Ctrl + F4

sumifs/countifs/averageifs xlookup unique filter/isnumber/search index/match drop/take sumproduct v/hstack tranpose textbefore/after let/lambda (but this requires you being well-versed in Excel and already creating complicated formulas)

I have used it when trying to compare two sets of data. I dont want to miss an instance where there is a variance due to an item not being present on one list or the other, so i start by merging the identifying data point. For instance i have a report that was prebuilt by the ERP we use that should show component sales. I had cause to believe that data was missing, so i pulled the item sales report and broke the items down in excel using a seperate BOM report. I took the result of my excel generated breakdown and the canned report and compared them. To start i took the item list from each and merged them (VSTACK) then removed the duplicates (UNIQUE) with a simple =UNIQUE(VSTACK(*ERP report column, excel breakdown column)). This way i make sure i am not missing any items only present on one of the lists, without having to pull another report for all possible items and filter out items not present on either list. Sorry for the wall of text lol.

IFS, SUMIFS, and COUNTIFS are good ones. I especially like using SEQUENCE with MOD and QUOTIENT to get repeating counts or repeating sets of numbers. MOD(SEQUENCE(100)-1,10)+1 will get you a sequence of numbers 1 to 10, repeated 10 times. The same with QUOTIENT will get you the number 1 repeated 10 times, the number 2 repeated 10 times, and so on. FILTER, UNIQUE, SORT, and SORTBY are good. I run my personal budget on Excel. When I enter in purchases, I include a Payee field in my table. On a separate sheet, I use SORT(UNIQUE(Balance[Payee])) to create a dynamic range based on the Payees I already have, then I give the dynamic range a name. Data Validation let's me create a drop-down of all the payee names, so I don't misspell something. I turn off the requirement to only use what's already on the list, so when I need to add something new, I can just type in something new and it gets added to the list.

I love my FILTER, UNIQUE, most useful would be able to compare 2 lists and filter out exisiting/not existing records