Microsoft Excel 2003

TABLE OF CONTENTS

1.0 INTRODUCTION... 1

1.1 WHAT ISMICROSOFTEXCEL?...1

1.2 FEATURES OFMICROSOFTEXCEL... 1

1.3 STARTINGMICROSOFTEXCEL... 1

1.4 THELAYOUT OF THEEXCELSCREEN... 2

1.5 THEWORKSHEETWINDOW... 3

1.6 THEMENUS ANDTOOLBARS... 4

1.7 USING THEKEYBOARD... 4

1.8 MOVINGAROUNDUSING THEKEYBOARD... 5

1.9 MOVINGAROUNDUSING THEMOUSE... 6

1.10 EXITEXCEL... 7

1.11 EXERCISE1...8

2.0 MANAGING WORKBOOKS AND WORKSHEETS... 9

2.1 CREATE AWORKBOOK... 9

2.1.1 Workbook Structure...9

2.1.2 Creating a New Workbook ...9

2.1.3 Perform Calculations ...10

2.1.4 Mathematical Operators ...11

2.1.5 AutoSum...12

2.2 SAVE, OPEN ANDCLOSEWORKBOOKS... 12

2.2.1 Saving a New Workbook ...12

2.2.2 Saving a Named Workbook...13

2.2.3 Closing a Workbook ...13

2.2.4 Starting a New Workbook...14

2.2.5 Opening an Existing Workbook...15

2.3 WORKING WITH AWORKBOOK... 15

2.3.1 Using Sheets...15

2.3.2 Renaming Sheets ...16

2.3.3 Insert and Delete Sheets ...17

2.3.4 Copy and Move Sheets ...18

2.3.5 Group Sheets...19

2.4 FORMATTINGWORKSHEETS... 19

2.4.1 Long Labels ...19

2.4.2 Change Column Width ...20

2.4.3 Change Row Height ...21

2.4.4 Insert Rows and Columns...22

2.4.5 Delete Rows and Columns...22

2.4.6 Hide/Unhide Rows and Columns...23

2.5 EXERCISE2...24

3.0 WORKING WITH DATA & CELLS... 25

3.1 ENTERINGDATA INTOCELLS... 25

3.1.1 Types of Data...25

3.1.2 Entering Data into any Cell ...25

3.1.3 AutoFill...25 3.2 EDITINGDATA... 26 3.2.1 Overtyping ...26 3.2.2 Undo/Redo ...27 3.2.3 Editing Cells...27 3.2.4 Erasing Data...28

3.3.1 Selecting Cells using the Mouse ...30

3.3.2 Using the Fill Handle...31

3.3.3 Filling a Range...31

3.3.4 Copy Formulas ...32

3.4 COPYING... 33

3.4.1 Copy Cells by Dragging...33

3.4.2 Copy Ranges ...34

3.4.3 Move & Insert Cells by Dragging ...34

3.5 EXERCISE3...35

4.0 WORKSHEET FORMATTING ... 36

4.1 FORMATTINGCELLS... 36

4.1.1 Bold, Italic and Underline ...36

4.1.2 Change Font and Font Size ...37

4.1.3 Format Numbers...38

4.1.4 Date and Time ...38

4.1.5 Alignment...39

4.1.6 Text Wrap...39

4.1.7 Merge and Shrink to Fit...40

4.1.8 Text Orientation...41 4.1.9 Text Colour...41 4.1.10 Cell Borders...42 4.1.11 Cell Shading ...43 4.2 CELLREFERENCING... 44 4.2.1 Relative Referencing ...44 4.2.2 Absolute Referencing ...45 4.2.3 Mixed Referencing...45 4.3 CELLCOMMENTS... 45 4.3.1 Cell Comments...45 4.3.2 Display Comments...46

4.3.3 Edit & Delete Comments...47

4.3.4 Format Comments...47 4.3.5 Print Comments ...48 4.4 WORKSHEETAPPEARANCE... 49 4.4.1 Zoom ...49 4.4.2 Multiple Windows ...49 4.4.3 Hide Windows ...50 4.4.4 Freeze Panes...50 4.4.5 Split...51 4.4.6 Outline...52 4.5 EXERCISE4...53

5.0 USING EXCEL FORMULAS & FUNCTIONS... 55

5.1 FORMULAS... 55

5.1.1 Percentages...55

5.1.2 Cumulative Totals ...55

5.1.3 Checking Formulas...56

5.1.4 Formulas that Produce Errors...57

5.2 FUNCTIONS... 57

5.2.1 Functions ...57

5.2.2 Insert Function ...58

5.2.3 Common Functions ...59

5.3 TEXTFUNCTIONS... 63

5.3.1 Left, Right and Mid...63

6.0 CHARTS... 68 6.1 CHARTS... 68 6.2 CREATINGCHARTS... 68 6.3 EMBEDDEDCHARTS... 71 6.4FORMATTING CHARTS... 72 6.5 PRINTINGCHARTS... 73

6.6 CHARTMENUOPTIONS... 74

6.7 TRENDLINES...75

6.8 3DCHARTVIEW... 77

6.9 DATALABELS ANDMARKERS... 78

6.10 STACKEDBARS... 79

6.11 Protecting Excel Files ... 80

EXERCISE6...80

7.0 BUILDING AN EXCEL DATABASE... 83

7.1 BASICDATABASECONCEPTS... 83

7.1.1 Creating a Database ...84

7.1.2 Entering and Adding Data ...84

7.1.3 Searching for Data ...87

7.1.4 Saving the Database...91

7.2 WORKING WITHYOURDATABASE... 92

7.2.1 Working with Subtotals...92

7.2.2 Using Advanced AutoFilter Functions...95

7.3 PIVOTTABLES ANDPIVOTCHARTS... 98

7.3.1 What Is a Pivot Table?...98

7.3.2 Terms used in PivotTable ...98

7.3.3 Creating a Pivot Table or Chart ...99

7.3.4 To Change the Way the Data Is Displayed In the Pivot Table...101

7.3.5 To Update the Pivot Table...101

7.3.6 TO Delete a Pivot Table...102

7.3.7 USER-defined chart formats ...102

7.4 EXERCISE7...104

8.0 LINKING AND EMBEDDING ... 105

8.1 LINKING... 105 8.2 LINKING ARANGE... 106 8.3 EMBEDDINGOBJECTS... 107 9.0 PROTECTION ... 109 9.1 PROTECTION... 109 9.2 CELLPROTECTION... 109 9.3 HIDEDATA... 111

