Excel Hints & Tips - Mixing Absolute + Relative mode

In the example below our data is imported into an area of our spread sheet in column B – the Sales Order Form (in yellow). We then wish to copy this data from the yellow area and transpose it as a new record in the first available empty row below any existing records – with the blue field headings in row 2.

Step 1: Start your macro recorder with the setting set to Absolute mode

Step 2: Select the cells to be transposed – cells B3:B11

Step 3: Copy into system memory (Ctr+C)

Step 4: Select the first cell in the existing table where the record is to be transposed – cell E2

Step 5: Move the cursor down to the last record – press Ctr+ (down cursor)

Step 6: Switch the recording to Relative mode

Step 7: Click into the empty cell immediately below (in this example this happens to be E6)

Step 8: Choose, Home à Paste à Transpose (as below)

Step 8: Press Escape

Step 9: Stop Recoding

So by starting at the top of the table and using the Ctr+down-key (in Absolute mode) followed by a switch to Relative mode and then clicking one cell lower, the replay is always able to locate the first empty row wherever it happens to be!

Related Blogs

• Using Absolute Cell References in Excel -  What exactly IS an Absolute Cell Reference. Jonny breaks down the basics simply in this guide.
• How to Create Partial Absolute Cell References in Excel - This quick, time-saving video by Nicky will talk you through all you need to know about creating Partial Absolute Cell References.
• Save Time in Excel with Autofill — Autofill can save you lots of time, filling in data so you don't have to type it in yourself.