r/excel Jan 26 '24

In your opinion, what formulas are necessary in the work place? Discussion

I recently got a job interview for a company and they've asked for the following,

"You are to prep an excel file that you have built that shows off your skills. The purpose of this excel file is so we can see your excel skills and your level of knowledge utilizing excel.".

I used a lot of excel in school a year ago but would like to brush up on anything anyone could think of!

Thank you!

84 Upvotes

58 comments sorted by

u/AutoModerator Jan 26 '24

/u/jergennerd - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

150

u/bradland 89 Jan 26 '24 edited Jan 27 '24

Here's a short list of items I'd look for in a candidate:

  • IF(), IFS(), and SWITCH() for conditionals.
  • SUM() and SUMIFS() along with COUNT() and COUNTIFS().
  • SUBTOTAL() and AGGREGATE() to demonstrate the use of subtotals and aggregate functions along with a grand total.
  • XLOOKUP() for relational lookups. Bonus points for demonstrating both exact matches and next smaller or larger item lookups.
  • Use of TEXT() and VALUE() to demonstrate an understanding of text and numeric data types.
  • TRIM() for removing spurious whitespace.
  • LEN() for checking string length.
  • CONCAT() for building strings.
  • Dynamic array formulas like SORT(), UNIQUE() and FILTER() combined with a formula that uses the spilled range operator (#).
  • SEQUENCE() for enumerating lists.
  • ROUND() for limiting precision.
  • MIN() and MAX() for finding upper and lower bounds.
  • LET() for minimizing repetition and increasing readability.
  • IFERROR() for handling errors.
  • Pivot Table for summarizing data.
  • Pivot Table calculated column.
  • Pivot Table "show as % of total" column (or similar).
  • If you want to get fancy, use of the data model plus CONCATENATEX() (in DAX) to summarize text as values in a Pivot Table.
  • Power Query to pull in data from a folder containing multiple CSV files, remove extraneous columns, and clean up repeated header rows; load the data to a table in a sheet.

Really most importantly though is how you put these things together to solve interesting problems. Rote use of formulas will only get you so far. If you're a fresh graduate though, really I'm just looking to see if you have a passion for going deeper than simply entering text into cells and wondering why Excel doesn't magically understand that "John R. Smith" is not the same as "John R Smith", or other "easy for human, but hard for computer" leaps of cognitive function. I want to know that the candidate understands the fundamentals of how data works in Excel, and is willing to learn how to chain together a set of tools.

20

u/jergennerd Jan 26 '24

This is so helpful, thank you so much!! I'm going to get on Youtube ASAP lol

10

u/ISoldObamaMids Jan 26 '24

Also for display purposes, whatever examples you provide ( say index-match on one sheet, amortization table on another sheet) people tend to react well to a clear “title/home” sheet with a table of hyperlinks to take you to each sheet (and hyperlinks back to the “home” sheet) -It makes the navigation and understanding a touch more efficient for the user

2

u/wearsunblock Jan 26 '24

I could google this but very late and tired. Could you explain this “title/home” sheet?

3

u/tomsing98 25 Jan 27 '24

A sheet in the workbook with a title, company, author, date, revision, some info on what it is, and a "table of contents" telling you what's on each individual sheet, with links.

1

u/wearsunblock Jan 27 '24

This is great. TY. Why didn’t I think of this?!! Not enough ppl Do this

2

u/peekabook Jan 26 '24

Try techonthenet.com too! It’s the best! Comes with examples too

2

u/tallbluecoffee Jan 26 '24

Be warned, several of these formulas require the O365 subscription so if the company still uses Excel 2016 or 2019 then you will not have access to them.

1

u/jergennerd Jan 26 '24

thanks for the heads up, it looks like I might just have to get a subscription then

1

u/tallbluecoffee Jan 29 '24 edited Apr 13 '24

I think Google Sheets gives access to the O365 formulas without having to pay if you want to practice with them. Because you might get the subscription and then learn this company hasn't upgraded, so you won't be able to use those formulas at work.

1

u/F5x9 Jan 28 '24

The other thing to consider is excel formulas are a maintenance nightmare. They are devoid of whitespace, which makes them harder to read. When people start merging and naming things, it can get ugly. 

I like to treat workbooks as ephemeral. Usually, I have a csv file. I hit “format as table”. This gives me the autofilter, but you also get named columns. Named columns make references a lot easier, and formulas fill down. You can also mess around with power query to do joins and complex formatting. I don’t usually

When it comes to concatenation, you can use the “&” operator, which is easier to read. 

12

u/Altruistic_Fox1808 Jan 26 '24

this covers most of what I use (I do sales and inventory planning for a retail company). The one I would add is OFFSET; I partner it any arithmetic I need to do that needs to be adjustable, ie average the sales for a certain amount of months of which I can change how many months easily

8

u/rossco-dash 3 Jan 26 '24

There is a better way to do this (resource wise) that makes a huge difference on bigger sheets.

Offset is a volatile function, so it calculates anytime a change is made in a workbook. You can use INDEX(RANGE, COUNTA(RANGE)) to accomplish the same thing, and that is not volatile - it will only calculate if there is a change in RANGE.

Depending on how your data is structured in RANGE, you may need to use COUNTIF(S) instead of COUNTA.

1

u/Altruistic_Fox1808 Jan 27 '24

Do you have an example of this? When using offset, I reference a cell I change that determines the range length so it's worked perfectly for me (for example I'll couple Offset with average and have 5 in my referenced cell. This way offset would average the 5 values in the cell range). Am open to a better way to do this though

