I somewhat disagree. I work with a lot of excel power users. We have some massive spreadsheets which are collaboratively worked on and do complicated things. The first thing to say about them is that they are very valuable to the business so it is important to be able to do some of the things excel does.
Excel has a lot of advantages compared to regular programming:
- It is quick to change. The programs I work on take nearly an hour to go from code review completion to production, even with manual poking to speed up continuous deployment. It can be valuable to be able to change things quickly.
- In excel the main thing you interact with is the data. If you are a domain expert then you should be able to look at outputs and see if they seem right. When you change a formula or add a column, you are, in some sense, also getting to run it on realistic data instead of needing to try to construct realistic tests.
- There isn’t much difference between config parameters and hard coded values. In the programming language I use, you can’t really have globally readable configs so any new parameter must be threaded through from app startup to the place you want to use it, discouraging configuration parameters. Which means it is often slow to change something that ought to have been configurable. In excel you can make a quick cell for some Config parameter (changing a lot of formulas is not so fun though.)
- Functional and declarative, Excel tends to give you internally consistent output. There is less need to worry about incorrect state updates.
- Its maybe better for producing graphs. I never really liked making graphs in excel and I thought the defaults were bad for good data visualisation but then other systems have bad defaults (when I draw a graph I often use GNU Calc with gnuplot…)
- Pivot tables are great for ad-hoc analysis (indeed Excel is pretty good for as-hoc analysis in general.) The pivoting operation is trivial in excel and a big pain to with tools like grep or awk or sed.
These Excel users are generally capable of programming too and may use jupyter notebooks with python or R, or something more fully featured when required. And some things will get outsourced to software engineers, but excel is still clearly useful (so long as it scales) and people don’t just use it because they are desperate for some kind of ‘real’ programming language.
I agree, what I shared was mostly my experience with Excel. We did mostly lots of data transformation, and for that it was far from ideal. But for your use case, it seems great. My father works as a CFO and uses Excel in the way you describe it, and it's really hard to find a better tool for the task. There is another limitation that I forgot to mention: if you are not using Windows, you don't have acces to Pwoer Query and things like that, so for huge datasets, Excel becomes very slow.
To be honest half of these points sound like company process issues (you can make changes quickly with code, just not with this particular review process/CI), and half of them sound like programming-language issues (lots of languages are functional and/or let you make a quick global config parameter)
It’s never so simple. I use a functional programming language but generally in functional programming you don’t want to have global configs but rather to pass functions the things they need. Compilation is mostly fast but there is a lot of code (and we get roughly exponentially faster at building it. The problem is that we also have an exponentially increasing codebase.) Faster deployments could be possible (eg deploying dev builds rather than release builds made in the standard way) but it’s better to have the system work automatically and sensibly in the ordinary case than to try to support quick changes in the extraordinary case. It’s not really a good practice to support deploying random binaries of unknown origin, and certainly not great to do it routinely.) To be clear, excel can have scaling issues too (as well as issues with multiple people trying to change a single sheet) and we ended up recreating some excel-like features in some of our own applications.
Excel has a lot of advantages compared to regular programming:
- It is quick to change. The programs I work on take nearly an hour to go from code review completion to production, even with manual poking to speed up continuous deployment. It can be valuable to be able to change things quickly.
- In excel the main thing you interact with is the data. If you are a domain expert then you should be able to look at outputs and see if they seem right. When you change a formula or add a column, you are, in some sense, also getting to run it on realistic data instead of needing to try to construct realistic tests.
- There isn’t much difference between config parameters and hard coded values. In the programming language I use, you can’t really have globally readable configs so any new parameter must be threaded through from app startup to the place you want to use it, discouraging configuration parameters. Which means it is often slow to change something that ought to have been configurable. In excel you can make a quick cell for some Config parameter (changing a lot of formulas is not so fun though.)
- Functional and declarative, Excel tends to give you internally consistent output. There is less need to worry about incorrect state updates.
- Its maybe better for producing graphs. I never really liked making graphs in excel and I thought the defaults were bad for good data visualisation but then other systems have bad defaults (when I draw a graph I often use GNU Calc with gnuplot…)
- Pivot tables are great for ad-hoc analysis (indeed Excel is pretty good for as-hoc analysis in general.) The pivoting operation is trivial in excel and a big pain to with tools like grep or awk or sed.
These Excel users are generally capable of programming too and may use jupyter notebooks with python or R, or something more fully featured when required. And some things will get outsourced to software engineers, but excel is still clearly useful (so long as it scales) and people don’t just use it because they are desperate for some kind of ‘real’ programming language.