2  The worksheet

OBJECTIVES

  • Layout and use of the worksheet.
  • Activating a worksheet.
  • Adding, deleting, moving and copying a worksheet.
  • Renaming a worksheet and changing it.
  • Select with the mouse.
  • The important difference between the contents and the format of a cell.
  • Printing and print settings.

A workbook can have one or more worksheets. Each worksheet has a tab that is displayed at the bottom of the workbook window. The tab contains the name of the worksheet. In the worksheet, you can enter the data and formulas. The main topics in this chapter are how and where to enter this data in the worksheet and how it is displayed.

2.1 What is a worksheet?

A brief explanation of the design and use of the worksheet.

A worksheet contains rows and columns. The intersection of a row and a column is a cell. You can store data and formulas in the cell.

Figure 2.1: Worksheet with rows, columns and cells.

The rows are noted by numbers 1, 2, 3, … The columns are noted by letters A, B, C, … And after the letter Z it’s it starts again with AA, AB, … And after ZZ a third letter is added and it’s going on with AAA, AAB, etc. A worksheet can contain 16,384 columns and 1,048,576 rows. This provides more than 17 billion cells.

Each cell corresponds to a particular row and a particular column. The combination of the column letter and the row number is called a cell address or cell reference. The address of the first cell in the upper left corner is A1. The cell address can be used in formulas to make references to the content of the cell.

A cell is selected by clicking on it with the mouse. Then it becomes the active cell and the cell is surrounded by a border.Outline active cell.

There is always an active cell in the worksheet. You can activate another cell by clicking on it, or you can move the active cell with the arrow keys on the keyboard. When you start typing, then the data is entered in the active cell. You can end the data entry with the Enter key. By default, the active cell is moved 1 cell downwards.

The direction of the movement of the active cell after pressing the Enter key can be changed in the Excel Options. The choices are: down, up, right, and left.

The content of a cell can be:

  • text
  • numbers
  • formulas

References to other cells are allowed in the formulas. All the formulas are recalculated if the content of these referenced cells is changed. This makes a spreadsheet very powerful.

2.2 Activating worksheet

A workbook can have multiple worksheets, all identified by a tab at the bottom left of the window. A worksheet must be activated before you can use it. When there is only one worksheet, then this sheet is automatically activated. When there are more worksheets, then you can activate a sheet by clicking on the tab of the worksheet. The tab of the active worksheet has a white background.

Task 2.1 File: Consolidation.xlsx

  1. Open the file.

  2. Click alternately on one of the tabs of the worksheets. The worksheet becomes visible and the tab gets a white background color.

2.3 Add a new worksheet

Task 2.2  

  1. Start with a new blank workbook.

  2. Click on the button New sheet Button for a new worksheet..

A new worksheet is added at the end and active. The new worksheet is automatically named Sheet followed by a number. Of course, you can change this name (see Section 2.5).

2.4 Deleting a worksheet

It is a good practice to keep worksheets with content only and remove empty ones. Each worksheet can be removed except the last one, because each workbook must have at least one worksheet. If a worksheet contains data or is empty by removing all content, then Excel will ask for a confirmation for deleting it. In the other case, the worksheet will be removed immediately.

Task 2.3  

  1. Right-click on the tab name of the worksheet and then choose Delete.

There are now two possibilities:

  • The worksheet will be removed immediately. In that case, there is no next step.
  • A dialog box appears, asking for a confirmation of the delete action.
  1. Click Delete.

2.5 Renaming worksheet

It is recommended to give meaningful names to worksheets instead of the generated default names like Sheet1, Sheet2, Sheet3, …

Task 2.4  

  1. Rename a worksheet in one of the following ways:

    • Right-click on the tab name of the worksheet and then choose Rename.
    • Double click on the tab name of the worksheet.

  2. Type the new name, closing with Enter.

2.6 Copying worksheet

Task 2.5  

  1. Right-click on the tab name of the worksheet and then choose Move or Copy ...
