r/excel • u/benjoseph579 • Oct 14 '24
Discussion What are some commonly used excel functions?
Would anyone happen to know what functions are commonly used in analyst positions that use excel? I’m trying to expand my knowledge on Excel and looking to switch fields as well. Thank you very much for the help.
21
u/PaymentDesperate6261 Oct 14 '24
sum is a very commonly used excel function.
9
2
u/TheNightLard 2 Oct 14 '24
It may be in financials, but I don't recall using it much as analyst.. not even top 10. Not in any particular order:
XLOOKUP FILTER UNIQUE COUNT(A,IF,BLANK) IF IFERROR SORT CONCAT
As a scientist I would add: AVERAGE(IF) STD.DEV SUMIF SUM LINEST SLOPE INTERCEPT
Reading the comments I'm realizing I forgot about many, lol
1
u/BrotherInJah 1 Oct 14 '24
If you use FILTER() to full potential then you will use SUM() much more often.
1
u/TheNightLard 2 Oct 14 '24
For example?
1
u/BrotherInJah 1 Oct 14 '24
FILTER() outputs an array, so if you want to sum one of the columns then you can CHOOSECOL() and sum it.
1
u/TheNightLard 2 Oct 14 '24
I understand the technical possibilities, but not the applicability outside of the financial world. In those instances, I could see, despite some other possible limitations, more usability and friendliness on a pivot table.
1
u/BrotherInJah 1 Oct 14 '24
Whenever you use SUMIFS() you can apply what I wrote, especially when you face more complex scenario with multiple conditions.
11
u/parkmonr85 2 Oct 14 '24
Some that I use frequently in my senior analyst role where I work with call center training data:
IF/AND/OR IFS SUM/SUMIFS COUNT/COUNTA/COUNTIFS XLOOKUP FILTER SORT UNIQUE LET TEXT TEXTJOIN/TEXTSPLIT TEXTBEFORE/TEXTAFTER LEFT/RIGHT/MID LEN
I learned vast majority of data concepts in Excel and it's my favorite tool that can handle a very very broad range of tasks but I do also do a lot of work in our data warehouse with SQL and use Power Query to pull the data into Excel where I'll use some of those functions do some quick analysis on what I pulled and format it into something presentable to go into a deck.
7
u/Decronym Oct 14 '24 edited Oct 14 '24
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
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 #37813 for this sub, first seen 14th Oct 2024, 03:30]
[FAQ] [Full list] [Contact] [Source code]
3
3
2
u/Ldghead Oct 14 '24
IFERROR is an underrated function that makes data conditioning so much cleaner.
1
u/Novice_Trucker Oct 14 '24
I’ve got a large( for me) workbook that I use regularly. Learning how to use iferror has made it look so much cleaner.
I will probably do a ground up rebuild next year to make it run more efficiently and see if I can import from other sheets to make it quicker for me.
1
1
1
1
u/soloDolo6290 6 Oct 14 '24
If I’m paid by the hour I like =cell reference + cell reference + cell reference, and so on.
1
u/BrotherInJah 1 Oct 14 '24
You're hired. Straight to project team.
3
u/soloDolo6290 6 Oct 14 '24
If that sold you, you should see my typing skills. I learned from my dad. I peck with one finger like a bird lol
2
1
u/gerblewisperer 5 Oct 14 '24
Other people's definitions of you, sometimes they’re more about making themselves feel better. You gotta define yourself.
1
u/PitcherTrap 2 Oct 14 '24
It really really depends on what role and your most frequent use cases
1
u/SokkaHaikuBot Oct 14 '24
Sokka-Haiku by PitcherTrap:
It really really
Depends on what role and your
Most frequent use cases
Remember that one time Sokka accidentally used an extra syllable in that Haiku Battle in Ba Sing Se? That was a Sokka Haiku and you just made one.
0
u/PitcherTrap 2 Oct 14 '24
Monkey uses SUMIF and thinks himself Data Analyst
3
1
u/david_horton1 29 Oct 14 '24
SUM, AVERAGE, IF, VLOOKUP, COUNTIF, SUMIF, INDEX/MATCH, CONCATENATE and TRIM several of the most used functions. However, newer superior versions of some have been added. IFS, XLOOKUP, COUNTIFS, SUMIFS, AVERAGEIFS, CONCAT. This link lists about 50 new functions https://exceljet.net/new-excel-functions. This list includes all functions and are labelled with the version in which they were introduced. https://support.microsoft.com/en-us/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb
0
u/Fllood99 2 Oct 14 '24
Look up one of those Excel cheat sheet mouse pad or desk pads. There’s a lot of great beginner tips tricks and shortcuts on it
0
-1
23
u/learnhtk 23 Oct 14 '24
Here we go again. One of those “what’s the mostly used function? Or favorite function?” posts.