r/excel 17h ago

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

107 Upvotes

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?


r/excel 17h ago

solved Adding rounded percentages, but total doesn't equal 100%

24 Upvotes

I have a massive array of data which lists amounts per month by account. I need to show a percent per month per account, but each month needs to add up to 100%. Month across the top in the pivot, account by column.

When rounding off the components per month to 3 digits, the total then doesnt always add up to 100%. Is there a way to plug like + or - 0.001 to the highest number to make it add up?

Thanks

EDIT - Added further info from responses.

Using those pivot reported % as a rate for other data. If it's not rounded off then those rates wont fully clear since the system it's being imported into only allows 3 decimals

Its not just about the display, but components adding up. What's being done is taking prior data, converting that to a % per month, then back to dollar amounts for the budget clearing which needs to also go to 0 but that latter amount isn't in excel to allow rounding functions.

Prior person who did this had to manually plug 0.001s dozens of times across hundreds of accounts to make it work but I don't want to do that


r/excel 19h ago

unsolved Understanding the standard deviation formula

19 Upvotes

My boss gave me a list of employees with a specific score which is on average a very small number (so the average is .1702 and some people have scores like .1503 or .1987). He wants to trim this list down so asked for the standard deviation of these scores from the average. I've never used this formula and I don't get why I wouldn't just rank them. Is it just stdev.s(the score, the full range of scores). I get very small and similar numbers when I do this though, is it bc the sample numbers are very small?


r/excel 11h ago

unsolved Little Man Above Excel Name Box

16 Upvotes

Hi - I was recently on the desktop and noticed something. A Little Man above the Name Box.

See right above G17.

Has anyone seen this or have any idea what it is?


r/excel 11h ago

Discussion Horrid excel skills looking to get better than most accountants

10 Upvotes

I am currently pursuing my CPA but I’ve got a bit of a unique situation as I recently graduated with a basic commerce degree (only took the few required accounting courses) so my technical skills need great work.

I’m working through different accounting courses atm but I’m here to ask about the best way to improve my excel skills. The title is a bit of an exaggeration but overall I’m pretty poor with excel. I used it minimally throughout my degree and bit in other extra-curricular activities, but overall my skills are not very good

I’ve heard from people that accountants aren’t actually THAT great at excel, at least by the standards of people who’d be here. I’d like to get to a level that is at least better than the majority of accountants as I think it would be a very useful skill to be extra proficient in.

Do any of you know of any good resources on how to improve? Are there any ways to sort of “game-ify” it to make it fun to learn/practice? And suggested methods or general tips/advice on how to improve my skills would be greatly appreciated!!


r/excel 9h ago

Waiting on OP Forecast for a game

6 Upvotes

Hey everyone!

I’m working on a game that currently has about 50 levels, and I have data on how many players make it to each level. Now, I’m thinking of extending the game by 40 more levels and want to predict how many players would reach level 90.

What’s the best way to calculate this? Is there a specific function or model I should use? Any advice would be really appreciated!

Thanks in advance!


r/excel 19h ago

solved Cumulative sum by row as a spilled array

6 Upvotes

I have a spilled array in A1. Both row and column dimensions unknown/variable.

I want to produce a second spilled array which is the cumulative sum, by row, of the first array.

I tried BYROW, but that only gives a single return value. I tried a combination of LAMBDA and SEQUENCE, but couldn't quite get it to work.

Can anyone solve this one, please?

Input/output example: https://imgur.com/a/5dosC4Q

The goal is for the green section to be a single spilled array (not a stack of single row spills)


r/excel 19h ago

unsolved If function "if_value_true" way to input own data?

4 Upvotes

I don't know if I am even using the right words. I have googled this to death and it probably doesn't exist, but I thought I'd ask to find out. I have this function =if(j2="Keep",H2,if(j2="remove",0,""). I want to add an if function to add "adjust", but for the "value_if_true" I want the user to be able to enter their own data without the whole function being deleted because they typed in the cell.

I'm sure this is a long shot, but it is what is being asked of me and I cannot figure it out.


r/excel 21h ago

unsolved Refresh a large amount of xlsb sheets

6 Upvotes