9.4 HIDEROWS/ COLUMNS... 112

9.5 WORKBOOKPROTECTION... 112

9.6 READ-ONLYWORKBOOKS... 114

10.0 MACROS ... 115

10.1 MACROS... 115

10.2 RECORD AMACRO... 115

10.3 ASSIGNMACRO TOA TOOLBARBUTTON... 117

10.4 ASSIGNMACRO TO AMENU... 118

10.5 EDITMACROS... 119

11.0 PRINTING... 124

11.1 PRINTINGGENERAL... 124

11.2 CHANGING THEPAGELAYOUT... 124

11.3 CHANGINGHEADERS ANDFOOTERS... 126

11.4 PRINTPREVIEW... 127

11.5 PAGEBREAKPREVIEW... 127

11.6 ADJUST ANDPRINTTOFIT... 128

1.0 INTRODUCTION

MS-Excel is one of the most commonly used spreadsheet application

software. A spreadsheet application is a computer program created specifically to help in the processing of tabular information, usually numbers. The spreadsheet stores information in rows (across the screen) and columns (down the screen), forming a worksheet (the Excel term for a spreadsheet). Any job that involves the use of numbers such as

 Accounting

 Cash flows

 Budgets

 Forecasts, etc can be done on a spreadsheet.

The biggest advantage that a spreadsheet has over other methods of manipulating data is its ability to constantly update figures without the user having to do any additional calculations. Once a spreadsheet is set up, its calculations will always be correct and any changes in data are recalculated automatically.

Spreadsheets can also take raw data and present it in an attractive way, with formatted tables and graphs.

1.2 Features of Microsoft Excel

Microsoft Excel has three major features. These are:

 Worksheet feature: Excel workspace with gridlines that helps you

enter, manipulate and calculate data.

 Chart feature: Enables you to store the analyzed numeric data

graphically on a worksheet.

 Database: Enables you to perform tasks that require maintaining

and manipulating a collection of huge data or information

1.3 Starting Microsoft Excel

There are numerous ways to start Excel. The following method is recommended for beginners. Starting the computer will automatically show the Windows XP desktop.

 Click once on Start to show the list of start options available. All Windows XP applications are started here.

 Move to All Programs, Microsoft office and Click on Microsoft

1.4 The Layout of the Excel Screen

Title Bar - Indicates application and current workbook in use

Standard Toolbar - Quick access to basic features of Excel

Formatting Toolbar - Quick access to change appearance of cells

Formula Bar - Indicates active cell address and displays cell contents

Scroll Bars - Allows quick movement around the worksheet

Active Cell - Position to insert information

Status Bar - Displays information about the selected command and to the right, the state of certain keys e.g. CAPS, NUM, etc. 1.5 The Worksheet Window

The diagram shows a workbook that has 3 sheets (the default number of sheets that a new workbook has).

 Excel stores information in workbooks. Each workbook contains 3 worksheets by default, but can contain up to 255.

 Each worksheet has 256 columns and 65536 rows. The columns are denoted by letters, A, B, C,…, Z,AA, AB,… CV and the rows are denoted by numbers 1, 2, 3,… ,65536.

 These letters and numbers are shown in the Row and Column Headings on the worksheet.

 The point where a column and row intersect is known as a Cell. Each cell is identified by the column letter and row number which forms the intersection, e.g. the cell formed by the intersection of column D and row 8 is known as cell D8.

 There are Scroll bars at the right and bottom edges of the worksheet which are used to display different areas of the worksheet.

 Each worksheet in the workbook has a name. At the moment they are named Sheet1, Sheet2, and Sheet3. Each one has a Sheet

Tab.

 There are buttons to the left of Sheet1 that are used to display sheets when there are more in the workbook than can be displayed in the space.

1.6 The Menus and Toolbars

The Menus and Toolbars are displayed at the top of the screen.

 To open a menu, move the mouse pointer to the menu bar, position it directly over the menu name and click once. A list of commands will appear as a drop down menu. Some command names may appear as dim, pale, grey (ghosted) text. This indicates that these commands are not available for selection at the moment.

 Three dots after the command indicate that further selections are available.

 To select a command from the drop down menu, simply click on it.

 Toolbars allow quick access to commands. When moving the

mouse pointer over the buttons on the Toolbar, Excel displays the

button name as a ToolTip. Clicking on the button with the mouse carries out the task.

1.7 Using the Keyboard

the keyboard. To open a menu using the keyboard:

 Activate the menu bar by pressing <Alt>.

 The File menu will appear as a

button to indicate the file menu is active.

 To display the menu, press the letter that is underlined in the menu name.

 In the same way, choose a command by pressing the underlined letter in the command name.

 To close a menu, press <Esc>. The menu bar remains active. Press

<Esc> to return to the worksheet.



TIP

can be chosen without having to display a menu. In these cases, the key presses are displayed in the menu, alongside the command name. 1.8 Moving Around Using the Keyboard

This Course Notes package will concentrate on using the mouse to access the commands and to move around the screen, but the keyboard can be used instead.

 The Current or Active cell is shown on the screen by a dark border. The

Active Cell Reference is shown in the Formula Bar. The active cell can

be moved about the worksheet using various key presses. The keys are:

Active Cell Reference

< Arrow keys > Move one cell up, down, left, or right. < TAB > Move one cell to the right.

< SHIFT+TAB > Move one cell to the left

< CTRL+ arrow key > Move to the edge of the current data region < HOME > Move to the beginning of the row

< CTRL+HOME > Move to the beginning of the worksheet < CTRL+END > Move to the last cell on the worksheet, in the

bottom-most used row of the rightmost used column.

< PAGE DOWN > Move down one screen < PAGE UP > Move up one screen

< ALT+PAGE DOWN > Move one screen to the right < ALT+PAGE UP > Move one screen to the left

<F5> allows the user to move directly to a specific cell

1.9 Moving Around Using the Mouse

The mouse provides an easy way of moving around the worksheet and accessing the menus and buttons. To work on a different cell:

 Click on a cell to make it the Active Cell

The screen has the usual Windows scroll bars, which can be used to scroll around the worksheet. The extent to which it is possible to scroll depends upon the size of the worksheet.

 Clicking on the scroll bar arrows will move the worksheet along or up/down by one column/row.

Horizontal Scroll Bar

Vertical Scroll Bar

 Clicking on the actual scroll bar scrolls down/up or left/right by one screen.

 The usual method of moving around the screen is by a combination of key presses and mouse movements.

 Small movements around the worksheet are usually achieved using the keys, when moving to a different area, the mouse and scroll bars are used.

