Navigating within a worksheet

Getting started with Excel

Excel and Word have a lot in common, since they both belong to the MS Office suite of programs. This means that if you are familiar with Word, then you already know how to use several Excel features!

In the Word section of this manual, you’ll be able to find more information and guidance on

- Using the mouse and keyboard

- Starting the program

- The Office button and ribbon

- Character formatting

- Opening, saving and printing files

- Accessing Help

What is Excel?

Excel is all about numbers! There’s almost no limit to what you can do with numbers in Excel, including sorting, advanced calculations, and graphing. In addition, Excel’s formatting options mean that whatever you do with your numbers, the result will always look professional!

Data files created with Excel are called workbooks (in the same way as Word files are called documents). But where Word starts up with a single blank page, Excel files by default contain three blank worksheets. This gives you the flexibility to store related data in different locations within the same file. More worksheets can be added, and others deleted, as required.

You’ll often hear Excel files referred to as spreadsheets. This is a generic term, which sometimes means a workbook (file) and sometimes means a worksheet (a page within the file). For the sake of clarity, I’ll be using the terms workbook and worksheet in this manual.

The Excel 2007 window

As in Word 2007, the old menu system has been replaced by the Ribbon and the Office button. The title bar displays the name of your current workbook. Tabs at the bottom of the screen identify the different worksheets available to you - I’ll show you a little later how to give them meaningful names.

Navigating within a worksheet - №1 - открытая онлайн библиотека

Notice how the working area of the screen is divided into rows (1, 2, 3, 4, ...) and columns (A, B, C, D, …). Together these provide an address, such a C10 or G21, that uniquely identifies each cell in the worksheet. A range of cells extends in a rectangle from one cell to another, and is referred to by using the first and last cell addresses separated by a colon. For example, the group of cells from A3 to G4 would be written as A3:G4.

In the example above, the current or active cell is B7. It is surrounded by a heavy black border, and its address is displayed in the name box above column A. Its row and column numbers are also highlighted.

On the right of the name box is the formula bar. This displays the value stored in the active cell, and is also the place where you would enter a new data value or formula into that cell.

Starting Excel

If you have an icon on the desktop for Excel, then all you have to do is double-click it to open Excel.

Alternatively, click the Start button and then select All Programs, Microsoft Office, Navigating within a worksheet - №2 - открытая онлайн библиотека Microsoft Excel.

Navigating within a worksheet - №3 - открытая онлайн библиотека

-When you open Excel from a desktop icon or from the Start menu, a new empty workbook (consisting of three worksheets) will be displayed on your screen.

-If you double-click on an existing Excel file from inside the Windows Explorer
window, then Excel will open and display the selected file on your screen.

Closing Excel

Close Excel by clicking the X on the far right of the title bar. Navigating within a worksheet - №4 - открытая онлайн библиотека

Navigating within a worksheet

Using the mouse:

-Use the vertical and horizontal scroll bars if you want to move to an area of the
screen that is not currently visible.

-To move to a different worksheet, just click on the tab below the worksheet.
Using the keyboard:

- Use the arrow keys, or [PAGE UP] and [PAGE DOWN], to move to a different area of the screen.

- [CTRL] + [HOME} will take you to cell A1.

-[CTRL] + [PAGE DOWN] will take you to the next worksheet, or use [CTRL] +
[PAGE UP] for the preceding worksheet.

You can jump quickly to a specific cell by pressing [F5] and typing in the cell address. You can also type the cell address in the name box above column A, and press [ENTER].

Selecting cells

Using the mouse:

- Click on a cell to select it.

- You can select a range of adjacent cells by clicking on the first one, and then dragging the mouse over the others.

- You can select a set of non-adjacent cells by clicking on the first one, and then holding down the [CTRL] key as you click on the others.

Using the keyboard:

- Use the arrow keys to move to the desired cell, which is automatically selected.

- To select multiple cells, hold down the [SHIFT] key while the first cell is active, and then use the arrow keys to select the rest of the range.

Navigating within a worksheet - №5 - открытая онлайн библиотека

Cut or Copy

1. Select a region.

2. Click the Cutor Copybutton in the Clipboard group in the Ribbon’s Home tab.

Paste

1. When there is material on the Clipboard, select a destination cell or region.

2. Click the Pastebutton in the Clipboard group in the Ribbon’s Home tab.

Navigating within a worksheet - №6 - открытая онлайн библиотека

Selecting rows or columns

