Other Excel Add-In, Templates and Training

Lesson 1D

Worksheets

All Workbooks must contain at least one Worksheet. The maximum number of Worksheets a Workbook can contain is limited only by the available memory of the PC. As a default, Excel places three blank Worksheets in front of you. Each one of these has the default name Sheet1, Sheet2, Sheet3 at the bottom.  By default all Worksheets are identical in that they contain Rows, Columns and Cells (all discussed below).

NOTE: You can change the default of three blank Worksheets by going to Tools>Options/General and changing Sheets in new workbook: by typing in the number you require.

All Worksheets contains 256 columns and 65,536 rows.  Each separate rectangle within the Worksheet is called a Cell. You will notice letters across the top of your Worksheet. These are the names of the columns and go from A through to IV. The numbers down the left hand side of your sheet of graph paper symbolise row headings and go from 1 through to 65,536. 

Inserting Worksheets

Adding new Worksheets to a Workbook can be done in a number of ways.  The most common are:

  • Go to Insert>Worksheet

  • Right click on another Sheet name tab and select Insert and then select Worksheet.

There is another way that is not exactly inserting a new Worksheet, but rather making a copy of an existing Worksheet.

To do this, select the Sheet you want to copy then left click on the name tab and holding down the Ctrl key (bottom left hand side of your keyboard), then drag it to one side, then release. This is a great time saver when you have spent hours setting up a Worksheet with all the formatting you want. You will notice that Excel will place a number after the copied sheets name in brackets. This is because we cannot have two Worksheets of the same name in the same Workbook. Name and Renaming Worksheets is covered later.

Deleting Worksheets

The logical opposite to inserting Worksheets is Deleting them. This can be done in any of the following ways.

  • Right click on the Worksheets name tab and select Delete

  • Go to Edit>Delete..... the Delete dialogue box will appear with options, select your option and click OK to verify your request.

Whichever method we use, Excel will display a warning message letting us know that the deletion cannot be undone. This is not technically true as we could always close our Workbook WITHOUT saving and then re-open the Workbook, but of course this means we would loose any other changes we had made.

Naming Worksheets

We can also name our Worksheets with meaningful names (which is a good idea!). The only restrictions we have with Worksheet names is:

  • They cannot exceed 31 characters.

  • They cannot contain the following characters: \ / ? * [ ].

  • The name cannot be the same as another Worksheet in the Workbook.

While it is not totally necessary, try to AVOID using spaces between words. For example, if you wanted to name a Worksheet Bills Hours use BillsHours instead! The reason for this is that some of Excels formulas have slight problems when they reference Worksheets that contain spaces.

To name a Worksheet we can do so with any of the most methods below:

  • Double click the Worksheet Name tab. This will allow you to now type the name. Once finished, push Enter or click any cell.

  • Right click on the Worksheet Name tab and select Rename from the shortcut menu. Then type the name and hit Enter or click somewhere else on your Worksheet.

  • Go to Format>Sheet>Rename type the chosen name and hit Enter or click somewhere else on your Worksheet.

Moving Worksheets

At times we may need or wish to move a Worksheet to another position in the Workbook or even to another Workbook.  We have already looked at how we can make a copy of a Worksheet, select the Sheet you want to copy then left click on the name tab and holding down the Ctrl key drag it to one side, then release.  If we do not want to copy but rather move the entire Worksheet to another position in the Workbook we can simply left click the Worksheet Name tab, then holding down the mouse button drag it to it's new position and release.  The other method is to use the Move or the Copy dialogue box. This can be accessed in two ways.

  • Right click on the Worksheet Name tab and select Move or Copy

  • Go to Edit>Move or Copy Sheet…

