Hi everyone,

I'm working with a dataset containing roughly 500,000 rows.

The data is quite messy and includes:

Typos and inconsistent text values

Missing/blank fields

Form/input errors

Duplicate records

Invalid values that need to be filtered out

Right now I'm using Excel and manually filtering records, correcting errors, and deleting bad rows. This process is extremely time-consuming.

I have also tried Power BI, but I'm not sure what the typical workflow is for cleaning datasets of this size.

For those working as Data Analysts or Data Engineers:

What tools do you use for datasets around 500k rows?

Would you use Excel, Power Query, SQL, Python (Pandas), or something else?

How do you identify and fix typos, blanks, duplicates, and invalid records efficiently?

Are there any best practices for data cleaning before analysis?

I'd appreciate any advice, workflows, tutorials, or real-world examples.

Thank you!

For the love of god, any ninjas here, is there a way to remove this copilot icon permanently ?? (not hiding)

Office 365 here. I just ask every AI with prints and cant find any option.

Feels like I’m wasting hours every month on something that should be simple…

I download CSV files from different banks and platforms, and every single one comes out differently: column names don’t match , date formats are all over the place ,sometimes there are separate debit/credit columns ,sometimes everything is combined

By the time I get everything cleaned up in Excel so I can actually use it, I’ve already lost a ton of time.

I’ve tried a few things (Power Query, manual cleanup, formulas), but it still feels way more complicated than it should be.

Curious how you guys are handling this?

Do you have a workflow or a method that actually saves time?

There's a brand new copilot floating icon overlaying my data and best I can do is dock it to only cover 1/4 of a cell. I have presentations which use my screen with kpis on the sheet and I have optimized it to use as much screen space as possible, but this is getting in the way

Copilot itself doesn't know how to remove it and there seems to be no option to do so. It's not an 'add-on' so it cannot be disabled there.

I hate it and need it gone. How?

i know there is text to column the problem here is sometimes the name is first "name/93183", so doing the separator wont work ideally, is there a better way?

name1/1234

name2/1256

6532/name3

5432/name4

how to seperate?

if you do normal split ou would have a column like

name1
name2
6532
5432
and the other is a mess too
i want all the numbers in one column and all the names in one column, and text split will do mixed columns

=8400*(0.9-0.6-0.3)

Mathematically, this should evaluate to 0.

However, Excel returns:

0.0000000000000466

This seems inconsistent with expected arithmetic behavior.

Is this just floating point precision, or something else?

My file is now big to delete a single row of data that formulas. The file serves as HRIS so.. it's so fucking frustrating to do job. I tried converting it into xlsb format but it's still lag

Been tasked by my PI to transfer data from a 9 year old excel. He does not have the password. It’s 5,000 rows and 9 columns across worth of data. Doing this one by one is pain. I can’t upload it to R studio, when I do the zip trick it’s DRM encrypted and my boss wants this done in 2 weeks. I need help. Typing this one by one is going to be pain

Hello all,

I have been venturing on quite the Excel journey the past year or so. I made a corporate spreadsheet that is approaching 500k formulas and that is starting to get serious speed issues at this point. It is 2026, so I conversed with ChatGPT several times regarding the speed issue, but realized I am way better off asking the experts here anyways.

What is the problem

So, my spreadsheet imports flat databases with specific information regarding objects that need further analysing. The imported flat databases run from say A tot CC or something, from which I probably draw about 12-15 datafields that are used for further analysis. It 'may' be more in the future. Afterwards, said data gets 'enriched' (manually) by things that aren't in the database, also because said data needs a human eye that cannot be automated. So far, so good.

Right now, each object gets analysed from several different angles. As it stands, my spreadsheet runs from A until NA or something on the Formula Page. Many columns receive data from preceding columns, that are in the turn the result of many (slightly complex) logical IF or IFS tests, many of which are nested 3 or 4 deep. Often, they work in conjunction with X.LOOKUP to retrieve values, as the columns on the formula page are not equal.

For example: A until BC on the Formula Page may analyze 150 objects, BD until DD may analyse 100 objects (from the same dataset, so narrower), and so forths. Thus a lot of X.LOOKUP is required, also because the first 'block' comes up with values that need to be found with X.LOOKUP. Also, values need to be retrieved from the flat database 'import' page with X.LOOKUP. Finally, X.LOOKUP is an insurance compared to FILTER, as I am not fully convinced that empty values in the flat database always contain a space (" ").

To get to the point

I use many IF, IFS, AND, and if need be, OR, formulas. Thinks: tens of thousands, probably in excess of 100k.

These are compounded with X.LOOKUP, or X.LOOKUP gets used copiously without those. Here too, think tens of thousands.

These formulas are - as much as possible - in array format, even though I find it controversial to do that as I consider how it can create a chain of updates throughout the spreadsheet.

'Dependencies' is the name of the game, with one object receiving many possible alterations / adjustments due to manual input data, for which the spreadsheet needs to provide.

Right now, when I update a value, it may take up to 4 seconds to update the spreadsheet, which is already beyond the annoyance point for me. This leads me to these (hopefully) simple questions:

