Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Excel warriors who save governments and companies from spreadsheet errors

wired.co.uk

Post reply on HN
Print view View original

11–20 of 193 posts

Re: Excel warriors who save governments and companies from spreadsheet errors

#11

Post by swiley »

claudeganon wrote: post #10
NoPicklez wrote: post #4

I work at a large firm which has shifted itself from using Excel, to significantly promoting data analytics tools such as Alteryx in place of Excel. One of the MAJOR improvements from using Alteryx is that it is easier to audit and review the process. With Excel you can be down the rabbit hole of data manipulation or formula driven outputs and realise you have made a mistake but cannot find where or how. Or cannot fi…

Also, Excel is just buggy enough to screw you over anyway, even if you’re trying to use it safely. I can’t count how many times valid formulas have failed on me across various instances of spreadsheets. Or I have to save, reload, or restart just to make things work. I’m constantly amazed by how much it’s used for business critical functions while being so routinely unreliable.

Most business people don't value and appreciate correct software. It's odd but shouldn't be surprising that most people are unable to effectively evaluate the risk of a software defect, especially in a tool like excel.

Re: Excel warriors who save governments and companies from spreadsheet errors

#12

Post by cosmie »

orthoxerox wrote: post #6

Excel took a step in the right direction when it introduced tables, which autofill formulas for all its rows (seriously, use them!), but it should've made deviations more obvious in them. Right now it's the usual green corner, which is extremely easy to miss in a large data set. This, and explicit column types would prevent maybe 80% of spreadsheet errors.

Technically you do have column types already[1], although only available via the data model[2] (another wholly underutilized feature set).

Although it only helps to an extent, since it doesn't prevent bad data from being input into a table, and supports so many implicit conversions (listed out in [1]) that undesired behavior is still possible. Plus it enters into a little known area of Excel rather than baked into the customary workflows.

That said, it still comes in handy. A typical pattern of mine for worksheets I have to release into the wild (i.e. no telling who will end up (ab)using it and when it'll eventually come back to me in a bastardized form to salvage) is:

1. Create table(s) for raw/user-inputted data

2. Use that table as a source for PowerQuery

3. Leverage PowerQuery for type checking, data normalization validation, and data joining.

4. Output to a new table on the worksheet, referencing this instead of the original table for all formulas.

Also optionally putting complex logic into the PowerQuery steps, that way the "output" table is ready for end-use and doesn't require any complex and error-prone formulas or anything.

Even without leveraging the data model and DAX functions, this works out super well. More sophisticated users can easily modify the PowerQuery steps, and less sophisticated users are protected from accidental mistakes by either using it as-is or reaching out for help earlier on, before there's a crisis for assistance.

[1] https://support.microsoft.com/en-us/office/create-a-data-mod...

[2] https://support.microsoft.com/en-us/office/data-types-in-dat...

Re: Excel warriors who save governments and companies from spreadsheet errors

#13

Post by iagovar »

1123581321 wrote: post #9
NoPicklez wrote: post #4

I work at a large firm which has shifted itself from using Excel, to significantly promoting data analytics tools such as Alteryx in place of Excel. One of the MAJOR improvements from using Alteryx is that it is easier to audit and review the process. With Excel you can be down the rabbit hole of data manipulation or formula driven outputs and realise you have made a mistake but cannot find where or how. Or cannot fi…

Alteryx appears to be considerably more expensive, though. Shouldn’t it take a tool of comparable cost to replace Excel? Especially in self-serve use. I’m not disputing the quality of the tool, to be clear. It seems like a good change and a totally reasonable line item. Just questioning what it will take to actually replace Excel in a widespread way. https://www.alteryx.com/products/platform-details/pricing

The answer to that is Knime. Not as polished, but still way better than Excel.

Re: Excel warriors who save governments and companies from spreadsheet errors

#14

Post by Spooky23 »

NoPicklez wrote: post #4

I work at a large firm which has shifted itself from using Excel, to significantly promoting data analytics tools such as Alteryx in place of Excel. One of the MAJOR improvements from using Alteryx is that it is easier to audit and review the process. With Excel you can be down the rabbit hole of data manipulation or formula driven outputs and realise you have made a mistake but cannot find where or how. Or cannot fi…

Getting away from using excel is something you demand that everyone else do.

Re: Excel warriors who save governments and companies from spreadsheet errors

#15

Post by moomin »

I used to professionally audit spreadsheets that were used to back up million dollar decisions. I say “back up” because it was fairly clear the decisions were made independently. But they needed to tick a box saying they’d had it audited so they paid us for a two day audit. In that limited time, which included writing the report, you’ll appreciate we rarely had time to audit anything more than the top-level summary sheet.

There was always at least one mistake that put the numbers out by 8 digits.

Re: Excel warriors who save governments and companies from spreadsheet errors

#16

Post by cosmie »

NoPicklez wrote: post #4

I work at a large firm which has shifted itself from using Excel, to significantly promoting data analytics tools such as Alteryx in place of Excel. One of the MAJOR improvements from using Alteryx is that it is easier to audit and review the process. With Excel you can be down the rabbit hole of data manipulation or formula driven outputs and realise you have made a mistake but cannot find where or how. Or cannot fi…

Excel has similar capabilities (since Excel 2010) in the form of PowerQuery[1].

It can pull in 3rd party data sources (including custom HTTP calls if you want to reach out to an API), has data types, can both process and store substantially larger data volumes (data is cached/stored separately from the spreadsheet data in a compressed, columnar format and processed by a separate data processing engine), and every step of the data processing pipeline is shown and easily referenced/reviewed (including the ability to leave comments in the code).

That said, PowerQuery is a graft onto Excel, originating in the SQL Server world. So "Excel" skills aren't very helpful/transferable; it leverages Microsoft's M language[2] instead of Excel functions, and the usage paradigm is far more natural for someone with a programming background than an Excel background.

But as someone else mentioned in another comment, Alteryx is anything but cheap. PowerQuery is already widely distributed and available to anyone with Excel installed[3]. So for anyone looking for this type of operational discipline around data, it's a handy tool to pick up. Bonus points that PowerQuery/M-code is portable between a variety of Microsoft tools (SQL Server, PowerBI, Excel, and a variety of Azure's data services).

