How to use the ADDRESS function in Excel, and how to get headers and footers right the first time

October 19th Excel 3 tip Friday

In our weekly blog, this Friday DataMinded brings you a tip, a function, and a shortcut that will help you to take your Excel skills to the next level.

A quick win you can learn today -

Adding headers and footers to a word document is seemingly easy, but for Excel, the option can be hard to find. Go to the insert tab and select headers and footers.

The pop-up box gives the option to add text to the left, middle or right of the page at top or bottom, and there’s some useful presets including page numbers and file name.

However, unlike word you can’t see how your headers and footers will display until you go to print preview - and depending on your margins, they can often overlap the spreadsheet contents! To fix this, go to margins under the page setup tab and increase the height of the header and footer areas and check the print preview again.

A keyboard shortcut I'm adding to my list -

I often find myself performing formatting or other options on entire columns or rows, and if you find it faster to use the keyboard, you can do this easily using the keyboard shortcuts Ctrl + Spacebar to select the column of the active cell, and Shift + Spacebar to select the row of the active cell.

Check more useful keyboard shortcuts in Excel for Windows here!

A function I've found super useful this week -

It can be useful to display a cell address - for example if you’re instructing the user of a spreadsheet where they should enter data - you could use the COUNTA function to find the bottom row of a table of data. The ADDRESS function allows you to enter a row number and column number to return the address of a cell. So for example, row 523, column 27 could return AA523.

There are just 3 components to the function - the row number, column number and a final optional number from 1 to 4 to choose if you’d like to display absolute references (with $ symbols). The 4 codes are:

  1. (or omitted) Absolute references

  2. Absolute row, relative column

  3. Absolute column, relative row

  4. Relative references (no $ symbols)

As always, I hope you find these tips useful, and please do reply if you have any comments or questions!

- Martyn

Edinburgh, Scotland

October 2018