4 ms·

Teach people how to use excel properly: - treat inputs and outputs separately - always use simple tables for input (no breaks, first lines are titles, basic v

Teach people how to use excel properly: - treat inputs and outputs separately - always use simple tables for input (no breaks, first lines are titles, basic vlookups for relationships, etc.) - use pivot tables to summarize the tables, and build reports on top of the pivot tables. EDIT: BTW, on a personal level, I've moved to CSV/Jupyter Notebooks combo, but I have no hope that my fellow non-tech workers will do the same...

marcosdumay 9y ago
- finally, pray that Excel won't corrupt your data (be very wary of dates, texts only composed of digits, and different decimal separators)
tcarn 9y ago
I agree. A lot of these tools are coming about because people don't understand how to use excel and frequently Tableau/Power BI is not a better alternative, unless you have no clue about Excel.
froindt 9y ago
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. =SUMIFS(tbl_sales[Total_Sales], tbl_sales[Month], "Jan", tbl_sales[Region], "West")

marcosdumay 9y ago

- finally, pray that Excel won't corrupt your data (be very wary of dates, texts only composed of digits, and different decimal separators)

tcarn 9y ago

I agree. A lot of these tools are coming about because people don't understand how to use excel and frequently Tableau/Power BI is not a better alternative, unless you have no clue about Excel.

froindt 9y ago

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. =SUMIFS(tbl_sales[Total_Sales], tbl_sales[Month], "Jan", tbl_sales[Region], "West")