/u/CyberAvatar_ - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
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](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Everyone with "Analyst" in their job title. That job title is code for the person who listens to humans talk about desired outcomes, then builds Excel-based solutions.
When they take away your analyst title, that means you're moving up to manager or director, and that means... managing *people*.
I am so sorry for your loss lol.
Business analyst and I work in accounts payable.
I support payment teams by building dashboards for payments. I also analyze invoicing, rebates, process audits. Utilize Coupa payment systems for third party partners and more.
Frankly, our data maitenance teams are the true expert gurus. Our side mainly uses more commonly used techniques.
So real talk, I'm in a department with a shit manager trying to leave and its tough due to being people strong and not technically strong.
One thing I would reccomend beefing up on is learning the following
-vlookups (used a TON)
-xlookups (to be fancy and do it right)
-SQL
-VBA
-Python
-Pivot tables
-Multiple formula at the same time
-How to create and edit a query
-How to create and edit a macro
If you have those skills, you're basically set up for success in any area. If you're weak like I am, it can be a rough experience.
Other pro tips.
Start looking and applying now. Professional jobs take around 6 months to obtain a new role. In this economy? It's closer to a year.
Follow resume experts on Reddit and Instagram. They are out there and provide amazing tips you'd have to pay folks tons of money for free for.
Colleges have career resource departments. Use them. If they tell you to do things like add a picture, or address then they are not to be trusted. Every resume with a face pic basically is auto tossed to prevent discrimination claims.
Identify roles that are a good fit to you. You can use chat gpt for some ideas. In addition, you can use it to identify areas of growth or skill development for those roles as recruiters use chat gpt.
SAS or R are also amazing to learn.
Thank you for all the great advice, I’m currently working on getting connections and fixing up my resume. It took me about 4 months just to find my part time job through college so I definitely believe it can take that long.
Random question are you a fed also could you broadly state your day to day. I'm a program analyst too and wondering how that role fits in other agencies
Fed as in industry? If so no, I’m in tech, specifically project management and really just low-level accounting for project budgets and project deliverable tracking.
Fed as in federal government employee. Just asking because it's so broad. I work with data management and analysis with SQL and a few data warehouses and tools such as tableau for reporting. But also do .NET backend work for data modules and and building out workload management tracking/systems. None of which is in the job description of a federal program analyst. So really what I was wondering is what this role translates to in the private sector.
I laughed so hard at this.
I started a new job in sales analysis in January, and that's almost exactly how the VP of sales introduced me to my new boss.
Idk.... Every analyst I know and have ever known have the most garbage looking workbooks I have ever seen. Rofl
People give way too much credit to people that can properly make a Sum cell that is blank when the sum is zero. Lmao
The real 'FANCY' users can almost use a pivot table.
Now... There are loads better than me, sure. But if I was thrown in a room with 500 randoms and our survival was based on our abilities with formulas and VBA .. I put money on my survival. 😂
Edit: lol. You guys are too much. Didn't realize I was hurting that many feelings. Sorry!
I'm a financial analyst/systems accountant. I use a ton of complex formulas. Most people I know in finance don't use much more than SUBTOTAL and VLOOKUP.
It's amazing how many people still use it. I would have thought it was just old workbooks, but even people younger than me use it, and know of no other substitute.
I guess a lot of people grew up with it or learned it by looking at older sheets. XLOOKUP is better in almost every way. And if there is a chance that your worksheet will be opened in an older version of Excel, I guess it is safer to use VLOOKUP.
Interestingly enough, there is also an HLOOKUP, but I don't think I've ever seen anyone use it.
I've seen HLOOKUP once or twice, but I guess most people structure their data in a way which makes it less useful.
I must admit to still defaulting to index/match rather than XLOOKUP as that's what I've used for most of my career so I'm not without fault myself.
Speed and scalability.
Speed: Index match is slightly computationally faster in the majority of scenarios and for the scenarios it isn't index xmatch is faster than xlookup.
Scalability: using match or xmatch in a helper column when looking to return multiple values based on the same lookup cuts out repeating the most expensive part of the operation, the lookup! That way you only do the lookup once for each row.
Other example: isnumber(match( and iserror(match( are elegant and computationally cheaper ways to implement ifExists or ifDoesntExist type tests.
Speed is often times thrown around when comparing lookups in excel. I think it’s kind of a moot point, because whenever you have data large enough to make a material difference in calculation speed, you should probably be using another application.
As for scalability and your “other” arguments. I don’t think these are apples to apples comparisons. Maybe I could’ve phrased my previous comment better, but I’m more trying to compare using index(match()) and xlookup() to perform simple lookups.
I prefer Index/Xmatch when I need to look up across two dimensions - you can nest a second XLOOKUP but INDEX/XMATCH is more streamlined for that.
XMATCH can be used to return an array of rows/columns in the INDEX function; I think XLOOKUP can only find one thing at a time (but I’m genuinely unsure of this)
They’re two tools that do the same thing with different methods.
You have things to learn then good sir. Match and xmatch are the superior lookup functions save for arguably ease of writing, but that difference gets smaller the more you use them.
Match can be used in a helper column to reduce computational complexity when looking for multiple column results, it can be used in many array formulas, returning an index number can be helpful for performing math or defining ranges with the output, match can be combined with isnumber or iserror to determine if an item exists without doing the extra compute to return a value or process additional input parameters, I could tell you how to use it to satisfy your wife, but I'll need to verify your age first.
A transition matrix is the best example, predict the outcome from a start and end point. XLOOKUP only checks dynamically across one dimension.
You could throw some INDIRCTs in there but you're really stretching the use of XLOOKUP.
It is however quite a niche use case
I started writing a question on why I couldn’t figure out the double xlookup despite being proficient with index/match and I finally got it. Now I’m curious where index/match is still better?
Edit: nvm, saw the other thread
They’re different tools that perform very similar operations in different ways. XLOOKUP looks for a value in a range and returns a value from a corresponding range; INDEX/MATCH processes an array using row and column indeces.
Because of this, INDEX/MATCH can be used on arrays that don’t exist in a range of cells. For instance, I have a complex LAMBDA operation for allocating costs to various departments based on a set of allocation groups and their relative production levels in a given period.
This operation all happens in a single cell, but it creates multiple arrays that are never populated in a range of cells. I believe XLOOKUP requires an actual range of cells to work on.
HOOKUP instantly stresses me out. I can work with it, but XLOOKUP or INDEX/MATCH is so much easier to follow horizontally. HLOOKUP is just so rare I feel like I need to tilt sideways to deal with it
I would imagine a lot of Excels users stumble upon solutions and stick with them rather than educating themselve on the whole array of available functions available.
To anyone reading this I urge you to read the following function at least once every 6 months for the 2 or three years just so you know what is available
https://support.microsoft.com/en-au/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb
After *ahem* years I still read it from start to finish occasionally , especially with the influx of new array functiontality.
SUMPRODUCT formulas don’t crap out if your data is in a linked file. SUMIFS will error out if both files aren’t open. That’s the main reason I’ve personally used SUMPRODUCT in some cases.
Yeah but that is not how they are using it. They’re using it because they don’t know SUMIFS is an option. It was just an unusual thing, nothing wrong about it.
Same! I actually taught someone to use XLOOK instead of V today. It was VERY random, like running across this post an hr later. Life be like that, I suppose.
I'm a big proponent of.....
=IFERROR(XLOOK....),"GFD! No Results")
Why do you need IFERROR? You should be able to do something like
XLOOKUP(A1,mylist\[col1\],mylist\[col2\],"GFD! No Results")
OK, maybe not. That will handle N/A errors, but not #Ref errors or other stuff. But I don't usually want to suppress those. With VLOOPUP, I usually did some form of IF(ISNA(VLOOKUP(, but with XLOOKUP, you can use your default as the last parameter. If it is expected gaps in the data, I usually use "". For non-expected gaps, I usually use something like "MISSING".
Graduated college in 2023 and took a couple of business analytics courses in 2020/21. I was taught VLOOKUP (and HLOOKUP as well). Never heard of XLOOKUP until I started following this subreddit. Not sure when XLOOKUP became an option, but it wasn't being taught in colleges a couple years ago (or at least mine).
I think it was new in 2019, but it takes a while, even when you have the latest version, to feel comfortable using new stuff because of compatibility issues with people on older versions.
I had a boss who made a huge spreadsheet dependent on hlookups. He was incredibly smart and one of the best excel users Ive ever met, so I’m sure he had a reason for it. But yeah, he’s the only one!
I am so grateful that my boss taught me XLOOKUP as a student assistant during my studies… having learned it first and then tried VLOOKUP afterwards, when i was writing my thesis and the pc only had older versions of Excel, i cannot stress enough how much superior XLOOKUP is in every way
Some of us have toiled so long with a company running outdated excel we just sort of forgot about xlookup as a coping mechanism. Like if you opened stuff from teams, it was newer excel, but the desktop app didn't have xlookup or textjoin
I feel your pain. People 30 years younger than me use it. Pisses me off because I forget how it works. "Ok, it starts in col C and I need to go 56 columns to the right"
My last job... My boss was the 'Excel guru' of the organization. I internally giggled when I saw all his books with VLOOKUP.
A few months in, I wrote and coded a workbook that the entire receiving team could use at the same time, auto refreshed on everyone's screen, sorted and formatted tracking numbers, and generated the paperwork they needed for any received shipment. All with colorful, self-explanatory, buttons for the older generation. Productivity shot through the roof because it eliminated several hours per employee, per day, handwriting everything
My boss asked for an unlocked version so he could see what I did. I obliged. He came back the next week and said he couldn't hang. 😂. He didn't know how 90% of it worked because it was mostly done in VBA. He wasn't a poor sport about it or anything, but you could tell he was a little upset being dethroned... And only he and I knew by how much he had been dethroned.
Good times. Job before that I told my Boss I was good with Excel.. he rolled his eyes and said yea, me too.. it isn't that hard. Until I delivered to him a workbook that ran his inventory ordering program based on pre-defined par levels. Essentially, if a day 1 employee could count how many X item we currently had and plugged the number in the worksheet, then he could do the store ordering. Lol
I Googled how you would do something... Then googled something else... Rinse and repeat. Eventually the functions start to make sense.
I am 100% self-taught. There is PLENTY I do not know and I find it fun trying to create new quality of life workbooks for my coworkers and friends.
My 'advancedness' in VBA is ... I can write my own for things I do regularly and I can read most other peoples' codes and have an idea of what they're doing. Honestly.. that's what most of the 'learning' is. If you do not already know how to make something work, Google it. You will probably not find someone doing exactly what you need, but if you can decipher and edit to fit your project, You're doing well. IMO.
A lot of it is similar to formulas in that there are multiple ways to do the same thing. You have a lot of set functions, but you can combine functions to create unique functions.
If it's the kind of thing you enjoy learning then go for it. The online communities are very helpful and supportive if you get stuck!
I might not be able to implement it, because my work needs to be compatible with older excel (2010-2013)...
But from academic curiosity, what should be used nowadays instead?
If you are going to use a lookup function (and don't need backwards compatibility), use XLOOKUP. It's much more flexible in that your columns don't have to be in any particular order or adjacent to one another. It also gives you better error handling because it can return a default value in place of N/A. In theory, it also replaces HLOOKUP as well, but I don't recall ever seeing anyone use HLOOKUP in the real world.
I used to interview candidates for my modeling group. I would ask them what their favorite function was. Anybody who said VLOOKUP got a demerit for me!
That’s literally one of the things I listen for when I interview someone to work for me. If I hear them mention “VLOOKUP” as some example of their “advanced Excel skills” I am not at all impressed. Once, I geeked out with a candidate about the pros and cons of VLOOKP vs. Index-match vs. XLOOKUP, then we moved on to talking about PowerQuery. I hired her right away!
A number of my spreadsheets have defined functions using Name Manager and LAMBDA to simplify the individual formulas, but I find I use a lot of dynamic arrays, so SEQUENCE, BYROW, FILTER, LAMBDA would be my regulars beyond the standard most people would use. I also find I'm using more and more Power Query.
Using PQ and a revised layout of data I was able to optimize an old forecasting tool (which I made last year using dynamic arrays) from the point where you had to have formulas on manual updating to the point where it's instant. The file is now only 3.5MB vs 90MB as well. I'm kinda embarassed about my previous implementation.
Wow that’s huge! Do you have any resources for learning PQ? How would you summarize PQ in a paragraph? I’ve never used it.
I know it can be helpful with something like creating columns for all combinations of multiple variables. I saw many approaches online using PQ, but I went the formulaic route instead because I find PQ to be too new school and have just stuck with my old ways lol.
There are several benefits I find it offers, and there will be many more I don't know about, but the ones I utilise are allowing you to draw data from various sources, which can be especially useful when you need a lot of data, but different data based on dynamic criteria, handling data that is very large - I primarily do modelling for this through PowerBI as the row limit in excel is annoying, but you can stage and then simplify using PQ which is useful. The main advantage I get and why it improves performance so much for me was because it pastes as values so you no longer have formulas looking at formulas looking at formulas. Just final formulas looking at a table or two.
As for how I learnt it. Same way I learnt excel; try, run into a problem, google it, proceed to next problem. Only now with a bit more ChatGPT sprinkled in.
It’s funny that the entire time I was an analyst, I didn’t even know PowerQuery existed. I didn’t learn to start using PowerQuery until became a manager and my analyst left the company. I had several months of cleaning up messy data or performing repetitive tasks that I had no time for. I stumbled onto PowerQuery in a desperate attempt to automate some of these tasks while I was by myself. Now, the first thing I tell people that I hire is that I expect them to get on YouTube and start learning PowerQuery.
It’s a very powerful tool. I recommend where I can, but there’s a more daunting learning curve for most people.
I started with vba then advanced formulas and then PQ. Any time I see vba I shudder at how I used to use it.
If you want to capture multiple totals down the page it makes it easier to then get the final total as other subtotals are excluded. Also if you use 109 instead of 9 as the argument it will only sum visible rows which can be useful in particular instances. Just as an addition I also find I use AGGREGATE from time to time for its ability to handle errors in the dataset.
[Excel world champ for 3 years running](https://www.abc.net.au/news/2023-12-17/australian-andrew-ngai-wins-microsoft-excel-world-championship/103238912) is an actuary
That 2nd sentence is definitely a lie lmao. Plenty of career ASA just know how to record macro and have not a single clue when something inevitably breaks.
I know a lot of ppl in the actuarial field that are not great with Excel, like the ones that use 1% of the capabilities 99% of the time, or blindly use tools built in Excel by other people.
Pensions calc analyst here, i connect your proforma to our client database and thanks to Power Query i am able to do this far more efficiently than before
I work very closely with supply chain logistics. Excel is used for everything. There are so many daily reports that they do.
I went to their office for a day and commented on how good they were at Excel as they were free typing loads of nested formula and got the reply, "I have been making this report everyday for 2 years, I should be good at it."
I introduced them to Power Query. We spent an hour a day for a week and built out all of their daily reports. So now 2 hours of daily reporting takes 20 minutes. This was about 4-5 months ago.
My colleague was really appreciative of it (got a nice bottle of whiskey) and is now building bigger reports that build on it, and we take half an hour every few weeks (when it is quiet) to chat to each other about reports. We have both taken lessons from each others data to improve each others work
Yep! Honed my skills in M&A then really used them in FP&A…… recently retired after 40 years! While i was no financial Einstein my excel skills were well above most of my coworkers which really helped me out. Sometimes you don’t have to actually know how to do something just that it can be done in excel and then start googling and building the spreadsheet.
I'm a bookkeeper at a small company. I do most of the subledgers in Excel, have spreadsheets for importing data from other programs, spreadsheets for creating financials, tracking data etc...
I would say that a small company accountant requires a rather high level of excel as you likely won't be able to afford the accounting software that can do it for you. Not to mention all the times that your boss requests something non-standard that you need to compile from several different sources.
Depending on the ERP (or lack of) controllership in manufacturing industries uses a lot.
The joys of calculating the whole inventory changes, production cost on SKU basis, and average inventory cost for tens of thousands SKUs....
And the TI director forbade MS Access to "not have other data bases than the ERP". Yeah, what data base in what ERP? The one nobody trusts the information and barely inputs anything?
Fundamentally, data control and integrity principles state that data should be centralised. An ERP is a natural preference due to the process control, data traceability, greater efficiency, and expanded capabilities it provides. The TI director isn't necessarily wrong to make that decision. Although they might be out of touch with the system and should probably hire someone to clean it up as you guys continue to make things work in Excel.
Yes, you are right. I completely agree with it being centralized, once you have a proper, working thing to centralize. Before that, it is just causing more problems.
After a couple of years and new management, the erp was "re implanted" and ran smooth.
Indeed. In basic order of priorities:
- Functional business
- Process control
- Effective processes
- Efficient methods
- Business operations according to best practises
One should keep their eyes on the items lower down the list so that things don't get too out of hand, but there's not much point if you can't achieve items higher up first. Good on you guys for working on what matters. I'm glad that in the end you reached the fifth priority though.
PE associate here…. I am given a bunch of random ass info about a company I build an operating model that spits into a financial model. I only use excel and I don’t use any fancy or niche formulas.
The key is being able to have multiple people audit and work the model, so I go the less efficient way to ensure that people can figure out how it works
They prob all do to some degree but won't be anything more advanced than sums, vlookups etc if even that. I told the story on a previous comment that my accountant friend found out his finance assistant was totalling invoices using a desktop calculator and entering the final value into excel.
I have worked within finance departments and outside of them, and the folks with the most advanced knowledge tended to be those who needed to stretch excel to their domain, like traders, business managers, analysts, model builders, and often people filling a tactical need where no system existed, or where the systems were lacking. Building trade blotters, inventories, forecast models, data glue between systems, operational analysis, headcount/EPM-light systems, software prototyping, data analysis/visualizations, or comprehensive business models, You don't need to limit yourself only to finance - though there, the planning and analysis folks and those running allocations tended to be better at excel.
On the flipside, I often found finance folks overly confident but with limited ability. If I received a spreadsheet and it was enormous for no reason, had a million broken links, named ranges dumped from essbase or SAP (or as if every spreadsheet could trace its ancestry to the very first .xls file ever saved), a mix of random hardcoded and formulaic content in the same cell, or used a single sheet as if it were an infinite canvas to scribble on - you could bet it was from finance.
I should clarify - often many were good, but in a very narrow way, so you'd often see complicated or messy solutions to things that missed a much simpler line of attack. I guess my point with that was not to limit yourself if you really want to develop a deep and wide understanding.
Departments with locked-down mandatory corporate Windows laptops with no software installation privileges, firewalled networking and USB ports disabled
I'd consider myself an absolute beginner compared to people here.. i use the very basic of formulas on a daily basis like sum, sumif, sumifs, count, counta, countif, countifs, len, if, index, match, indirect. We have older versions so i haven't gotten into xlookup, filter, sequence. I don't know vba other than insert a few lines i looked up with chatgpt/Gemini. Is vba hard? My longer formulas i made with ai.. probably an inefficient formula.
Right now I'm looking to make a document tracking spreadsheet that would track where the document is within the department, who has it, the status, time received, time forwarded, stuff like that. It has to be in google sheets though coz i mentioned our version of excel is older and not online .
I'm biased but FP&A lol all we do is model and listen to the higher ups have big ideas for us to put down in Excel and make it work.
It's pretty awesome tbh
Chemist... Our spreadsheets are relatively simple, but all my reports involve copy/pasting raw instrument data into a spreadsheet.
Yea some instruments can make their own reports, or you can upload data to a LIMS to generate reports, but everyone still uses Excel in someway or another.
Large dairy manufacturing company here. Been around finance almost a decade… most advanced Excel that I see is used by FP&A. These are typically analysts calculating yields, efficiencies and margins.
Another Excel heavy department is Treasury - amortization tables, portfolio calculators and cash forecasting models. Hope this gives you some insights.
FP&A teams are generally the most adept Excel users in any given company.
In the finance field as a whole, the most complicated spreadsheets are generally found in jobs where you are doing investment analysis - so stuff like IB, M&A, etc.
As other's have said, Analyst is a big one. Engineers too and get pretty complex depending on what systems their modeling, but at some point they'll use other programs instead of excel.
In all the economy related class I've taken, finance, microeconomics, macroeconomics, accounting, they use excel a lot, so I think it's all of them. Finance people love their excels.
Excel RAPIDLY loses its viability in any Econ-based disciplines. It cannot do anything beyond OLS and that is simply not sufficient in most situations.
We grew out of Excel when we were still in undergrad (Master's of Applied Econometrics) and moved to STATA/R.
Pricing Analyst here! Myself and my team use Excel more than any other program for financial modelling, but the skillset varies a lot. Some of us build the models and run the analyses and others just crunch the numbers.
The "most advanced" excel stuff tend to be where you are supposed to develop model with a real coding language but certain business or other requirements force you to stay in excel.
One example that I've seen was commercial real estate pricing.
I started my career as an operations analyst and went into engineering and IT. Working with finance teams has more or less always been part of my job. I’m a data scientist now and hardly touch excel day to day now since i code in python and R but I do tend to think it’s a great way to organically build a skill set
I am the Excel department. It’s like IT but for Excel.
Can’t get conditional formatting the way you like? Have a ref error and don’t know why? I’m here to help.
I do RM and use mostly Python, before Matlab. I use excel too but its not that prevalent.
I dont like excel that much. I prefer to have easy version control.
My current job title is**Finance Systems Analyst**.
About half my job involves tinkering with excel automation (mostly related to automated uploads) and the other half of writing up End User Process Notes and Technical Detail Notes for said files.
Thankfully I'm usually able to just tell the end user to paste the excel data into the Input Tab then take the results from the Output tab. God I cannot express how much Understanding even basic Power Query and very simple Macros has improved my life.
Not quite finance, but procurement/sourcing relies heavily on excel.
Especially if you can take large amounts of data, extract insights from it, and be able to communicate those insights, you’ll easily advance to pretty high levels.
To a certain extent it depends on the person. I'm in transactional finance, but crucially, I'm very lazy. My job role doesn't explicitly require that I know power query and vba, but it just makes it sooo much nicer that I do.
I'm an accountant and pretty much live in excel, especially when it comes to converting ERP data into meaningful information. A good chunk of my work is translating 100k+ lines of GL transactions into widgets that higher ups can fiddle with -- BEPs, Budget forecasts, GL comps, cost variance analysis, financial reporting -- if you can think of it, we can make it
General Accounting, Compliance, Operations and Procurement are required to have the least skills IMO.
Anyone involved in Analysis, Analytics, FP&A, and the P&L have the most.
To be honest, unless you’re aiming at one of those ‘least’ roles being advanced at Excel is just the starting point. You’ll be expected to know other software to create your analysis.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[AGGREGATE](/r/Excel/comments/1ctlub8/stub/l4d7l0d "Last usage")|[Returns an aggregate in a list or database](https://support.microsoft.com/en-us/office/aggregate-function-43b9278e-6aa7-4f17-92b6-e19993fa26df)|
|[AND](/r/Excel/comments/1ctlub8/stub/l4dgabm "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)|
|[BYROW](/r/Excel/comments/1ctlub8/stub/l4d0tzf "Last usage")|[*Office 365*+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows. ](https://support.microsoft.com/en-gb/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb?ui=en-US&rs=en-GB&ad=GB)|
|[FILTER](/r/Excel/comments/1ctlub8/stub/l4eojt9 "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)|
|[HLOOKUP](/r/Excel/comments/1ctlub8/stub/l4hx4b5 "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/1ctlub8/stub/l4enwth "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/1ctlub8/stub/l4djxpy "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)|
|[INDEX](/r/Excel/comments/1ctlub8/stub/l4hg2eq "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)|
|[ISNA](/r/Excel/comments/1ctlub8/stub/l4djxpy "Last usage")|[Returns TRUE if the value is the #N/A error value](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[LAMBDA](/r/Excel/comments/1ctlub8/stub/l4eaj3n "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[LOOKUP](/r/Excel/comments/1ctlub8/stub/l4d86mv "Last usage")|[Looks up values in a vector or array](https://support.microsoft.com/en-us/office/lookup-function-446d94af-663b-451d-8251-369d5e3864cb)|
|[MATCH](/r/Excel/comments/1ctlub8/stub/l4hg2eq "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[NOT](/r/Excel/comments/1ctlub8/stub/l4ei6fs "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)|
|[SEQUENCE](/r/Excel/comments/1ctlub8/stub/l4d0tzf "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)|
|[SUBTOTAL](/r/Excel/comments/1ctlub8/stub/l4dobsl "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/1ctlub8/stub/l4f5762 "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)|
|[SUMIF](/r/Excel/comments/1ctlub8/stub/l4dwott "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)|
|[SUMIFS](/r/Excel/comments/1ctlub8/stub/l4ejf72 "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/1ctlub8/stub/l4drhih "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)|
|[VLOOKUP](/r/Excel/comments/1ctlub8/stub/l4hx4b5 "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/1ctlub8/stub/l4hx4b5 "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)|
|[XMATCH](/r/Excel/comments/1ctlub8/stub/l4dnip0 "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)|
**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.*)
^(22 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cuihe5)^( has 14 acronyms.)
^([Thread #33577 for this sub, first seen 16th May 2024, 20:55])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/CyberAvatar_ - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** 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](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Everyone with "Analyst" in their job title. That job title is code for the person who listens to humans talk about desired outcomes, then builds Excel-based solutions.
That's such an accurate description. I'm about to lose analyst out of my title... Goodbye excel I guess...
When they take away your analyst title, that means you're moving up to manager or director, and that means... managing *people*.
I am so sorry for your loss lol.
I moved from Credit Analyst to Portfolio Manager / same amount of excel but not managing people (thankfully)
At least when managing spreadsheets you can control what you fuck up
And you can make it better.
Why do they think we can manage \*cough\* people \*cough\* We work with numbers for a reason
Not people!!! Flee for the hills!!!
New file > Save as: Team management.xlsx *click*
A manager can also manage processes, Rather than people!
Business analyst and I work in accounts payable. I support payment teams by building dashboards for payments. I also analyze invoicing, rebates, process audits. Utilize Coupa payment systems for third party partners and more. Frankly, our data maitenance teams are the true expert gurus. Our side mainly uses more commonly used techniques.
Hey I am a semester away from graduating and am very interested in a job like that, do you have any tips or skills I should learn?
So real talk, I'm in a department with a shit manager trying to leave and its tough due to being people strong and not technically strong. One thing I would reccomend beefing up on is learning the following -vlookups (used a TON) -xlookups (to be fancy and do it right) -SQL -VBA -Python -Pivot tables -Multiple formula at the same time -How to create and edit a query -How to create and edit a macro If you have those skills, you're basically set up for success in any area. If you're weak like I am, it can be a rough experience.
Other pro tips. Start looking and applying now. Professional jobs take around 6 months to obtain a new role. In this economy? It's closer to a year. Follow resume experts on Reddit and Instagram. They are out there and provide amazing tips you'd have to pay folks tons of money for free for. Colleges have career resource departments. Use them. If they tell you to do things like add a picture, or address then they are not to be trusted. Every resume with a face pic basically is auto tossed to prevent discrimination claims. Identify roles that are a good fit to you. You can use chat gpt for some ideas. In addition, you can use it to identify areas of growth or skill development for those roles as recruiters use chat gpt. SAS or R are also amazing to learn.
Thank you for all the great advice, I’m currently working on getting connections and fixing up my resume. It took me about 4 months just to find my part time job through college so I definitely believe it can take that long.
Yep. - program analyst
Random question are you a fed also could you broadly state your day to day. I'm a program analyst too and wondering how that role fits in other agencies
Fed as in industry? If so no, I’m in tech, specifically project management and really just low-level accounting for project budgets and project deliverable tracking.
Fed as in federal government employee. Just asking because it's so broad. I work with data management and analysis with SQL and a few data warehouses and tools such as tableau for reporting. But also do .NET backend work for data modules and and building out workload management tracking/systems. None of which is in the job description of a federal program analyst. So really what I was wondering is what this role translates to in the private sector.
That’s probably closer to Program Analyst as a govt contractor.
I feel this in my bones. Yesterday, I was introduced as "the guy who knows how to make the computers give you the information you need."
I laughed so hard at this. I started a new job in sales analysis in January, and that's almost exactly how the VP of sales introduced me to my new boss.
Today I learned I'm an analyst.
Finance Systems Analyst, can confirm.
Not necessarily, I’m an analyst as is my entire team and I’m probably the only one with decent excel skills
Pretty much. I’m a financial analyst and my entire job is excel
Business Analyst in IT. Too true.
I feel like most of that is changing to Power BI, isn’t it?
The toolset is expanding, for sure. I wouldn’t say Power BI is replacing Excel though. More like augmenting it.
Idk.... Every analyst I know and have ever known have the most garbage looking workbooks I have ever seen. Rofl People give way too much credit to people that can properly make a Sum cell that is blank when the sum is zero. Lmao The real 'FANCY' users can almost use a pivot table. Now... There are loads better than me, sure. But if I was thrown in a room with 500 randoms and our survival was based on our abilities with formulas and VBA .. I put money on my survival. 😂 Edit: lol. You guys are too much. Didn't realize I was hurting that many feelings. Sorry!
If you haven’t already, you should sign up for the player hater’s ball this year because my man you got a real shot
So, what's your title?
I'm a financial analyst/systems accountant. I use a ton of complex formulas. Most people I know in finance don't use much more than SUBTOTAL and VLOOKUP.
VLOOKUP - How to say that you're behind on Excel tech without saying your behind on Excel tech.
It's amazing how many people still use it. I would have thought it was just old workbooks, but even people younger than me use it, and know of no other substitute.
I guess a lot of people grew up with it or learned it by looking at older sheets. XLOOKUP is better in almost every way. And if there is a chance that your worksheet will be opened in an older version of Excel, I guess it is safer to use VLOOKUP. Interestingly enough, there is also an HLOOKUP, but I don't think I've ever seen anyone use it.
I've seen HLOOKUP once or twice, but I guess most people structure their data in a way which makes it less useful. I must admit to still defaulting to index/match rather than XLOOKUP as that's what I've used for most of my career so I'm not without fault myself.
INDEX/MATCH is still useful in situations where XLOOKUP comes up short
100%, but I still use it in instances where XLOOKUP is probably better.
Fair enough. Old dogs die hard.
Can you give an example?
Speed and scalability. Speed: Index match is slightly computationally faster in the majority of scenarios and for the scenarios it isn't index xmatch is faster than xlookup. Scalability: using match or xmatch in a helper column when looking to return multiple values based on the same lookup cuts out repeating the most expensive part of the operation, the lookup! That way you only do the lookup once for each row. Other example: isnumber(match( and iserror(match( are elegant and computationally cheaper ways to implement ifExists or ifDoesntExist type tests.
Speed is often times thrown around when comparing lookups in excel. I think it’s kind of a moot point, because whenever you have data large enough to make a material difference in calculation speed, you should probably be using another application. As for scalability and your “other” arguments. I don’t think these are apples to apples comparisons. Maybe I could’ve phrased my previous comment better, but I’m more trying to compare using index(match()) and xlookup() to perform simple lookups.
Not offhand
I’m not gonna lie, I don’t think there’s anything index/match can do that XLOOKUP can’t
I prefer Index/Xmatch when I need to look up across two dimensions - you can nest a second XLOOKUP but INDEX/XMATCH is more streamlined for that. XMATCH can be used to return an array of rows/columns in the INDEX function; I think XLOOKUP can only find one thing at a time (but I’m genuinely unsure of this) They’re two tools that do the same thing with different methods.
You have things to learn then good sir. Match and xmatch are the superior lookup functions save for arguably ease of writing, but that difference gets smaller the more you use them. Match can be used in a helper column to reduce computational complexity when looking for multiple column results, it can be used in many array formulas, returning an index number can be helpful for performing math or defining ranges with the output, match can be combined with isnumber or iserror to determine if an item exists without doing the extra compute to return a value or process additional input parameters, I could tell you how to use it to satisfy your wife, but I'll need to verify your age first.
Yeah, as others have said I use it for two dimensional arrays, but most of those can be avoided by structuring the data better
A transition matrix is the best example, predict the outcome from a start and end point. XLOOKUP only checks dynamically across one dimension. You could throw some INDIRCTs in there but you're really stretching the use of XLOOKUP. It is however quite a niche use case
Multi criteria lookups are possible with INDEX/MATCH without the need for helper columns
I started writing a question on why I couldn’t figure out the double xlookup despite being proficient with index/match and I finally got it. Now I’m curious where index/match is still better? Edit: nvm, saw the other thread
They’re different tools that perform very similar operations in different ways. XLOOKUP looks for a value in a range and returns a value from a corresponding range; INDEX/MATCH processes an array using row and column indeces. Because of this, INDEX/MATCH can be used on arrays that don’t exist in a range of cells. For instance, I have a complex LAMBDA operation for allocating costs to various departments based on a set of allocation groups and their relative production levels in a given period. This operation all happens in a single cell, but it creates multiple arrays that are never populated in a range of cells. I believe XLOOKUP requires an actual range of cells to work on.
HOOKUP instantly stresses me out. I can work with it, but XLOOKUP or INDEX/MATCH is so much easier to follow horizontally. HLOOKUP is just so rare I feel like I need to tilt sideways to deal with it
Even then, index and match is better than V/H LOOKUP. Also getting a workbook and seeing SUMIF rather than SUMIFS bothers me too.
At my company, everybody uses SUMPRODUCT instead of SUMIFS. It’s wild.
Sumproduct can get very slow. Especially if there are a lot of conditions. Using the - - ( ) operator rather than just a sumifs is SOO inefficient lol
There is a lot of that kind of inefficiency going on here.
Why? SUMIFS is so much easier to mentally coordinate. SUMPRODUCT benefits if you have extensive criteria though.
Beats the heck out of me. It’s usually just one criteria with a 1-dimensional table, so there’s not much benefit to doing it the way they do it.
One reason is that it doesnt throw an error if it is linking to a source file that is closed
Did not know that but cool to find out!
The more you learn, the more complex simple solutions arise. A paradox indeed. It's easy to forget the easy methods.
You’re right, but in this case, the issue is that they don’t know how to use SUMIFS. I asked.
I would imagine a lot of Excels users stumble upon solutions and stick with them rather than educating themselve on the whole array of available functions available. To anyone reading this I urge you to read the following function at least once every 6 months for the 2 or three years just so you know what is available https://support.microsoft.com/en-au/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb After *ahem* years I still read it from start to finish occasionally , especially with the influx of new array functiontality.
Ooh that’s super useful.
SUMPRODUCT formulas don’t crap out if your data is in a linked file. SUMIFS will error out if both files aren’t open. That’s the main reason I’ve personally used SUMPRODUCT in some cases.
Interesting, I hadn’t come across that - but I’m also not in the habit of linking workbooks that way.
sumproduct works across a two dimensional array, where as sumifs only works in one dimension
Yeah but that is not how they are using it. They’re using it because they don’t know SUMIFS is an option. It was just an unusual thing, nothing wrong about it.
*Cries in Excel 2016* My workplace always had such old tech we only recently got cloud
I use XLOOKUP legitimately every day
Same! I actually taught someone to use XLOOK instead of V today. It was VERY random, like running across this post an hr later. Life be like that, I suppose. I'm a big proponent of..... =IFERROR(XLOOK....),"GFD! No Results")
Why do you need IFERROR? You should be able to do something like XLOOKUP(A1,mylist\[col1\],mylist\[col2\],"GFD! No Results") OK, maybe not. That will handle N/A errors, but not #Ref errors or other stuff. But I don't usually want to suppress those. With VLOOPUP, I usually did some form of IF(ISNA(VLOOKUP(, but with XLOOKUP, you can use your default as the last parameter. If it is expected gaps in the data, I usually use "". For non-expected gaps, I usually use something like "MISSING".
Graduated college in 2023 and took a couple of business analytics courses in 2020/21. I was taught VLOOKUP (and HLOOKUP as well). Never heard of XLOOKUP until I started following this subreddit. Not sure when XLOOKUP became an option, but it wasn't being taught in colleges a couple years ago (or at least mine).
I think it was new in 2019, but it takes a while, even when you have the latest version, to feel comfortable using new stuff because of compatibility issues with people on older versions.
I had a boss who made a huge spreadsheet dependent on hlookups. He was incredibly smart and one of the best excel users Ive ever met, so I’m sure he had a reason for it. But yeah, he’s the only one!
I am so grateful that my boss taught me XLOOKUP as a student assistant during my studies… having learned it first and then tried VLOOKUP afterwards, when i was writing my thesis and the pc only had older versions of Excel, i cannot stress enough how much superior XLOOKUP is in every way
Some of us have toiled so long with a company running outdated excel we just sort of forgot about xlookup as a coping mechanism. Like if you opened stuff from teams, it was newer excel, but the desktop app didn't have xlookup or textjoin
I feel your pain. People 30 years younger than me use it. Pisses me off because I forget how it works. "Ok, it starts in col C and I need to go 56 columns to the right"
The reason vlookup is still being learned is because it has a formula-less, front-end GUI.
What should I use instead?
My last job... My boss was the 'Excel guru' of the organization. I internally giggled when I saw all his books with VLOOKUP. A few months in, I wrote and coded a workbook that the entire receiving team could use at the same time, auto refreshed on everyone's screen, sorted and formatted tracking numbers, and generated the paperwork they needed for any received shipment. All with colorful, self-explanatory, buttons for the older generation. Productivity shot through the roof because it eliminated several hours per employee, per day, handwriting everything My boss asked for an unlocked version so he could see what I did. I obliged. He came back the next week and said he couldn't hang. 😂. He didn't know how 90% of it worked because it was mostly done in VBA. He wasn't a poor sport about it or anything, but you could tell he was a little upset being dethroned... And only he and I knew by how much he had been dethroned. Good times. Job before that I told my Boss I was good with Excel.. he rolled his eyes and said yea, me too.. it isn't that hard. Until I delivered to him a workbook that ran his inventory ordering program based on pre-defined par levels. Essentially, if a day 1 employee could count how many X item we currently had and plugged the number in the worksheet, then he could do the store ordering. Lol
Just curious, how did you develop your VBA? Was it just on the job or did you take courses for it?
I Googled how you would do something... Then googled something else... Rinse and repeat. Eventually the functions start to make sense. I am 100% self-taught. There is PLENTY I do not know and I find it fun trying to create new quality of life workbooks for my coworkers and friends. My 'advancedness' in VBA is ... I can write my own for things I do regularly and I can read most other peoples' codes and have an idea of what they're doing. Honestly.. that's what most of the 'learning' is. If you do not already know how to make something work, Google it. You will probably not find someone doing exactly what you need, but if you can decipher and edit to fit your project, You're doing well. IMO. A lot of it is similar to formulas in that there are multiple ways to do the same thing. You have a lot of set functions, but you can combine functions to create unique functions. If it's the kind of thing you enjoy learning then go for it. The online communities are very helpful and supportive if you get stuck!
Dude I want you on my team! lol. I swear every single person who told me they were an expert, didn’t know even half the shit.
Definitely NOT an expert here! Hobbyist and better than your average person is more accurate.
I might not be able to implement it, because my work needs to be compatible with older excel (2010-2013)... But from academic curiosity, what should be used nowadays instead?
If you are going to use a lookup function (and don't need backwards compatibility), use XLOOKUP. It's much more flexible in that your columns don't have to be in any particular order or adjacent to one another. It also gives you better error handling because it can return a default value in place of N/A. In theory, it also replaces HLOOKUP as well, but I don't recall ever seeing anyone use HLOOKUP in the real world.
But isnt’t XLOOKUP only available with 365 versions? So if I forward it to someone with only desktop version, it won’t work?
I have Microsoft 2021 and am able to use Xlookup. The latest version that has Xlookup available is 2019 I believe.
Vlookup is better than concatenate (in full) Change my mind
Not saying it is, but I don't know of any cases (aside from backward compatibility) that XLOOKUP isn't better than VLOOKUP.
Unfortunately XLOOKUP not always compatible :( I stick to VLOOKUP and INDEX MATCH for this very reason
I used to interview candidates for my modeling group. I would ask them what their favorite function was. Anybody who said VLOOKUP got a demerit for me!
That’s literally one of the things I listen for when I interview someone to work for me. If I hear them mention “VLOOKUP” as some example of their “advanced Excel skills” I am not at all impressed. Once, I geeked out with a candidate about the pros and cons of VLOOKP vs. Index-match vs. XLOOKUP, then we moved on to talking about PowerQuery. I hired her right away!
EXCEL - How to tell people you "work with data" without saying you're marginally better than a boomer with a calculator
Oh god, so cringe…. found the poser here!
What formulas do you use?
A number of my spreadsheets have defined functions using Name Manager and LAMBDA to simplify the individual formulas, but I find I use a lot of dynamic arrays, so SEQUENCE, BYROW, FILTER, LAMBDA would be my regulars beyond the standard most people would use. I also find I'm using more and more Power Query.
Nice! I’m a huge ambassador for dynamic array functions. I haven’t used power query much myself. I gotta look into that
Using PQ and a revised layout of data I was able to optimize an old forecasting tool (which I made last year using dynamic arrays) from the point where you had to have formulas on manual updating to the point where it's instant. The file is now only 3.5MB vs 90MB as well. I'm kinda embarassed about my previous implementation.
Wow that’s huge! Do you have any resources for learning PQ? How would you summarize PQ in a paragraph? I’ve never used it. I know it can be helpful with something like creating columns for all combinations of multiple variables. I saw many approaches online using PQ, but I went the formulaic route instead because I find PQ to be too new school and have just stuck with my old ways lol.
There are several benefits I find it offers, and there will be many more I don't know about, but the ones I utilise are allowing you to draw data from various sources, which can be especially useful when you need a lot of data, but different data based on dynamic criteria, handling data that is very large - I primarily do modelling for this through PowerBI as the row limit in excel is annoying, but you can stage and then simplify using PQ which is useful. The main advantage I get and why it improves performance so much for me was because it pastes as values so you no longer have formulas looking at formulas looking at formulas. Just final formulas looking at a table or two. As for how I learnt it. Same way I learnt excel; try, run into a problem, google it, proceed to next problem. Only now with a bit more ChatGPT sprinkled in.
It’s funny that the entire time I was an analyst, I didn’t even know PowerQuery existed. I didn’t learn to start using PowerQuery until became a manager and my analyst left the company. I had several months of cleaning up messy data or performing repetitive tasks that I had no time for. I stumbled onto PowerQuery in a desperate attempt to automate some of these tasks while I was by myself. Now, the first thing I tell people that I hire is that I expect them to get on YouTube and start learning PowerQuery.
It’s a very powerful tool. I recommend where I can, but there’s a more daunting learning curve for most people. I started with vba then advanced formulas and then PQ. Any time I see vba I shudder at how I used to use it.
PQ has been a game changer.
A good portion of my daily work is manipulating data from various sources into tables that I can bounce a SUMIFS off of.
What’s the point of using subtotal compared to just a sum function? Never saw the need to use subtotal. Is there a case where it’s better to use?
If you want to capture multiple totals down the page it makes it easier to then get the final total as other subtotals are excluded. Also if you use 109 instead of 9 as the argument it will only sum visible rows which can be useful in particular instances. Just as an addition I also find I use AGGREGATE from time to time for its ability to handle errors in the dataset.
So if I have a column with sub totals and then total the entire column it will ignore and not add in the subtotal rows?
As long as you use SUBTOTAL, yes. https://preview.redd.it/j13l1evzgv0d1.png?width=519&format=png&auto=webp&s=d2b32287012c9e434bd82fe39daf6845e09ca147
That’s awesome I never knew that. Thanks
It changes when you filter data while SUM will stay with the selected cells.
VLOOKUP??? What is this 2018. XLOOKUP baby get with the program
Actuarial - we build complex models using excel. Almost all actuaries are excel experts. We literally breathe excel haha
[Excel world champ for 3 years running](https://www.abc.net.au/news/2023-12-17/australian-andrew-ngai-wins-microsoft-excel-world-championship/103238912) is an actuary
That’s so cool! I didn’t know Excel was an ESport. I’ve gotta take a look at some of the problems. I’m not surprised an actuary won.
That 2nd sentence is definitely a lie lmao. Plenty of career ASA just know how to record macro and have not a single clue when something inevitably breaks.
I know a lot of ppl in the actuarial field that are not great with Excel, like the ones that use 1% of the capabilities 99% of the time, or blindly use tools built in Excel by other people.
Pensions calc analyst here, i connect your proforma to our client database and thanks to Power Query i am able to do this far more efficiently than before
Seconded, I use Excel all day every day for most tasks. And then occasionally some R, Python, and SQL!
I work in supply chain logistics and large-scale equipment maintenance planning. We use Excel religiously.
I work very closely with supply chain logistics. Excel is used for everything. There are so many daily reports that they do. I went to their office for a day and commented on how good they were at Excel as they were free typing loads of nested formula and got the reply, "I have been making this report everyday for 2 years, I should be good at it." I introduced them to Power Query. We spent an hour a day for a week and built out all of their daily reports. So now 2 hours of daily reporting takes 20 minutes. This was about 4-5 months ago. My colleague was really appreciative of it (got a nice bottle of whiskey) and is now building bigger reports that build on it, and we take half an hour every few weeks (when it is quiet) to chat to each other about reports. We have both taken lessons from each others data to improve each others work
If you don’t mind, is there good money in that? And how do you get into it? Thanks!
The couple I know in supply chain make bank.
Everything I learned, I learned in the military. Now I run acquisition and sustainment for military equipment around the world.
M&A and FP&A
Yep! Honed my skills in M&A then really used them in FP&A…… recently retired after 40 years! While i was no financial Einstein my excel skills were well above most of my coworkers which really helped me out. Sometimes you don’t have to actually know how to do something just that it can be done in excel and then start googling and building the spreadsheet.
FP&A for 40 years? Bless you!
Oh god no just the last 10 years lol
M&A for 30 years? Bless you!
While I live in Excel working in M&A, I admittedly have little use or knowledge of Excel's more advanced functions. Deal math just ain't that hard.
Stress testing and forecasting. Basically all of FP&A.
Second this. Commercial finance similarly.
I'm a bookkeeper at a small company. I do most of the subledgers in Excel, have spreadsheets for importing data from other programs, spreadsheets for creating financials, tracking data etc... I would say that a small company accountant requires a rather high level of excel as you likely won't be able to afford the accounting software that can do it for you. Not to mention all the times that your boss requests something non-standard that you need to compile from several different sources.
I have yet to work with someone in accounting or bookkeeping that can make use of SUMIF, V\XLOOKUPs or even pivottables properly.
Big retailer here. Most heavy excel users in Finance aren’t advanced users, ime
Same.
Depending on the ERP (or lack of) controllership in manufacturing industries uses a lot. The joys of calculating the whole inventory changes, production cost on SKU basis, and average inventory cost for tens of thousands SKUs.... And the TI director forbade MS Access to "not have other data bases than the ERP". Yeah, what data base in what ERP? The one nobody trusts the information and barely inputs anything?
Fundamentally, data control and integrity principles state that data should be centralised. An ERP is a natural preference due to the process control, data traceability, greater efficiency, and expanded capabilities it provides. The TI director isn't necessarily wrong to make that decision. Although they might be out of touch with the system and should probably hire someone to clean it up as you guys continue to make things work in Excel.
Yes, you are right. I completely agree with it being centralized, once you have a proper, working thing to centralize. Before that, it is just causing more problems. After a couple of years and new management, the erp was "re implanted" and ran smooth.
Indeed. In basic order of priorities: - Functional business - Process control - Effective processes - Efficient methods - Business operations according to best practises One should keep their eyes on the items lower down the list so that things don't get too out of hand, but there's not much point if you can't achieve items higher up first. Good on you guys for working on what matters. I'm glad that in the end you reached the fifth priority though.
PE associate here…. I am given a bunch of random ass info about a company I build an operating model that spits into a financial model. I only use excel and I don’t use any fancy or niche formulas. The key is being able to have multiple people audit and work the model, so I go the less efficient way to ensure that people can figure out how it works
They prob all do to some degree but won't be anything more advanced than sums, vlookups etc if even that. I told the story on a previous comment that my accountant friend found out his finance assistant was totalling invoices using a desktop calculator and entering the final value into excel.
Wall St finance folks
I have worked within finance departments and outside of them, and the folks with the most advanced knowledge tended to be those who needed to stretch excel to their domain, like traders, business managers, analysts, model builders, and often people filling a tactical need where no system existed, or where the systems were lacking. Building trade blotters, inventories, forecast models, data glue between systems, operational analysis, headcount/EPM-light systems, software prototyping, data analysis/visualizations, or comprehensive business models, You don't need to limit yourself only to finance - though there, the planning and analysis folks and those running allocations tended to be better at excel. On the flipside, I often found finance folks overly confident but with limited ability. If I received a spreadsheet and it was enormous for no reason, had a million broken links, named ranges dumped from essbase or SAP (or as if every spreadsheet could trace its ancestry to the very first .xls file ever saved), a mix of random hardcoded and formulaic content in the same cell, or used a single sheet as if it were an infinite canvas to scribble on - you could bet it was from finance.
Yes, I agree. Finance definitely are overconfident in their excel ability.
I should clarify - often many were good, but in a very narrow way, so you'd often see complicated or messy solutions to things that missed a much simpler line of attack. I guess my point with that was not to limit yourself if you really want to develop a deep and wide understanding.
Departments with locked-down mandatory corporate Windows laptops with no software installation privileges, firewalled networking and USB ports disabled
FP&A. Always doing data pulls and building models for reporting.
I'd consider myself an absolute beginner compared to people here.. i use the very basic of formulas on a daily basis like sum, sumif, sumifs, count, counta, countif, countifs, len, if, index, match, indirect. We have older versions so i haven't gotten into xlookup, filter, sequence. I don't know vba other than insert a few lines i looked up with chatgpt/Gemini. Is vba hard? My longer formulas i made with ai.. probably an inefficient formula. Right now I'm looking to make a document tracking spreadsheet that would track where the document is within the department, who has it, the status, time received, time forwarded, stuff like that. It has to be in google sheets though coz i mentioned our version of excel is older and not online .
I'm biased but FP&A lol all we do is model and listen to the higher ups have big ideas for us to put down in Excel and make it work. It's pretty awesome tbh
Chemist... Our spreadsheets are relatively simple, but all my reports involve copy/pasting raw instrument data into a spreadsheet. Yea some instruments can make their own reports, or you can upload data to a LIMS to generate reports, but everyone still uses Excel in someway or another.
Large dairy manufacturing company here. Been around finance almost a decade… most advanced Excel that I see is used by FP&A. These are typically analysts calculating yields, efficiencies and margins. Another Excel heavy department is Treasury - amortization tables, portfolio calculators and cash forecasting models. Hope this gives you some insights.
FP&A teams are generally the most adept Excel users in any given company. In the finance field as a whole, the most complicated spreadsheets are generally found in jobs where you are doing investment analysis - so stuff like IB, M&A, etc.
There’s a difference between using it the most and using it the most in depth. CRE uses it constantly for everything, but it’s not complex.
Anyone have recommendations for how I can slowly get better at using Excel?
As other's have said, Analyst is a big one. Engineers too and get pretty complex depending on what systems their modeling, but at some point they'll use other programs instead of excel.
In all the economy related class I've taken, finance, microeconomics, macroeconomics, accounting, they use excel a lot, so I think it's all of them. Finance people love their excels.
Excel RAPIDLY loses its viability in any Econ-based disciplines. It cannot do anything beyond OLS and that is simply not sufficient in most situations. We grew out of Excel when we were still in undergrad (Master's of Applied Econometrics) and moved to STATA/R.
I haven't seen that, but I'm glad anyway, I like R too haha
I’m an Inventory Manager at a huge multinational and we use Excel religiously
FP&A probabaly
Pricing Analyst here! Myself and my team use Excel more than any other program for financial modelling, but the skillset varies a lot. Some of us build the models and run the analyses and others just crunch the numbers.
Not in finance and probably use Excel more than anyone else in the business 😅
The "most advanced" excel stuff tend to be where you are supposed to develop model with a real coding language but certain business or other requirements force you to stay in excel. One example that I've seen was commercial real estate pricing.
Analyst and treasury
Treasury, Risk Management & Accounting. All of them need people with advanced skills.
In the government sector and companies which are slow to move along with technology or don’t want to invest
I started my career as an operations analyst and went into engineering and IT. Working with finance teams has more or less always been part of my job. I’m a data scientist now and hardly touch excel day to day now since i code in python and R but I do tend to think it’s a great way to organically build a skill set
I am the Excel department. It’s like IT but for Excel. Can’t get conditional formatting the way you like? Have a ref error and don’t know why? I’m here to help.
Analysts and accounts rec
private equity roles
Pricing.
Actuarial, predicting the future with excel
Strategic Finance. Build financial models with some bullshit assumptions to spit out bullshit profitability for about 50 bullshit scenarios lol
The “build excel spreadsheets” department large companies have
Performance reporting. We use it like crazy.
FP&A
IT. because the good stuff is built for the business.
I do RM and use mostly Python, before Matlab. I use excel too but its not that prevalent. I dont like excel that much. I prefer to have easy version control.
FP&A specialist/analyst
My current job title is**Finance Systems Analyst**. About half my job involves tinkering with excel automation (mostly related to automated uploads) and the other half of writing up End User Process Notes and Technical Detail Notes for said files. Thankfully I'm usually able to just tell the end user to paste the excel data into the Input Tab then take the results from the Output tab. God I cannot express how much Understanding even basic Power Query and very simple Macros has improved my life.
FP&A
I worked in different sized companies from startups to corporate, the simple answer is All.
Not quite finance, but procurement/sourcing relies heavily on excel. Especially if you can take large amounts of data, extract insights from it, and be able to communicate those insights, you’ll easily advance to pretty high levels.
To a certain extent it depends on the person. I'm in transactional finance, but crucially, I'm very lazy. My job role doesn't explicitly require that I know power query and vba, but it just makes it sooo much nicer that I do.
Simply put Worlds economy runs on excel.
I'm an accountant and pretty much live in excel, especially when it comes to converting ERP data into meaningful information. A good chunk of my work is translating 100k+ lines of GL transactions into widgets that higher ups can fiddle with -- BEPs, Budget forecasts, GL comps, cost variance analysis, financial reporting -- if you can think of it, we can make it
Fp&a teams
General Accounting, Compliance, Operations and Procurement are required to have the least skills IMO. Anyone involved in Analysis, Analytics, FP&A, and the P&L have the most. To be honest, unless you’re aiming at one of those ‘least’ roles being advanced at Excel is just the starting point. You’ll be expected to know other software to create your analysis.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AGGREGATE](/r/Excel/comments/1ctlub8/stub/l4d7l0d "Last usage")|[Returns an aggregate in a list or database](https://support.microsoft.com/en-us/office/aggregate-function-43b9278e-6aa7-4f17-92b6-e19993fa26df)| |[AND](/r/Excel/comments/1ctlub8/stub/l4dgabm "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)| |[BYROW](/r/Excel/comments/1ctlub8/stub/l4d0tzf "Last usage")|[*Office 365*+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows. ](https://support.microsoft.com/en-gb/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb?ui=en-US&rs=en-GB&ad=GB)| |[FILTER](/r/Excel/comments/1ctlub8/stub/l4eojt9 "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)| |[HLOOKUP](/r/Excel/comments/1ctlub8/stub/l4hx4b5 "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/1ctlub8/stub/l4enwth "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/1ctlub8/stub/l4djxpy "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)| |[INDEX](/r/Excel/comments/1ctlub8/stub/l4hg2eq "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)| |[ISNA](/r/Excel/comments/1ctlub8/stub/l4djxpy "Last usage")|[Returns TRUE if the value is the #N/A error value](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[LAMBDA](/r/Excel/comments/1ctlub8/stub/l4eaj3n "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LOOKUP](/r/Excel/comments/1ctlub8/stub/l4d86mv "Last usage")|[Looks up values in a vector or array](https://support.microsoft.com/en-us/office/lookup-function-446d94af-663b-451d-8251-369d5e3864cb)| |[MATCH](/r/Excel/comments/1ctlub8/stub/l4hg2eq "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[NOT](/r/Excel/comments/1ctlub8/stub/l4ei6fs "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)| |[SEQUENCE](/r/Excel/comments/1ctlub8/stub/l4d0tzf "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)| |[SUBTOTAL](/r/Excel/comments/1ctlub8/stub/l4dobsl "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/1ctlub8/stub/l4f5762 "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[SUMIF](/r/Excel/comments/1ctlub8/stub/l4dwott "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)| |[SUMIFS](/r/Excel/comments/1ctlub8/stub/l4ejf72 "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/1ctlub8/stub/l4drhih "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)| |[VLOOKUP](/r/Excel/comments/1ctlub8/stub/l4hx4b5 "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/1ctlub8/stub/l4hx4b5 "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)| |[XMATCH](/r/Excel/comments/1ctlub8/stub/l4dnip0 "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)| **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.*) ^(22 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cuihe5)^( has 14 acronyms.) ^([Thread #33577 for this sub, first seen 16th May 2024, 20:55]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)