Excel Hints & Tips – Mixing Absolute + Relative mode

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?

Keep informed about happy workplaces

Sign up to Henry's monthly Happy Manifesto newsletter, full of tips and inspiration to help you to create a happy, engaged workplace.

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.

Transposed data

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

Switching 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.

Learn the 10 core principles to create a happy and productive workplace in Henry Stewart's book, The Happy Manifesto.

Support your aspiring and current managers to be empowering and confident leaders with Happy

Happy offers leadership programmes at Level 3, Level 5 and Level 7, from new managers/supervisor level all the way up to senior leadership teams and CEOs. These programmes are based on the ideas of trusting your people. They are practical and based on applying what yo’ve learnt. We aim to inspire and ignite change in your organisation, as well as giving you valuable management skills such as business strategy, decision-making, negotiation and project management.

We also offer programmes tailored specifically to people from Global Majority backgrounds. The content is the same, but have been designed to give new and experienced managers the skills they need to navigate organisational culture with a clearer perspective on their own potential, as well as building their confidence and expanding their professional strengths.