It makes no difference which method you use!  You will see on this dialogue box a To book: box with a small drop arrow to the right. The selection in this box will determine where our sheet will be moved or copied to.  The default name in this box will be the active Workbook name (the current Workbook we are in).  There will also always be another choice, if we click the drop arrow. This is (new book), if we choose this option Excel will automatically create a new Workbook for the sheet to be moved or copied to. Within this box will also be the names of any other open Workbooks, should there be any.

Directly below the To book: box is the Before sheet: box. Our selection in this box will decide which position our sheet will be moved or copied to. Last of all is the Create a copy checkbox.  If we check this box Excel will create a copy of our moved Worksheet. In other words our original sheet will remain where it is and a copy of it will be moved to our chosen location.  If we leave it unchecked (which is the default) the Worksheet will be moved and not copied. The best way (as with most of excel) is to jump straight in and try out different options - you cannot do any harm!

Let’s now have a look at the cells on our Worksheet in more detail.

Cells

These are without doubt the very backbone of Excel. On each Worksheet there are 16777216 cells. These are divided into 65,536 Rows and 256 Columns, as I mentioned earlier.  The number of Rows and Columns in a Worksheet are fixed, which means we cannot add more and we cannot take any away, so as a consequence the number of cells are also fixed. This should never become a problem, as we can add a lot of Worksheets if we need to, each with 16777216 cells, so this should give us plenty!  A cell is defined as the intersection of where a column and row meet, just like the coordinates on a road map.

The method that Excel uses to reference these cells (as a default) is called the A1 style reference. When we refer to a particular cell we use it's cell Address (or location) to nominate the cell. The method Excel uses to reference cells is a very simple, but highly effective method, often referred to as the grid pattern method, exactly the same as a road map.

Click in cell C6.  It should be defined as your active cell, or the cell in which you are clicked, by the border of the cell or rectangle being different in some way, either bolded, or maybe a different colour.  Now have a look at the column heading letter.  It should be defined in some way, either bolded, or highlighted in a colour, it may even look pushed in like a button.  Now look at the row number on the left of your screen which should also be defined in some way.  This is telling you your cell reference.  Now look above the column heading A and you will see the reference C6 in what is known as the Name box (discussed below).

In the situation above we have selected one cell only. We can select many more cells at one time if we wish. When we do so, we have selected what is known as Range of cells. A range can be one cell, or many cells.

To select a range of cells we simply select any one cell (position the cursor so a cross appears), then holding down our left mouse button, drag in the direction we wish to select. It is important to note here that although we have more than one cell selected (a range) there is only one Active cell and this will always be the first cell we select. There is no limit to the number of cells we can select on a Worksheet. 

To select all cells on your worksheet either push Ctrl + A or the Select all button, (the empty grey rectangle in the upper-left corner of a Worksheet where the row and column headings meet). We can also select entire columns or rows by simply left clicking on the column letter or row number.

We mentioned above while looking at Toolbars that there is another shortcut menu in Excel, the right click menu. The right click menu is also known as the context-sensitive menu.  This means that it will display options that are sensitive to the area in which you are clicked.  To see this simply select any cell and (you guessed it!) right click. You will see a list of common options available in the area you are clicked in. We won't go into any detail on these at this stage but we most certainly will in later lessons.

While we are on the subject of the right click, you will notice as we go into Excel deeper that it has many of these right click menus. As stated above, the menu that we are presented with is wholly dependent on the area of Excel we are in at the time.

Moving About the Worksheet

There are many ways that can be used to move around a Worksheet and the method used is purely user preference.

At the far right of your Worksheet window is what is known as a Vertical scroll bar.  This bar has an upward pointing arrow at the top of it and a downward pointing arrow at the bottom of it.  You can vertically scroll up and down through your document in any one of the common following ways (there are more!):

  • Place your left mouse button on the downward (or upwards) pointing arrow and click once to scroll slowly through your document. This will move you down one row at a time.

  • Hold the left mouse button down on the downward (or upwards) pointing arrow to move more quickly through the document

  • Hold your left mouse button down and drag the grey sliding button between the black upward and downwards arrows. Notice when you select the last option a yellow box with the row number will appear. This is because you will not move to the location until you release the mouse button.

  • Select any cell and then drag down. You may notice that doing this will display a small yellow box that informs you of how many rows you have selected and how many columns. This will only appear after the active cell has scrolled from view. You can also do the same by selecting any cell and holding down the Down arrow on your keyboard while holding down your Shift key.

  • Use the Page Up and Page Down keys on your keyboard.