[1] https://support.microsoft.com/en-us/office/power-query-overv...

[2] https://docs.microsoft.com/en-us/powerquery-m/

[3] Traditionally only available for Windows, but the base tech has recently been added to Excel for Mac and is being brought up to parity.

Re: Excel warriors who save governments and companies from spreadsheet errors

#17

Post by leetrout »

iagovar wrote: post #13
1123581321 wrote: post #9

Earlier quoted context omitted.

Alteryx appears to be considerably more expensive, though. Shouldn’t it take a tool of comparable cost to replace Excel? Especially in self-serve use. I’m not disputing the quality of the tool, to be clear. It seems like a good change and a totally reasonable line item. Just questioning what it will take to actually replace Excel in a widespread way. https://www.alteryx.com/products/platform-details/pricing

The answer to that is Knime. Not as polished, but still way better than Excel.

That is a very interesting tool.

I’m always interested in visual programming tooling. Thanks for sharing.

Re: Excel warriors who save governments and companies from spreadsheet errors

#18

Post by clairity »

cosmie wrote: post #12
orthoxerox wrote: post #6

Excel took a step in the right direction when it introduced tables, which autofill formulas for all its rows (seriously, use them!), but it should've made deviations more obvious in them. Right now it's the usual green corner, which is extremely easy to miss in a large data set. This, and explicit column types would prevent maybe 80% of spreadsheet errors.

Technically you do have column types already[1], although only available via the data model[2] (another wholly underutilized feature set). Although it only helps to an extent, since it doesn't prevent bad data from being input into a table, and supports so many implicit conversions (listed out in [1]) that undesired behavior is still possible. Plus it enters into a little known area of Excel rather than baked into th…

> "1. Create table(s) for raw/user-inputted data"

you can still be exposed to data munging on input/import, due to all sorts of formatting issues (like zip codes losing their leading zeroes or invalid phone numbers due to misaligned formatting).

Re: Excel warriors who save governments and companies from spreadsheet errors

#19

Post by jgilias »

Excel is a great case study. Turns out, if you give people an accessible and widely available Touring complete environment, they will manage to do pretty much anything with it.

On another note, it's interesting to observe that the problems spreadsheets have are the same problems that plague poorly designed software everywhere - lack of documentation, lack of testing, lack of input validation, among others.

Re: Excel warriors who save governments and companies from spreadsheet errors

#20

Post by hermitcrab »

1123581321 wrote: post #9
NoPicklez wrote: post #4

I work at a large firm which has shifted itself from using Excel, to significantly promoting data analytics tools such as Alteryx in place of Excel. One of the MAJOR improvements from using Alteryx is that it is easier to audit and review the process. With Excel you can be down the rabbit hole of data manipulation or formula driven outputs and realise you have made a mistake but cannot find where or how. Or cannot fi…

Alteryx appears to be considerably more expensive, though. Shouldn’t it take a tool of comparable cost to replace Excel? Especially in self-serve use. I’m not disputing the quality of the tool, to be clear. It seems like a good change and a totally reasonable line item. Just questioning what it will take to actually replace Excel in a widespread way. https://www.alteryx.com/products/platform-details/pricing

My own Easy Data Transform software (https://www.easydatatransform.com) does a fair amount of what Alteryx does and is just $99 (one time fee). So cheaper than Excel!
Post reply on HN