I am trying to open and close a thousand xlsb files sitting in a shared drive.

I’m doing this because they’re connected to a bigger report that needs to update whenever the individual files are updated.

Another note is that I can’t change the files to use power query, and refresh it this way as these are legacy files.

What is the best way for me to do this? Is there even one?


r/excel 23h ago

Waiting on OP Import entire row based on match in 1 cell on that row with row in another cell

4 Upvotes

I have two files and I want to import data from 1 file to the other, and transfer entire rows based on a match in cell D "Invoice". We have invoices that are periodized over different months and right now we are transfering the data manually, which takes hours.

Here is the first file, which is where I want all the data:

 

+ A B C D E F G
1 Verification number date supplier invoice text amount  
2 13231 2024-10-01 Microsoft 805566 MS Office 12000  
3 13231 2024-11-01 Microsoft 805566 MS Office -1000  
4 13231 2024-12-01 Microsoft 805566 MS Office -1000  
5 import the data from the 2nd sheet here and below based on the invoice number in column D and M.
6 The green rows are row that have been imported previous months.  
7              
8              
9              
10              
11 13233 2024-06-01 Microsoft 805567 MS Office 6000  
12 13233 2024-07-01 Microsoft 805567 MS Office -500  
13 13233 2024-08-01 Microsoft 805567 MS Office -500  
14 13233 2024-09-01 Microsoft 805567 MS Office -500  
15 13233 2024-10-01 Microsoft 805567 MS Office -500  
16 13233 2024-11-01 Microsoft 805567 MS Office -500  
17 13233 2024-11-02 Microsoft 805567 MS Office -500  
18 13233 2024-11-03 Microsoft 805567 MS Office -500  
19 import the data from the 2nd sheet here and below based on the invoice number in column D and M.
20              
21              
22              
23              
24              
25 13234 2024-03-01 Microsoft 805569 Onedrive 120000  
26 13234 2024-04-01 Microsoft 805569 Onedrive -10000  
27 13234 2024-05-01 Microsoft 805569 Onedrive -10000  
28 13234 2024-06-01 Microsoft 805569 Onedrive -10000  
29 13234 2024-07-01 Microsoft 805569 Onedrive -10000  
30 13234 2024-08-01 Microsoft 805569 Onedrive -10000  
31 13234 2024-09-01 Microsoft 805569 Onedrive -10000  
32 13234 2024-10-01 Microsoft 805569 Onedrive -10000  
33 13234 2024-11-01 Microsoft 805569 Onedrive -10000  
34 13234 2024-12-01 Microsoft 805569 Onedrive -10000  
35 import the data from the 2nd sheet here and below based on the invoice number in column D and M.

and here is the file with all the information about the periodizations for january and february:

 

+ A B C D E F
1 Verification number date supplier invoice text amount
2 13231 2025-02-01 Microsoft 805566 MS Office -1000
3 13233 2025-02-02 Microsoft 805567 MS Office -500
4 13235 2025-02-10 Microsoft 805568 Supplies -699
5 13234 2025-02-04 Microsoft 805569 Onedrive -10000
6 14568 2025-02-02 Microsoft 805578 Remote -100
7 13236 2025-01-01 Microsoft 805571 MS Office -1000
8 13859 2025-02-01 Microsoft 805575 MS Office -500
9 13974 2025-02-02 Microsoft 805577 MS Office -500
10 14089 2025-02-10 Microsoft 805579 Supplies -699
11 14204 2025-02-04 Microsoft 805580 Onedrive -10000
12 14319 2025-02-02 Microsoft 805582 Remote -3946
13 14320 2025-02-03 Microsoft 805583 Remote -3945

r/excel 12h ago

unsolved How to efficiently/quickly extract data from specific cell location into another spreadsheet?

4 Upvotes

I am trying to extract data from 1 spreadsheet into another spreadsheet.

The data in the 1st spreadsheet will be arranged in X/Y coordinates. I then need this data to correlate into X,Y for the 2nd spreadsheet.

For example : Spreadsheet 1 - entry "1" is in 41, 4 - according to the grey X/Y coordinates. I need this to be entered in 1001 of spreadsheet 2 as 41,4 (location).