To get quickly back to the top of your Worksheet, push Ctrl + Home. This will always take you to cell A1.

At the bottom of your Worksheet window to the right is your Horizontal scroll Bar. The use of this is the same as with the Vertical scroll bar, except of course you move horizontally and you cannot use Page Up and Page Down.

If you happen to know the address of the cell you wish to go to we can use the Go To dialogue box.

  • Go back to cell A1 (Ctrl + Home)

  • Select Edit>Go To (or push your F5 key, or push Ctrl + G)

What has popped up in front of your now is the Go To dialogue box.  Note that your cursor is flashing in the Reference section of the box. This is where you type the cell reference that you wish to Go to.  The big box (Go To) is where Excel stores the last references used.  If the reference is in here you would simply select it and click OK

Type in HS3156 in the Reference box and select OK (or hit Enter on your keyboard if you prefer). Excel will take us straight to the cell reference we typed.

To get back to the top left of the Worksheet, push Ctrl + Home. The Ctrl + Home method is simply a short cut key for typing A1 in the Reference box of the Go To dialog box.

While the Go To dialogue box can be handy for moving straight to any cell, there is an easier way!  To see what I mean left click in the Name box (left of the Formula bar), type any cell address and push Enter.

You are no doubt wondering why this is called the Name box? We will cover this in detail later, but not in this lesson.

Moving Through the Worksheets

The only area of navigation we need to look at now is the Worksheets themselves. This can be done in one of two ways. The most popular and probably the simplest method is to simply left click on the sheet name tab of the sheet you want. This will automatically activate the appropriate sheet.

The second method is probably best suited to when you have a lot of worksheets in the same Workbook.

You will notice to the far left of the sheet name tabs, you have four arrows. Place your mouse pointer over any one of these and then right click. You will see a pop up menu containing the names of all the Worksheets within the Workbook. You simply select the one you want with your left mouse button. The four arrows to the left of the sheet name tab are called Tab Scrolling Buttons. These four arrow buttons are to allow you to scroll through the Worksheet name tabs should your workbook contain more Worksheets than are currently visible. If you click the arrow with the vertical line after it, you will scroll so that your far right hand Worksheet name tab is visible. If you click the arrow without the vertical line, you will basically scroll one Worksheet tab at a time.

Exiting

There are two ways to exit Microsoft Excel and close the program down. These are:

  • By selecting the cross (X) at the top right hand side of your screen

  • By selecting File , then Exit

Select either of these options to close down Excel. If we wished to Exit Excel and we were working in a Workbook that we have made changes to, Excel will ask us (via a message box) if we wish to save the changes we have made. If we wished to Exit Excel and we had a Workbook that we had not made any changes to, no message will appear and Excel will Exit without saving.

Summary

So in summary, we have discussed the three most important aspects of Excel, these are:

  • The Workbook

  • The Worksheets and

  • The cells

We have looked at the definition of each of these aspects in detail. We have also covered:

  • How to save Workbooks

  • Create new Workbooks

  • To insert and delete Worksheets

  • Name, move and create new Worksheets in a variety of ways.

We have looked at the many ways of moving through the cells within your Worksheet, using a multitude of options, including the Go To option located under the Edit menu item.

We have looked at our Excel screen in detail, the toolbars and how to dock them and the formula bar. We learnt how to get help on a specific menu item by using the Shift + F1 option. And finally, we learnt how to start and Exit Excel in the correct way.

Goto Next Lesson