Grand total automatically

[arif]

I have a marksheet with students' names
in e3 to 36 columns total is put using sum formula
but suppose i enter a subject then i have to shift sum formula to F3 to F7 but manually
is there any way that i enter a column or row and put data in that column and sum formula is changed automatically for adding the data in that new coloumn
plz

Find sample worksheet

 

[Hui]

If you change the data area to a Data Table using the Insert Table command
Remake the Sum formulas and they will now be structured references instead of ranges
Now as Rows are added or deleted the formula will allow for that



See attached file:

 

[Ajesh]

Best option would be to convert data range to data table as suggested by @Hui. But if for some reason you cannot use data tables you can leave one column blank (Column E in enclosed file) and use absolute reference in SUM formula including that blank column. Whenever you need to add a column you insert it before the blank column and your total update automatically.

Similar approach can be adopted for row total as well.
Regards/Ajesh