Before I move on to the next common error, which is the error that occurs when you refer to something you’ve deleted (briefly called “missing references”) I want to talk about deleting rows/columns/cells. In this post I’ll be discussing why you might want to do it, and some of the options you’ll encounter when you do. Next week I’ll discuss the nuts and bolts on how to do it in various programs. I’m probably going to be doing a lot more of these split posts in the future, since the topics I’m covering now often have a lot more options and intricacies to be discussed than the earlier ones did.
Why you’d do it
First off, deleting a cell is removing the entire cell, not just the contents of the cell. So why would you want to do it? There are a lot of possible reasons, although most of them boil down to sorting, keeping things tidy, or Appeasing the Arbitrary Spreadsheet Gods working within the limitations of the program. For example:
- When pasting from the web, blank columns often get inserted between the columns of data. If you want to sort everything as a group, it’s best to start by deleting the blank columns.
- Column of data that you’ll never need (or which you can re-generate easily if you do need it) are often best deleted. Yes, you could just hide them, but if you truly don’t need them then it can save file size and future confusion by just getting rid of them entirely.
- If you’re already at maximum. In some spreadsheet programs, if you do anything to the bottom row/last column, the program will remember that! Even if you delete whatever it was (sometimes even if it’s just formatting!) the program will be convinced that there’s something there. And, since the program can only handle a certain number, it won’t let you shove something else in. That’s kind of like if the first 100 customers get a free Nifty Gift. If all 100 are lined up outside the store, no-one’s letting someone else in, or else the previous #100 is going to become #101 and be out of luck. So if you want to insert a row when the program thinks it’s got the max, you’ll have to start by deleting a row that you know is blank, even if the spreadsheet doesn’t know it.
What happens when you do it
Most programs will offer 4 options: shift cells left, shift cells up, delete row, and delete column. Here’s how they behave.
Shift Cells Left: Every cell which is currently to the right of the cell(s) you’re deleting will shift to the left by the number of cells you’re deleting in that row. Imagine a grocery story with a bunch of checkout lanes. If one customer finishes and goes away, everyone in that lane moves, and no-one in the other lanes moves. That might seem pretty intuitive, but if Alice, Betty, and Carol were all chatting together and Betty’s lane moved, she’d no longer be able to talk with her friends. So if you have a column or table to the right of the cell(s) you’re deleting, it’s all crooked now.
Shift Cells Up: This is like “Shift Cells Left” except that everything below the cell(s) you’re deleting gets shifted towards the top.
Entire Row: Every cell in the row is deleted, and all the cells below that row are shifted towards the top. Everything stays even, but if you had multiple tables that included that row then you just deleted it out of all of them.
Entire Column: Every cell in the column is deleted, and all the cells to the right of that that column are shifted towards the left. Everything stays even, but if you had multiple tables that included that column then you just deleted it out of all of them.