1

u/rossco-dash 3 Jan 28 '24

Offset recalculates when any cell in a workbook is changed. This will slow down workbooks if they’re large enough. So Offset in your workbooks is recalculating when anything changes, not just the reference cell for the range size.

Index only recalculates when the range is affected/changes. So it would only calculate when the reference cell changes, or if your data grows adding to your range.

6

u/Mooseymax 6 Jan 26 '24

SUMIFS, FILTER or XLOOKUP are far better solutions for this problem - in an ideal world I think OFFSET should be avoided.

6

u/Lord_Blackthorn 7 Jan 26 '24 edited Jan 26 '24

This is a great post, I'd like to add AGGREGATE(), as well, as it allows you to do math and ignore errors without any tricks.

Also I would like to add =SUMPRODUCT() as well, as there are a lot of math tricks with it.

6

u/bradland 89 Jan 26 '24

Also I would like to add =SUMPRODUCT() as well, as there are a lot of math tricks with itm

You tryna break the kid's brain? lol

Just kidding. Both of those are great suggestions. The earlier someone learns about SUMPRODUCT() and wraps their head around the way Excel handles arithmetic operations on non-numeric operands, the better.

2

u/Lord_Blackthorn 7 Jan 26 '24

Lol brain breaking is my favorite past time!

3

u/IcyPilgrim 1 Jan 26 '24

As an Excel trainer for 30 years, there’s some great advice here 👏 only change I’d suggest - AGGREGATE is better than SUBTOTAL(), as you can tell it to ignore Errors. Also make sure you show nested formulas, which will probably happen naturally with ROUND or IFERROR. could be as simple as =MONTH(TODAY())

2

u/bradland 89 Jan 27 '24

Good call. I added AGGREGATE() to my bullet point with subtotals.

2

u/semicolonsemicolon 1403 Jan 26 '24

CONCATENATE() for building strings.

This is pretty much the only one I disagree with. The newer CONCAT() function is useful. The older CONCATENATE() function can be universally removed and replaced with the & operator.

7

u/bradland 89 Jan 26 '24

Doh. Yeah. CONCAT() is what I meant. I was just typing from memory, and I’ve been using Excel a long time. Every time I start typing “conca” into a cell, the little deprecation warning next to CONCATENATE() catches me off guard lol.

1

u/lightning_fire 17 Jan 26 '24

Is there an advantage to CONCAT vs just using '&'?

6

u/semicolonsemicolon 1403 Jan 26 '24

CONCAT allows you to combine arrays into a single string (e.g., =CONCAT(B1:B8)) and you cannot do that with & unless you use =B1&B2&B3&B4&B5&B6&B7&B8.

20

u/Wilkinz027 Jan 26 '24

Depends a lot on use case. I use xlookup a lot to consolidate data from a bunch of sources and automate task I used to perform manually.

Also use Power automate and tables in general a fair amount

Power query and Pivot tables are also pretty cool

2

u/jergennerd Jan 26 '24

Thank you!

17

u/excelevator 2832 Jan 26 '24

What is the job ?

Learn what the job entails and reproduce a spreadsheet accordingly.

If you don't know Excel then reconsider applying for the job.

Do not get others to do it for you as you will not learn and will not be able to explain what you did in the interview, and you will struggle with the work.

16