1.10 Exit Excel

 From the Menu Bar click on File. A drop down menu will appear.

 Place the mouse pointer over Exit and click once.

Note: When exiting from Excel, if any workbooks are still open and have not

been saved, Excel will display a warning and will ask if they require saving.



TIP

1.11 Exercise 1

1. Switch on your computer and start MS-Excel.

2. Identify the following components of Excel program window:

Title Bar, Menu Bar, Toolbar, Formula Bar, Row Heading, Column Heading, Cell Pointer, Name Box, Tab Scrolling arrows, Vertical Scroll Bar and Scrolling arrow, Horizontal Scroll bar and Scrolling arrow

3. How many rows and columns can you have in a single worksheet? 4. How many cells do you observe in a single worksheet?

2.0 MANAGING WORKBOOKS AND WORKSHEETS

2.1.1 Workbook Structure

A spreadsheet in Excel is called a Workbook. A spreadsheet model is a block of occupied cells.

Cells within an Excel worksheet can contain Text (Labels), Numbers (Values) or Formulas (calculations from number cells). Labels are normally used for describing the contents of the worksheet, as column or row titles for example, whereas values are used for calculations. A typical model has no blank rows or columns within it, the relationship between Text, Numbers and Formulas is shown below (remember this is a typical example - cell contents can be arranged in any way).

2.1.2 Creating a New Workbook

A workbook is automatically opened when Excel starts and is named

Book1. A workbook is made up of separate spreadsheets bound

together called worksheets (sheets). Each workbook can contain as many as 255 sheets (the default is 3, named Sheet1, Sheet2, etc.).

When creating a spreadsheet model start on Sheet1 (the default) and begin by using the top left corner. Normally a Title is entered in cell A1 and the main block starts in either A2 or A3.

Entering information into a cell

typing. When information is entered the text appears in the formula bar as well as in the cell.

To complete an entry either use <Enter>, the cursor movement keys (arrow keys), click on the enter box in the formula bar, or click on another cell. Using <Enter> may move down a cell or stay on the same cell, whereas, the arrow keys allow movement in any direction, ready for the next entry.

Labels

Labels are entered as text and are usually placed down column A and across row 2 or 3 from column B. Text by default is aligned to the left (placed at the left edge of the cell). If the text you type does not fit then the size of the text or the size of the cell can be changed (covered later in Formatting).

Numbers

Select the cell, type the number and complete the entry with any of the methods described above. Numbers are right aligned (placed flush to the right edge of the cell).



TIP

e.g. ‘0786. The entry is now treated as text and calculations can be performed on it without modification. Trailing zeros in decimals are displayed via formatting.

2.1.3 Perform Calculations

A calculation in Excel is called a Formula and is a method of calculating answers from numbers elsewhere on the sheet, eg. sales for the year, net profit in a month, overall profitability of the company.

All formulas begin with an equals sign (=), followed by the calculation, this could be:

=345+45

but normally information from numeric cells is used. When using other cells their cell address is given, e.g. B7, F23. To create a formula in B7 to add two numbers placed in B5 and B6, click on B7 and type

The solution is placed in cell B7, the formula bar displays the formula that generated the answer.

Pointing

A better way to enter cell addresses is not to type them but use the mouse and click on them. The above formula is entered as = (point and click on cell B5)+ (point and click on cell B6) <Enter>.

This method is better because fewer mistakes are made with addresses and typing.

Re-Calculation

The real power of a spreadsheet becomes apparent when the contained data is changed. If the source data changes, the formulas are automatically recalculated, so that the user need never worry about the results and totals being up to date.

2.1.4 Mathematical Operators

The basic mathematical operators are add, subtract, multiply and divide. The symbols on a keyboard are slightly different to those used normally and are:

+ Add

- Subtract

* Multiply

/ Divide

These symbols appear twice on a keyboard, one set placed around the main keyboard and the other set on the numeric keypad (right side). The keypad is easier to use because the keys are closer together and the <Shift> key is not used.

Note: To use the numeric keypad theNum Lock must be active.

Other mathematical operations are used via Functions, covered later.

Adding brackets

When more than one operator is used in one formula then the order becomes important, e.g. D23+E17/E19. Excel performs Brackets first then Division, Multiplication, Addition and finally Subtraction (the

Formula bar displays formula

BODMAS theory).

The numerical example 6+4/2 gives the answer 8, when you may want, and expect, the answer, 5.

Brackets can be added to force the program to perform calculations in a different order to normal. In the example above the formula becomes:

= (6 + 4) / 2 displays 5

Note: The numbers in the above example would normally be replaced

by cell references.

2.1.5 AutoSum

One of the most important functions in Excel is SUM and because of this there is a button on the toolbar to sum automatically.

This feature only works without alteration when a list of numbers is already entered into a range. Spaces cause AutoSum to shorten the range.

 Move to the cell where the sum is required.

 Click .

 Adjust the range if not correct (the range is highlighted in black, ready to be overwritten).

 Press <Enter> to complete the entry.

Note: AutoSum sums left or up depending on where the figures are located.

If AutoSum has figures in both directions it will sum up by default.

2.2 Save, Open and Close Workbooks 2.2.1 Saving a New Workbook

After creating a spreadsheet it needs to be saved. Use Save As... to save a newly created workbook, i.e. one that has not been named. When a workbook has already been saved, i.e. been given a name, use File |

 Select the File menu and choose the Save As... command.

 The Save As dialog box will then appear on the screen, in front of the current worksheet.

 Enter the name of the file in the File name box.

 Select the correct drive from the Save in: box.

 Click to save.

Note: When a new workbook is to be saved, selectingSave displays the same

dialog box as Save As.



TIP

folder using Windows Explorer and then within Excel select Tools |

Options | General tab and in the Default file location box type your new

folder. Click OK. 2.2.2 Saving a Named Workbook

 Select File | Save to update a document that has already been named.

Note: When using save either via the command or the button the workbook

is saved automatically, overwriting the previous version and with very little on-screen evidence. Some users prefer to use Save As for saving under the same name. Click Save and confirm the replacement by clicking Yes.



TIP

saving.

2.2.3 Closing a Workbook

 If the workbook has not been previously given a file name, or if it has been modified in any way, Excel displays a Message Box.

 Three options are given:

Yes displays the Save As dialog box if the workbook has just been created or automatically saves the workbook if it has been modified.

No closes without saving

Cancel returns to the active worksheet without closing or saving

2.2.4 Starting a New Workbook

