It varies and how you're outgrowing it. I heavily use spreadsheets, but if I start to hit some limits or need to formalize a process I'll switch to something like Python. As a data store I may still query the spreadsheet, or use csv, sqlite, or something that scrapes the data source. I really like columnar data because I like mucking with it by piping data on the command line (which has its own, well known ways to bungle data).
As for reasons you're outgrowing a spreadsheet; access and permissions, friendlier error messages or a better "wizard-like" process, formalizing input data, outgrowing data size, smarter caching for computing values, protection or clarity around modifying "constant" or "magic" numbers. Many of those you could implement in a spreadsheet, but its often more complex than using something else. That something else probably varies with which problem you're trying to solve.
You would have the script take in the data as input from somewhere else, maybe your organizations shared drive. could be a .csv or whatever format. could be a database like sqlite. Much better than some big hulking macro heavy excel workbook.
I think you raise a good point that often gets forgotten. When you switch from a general purpose tool to something specific, you lose visibility and introspection tools.
I see people move from a filesystem based workflow to a database and while that may be faster and more efficient you lose those tools to look at and tweak data. You either need to write those or teach them how to navigate the database. People rightfully hate black-boxes as soon as stuff stops working as expected.
Personally, I love the Linux command line and will pipe stuff in and out or stash things in flat files. I may move the data back into a spreadsheet to get more visibility or look for trends.
Python or R have tons of ways to edit, extend, and quickly filter and search within stuff like a CSV. You could even do all of that with a bash script. It will probably run a whole lot faster on your computer than opening the excel workbook.
I think we might be talking at cross purposes. How, in Python, do I extend a CSV with new numbers, or edit existing values? Use 'input'? I could implement an excel-like interface, but then I already have Excel.
I could just open it in a text editor I suppose, but then I have to deal with escaping, and moving around cells, and that sounds painful.
Plus 100 for pandas. It is a truly amazing library. If I need to do some exploratory data analysis with it I'll often do so in a Jupyter notebook, but always make sure serious coding happens in a real editor like vscode.
But things really are hopeless.
Even Microsoft can't control the monster they unleashed on the world. Execs and managers using Excel instead of Planner for project tracking ("Spreadsheets are for manipulating numbers, not word processing!"). Used in that way, Excel isn't so fragile, but why is it being used at all? For f*cks sake, hasn't anyone ever heard of Word tables?
Honestly, anyone who uses Excel as a database should be exiled to... someplace where they can't do any more damage. Maybe connectivity-poor rural Arkansas. That's technological malpractice. It's indefensible, and an embarrassment to the whole tech sector that anyone is trying to excuse it.
You would read in the csv, edit what you need or add/remove data, then write a new file or overwrite the old one. You don't need to implement any interface, there are tools that do all of this. You could probably write the code up for something simple like this in the time it takes for excel to open the file and you to make the change manually by finding the field, clicking away with the mouse, and typing it into the cell. Specifics beyond that can be searched for on the internet as there are a wide variety of methods you could tailor to your purpose.