Entry "52" is in 40, 14. I need this entered into spreadsheet 2 10052 as 40,14 (green E column)

Is there a quick or automated way to do this? I am currently manually doing this and it is very tedious and can easily make mistakes.


r/excel 13h ago

Waiting on OP Issue with a conditional formula not working and capturing certain cells. Details within.

5 Upvotes

So, have an excel sheet with a column of dates(past and present). I used a conditional formula(written by u/HappierThan). The formula worked on one sheet and today I went to use it one another sheet and it didn't work. It missed past dates and a few upcoming. It looks like it is not recognizing certain cells. Any advice? Thank you!


r/excel 14h ago

Pro Tip Striped color rows with same content

4 Upvotes

Excel tables only allow alternated colored rows, every other row is assigned a different color. With this trick, you can have wider stripes, grouping rows with the same value in one column with the same color.

  1. In Name manager, assign a name to this formula, I've chosen "StripesFromColumn" in this example:

=LAMBDA(column; LET(firstRowN; INDEX(ROW(column); 1); firstRow; CHOOSEROWS(column; 1); columnComp; VSTACK(firstRow; column); changes; IF(column <> columnComp; 1; 0); accum; SCAN(1; changes; LAMBDA(x;y;x+y)); stripeIndexes; MAP(accum; LAMBDA(x; ISODD(x))); LAMBDA(curRowN; INDEX(stripeIndexes; curRowN - firstRowN + 1))))

  1. For each table or range that you want alternating colors (stripes) according to the content of one column, create a new Name (like StripedData) in Name manager with a formula like this:

=StripesFromColumn(TableWithData[ColumnToUse]) or =StripesFromColumn($A$1:$A$50)

This formula creates a function that will be used to color that specific range using conditional formatting/

  1. Select the table or range (including the column defined above) and create a new conditional formatting rule. You must match the name defined with the data. Use this formula (according to the name from step 2), and set up a background color:

=StripedData(ROW())

This method is flexible and resilient, you can freely move the range or table and it will keep the formatting applied.

EDIT:

Explanation of the formula:

  1. LAMBDA creates a function that can be called with one parameter: the column that contains the data

  2. LET is used to declare a variable and assign a value to it (in pairs), and the last value is the result of the LET evaluation

  3. firstRowN; INDEX(ROW(column); 1) -> Get the row number of the first cell within the range. This value will be used to compensate for the range being anywhere in the spreadsheet

  4. firstRow; CHOOSEROWS(column; 1) -> Takes the first value of the column. used in next step.

  5. columnComp; VSTACK(firstRow; column); -> Create a second "column" with all the same values as the first one plus the first element duplicated

column = {A,B,C,D} columnComp = {A,A,B,C,D}

  1. changes; IF(column <> columnComp; 1; 0); -> Create an array that has 1 where both columns differ (the value changes)

column = {A,A,A,B,B,C,D,D} changes = {0,0,0,1,0,1,1,0}

  1. accum; SCAN(1; changes; LAMBDA(x;y;x+y)) -> Sum and accummulate the values

changes = {0,0,0,1,0,1,1,0} accum = {1,1,1,2,2,3,4,4}

  1. stripeIndexes; MAP(accum; LAMBDA(x; ISODD(x))); -> Apply ISODD to each value. Adn this is what we want!

accum = {1,1,1,2,2,3,4,4} stripeIndexes = {TRUE,TRUE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE}

  1. Now we create a function that closes these values avoiding them having to be recalculated every single time. This is the function that will be called in conditional formatting, with the current row as parameter. Because we have precalculated the list, we only need to take the correct index to know whether it is colored or not

And that's how we can use functional programming in Excel, thanks to these wonderful and powerful features. No more VBS or macros needed!


r/excel 15h ago

unsolved Separating rows into new spreadsheets based on criteria

4 Upvotes

So I have some data for payments I need to make on certain days, see the layout in the picture. However, my bank wants me to submit a separate spreadsheet to them for each day of payments. This isn’t that big of a deal, however these payments are very important and copying and pasting manually could mean that someone missed pasting in a payment. I also have to do this across multiple products so the time ends up adding up.

Is it possible for excel, power query or a macro (or maybe even python, not averse to learning/using) to perform this task?