u/maxchktw Jan 26 '24

This

It is weird to me that they want OP to show off Excel skill rather than working towards the goal.

If you show off Macros in the interview but ended up using just sum avg and pivot at work, this job is probably not a step up.

6

u/post4u Jan 26 '24

Yep. It's a stupid interview question. I could come up with the most amazing formula to solve a problem in physics that has nothing to do with, say, finance if this is for a finance job.

5

u/justsomerandomnick 1 Jan 26 '24

This was my first thought. It's a really unfocused and pointless request. "Just fill a spreadsheet with complex formulas. Doesn't matter what they do, just... formulas, you know? Lots of them."

Reminds me of that classic Dilbert:

"Make a spreadsheet and track something."

"Track what?"

"I think you'll find that it doesn't matter."

1

u/jergennerd Jan 26 '24

thanks for your reply! I do agree it is super vague. It is for a budget analyst position at a notable gas company here in the United States. I have experience using excel but it has been over a year and half since I graduated and have done the coursework. A lot of these replies are things I've done for school and just needed a reminder for going into this interview. it is a 60 minute live interview, I will edit my post to add this context. thanks again!

14

u/johnnywonder85 1 Jan 26 '24

do you get 'something' foundational to start you off? (there's literally billions of things you can achieve)
is it timed interview while someone is "watching" you? or,
is it a take home assignment?

11

u/learnhtk 14 Jan 26 '24

Oh, in that case, searching for "most frequently used excel functions" gives you a nice list of Excel functions.

But, I was going to say that,

it should be more about how to strategically manage data and design the process so that anyone else in the work place can replicate and complete the task.

For example, at my work place, we spend time fixing individual formulas because it's not designed with error handling in mind. People forget to appropriately adjust the cell ranges used in the formula, and everyone's skill levels differ. So I was going to say that using formulas should be minimized, because humans can and make mistake.

0

u/jergennerd Jan 26 '24

Thanks! :)

1

u/subsetsum Jan 26 '24

Match, index and VBA. Do a countifs as well

8

u/gerblewisperer 5 Jan 26 '24

Filter and Unique. Get away from pivot tables and slicers. I use dynamic drop downs based on filters and use those dynamic ranges as my data validation.

Also, I started using index/match within sumifs and xlookups so I have endless flexibility as columns get added.

Imagine setting a filter in sheet List in A1: =transpose(filter(Table!1:1, Table1:1!<>""))

Now I set data validations on to a second sheet "List!A1#" and in a third sheet I run my formula: =SUMIFS( index(Table!$B$2:$Z$1000, match('cell', Table!B1:Z1,0), 0), repeat for criteria, criteria1, repeat for criteria, criteria2)

I've made pivot tables that feel like webpages without any coding experience. My finance knowledge and accounting skills still being considered through this process.

6

u/Decronym Jan 26 '24 edited Jan 26 '24

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
AGGREGATE Returns an aggregate in a list or database
AVERAGE Returns the average of its arguments
BAHTTEXT Converts a number to text, using the (baht) currency format
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
INDIRECT Returns a reference indicated by 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
MAX Returns the maximum value in a list of arguments
MEDIAN Returns the median of the given numbers
MIN Returns the minimum value in a list of arguments
OFFSET Returns a reference offset from a given reference
PHONETIC Extracts the phonetic (furigana) characters from a text string
ROMAN Converts an arabic numeral to roman, as text
ROUND Rounds a number to a specified number of digits
SEQUENCE Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
SORT Office 365+: Sorts the contents of a range or array
SUBTOTAL Returns a subtotal in a list or database
SUM Adds its arguments
SUMIFS Excel 2007+: Adds the cells in a range that meet multiple criteria
SUMPRODUCT Returns the sum of the products of corresponding array components
SWITCH Excel 2019+: Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned.
TEXT Formats a number and converts it to text
TRIM Removes spaces from text
UNIQUE Office 365+: Returns a list of unique values in a list or range
VALUE Converts a text argument to a number
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 #30083 for this sub, first seen 26th Jan 2024, 04:30] [FAQ] [Full list] [Contact] [Source code]

5

u/chairfairy 203 Jan 26 '24

Weird assignment, but okay. Here are some thoughts on structure more than specific formulas:

Data organization is at least as important as the formulas you use. One common way to structure your file is to split it into different tabs - raw data in one tab, calculations in another tab (referencing the raw data), and summary/dashboard/visualizations in a 3rd tab (referencing the calculations). You might have multiple tabs of each kind.

