Using Excel, how can I shift all values in a column down 1 row?

Question

I have a spreadsheet filled with timestamped data. However, I've found that the timestamps are paired with the wrong data (for example, the timestamp on row 8 belongs to the data on row 9). Does Excel 2016 (the version I'm using) have some way to shift all the timestamps down one row?

Solution

If your time stamps are in A1:A500

---

1. Select A1:A500

2. Cut

3. Select A2

4. Paste

---

or

1. Start this formula in B2 =A1

2. Autofill down the column

3. Select and copy the entire column B

4. Pastespecial(Values) in Column A to overwrite.

---

or

1. Insert a new row (every column shifts down)

2. Select the first cell in the columns you did not want to lower

3. Right click and select "delete"

4. Select "shift rows up" from the window that pops up

Comments

[Burgi]

Cut the entire dataset then paste it one row down.

[JellyMyst]

I should have noted this in the question. My apologies. There are over 70 thousand rows of data. Any method that requires me to drag all the way down the sheet, like your first two, are therefore a bit slow and inefficient. The third one worked a treat, though. Thanks!

[ProfoundlyOblivious]

@JellyMyst If your data is in Column A, and you have A1 selected, then the keyboard shortcut Ctrl + Shift + Down Arrow may help in that type of situation, it will select every cell between A1 and the first empty row. If your data has empty rows, you can press Ctrl + End to go to the last cell on the worksheet, then press Home to go to the last cell on Column A, then press Ctrl + Up Arrow to go to the last populated cell in Column A, and finally press Ctrl + Shift + Home to select every populated cell in Column A.