Table of Contents
Maybe not annoyances but ways of getting things done.
Replace dates with incremented year
Your spreadsheet has a column with dates, say A7 has 1/2/2019 and you want to replace the dates in the rest of the column with the next year, e.g., 2020.
- Insert new (temporary) column to right of dates column (new and blank column B)
- Apply this formula in new column B next to the first date you want to change. Say column A row 7 has 1/2/2019. Put this formula in column B row 7:
- Select the cell B7 and drag down to select all rows in column B that you want to use to replace column A's dates
- Control-D (pastes the formula down the column). You should see the updated dates in column C.
- Select all of the column B cells you have changed, and copy to clipboard
- Put cursor in first date you want to change, that is, in cell A7
- Edit > Paste Special > Paste Special. Make sure All is not checked and Formula is not checked. Paste. OK the overwrite.
- You can now safely delete this column B.
Give a Cell a Name and Use It Elsewhere
It's easier to name a cell so that (a) references make sense and (b) you can move the cell but retain the reference.
for example referencing payment totals when adding/deleting rows/columns around them.
How to name a cell
- Select the cell
- Upper-left corner, see the letter-number (e.g., D23) of the cell. Type the name you want in this spot (e.g., CCjan)
- Repeat as needed
Alternatively you can select the cell, Sheet > Named Ranges and Expressions > Define
How to use a named cell
- Select the cell to reference the name
- Type = and start to type the cell name (e.g, C C j a)
- When the desired name is the only choice, press enter.
Note that names can be used within formulas.