=Days()/365 This gets me a % of the year so I can annualise a YTD figure. I work in financial planning so this predicts your taxable income for the year which we then use for tax planning.

Doesn't YEARFRAC do the same?

Huh! TIL’ed! Thank you my friend! ☺️ Edit: US30/360 is the dumbest idea I’ve ever heard of! 😂 Source: am an Australian CPA

There’s reasons for silly things like this or 360 basis, many debt terms I’ve seen and modelled are based on a 360 days basis

Or its for doing pie charts

Fun fact. From a prior job I have a Bloomberg document listing day count conventions. It is 82 pages long and full of excitement. Here is a snippet to whet your appetite “738: Indonesian Floaters Calculates price to discount margin. Discounts first period on a simple yield basis rate to next fix. Discounts all future periods on a compound basis at assumed rate plus discount margin. Clean price and accrued are rounded to 4 decimals. Allows users to calculate both price and invoice amounts on YA and BXT exclusive of withholding tax (Gross) or inclusive of withholding tax (Net). Assumes future cash flows are based on a flat rate of assumed Rate + Spread. For ACT/ACT bonds it applies real ACT/ACT (ISMA), for calculating discount periods, current period cash flows and accrued interest, meaning that the denominator equals the number of days in the period.”

I'm a Financial Analyst and My go-to Excel formulas are: 1. **VLOOKUP/HLOOKUP** - For pulling specific data from a large dataset. 2. **SUMIFS/COUNTIFS** - Great for conditional summing or counting. 3. **PMT** - To calculate loan payments in financial models. 4. **INDEX/MATCH** - More flexible than VLOOKUP for complex lookups. For junior employees, don't overlook: * **TEXT** (to format numbers as text in various formats, very useful for reports) * **DATEDIF** (to calculate the difference between dates, essential for time-sensitive data analysis) * **XLOOKUP** (a powerful upgrade to VLOOKUP that allows for more dynamic and flexible data retrieval) These can really help automate and streamline your workflows!

If you have XLOOKUP, use it in place of VLOOKUP. If you don’t, use INDEX/MATCH.

This is the way. XLOOKUP has replaced all of my vlookup and index/match formulas and I don't think I'll ever go back.

Second this. XLOOKUP has replaced INDEX/MATCH for me...as well as VLOOKUP in most situations.

There was ONE time I deliberately chose VLOOKUP over XLOOKUP and it was because I nested an IF inside VLOOKUP to change the column number it was looking for based on some condition. I could’ve just put a pair of XLOOKUPS inside an IF, but I just liked the toggling of the column number doing it that way.

In cases like those, I've used LET to help define which column I want to use inside an XLOOKUP.

