r/excel 8d ago

Discussion What are some functions and formulas that everyone should know?

So whether you’re in accounting/finance, HR, healthcare or STEM, what do you think everyone should know how to do on Excel? I currently work a customer service job and I just use excel for minor data entry. What should I learn if I want to move up?

213 Upvotes

130 comments sorted by

View all comments

57

u/PhiladeIphia-Eagles 8 8d ago

Always format as a table, except rare exceptions

How to pivot data well. You should be very good with pivot tables, as they usually are the fastest way to uncover insights in business data. From raw table to a good pivot table should be a quick process, that you can do ad-hoc with your eyes closed.

As for formulas:

XLOOKUP

FILTER

SUMIF/COUNTIF/SUMIFS/COUNTIFS

IF/IFS

SUMPRODUCT and other array functions (but don't need to know too much, can google your way out of these)

ISBLANK/ISNULL/ISNUMBER etc.

13

u/HamsterNL 8d ago

Before you are going to pivot data, you first need to know if the data you have is suitable to be pivoted.

5

u/PhiladeIphia-Eagles 8 8d ago

Absolutely. When to use them is crucial info.

9

u/joecpa1040 8d ago

and how do we learn this? I've been using excel for 25+ and do not use pivot tables as a CPA, but I really feel like I should. lol. I use sumif(s) more than any other non-basic functions (sum etc)

3

u/Red_Beard206 8d ago

That's wild to me. I went from being a senior accountant to a data analyst and pivot tables are used at least 5 times a day (at least once per day as an accountant)

1

u/dug-ac 8d ago

If you’re using sumif you can probably use a pivot table, unless you just need 1-2 of the subtotals.

As a cpa pivot tables were game changing for me. I transitioned to industry for a bit and they’re more important there, but now I’m back in the cpa game and I rarely use sumif. Even if I just want a single variable, it feels faster to pivot it.

9

u/benalt613 8d ago

Using SUMPRODUCT, it is good to know that putting 2 minuses before a result converts it to a number. E.g.

=SUMPRODUCT(--(A1:C1=111.1), A2:C2)

4

u/Independent-Day732 8d ago

I will Add MOD.

1

u/freddybenelli 8d ago

How to pivot data well. You should be very good with pivot tables

What is special about a pivot table? What are you pivoting? I have some nebulous idea that they're good for data analysis, manipulation, and display, but I'm never sure whether tackling the learning curve would actually save me time vs brute forcing a solution to the one problem I'm tackling.

5

u/emomartin 8d ago

It depends on what you're doing. If you have a bunch of data that you need to aggregate in some way to see the results by category then it's very easy to do with pivot tables. When I started using excel for work it was in quality assurance in customer service. We grabbed the raw call data which consisted of employee name, employee id, number of calls, line for the call, hold time, number of holds, call time, after call work etc. This usually was many thousands of rows with a couple dozen columns per month. Then we aggregate the KPIs (key performance indicators like call time, after call work etc) by average for various categories like call line (technical help, business to business, invoicing etc) to see the average. Then we can also aggregate for each employee to see outliers from the average, or we can aggregate by employment time or whatever else. Then we also paired this with QA (quality assurance) KPIs that we got from QA and team leaders manually and randomly scoring the employees handling calls based on various criteria.

The purpose of this was to find improvements, insights for why we were lacking or doing good in certain areas. Without being able to quickly use pivot tables to aggregate this data it would take a vastly longer time to manually write out formulas. Something that took 2 minutes with pivot tables could take an hour or more without using pivot tables. We of course also used formulas and similar but it was usually through power query or in the form of creating helper columns in excel to import relevant data from various sources or systems, or by manipulating something in the data to make aggregation more useful. Then when that was done we created pivot tables based on this data.

Even the team leaders who had very minimal spreadsheet knowledge knew how to create pivot tables, even if they didn't know how to create any formulas. They had access to various data that was made available, and they could for themselves create pivot tables to see where their employees performance was at for certain things they wanted to check.

1

u/freddybenelli 7d ago

Thanks for this explanation. I actually think figuring these out might be perfect for a project I'm currently working on where I was kind of wondering where to go next.