Useful tools and features

Here are a few little tips for making your life quicker and easier when using Excel. It’s great when you know about them, as it can make you look like a real Excel pro!

Autofill 1

When you enter data into a cell, look carefully at the border. In the bottom right-hand corner of the active cell, you can see a small blob. When you pass your mouse over it, it becomes a baby plus – this is different from the big plus you normally get when you move your mouse over the spreadsheet.

This baby plus is called Autofill and lets you access all kinds of useful things!

First up in the demonstration gif (see below) is months and days. You can see at the bottom of where you stop dragging, you get a small square. I call it the Box of Tricks (but officially it’s called Autofill Options). You can see you can either have the original cell copied all the way down, or have alternatives like filling only with weekdays. So handy!

It works with numbers too. Stepping into your Box of Tricks allows you to choose to fill a series or to copy the cells, just as before. If you need to put in a different series, you have to give it the first two numbers, select them both, then drag with your Autofill.

You can see this works with dates as well. Incidentally, I used a keyboard shortcut to put in today’s date automatically: hold the Ctrl key down and press the ; key once and you will get today’s date! Ta-da!

Autofill 2

But wait! There’s more!

If you want to copy without including the formatting, you can do that too! This can be really handy if you don’t want to mess up a formula or overwrite text.

And here’s a real timesaver – if you have ANY data to your left, double-clicking your baby plus will snap your series straight down the same number of rows. Try it… How cool is that! If you’re like me, working on spreadsheets with thousands of rows, this can be a genius tool and no mistake.

Also, you can harness it in your formulae – probably the commonest use for Autofill. When you double-click or drag a formula down, any cells referred to in the formula will change as you go down.

And you can also use it to copy formatting over text, using your Box of Tricks. Huzzah!

Autofill 3

With Autofill, you don’t just have to drag down – you can drag across.

However, with a large number such as a date, you might get a bit of a hoohah going on in your cells, as can be seen here! But wait – when I look in the formula bar everything’s there, so what’s going on? Don’t panic, all it means is that the cell isn’t wide enough to show the whole number. Rather than showing you a portion and fooling you into thinking that’s the whole number, it puts a row of ### to warn you. Just widen the column!

As you can see here, you can widen the columns neatly by double-clicking on the boundary between one column letter and the next. If you have several to do at once, select all of them and double-click any of the boundaries.

I also did a formula here to add 7 days onto each of my dates. I just dragged the formula across instead of down, and hey presto! I have a week later on each of my dates!

‘Gluing’ a whole row in place (Freeze Panes)

This feature is surprisingly underused. Basically when you set up a spreadsheet, you’ll have column headings at the top, to tell you what’s in your columns. But when you scroll down, they can disappear off the top of the screen – which isn’t so useful!

To fix this, you can ‘glue’ rows in place so that no matter how far down you scroll, you can still see your column headings. This is called Freeze Panes.

You can also do this with row headings, so if you scroll off to the right they’re still in place.

The gif of this is very wide and won’t display properly here, so I have uploaded it to my Google Drive. You can see it here. Remember, please download it – it won’t play in Google Drive as is.

The opposite – removing the Freeze Panes – (disappointingly) isn’t called Thaw, but Unfreeze. Grammarians, look away now!

Hiding columns or rows

There may come a time when your spreadsheet is a bit unwieldy and you have too many columns or rows to be able to see clearly or navigate effectively. You might also need to show someone something but not wish to display data which might be confidential. When this happens, it may help to hide some rows or columns. This is like folding the columns and rows out of the way, it is not the same as deleting.

All the instructions here refer to columns, but they equally apply to rows.

• To hide: Firstly, select the entire column(s) you want to hide. Right click somewhere in the highlighted bit, and choose Hide. Note that there’s a pair of little lines as the boundary between the columns now, whereas before it was a single line. This is a clue that columns have been hidden.
• To unhide (reveal them again): Select BOTH columns (or rows) either side of the hidden column, right click somewhere in the highlighted bit, and choose Unhide.
• To unhide many columns at once: Select over all the columns concerned. Right click as before, and choose Unhide.
• To unhide Column A (or row 1): If your very first column(s) or row(s) have been hidden, you have to click and hold, and move back to the left. Then you can right click and Unhide. For Row 1, obviously you click and drag upwards.
• To unhide everything that’s hidden, in one go: Click to the left of the column letters and ‘north’ of your rows, on the small square with a triangle in – this selects your entire spreadsheet and is very handy to know about – then you can double-click as if to widen the columns, and everything gets Unhidden. Remember to do the same for the rows.

Flash Fill

This is a stupendously fab tool, which very few people seem to know about. It works by recognising a pattern and projecting the pattern all the way down all the rows.

In this example, I want to have a column with full names, rather than just one for First name and one for Surname. (Incidentally, Surname is a British term meaning family name or last name.) I start off by typing the first in the pattern, with a space as needed. Then I start typing for the next row – and before I’ve even got two characters in, it’s recognising what I want to do!

All I need to do now is press Enter to confirm it, and bingo! It’s done! Cool, hey?

 

If you’re following the suggested Learning Plan, please click here to go to the next post.