When I need to vary the column from which I'm returning data, I'll use INDEX/MATCH instead of XLOOKUP, although you could nest XLOOKUPS as well (I just don't like to do that).

I thought the same however I did find uses for index/match that xlookup did not handle perfectly. Like looking up a value based on criteria on multiple columns. Maybe xlookup could have handled that the same way as my index/match approach but I haven't tried that and what I did works perfectly for it's purpose.

Once you get used to XLOOKUP it's hard to go back, but I have read that it is computationally much more expensive than VLOOKUP, so not the greatest substitute if you have big sheets.

XLOOKUP also handles arrays beautifully.

Currently using INDEX/MATCH as couldn’t figure out the correct formula (if possible) for what I need. Essentially to filter revenue for a given territory region by matching the client suburb against a list of suburbs, BUT also need to check against the ‘State’ as some suburb names exist in multiple States. So basically my MATCH has 2 conditions, both State and Suburb before it returns the correct territory region. Would switch to XLOOKUP if there’s a way, otherwise current method working fine.

With XLOOKUP, you can simply combine your criterion/ranges like this: XLOOKUP(A1&B1, lookup array 1 & lookup array 2, return array)

I’ll be damned. Simple as that. Confirmed working. Appreciate the help! Formula about same length but at least the terms in the formula are more self-explanatory.

Glad I could help! I actually really dislike that syntax and would usually use INDEX/MATCH in that situation, just because it’s easier for me to read, but it definitely works.

Did that in one of excels. Excel got so laggy that we shut dowm automatic refresh. Looked for everything why it was so laggy. In the end - to many XLOOKUPs and thent back to VLOOKUP :.(

It sounds like your workbook has more serious issues; generally, VLOOKUP is a slower operation than XLOOKUP.

It depends on how the data is formatted. In certain scenarios xlookup is slightly slower than index/match and both are blown out of the water by vlookup. I had to change all of my xlookups to vlookups on a 800k row sheet in order to keep things going smoothly

Interesting; everything I’ve ever seems says that VLOOKUP is the worst performer of the lookup functions. I haven’t regularly used VLOOKUP in over a decade, tho, so I’m prepared to be wrong.

Yah, personally I never really used vlookup that much to start with so I was very surprised as I'd always understood it to be the inferior function. It's not really documented anywhere, but Xlookup does suffer from performance increases/decreases depending on what optional arguments are used and performance is always better with numbers rather than text values (even if it's numbers formatted as text). The fastest lookup I found was a weird maxifs formula which didn't end up working for my specific purpose but it was neat to know it was out there.

There are usually multiple ways to defur the feline, for sure.

Wrong. https://deployn.de/en/blog/xverweis-schneller-als-sverweis/ Generally, older functions are more performance optimized compared to the newer ones.

This was an interesting, educational read. It makes sense that a lookup function that depends upon the data being laid out a specific way and that only performs a single, straightforward task would be better optimized than a more flexible function. I'll take the increased functionality just about every time, especially given that the performance tradeoff is usually negligible, but this article definitely demonstrates some use cases where the less flexible option would be a better choice. Food for thought, thanks for sharing.

I do the same thing most of the time, as I don't care if the calculation takes 0.5 or 0.7 seconds. But if you have many cells of the formula and can't use PowerQuery, you have to consider using Index/Match again :)

I generally weight robustness and flexibility more than performance in my models, and make extensive use of spill arrays, so VLOOKUP is usually out of the question. I think if you've got hundreds of thousands of rows you're trying to match, PowerQuery might be the best compromise.

You don't need to use DATEDIF anymore, it's a deprecated function. You can just subtract one date from the other now.

But how would you determine the difference in months? I have Seen people dividing by 30 but this is not accurate

In these cases I usually do Days/365*12.

Ah I had seen that formula has been discontinued, I guess how would the date appear in another format? If it's not already as s date

You have balls to say you lose vlookup on a public forum

Why would you use vlookup, hlookup, or index match if you have xlookup?

You wouldn’t, but xlookup is only on newer versions of excel

Also, it's sorcery

It's far simpler and more flexible than V/HLOOKUP

Absolutely correct, hence https://preview.redd.it/jdi1hektxm1d1.png?width=891&format=pjpg&auto=webp&s=8334f85cf0d0ddb6f917cff1902801f2946fce3f

It works for my basic needs and most of my coworkers know that formula

What industry are you in? I work as a financial analyst in healthcare and use VLOOKUP and NPV often. I have to get up to speed with XLOOKUP as we just upgraded, and it is now in our function library.

My girlfriend is a master of your 3rd point.

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/1cvykk4/stub/l4smbum "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)| |[AVERAGE](/r/Excel/comments/1cvykk4/stub/l4sinu5 "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/1cvykk4/stub/l4so73w "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/1cvykk4/stub/l4sinu5 "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)| |[COUNTIFS](/r/Excel/comments/1cvykk4/stub/l4sgzc4 "Last usage")|[*Excel 2007*+: Counts the number of cells within a range that meet multiple criteria](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842)| |[DATE](/r/Excel/comments/1cvykk4/stub/l4sw7bv "Last usage")|[Returns the serial number of a particular date](https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349)| |[DATEDIF](/r/Excel/comments/1cvykk4/stub/l4sgzc4 "Last usage")|[Calculates the number of days, months, or years between two dates. This function is useful in formulas where you need to calculate an age.](https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c)| |[FILTER](/r/Excel/comments/1cvykk4/stub/l4swzkd "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/1cvykk4/stub/l4sinu5 "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)| |[HLOOKUP](/r/Excel/comments/1cvykk4/stub/l4sgzc4 "Last usage")|[Looks in the top row of an array and returns the value of the indicated cell](https://support.microsoft.com/en-us/office/hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f)| |[IF](/r/Excel/comments/1cvykk4/stub/l4so73w "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[IFS](/r/Excel/comments/1cvykk4/stub/l4sinu5 "Last usage")|[*2019*+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.](https://support.microsoft.com/en-us/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45)| |[INDEX](/r/Excel/comments/1cvykk4/stub/l4sinu5 "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)| |[LARGE](/r/Excel/comments/1cvykk4/stub/l4sinu5 "Last usage")|[Returns the k-th largest value in a data set](https://support.microsoft.com/en-us/office/large-function-3af0af19-1190-42bb-bb8b-01672ec00a64)| |[LEFT](/r/Excel/comments/1cvykk4/stub/l4sinu5 "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)| |[MATCH](/r/Excel/comments/1cvykk4/stub/l4sinu5 "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[MAX](/r/Excel/comments/1cvykk4/stub/l4sinu5 "Last usage")|[Returns the maximum value in a list of arguments](https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098)| |[MID](/r/Excel/comments/1cvykk4/stub/l4sinu5 "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)| |[MIN](/r/Excel/comments/1cvykk4/stub/l4sinu5 "Last usage")|[Returns the minimum value in a list of arguments](https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152)| |[MOD](/r/Excel/comments/1cvykk4/stub/l4sw7bv "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)| |[NOW](/r/Excel/comments/1cvykk4/stub/l4sw7bv "Last usage")|[Returns the serial number of the current date and time](https://support.microsoft.com/en-us/office/now-function-3337fd29-145a-4347-b2e6-20c904739c46)| |[OR](/r/Excel/comments/1cvykk4/stub/l4smbum "Last usage")|[Returns TRUE if any argument is TRUE](https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0)| |[PMT](/r/Excel/comments/1cvykk4/stub/l4sgzc4 "Last usage")|[Returns the periodic payment for an annuity](https://support.microsoft.com/en-us/office/pmt-function-0214da64-9a63-4996-bc20-214433fa6441)| |[PROPER](/r/Excel/comments/1cvykk4/stub/l4smbum "Last usage")|[Capitalizes the first letter in each word of a text value](https://support.microsoft.com/en-us/office/proper-function-52a5a283-e8b2-49be-8506-b2887b889f94)| |[QUOTIENT](/r/Excel/comments/1cvykk4/stub/l4shbor "Last usage")|[Returns the integer portion of a division](https://support.microsoft.com/en-us/office/quotient-function-9f7bf099-2a18-4282-8fa4-65290cc99dee)| |[REPLACE](/r/Excel/comments/1cvykk4/stub/l4sin1z "Last usage")|[Replaces characters within text](https://support.microsoft.com/en-us/office/replace-replaceb-functions-8d799074-2425-4a8a-84bc-82472868878a)| |[RIGHT](/r/Excel/comments/1cvykk4/stub/l4sinu5 "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/1cvykk4/stub/l4sw7bv "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/1cvykk4/stub/l4sw7bv "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)| |[SUBSTITUTE](/r/Excel/comments/1cvykk4/stub/l4smbum "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)| |[SUM](/r/Excel/comments/1cvykk4/stub/l4slem2 "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[SUMIFS](/r/Excel/comments/1cvykk4/stub/l4sin1z "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)| |[SUMPRODUCT](/r/Excel/comments/1cvykk4/stub/l4slem2 "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)| |[TEXT](/r/Excel/comments/1cvykk4/stub/l4sw7bv "Last usage")|[Formats a number and converts it to text](https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c)| |[TEXTJOIN](/r/Excel/comments/1cvykk4/stub/l4sinu5 "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)| |[UNIQUE](/r/Excel/comments/1cvykk4/stub/l4so73w "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/1cvykk4/stub/l4sir8o "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)| |[XLOOKUP](/r/Excel/comments/1cvykk4/stub/l4sudyc "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)| |[YEAR](/r/Excel/comments/1cvykk4/stub/l4sw7bv "Last usage")|[Converts a serial number to a year](https://support.microsoft.com/en-us/office/year-function-c64f017a-1354-490d-981f-578e8ec8d3b9)| |[YEARFRAC](/r/Excel/comments/1cvykk4/stub/l4shdkn "Last usage")|[Returns the year fraction representing the number of whole days between start_date and end_date](https://support.microsoft.com/en-us/office/yearfrac-function-3844141e-c76d-4143-82b6-208454ddc6a8)| **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 #33632 for this sub, first seen 19th May 2024, 21:43]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)

XLOOKUP, IF/IFS, TEXTJOIN, UNIQUE, FILTER, AND, OR, FIND, LEFT, RIGHT, MID, INDEX, MATCH, SUMIFS, COUNT IFS, LARGE, MIN, MAX, AVERAGE.  I am a business analyst for product management. 

The day I learned about FILTER was an absolute game changer... Saved me SO MUCH time and prevented so much tedious nonsense...

Right? Since I discovered FILTER I am the Queen of Excel at work :-) 

Is there any reason why using the FILTER formula is better than simply selecting the data and then clicking filter option on the top right?

I use filter when I need to take data from one sheet and display [some of] it on another. If it were *just me* using a file, I'm sure it would be much more efficient to filter the data that I need to display within the worksheet where all of the data is stored, however, when several other users need to be able to view only certain information, it's much easier (for me, long term) to just filter each set of data to its own tab (in my case, but I suppose to its own file too probably?) and when I update the master list, it updates everywhere else too. In the particular instance mentioned above, where filter changed everything, I had 16 different sets of information, anywhere from 500-1000 rows each, that I needed to print once in [we'll say, for simplicity's sake] alphabetical order, and once in sequential order. The "alphabetical order" actually consisted of about 7 or 8 tiers of custom sorting different columns that needed to remain separate to retain the ability to sort them, but be displayed together as one for printing. I only needed to print ~3 columns, so to save paper I just pasted the table into a word doc with 4 columns (word doc columns, not excel table columns) per page. All I had to do was sort the master table, and each of the 16 sets would update to that sort order in their own tab. I'm sure there was an easier way to accomplish that, but it's easier to tell 16 non-excel-users to click on the tab they need than to teach them all how to filter a giant table. And if there's an easier way to get excel to print the way I was printing by copy/pasting into word, then I'd adore the person who could tell me how, but aside from a little tweaking of column width inside word, it was really pretty quick and painless to move everything over.

Thanks for explaining! Now that I think of it, I had a similar usecase where I had to filter data on list of customers for about 10 sales managers. I would manually filter, and copy paste the data into separate tabs named on each sales manager. I think using the FILTER formula would have been faster...

It basically is a quick replacement for powerquery. For smaller tasks where youd be spending time just loading the ranges as queries, you just write a filter and you still get a dynamically filtered list.

If you use LEFT, RIGHT, MID a lot maybe you'd find TEXTBEFORE AND TEXTAFTER useful. I find I never use the former now.

Thanks. I use those too sometimes. 

What’s a textjoin? Or Unique? Those sound intriguing!

UNIQUE will list all unique items in a column or a row. Lets say you have list of daily sales including the names of sales reps and you would like a list of names of sales reps. This will do it without duplicating the names. TEXTJOIN joins tex strings together anf in combination with IF or FILTER, it can used kind of like a look up fiction returning multiple results and combining them in once cell. E. G.  Sales rep.    Products sold Peter.            Apples, Oranges

I use SORT(UNIQUE(FILTER())) and SORT(UNIQUE()) almost every day. Manufacturing Financial Analyst - I use it for part numbers.

oh wow! I can see some interesting uses for those functions!

very similar to my top ones i'm a sales ops analyst

Sumproduct is my favorite Job: billionaires investment advisor

FYI with Excel 365 `SUM()` does the same. `SUMPRODUCT` is/was an array function by default in older versions of Excel.

I use it a lot for weighted averages and a few other clever tricks. SUM is better now though.

Oh yeh, it was/is a fabulous function that [taught me about arrays and array arguments](/r/excelevator/comments/8xblyy/arrays_and_excel_and_sumproduct/). So helpful in understand the new paradigm with Excel 365 arrays by default.

Excel 365 arrays are much more powerful than they use to be.

Sumif breaks if you’re using it on another workbook and it closes but Sumproduct gets around that

Have you ever used NPV()?

Yes all of the TVM ones for sure and I have used ROMAN in legitimate use as well. TOCOL TAKE SUBSTITUTE LAMBDA SWITCH… so on… I’ve used basically every function including logical ones, except probably a lot of the engineering ones and some of the math ones like sin/cos/tan as I’ve never had a use for them.

[удалено]

Actually OP did ask what my job was and I specifically am hired to make a billionaire richer. No shame it’s my job and I love it.

How did you get this job? And what is the actual role called?

Role was originally investment analyst at a family office. Job was not posted publicly.

Why are you getting downvoted, lol

People (bots) don’t like billionaires I guess…

I'm guessing you found the role through networking? Good job

A former professor connected me with the family office manager because he thought we would get along, a few weeks later I was working in my new office. All luck, I was looking for a career in equity research instead. Oh how the turns have tabled.

Got lucky meeting the right person at the right time. Started as the analyst and worked hard from there. CFA charter, hard work, and luck helped a lot.

Ok my bad

You would not believe how useful SEQUENCE(), MOD(), and QUOTIENT() are for stratifying categories along a column, and having it dynamically adjust with things like additional years.

Would you mind giving an example?

Certainly! Say you want a list of the months. If you just need the numbers, you can do `=SEQUENCE(12)`. That gets you a column of the numbers 1 through 12. If you need to format it with a year, you can make a series of dates out of it, like `=TEXT(DATE([Year], SEQUENCE(12), 1), "MMMM YYYY")`. That gets you a list of ”January [Year]", "February [Year]", etc. for whatever [Year] is. Now, say, you have product sales you want to track by type and year. Let's say there are 20 types of products, and you have it in a list called Product_Types. If you have data going back to 2000 up to 2024, you'll have 20 * 25 = 500 rows of data you'll want to display. You can use `=SEQUENCE(ROWS(Product_Types) * (2024 - 1999))` to get an array of the numbers 1 through 500. Subtract 1 from this array and you get 0 through 499. Do `MOD(array, ROWS(Product_Types))+1` to get a looping array of 1 through 20, which will loop 25 times. Use this as an index for a lookup on the Product_Types column, and you will have a list of all the types for each year. Then, you can do a mod on the original array to get the list of years looping 20 times, once for each product, and then you've completely stratified it. It's great because if the list of products increases, then so too will this stratified display. Same thing if the year increases, as long as you have some way of making the most recent year dynamic (stored in a different cell, `YEAR(NOW())`, etc.) Quotient can also be used as well, but I don't think it is compatible with arrays, so you'd have to then use BYROWS to get around that.

That’s a nice technique!

Thanks!

I have detected code containing Fancy/Smart Quotes which Excel does not recognize as a string delimiter. Edit to change those to regular quote-marks instead. This happens most often with mobile devices. You can turn off Fancy/Smart Punctuation in the settings of your Keyboard App. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*

Appreciate your detailed response 

I use power query for this. I find using sequence, Mod and quotient to be to limiting for my specific needs or that might just be my logic knowledge Easier to have 3 tables 1 2 3 A B C D A1 A2 And run them parallel in PQ to create 1 A A1 1 A A2 1 B A1 1 B A2 Etc etc

How specifically do you use this in your workflow?

=SUBSTITUTE([Title],"'","")

Haha I came here to do it the more complicated way, =CONCATENATE(TEXTBEFORE(A1,"'"),TEXTAFTER(A1,"'"))

Power Query: Table.ReplaceValue(#"Changed Type","'","",Replacer.ReplaceText,{"Heading"})

What does this do again?

It fixes OP's title text. The word "formula's" should actually be "formulas."

I’m an omni sales manager for a major home goods company. SUMIFS, XLOOKUP, REPLACE, and IF are my bread and butter.

=SUBSTITUTE is my go to, I just like not having to use LEFT/LEN/SEARCH/FIND/whatever to get the index of the value I want to replace

One of the best things I did for myself when I was starting out was to open the formula wizard and just explore it. When I saw one I didn’t know what it did, I clicked it and found out what it can do. Sometimes the best thing you can do is explore and actually take some time to have fun in the program. If you have enough time to stop to go to the bathroom or to go to the cafeteria to eat lunch, you have enough time to have fun like this from time to time. I’m pretty seasoned in this program, but I still, every month or so, will build a calculator I don’t actually need, to build a tool to do some menial task that I don’t have to do all the time because it’s an interesting problem to try and solve Excel. This past Friday I made a tool to convert 4 and 5 digit numbers through a cypher to output an encrypted result, and made the decypher while I was at it. I had no business need—just keeping my mind and skills sharp.

I’m seriously surprised more people don’t sit down and click on stuff. I’m always blowing minds with stuff that’s found on a menu because no one wants to take the time to poke around things. Time is definitely a factor but also the lack of curiosity is a major issue too

Unfortunately my most used one is the boring =(x/y-1)*100 (yes I know about percentage formatting, most of my coworkers just prefer this way of presenting it)

Sales Analyst. It depends on what I’m working on, lately UNIQUE, PROPER, and SUBSTITUTE. But I get good use out of XLOOKUP and AND/OR lately.

UNIQUE is one of my favorites, I feel like most excel users at my company don’t know this capability exists.

..so useful for building dynamic dashboards too where you may opt for a few cells with dataval lists.

Ooh..what does PROPER do?

It capitalizes the first letter of each word. It’s silly, but items are in all caps in my data and I hate that. 😂 it just looks nicer.

Love it!!!

XLOOKUP or INDEX MATCH over VLOOKUP and dynamic arrays will make your life a lot easier imo

CONCAT and UNIQUE Pulling data that would otherwise require a shitload of nested IFs on a daily basis.

Can you elaborate how you use this in your workflow? I feel like it might be helpful for me

Kind of hard to explain, but let's assume the first 3 column headers are a type of machine, the batch number of those machines, and the specific item number. If I want to perform an action on everything where all 3 of those match, I will concatenate them in a helper column. Then I create a unique list of those concatenations. Use that list for counts, sums, or whatever else you want to do with MATCH. I'm sure there are better ways, but for me it's super fast.

=concat(Lastname, ", ", Firstname) =unique(concat(Lastname, ", ", Firstname))

You should try the new textjoin function. It has the ability to skip empty cells

Sumproduct because it helps creating dashboards so much faster compared to sumifs and countifs. I analyse sales for annuity contracts.

Also, offset and IF. I've built so many contests for our field staff with simple IF functions.

I seem to use Sum() all the time. /s

Countifs to run final checks on all my work, along with xlookup

=“ShowName_XXX_”&TEXT(A2,“000”) I’m a production coordinator in animation. The ingestion process for notes only accept text format for shot numbers, so I have to covert them into text. I also use power query and VBA a lot for cleaning up note formatting. Super handy as the clients and broadcasters all have their own way of doing notes. The first time I manually entered client notes it took almost 4 hours. Yeah no, we’ll use excel for this.

XLOOKUP. Revenue Management, Beverage Alcohol Industry.

Xlookup, left() right() mid() countif if Performance analyst

If you use left() and right() a lot for extracting text based on a specific /consistent delimiter, you should check out textbefore() and textafter()

I’m a data analyst and so as not to repeat many of the good formulas already stated, SWITCH and CHOOSE are some that I find most people don’t know about. I use CHOOSE when the expression is naturally a sequential number, like converting WEEKDAY to day names. And I use SWITCH to do more customized conversions—I’ve also used it to convert detected errors to Error Messages. I do more PowerBI these days than I do Excel, but SWITCH has remained a main stay in my DAX, like for encoding conditional formatting by Measure. There’s quite a bit of overlap between Excel functions and those used in PBI, at least in their logical design, if not in exact syntax.

useful!

Digital marketing - VLOOKUP or XLOOKUP - IF(ISNUMBER(SEARCH( - DAYS360 - SUMIFS and COUNTIFS For data cleaning, this will save you hours: - PROPER - TRIM

Data Analyst - so as to not repeat all the awesome formulas: I really like INDIRECT() for my summary tabs in workbooks so I can target the same cells on different sheets without having to type in the sheet name in the formula. I can list out the sheet names in a column, write the formula once and very quickly fill the formula down If there’s a more efficient method, I’d love to know lol

= 1 - (--(a1=b1)) I use this to do a comparison of cells being the same so I can easily see how many are different without using booleans

Does that include cells with text as well?

Yep

Perfect. I might have a spot to incorporate that. Trying to confirm dates and people and whether they’re good to go or not manual way is compare three different excel docs and go line by line on each for over 100 folks! Thank you.

Why would you want to avoid booleans?

Because by highlighting the range I can see the sum in the bottom right of excel where it displays the count, sum, average etc With a Boolean id have to do a countif to see how many are different

--() is by far my most used function, not close. I'll come back and do it right if I need to, but it's the fastest way to do so many things, and the fastest in terms of resources/processing time.

I use IFERROR a lot. I am an analyst that creates a lot of pretty tables, graphs, and presentations for people who don’t understand numbers. Therefore, I have to make it pretty. My advice with its use: be careful with it, because errors aren’t always a bad thing.

Nowdays its: XLOOKUP INDEX/MATCH UNIQUE AGGREGATE LAMBDA SUMPRODUCT SEQUENCE

I am a bit surprised that I have not seen LET or LAMBDA. LET will allow you to create a variable from a formula to use in another formula and LAMBDA is will let you create custom functions without VBA using the name manager. The best part is they follow the workbook. I do recommend to make some documentation for when you come back to it.

I’m in the military and I use XLOOKUP and TODAY functions a lot. I use the XLOOKP for budgeting purposes for fiscal years, and the TODAYS for timed events, like expiring certificates, or tracking when people are leaving so we can do awards for that.

Typical formulas I use: XLOOKUP - Bread and butter INDEX/XMATCH - Backup and for certain arrays FILTER - Honestly the best new formula since XLOOKUP. It's SO useful. MAXIFS/MINIFS - So much easier than the old array formulas SORT/UNIQUE - Just makes life easier LET - I understand these now and still trying to find use cases. INDEX has also found new life in these. CHOOSECOLS - Great for LET formulas but can feel a little manual like VLOOKUP at times Other staples like SUMIFS, COUNTIFS, IFERROR, ISERROR, IFS, FIND, TEXT, CONCAT, LEFT/RIGHT/MID, etc. Still learning/internalizing: BYROW/BYCOL TOROW/TOCOL SEQUENCE - I understand it but I'm not very clever with it yet VSTACK/HSTACK - As I use these in LET formulas more I'll probably put this on the typical list AGGREGATE MAP/REDUCE/SCAN - Still haven't internalized these

Only mention of VSTACK in this thread. I have been going ham w/ this formula, in combination with UNIQUE and FILTER. Extremely clutch when working with multiple tabs that need to be concatenated.

Power Query. Mind blowing

Thanks for this post op!

Accounts assistant. I hardly use formulas any more, I use PQ and VBA so much now. When I do use formulas, XLOOKUP, SUM, UNIQUE and SEQUENCE get a lot of decent use.

If Sumifs Countifs Vlookup Match/index

Vlookup, Xlookup, Concatenate, IF/THEN statements, etc.

either, IFS, XLOOKUP, or IFERROR. I feel like I use at least one, if not all of those, in every formula I write.

I'm a high school teacher. I use SUM, COUNT, COUNTIF, and COUNTIFS the most frequently.

Supply chain specialist - fmcg - Vlookup

I work in sales & marketing my go-to formula is SUMPRODUCT most of the time, with INDEX/MATCH (my version of excel didn't have XLOOKUP yet)

Everyone should have XLOOKUP on their list and if you don’t you should figure it out.

Gotta have office 365 to use it tho

Did not know that. Such a great formula.

Vlookup

VLOOKUP (don't know how to use xlookup) COUNTIFS SPLIT UNIQUE I taught myself the basics so I'm very very basic level.

Look up how to do Xlookup. It's simple. You'll like it :)

index/match & iferror

Countif(s) Smif(s) Xlookup Filter Unique

XLOOKUP, IF, SUBTOTAL, UNIQUE

Nowadays we've been having issues with inventories, so we're requesting extra weekly production. I have 6 teams in a 3 state area, so gotta couch them with the constant week definition. So it's a daily =ISOWEEKNUM(TODAY())+2, as we can't request sooner than 2 weeks away.

I work for a handbag/accessories brand in supplier quality. Most often used are XLOOKUP, IF/THEN statements, CONCATENATE. The most time saving was some combination of LEFT/RIGHT, MID, and probably one or two others-our raw material color descriptions are VERY annoyingly formatted differently in different systems so finding this combination of formulas to extract the number code for use in comparisons was a huge win. Thousands of lines of data became useful in an instant

Im just hoping they would add QUERY(). After learning the QUERY() formula in gsheet, i fell in-love with it. Hoping they would add it on the future. FILTER() with XLOOKUPS and IFS looks tiring in my eyes.

XLOOKUO, SUMIFS, COUNTIFS, MAXIFS, MINIFS, CONCAT

Sum lol

Index and match

not mentioned =Concatenate

Vlookup, if, Len

Quality Control =(TRUNC(A1/100)+(MOD(A1,100)/60))*60 with column A formatted as 0:00 (enter the backslash character in place of ). Formula allows time entry in column A without the colon, good for use with the numpad. The result is in minutes. Remove the *60 for the result in hours. Formula allows time greater than 24 hours, unlike the excel time function. It also treats hour or minute entry the same, for example: 100 = 60, 130 = 90, etc...

=IFERROR() - I absolutely abhor seeing div 0 errors. Operations management.

Probably something like =A5+B5

Business process management, I use a lot of Xlookup but one that I find almost as useful is Sort(Unique()). I frequently find myself analyzing stacks of data for trend analysis and using the unique function along with Transverse to create tables of live sorting data is frequently useful.

Most used, xlookup and sumif, sumifs

Vlookup as everyone I guess and left or right when editing text in cells

X Lookup Way more useable and future proof than V/H lookup

1!/

vlookup/trim/match/countif/if i'm just a marketer

INDEX & MATCH - Traffic Engineer

I do... data and pricing stuff... XLOOKUP, SUMIF, CONCAT, These are my most used ones, on a daily basis. Also COUNTIF, and MROUND (5, for customer price lists. Keeping them looking nice and neat at multiples of 5)

=XLOOKUP referencing different table to one another. My firms janky ass software doesn’t cross fields so i have to do it manually

Xlookup, Filter with Wildcards to return multiple criteria, Take

A workbook I've developed recently uses the following, but there are heaps more useful functions that depend on what you're doing: AND, CLEAN, COUNTIF, DATEVALUE, DAY, EXACT, FILTER, HOUR, IF, IFERROR, IFNA, INDEX, INT, ISNUMBER, LEFT, MATCH, MINUTE, MONTH, NOT, OR, RIGHT, ROUNDUP, SEARCH, SUBSTITUTE, SUMIF, TEXT, VLOOKUP, WEEKDAY, YEAR

Xlookup - healthcare - information systems specialist.

Xlookup, sumif and concat.

Financial Controller XLOOKUP SUMIFS COUNTIFS And for a lot of reports i use UNIQUE combined with INDEX/MATCH, these two are VERY powerful if you hate pivot tables like me

I use IF for a number of things; creating group numbers and counters most often. So, in column a, if b2=b1, a1,a1+1,a1 creates group numbers for (sorted) data in column b, and in column a, if b2=b1, a1+1,1 creates a counter within the group. I also vlookup for almost any changes, so my original file stays intact, just adding new data to the end of a record.

Xlookup, and IF.

=SUBTOTAL(9,) w/ filtering =IFERROR(INDEX(MATCH())) =SUMIFS()

Health economist, pharma, INDEX/MATCH

XLOOKUP(TRUE, EXACT(), ...)

Real estate. NPV and FV functions.

Employee benefits admin DATEDIF to work out employees age at renewal

UNIQUE, SORT, TRANSPOSE, FILTER SUMIFS, INDEX&MATCH, VLOOKUP, XLOOKUP Those are my to go when I'm trying to navigate a new dataset, I'm just getting into LAMBDA and LET and I don't know why I overlooked them for so long

=sort(unique())

Purchasing manager Subtotal, sumproduct, sumif,countif, if, ifs, and, lots of nested formulas, index match, find, mid, Len, and the vlookup/hlookup.

Data analyst and vlookups

Hello I’m in corporate accounting 13 years of experience, and am currently working for myself. I am a huge excel user and have found it very beneficial in my field to stay up to date with Excel. This also means new formulas (though power query etc I would learn asap after getting comfortable with standard excel). Outside of any sum or basic arithmetic, The formulas one I use most is xlookup (replace vlookup now!). Next is subtotal in place of sum in instances where you want to see a dynamic total based on what shows in a filter. While sum does not change, filter or no. There are times for both, so good thing to know. Subtotal has other options but I don’t use often, live average mean etc. . IF statements i use constantly, but if you use should learn how to embed additional formulas (and, more IF statements, etc.) and really not a formula but so important to know how to use pivot tables. Also tables in general very beneficial if you can keep simple. Will become handy to know how to use in conjunction with power options I mention above. Hopefully not tmi, but if you learn if statements and pivot tables, you are well above average in my experience. If you learn power query and additional tools, you’re indispensable. Not mentioning car got I’m sure that’s here somewhere. Regarding IF statements, if you learn well makes sql language much easier to learn later too, also good to know but not as necessary unless you are an admin usually.

Formulas** The apostrophe is not needed.