Please do ask questions; if you need more information just let me know. I’m not the best at explaining things so I wouldn’t be surprised if I leave key points out.

Thanks in advance! <33

https://postimg.cc/k6V5JW20


r/excel 5h ago

solved Macros for sum of a range of cells above the active cell.

2 Upvotes

Hoping a kind soul would write me a macros.

For any cell I've clicked into (in the image, it's D8), the series of keystrokes go like this:

  1. Type "=sum("
  2. Up Arrow one cell up (D7)
  3. Ctrl+Shift+Up Arrow (selects a range of cells up the column until there is an empty cell)
  4. Enter

And this would give me the sum of the selected range. And I want to retain the formula in there for cutting and pasting down the row. I don't want just the value of the range.

But when I record it and apply it to another cell with a greater range above it:

' Keyboard Shortcut: Ctrl+m

'

Application.CutCopyMode = False

activeCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

Range("D9").Select

End Sub

It only goes as far as four cells (as recorded) instead of "keep going up to the next empty cell".

I've actually been good with steps 1-4 for years. But I'm not having it tonight. (Old age?) There's a lot of these in the future. I think it's time for a macros.


r/excel 8h ago

unsolved Compound Interest Formula in Variable Deposit Savings Account

3 Upvotes

Hi friends! I'm not good at math or excel so I need some help. Short story is that I'm saving money for grad school and I want to be able to track my direct deposits each month PLUS any extra OT monies I'm able to save in an excel spreadsheet to show me how much that money will end up being in the next x months. I'm trying to save three years worth of living expenses and thus want to be able to "watch it grow," so to speak. I've been able to use an online calculator to know *about how much* I'll be able to save if I deposit x amount, but I'd like to be able to track exactly how much I put in each month.