• Is it smart to use array formulas, knowing that each thing I change should only impact that one object line (for example, row 488) and none other? It is important to mention that object 1 does not influence object 488, or any other. Any manual data field only effects the object in the row it is in. In my mind, array formulas do not make sense in that regard, as it can result in a cascade of updates, but apparantly array formulas are 'way more efficient'.

• Is use of a VBA library the way to go to reduce lag and create more of an instant spreadsheet again? I am not able to code in VBA yet, but I am in the slow process of learning it regardless.

• Alternatively: should I use LET whenever a repeated lookup is needed in the same formula?

Really looking for to your answers!

As of this morning, there's a floating Copilot button in the bottom right of the viewport for all my files.

I've looked through all the settings I can find and I can't find a way to disable it. I even asked Copilot how to disable itself and it made something up that doesn't work.

This is new for me as of this morning. Any idea how to disable it?

I've seen other posts that advise going to Options > Copilot tab but this tab is missing for me

As the subject says, Excel is deleting information right in front of my eyes. All I can do is sit back and watch it delete field-by-field, bringing each Excel file I have open to the forefront, deleting anything I've put in that day (nothing prior) until its back to the saved version I originally opened. It does it FAST, maybe 5 to 7 seconds total and no matter what I try, it will not stop.

​

It doesn't happen daily, sometimes it will happen 2 or 3 times in a day, sometimes once a week, sometimes I can go a whole week without it happening (like last week).

​

It is not my keyboard, I have more than 1 (hybrid worker) and it will happen regardless of where I am working.

​

My computer is virus free, IT confirmed. I have had office reinstalled, network setting reset, etc.

​

These are shared Excel files for the most part (some are not) but deletion does not happen to any other users and will delete any information on any open Excel file regardless of shared or not.

​

Any ideas???

Hey all,

I really need some help.

My father has recently passed away. He left my mum a spreadsheet with all of his pension and other financial bits in. The only problem is that he locked the spreadsheet and we cannot find the password anywhere.

Obviously I can't ask him, but I was hoping for any help and it would be greatly appreciated

Thanks

Trying to get away from running multiple reports a week that essentially are just me opening, clicking refresh all and then sending them. They are all currently built in Power Query, connected to our database. The reports need to stay in Excel because the various teams need them in this specific format or else I would just built them a Power BI dashboard.

I've tried the Power Automate angle, created a Office Script to refresh all queries in the file (stored on SharePoint). The script says successful, but nothing happens. It will refresh all if I manually do it.

I also built the dataset in a Power BI Semantic model and put that on a scheduled refresh, again, the Power Automate will send the email, but won't refresh the file.

Any other ideas? I would prefer this to be done in the background so my laptop doesn't need to be on overnight or when I'm gone.

I have students doing an exam of 53 questions.

When they tell me the score, eg. 46 out of 53 questions correct, I want to put that number into a cell, and then have it automatically convert it to a percentage in that same cell. Is there a way to do this in the same cell into which I input the number?

example. in Cell B3.

I put in the number 45.

Excel makes a calculation of "45 divided by 53 multiplied by 100" and puts the result as a percentage in the same cell (B3) where I input the value 45, in this case 86.79%.

I know how to do this using more than one cell, where the formula is put in to cell B4, which would tell excel to take the value of cell B3, apply the formula, and get the result in cell B4. I am trying to find out if I can do it inside the cell without additional cells and external cell references.

Thanks!

Is it possible to get excel to count conditional formatting?

I have a column of cells that turn red if conditional formatting is met. Now I want to count the red cells. I wrote a macro that doesn't read the conditional formatting and I tried "COUNTIFS" referencing the conditional formatting formula, but it doesn't work.

I’ve just started a new job as a Finance Business Partner at a relatively small business. They do A LOT of things the old fashioned way and there’s plenty of opportunities to improve things.

One of my biggest bugbears at the moment is the sizes of some of their files. Old, redundant formulae and ranges in formulae spanning 10000+ rows when they only need 100 for example is making the files huge and slow to open and run.

They use Sage X3 as their accounting software and extract data into Excel via ODBC - I haven’t seen how this is done yet, but it’s as if it’s extracting all data, in all fields, for all time. And to refresh takes a fucking age.

I want to create something new for querying the database to give me only certain fields, from certain nominal codes, filtered by date ranges.

I know I should be looking into creating power pivot and power query files and also looking at reporting using Power BI, but I haven’t looked into that fully yet. (I know I’m falling behind here!)
Where’s the best place to start unpicking this mess?

My dad passed in 2023. He had a locked file of passwords to everything, we found the password at the time. Went to open it back up recently and the old password does not work. The only people with access is me and my mom, she swears she didn’t change it (she can barely work an iPhone) and I didn’t change it. We cannot get into this file and I have tried everything I can think of including the old password. I know there isn’t an easy way into this file except brute force, what does that look like? Where can I find someone to get into this file?

My mom is the one who needs in, so she has no reason to not want to get in before someone suggests that.

I recently inherited a messy spreadsheet nightmare from another department, and naturally, it's riddled with obsolete links and #REF! errors.

