3 ms·

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

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.

amanzi 7y ago
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.
rahimnathwani 7y ago
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.
kvna 7y ago
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.
- mehrdadn 7y ago
  Wow I'd never heard of DAX. This sounds awesome. Thank you!
grosswait 7y ago
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.
- mehrdadn 7y ago
  You can look up formulas? Never realized that, that's really cool. Thanks!

amanzi 7y ago

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.

rahimnathwani 7y ago

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.

kvna 7y ago

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.

mehrdadn 7y ago
Wow I'd never heard of DAX. This sounds awesome. Thank you!

grosswait 7y ago

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.

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