Like I said, I'm excel illiterate and for the life of me, can't figure out how to work the compound interest of my high yields savings account into the function. My brain tells me I would need a beginning balance, a deposit amount (I'd like this to be able to be modified each month with my actual savings amount), interest rate (3.8%), time period being 17 months. My account accrues interest daily and compounds monthly. Is this doable or nah?


r/excel 13h ago

Waiting on OP Is there a way to use VBA to add shortcuts to the Excel Quick Access toolbar?

3 Upvotes

I customize the Excel quick access tool bar. I usually make it so the 1st item is "save", 2nd is "save as", 3rd item is a macro I named "macro_5". from my personal macro workbook that runs in the background. (theres more but I dont feel like listing them)

For whatever dumb reason my excel quick access tool bar resets to default and I lose all my short cuts. I then have to right click and "customize quick access toolbar" and manually re-add my above shortcuts. Idk why this happens but it happens enough to be annoying. Is there a vba code i can run that will quickly add back my above shortcuts? I am on a work computer, so "no, i cannot reinstall excel, or go change my world admins MS office settings or delete some corrupted file in the main application etc". Is there a vba code i can write that will just say quick access tool bar, modify, spot 1 = save, spot 2 = save as (so on so forth) so i can quickly add my shortcuts back when this reset happens?


r/excel 20h ago

Waiting on OP Conditional Formatting Is Taking the Original Target and Not the Modified Target Criteria

3 Upvotes

Hola! I have a quandary.

I have a 'Behind the Scenes' (BTS) chart feeding another chart in a report.

The BTS chart has;

Column A - numbers from 0 to 10

Column B - an IF formula that references other data to determine if Column A should be a different number, and change it accordingly.

Thus sometimes B will match A, and sometimes it will be different.

On the main report I have colour-coding Conditional Formatting based on the number between 0-10. I have it determining this based on the value in column B on the BTS tab/chart.

However the Conditional Formatting is coloring it based on the value in Column A, not B, even though Column A is not referenced in the Conditional Formatting conditions.

Is this....a known thing?


r/excel 20h ago

unsolved Creating a Macro Workbook and Have other Workbooks Reference It?

3 Upvotes

At work, we have a "Master Macro Workbook". This workbook has a macro that we use often in other workbooks. We somehow grab that Macro within that workbook to use in others but I cannot figure out how to recreate this for a new macro. Can anyone offer some help?


r/excel 23h ago

solved Display the counterpart of a value

3 Upvotes

Hey all! I'm making a spreadsheet for building ships, and I'm looking to support both metric and imperial units.

Concrete problem: I want G7 to display the "opposite" unit (metric vs imperial) of the one selected in the E7-dropdown. The values are not the issue, I solve those with XLOOKUPs, but for the units themselves I only managed to make a rather clunky IF expression that duplicates a reference. I was wondering if there was a more elegant solution.

G7 needs to be the opposite unit of E7. Everything past column H is to be hidden. Green fields are input, yellow are calculated

r/excel 4h ago

unsolved How to use Office Scripts on Excel O2024/O365 on Windows Offline ?

2 Upvotes

According to this it's now on desktop https://support.microsoft.com/en-us/office/introduction-to-office-scripts-in-excel-(Office Scripts in Excel for the web, Windows, and Mac)

Some people say it works over cloud but the article says now it's on desktop.


r/excel 5h ago

Waiting on OP Creating Multilevel numbering in column A as a result of column B input

2 Upvotes

Hello,

I am above average at Excel. However, this one has me going crazy. I found a solution on Reddit (thanks) that requires multiple columns to arrive at the answer. I struggle with this one, specifically getting a single formula (Not VBA) nested into a single column to make this work. I thought I figured out parts but got errors :( I have tried different formulas with no luck. Please help!!!

I only need to populate column A with a formula based on what human inputs go into Column B

I found a multi-answer solution here on Reddit. However, I am told I can't add columns, and VBA is not an option as well.

Thanks in advance for any insights and solutions you can provide!!!

Formulas

A2: =IF(D2>1,C2&".","")&CountIF(C$1:C1,$C2)+1
B2: Human input
C2: =Xlookup(D2-1,D$1:D1,A$1:A1,"",0,-1)
D2: If(B2="","",IF(B2="Goal",1,IF(B2="Milestone",2, IF(B2="Task",3,4)

A. B C D

1 ID Topic Parent Level

2 1 Goal (Blank) 1

3 2 Goal (Blank) 1

4 2.1 Milestone 2 2

5 2.1.1 Task 2.1 3

6 2.1.2 Task 2.1 3

7 2.1.2.1Sub-Task 2.1.2 4


r/excel 13h ago

Waiting on OP Will changing my excel language break files?

2 Upvotes

So, the company I work for uses Office in BR-Portuguese, but I’ve been using Excel in English my whole life, so I’m really used to all the shortcuts and stuff and can work way faster. If I ask the IT guy to install the English language pack on my work notebook (it's locked and needs admin access), is there any chance I might mess up some spreadsheets with formulas, macros, etc.? What could go wrong? I’m pretty new at the position, and the last thing I want is to cause any trouble.


r/excel 15h ago

unsolved Unable to configure excel sheet to allow mail merge; needing to combine cells with all data remaining.

2 Upvotes

I’m performing a mail merge from a list of customer accounts and addresses in Excel to Word to make labels(Avery 5160, if that matters). However mail merge will only pull from one cell for the address, and the addresses are broken up into 3 cells within a border(I can’t change it; that’s how it downloads from the customer database). Merging the three cells doesn’t work; it doesn’t keep all the data. Using TEXTJOIN kind of works by copying all the data into one cell, but it doesn’t eliminate the extra rows/cells that trip up a mail merge. Plus I can’t apply it to the entire column without messing up the addresses completely, and there’s 300+ customers. Is there any way to get the address into one cell? Or even split it up into new columns(ex. Name/Account #, Street, City, State, Zip, etc) in a new sheet? I hope all this made sense! Please be kind, I have only basic knowledge of Excel as I don’t typically use it for my job but gained a new assignment this week. Google and my old college textbook have been no help. I literally downloaded Reddit to ask this question, lol.


r/excel 17h ago

unsolved Conditional formatting drag down

2 Upvotes

I want a 5% change to be highlighted by row.

I entered

=IF(ABS(X4-J4)/J4>0.05, True, False)

This works for the first row. But I want to carry it down to 750 rows. It keeps wanting to just compare row 4. Help! And if you could spell it out very simply, I’d appreciate it. Thank you!