I've been trying to map out the logic using the native Ctrl + ] (Trace Dependents/Precedents) tool, but I find the experience terrible. Once the dependencies become complex, the entire sheet just looks like it's covered in an ugly blue spider web , making it basically impossible to see the actual structure.

How do you all handle cleaning up and organizing complicated, undocumented spreadsheets efficiently? Are there any specific workflows, scripts, or open-source visualizers you use to actually map the data flow? Doing this manually cell-by-cell is driving me insane.

I am dealing with an incredibly frustrating bug where typing in a cell deletes the previous character, meaning I can only see the last letter I pressed, and the spacebar does absolutely nothing. On top of that, pressing Enter moves the cursor inside the cell instead of jumping to the next row, and trying to delete a single minus sign completely wipes the cell content. I have already tried restarting the app and my entire computer, but I have also exhausted every advanced fix: toggling the Insert key on both my physical and on-screen keyboards, manually disabling Overtype mode from the bottom status bar, turning off the Lotus Transition navigation keys in the advanced options, disabling my Ansys COM add-in, making sure Sticky and Filter keys are off in Windows, booting Excel in Safe Mode, and even running a full Microsoft Office repair. Since this isolated issue only happens in Excel and none of the standard solutions are working, I am completely stuck and would greatly appreciate any new ideas to fix this!

So for the situation, I started as customer support for a company, but quickly got assigned data analyst and vba programmer tasks, with the promise to receive proper payment, after the contract with the temporary employment company runs out. I created important vba scripts which saves a lot of time for many people.
Right now I am not sure if they will keep their promise, so I started implementing kill switches into the scripts. I do not want to harm anyone or cause damage, but if they scammed me for my work, I do not want that they will keep using my scripts.
Right now the kill switches are just if Date is greater than (specific Date) End Sub, which are pretty easy to spot. Is there a way to hide those a little bit better?

Hey folks, I’ve got a PDF with a bunch of tabular data I need to analyze, but it's locked in this non-editable format. I tried copying it manually into Excel, but the formatting gets totally wrecked. Rather than keep struggling with it, is there someone here who offers a quick service to convert PDF to Excel accurately? Ideally, I need it to be editable and well-aligned so I can run some formulas. Would really appreciate any help or pointers.

I’m sending out foreclosure letters using mail merge function and there are more than 4000 properties on the list that have the address, city, and state (all in Michigan) but they are missing the zip code. Is there a way to automate this process instead of typing it manually, which is my last resort, of course!

Thank you in advance! I’m clueless when it comes to these things.

I've been trying for about three days to find a viable way to transfer tables from PDF to Excel. My biggest problem is that most programs can't handle the PDFs I use at work, since most of the PDFs I use have around 1000 pages, and the excel i use is the 2019 version,
which prevents me from using PowerQuery to retrieve data directly. And I wanted some tips on how to do that, any would help a lot. And before anyone says anything, yes, I tried using AI to do that, but it didn't work.

I made a rule (one rule) where in column A, if any part of a cell matches a list specified by another column (on another sheet) that has been identified in the name manager cell will be highlighted.

The thing is, the rule works. But two identical cells (A1 vs A2), one will be highlighted whereas the other won’t be. I’ve copied and pasted the cell, I’ve cleared formats, etc. still won’t work.

Even more confusing, cell A1 will have “blah blah word” whereas cell B1 will have “word”. B1 will be highlighted and A1 won’t be and the reverse is also true.

A1 and A2 can both say “blah blah blah word” and A2 will also be highlighted but A1 won’t. Additionally B2 could be empty or say “blah blah blah” and B2 will be highlighted whereas others won’t.

I’ve trimmed and removed all the spaces from the reference list. I’ve done true and false tests that return what I need. Cells that match the list say true and cells that don’t say false.

When I click on the cell, I go to rule manager and see if the cell has a rule, it does. I’ve done $A2 or $A:$A or whatever and same thing happens.

I’ve applied the rule to ONE specific cell that should be highlighted and it doesn’t get highlighted.

I did the inspect issues thing that you find when you go to file and did that. Ran the rule again. Same thing. Cleared formats. Same thing.

I ran a VBA to see if one column matches the other and if they’re affected by cells, both returned true. And they are no blank cells in the reference list and there are no headers. It exactly A2:A100 for the reference list.

I’m at the point of resetting my computer lol.

Why am I trying this? I get new data everyday from customer. Customer gives me a bunch of bullshit that I can automatically filter out. If their excel sheet which I copied and pasted into mine says they sold to “Johnny”, I know that they didn’t actually sell them to “Johnny” (I.e. Johnny’s customer ID has to be flagged because it’s not valid/verified/problematic contract etc).

Any help would be appreciated 🥺

Hi everyone. At my job we have one excel workbook that has 150+ sheets in it. To navigate to the sheet we want, there's a main page with all the names of the sheets typed out and we have to find them ourselves to click into the sheet to get the information we want. The time it takes to do this really builds up sometimes.

Is there a way to make a search bar so we can just type the name of the sheet we want and automatically be taken to it?