Automatically Updating A Row/Column...

I’m trying to switch from Apple Numbers to Excel. One of the issues I’m having is the fact that with Numbers a given sheet will show only the rows/columns that have data. When there is no more data the sheet ends. With Excel the last row of data isn’t the last row displayed… it keeps going.

So with Numbers if I have a sheet that “ends” and I later want to add an additional row/column I just “pull down” to get an extra row and add the new data. When I do that any calculations in any cells/sheets are updated to include this new row/column. So for example if I have a calculation that has a formula that includes “sheet 1 a1-a100” and I then add a new row, that formula will automatically update to “sheet 1 a1-a101”. In addition, all formatting is updated and included when creating the new row (cell colors, fonts, etc).

However, in excel because the list just goes on and on past the last row of data, when I add a new row/column of data, none of the formulas are updated. So I have to go in and manually update formulas to include this new row of data.

I’m sure this can’t be the way Excel works and I’m missing something. How do I make it so Excel only shows the rows and columns that have data and when I add a new row/column of data all formulas will automatically include the new row/column?