A new workbook can be opened at any time within Excel:

 Select File | New the New dialog box is displayed, click OK.

 A new workbook is opened, pushing any other open workbooks to the back. The new workbook is named by default using the next number in the sequence e.g. Book2.

 Any other open workbooks can be brought to the front using the

Window command on the Menu bar and selecting from the open

workbook list at the bottom of the menu.

 The open workbooks are numbered, click on the one required, to display it.



TIP

Toolbar.

Open workbooks

2.2.5 Opening an Existing Workbook

Once a workbook has been created and saved, it can be opened at any time.

 To open an existing workbook, select the File | Open command. This will display the Open dialog box.

 Use the drop down list of Look in: to select the correct drive and folder, from which to open the document.

 Finally, click on the name of the file to be opened. This will now be highlighted.

 Click the Open button to open the file. (Double clicking the file name in the list also opens the file).

Opening recently used workbooks

The last four Workbooks that have been used by Excel are listed at the bottom of the File menu. Click once on the file name to open the file -the file must, of course, be on one of -the disks currently being used by the machine.



TIP

then displayed on the screen. Select the required drive, folder and finally the file name. Click Open to complete the process and open the workbook. 2.3 Working with a Workbook

2.3.1 Using Sheets

Workbooks are made up of worksheets named Sheet1, Sheet2, etc. The worksheet tabs are arranged across the bottom of the worksheet window.

First Left Right Last

By default there are only 3 sheets to a workbook. The maximum number of sheets that can be set in a workbook is 255.

To change the default number of sheets in a workbook

 Select Tools | Options and the General tab.

 Change the number within Sheets in new workbook.

 Click OK.

 Every new workbook started after this will contain the new amount of sheets.

Moving around the sheets

When more sheets are added or more descriptive (longer) sheet names are used they take up more space and can become hidden.

When this happens the movement buttons on the left are then used to show hidden sheet tabs.

2.3.2 Renaming Sheets

name adds to the understanding of the workbook. Sheet names can have up to 31 characters.

To rename a sheet

 Double click on the required sheet tab or select Format | Sheet |

Rename.

 Type the new name.

 Press <Enter> or click away from the sheet tab to complete the renaming.

Note: Sheet names can include punctuation e.g. spaces and be as descriptive

as required to relate to the contents.

2.3.3 Insert and Delete Sheets

Individual sheets can be added to, or deleted from, any workbook.

To insert a sheet

Either

 Select Insert | Worksheet. Or

 Right click on a sheet tab and select Insert.

To delete a sheet

 Click on the sheet tab to make the required sheet active and either

 Select Edit | Delete Sheet

Or Right click on the sheet tab to delete and select Delete.

 Confirm the deletion by clicking Delete.

2.3.4 Copy and Move Sheets

To move a sheet within the same workbook

 Click and drag the sheet tab horizontally to the new position . The cursor changes to a .

To copy a sheet within the same workbook

 Hold <Ctrl> and click and drag the sheet tab to another position.

Note: The copy uses the same sheet name but with (2) added as two sheets

cannot have the same name. Rename the copy. Making an identical copy is more efficient than copying data from one sheet to another.

To move a sheet to another workbook

 Right click on the sheet tab to move and select Move or Copy.

 In To book, select from the open workbooks in the drop down list (including a new book) to move the sheet.

 Click OK to complete the move.

To copy a sheet to another workbook

 Right click on the sheet tab to move and select Move or Copy.

 Check Create a copy.

 In To Book, select from the open workbooks in the drop the list (including a new book) to move the sheet.

2.3.5 Group Sheets

Several worksheets can be grouped and worked on together for creation or formatting purposes.

To group sheets

 To group all sheets, right click on any sheet tab and click Select

All Sheets

 To group a range of sheets, click on the first sheet tab required, hold <Shift> and click on the last sheet required in the group.

 To group individual sheets, click on the first sheet tab required, hold <Ctrl> and click on each sheet tab required in the group in turn.

Note: The title bar now displays[Group]

After sheets are grouped any actions are performed on all sheets in the group.

To de-select a group

 For all sheets in the group, right click a sheet tab and select

Ungroup Sheets.

 For a group not containing all sheets click on any sheet not in the group.

2.4 Formatting Worksheets 2.4.1 Long Labels

A long label is text that is wider than the cell into which it is entered. If adjacent cells are empty, then the text flows into them, as when entering titles.

If the adjacent cell is occupied, then the label is truncated (the cell only shows what it can). The label can still be seen in the formula bar.

Diagram shows Sheets 1 to 3 bound as a Group

To display long labels

 Widen the column so that the label fits Or

 Reduce the size of the font so that the label fits Or

 Apply the formatting features Word Wrap or Shrink to Fit (both covered in the next section).

2.4.2 Change Column Width

Individual column widths can be changed in a number of ways:

Using the menus

 Place cursor anywhere in the column to change.

 Select Format | Column | Width.

 Enter the column width in character units (the default column width is 8.43).

 Click OK.

Using the mouse to change column width

 Point in the Column Border, at the border between two columns, the mouse pointer changes to .

 Clicking and dragging to the left or right now alters the width of the column to the left (Take care when dragging left as width 0 hides the column).



TIP

To adjust multiple columns

 In the column headings, click and drag to select the required columns.

 Display the adjust cursor at any vertical column edge within the range.

 Click and drag one column border to change them all or double click to adjust all columns to the widest entry within each column.

2.4.3 Change Row Height

Row heights can be changed in exactly the same way as changing column widths, except the adjust cursor is between two rows and the adjustment changes the row above.

Using the menus

 Place cursor anywhere in the row or click the row number to change.

 Select Format | Row | Height.

 Enter the row height in point size (the default row height is 12.75).

 Click OK.

Using the mouse to change row height

 Point in the Row Border, at the division between two rows, the mouse pointer changes to .

 Clicking and dragging up or down now alters the height of the row above (careful when dragging up as a row can be hidden - 0 height).

T I P _{Double clicking with the adjust cursor displayed automatically}

adjusts the row above to the largest row height entry on that row.

To adjust multiple rows

 In the row headings, click and drag to select the required rows.

 Display the adjust cursor at any row horizontal edge within the range.

 Click and drag one row border to change them all or double click to adjust all rows to the highest entry.

Note: If row height has not been manually changed then an increase in font

2.4.4 Insert Rows and Columns

Rows and columns can be inserted into a worksheet between existing rows and columns. A problem will arise if a worksheet is fully developed with formulas in place. Rows or columns inserted at either end of a range, i.e. the first item or last items, will mean an adjustment of all the formulas.