Figure 2.2: Dialog box Move or Copy worksheet.
  1. You can perform one or more of the following possibilities:
  • To book - Default, this is the same workbook. By clicking on the drop-down arrow at the end, you can choose another open workbook or a new workbook. In the latter case, a new workbook is created.
  • Before sheet - Choose the location in the destination workbook.
  • Create a copy - When this choice is selected, then a copy of the worksheet is created. If not checked the worksheet is moved to the new location

2.7 Mouse selections

Commonly, you want to perform one command on several cells. For example, if you want to centralize the text in some cells, or change the font of these cells. Therefore you must get a good routine selecting multiple cells at the same time. In Table 2.1 you can see the most common selections.

Table 2.1: Most common mouse selections.
desired selection method
single cell Click on the cell.
range of cells Click the first cell in the range and then drag it to the last cell.
single column Click on the column letter.
adjacent columns Click on the first column letter and drag it to the last column letter.
single row Click on the row number.
adjacent rows Click on the first row number and drag it to the last row number.
all cells in worksheet Click on the button, above row number 1, and left from column A, or Shortcut CTRL-A

When selecting, you can also use the Shift key or the [CTRL key**, with the following effects:

SHIFT
Using the SHIFT key you can select an adjacent area.
  • For a rectangular area, click on one of the corner cells, hold down the SHIFT key and click on the cell in the diagonally opposite corner.
  • For adjacent columns, select the first column, hold down the SHIFT key while selecting the last column.
  • For adjacent rows, select the first row, hold down the SHIFT key while you select the last row.
CTRL
Using the CTRL key, you can select non-adjacent areas. Select the first cell, area, row or column, hold down CTRL key while you select the other cells, areas, rows, and columns.

  1. Selections are only possible when the mouse pointer has the shape for making selections (thick plus sign) Mouse selection shape.

  2. A selected area is highlighted in color except for the cell where you started the selection. This cell is the active cell and has a white background.

  3. Dragging from bottom to top and from right to left is easier than doing it in the reverse direction.

Indicating areas

You can use cell addresses for indicating adjacent areas. A few examples:

  • A2: C7 is a rectangular area of cells of A2 through C7. You always start with the cell address of the upper left corner, followed by a colon and then the address of the bottom right corner.
  • B:E are columns B through E.
  • 3:9 are rows 3 through 9.

2.8 Cell: content and format

A cell has a content and a format. It is very important to keep this difference in mind! A poor understanding of this is often the source of errors.

Example 2.1 Content - Format

In the cells A1:A10 the same number 40299 is always entered. So these cells have the same content. Yet they still look different and that’s because the formatting of these cells is different.

Figure 2.3: Example of cells with the same content and different formatting.

Looking at the figure it is difficult to see what the format and what the content is. Content and formatting are two different things.

Make sure that you don’t type the formatting when entering data in a cell.

For example, if you type 40.299 hours in cell A9, then it is considered as text and not as a number. And because it is a text you cannot calculate with the content.

It is possible to only enter the number and then format the cell so that it has the appearance as shown. The content of the cell is then a number with which you can make calculations.

The preferred method is to first enter the content in the cell and give the cell the desired format after that.

2.9 Printing worksheets

Things that must be done to make a good print of a worksheet.

Most worksheets with calculation models will be printed. When printing, you have many options available to customize the page layout. For example, you can choose headers and footers, margins, portrait or landscape orientation, the layout of the pages, etc.

It is recommended that before you print, you should first take a look at the print preview on the screen. From there you can then change several settings before the actual printing.

For making a print choose File > Print.

From here you can select what printer to use and what worksheets to print. By default, only the selected worksheet will be printed. But you can also choose to print multiple worksheets and even the entire workbook.

2.9.1 Print Preview

The preview shows you how the printed page will look like and gives you options to change several settings.

To judge if a print will look good, it is recommended to look at the print preview first. From there you can easily change some print settings.

Task 2.6 File: Personnel.xlsx

  1. Open the file.

  2. Kies File > Print.

A preview of the print and possibilities to change settings are displayed.

Figure 2.4: Print Preview.

  • If you want to see the margins, then click on the button Show Margins at the bottom right corner of the window.

  • By clicking on Page Setup you get a dialog box with tabs with a lot of print options.

2.9.2 Page Setup

By clicking on Page Setup you get a dialog box with four tabs each with a lot of print options. The most common options are discussed in this section.

Page

Figure 2.5: Settings for the page.

The page orientation is important: Portrait of Landscape.

Very useful are the choices in the area under Scaling. Through Adjust to you can manually customize the number of pages. It is also possible to let Excel do this automatically through Fit to by specifying how many pages the printout should have.

Margins

Figure 2.6: Settings for the page margins.

Here you can set the dimensions for the top-, bottom-, left- and right margin. Furthermore, the distance to the edge of the header and footer can be specified. These distances should be less than the corresponding margins otherwise the headers and footers overlap with the regular print.

Header/Footer

Figure 2.7: Settings for header and footer.

By clicking the arrow at the end of the header and footer boxes you can select from a set of predefined texts. Here you have also the choice (none) if you don’t want a header or footer. If you want to design your custom header or footer, then click the button for the custom text. You’ll get a dialog box where you can type text and format it.

Content and format of header and footer. Headers and footers have three sections each (left, center, and right). In each section, you can enter your text. You can also enter predefined content through the available buttons, such as:

  • Page Number
  • Number of Pages
  • Date and Time
  • File Path, File Name, and the Sheet Name

Sheet

Figure 2.8: Settings for the sheet.

Often there are titles in the first row(s) or column(s) of a worksheet. And if the worksheet is bigger than one page, you can indicate how that row( s) and / or column(s ) should appear on each page.

An interesting option is that you can indicate that the grid lines of the worksheet should be printed.

The settings of the page setup are also accessible through the menu Page Layout > group Page Setup.

2.9.3 Page Breaks

There may be only a certain number of rows and columns that could be printed on one sheet. Excel automatically adds horizontal and vertical page breaks to divide the sheet into appropriate pages. Where these page breaks depend on many factors such as paper size, margins, row heights, column widths, font, .. You can also determine yourself where pages should be started by insert, move or delete page breaks.

Page Preview

Although you can work with page breaks in Normal view, the best way is to use the Page Break Preview. In this view, you can see how choices that you can make (such as page orientation and changes of formatting) affect the automatic page breaks. For example, you can see how a change of row height or column width affects the placement of the automatic page breaks.

Choose View > Page Break Preview (group Workbook Views).

In this view, you can also see the order of the pages. Standard the order is first downwards and then sideways. This order can be changed using Page Setup.

To move page breaks, you can drag them with the mouse to the new location.

To switch back to the Normal view, choose View > Normal (group Workbook Views).

Inserting Page breaks

Page breaks can also be applied manually. To do this, follow these steps.

  • Horizontal page break: Select the row that has to start on a new page.

  • Vertical page break: Select the column that has to start on a new page.

Then choose Page Layout > Breaks (group Page Setup) > Insert Page Break.

Deleting Page breaks

The automatically generated page breaks cannot be removed. Only the manually applied page breaks can be removed. To do this, follow these steps.

  • Horizontal page break: Select the row below the page break.

  • Vertical page break: Select the column at the right from the page break.

Then choose Page Layout > Breaks (group Page Setup) > Remove Page Break.

2.10 Shortcuts cell movements

Table 2.2: Shortcuts for cell movements.
Shortcut Active cell becomes
Arrow Up one cell up
Arrow Right one cell right
Arrow Down one cell down
Arrow Left one cell left
CTRL arrow right right-most cell of the current data region, otherwise the last cell in the row
CTRL arrow left left-most cell of the current data region, otherwise the first cell in the row
CTRL arrow up top-most cell of the current data region, otherwise the first cell in the column
CTRL arrow down bottom-most cell of the current data region, otherwise the last cell in the column
Home first cell in the row
CTRL Home first cell in sheet, A1