3 ms·

What should we use, then? I desperately need some tool I can tell non-tech people to use where they can input arbitrary structured data. After the data is the

What should we use, then? I desperately need some tool I can tell non-tech people to use where they can input arbitrary structured data. After the data is there, in any format the user wants, as long as it is structured somehow, millions of programs can be written to use that data and combine it, but somehow the data has to be written FIRST. It is not feasible to come up with the schema, write a software based on that schema and then give it to the non-tech people.

tallanvor 9y ago
That's a hard question to answer and depends on your size and what you really need. For small companies, Excel may really be all that you need. But at a certain size, you might want to use something like PowerBI instead since it will also pull directly from the database and you can share the dashboards you create very easily. Large companies like the article are referring to might be able to get by with PowerBI (Microsoft would certainly argue there are still plenty of places where it works) or ERP/HR tools more specifically designed for certain uses.
- CurtHagenlocher 9y ago
  The same ETL functionality that's in Power BI is also available in Excel 2010+ as "Power Query".
  - fiatjaf 9y ago
    See also https://www.querystorm.com/ https://www.querystorm.com/
rjtavares 9y ago
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")
drinchev 9y ago
The startup I'm working with right now is building a tool that allows you to create spreadsheet-based business applications. What I like to call the product is "excel on steroids". You can check it at www.ninoxdb.de.
- fiatjaf 9y ago
  This seems like an alternative to Airtable and Fieldbook. Am I wrong? I know these tools and I like them, but this wasn't what I was talking about in my comment. I meant something more flexible and more arbitrary, like a key-value store non-tech people could edit.
yorwba 9y ago
> It is not feasible to come up with the schema, write a software based on that schema and then give it to the non-tech people. Would it be feasible if you only had to write the schema and the software were generated based on the schema?
- fiatjaf 9y ago
  That would be much better. I was working on something like that based on https://postgrest.com/ https://postgrest.com/, but I'm now reconsidering the approach.

tallanvor 9y ago

That's a hard question to answer and depends on your size and what you really need. For small companies, Excel may really be all that you need. But at a certain size, you might want to use something like PowerBI instead since it will also pull directly from the database and you can share the dashboards you create very easily. Large companies like the article are referring to might be able to get by with PowerBI (Microsoft would certainly argue there are still plenty of places where it works) or ERP/HR tools more specifically designed for certain uses.

CurtHagenlocher 9y ago
The same ETL functionality that's in Power BI is also available in Excel 2010+ as "Power Query".
- fiatjaf 9y ago
  See also https://www.querystorm.com/ https://www.querystorm.com/

rjtavares 9y ago

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")

drinchev 9y ago

The startup I'm working with right now is building a tool that allows you to create spreadsheet-based business applications. What I like to call the product is "excel on steroids". You can check it at www.ninoxdb.de.

fiatjaf 9y ago
This seems like an alternative to Airtable and Fieldbook. Am I wrong? I know these tools and I like them, but this wasn't what I was talking about in my comment. I meant something more flexible and more arbitrary, like a key-value store non-tech people could edit.

yorwba 9y ago

> It is not feasible to come up with the schema, write a software based on that schema and then give it to the non-tech people. Would it be feasible if you only had to write the schema and the software were generated based on the schema?

fiatjaf 9y ago
That would be much better. I was working on something like that based on https://postgrest.com/ https://postgrest.com/, but I'm now reconsidering the approach.