Check all formulas after inserting

To insert entire rows or columns

 Click on a cell or a range of cells (remember new columns are inserted to left and new rows are inserted above).

 Select Insert and then either Rows or Columns.

To insert a group of cells

 Highlight the range of cells to insert.

 Select Insert | Cells.

 Choose the required option from Shift cells right and Shift cells

down.

 Click OK.

2.4.5 Delete Rows and Columns

To delete rows, columns or a range of cells

 Highlight the appropriate row/column/cells.

 Select Edit | Delete.

 Entire rows or columns may be deleted or just selected cells, moving the surrounding cells up or left to fill the gaps.

 Selecting the row number or column letter before selecting Edit |

Delete will delete the row or column without displaying the Delete dialog box.

Note: The results in cell formulas may be altered by deleting parts of the

worksheet, resulting in errors, indicated by #REF in the cells.

2.4.6 Hide/Unhide Rows and Columns

Rows or columns of data can be hidden when the data is not required to be seen.

To hide a row

 Drag the adjust cursor up (effectively making the height 0). Or

 Select Format | Row | Hide.

To hide a column

 Drag the adjust cursor left (effectively making the width 0). Or

 Select Format | Column | Hide.

Note: Dragging the adjust cursor across several columns or rows hides multiple columns/rows.

To unhide rows

 Highlight the rows above and below the hidden data.

 Select Format | Column | Unhide.

To unhide columns

 Highlight the columns either side of the hidden data.

 Select Format | Column | Unhide.



TIP

2.5 Exercise 2

1. Open a workbook that contains 10 sheets.

2. Save the workbook by a file name Managing Worksheet in a folder called

"Excel Training "

3. Rename sheet 1 as Format.

4. Insert two worksheets before Format. 5. Move Format sheet to the end of Sheet 10.

6. Create a copy of sheet 2 with the same workbook. 7. Delete sheets 3 and 8 at the same time.

3.0 WORKING WITH DATA & CELLS

3.1.1 Types of Data

There are two types of data you can enter into a cell: text or numbers. Any data containing a text character is treated as text, i.e. “January”, “Week 1” or “55B”.

Any data containing purely numbers is treated as a number, i.e. “5”, “3.14” or “1998”. You should use a full stop as a decimal point. If you put two full stops, or a comma, into a number it will be treated as text. Excel 2003 recognises and treats text and numbers slightly differently. The most obvious difference is that text will be automatically left aligned in a cell. Numbers will be right aligned. (You can override this alignment at any time).

3.1.2 Entering Data into any Cell

 Select the cell

 Type the data

Having typed the data, you need to enter it into the cell.

If the text you enter is too long to fit in a cell, it will spill across into the neighbouring cell on the right. If you enter data into the cell on the right, it will cut off the text. In this situation you should either keep the cell on the right empty, or increase the width of the column containing the first cell until it is wide enough to contain the text.

As Excel 2003 is designed more for number storage than word processing, there is a limit to the amount of text you can enter into any cell. The limit is

112 characters in a cell. 3.1.3 AutoFill

The AutoFill feature is used to copy cell contents across or down the spreadsheet.

To Use AutoFill

 Select the cell containing the data you wish to copy

When a cell is selected it has a thick black border around it. In the bottom right hand corner of that border is a little black square called the fill handle.

 Position over the fill handle until the cursor changes to a black cross 

 Click on the fill handle and drag down or across the sheet, until the data series has reached the size you want (indicated by a flag next to the cursor), then release the mouse button

If AutoFill is used with dates or words relating to dates, such as months or days, it will produce a ‘fill series’. For example if you use the AutoFill feature on a cell containing the word “January” it will create the series: “February”, “March”, “April”, etc in the neighbouring cells.

January February March April

This will work with months in full (January) or in short (Jan) and days of the week in full (Monday) or in short (Mon).

3.2 Editing Data 3.2.1 Overtyping

Overtyping is used to replace data in cells by typing new information.

Editing by Overtyping

 Click on the cell to be changed.

 Enter new data.

 Any time before <Enter> is pressed, the user may click on the

Cancel button on the formula bar or press <Esc> which will

cancel the new input, leaving the originally entered data unchanged.

 After completing the cell entry the new information replaces the old.

3.2.2 Undo/Redo

When editing a worksheet, the last change made can be undone by selecting either Edit | Undo …. or the Undo button, .

After completing a replacement entry, this action can be undone with

Undo.

 Select Edit | Undo or click Undo button to reverse the last action.

 After Undoing an action, it can be Redone by selecting Edit |

Redo …. or clicking the Redo button .

All actions that can be undone are stored in the Undo history. To use:

 Click the drop down list next to the Undo button

 Select the number of required Undo actions to a maximum of 16.

3.2.3 Editing Cells

Cell contents can be changed by editing when the information is more complicated and overtyping becomes impractical.

Formula bar editing

 Click on the cell to edit and click in the contents in the Formula Bar.

 The cursor can be moved around using the cursor movement keys and the Home and End keys.

 To complete the entry press <Enter>. Other keys such as the down cursor do not complete the entry after editing.

In-cell editing

 Place the cursor within the cell.

 Double click to start In-cell editing.

The same rules apply as with Formula Bar editing.



TIP

contents in the active cell.

 Any part of a cell’s contents can be formatted, by clicking and dragging, during the edit process.

3.2.4 Erasing Data

The contents of cell/s can be erased by:

 Clicking on the required cell or highlight a group of cells.  Select Edit | Clear | Contents.

Note:Selecting All clears contents, comments and any formatting. Formats clear all the formatting and leaves the contents. Comments only clear the cell comments.



TIP

3.2.5 Copy, Cut and Paste Cells

The copy command can be used to copy labels, values and formulas of a single cell or a group of cells to other areas of the worksheet.

The selected cells are placed in an area of Windows called the

Copy

 Select the cell or cells to be copied.

 Select Edit | Copy or the Copy button, on the toolbar.

Note: Excel places a marquee (a dotted line around the selected cells to be

copied).

Paste

 Select the cell or click and drag a range or select the top left cell of the paste area for a range.

 Click Paste , if required, more than once (see Note) Or

 Press <Enter>, to paste and clear the clipboard.

Note: When usingPaste, the original cell can be pasted repeatedly whereas

<Enter> terminates the copy process.

Paste Special

Paste Special is used to paste cell information when a straight forward copy is not required.

