r/excel

[supprimé]

I feel like every company has at least one spreadsheet that started as “just a quick tracker” and somehow became business-critical three years later.

More tabs get added, formulas get more complicated, 100k rows becomes 200k, multiple people need to use it... and somehow the answer is still “just add another column.”

At what point do you look at a spreadsheet and say, okay, this really needs to be a database, ERP, Power BI, an actual application, or something else?

And what was the spreadsheet that finally made you realize you'd crossed that line?

Just got a file from someone and they set up the sheet by filling all cells with white and then putting borders around every cell. And the data only took up 4 columns by 30 rows 😵‍💫

What are some odd setups you've seen?

I have just discovered that if password protected excel files with locked and hidden ranges are uploaded to Google Sheets all our Custom formulas are visible and ediatble !

What exact functions or tools should I be learning in Excel to solve the problem below?

Problem: I have rookies and veterans, and their performance is measured differently. Each player, whether rookie or veteran, will have their data changed at a different pace based on how much they play.

Current workflow: I am using a pivot table to parse the data out manually by separating rookies and vets, then applying the sumproduct formula to their performance to apply the corresponding weights for each event they play.

Issue with current workflow: I can get the data I need, but the problem with this is it's not scalable or efficient to retrieve between rookies/vets and the different pace/amount each player plays. The way I am doing it now is a ton of manual work for the amount of data points I need to retrieve.

I am happy to learn whatever I need to in order to accomplish my goal, but I am not sure exactly what part of Excel it is I need to learn. I would love some guidance!

(edit - added more context below. Added pics to replies)

Here is some dummy data. Bear with me while I try to explain this.

I have veterans and rookies in a league I run. They are rated based on performance with two different game types (Gametype A and Gametype B). They have points and deductions for each game type, and based on those numbers they will receive a rating for the corresponding game type via a formula I have. Players will also have a comprehensive rating that takes into account both game types, which is their overall rating.

However, these ratings and pieces of data need to be dynamic based on a player's type (rookie/veteran) and how many events they have played. A player's Draft Rating becomes less and less significant the more events they play, and the weights will be dramatically different depending upon if the player is a veteran or a rookie.

I am trying to figure out a way how I can parse this data properly based on the player type (rookie/veteran) weights and how many events they have played.

I am having a hard time processing how to best go about this, or which tool/function I should be using. I am not an expert with Excel, but I am happy to learn. I understand pivot tables, but not sure if a pivot table is adequate for what I am trying to accomplish. But, I do not know what I should be using or learning based on what I am trying to do.

The data I am trying to retrieve:

- Gametype A Rating

- Gametype A Point Quota

- Gametype B Rating

- Gametype B Point Quota

- Comprehensive Rating

- Comprehensive Point Quota

Of course, all of those data points above need to have their respective weights applied based on the player type and number of games played for each game type.

I hope this helps outline my problem better! Thanks again for your help!

Hi!
I am pretty much using Excel for the first time in my life, and my deadline is tomorrow. I tried to find a solution online, and I found some results about comparing tables, but I honestly didn't understand if that was even what I'm looking for... If I had a few more days I might be able to figure it out on my own, but right now I'm running out of time.

Here's my situation: I have two big tables with identical labelling (they're comparisons of data between two different years). They're formatted as tables, like in the mock-up just much larger. The numbers in the mock-up don't make sense, don't mind them.

I want to add difference markers to the second table, something like those arrows or percentages I manually added in the mock-up. I'm hoping there's some pre-programmed function I just can't find?

Ideally the solution would be through conditional formatting, because adding new rows in the middle would muck up everything below those two tables and take ages to fix...

Percentages would be great, but arrows would be better than nothing.

Thank you!

https://preview.redd.it/5l659wabttth1.png?width=553&format=png&auto=webp&s=7618de313ca394a21f9a23a0a25dc08f74a9c25f

Hi all, I was looking for some help.

I am a total newbie to excel but have to get to grips pretty fast with it for my work role. We currently have a process where we have a list of nominations with people's names, in column D. There are 996 rows in this spreadsheet.

Part of this process is for me to go through column D and remove all the names. Currently we are doing find x name and replace it with a capital X to make all entries anonymous. This process takes me hours and hours and hours.

It would really swing in my favour if there was a way to do this quicker, like any hacks anyone has? I did try a lengthy formula but it only worked on one entry and not for the full column.

