Aug 22 2022 11:35 AM
Sorry, I can't believe this is not already answered somewhere, but I can't find it.
I want the value in column E of any particular row to always be the sum of the values in columns C and D from the same row. I understand that I can do this on a row-by-row basis by manually entering a formula, e.g., =SUM(C2:D2) for row 2. I really don't want to have to type this formula for every bloody row in the column, and then if I do some editing which changes row numbers the formulas are all wrong. Isn't there some magic shorthand that means "this row" that I can use for every row? Or better yet, something that I only have to type once for the entire spreadsheet for all time, perhaps in the column header, and then it works for every new row which I add to the bottom of the table? Suppose this magic shorthand for "this row" is the character # . I would like to use a formula that looks like =SUM(C#:D#).
How do I do this? I'm sure there must be a way.
Aug 22 2022 11:42 AM
You may use structured tables instead of ranges. It operates with "this row" and automatically expands the formula on all rows in the table.
With range, you may enter formula into the first cell of the column, select starting from that cell till end of the column and Ctrl+D
Aug 22 2022 11:46 AM
Aug 22 2022 01:13 PM
SolutionFor the structured table it's enough to enter formula like
in any row, it will be applied to all other rows (of course, if they were empty). Not necessary to type everything what is in formula bar, with using of arrow keys Excel helps to combine formula. "@" means "this row" Using structured references with Excel tables (microsoft.com)
For the range enter formula in first cell and select from it end of the column
Ctrl+D
Aug 22 2022 04:02 PM
Thanks for your help, @Sergei Baklan !
Aug 22 2022 01:13 PM
SolutionFor the structured table it's enough to enter formula like
in any row, it will be applied to all other rows (of course, if they were empty). Not necessary to type everything what is in formula bar, with using of arrow keys Excel helps to combine formula. "@" means "this row" Using structured references with Excel tables (microsoft.com)
For the range enter formula in first cell and select from it end of the column
Ctrl+D