Excel Hints & Tips - Mixing Absolute + Relative mode

In: BlogDate: Jun 19, 2013By: Chris James

How do you record a macro that will copy data from a set area on your spread sheet and then paste it into the first available empty row, in an ever increasing list?

Hi, we are Happy

We are leading a movement to create happy, empowered and productive workplaces.

How can we help you and your people to find joy in at least 80% of your work?

More about Happy

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!

Why not sign up to our newsletter?

Sign up to our monthly newsletter, full of tips, tricks and news to help you to be happier and more productive at work.

Sign up here

Improve your productivity in Excel with happy

Happy has high-quality, learner-focused Excel training courses for all skill levels, designed to improve your confidence and productivity. And our new online learning programme is just as interactive as our classroom sessions.

Here are our most popular options:

Our learners tell us that they save an average of 32 minutes a day with our Excel training courses. How much time could you save with us?

Why learn online with Happy?

  • Interactive and engaging - just like our classroom sessions
  • Bitesize or full-day sessions - fit around your schedule
  • Learn from home - all you need is a quiet place to call from and an internet connection
  • IT Helpline - 2 years of free support after your course
  • No quibble money-back guarantee

Chris James

Chris was an Associate Trainer for Happy between 2012 and 2016.

More by Chris

Testimonials from happy Customers