21–30 of 193 posts
Fear the excel less world, for without it society is a shadow of its potential!
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.
The other problem is specific to Excel. It just loves to mangle your data! The problem is so bad that geneticists have renamed some genes to stop Excel reinterpreting them as dates.
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 ste…
Matt Parker's "When Spreadsheets Attack" video about EUSpRig https://www.youtube.com/watch?v=yb2zkxHDfUE
Earlier quoted context omitted.
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.
This is why business is not considered a form of engineering, while software is (just barely). Imprecision is a tool in business, while in engineering it is an enemy.
Earlier quoted context omitted.
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).
In some cases, data validation[1] on the cells directly can help. Particularly if the data is going to be manually entered by the end user, rather than a bulk copy/paste job from elsewhere.
In other cases, you apply defensive checks within the PowerQuery processing. If a field is supposed to contain US-formatted phone numbers, you can explicitly set the column type to text, split at dashes and keep the first part, verify it only contains numerical content, optionally check for non-sense values (all zeros, all nines, 12345, etc), then left pad it with zeros to ensure they're all 5 digits. You can also package up all of those steps into a function that can easily be added into future worksheets via copy/paste and save you from re-creating the wheel. And depending on the data itself, it may be handy to call out to a third-party API for validation/normalization.
To maintain sanity, you can also create a helper column for validation. If any of the columns can't be "safely" normalized or validated, you update this field with details on what failed. At the end, split out any records where that column is not null into its own table. That way you both filter those records out of the "final" table, and can also easily add in an Exceptions Report section to the spreadsheet, clearly calling out the rows that had issues and providing the error details to the end user.
It sounds complicated, and it is compared to the alternative (Excel's undefined behavior silently swallowing it). But it's actually incredibly straightforward to implement and a fairly natural design pattern for someone coming from a software development background. You just start treating Excel as a UI and PowerQuery as a backend. All of the business/processing logic gets decoupled into PowerQuery, and formulas become primarily tools for presentation logic (such as adding an an Exceptions notice to the primary worksheet if COUNT(EXCEPTION_TABLE)>0).
[1] https://www.howtoexcel.org/tips-and-tricks/11-awesome-exampl...
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.
One of the big issues with spreadsheets is the lack of visibility. It is very hard to tell what is going on in any non-trivial spreadsheet. That something that visual data flow tools such as Alteryx, Knime and (my own) Easy Data Transform try to address. The other problem is specific to Excel. It just loves to mangle your data! The problem is so bad that geneticists have renamed some genes to stop Excel reinterpretin…
While I'm not an auditor, I've been the go-to Excel person at all my internships and in full time roles. I've reviewed, enhanced, and fixed hundreds for tons of different functions (HR, supply chain, manufacturing production reporting). You can build a respectable system in Excel which has built in error flags that make it obvious to the end user. Here are some of my tips for people who deal with spreadsheets:
* Cell styles, much like headings in Word, are really great but nobody uses them. They clue end users about what cells do. If my end users see an orange cell, they know it's an input they can modify.
* Lock sheets, even without a password. 90% of the time will prevent people from breaking the spreadsheet in the first case.
* Stay up on new functions! =Unique, filter, sort, Switch, textjoin, and {sum|average|min|max}ifs have been added in the last ~18 months and can greatly clean up messy formulas.
* Document stuff like code should be documented. The Explanatory Text style is excellent for more in depth explanations but not visually clogging things up.
* Use tables. Name the tables. =Average(tbl_Production[Widgets Produced]) is self documenting, and the range expands with the table. Range references are notoriously obtuse.
--
The European Spreadsheet Risks Interest Group has some great information on best practices.
http://www.eusprig.org/best-practice.htm
Joel Spolsky's "You Suck at Excel" is also eye opening for most.
Earlier quoted context omitted.
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 ste…
I tried doing some joins in Power Query and was surprised how slow it is.
If you're pulling data from an external source that supports query folding[1] such as a database, it'll try to push those joins to the original source.
If you're pulling in data from a source that doesn't (or the join occurs after query folding is no longer possible), there are a host of performance considerations/optimizations.
By default PowerQuery knows nothing about your source data and makes no assumptions. If you're joining data using the GUI, the generated code will likely use the NestedJoin[2] function. A single "step" in the processing will have its ram capped at 256MB[3], so depending on the size of the data you're joining and absent of any data processing steps that would give it guarantees about your data, you may or may not be paging massive amounts of data to disk as it loops through the entire dataset you're joining for each row that is being joined.
That said, PowerQuery has 6-7 join algorithms available, and a variety of techniques that can be used to optimize the processing. For example, if you add a primary key to the table (via Table.AddKey or Table.Distinct or Table.Group), it can short-circuit the data processing the moment it finds a match. This[4] article series is really helpful for optimizing joins in particular and the link-outs and tidbits scattered within the series are fantastic for getting a better understanding of PowerQuery's inner workings and performance considerations.
[1] https://docs.microsoft.com/en-us/power-query/power-query-fol...
[2] https://docs.microsoft.com/en-us/powerquery-m/table-nestedjo...
[3] Mentioned halfway down this article: https://blog.crossjoin.co.uk/2019/04/21/power-bi-dataflow-co...
[4] https://blog.crossjoin.co.uk/2020/05/31/optimising-the-perfo...
Excel suffers from being accessible to "everyone", and having a low barrier of entry. While I'm not an auditor, I've been the go-to Excel person at all my internships and in full time roles. I've reviewed, enhanced, and fixed hundreds for tons of different functions (HR, supply chain, manufacturing production reporting). You can build a respectable system in Excel which has built in error flags that make it obvious t…
I call this "baby with a nailgun syndrome". You end up with a bloody mess.