Ideally raw data is in well-designed tables. Standard format is that each column is a different field and each row is a different entry.

Tables

I also like to format my data as Tables (capital T), which means select the data then hit Ctrl+T, or do Insert >> Table. This tells Excel that this is a functional group of data and lets you use "structured references" in your formulas. E.g. instead of =MEDIAN(D2:D6) you can have =MEDIAN(tblEmployeeData[Age]) (after you create a table, you can rename it with Formulas tab >> Name Manager). It makes the formulas more readable, and when you add new data at the bottom of the table it automatically expands to include that data. That's super useful because you don't need to update the MEDIAN formula or do something like =MEDIAN(D2:D1000) (look at more rows than you expect to ever use, and hope you're not wrong) - it just automatically continues to reference the entire data column. You can also add a new column by entering data in the next column to the right of the table.

Named ranges

This improves formula readability, like structured references do. Select a cell or range of cells that you want to reference in other formulas, then type a meaningful name for it (like a variable name in regular programming) into the navigation bar at the top left. You can edit existing named ranges with Formulas tab >> Name Manager

Basic shortcut keys.

These aren't a question of competence, but they make me feel more efficient. If a non-Excel person is looking over your shoulder, it also looks impressive while you flit around the file without the mouse.

F2 is huge for me: select a cell with data or a formula, and press F2 to start editing the formula (no need to click with your mouse in the formula bar).

Navigation and selection:

  • Ctrl+Arrow key jumps you around your sheet
  • Shift+Arrow key expands your selection
  • Ctrl+Shift+Arrow key jumps like Ctrl+Arrow and expands your selection
  • Home key jumps you to column A of your current row
  • Ctrl+Home jumps you to A1
  • Select one or more columns then Ctrl+D to fill down (populates whole column with value or formula from top row). E.g. enter a formula in C2, select C2:C100, and hit Ctrl+D. Or enter formulas in C2 and D2 (they can be different formulas), select C2:D100, and hit Ctrl+D.
  • Similarly, Ctrl+R to fill right. Fill down and fill right rely on you making good use of relative vs absolute references (those are the dollar signs you put in cell references in your formulas; though relative vs absolute reference behavior changes a little with structured references)
  • Ctrl+Space selects the entire column
  • Shift+Space selects the entire row
  • bonus: select a row or column, then Ctrl+Shift+Space to insert a new row or column

Graphs

Label your axes. Use graph types (line vs column etc) that make sense for the type of data you're presenting - line charts for things that are a single value with real continuity e.g. tracking your weight over time, column charts for things that are discrete events/phenomena e.g. a graph showing the average weight of different groups of people. Mostly avoid pie charts unless you have good reason not to. Donut charts are just pie charts with a hole in the middle, but can be useful as a "progress bar" kind of thing (I see this often in project management spreadsheets).

Stupid "trick" that kind of impresses some people

You can make your summary/dashboard page (the tab(s) the user looks at) a "dark mode" color theme. Make the background dark gray, make the grid lines the same color except for where you want to show them, and make font color white.

Things to avoid

Not universal, but common dogma among Excel users is to avoid merged ranges (when you select multiple cells and click Merge & Center) and the INDIRECT formula.

Ultimately, Excel is a tool. It's not a field of expertise in and of itself. People don't hire Excel experts - they hire finance experts or inventory experts or project managers, and Excel is one thing you can use to do your job better. So, by all means learn what you can about Excel, but also develop expertise in your actual field of work.

4

u/NoYouAreTheTroll 14 Jan 26 '24

Normalisation. leading to Correct table structure.

Insert - Table - Has headers, yes.

Make a few with ID make sure you have a ForeignKeyID matching a PrimaryKey ID on the tables.

Then, use the Data Tab

Relationships

Join Inner Join on ID

Then Insert - Pivot Table on the Datamodel

Then click the Pivot Table - Pivot Table Analyse Tab. - Fields, Items, and Sets

Create a new named Calculated Field name it

In the formula

=Table1[ColumnName]+Table2[ColumnName]

Save.

Now you have the calculated field in the available fields in your Pivot table.

Make a basic report and a graph.

Congrats, this is simple Excel usage with incredibly advanced data usage knowledge supporting lean practices.

3

u/W1ULH 1 Jan 26 '24

Dont forget array formulas!

There's a sheet my company uses a LOT. 6-7 months ago I was asked to do something about how slow it was...

the sheet goes out about 100 columns.

each row has 40-50 formulas in it.

I noticed that each row actually had the formulas in it!

one at a time I wiped those columns out, dropped a decent array into the top data row (set to go down 2000 rows with error trapping).

Took a full digit off the size of the file and sped it up 10x easily.

2

u/david_horton1 15 Jan 26 '24

If you have 365 look up 14 new Excel functions. Power Query and its M Code are recommended and 365 now includes Python. Also being able to present data in a clean and simple manner so that a manager can understand without need to refer to the creator is recommended. Pivot Tables are great for managers. For graphical presentation of data, Power BI may be an app to consider as part of a learning strategy. The important thing to understand is that learning Excel is an ongoing process. https://techcommunity.microsoft.com/t5/excel-blog/announcing-new-text-and-array-functions/ba-p/3186066

1

u/[deleted] Jan 26 '24

For corporate world, SUM(), AVERAGE() and XLOOKUP() :)

