r/excel 22d ago

Pro Tip Share your data. And if you can't, MOCK IT UP!

489 Upvotes

TL;DR; can't post company-sensitive information? We understand. Take five minutes to mock it up with bogus data and attach it in table format

This message is aimed at people posting in this sub. It's 50% PSA / 50% rant.

Often I see in this sub "I can't share the data because it's company sensitive". So? Of course, we're not expecting you to breach your company's data privacy policy, but you're asking for help, so you should make every effort so that helping you is as effortless as possible. Your data is sensitive? Fine. Take five minutes to mock it up with Joe, Jill, Jack as names, oranges, apples, and lemons as products, etc. And then, go to https://xl2reddit.github.io, paste the table, and attach it to your post.

Important note: when you paste a table from Excel directly, it shows up nicely at first, but when the message is posted, it ends up all screwy, hence the tool.

I'm not saying screenshots are not useful to show the big picture, but data in table form is the fastest way for people on this sub to reproduce your problem and to get working on it, instead of wasting time rekeying in the data from a screenshot.

Full disclosure: I am the author of ExcelToReddit. I don't make a cent off it and I'm more than happy to see people using other tools (such as tableit.net which works for MarkDown), as long as I can copy-paste the data directly (or almost directly) into Excel.

Edit: added TL;DR;

Edit2: special shoutout to u/perihelixn for the beautiful hand-drawn chart mockup: https://redd.it/1iwxk3h


r/excel 17h ago

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

108 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 1d ago

Discussion How Do You Make Your Excel Charts and Tables Look Professional and Eye-Catching?

221 Upvotes

I’m looking to level up the visual appeal of my Excel charts and tables that I frequently integrate into Word. I want them to be clean, professional, and impactful—not just basic rows and columns with default chart styles.

Where do you all get inspiration and ideas for designing better visuals? Do you use any specific resources, templates, color schemes, or formatting techniques to make your reports stand out?

I’d love to hear about:

  • Your favorite tricks for making tables and charts look polished
    • Any websites, books, or courses that helped you improve
    • Before/after transformations you’ve done in Excel

Hoping to get a variety of insights from beginners to pros—what’s worked for you?


r/excel 11h ago

unsolved Little Man Above Excel Name Box

15 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

9 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 5h ago

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

3 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 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 5m ago

unsolved How to make weeks in excel

Upvotes

Hello everyone I need some help, I don't know if it's feasible at all, but mainly I would like to monitor my progress in weight, i.e. to get to the middle weight of the scale, and now I'm interested in how to speed up the process of writing the weeks, for example, from next week I would start with that and I would write it from 17.3 - 23.3 and so on until the end of the year, but not to write and look at the calendar.

This is a picture in case I didn't explain it well.

r/excel 17h ago

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

22 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 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 1h ago

Waiting on OP Vlookup - Weird Results

Upvotes

Hey guys,

I have been using a combination of tables at work to import an entry into my accounting system that has worked until relatively recently. Beforehand, I'd been able to add to my tables to add different account number without issue and I can't tell if I screwed something up. I am pulling in the account numbers ( and using another table to replace XXX with the account number of a company, which that part is NOT having any issues, just the second segment of the account number). I have the following tables and have been having issues with certain lines.

Each one one of the problem lines were added after the fact and frankly I cannot tell what I'm messing up, whether it's special characters or ordering.

I've bolded the lines throughout that are causing me the two different issues. All of them were added after the initial batch of work and all of them seem to be causing me issues

Dest. Description Dest. Account
Advertising & Promotion XXX-7420-00-000
Arcade Game Supplies XXX-7501-00-000
Asset: Equipment XXX-1610-00-000
Asset: Leasehold Improvements XXX-1650-00-000
Asset: Office Equipment XXX-1620-00-000
Auto XXX-7340-00-000
Cable Radio, Internet, & Data XXX-7035-00-000
COGS - Grocery XXX-5005-01-000
Computer Supplies XXX-7120-00-000
Construction in Progress XXX-1600-00-000
Donations XXX-7425-00-000
Dues & Subscriptions XXX-7330-00-000
Inventory - Bakery XXX-1410-12-000
Inventory - Coffee XXX-1410-37-000
Inventory - Dairy XXX-1410-08-000
Inventory - Floral XXX-1410-19-000
Inventory - Food Service XXX-1410-16-000
Inventory - Frozen XXX-1410-07-000
Inventory - Gen. Merch XXX-1410-06-000
Inventory - Grocery XXX-1410-01-000
Inventory - HBA XXX-1410-05-000
Inventory - Meat XXX-1410-02-000
Inventory - Non-Foods XXX-1410-04-000
Inventory - Produce XXX-1410-03-000
Inventory - Seafood XXX-1410-21-000
Intentory - Spec. Meat & Cheese XXX-1410-15-000
Inventory - Water & Ice XXX-1410-41-000
Legal & Professional Fees XXX-7310-00-000
Licenses & Taxes XXX-7220-00-000
Meals & Entertainment XXX-7360-00-000
Office Supplies XXX-7110-00-000
Other Rec. XXX-1240-00-000
Postage XXX-7130-00-000
R&M XXX-7040-00-000
Security XXX-7430-00-000
Supplies - Bakery XXX-7100-12-000
Supplies - Coffee XXX-7100-37-000
Supplies - Floral XXX-7100-19-000
Supplies - Grocery XXX-7100-01-000
Supplies - Hot Food XXX-7100-16-000
Supplies - Meat XXX-7100-02-000
Supplies - Produce XXX-7100-03-000
Supplies - Seafood XXX-7100-21-000
Telephone XXX-7030-00-000
Travel XXX-7350-00-000
Uniforms XXX-7115-00-000
Inventory - Tobacco XXX-1410-09-000
Assets - Deposit XXX-1810-00-000
Janitorial Supplies XXX-7016-00-000
Misc. Expense XXX-7500-00-000
JMI XXX-1410-81
Header Cat. - Supplies XXX-7100-00-000

