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 a 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.
Contents
Video Tutorial
Working with Rows, Columns, and Cells
Adding Rows
First, we will add a row between rows 6 and 7.
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 E8, leaving 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 SUM formula.
Deleting Columns
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 left 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 E3:E6 to D3:D6 when we delete this column.
As long as no formulas depend on the columns, you can delete columns without complications. Adding columns is a similar process as formulas are updated to compensate for the extra column. However, working with single cells is trickier. Let’s take a look at that next.
Deleting Cells
In the animation above, we delete cell A4 from the table. Since the table consists of similar rows meant to be 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 908.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 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
Moving 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.
Cell References
Most of the issues encountered above deal with cell references changing. We will discuss how cell references work next.