Hacker News Reader

Comment by mehrdadn | Hacker News Reader

For the Excel pros here: how do you ever template anything in Excel? Like if you want the same formula for two different tables, do you just copy-paste? How do you keep them in sync? There's just so many things I want to do in Excel and it seems so limited in what it can do that I can't fathom how non-programmers end up using it so successfully.

View on HN

I've seen locked sheets used as data source for constants and formulas with variables, the cells reference those with lookups and variables.

I've come across a few spreadsheets that made me want to meet and learn something from the authors. Some have been very elaborate and surprisingly resilient.

You can look up formulas? Never realized that, that's really cool. Thanks!

I have seen formulas entered as plain text in a cell, and then a macro used to update other cells with that formula. You can then change the formula in one place and run the macro to update it everywhere.

The best way to create reusable formulas in your workbooks is to add your table to the data model (which creates an in-memory tabular cube) and write measures using the DAX formula language. This has the added benefit that a single formula can be written to aggregate data at different levels, for example, a sum can be calculated over days, months, or years. This will only allow you to share formulas in a single workbook. The data model in Excel is powerful and under utilized.

Wow I'd never heard of DAX. This sounds awesome. Thank you!

Most Excel users would copy paste a formula even _within_ the same table, and don't know that Excel's built-in 'Tables' allow you to automatically keep a formula consistent between rows.

I'm not sure whether there's a good answer to your question If there is, I'm sure that most 'Excel pros' don't know it.

Reply on news.ycombinator.com