6 ms·

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 inte

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 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 http://www.eusprig.org/best-practice.htm Joel Spolsky's "You Suck at Excel" is also eye opening for most. https://youtube.com/watch?v=0nbkaYsR94c https://youtube.com/watch?v=0nbkaYsR94c

razakel 6y ago
>Excel suffers from being accessible to "everyone", and having a low barrier of entry. I call this "baby with a nailgun syndrome". You end up with a bloody mess.
- airstrike 6y ago
  Love it. I'm in Excel ~50+ hours / week and will definitely start using this sentence
  - jen729w 6y ago
    Fifty hours a week? You might need to think about working less.
    - qayxc 6y ago
      ...or becoming more efficient - who knows?
      - airstrike 6y ago
        In Excel? Impossible.
    - airstrike 6y ago
      I work this much out of my own volition and I am happy to do so
    - m463 6y ago
      He could be balancing his player stats.
no_wizard 6y ago
I am just completely amazed but yet not surprised there is an entire organization dedicated to spreadsheet risks and best practices. What a world we live in. This just goes to show what the killer app is for computers, it seems. We have these wonderful machines and 80% of people use them for browsing the web/using web apps and producing spreadsheets. Thats the reality I tend to forget sometimes.
- wintorez 6y ago
  Spreadsheet and word processor were THE killer apps of microcomputers. As long as humanity uses letters and numbers, we will have spreadsheet and word processor in some shape and form.
dwaltrip 6y ago
> * Lock sheets, even without a password. 90% of the time will prevent people from breaking the spreadsheet in the first case. Is there any way to lock all cells in sheet except for certain input cells? That seems like it would prevent many errors.
- gegtik 6y ago
  yes. Go to cell properties and you will find there is a "locked cell" property that is on by default. When you Protect a spreadsheet, all Locked Cells become... locked. So a typical use case is to only un-lock the cells you wish people to enter variables into, (color the cell to make it clear), and then protect the sheet so nothing else can be tampered with.
- nhebb 6y ago
  I'm reluctant to pimp my own product on HN, but one of the features of Spreadspeed [1] (my Excel add-in) is a Quick Protect tool which protects the sheet but will unlock individual cells based on styling (e.g., Input style). https://www.breezetree.com/excel-utilities https://www.breezetree.com/excel-utilities
- jen729w 6y ago
  The problem is that someone creates a sheet, locks all the cells, forces a bunch of people to use it via some process, and then either a) never touches it again or b) leaves the organisation and doesn’t tell anyone the unlock password. Example: a lookup list for “hardware models” which is protected. So you can’t add a new model, but some cretin forces you to keep using the sheet. With great power comes great responsibility.
fouc 6y ago
>* 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. It would be interesting if excel had some sort of internal markup language perhaps. So those orange cells could be semantically represented as <input> or some such.

razakel 6y ago

>Excel suffers from being accessible to "everyone", and having a low barrier of entry. I call this "baby with a nailgun syndrome". You end up with a bloody mess.

airstrike 6y ago
Love it. I'm in Excel ~50+ hours / week and will definitely start using this sentence
- jen729w 6y ago
  Fifty hours a week? You might need to think about working less.
  - qayxc 6y ago
    ...or becoming more efficient - who knows?
    - airstrike 6y ago
      In Excel? Impossible.
  - airstrike 6y ago
    I work this much out of my own volition and I am happy to do so
  - m463 6y ago
    He could be balancing his player stats.

no_wizard 6y ago

I am just completely amazed but yet not surprised there is an entire organization dedicated to spreadsheet risks and best practices. What a world we live in. This just goes to show what the killer app is for computers, it seems. We have these wonderful machines and 80% of people use them for browsing the web/using web apps and producing spreadsheets. Thats the reality I tend to forget sometimes.

wintorez 6y ago
Spreadsheet and word processor were THE killer apps of microcomputers. As long as humanity uses letters and numbers, we will have spreadsheet and word processor in some shape and form.

dwaltrip 6y ago

> * Lock sheets, even without a password. 90% of the time will prevent people from breaking the spreadsheet in the first case. Is there any way to lock all cells in sheet except for certain input cells? That seems like it would prevent many errors.

gegtik 6y ago
yes. Go to cell properties and you will find there is a "locked cell" property that is on by default. When you Protect a spreadsheet, all Locked Cells become... locked. So a typical use case is to only un-lock the cells you wish people to enter variables into, (color the cell to make it clear), and then protect the sheet so nothing else can be tampered with.
nhebb 6y ago
I'm reluctant to pimp my own product on HN, but one of the features of Spreadspeed [1] (my Excel add-in) is a Quick Protect tool which protects the sheet but will unlock individual cells based on styling (e.g., Input style). https://www.breezetree.com/excel-utilities https://www.breezetree.com/excel-utilities
jen729w 6y ago
The problem is that someone creates a sheet, locks all the cells, forces a bunch of people to use it via some process, and then either a) never touches it again or b) leaves the organisation and doesn’t tell anyone the unlock password. Example: a lookup list for “hardware models” which is protected. So you can’t add a new model, but some cretin forces you to keep using the sheet. With great power comes great responsibility.

fouc 6y ago

>* 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. It would be interesting if excel had some sort of internal markup language perhaps. So those orange cells could be semantically represented as <input> or some such.