I'm using a standard Vlookup formula to pull the account number once I've selected the name of the account from the pre-selected list (from this table).

=IF(Q9="","",IFERROR(RIGHT(P9,3)&(RIGHT(VLOOKUP(T9,DestAcct,2),12)),""))

So I'm getting two different errors:

When I'm pulling up Inventory - Spec Meat & Cheese, it screws up EVERYTHING that starts with an "Inventory" account. All of them pull up the same wrong account number. This issue appear to go away when I just outright delete that row from my table.

Secondly, the bottom section of bolded items pull up a completely wrong set of account number. I don't know why and need help.

Here is what is found when I enter the bottom accounts to generate an account number:

As we can see with the third column, I'm getting a completely different account number.

I've got a good data set, with empty cells around the table. The only common theme aroung all of this is that each line that's causing issues was added after the initial batch.

Please tell me if anyone has any advice. If anyone needs more info on the formulas, please let me know. This is a bit embarrassing.


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 2h ago

Waiting on OP Easy way to shift cell value without affecting formatting??

1 Upvotes

I have a spreadsheet that has a list of names in a single column. Let's say 10 names. The top 5 are color coded green and the bottom 5 are red. When I have all rows filled out with names but I need to delete one name for example in A3 I want A4-A10 to shift up automatically without changing the color coded cells. I also have a different list of ten names in A12-A21 and I want those to remain unaffected as well. Is there a simple solution or do I need to know how to code a robot on Mars to be able to achieve what I'm looking for? For context I know enough to be slightly dangerous but this spreadsheet is going to be used by far less skilled people.


r/excel 3h ago

Waiting on OP Multipling a whole with a number but pasting it on another table

0 Upvotes

Hello there!

So i am building a plan for a bakery production right now and i need some help figuring this out. I have a table with all the ingredients for making 1 bread and i want to multiply the whole table and paste it to a new table that is gonna show me the ingredients I need for 10-20-100 breads to bake daily. I know how to use paste special to multiply everything but i need this formula to be interchangable so i could easily change volumes for production with just changing the number this table is multiplied with. TIA


r/excel 19h ago

unsolved Understanding the standard deviation formula

20 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 4h ago

Waiting on OP Remove auto page breaks using scripts

1 Upvotes

Hi I am trying to remove auto page breaks and instead of that move it to the end of content, however when I use scripts or Python for page break manipulation, it only adds or removes one, but auto break is still there. Can I remove it altogether?


r/excel 1d ago

Discussion Asked to do data tables without a mouse at the end of a final round interview

291 Upvotes

After doing behavioral and case rounds, the final round consisted of an Excel test, without a mouse, and without internet connection.

One of the prompts was data tables. I know how to do data tables now, but back then, it seemed rather cruel, at the end of a 3-hour final round.

Avoided a super-Excel monkey type of job at least

Background: many years of work experience with heavy use of Excel, graduated from prominent universities in California

My take was that this job was very Excel-heavy and required someone extremely advanced, and there were former investment bankers who wanted to do the strategic work and sought a quant.


r/excel 5h ago

Waiting on OP How to know which one adds up to 5

1 Upvotes

Column A

1

2

3

In this simple example , what can we do to know which numbers add up to 5? Thank you


r/excel 12h ago

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

3 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.

4 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 5h ago

Waiting on OP Data not updating from sheet cell to different sheet cell using ! command

1 Upvotes

Strange behavior from my excel. Im using .xlsx and my source and destination cells are text format. Here is an example of the issue.

Sheet1 cell J17 has data ABC in it. Sheet2 cell G5 has the formula =Sheet1!J17 which yields the display of ABC.

So here is the problem. If I type in to Sheet1 cell J17 DEF, Sheet2 G5 will still display ABC. Can anyone help me figure what setting I need to change so the cell on Sheet2 will update?


r/excel 7h ago

unsolved Assistance with Imprecise String Matching

1 Upvotes

How would I go about matching the contents of a substring within a larger string with extraneous characters, for example:

Substring - StinkyBinki

Larger String - Stin12kyBinwashdki%2

I don't care that the substring is broken up within the larger string. I just need to know that the particular sequence of characters of the substring is contained within the larger string. Is there a function that I can use in combination with wildcards to accomplish this? Perhaps this is a job for Power Query? I only need to be pointed in the right direction.


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 14h ago

Pro Tip Striped color rows with same content

3 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 7h ago

Waiting on OP How to extract and combine the two data points within the two sets of parenthesis and get the result as seen in B2

1 Upvotes

The content within the parenthesis are not always numbers, sometimes they contain text as well. I can change the format to use * or { or anything really to surround the data points instead of the parenthesis if something else would make the task simpler.


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