To select all the cells in a particular row, just click on the row number (1, 2, 3, etc) at the left edge of the worksheet. Hold down the mouse button and drag across row numbers to select multiple adjacent rows. Hold down [CTRL] if you want to select a set of non-adjacent rows.

Similarly, to select all the cells in column, you should click on the column heading (A, B, C, etc) at the top edge of the worksheet. Hold down the mouse button and drag across column headings to select multiple adjacent columns. Hold down [CTRL] if you want to select a set of non-adjacent columns.

You can quickly select all the cells in a worksheet by clicking the square to the immediate left of the Column A heading (just above the label for Row 1).

Navigating within a worksheet - №7 - открытая онлайн библиотека

Entering data

First you need a workbook

Before you start entering data, you need to decide whether this is a completely new project deserving a workbook of its own, or whether the data you are going to enter relates to an existing workbook. Remember that you can always add a new worksheet to an existing workbook, and you’ll find it much easier to work with related data if it’s all stored in the same file.

If you need to create a new workbook from inside Excel:

1. Click on the Office button, select New and then Blank Workbook.

2. Sheet 1 of a new workbook will be displayed on your screen, with cell A1 active. To open an existing workbook from inside Excel:

1. Click on the Office button, click Open, and then navigate to the drive and folder containing the file you want to open.

2. Double-click on the required file name.

Overview of data types

Excel allows you to enter different sorts of data into the cells on a worksheet, such as dates, text, and numbers. If you understand how Excel treats the different types of data, you’ll be able to structure your worksheet as efficiently as possible.

- Numberslie at the heart of Excel’s functionality. They can be formatted in a
variety of different ways - we’ll get to that later. You should generally avoid
mixing text and numbers in a single cell, since Excel will regard the cell contents
as text, and won’t include the embedded number in calculations. If you type any
spaces within a number, it will also be regarded as text.

Navigating within a worksheet - №8 - открытая онлайн библиотека

Note that dates and times are stored as numbers in Excel, so that you can calculate the difference between two dates. However, they are usually displayed as if they are text.

If a number is too large to be displayed in the current cell, it will be displayed as “#####”. The formatting section of this manual explains how to widen a column.

- Textconsist mainly of alphabetic characters, but can also include numbers,
punctuation marks and special characters (like the check mark in the example
above). Text fields are not included in numeric calculations. If you want Excel to
treat an apparent number as text, then you should precede the number with a
single quotation mark (‘). This can be useful when entering for example a phone
number that starts with 0, since leading zeros are not usually displayed for Excel
numbers.

If a text field is too long to be displayed in the current cell, it will spill over into the next cell if that cell is empty, otherwise it will be truncated at the cell border. The formatting section of this manual explains how to wrap text within a cell.

- Formulasare the most powerful elements of an Excel spreadsheet. Every
formula starts with an “=” sign, and contains at least one logical or mathematical
operation (or special function), combined with numbers and/or cell references.
We’ll discuss formulas and functions in more detail later in the manual.

Data entry cell by cell

To enter either numbers or text:

1. Click on the cell where you want the data to be stored, so that the cell becomes active.

2. Type the number or text.

3. Press [ENTER] to move to the next row, or [TAB] to move to the next column. Until you’ve pressed [ENTER] or [TAB], you can cancel the data entry by pressing [ESC].

To enter a date, use a slash or hyphen between the day, month and year, for example 14/02/2009. Use a colon between hours, minutes and seconds, for example 13:45:20.

Remember that useful Undo button on the Quick Access toolbar!

Deleting data

You want to delete data that’s already been entered in a worksheet? Simple!

1. Select the cell or cells containing data to be deleted.

2. Press the [DEL] key on your keyboard.

3. The cells remain in the same position as before, but their contents are deleted.

Moving data

You’ve already entered some data, and want to move it to a different area on the worksheet?

1. Select the cells you want to move (they will become highlighted).

2. Navigating within a worksheet - №9 - открытая онлайн библиотека Move the cursor to the border of the highlighted cells. When the cursor changes from a white cross to a four-headed arrow (the move pointer), hold down the left mouse button.

3. Drag the selected cells to a new area of the worksheet, then release the mouse button.

You can also cut the selected data using the ribbon icon or [CTRL] + [X], then click in the top left cell of the destination area and paste the data with the ribbon icon or [CTRL] + [V].

Copying data

To copy existing cell contents to another area on the worksheet:

1. Select the cells you want to copy (they will become highlighted).

