hide – Excel for Accounting Students

To remind you if you’re a bit unsure, a worksheet is one tab in a whole spreadsheet (or, in Excel terms, workbook). The default (standard) number of tabs varies from computer to computer and depends on your own settings – for me, I only get one worksheet in a new workbook, but at work I get three. They are each called Sheet1, Sheet2, Sheet3 and so on.

For your delectation and delight, each of these skills is demonstrated in animated gifs available in a PowerPoint file to download – click here.

Here we’re going to look at a few different things we can do with worksheets… starting off with the tab itself!

Renaming a worksheet

There are two ways to do this – both are equally quick.

Either double-click on the tab name, or
Right-click on the tab name and choose Rename.

You must confirm by pressing the Return (or Enter) key once you’ve finished typing.

Recolouring the tab

Again, this is accessed via right-click. When you point to Recolor, you’ll see a fly-out palette. If you are asked to recolour to a specific shade, point to each colour and wait a short second – a panel will appear telling you the exact name of the colour.

While you’re in the tab, the colour is pale; when you move to another tab, the colour is the full shade which you chose.

Selecting more than one worksheet at once

There may be occasions where you want to apply the same formatting, typing or even formula to more than one worksheet. Rather than repeat this over and over, you can select more than one at once – and any action you perform on one will appear on all.

There are two possibilities: You want to select worksheets A through to F, or you want to select A, C, D and F (missing out B and E).

To select ALL worksheets, hold the Shift key down on your keyboard, click the first and then the last worksheet. All worksheets between the first and the last you clicked will be selected.

To select individual worksheets, hold the Ctrl key down on your keyboard, then click whichever ones you need.

Do be careful: it’s easy to forget you have more than one worksheet selected at the same time. Many has been the time I’ve done what I needed to all worksheets, then plunged on with what else I needed to do – which has doggedly replicated on all of them, when I didn’t want it to!

Remove the ‘select all‘ by right-clicking and choosing Ungroup.

Incidentally, the Shift-click to select A-Z and Ctrl-click to select random isn’t just limited to worksheets. Try it with cells as well. Try it with files in your Documents folders or File Explorer. It even works with words in Microsoft Word!

Moving, copying and deleting worksheets

Like renaming, there’s two ways of moving or copying.

‘Manually’: click and drag to move or reorder your tabs. To create a copy, hold your Ctrl key down while clicking and dragging
‘Mouse only’: right-click on the tab name and choose Move or Copy. Look at Before Sheet and decide which sheet you’d like your sheet to end up before. If you want a copy of it, make sure you place a tick in the Create a copy box at the bottom.

To delete a sheet, right-click on it and select Delete. Do be aware that this action cannot be undone! If you make a mistake, you might be lucky if you close without saving changes.

If you have more than one worksheet selected, right-clicking on any and choosing Delete will allow you to delete multiple sheets at once.

You can’t delete the final sheet. Which is kind of logical, really – who heard of a book with no pages?

Adding a new worksheet

Next to your tabs is a small circle with a plus in. Click on this to add sheets quickly.

Hiding/unhiding worksheets

Let’s start by asking – why would you need to hide a worksheet? Well, you might have a formula on it, reference cells that feed into a Data Validation drop-down list, or even confidential data. Unlike hiding columns or rows, though, this is less obvious. Once you hide the sheet, you can’t see any part of it.

Right-click on the tab name and choose Hide. For additional security, move to the Review tab and click Protect Workbook. Enter a password if required (with the standard provisos about forgetting it!) and click OK. Now you can’t unhide the worksheet!

To show it again, remove the protection from the Review tab, then right-click on any of the existing tabs. You’ll get a panel appear which lists all the sheets you have hidden, so you can select the one you want to unhide.

Incidentally, you have to have at least one visible sheet in a workbook.

Moving or copying worksheets to a new or different workbook

Not many people realise you can copy an entire worksheet in just a few clicks to somewhere entirely new. I’ve seen lots of people carefully copying and pasting, trying to get all the column widths the same, the formatting, the formulae and so on… you really don’t need that faff!

Firstly, if you’re moving your worksheet to an existing spreadsheet, make sure it’s open. If you want to move it to a new workbook you don’t have to create a new one first.

Right-click on the tab name of the sheet you want to move or copy, and click on Move or Copy. A small panel will appear. At the very top, it says Move selected sheets to book: with a drop-down panel. You can choose the filename of the other workbook, or to (new book).

Of course, if you want to create a copy, just make sure you have that tick in the bottom panel. It’s easy to forget, so nine times out of ten I move the sheet by accident then have to do a copy back to the original workbook!

If you have a new book with a single sheet in it and you choose to move that sheet to an existing workbook, the new book will close. Of course, you can’t have a workbook with no sheets in it, so there’s no point in it staying open.

A little navigation trick

There are two ways that I move around workbooks really quickly. You can bet I’ll be doing these in the exam, to zip backwards and forwards to the sheet with the questions on!

To move between them by keyboard shortcut, hold your Ctrl key down and press Page Up or Page Down (might be PgUp/PgDn on your keyboard).

To move between worksheets in a book with masses of sheets, move to the empty area to the left of your tabs – it has two tiny arrows on it. Usually, clicking those arrows allows you to move to the left or right by one tab – however, pausing your mouse in that area tells you a couple of useful features. I use right-click to see a list of all the sheets, then you can just double-click on whichever one it is you want and bam, you’re straight there. Handy, hey?!

Have a go

You can download a version of the spreadsheet in the animations by clicking here. Have fun, there’s really not much harm you can do with this set of features – even deleting can be rescued by a close-without-save.

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

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?