Hey, how about checking how fast your Excel Find and Replace gets when you do it?
Hey, how about checking how fast your Excel Find and Replace gets when you do it?
I have a spreadsheet with around 320,000 cells. Those cells hold formulas like = 'R:\Basketball\[Ref Stats v8.xlsx]Analysis Data'!$t1465 I only link to external files on one sheet and all other sheets just use that data so they don't need their own links. It's pretty basic stuff. I am doing a find and replace on this sheet replacing Ref Stats v8.xlsx with Ref Stats v9.xlsx It has been going for 23 hours. When I used excel 2010 it took me six hours. My CPU usage is fine and my temps are okay. Should I worry about a problem, or is this normal behavior of excel 2016 when it takes so long to do find and replace?
It feels like I should stay on Excel 2010 because it's working fine. Why have you seen Excel 2016 acting so sluggish lately?
Hey there, I found a problem with your data setup. It's probably much worse than you think. You need to use a database instead of just Excel. Excel can do some database stuff, but it isn't actually one. The issue you mentioned is common when spreadsheets get too big. 320,000 cells is way too many. To fix this, you should move the data into an Access table or use Excel tables to act as the back end for your database. That might sound hard, but it depends on how messy your current files are. Most of those old "stats" spreadsheets have a lot of rules and complicated parts. These kinds of messes usually cause issues now. What's wrong with the way things are set up? Like, what about the rows and columns, and is everything staying consistent?
I plan to try exporting the file to a .csv format. I will then copy and paste that over to a Word document. It seems like using Word works better for my search and replace tasks than in Excel. Finally, I'll reverse the steps to bring it back into Excel. Just keep in mind, success isn't guaranteed here! -Wolf sends
Sometimes I get lost where I am. I used to think my computer specs were part of my profile, so here are the details: an Intel Core i9-10940X at 4.1GHz, 32GB RAM in a Corsair H115i motherboard running on a W10 1809 LTSC system that's been patched to late 2021. I have two monitors with Office 2016 up to date, a Razer mouse and keyboard, and lots of extra gear like RAM sticks, fans, and switches for internet connections. Most stuff runs on 10Gbit networks.
I got tired of working in Access because it's hard to use now; I can't remember what the problems were back then. It seemed like the data was too big for that old tool, or maybe there were issues with connecting things using ODBC. Instead of trying those methods anymore, I'm making a new suite of spreadsheets. These are complicated calculations taking over nine years to make.
It's getting hard because figuring out how to turn Excel formulas into Cold Fusion code is tricky, even though it might be possible. Right now, we're in a big server shutdown for at least a few months, so I'll need to really work on this stuff soon. You said a single spreadsheet linked to an external file? No, every other sheet just uses that one as its main source, so there are no other outside connections.
I have set up the system to handle 2000 sessions, which is enough for about eleven years of data. In this workbook, each sheet gives different views of the same source data.
I'm going to try it. Thanks. Edit: I actually believe that won't work unless I keep only the numbers in the file and delete the math rules.
I made a mistake back when I started the project in tracking system version 7. Back then, the source sheet was expanded to hold 2000 rows. So versions 7, 8 and 9 all had exactly the same size. From version 7 to 8 it took me about 5 hours on office 2010. From version 8 to 9 it took 30 hours on office 2016. And that huge jump is why I posted here: there was no change in how big the source data got or how its structure looked. Every single column stayed the same size, had the same order and contained the same data types. The only thing I actually changed back in 2018 was to add more rows from 1500 up to 2000. In Excel terms, I changed the formulas from working on a range like $A$5:$A$1500 all the way down to $A$5:$A$2000.
It's not about how we typed it in Excel, but about how the data was built.