Excel Hints & Tips – The OFFSET Function

How do I set my spreadsheet to automatically extract this month’s figure from the accounts?

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.

Let us assume the spread-sheet uses the standard accounts layout of figures for each month, so 12 columns of figures in all, for January to December. You want to indicate the current month with a number (1 for January, 2 for February etc) and have this month’s figure automatically displayed separately.

One approach is to use a very complicated IF statement:

IF(P1=1,C4,if(P1=2,D4,if(P1=3,E4 … and so on up to P1=12

However this is long-winded and complex and therefore invites errors. Our rule of thumb is if you get to three levels of Ifs, there is probably a simpler way to do it. And in this case there is a much simpler way.

The OFFSET Function

OFFSET is a neat little function which allows you to say, from any cell go 3 to the right and 4 down – or, better, use cell references. So to extract this month’s accounts:

=OFFSET(B4,0,P$1)

P1 is the number of the month. B4 is the column before the financial data starts, and the 0 indicates you don’t want to move down a row. So it will take the value of C4 when when P1 is 1, D4 when P1 is 2 and so on.

Offset example

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.

Darren Andrews

Darren is one of Happy’s Senior Trainers, able to train almost every IT course on our course programme. He worked for Happy for 12 years and has been an Associate Trainer since 2018.