Ideally, it would be without having to do pivot tables or anything but would be so grateful if you have any ideas! ❤️

Thanks all

P.S I am taking all the YouTube courses and watching all the excel clips, tips and videos that I possibly can.

Hi, my company recently changed how some error reports I run output, and now all my various helper sheets with formulas that look for numbers in column A of the reports error:

=FILTER(HSTACK(...),ISNUMBER('Error Report'!A:A))

I can't just filter for anything not blank because there are headings, and I need the whole column cause the reports can have thousands of lines some days. I've tried adding NUMBERVALUE within ISNUMBER and a few other attempted workarounds, but they either errored or caused other problems.

It's not a big deal to manually convert the whole output to number every day, and I've added a helper column with =IF(ISBLANK(A3),"",NUMBERVALUE(A3)) copied down like 10k rows and updated the FILTER to look at that column instead, which is ok enough, but the extra few clicks is nagging at me like a hangnail...

Is there a more elegant solution for the FILTER / ISNUMBER I'm missing?

https://preview.redd.it/9g0olfr43vth1.png?width=313&format=png&auto=webp&s=0b5fd9579fc021652ccfa1af9cc6e555a5de5a57

I have created a list of items in a game and have placed them into a table with checkboxes in the first column. I use the checkboxes to annotate if I own the item or not. I have 46 total pages separating where each item comes from. I am using the web version of the Excel so I can't use macros unfortunately.

Would it be possible to create a new sheet with a function that creates a list of all the items I don't own from across the entire workbook?

spent three hours today untangling a workbook where someone nested so many ⁠IFS⁠ statements and volatile functions that recalculating a single row felt like loading a video game

started rebuilding the whole thing from scratch using ⁠LET⁠ and dynamic arrays and honestly the difference is night and day

curious what the one formula or trick is that completely changed how you structure your sheets?

https://preview.redd.it/lx618qczzvth1.png?width=370&format=png&auto=webp&s=68da901c1c58e1673f9a62da457e144b61d7ffaf

I want to use this formula for example:

=IF(XLOOKUP(A2,A:.A,B:.B,"")="","Not Ready",XLOOKUP(A2,A:.A,B:.B,""))

I put the dot operator to trim trailing cells. The problem is that B5 is empty and the formula is giving me an error. If I put a value in B5 then it works.

How can I make it work as long as A5 has a value?

Cltrl + D, cltrl + V, and dragging cross hair down is not working. I'm not able to copy formula below

What might be the issue.

[removed]

Mac Excel 16.113.3 (365), macOS 26.5

I read .xlsx files with a script (ExcelJS 4.4, Node), so I only ever see whatever result Excel cached at save time. Trying to figure out if the file can tell me when that cache is lying.

Tiny repro:

1. A1 = 10, B1 = =A1*2 → 20

2. Calc set to Manual, "Recalculate workbook before saving" unchecked

3. A1 → 15, save

Excel shows B1 as 20 with a strikethrough (Format Stale Values), so Excel clearly knows. The file still stores 20, and my script happily reads 20.

What's in xl/workbook.xml:
<calcPr calcId="181029" calcMode="manual" calcCompleted="0" calcOnSave="0"/>
and B1 is saved as <f ca="1">A1*2</f>

So calcCompleted="0" looks like the flag I want. Before I build a warning on it:

1. does Excel ever write calcCompleted="0" when the values are actually fine?

2. if it's missing or "1", can the cached values still be stale?

3. what do non-Excel files do (LibreOffice, Google Sheets downloads, openpyxl)? Do they write calcPr at all?

ExcelJS doesn't give me calcPr, so I'll parse workbook.xml myself. That part's easy. I just don't want to build on a flag that only works half the time.

Disclosure: this is for a tool I'm building (Pactbeam). No link, just want to get this bit right.

I’d like to be able to create something where if I colour let’s say D5 in a colour, K5 is automatically made that same colour and all cells in the H column of that colour only are added to a total in ,for example, Q10.

Sorry if I haven’t given much of a description to what I would like to do im still quite new to excel, but any suggestions and advice would be really appreciated.

Edit: thank you for the quick responses! I’ll see what I can accomplish with conditional formatting as it’s completely new to me and hopefully I can come back with some good news!
Really appreciate the advice.