7 ms·

Spreadsheets are a hammer so if that's all you know then all problems look like the nail. Really, many power users of Excel ought to be looking at more capable,

Spreadsheets are a hammer so if that's all you know then all problems look like the nail. Really, many power users of Excel ought to be looking at more capable, testable and readable solutions from professional data analysts and scientists. Python would be a good start, but there are many options better than Excel for critical systems.

Closi 5y ago
> Really, many power users of Excel ought to be looking at more capable, testable and readable solutions from professional data analysts and scientists. Python would be a good start. I can write python, but I can get the answer out of excel in a fraction of the time for most use cases. As an added bonus, excel documents are far more ubiquitous and I can share them with clients, and they can see my workings. Python isn’t very transparent and business users can’t usually check the logic. Excel is also much more interactive and allows for much more “discovery” and playing with the data. What I would say though, is most power users don’t know about functionality in excel like PowerQuery, relational data models and DAX that actually do turn it into a serious and repeatable tool. (I’m not arguing that Python isn’t better for many use cases, and excel definitely isn’t right for most critical applications, but I also think Python is much slower for ad-hoc data analysis so both have different places in the market. Anyone think python is faster? I’m down for a race!).
- codesnik 5y ago
  > they can see my workings How? Do they check that every cell in a column actually has the same formula? Do they check data format everywhere? Python script is something that is possible to be actually reviewed, and results - reproduced, and "formula's" there are actually readable. But for some stupid reason excel files are still shared over email.
  - eropple 5y ago
    It's only "stupid" if one suffers from a catastrophic lack of empathy for people who are not programmers and for the incentives to which they are exposed--which do not include using a software developer's preferred tools, nor do they include the carving out of time with which to learn them. I would hope that we would be generally wiser than that here.
    - codesnik 5y ago
      Like being a "programmer" is some genetic trait or something, or like using excel in any more or less productive manner doesn't require carving out hell a lot of time, it's just that time is taken from user's lives in small pieces, and doing PROGRAMMING seems like taking a university course. I actually have a lot of empathy for people who forced to deal with all those problems, otherwise I just wouldn't care.
  - Closi 5y ago
    > Do they check that every cell in a column actually has the same formula? They just have to check the top cell as most of my formulas are array formulas. You don’t have to drag a formula down - that’s a common misconception in the latest versions. If you do =A1 + B1 and want to apply it to the 1000 cells below you just write = A1:A1000 + B2:B1000. That’s still not that readable though, so I’ll apply those cells two named ranges “Sales” and “Taxes”. Then the formula is = Sales + Taxes once and that will populate the whole column of data. Then there’s M Code and PowerQuery which literally allows you to review the data cleaning line by line and even see the data state at any intermediary step. It also has > Python script is something that is possible to be actually reviewed The problem for me is that, as someone who works in consulting, it can’t be reviewed by my boss or a client, neither of whom can program. But they can review a tidy excel sheet. And then they can’t edit it either, so if I go on holiday and I’ve built some sort of model nobody else can make progress until I come back, or if I move projects I’m also stuck maintaining the model on the old one.
    - LordEthano 5y ago
      That's terrible practice, ironically, as it's extremely unreadable. How could someone looking at it know what Sales and Tax actually are? You have to go into the formula name box and dig in to find sales = "yada yada" etc. That doesn't seem too bad until you have a decently sized file and you have to dig into 40 formulas to find the one you want, and go check that it's actually referencing what you want. I work as a banker, and what you do is one of the very first things new employees are taught not to do in excel. It's an amazing solution for the person that built it, but a terrible one for anyone looking to check the work.
      - Closi 5y ago
        > That's terrible practice, ironically. Can you point me to the 'best practice' guide you are referring to? What authority on excel standards said this? Personally I tried to find articles saying it's not best practice by typing in "dont use named ranges best practice" or "named ranges in excel are bad" into google, but it mostly brings up articles stating that using named ranges is best practice and improves readability! > How could someone looking at it know what Sales and Tax actually are? If you really want to use that example, you would click in the formula bar and it will highlight the ranges, and colour code them automatically. If it's on a different sheet, you just hit ctrl + g and type in the name, and it will take you directly to the cell it's linked to (which usually in my case, is linked to a sheet that contains all my model's assumptions in one place, each one with a named range describing what it is). It's much easier and quicker than going to =Assumptions!G52. > I work as a banker, and what you do is one of the very first things new employees are taught not to do in excel. Seems like a silly thing to teach people IMO. In my experience it makes formulas much more readable (both writer and reader), makes it much faster to build models, and cuts down errors substantially. I personally find that formulas are much easier to review, because the named ranges provide some intent. If someone writes =(A2Assumptions!92)/Assumptions!91 I've got to really unpick it to work out if it's right, but if someone labels it =(A2Miles_Per_Hour)/Average_Miles_Per_Vehicle then I can see that the formula is wrong almost instantly. Additionally if I want to write another formula using those values, I can just type it straight into the formula bar without having to go and click on the right cell reference in another sheet.
chasd00 5y ago
what would python be working on then? a .csv, textfile, database, something else? Or would the data be stored as part of the script itself?
- anthk 5y ago
  TSV, you have several tools to parse it. Even AWK.
- asdff 5y ago
  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.
  - CJefferson 5y ago
    Then how do I edit, extend and quickly filter and search that CSV? I'd probably do it with Excel to be honest. Lots of data in business isn't generated by another program, it's built and maintained by users.
    - asdff 5y ago
      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.
      - CJefferson 5y ago
        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.
vxNsr 5y ago
Excel is more than just a hammer it’s the fork that is in every single drawer of every single company/org in the world. It’s not about every problem looking like a nail, it’s that, nobody had to pay for this fork, somehow everyone has it and it’s such a great fork that it can do 90% of the problems you throw at it. Yea sure some problems would be better solved with specialized tools, (eg knife, spoon, etc) but those require training and budget. No one has those. Everyone has a fork.
- pault 5y ago
  Cutting steak with a fork is a pretty good metaphor for working with a spreadsheet heavy enterprise team.
- gizmo686 5y ago
  > nobody had to pay for this fork Actually, everyone had to pay for that fork. Excel is a paid product, and isn't even included in the cost of Windows. It is, however, bundled with MS Office, which includes Word, which is even more of a must have at almost every single company/org.
  - vxNsr 5y ago
    I know very few people who have paid for excel specifically. everyone pays for MS office, albeit because it includes excel, but in an workplace setting the main thing you're paying for is outlook/exchange the fact that word/excel/powerpoint are bundled is a plus. The main point I'm making is that no one has to go to their manager and justify paying for it. It's already bought and paid for, and because of its ubiquity most people have a pretty good idea of how to use it.
    - pjmlp 5y ago
      Even better, because some of those deals include MSDN, when Excel users grow beyond VBA, they just get VB.NET installed and keep doing AddOns in .NET.
  - lazide 5y ago
Xavdidtheshadow 5y ago
Spreadsheets are great. I just wish folks would use a more appropriate tool for what they really want: a very light-touch database. Something like Airtable is perfect for structuring a bit of data into rows and columns. But, nothing beats the availability (both of sharing and editing) of an open Google Sheet.
- gauchojs 5y ago
  And then there's the pricing.. "We already have Google". No way my company would pay for Airtable, Notion, etc Its spreadsheets all the way.
dan-robertson 5y ago
Many power users of excel do use more capable, testable and readable solutions. But they also use excel. Programming sucks in many ways that excel does not. Excel is incredibly flexible and adaptable. If you need to treat one row differently as a special case that is easy in excel but can be a lot of work with programming, both for technical reasons (you need to handle the special case with special code) and for silly reasons (in excel you click the row which is special; in a program you must describe how to find the row.) Certainly there is an argument that one shouldn’t have such special cases as they make maintainability a nightmare but sometimes it matters a lot more to have something now rather than something later. In excel, the data is the most important thing and in the real world it often is too. In a program, the data (especially the intermediate data) is quite invisible. Power users may spot errors because intermediate values look wrong, rather than by carefully reading the formulas. Though if the values look right or there are a lot of them, errors may not be spotted. Excel is very ad-hoc which is the problem people complain about but also it’s power in dealing with the changing real world and anything exploratory. Once it is working and stable in excel, it is more wise to do it in python.
tanin 5y ago
Shameless plug: I'm building a Desktop app that serves the niche between Excel and Python. It enables you to load CSVs and write SQL on those CSVs. The main benefit is that it is very fast when compared to Excel or Python. Excel cannot really handle 1GB CSV file. With Python, just loading 1GB file and iterating through every line probably already takes more than a minutes. Superintendent pushes most logic to C and Sqlite, so it's super fast. Here's the app: https://superintendent.app https://superintendent.app
lightbendover 5y ago
How many professional construction sites do not have a supply of hammers? I think your analogy actually fits pretty well seeing as how Excel is an invaluable tool that fits into a larger enterprise.
kumarvvr 5y ago
I disagree. Spreadsheets are like a Swiss Army Knife. I have used them for - Templates for Printing - Data Analysis - Financial Tracking - Report Generation - Calculation sheet for engineering stuff and a multitude more uses that I cannot even remember now. They are not a hammer, though that is one form in which they can work. Ultimately they are a tool, and a damn fine tool at that. Their potential is limited only by the creativity of the user using them.
6gvONxR4sf7o 5y ago
Spreadsheets are sometimes more readable than code. It’s harder for certain kinds of bugs to get through when you see the whole program, inputs, and outputs, and all intermediate states at once.
lordnacho 5y ago
I think of Excel as the modern back of the envelope. You can prototype some things, proof of concept etc. But if you're running things in prod via excel you haven't actually thought enough about it to call it prod.

Closi 5y ago

> Really, many power users of Excel ought to be looking at more capable, testable and readable solutions from professional data analysts and scientists. Python would be a good start. I can write python, but I can get the answer out of excel in a fraction of the time for most use cases. As an added bonus, excel documents are far more ubiquitous and I can share them with clients, and they can see my workings. Python isn’t very transparent and business users can’t usually check the logic. Excel is also much more interactive and allows for much more “discovery” and playing with the data. What I would say though, is most power users don’t know about functionality in excel like PowerQuery, relational data models and DAX that actually do turn it into a serious and repeatable tool. (I’m not arguing that Python isn’t better for many use cases, and excel definitely isn’t right for most critical applications, but I also think Python is much slower for ad-hoc data analysis so both have different places in the market. Anyone think python is faster? I’m down for a race!).

codesnik 5y ago
> they can see my workings How? Do they check that every cell in a column actually has the same formula? Do they check data format everywhere? Python script is something that is possible to be actually reviewed, and results - reproduced, and "formula's" there are actually readable. But for some stupid reason excel files are still shared over email.
- eropple 5y ago
  It's only "stupid" if one suffers from a catastrophic lack of empathy for people who are not programmers and for the incentives to which they are exposed--which do not include using a software developer's preferred tools, nor do they include the carving out of time with which to learn them. I would hope that we would be generally wiser than that here.
  - codesnik 5y ago
    Like being a "programmer" is some genetic trait or something, or like using excel in any more or less productive manner doesn't require carving out hell a lot of time, it's just that time is taken from user's lives in small pieces, and doing PROGRAMMING seems like taking a university course. I actually have a lot of empathy for people who forced to deal with all those problems, otherwise I just wouldn't care.
- Closi 5y ago
  > Do they check that every cell in a column actually has the same formula? They just have to check the top cell as most of my formulas are array formulas. You don’t have to drag a formula down - that’s a common misconception in the latest versions. If you do =A1 + B1 and want to apply it to the 1000 cells below you just write = A1:A1000 + B2:B1000. That’s still not that readable though, so I’ll apply those cells two named ranges “Sales” and “Taxes”. Then the formula is = Sales + Taxes once and that will populate the whole column of data. Then there’s M Code and PowerQuery which literally allows you to review the data cleaning line by line and even see the data state at any intermediary step. It also has > Python script is something that is possible to be actually reviewed The problem for me is that, as someone who works in consulting, it can’t be reviewed by my boss or a client, neither of whom can program. But they can review a tidy excel sheet. And then they can’t edit it either, so if I go on holiday and I’ve built some sort of model nobody else can make progress until I come back, or if I move projects I’m also stuck maintaining the model on the old one.
  - LordEthano 5y ago
    That's terrible practice, ironically, as it's extremely unreadable. How could someone looking at it know what Sales and Tax actually are? You have to go into the formula name box and dig in to find sales = "yada yada" etc. That doesn't seem too bad until you have a decently sized file and you have to dig into 40 formulas to find the one you want, and go check that it's actually referencing what you want. I work as a banker, and what you do is one of the very first things new employees are taught not to do in excel. It's an amazing solution for the person that built it, but a terrible one for anyone looking to check the work.
    - Closi 5y ago
      > That's terrible practice, ironically. Can you point me to the 'best practice' guide you are referring to? What authority on excel standards said this? Personally I tried to find articles saying it's not best practice by typing in "dont use named ranges best practice" or "named ranges in excel are bad" into google, but it mostly brings up articles stating that using named ranges is best practice and improves readability! > How could someone looking at it know what Sales and Tax actually are? If you really want to use that example, you would click in the formula bar and it will highlight the ranges, and colour code them automatically. If it's on a different sheet, you just hit ctrl + g and type in the name, and it will take you directly to the cell it's linked to (which usually in my case, is linked to a sheet that contains all my model's assumptions in one place, each one with a named range describing what it is). It's much easier and quicker than going to =Assumptions!G52. > I work as a banker, and what you do is one of the very first things new employees are taught not to do in excel. Seems like a silly thing to teach people IMO. In my experience it makes formulas much more readable (both writer and reader), makes it much faster to build models, and cuts down errors substantially. I personally find that formulas are much easier to review, because the named ranges provide some intent. If someone writes =(A2Assumptions!92)/Assumptions!91 I've got to really unpick it to work out if it's right, but if someone labels it =(A2Miles_Per_Hour)/Average_Miles_Per_Vehicle then I can see that the formula is wrong almost instantly. Additionally if I want to write another formula using those values, I can just type it straight into the formula bar without having to go and click on the right cell reference in another sheet.

chasd00 5y ago

what would python be working on then? a .csv, textfile, database, something else? Or would the data be stored as part of the script itself?

anthk 5y ago
TSV, you have several tools to parse it. Even AWK.
asdff 5y ago
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.
- CJefferson 5y ago
  Then how do I edit, extend and quickly filter and search that CSV? I'd probably do it with Excel to be honest. Lots of data in business isn't generated by another program, it's built and maintained by users.
  - asdff 5y ago
    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.
    - CJefferson 5y ago
      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.

vxNsr 5y ago

Excel is more than just a hammer it’s the fork that is in every single drawer of every single company/org in the world. It’s not about every problem looking like a nail, it’s that, nobody had to pay for this fork, somehow everyone has it and it’s such a great fork that it can do 90% of the problems you throw at it. Yea sure some problems would be better solved with specialized tools, (eg knife, spoon, etc) but those require training and budget. No one has those. Everyone has a fork.

pault 5y ago
Cutting steak with a fork is a pretty good metaphor for working with a spreadsheet heavy enterprise team.
gizmo686 5y ago
> nobody had to pay for this fork Actually, everyone had to pay for that fork. Excel is a paid product, and isn't even included in the cost of Windows. It is, however, bundled with MS Office, which includes Word, which is even more of a must have at almost every single company/org.
- vxNsr 5y ago
  I know very few people who have paid for excel specifically. everyone pays for MS office, albeit because it includes excel, but in an workplace setting the main thing you're paying for is outlook/exchange the fact that word/excel/powerpoint are bundled is a plus. The main point I'm making is that no one has to go to their manager and justify paying for it. It's already bought and paid for, and because of its ubiquity most people have a pretty good idea of how to use it.
  - pjmlp 5y ago
    Even better, because some of those deals include MSDN, when Excel users grow beyond VBA, they just get VB.NET installed and keep doing AddOns in .NET.
- lazide 5y ago

Xavdidtheshadow 5y ago

Spreadsheets are great. I just wish folks would use a more appropriate tool for what they really want: a very light-touch database. Something like Airtable is perfect for structuring a bit of data into rows and columns. But, nothing beats the availability (both of sharing and editing) of an open Google Sheet.

gauchojs 5y ago
And then there's the pricing.. "We already have Google". No way my company would pay for Airtable, Notion, etc Its spreadsheets all the way.

dan-robertson 5y ago

Many power users of excel do use more capable, testable and readable solutions. But they also use excel. Programming sucks in many ways that excel does not. Excel is incredibly flexible and adaptable. If you need to treat one row differently as a special case that is easy in excel but can be a lot of work with programming, both for technical reasons (you need to handle the special case with special code) and for silly reasons (in excel you click the row which is special; in a program you must describe how to find the row.) Certainly there is an argument that one shouldn’t have such special cases as they make maintainability a nightmare but sometimes it matters a lot more to have something now rather than something later. In excel, the data is the most important thing and in the real world it often is too. In a program, the data (especially the intermediate data) is quite invisible. Power users may spot errors because intermediate values look wrong, rather than by carefully reading the formulas. Though if the values look right or there are a lot of them, errors may not be spotted. Excel is very ad-hoc which is the problem people complain about but also it’s power in dealing with the changing real world and anything exploratory. Once it is working and stable in excel, it is more wise to do it in python.

tanin 5y ago

Shameless plug: I'm building a Desktop app that serves the niche between Excel and Python. It enables you to load CSVs and write SQL on those CSVs. The main benefit is that it is very fast when compared to Excel or Python. Excel cannot really handle 1GB CSV file. With Python, just loading 1GB file and iterating through every line probably already takes more than a minutes. Superintendent pushes most logic to C and Sqlite, so it's super fast. Here's the app: https://superintendent.app https://superintendent.app

lightbendover 5y ago

How many professional construction sites do not have a supply of hammers? I think your analogy actually fits pretty well seeing as how Excel is an invaluable tool that fits into a larger enterprise.

kumarvvr 5y ago

I disagree. Spreadsheets are like a Swiss Army Knife. I have used them for - Templates for Printing - Data Analysis - Financial Tracking - Report Generation - Calculation sheet for engineering stuff and a multitude more uses that I cannot even remember now. They are not a hammer, though that is one form in which they can work. Ultimately they are a tool, and a damn fine tool at that. Their potential is limited only by the creativity of the user using them.

6gvONxR4sf7o 5y ago

Spreadsheets are sometimes more readable than code. It’s harder for certain kinds of bugs to get through when you see the whole program, inputs, and outputs, and all intermediate states at once.

lordnacho 5y ago

I think of Excel as the modern back of the envelope. You can prototype some things, proof of concept etc. But if you're running things in prod via excel you haven't actually thought enough about it to call it prod.