When to use Paste Special:

 To combine 2 ranges by adding, subtracting, etc.

 To transpose a range (rows to columns and columns to rows).

 To convert formula to values by pasting to the same location.

To use paste special

 Highlight cell/s to copy.

 Select Edit | Copy or click or right click and select Copy.

 Choose the required options.

 Click OK.

Cut

 The same as copy except the original cell/s are removed after pasting

 Edit | Cut or click

 Select destination cell and Edit |Paste or or press <Enter>



TIP

the cursor on any edge (it becomes a white arrow), drag and drop to the new location.

3.3 Ranges

3.3.1 Selecting Cells using the Mouse

A range is a rectangular collection of cells, consisting of just one cell, a row or column of cells or a block of cells covering several rows and columns. Just as single cells are identified by location, e.g. C9, F11 etc., ranges are identified by the cells of their outer limits, e.g. the four cells B2, B3, C2 and C3 is the range B2:C3.

Selecting a range using the mouse

 Click and drag the mouse to highlight a group of cells.

Note: The first cell is the active cell and remains white, whilst the rest of the

range is displayed in black.

 Alternatively, click on the first cell and hold down <Shift> whilst clicking the last cell.

Note: If the range is larger than the screen, stay in the grey areas next to the

worksheet, as the selection process is very fast if the pointer touches the edge of the screen.

Selecting more than one range

 Holding <Ctrl> whilst clicking and dragging from the second range onwards. This keeps the first range highlighted whilst selecting the others (useful when charting).

 Move using the cursor keys to highlight the range.

 Press F8 again to ‘turn off’ the selection.



TIP

click the last cell or drag to the last cell. 3.3.2 Using the Fill Handle

Excel allows ranges to be quickly filled with data by using the Fill

Handle, at the bottom right corner of the active cell or range.

 Select the cell to be copied.

 Place the mouse pointer over the fill handle (pointer changes to a small black cross).

 Click and drag in one direction (usually down or right) until the required range is selected and the mouse button released.

 The range will be filled with the original cell contents or the formula copied.

The Fill Handle can be used to custom fill a range (a ToolTip shows the extent of the range).

 Any text with a number on the end, e.g. Month 1, Week 4, Salesperson 2.

 Months of the year (long or short format, Jan or January).

 Days (steps in single days).

 Custom Lists created by user (covered in a later section).

More complicated series

 Enter in the first two cells (week ending dates, stepped numbers).

 Highlight the two entries.

 Drag with the Fill Handle to complete the series.



TIP

whilst dragging the fill handle. 3.3.3 Filling a Range

 Enter first cell.

 Highlight the range to fill including the first cell.

 Select Edit | Fill | Series.

 Enter step value, if not 1.

 Select from Type or Date unit.

 Click OK to fill the range.

Note: Fill series only works in one direction, either a row or a column. 3.3.4 Copy Formulas

Copying formulas can be carried out in precisely the same way as labels. If the user wishes to use the same formula in another position, e.g. to total the NEXT column of figures, then the references of the columns within the formula will require adjustment.

By default Excel will carry out this adjustment automatically. This is because cell addresses are held as Relative Addresses. (This is covered in a later section.)

Copying formulas using copy and paste

 Select the cell to be copied.

 Select Edit | Copy or click .

 Highlight a cell or range to receive the copy.

 Select Edit | Paste or , if required more than once or press <Enter>.

Copying formulas using the Fill Handle

 Enter the formula to the right of the first row and at the bottom of the first column.

 Use the fill handle to copy each of the formulas to adjacent cells (the grand total, bottom right can be either the sum of the rows or of the columns in a normal spreadsheet model).

3.4 Copying

3.4.1 Copy Cells by Dragging

 Select the cell to copy.

 Place the cursor at the edge; hold <Ctrl> (white arrow with + sign ).

 Click and drag to the new location.

 Release mouse button.

Alternatively

 Select the cell to copy.

 Place the cursor at the edge.

 With the right mouse button click and drag to the new location.  Release the mouse button and select Copy Here from the quick

menu.

Range

3.4.2 Copy Ranges

Copying from one cell to a range of cells

 Click on cell to copy.

 Select Edit | Copy or copy button .

 Select the range of cells to receive the copy.

 Press <Enter> or for multiple pastes use Edit | Paste or paste button .

Copying from one range to another

 Click on range to copy

 Select Edit | Copy or copy button

 Click on the top left cell of the receiving range.

 Press <Enter> or for multiple pastes use Edit | Paste or paste button .

Note: Do not try to match the size of the destination range to the source range Alternatively

 Highlight the range to copy.

 Place the cursor at the edge, hold <Ctrl> (cursor becomes ).

 Click and drag to the new location.

 Release mouse button to copy.

3.4.3 Move & Insert Cells by Dragging Moving cell contents

 Place the cursor at the edge of the range (move cursor - white arrow).

 Click and drag to the new location.

 Release mouse button to move.

Note: This method is quicker than cut and paste described in an earlier section. Inserting cells by dragging

Ranges can be moved from one location and inserted into another without first clicking on the destination cell in which it is to be placed.

 Highlight the original range.

 Hold <Shift> and click and drag the edge to the new location.

Note: Holding <Ctrl Shift> copies and inserts. 3.5 Exercise 3

1. The file below shows the lists of the best students in IT in a certain school. Enter the record using Microsoft Excel and save the file as "Students Score" in the folder " Excel Training"

Roll

No Name Sex scoreTest Final Examscore

33 Tesfaye M 30 55 12 Desalegn M 25 49 10 Benchiamlak F 20 59 20 Eden F 33 45 38 Tiruwork F 28 50 40 Yenehun M 20 59

2. Select the following cells:

 Single Cell: A10, B5, F9

 Range of Adjacent Cells: B5 to B17, 04 to 011

 Non-Adjacent Cells: A2 to A 10, B7 to B 11, and D10 to D20

3. What is the name of the cell, which is the intersection of column F and Row 100?

4. Move the cell pointer to G60.

5. Enter the first 20 natural numbers starting from cell G2 downwards. 6. Enter the series of days of a week starting with Monday in row 25. 7. Enter the series with starting value 1, step value 3 & stop value 61 in

column J

4.0 WORKSHEET FORMATTING

4.1.1 Bold, Italic and Underline

Formatting is changing the way cells look in order to improve readability, appearance and ease of use.

Applying bold, italic and underline

 Highlight the required cell/s.

 Select Format | Cells, Font tab.

 Choose the required formatting from within Font Style and

Underline.

Note: When using the Format dialog box all selections are previewed to show

