My current role is at a large company, and a significant portion of my work is getting Excel reports which are broken and fixing them. Most I've never seen working. Some reports have been broken for years but need to be resurrected.
To add a couple more:
- Use actual tables. (Select data+headers, Insert -> Tables -> Table). In most cases the data is tabular but not formatted as a table. Making this one change makes the formulas self-documenting, and rather than selecting rows 2:5000, it will automatically accommodate more data as it's added. Tables can also be named (click in the table, Table Tools [Design] -> Properties -> Table Name). E.g. tbl_sales. This allows nice formulas like
=SUM(tbl_sales[Sales])
or
=SUMIF(tbl_sales[Region],"West",tbl_sales[Sales])
At a certain point, it really needs to be SQL, but between this and pivot tables you can greatly extend the time before a database is needed.
- Add a sheet with instructions. Explain hiccups which may be encountered, and how to fix them.
- Add comments to VBA. There is about 1 person at my facility (other than me) who adds any comments at all. Most use the Macro Recorder then add in the loops and logic as needed. Makes it really tough to figure out.
- Protect cells which hold calculations. I recently saw a spreadsheet which has been giving sub-optimal output for years because the ranges used in a function were messed up and copied down every week.
- Use the built-in styles (Home -> Styles). Same goes for Word (Heading 1, Heading 2, etc.). It makes spreadsheets way more straightforward to read if there are some cells using Explanatory, Input, and Note styles, and it's less abrasive to read. I particularly like the Input style because you can tell users "You'll only need to update the orange cells".
- Use multi-line formulas. Alt+Enter while editing a cell allows multiple lines on a single formula. You can also improve the readability by adding four spaces at the start of the new lines. E.g.
To add a couple more:
- Use actual tables. (Select data+headers, Insert -> Tables -> Table). In most cases the data is tabular but not formatted as a table. Making this one change makes the formulas self-documenting, and rather than selecting rows 2:5000, it will automatically accommodate more data as it's added. Tables can also be named (click in the table, Table Tools [Design] -> Properties -> Table Name). E.g. tbl_sales. This allows nice formulas like
or At a certain point, it really needs to be SQL, but between this and pivot tables you can greatly extend the time before a database is needed.- Add a sheet with instructions. Explain hiccups which may be encountered, and how to fix them.
- Add comments to VBA. There is about 1 person at my facility (other than me) who adds any comments at all. Most use the Macro Recorder then add in the loops and logic as needed. Makes it really tough to figure out.
- Protect cells which hold calculations. I recently saw a spreadsheet which has been giving sub-optimal output for years because the ranges used in a function were messed up and copied down every week.
- Use the built-in styles (Home -> Styles). Same goes for Word (Heading 1, Heading 2, etc.). It makes spreadsheets way more straightforward to read if there are some cells using Explanatory, Input, and Note styles, and it's less abrasive to read. I particularly like the Input style because you can tell users "You'll only need to update the orange cells".
- Use multi-line formulas. Alt+Enter while editing a cell allows multiple lines on a single formula. You can also improve the readability by adding four spaces at the start of the new lines. E.g.