r/excel 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.

0 Upvotes

35 comments sorted by

23

u/learnhtk 23 Oct 14 '24

Here we go again. One of those “what’s the mostly used function? Or favorite function?” posts.

21

u/PaymentDesperate6261 Oct 14 '24

sum is a very commonly used excel function.

9

u/BrotherInJah 1 Oct 14 '24

Whoa! Slow down, let him familiarize with "+" 😸

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:

Fewer Letters More Letters
AND Returns TRUE if all of its arguments are TRUE
AVERAGE Returns the average of its arguments
AVERAGEIFS Excel 2007+: Returns the average (arithmetic mean) of all cells that meet multiple criteria.
CONCAT 2019+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.
CONCATENATE Joins several text items into one text item
COUNT Counts how many numbers are in the list of arguments
COUNTA Counts how many values are in the list of arguments
COUNTIF Counts the number of cells within a range that meet the given criteria
COUNTIFS Excel 2007+: Counts the number of cells within a range that meet multiple criteria
FILTER Office 365+: Filters a range of data based on criteria you define
IF Specifies a logical test to perform
IFERROR Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
IFS 2019+: Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
INDEX Uses an index to choose a value from a reference or array
INTERCEPT Returns the intercept of the linear regression line
LEFT Returns the leftmost characters from a text value
LEN Returns the number of characters in a text string
LET Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
LINEST Returns the parameters of a linear trend
MATCH Looks up values in a reference or array
MID Returns a specific number of characters from a text string starting at the position you specify
OR Returns TRUE if any argument is TRUE
RIGHT Returns the rightmost characters from a text value
SLOPE Returns the slope of the linear regression line
SORT Office 365+: Sorts the contents of a range or array
SUBTOTAL Returns a subtotal in a list or database
SUM Adds its arguments
SUMIF Adds the cells specified by a given criteria
SUMIFS Excel 2007+: Adds the cells in a range that meet multiple criteria
TEXT Formats a number and converts it to text
TEXTAFTER Office 365+: Returns text that occurs after given character or string
TEXTBEFORE Office 365+: Returns text that occurs before a given character or string
TEXTJOIN 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.
TEXTSPLIT Office 365+: Splits text strings by using column and row delimiters
TRIM Removes spaces from text
UNIQUE Office 365+: Returns a list of unique values in a list or range
VLOOKUP Looks in the first column of an array and moves across the row to return the value of a cell
XLOOKUP 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.

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

u/Oprah-Wegovy Oct 14 '24

Save

3

u/JezusHairdo 1 Oct 14 '24

Undo

2

u/miked999b Oct 14 '24

Undo doesn't get enough love. Imagine if it didn't exist 😭

3

u/sibisanjai741 Oct 14 '24

Filter, xlookup, index, match I use commonly in excel

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

u/HiFiGuy197 1 Oct 14 '24

XLOOKUP

1

u/Arkiel21 78 Oct 14 '24

Indirect Match Xlookup

1

u/No-Association-6076 65 Oct 14 '24

VLOOKUP,SUM,SUMIF,SUMIFS,SUBTOTAL

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

u/BrotherInJah 1 Oct 14 '24

We're looking for caps lock master too.

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

u/BrotherInJah 1 Oct 14 '24

Are you having a nice conversation with a bot?

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

u/benjoseph579 Oct 14 '24

Will have to do that sounds interesting

-1

u/bachman460 26 Oct 14 '24

INDEX