2. Navigating within a worksheet - №10 - открытая онлайн библиотека Move the cursor to the border of the highlighted cells while holding down the [CTRL] key. When the cursor changes from a white cross to a hollow left-pointing arrow (the copy pointer), hold down the left mouse button.

3. Drag the selected cells to a second area of the worksheet, then release the mouse button.

You can also copy the selected data using the ribbon icon or [CTRL] + [C], then click in the top left cell of the destination area and paste the data with the ribbon icon or [CTRL] + [V].

Navigating within a worksheet - №11 - открытая онлайн библиотека To copy the contents of one cell to a set of adjacent cells, select the initial cell and then move the cursor over the small square in the bottom right-hand corner (the fill handle). The cursor will change from a white cross to a black cross. Hold down the mouse button and drag to a range of adjacent cells. The initial cell contents will be copied to the other cells. Note that if the original cell contents end with a number, then the number will be incremented in the copied cells.

If the original cell that you are moving or copying contains a reference to a cell address, then the copied cell address will be adjusted relative to the target cell. Refer to Formulas – Referencing later in this manual for details.

Using Autofill

This is one of Excel’s niftiest features! It takes no effort at all to repeat a data series (such as the days of the week, months of the year, or a numbers series such as odd numbers) over a range of cells.

1. Enter the start of the series into a few adjacent cells (enough to show the underlying pattern).

2. Select the cells that contain series data.

3. Move the cursor over the small square in the bottom right-hand corner of the selection (the fill handle). Hold down the mouse button and drag to a range of adjacent cells.

4. The target cells will be filled based on the pattern of the original series cells.

Saving a workbook

So now it’s time to save your work. As usual, you need to specify the file name, and its location (drive and folder).

1. Click the Office button and select Save, or click the Save icon on the Quick Access toolbar. If this workbook has been saved before, then that’s it – your workbook will be saved again with the same name and location.

2. If it’s the first time of saving this workbook, then the Save As dialogue box will open.

3. Click the drop-down arrow next to Save In to select the desired drive and folder.

4. Type the new file name in the File Name field.

5. Click the Save button.

Every time you Save or Save As in Excel, the entire workbook is saved. This is another good reason for keeping related data on different worksheets in the same workbook! When you’re working in the UCT computer labs, remember to save to the F: drive, so that you’ll be able to access your work again later.

Editing data

In data entry mode, when you move the cursor to a new cell, anything you type replaces the previous cell contents. Edit mode allow you to amend existing cell contents without having to retype the entire entry. Note that while you are in edit mode, many of the Ribbon commands are disabled.

Editing cell contents

There are two different ways to enter edit mode: either double-click on the cell whose contents you want to edit, or else click to select the cell you want to edit, and then click anywhere in the formula bar.

- To delete characters, use the [BACKSPACE] or [DEL] key.

- To insert characters, click where you want to insert them, and then type. You can toggle between insert and overtype mode by pressing the [INSERT] key.

-You can force a line break within the current cell contents by typing [ALT] +
[ENTER].

Exit edit mode by pressing [ENTER].

Your cell contents look correct in the formula bar, but don’t display correctly in the worksheet? Check whether either of these common problems is the culprit: * If a cell contains a number but displays #####, then the column is not wide enough to show the full data value. You need to make the column wider (see formatting). * If a cell contains text but chops off the display at the edge of the column, then you need to either widen the column or wrap the text within the column (see formatting).

Navigating within a worksheet - №12 - открытая онлайн библиотека Inserting or deleting cells

You can insert a new cell above the current active cell, in which case the active cell and those below it will each move down one row. You can also insert a new cell to the left of the current active cell, in which case the active cell and those on its right will each move one column to the right.

To insert a cell:

1. Select the cell next to which you want to insert a new cell.

2. On the Home ribbon, find the Cells group and click Insert followed by Insert Cells.

Navigating within a worksheet - №13 - открытая онлайн библиотека

3. A dialog box will open. Click the direction in which you want the surrounding cells to
shift.

Navigating within a worksheet - №14 - открытая онлайн библиотека

To delete a cell, do as follows:

1. Select the cell that you want to delete.

2. On the Home ribbon, find the Cells group and click Delete followed by Delete Cells.

Navigating within a worksheet - №15 - открытая онлайн библиотека

3. A dialog box will open. Click the direction in which you want the surrounding cells to shift.

You can also right-click on the active cell and select Insert or Delete on the pop-up menu.