The main workspace of every spreadsheet is a grid of columns and rows. You can move, insert and delete these columns and rows, and the surrounding data will adjust accordingly.
Let’s use the basic spreadsheet as an example as we dive further into this concept.
In the image above, the active cell is
E7. The cell is named
E7 because it is at the intersection of column
E and row
7. As we work through the examples below, the
SUM formula in
E7 will be our focus as the formula adapts to the changing layouts.
Working with Rows, Columns, and Cells
First, we will add a row between rows
To add a row, right-click (command-click on a Mac) in the grid as shown in the animation above. Right-clicking will bring up a context menu with the options to Insert one row above or Insert one row below. We inserted a row above
7. This moved the contents of cell
E7 down one row to
E7 empty. If we inserted a row below
6, it would produce the same result.
Notice the cell reference
E3:E6 in the
SUM formula stayed the same. This is helpful because the formula still includes the same range instead of shifting down to start at
E4. However, be aware the new cell range does not include cell
E7. If you add data to row
7, you will have to update the range in the
Adding or deleting columns is also done through the context menu. In this case, we chose Delete column. The data from columns C and D shifts lefts to columns B and C. Similar to how the formula updated when we added a row, the SUM formula range shifts one column left from
D3:D6 when we delete this column.
As long as no formulas are dependent on the columns, you can delete columns without complications. Adding columns is a similar process as formulas are updated to make up for the extra column. However, working with single cells is trickier. Let’s take a look at that next.
In the animation above, we delete cell A4 from the table. Since the table consists of similar rows meant to be vertically aligned, deleting one cell disrupts this alignment.
Unlike working with rows and columns, formulas do not necessarily update when one cell is added, deleted, or moved. The SUM formula in cell
D7 does not change in the example above. Therefore, the amount in cell
D7 decreases from 1907.92 to 904.42.
Swapping Rows and Columns
There may be times when you want to switch the locations of rows or columns.
After selecting the rows or columns you want to switch, click on the corresponding row numbers or column letters and drag them to the desired location. Notice in the animation a thick grey line indicates the insertion point.
Be careful when swapping rows. However, if you watch the animation closely, you will see that total in the lower right changes from 1907.92 to 1188.55! You may need to adjust the cell ranges of the formula to avoid mistakes like these.
While swapping data trades one location with another, you can also move rows, columns, or cells without switching places. Moving items comes with its issues, which we will talk about next.
Moving Rows, Columns, and Cells
Cells, rows, and columns can be a quick way to rearrange your data. Moving a cell to the edge of your data, such as D7 above, will not break any formulas. However, you will notice that moving a row in the middle of a table overwrites the data in the destination row.
Most of the issues encountered above deal with cell references changing. We will discuss how cell references work next.