what the results will look like.

 Click OK to add the formatting.

From the keyboard

 Select the cell/s.

 To add Bold, press <Ctrl B>.

 To add Italic, press <Ctrl I>.

 To start Bold press <Ctrl B>, type text and <Ctrl B> to turn bold off.

 To start Italic press <Ctrl I>, type text and <Ctrl I> to turn italic off.

 To start Underline press <Ctrl U>, type text and <Ctrl U> to turn underline off.

Part of a cell only, while editing

 Enter edit mode by double clicking on the required cell or press <F2>.

 Click and drag any part of the cell contents and use any method described previously to add the required formatting.

 Press <Enter>, to exit edit mode.

Using the mouse

 Select the required cell/s and click once on the required toolbar

button from .

 When a feature is in operation the button changes colour and appears to be pressed.

Removing bold, italic and underline

 Select the required cell/s.

 Turn off by clicking once on the depressed button.

4.1.2 Change Font and Font Size

A font is a type or style of print. Examples of fonts are Arial, Roman, Modern, Script, etc. A combination of the software in use and the selected printer determines which fonts are available for use. The default font is Arial, 10 point. Font size is measured in points. There are 72 points to 1 inch in height.

 Select the range.

 Select Format | Cells.

 Select Font tab.

 Select Font from Font box and Size from Size box.

Note: Text can be typed into these boxes, especially the size box, when a size

is not displayed in the list.

 Check the Preview box for an example of the chosen options.



TIP

and Size from the size box . The changes have to

be applied to see the results, there is no preview available. Change of base font and size

To change the base font for all new workbooks:

 Select Tools | Options, click the General tab.

 Choose the required Font and Size.

 The changes will only take effect after Excel is restarted.

4.1.3 Format Numbers

Numbers can be displayed in various formats with options for decimal places and the display of negative values.

Formatting numbers

 Select the range.

 Select Format | Cells.

 Choose the Number tab.

 Select required Category, Number of decimal places and Negative number display (for currency).

 Check the sample box.

 Click OK to apply the chosen formats.



TIP

standard Currency format, gives a standard Percentage format,

gives a Comma format and and increase and decrease the number

of decimal places by one. Select the cells to be formatted and then click on the appropriate button.

4.1.4 Date and Time

Date and Time is stored as a number including a decimal part. The

whole number represents the number of days since 1 January 1990 and the decimal part represents the time as a fraction of a day.

Formatting date and time

 Select the range.

 Select Format | Cells.

 Choose Number tab.

 Click OK.

T I P _{To enter Today’s date press <Ctrl ;>. To enter the current time press} <Ctrl Shift ;>

Note: Elapsed time calculations are carried out by subtracting one time from

another.

4.1.5 Alignment

Alignment is the positioning of text in a cell relative to its edges. By default Labels (Text) are aligned to the left and Numbers to the right.

Aligning cell contents

 Highlight the required range.

 Select Format | Cells | Alignment tab.

 Select the required option from within Horizontal (choosing Left

(Indent) optionally enter the amount in the Indent box, changing

the Indent automatically selects Left (Indent) in the Horizontal box).

 Select the required option from within Vertical.

 Click OK to apply the formatting.



TIP

Right on the formatting toolbar. Click once to apply formatting.

4.1.6 Text Wrap

When text needs to fit a cell without widening the column, text wrap can be used.

To apply text wrap

 Select Format | Cells and the Alignment tab.

 Check Wrap text under Text control.

 Click OK to apply the formatting.

Note: The row height is changed automatically to accommodate the text if the

user has not manually set the row height. If the row height has been set, it must be increased manually to view all the text.

4.1.7 Merge and Shrink to Fit

Merge and centre is used to centre a title across a range of cells, either horizontally or vertically.

 Enter text into a cell.

 Highlight the range starting with the cell that contains the text.

 Select Format | Cells, Alignment tab.

 Under text alignment, select Centre (vertical or horizontal, as required).

 Check Merge cells under Text control.

 Click OK.



TIP

e.g. ‘0786. The entry is now treated as text and calculations can be performed on it without modification. Trailing zeros in decimals are displayed via formatting.

To remove merge and centre

 Highlight the merged cell.

 Select Format | Cells, Alignment tab.

 Uncheck Merge cells under Text control.

 Click OK.

Shrink to Fit

Text can be made to fit a cell by applying Shrink to Fit.

 Select the cell/s.

 Select Format | Cells, Alignment tab.

 Check Shrink to Fit under Text control.

 Click OK to apply the formatting.

4.1.8 Text Orientation

Text within cells can be displayed vertically or at any angle.

To change the text orientation

 Highlight the range of cells to be formatted.

 Select Format | Cells and the Alignment tab.

 Within Orientation click on the vertical text or either Rotate the text by dragging the line round to the required angle or enter the number of degrees in the box.

Note: If the row height has not been set manually it changes automatically to

accommodate the text. If it has been set manually the row height must be adjusted manually as required.

4.1.9 Text Colour

To change font colour

 Highlight the required range.

 Click the Font colour button to change to the displayed colour or

 Click the to display the drop down list and choose from the palette of colours.

 Selecting a Font Colour automatically closes the drop down box and adds that colour to the text in the selected range.

Note: The last colour used will be displayed as the colour for the rest of the

current working session. Red is displayed as the default text colour if the program is restarted.

Alternatively

 Font colour can also be changed if formatting text via Format |

Cells and Font tab.

 In the Color box click the drop down arrow to display the range of colours.

 Select the required text colour.

 Click OK to apply the text colour.

4.1.10 Cell Borders

Lines are added to worksheets via Cell Borders. Options are given on line style and location within the cell or range of cells.

To add cell borders

 Select the required cell/range.

 Choose from Style, Color (if required) and then from either

Presets or Border to add lines to the required areas.

Note: When adding lines, remember that lines are added in the chosen

position to every cell in the chosen range.



TIP

Borders button . Choose from the drop down list. The last chosen option is then displayed for future use. The default Border is a single line on the bottom of cells.

4.1.11 Cell Shading

Background cell colour is added to cells to enhance the appearance of worksheets.

To add cell shading

 Highlight the required range.

 Click the Fill Color button for the default colour or  Click the down triangle to display the range of colours.

 Click on the required colour to apply to the range of cells.

 Highlight the range and select Format | Cells and Patterns tab and choose from Pattern.

 Click OK to add pattern and colour.

4.2 Cell Referencing

4.2.1 Relative Referencing