Also learn to format data as table so you can filter and sort easily.

1

u/PartagasSD4 Jan 26 '24

Proper() is a useful one that converts ALL CAPS cells to All Caps

1

u/gumburculeez Jan 26 '24

As said in other comments xlookup is great. When someone ask the generic “do you know vlookup?” Because that’s on every job post requirement, I say “of course, and hlookup as well, but honestly I prefer xlookup.” And they are dumbfounded and usually ask “what are those?”

1

u/4BlackHeart4 Jan 26 '24

XLOOKUP, VLOOKUP, HLOOKUP, INDEX, MATCH

COUNT, COUNTIF, COUNTA

IF, IFS

LEFT, RIGHT, MID, CONCATENATE

SUBTOTAL (you should specifically demonstrate using this for a table with filtered out rows, and only sum or count the visible rows)

Pivot tables, fancy conditional formatting, creating and choosing graphs that make sense for the data, using data validation to create drop-down boxes for cells, adding comments to cells, macros (if you have experience with them)

1

u/MoralHazardFunction 1 Jan 26 '24

XMATCH. It's like MATCH, but X-ier. In particular, it has more matching modes and searching modes, including the very useful 2/-2 binary search modes, which can speed up searching through large, ordered ranges immensely.

1

u/zeppo2k Jan 26 '24

Don't ignore formatting. As long as the numbers are right if they're well laid out and easy to understand that can be more important than how elegantly you got there

1

u/fabuzo Jan 26 '24

Nowadays, anything you can think of, you can just ask chatgpt to write you the formula for it.

1

u/jergennerd Jan 26 '24

Wow! Thank you so much everyone that's replied, I have been studying all day thanks to you guys! For more context, this for a budget analyst position for a gas company in the United States. I absolutely agree it's a vague question, which is why I really wanted to brush up and get some opinions. It is a live interview. I had used excel in the past and have done a lot of this, but its been a lot of time passed and would like to refresh my memory. Thanks! :)

1

u/390M386 3 Jan 27 '24

For me, in these types of exercises, im looking for logical model building rather than usage of formulas. Of course formulas will be utilized inherently.

Im looking for something that may have assumptions sheet, data, any other reference sheet, and a calculation sheet, and a summary/output sheet.

Formulas to co-mingle everything. Im def not looking for this cell equals that cell from that sheet lol. Use what you know but im looking for a model that makes sense. Especially coming out of college i don’t expect formulas wizardry yet.

1

u/nryporter25 Jan 27 '24

Xlookup is the most versatile thing I've done across and had been able to face my team members hundreds and hundreds of hours when i make them tools throughout the year

1

u/Illustrious-Engine23 Jan 28 '24

Honestly, if it's an budget analyst role as you say, I would just create some cool stuff with powerBI, power query and power automate.

1

u/Free-Gigabytes Feb 01 '24

When I hire people I want to know if they can:
Sort and filter?
Create charts and graphs?
Can they SUM/AVG and use other formula functions?
Do they know how to use a pivot table, and what it's for?
Do they know any advanced functions - like creating their own formulas and successfully making combined charts?

Here at my company, we use Excel for almost everything. They need to be at advanced if not expert levels, but if they have basic skills, we can teach most everything. The most important thing is to be able to demonstrate that you CAN figure things out and that you will remember what you learned going forward. If someone shows me those skills, I can teach them anything. :)

-2

u/perk11 Jan 26 '24

ROMAN

BAHTTEXT

PHONETIC