Excel Tips: Use an Entire Column as a Cell Range

Lesson 16: Use an Entire Column as a Cell Range

/en/excel-tips/add-a-new-row-with-a-shortcut/content/

Use an entire column as a cell range

In some situations, you may want to find the sum or average of a column of data. Although you can do this with a normal cell range, you may have to update the cell range later if you add more data to your spreadsheet.

However, Excel also has a different kind of cell range that omits the row number and pulls in all of the cells in the entire column. This kind of cell range allows you to add as much data as you want to your spreadsheet without having to update your cell ranges.

Check out the video below to see exactly how this works.

Steps

Type =SUM or =AVERAGE in a cell where you want the result to appear, followed by an open parenthesis.
To create the argument, click near the column letter, known as the column heading. This way, the entire column is selected.
If you clicked on Column C, for example, you would notice that C:C has been added to your formula. This means that it will pull in all of the values in Column C.
Close the parentheses and press Enter. This will give you the sum or average, respectively.
Double-click the formula to review the selected cell range. If it includes the empty cells below the data entered in the selected column, you can add more data later on, and it will automatically be included in the formula.

Conversely, make sure you don't include any unrelated data in that column that you don’t want included in the formula. You have to be careful when you’re using a whole column in a cell range–but it’s still a helpful shortcut!

In Lesson 17, we’ll talk a little bit about named ranges in Excel.