As a formula is copied across a range of cells, the formulas change automatically. The calculation is performed on cells in the positions relative to those copied, e.g. B2+B3 become C2+C3 in the next column. The formulas can be copied to any cells in any position on the worksheet.

Excel calculates formulas relative to where the formula is in the spreadsheet. In the example above the formula in B4 adds the two cells directly above B4. Copying this formula to D8 adds D6 and D7 (the two cells directly above D8).

The fill handle is used to copy to adjacent rows or adjacent columns (in one direction only).

Fill Handle

4.2.2 Absolute Referencing

The user may wish to use a fixed cell address in a formula to refer back to the same cell when the formula is copied. Excel treats all cell references as relative unless they are fixed by the user.

Examples of fixed cell references are fixed values, interest rates, VAT, fixed overheads. The advantage of fixed references is that changing one cell, changes all other references, the worksheet do not need to be searched for cells that contain the old value.

To fix a cell as Absolute, $ symbols are added to the cell references. =C3*$B$15



TIP

after typing, pointing or during editing. 4.2.3 Mixed Referencing

A mixed reference is a combination of both relative and absolute, e.g.

A$1 or $A1 where an exact row or column is referenced with a relative

row or column.

Note: Mixed referencing is rarely used as the fill handle references

across a row or down a column automatically.



TIP

addressing: relative, absolute, mixed (row fixed) and Mixed (column fixed). 4.3 Cell Comments

4.3.1 Cell Comments

A cell Comment is a note - a piece of text that is attached to a cell.

Comments are not seen on a worksheet by default. When the mouse

pointer is placed over the cell the comment is displayed as a cell tip. Cell comments can be displayed permanently.

Cells that have comments attached to them have a comment indicator (a small red triangle in the top right corner of the cell).

Note: If the comment indicator cannot be seen, check that the Comment indicator only display is checked in Tools | Options, View tab

options..

To create a cell comment

Relative Reference

 Click on the required cell.

 Select Insert | Comment.

The user name is added automatically to the top line of the comment but can be deleted if not required.

 Enter the comment.

 Resize the comment box to fit the text by clicking and dragging the appropriate handle.

 Click away from the Comment box to complete the action.

The cell comment is a yellow shadow box with an arrow to the relevant cell.



TIP

Comment from the menu.

4.3.2 Display Comments

To display comments temporarily

 Place mouse cursor over required cell

To display all comments permanently

 Select View | Comments

All comments on the active worksheet are displayed. Displaying the comments opens the Reviewing toolbar.

To hide comments

 Select View | Comments (toggles between show and hide comments) or click on on the Reviewing toolbar.

 Close the Reviewing toolbar, click .

To show a specific comment

 Select the required cell with the comment attached.

To hide a specific comment

 Select the required cell with the comment attached.

 Right click the mouse button and select Hide comment.

4.3.3 Edit & Delete Comments To edit comments

 Click on the required cell.

 Select Insert | Edit Comment.

 Make the necessary changes to the comment.

 Click away from the comment.



TIP

Comment.

To delete comments

 Highlight the required cell/range of cells.

 Select Edit | Clear | Comments.



TIP

Comment.

4.3.4 Format Comments To format comments

Either

 Highlight required cell with the comment. Ensure the option to view the comment is active. Select the comment box.

 Select Format | Comment. Or

The options within Format Comment are:

Font Normal font dialog box to change font, size, colour, underline, etc

Alignment Horizontal, vertical and orientation

Colour and Lines Fill colour, line properties, etc.

Size Size and rotate and scale

Margins Set internal margins

Properties Object positioning

Protection Lock box and text

 Click OK to apply the formatting when finished.

4.3.5 Print Comments To print comments

 Select File | Page Setup.

 Choose Sheet tab.

 Click on the Comments drop down list.

 Choose between None, At end of sheet and As displayed on

sheet.

 Click OK. This sets the comment print option.

4.4 Worksheet Appearance 4.4.1 Zoom

Zoom is used to control the magnification of the worksheet window. A

worksheet can be increased or decreased to any percentage of its

original size. To Zoom

 Select View | Zoom.

 Choose the required magnification option.

Note: Selecting Custom requires a percentage to be entered.

 Click OK.

Note: To use Fit selection, highlight the range to be displayed before

following the above commands.



TIP

from the available options to change Zoom magnification or type any number directly into the Zoom box.

4.4.2 Multiple Windows

Multiple windows make it easier to:

 Enter data

 Compare data

 Format data

 Edit data

.To work with different parts of the same sheet or different sheets in the same workbook

 Make the required workbook active.

Note: This action creates a second copy of the same workbook, with the added

extensions of: 1 and: 2. this allows the user to view two parts of the same workbook/sheet simultaneously.

 Select Window | Arrange and choose from Tiled, Horizontal,

Vertical or Cascade. Click OK.

Note: Arrange displays all open workbooks, unless Windows of active workbook is checked, then only the open windows in the active

workbook are displayed.

Note: Closing one of the copies removes the extensions: 1 and: 2 but still

leaves the original copy open.

4.4.3 Hide Windows

Windows can be hidden. They remain open.

To hide windows

 Make the window to be hidden, active.

 Select Window | Hide.

To display hidden windows

 Select Window | Unhide to display a dialog box showing those windows which are hidden.

 Select the window to unhide.

 Click OK.

4.4.4 Freeze Panes

Placement of the active cell is important as all rows above the active cell and all columns to the left of the active cell are frozen.

To freeze panes

 Place the active cell in the required position.

 Select Window | Freeze Panes.

 The rows and/or columns are then frozen. In the above diagram the active cell used was B4 to freeze row 3 and column A.

To remove frozen panes

 Select Window | Unfreeze Panes.

 The panes are then unfrozen.

Note: Columns only can be frozen, by placing the active cell in row 1 and Rows, by placing the active cell in column A.

4.4.5 Split

Split is similar to freeze except with split the individual panes can be

scrolled to show different information.

To use split

 To adjust the panes click and drag either the horizontal or vertical split.

To remove a split

 Select Window | Remove Split



TIP

required positions.

4.4.6 Outline

An outline is a method of hiding and displaying parts of a worksheet for different situations. Different users may need to see different information or different amounts of information. The managing director may just be interested in totals, whereas the marketing manager may be interested in the areas where the totals came from, without seeing individual figures that make up the sub-totals.

If formulas refer to either cells above or to the left an Outline can be created automatically.

To create an outline

 Select Data | Group and Outline | Auto Outline.

There are various buttons and bars that appear on the worksheet when an outline has been created.