1-excel

University Technology Services Computer Training

Creating Excel WorksheetsMicrosoft Excel 2003

Reference Guide

Microsoft Excel 2003

Copyright Notice Copyright 2003 EBook Publishing. All rights reserved. No part of this publication may be reproduced, transmitted, or translated into any language, in any form or by any means, electronically or mechanical, including photocopying, recording, storage in a database or retrieval system, or otherwise, without the prior written permission of EBook Publishing. Disclaimer We take great care to ensure the accuracy of these materials; however, EBook Publishing makes no warranty, express or implies, including without limitation any warranty concerning the adequacy, accuracy or completeness of such information or material or the results to be obtained from using such information or material. EBook Publishing reserves the right to revise this publication and to make changes in its content at any time, without obligation to notify any person or entity of such revisions or changes.

Microsoft Excel 2003 TABLE OF CONTENTS

GETTING STARTED ........................................................................................ 1 Creating an Excel 2003 Workbook...........................................................................................2

How to Start Excel 2003 .....................................................................................................2 How to Create a New Default Excel Workbook ..................................................................3

Getting Familiar with the Excel 2003 Screen ..........................................................................4 Moving Around Your Cursor ....................................................................................................6

How to Activate a Cell Using the Mouse.............................................................................6 How to Move to a Cell Using Keyboard ..............................................................................7 How to Move to Other Sheets within a Workbook ..............................................................8

Selecting Cells Range in Workbook ........................................................................................9 How to Select a Single Cell...............................................................................................10 How to Select a Range of Cells ........................................................................................10 How to Select a Range Of Cells Using .................................................................10 How to Select the Entire Column......................................................................................11 How to Select the Entire Row ...........................................................................................11 How to Select the Entire Worksheet .................................................................................11 How to Select Non-Adjacent Ranges................................................................................12 How to Select Multiple Worksheets ..................................................................................12

Entering Data into Worksheet ................................................................................................13 How to Enter Text (Label) Into a Cell................................................................................13 How to Edit Cell Contents .................................................................................................13 How to Edit Contents Using Formula Bar .........................................................................14 How to Replace Contents in a Cell ...................................................................................14 How to Delete Contents in a Cell ......................................................................................14 How to Undo and Redo.....................................................................................................15 How to Enter a Number as a Value into a Cell .................................................................16 How to Enter a Number as a Label into a Cell..................................................................17 How to Enter Date into a Cell............................................................................................18 How to Use AutoFill to Enter Text.....................................................................................19 How to Use AutoFill Options .............................................................................................20 How to Use AutoFill to Enter Sequence Numbers............................................................21 How to Save a Workbook for the First Time .....................................................................22 How to Save Another Copy Using `Save As....................................................................22 How to Close the Current Workbook ................................................................................23 How to Open a Workbook.................................................................................................24 How to Exit Excel 2003 .....................................................................................................24

PERFORMING CALCULATIONS .................................................................. 25 Using Simple Formula.............................................................................................................26

How to Enter a Formula Using Keyboard .........................................................................28 How to Enter a Cell or a Range Reference Using Mouse ................................................28 How to Use AutoSum to Calculate Total Quickly..............................................................29 How to Use Relative References ......................................................................................30 How to Use Absolute References .....................................................................................31

Using Simple Functions..........................................................................................................33

MANAGING WORKSHEET AND WORKBOOK INFORMATION.................. 35 Managing Worksheet Information..........................................................................................36

How to Move Information Using Cut and Paste................................................................36 How to Move Information Using Drag and Drop ...............................................................37


How to Copy Information Using Copy and Paste .............................................................38 How to Use Paste Options................................................................................................39 How to Copy Information Using Drag and Drop ...............................................................40 How to Insert a New Row .................................................................................................41 How to Insert Multiple Rows .............................................................................................42 How to Delete Rows..........................................................................................................43 How to Insert a New Column ............................................................................................44 How to Insert Multiple Columns ........................................................................................45 How to Delete Columns ....................................................................................................46 How to Insert a Cell...........................................................................................................47

Managing Workbook Information ..........................................................................................48 How to Rename Worksheet ..............................................................................................49 How to Add New Worksheet .............................................................................................50 How to Rearrange Worksheet...........................................................................................51 How to Copy Worksheet ...................................................................................................51 How to Format Worksheet Tab .........................................................................................52 How to Add Worksheet Background.................................................................................53 How to Delete Worksheet .................................................................................................54

FORMATTING WORKSHEET........................................................................ 55 Adjusting Column Width / Row Height..................................................................................56

How to Adjust Column Width Using Mouse ......................................................................57 How to Adjust Column Width Using Menu........................................................................58 How to Adjust Row Height Using Mouse ..........................................................................59 How to Adjust Row Height Using Menu............................................................................60 How to Hide / Unhide Rows / Columns.............................................................................61 How to Freeze a Column / a Row.....................................................................................62 How to Split a Worksheet in Panes...................................................................................63

Formatting Cell Using Toolbar ...............................................................................................64 How to Change Font .........................................................................................................64 How to Change Font Size .................................................................................................65 How to Bold.......................................................................................................................65 How to Italic.......................................................................................................................65 How to Underline...............................................................................................................65 How to Change Font Color ...............................................................................................66 How to Align Data in a Cell ...............................................................................................67 How to Indent Data in a Cell .............................................................................................68 How to Merge Cells and Center Data ...............................................................................69 How to Change Number to Percentage............................................................................70 How to Increase/ Decrease Decimal Points .....................................................................71 How to Copy Format Using Format Painter ......................................................................72

Formatting Cell Using Menu Options ....................................................................................73 How to Double Underline Using Menu Option ..................................................................74 How to Align Vertically ......................................................................................................75 How to Change Text Orientation.......................................................................................77 How to Wrap Text in a Cell ...............................................................................................78 How to Format Numbers...................................................................................................79 How to Format Date..........................................................................................................80 How to Change Cell Background Color ............................................................................81 How to Add Outline Border ...............................................................................................82 How to Add Double Line Border .......................................................................................83


How to Format Table Using AutoFormat ..........................................................................84

SETTING UP PAGE AND PRINT................................................................... 85 Setting Up Page .......................................................................................................................86

How to Insert and Remove Page Break............................................................................87 How to Set and Clear Print Area.......................................................................................88 How to Change Page Orientation .....................................................................................89 How to Change Scaling ....................................................................................................89 How to Change Paper Size...............................................................................................90 How to Change Page Margin............................................................................................90 How to Align to Center Of Page........................................................................................91 How to Add Header / Footer .............................................................................................92

Previewing and Printing..........................................................................................................94 How to Print Preview.........................................................................................................95 How to Print Active Sheets ...............................................................................................96 How to Print Selected Cells ..............................................................................................97 How to Print Multiple Copies.............................................................................................98

2003 ComputerTrainingManual.com 1

Getting Started

Objectives: When you have completed these lessons, you will be able to:

Create an excel workbook

Create a new default excel workbook

Create a new excel workbook using Template

Get Excel and Microsoft Online Help

Detect and repair errors

Recover excel program

Recover an excel file

Navigate around the cells in worksheets

Select cell ranges in workbook

Enter text into a cell

Enter number as value or label into the cell

Enter date into a cell

Use Undo and redo

Use AutoFill to enter text and sequence numbers

Use Pick List to enter data

Enter data into a range of cells

Save and open workbook



Creating an Excel 2003 Workbook How to Start Excel 2003

1. Click on

the taskbar. 2. Click Programs >>

Microsoft Excel.

You can also start

Microsoft Excel by clicking the Excel icon on the Microsoft Office Shortcut Bar.

1. Click Start button on the taskbar. The Start menu appears.

2. Click Programs >> Microsoft Office >> Microsoft Excel. The Microsoft Excel application starts.

Excel displays the following screen. a new blank workbook is created automatically, as shown below:

SUMMARY

TIPS



How to Create a New Default Excel Workbook

1. Click File >> New. 2. Under New, click

Blank Workbook.

You can create a blank new workbook quickly by clicking the

on the Standard toolbar or press +.

1. From the menu, click File >> New. The task pane appears, as shown below.

2. Under New, click Blank Workbook. The pointer changes to when you move it to the command. A blank new workbook is created and the task pane disappears.

SUMMARY

TIPS



Getting Familiar with the Excel 2003 Screen This lesson describes screen elements specific to Excel 2003. This helps you to be familiar with the program screen / interface. Some of the screen elements can be toggled on or off. For this lesson, you screen may look slightly different to the illustration below due to your own settings.

Title Bar Menu Bar Standard Toolbar Formatting Toolbar

Active Cell Formula Bar

Row Heading

Column Heading

Name Box

Vertical Scroll Bar

Task Pane

Sheet Tab Horizontal Scroll Bar

Status Bar



Screen Elements Functions

Title Bar Displays the program name and the workbook name you are working on.

Menu Bar Displays and access the drop-down menus that contain the Excel command options.

Standard Toolbar Contains a number of buttons, which you click using the mouse, to carry out some of the more commonly used command options.

Formatting Toolbar Contains a number of buttons that help you change the format or appearance of text, such as bold and underlined.

Name Box Shows the selected cell, drawing object or chart item. You can also name a selected cell / range or move to the selected cell / range.

Formula Bar Displays the content (value or formula) of the active cell. You can also edit the formula using the formula bar.

Active Cell The selected cell in which data is entered when you begin typing. Only one cell is active at a time. The active cell is bounded by a heavy border.

Column Heading Shows the column reference letter.

Row Heading Shows the row reference number.

Sheet Tab Shows the sheet name.

Horizontal Scroll Bar or Vertical Scroll Bar

Help you to scroll through your worksheet using the mouse.

Status Bar Displays information about a selected command or an operation in progress. The right side of the status bar shows whether the keys (CAPS LOCK, SCROLL LOCK, or NUM LOCK) are turned on.

Task Pane Display useful common tasks such as creating and opening a workbook. There are task panes with different contents that help you do your work. The contents of the task pane is context-sensitive, depending on what task you are performing.



Moving Around Your Cursor When you want to place your cursor or activate a cell in order to insert, edit or format the cells, there are ways to help you. You can use: Mouse Keyboard Go to command Name box on the formula bar

How to Activate a Cell Using the Mouse

1. Position the mouse

pointer over the cell you want to activate.

2. Click the cell to activate it.

1. Position the mouse pointer to the cell B3. The pointer changes to a white plus sign, as shown below.

2. Click the cell B3 to activate it. The cell B3 becomes active, as shown below.

SUMMARY



How to Move to a Cell Using Keyboard

3. Press the key on the keyboard as shown in the table below:

Press Move to:

One cell to the right One cell to the left One cell to the down One cell to the up + Last cell to the right of the current region + First cell to the left of the current region + Last cell to the bottom of the current region + First cell to the top of the current region First cell in the row

+ First cell in the worksheet

+ Last cell in the worksheet which contains data

Page Down One screen down

Page Up One screen up

+ Page Down One screen right

+ Page Up One screen up



How to Move to Other Sheets within a Workbook

You can press

shortcut keys + to move to the next worksheet and press + to move to the previous worksheet.

1. Move the mouse pointer to the Sheet2 tab. The pointer is changed to a white arrow as illustrated.

2. Click the Sheet2 tab. Sheet2 is brought to the front most of all worksheets.

TIPS



Selecting Cells Range in Workbook You need to select the cell (activate the cell) before you place the information in it. Besides that, if you want to execute a command onto several cells, you may select all the cells before you run the command. A group of selected cells is called a range. A range can contain a block of cells, a complete column, a complete row or a non-adjacent range. .

A cell A block of cells A complete column A complete row

Selected non-adjacent cells range A non-adjacent range consists of separate blocks of cells that are selected at the same time. A selected range is shaded except for the active cell, information appears in that active cell when you type.

Active cell



How to Select a Single Cell

1. Click the cell B2. Click at the cell that you want to select. The Name box on the left of the formula bar shows which cell is active.

How to Select a Range of Cells

2. Click the cell B2 and drag to the cell C4. Cell B2 is the first cell while the cell C4 is the last cell of the selection range. The range B2:C4 is then highlighted, as shown below.

How to Select a Range Of Cells Using

How to select a range of cells using click and drag Click the first cell and

drag to the last cell. How to Select a Range of Cells Using 1. Click the first cell. 2. Hold down . 3. Click the last cell.

You can also use the

arrow keys (,,, ) to select a range. Hold down and press arrow key to select range.

1. Click the cell B2. This is where the selection begins.

2. Hold down . Excel marks the cell B2 as the beginning of the selection.

3. Click the cell C5. This is the last cell of the selection. A range of B2:C5 is selected, as shown below.

SUMMARY

TIPS



How to Select the Entire Column

Click the Column

Heading and drag to left or right to select more than one column.

4. Click the Column B Heading. The entire column B is selected, as shown below.

How to Select the Entire Row

Click the Row

Heading and drag up or down to select more than one row.

5. Click the Row 3 Heading. The entire row 3 is selected, as shown below.

How to Select the Entire Worksheet

You can also press

+ to select the entire worksheet.

6. Click the Select All button. The button is located at the upper-left corner of the worksheet where the column and row headings meet.

TIPS

TIPS

TIPS



How to Select Non-Adjacent Ranges

How to select Non-Adjacent Ranges: 1. Select the first range. 2. Hold down . 3. Select the non-

adjacent ranges. How to select multiple worksheets: 1. Click the first

worksheet tab. 2. Hold down . 3. Click the worksheet

tabs you want to select.

1. Select the range C2:D4 as the first selected range. The range is selected, as shown below.

2. Hold down . The first selected range remains selected even when you select another range.

3. Click the range A6:C9 to include another non-adjacent range. Both the ranges are selected, as shown below.

How to Select Multiple Worksheets

If you want to

deselect the worksheets, click the sheet tab that is not selected. For the example on the right, you can click the Sheet2 as it is not currently selected.

1. Click the Sheet1 tab. Sheet1 is selected.

2. Hold down . The first selected sheet remains selected if you select subsequent sheet.

3. Click the Sheet3 tab. Sheet3 is selected while Sheet1 still remains selected.

SUMMARY

TIPS



Entering Data into Worksheet Excel provides different ways to enter and edit data in the cells on worksheet easily and quickly. The data you enter in the cells is formatted automatically. E.g. When you type a date 1/1/03 in a cell, Excel format the cell as a date automatically. How to Enter Text (Label) Into a Cell

You can also click

Confirm button on the left of the formula bar to confirm the entry. The same cell remains active if you click the confirm button, while pressing moves the active cell down.

1. Click the cell A1. Cell A1 becomes the active cell. You want to enter a text in the active cell.

2. Type Jan. Text appears in cell A1.

3. Press to confirm the entry. You can also press to confirm the entry.

How to Edit Cell Contents

How to enter text into cell: 1. Click the cell. 2. Type the text. 3. Press . How to edit the cell contents: 1. Click the cell. 2. Press . 3. Type the changes. 4. Press .

1. Click the cell A1. You are going to edit `Jan to `January in cell A1.

2. Press . The blinking insertion cursor appears at the end of the cell content.

3. Type uary. This is to add text after the word `Jan.

4. Press . The cell is updated.

SUMMARY

TIPS



How to Edit Contents Using Formula Bar

How to edit text using formula bar 1. Click the cell. 2. Click the contents in

the formula bar. 3. Type the changes. 4. Press . How to replace the cell contents 1. Click the cell. 2. Type the new entry. How to delete the cell contents 1. Click the cell. 2. Press .

1. Click the cell A1. You are going to change `January to `Report for January.

2. Move the mouse over the formula bar and click at the left of the word January. When you position the mouse pointer over the formula bar, the pointer changes to a text editor, also know as I-beam, as shown below.

3. Type Report for and press . You have just added text before the word `January. You can use arrow keys to move the cursor to the right and the left.

How to Replace Contents in a Cell

1. Click the cell A1. You are going to replace `Report for January with `Feb.

2. Type Feb and press . The text in cell A1 is replaced.

How to Delete Contents in a Cell

1. Click the cell A1. You are going to delete `Feb in cell A1.

2. Press . The content is deleted.

SUMMARY



How to Undo and Redo

To Undo, click . To Redo, click .

If you want to cancel

an entry in a cell or the formula bar before you press , you can just press to undo the typing. You can click the

Undo button a few times to undo more than one previous action. To undo multiple

actions quickly, you can click the Undo drop-down arrow to list the previous actions. Click at the action, until which you want to undo.

1. Click cell A1, type February and press . The text appears in cell A1. The active cell moves to cell A2.

2. Click Undo button on the Standard toolbar. Your active cell returns to the cell A1. The previous action, which was typing the word February in the cell A1, is being reversed. The word `February' is removed.

3. Click Redo button on the Standard toolbar. The action that you undo previously is carried out again. The word February appears again in the cell A1.

.

SUMMARY

TIPS



How to Enter a Number as a Value into a Cell

1. Click the cell. 2. Type the number. 3. Press .

1. Click the cell A1. You want to enter a number into this cell.

2. Type 100 and press . You will see that the value is aligned to right instead of left for label or text.

SUMMARY



How to Enter a Number as a Label into a Cell

1. Click the cell. 2. Type ' an apostrophe,

then type the number. 3. Press .

1. Click the cell B1. You want to enter a number as a label into this cell.

2. Type 200. The apostrophe is a label prefix that will not appear on the worksheet.

3. Press . Click the cell A2 again. A SmartTag appears to let you know the cell is formatted as text. You can convert the text to number if you want.

4. Click the SmartTag drop-down arrow and click Ignore Error from the list. The SmartTag indicator disappears, as you have confirmed to ignore it.

SUMMARY



How to Enter Date into a Cell

1. Click the cell. 2. Type the date with

slash (/) as the separator.

A date is a value.

That is why it aligns to the right. If you want to change

the date format, click Start >> Control Panel >> Regional & Language Options. Click Regional Options tab and click the Customize button. Click Date tab and change the Short Date format.

1. Click the cell A3. You want to enter a date into this cell.

2. Type 6/20/01. If the date aligned to left, your computer date is probably set to DD/MM/YY instead of MM/DD/YY. You should then enter the date as 20/6/01 or change the date format in your control panel.

3. Press . The date format changes automatically, as shown below:

SUMMARY

TIPS



How to Use AutoFill to Enter Text

1. Type a data in any

cell. 2. Click and drag the

AutoFill Handle.

You may want to try

January, Mon, Monday, 1st Qtr or 1st Quarter.

1. In the cell A3, type Jan. This is the first cell that you want to enter the series of data.

2. Place the mouse pointer to the AutoFill Handle. The pointer changes to a + sign.

3. Click and drag to the right to fill the contents until cell D3. You can also drag down to fill the cells downwards.

The cells are filled, as shown below.

.

AutoFill Handle SUMMARY

TIPS



How to Use AutoFill Options

1. Click the AutoFill

Options. 2. Click the option from

the list.

4. Click the AutoFill Options. The options list appears, as shown below.

5. Click the Copy Cells option from the list. The AutoFill copies the cell content instead of fill the data series, as shown below:

.

SUMMARY



How to Use AutoFill to Enter Sequence Numbers

1. Type the first and

second numbers in the cells.

2. Select both cells. 3. Click and drag from

the AutoFill handle of the selection.

You may try even

numbers (2 and 4), odd numbers (1 and 3) or 0 and 5.

1. In the cell A4, type 1. In the cell B4, type 2. You need to enter data in the first and second cells.

2. Select both of the cells A4 and B4. To highlight the cells, click and drag from the center of cell A4 and drag to cell B4.

3. Move the mouse pointer to the AutoFill Handle The pointer changes to a + sign.

4. Click and drag to the right to fill the contents until cell D4. The subsequent cells are filled with a series of numbers, as shown below.

SUMMARY

TIPS



How to Save a Workbook for the First Time

How to save a Workbook 1. Click File >> Save. 2. Type the file name. 3. Click the Save button. How to save another copy of the Workbook 1. Click File >> Save

As. 2. Type the file name. 3. Click the Save button.

You can also use

Save button to save the file.

1. From the menu, click File >> Save As. The Save As dialog box appears.

2. In the File name box, type Excel Training. Excel has already entered a placeholder name in the File name box before you type your own file name. Since the name is highlighted. You just need to type in the new file name to overwrite it without having to click in the box.

3. Click the Save button. The file is saved in the default folder My Documents folder.

How to Save Another Copy Using `Save As

Use Save As

command if you would like to save an existing workbook with a new file name, or to save it in another folder or to save it in a different file format.

1. From the menu, click File >> Save As. The Save As Dialog Box appears.

2. You can type in another file name, select a new folder or new file type.

3. Click the Save button. Another copy of the file is created.

SUMMARY

TIPS

TIPS



How to Close the Current Workbook

1. Click File >> Close. 2. Click the Yes button.

1. From the menu, click File >> Close. Excel prompts to ask if you want to save the changes you made if you have made any changes that you have not saved.

2. Click the Yes button. Only if you want to save the changes before closing the workbook.

SUMMARY



How to Open a Workbook

1. Click File >> Open. 2. In the Look in box,

select the drive or folder that contains the file you want to open.

3. Double-click the file name. or You can click the file name, and then click the Open button.

You can use the

shortcut key + to open a workbook. You can also click the

Open drop-down button to select if you want to open as read- only, open as a copy, open in browser or open and repair.

1. From the menu, click File >> Open. The Open Dialog Box appears, as shown below.

2. In the Look in box, select the drive or folder that contains the file you want to open. This is necessary only if the workbook is saved in another folder.

3. Double-click at the file name Excel Training. This is the workbook you saved previously.

How to Exit Excel 2003

Click File >> Exit.

4. From the menu, click File >> Exit. Excel will prompt you to save your file before exit the application if you have not save the existing files.

SUMMARY

SUMMARY

TIPS


Performing Calculations


Understand formulas

Use simple calculations

Use AutoSum to add

Apply Absolute and Relative References

Create formula with functions

Use AutoCalculate and Labels



Using Simple Formula Formula helps you to calculate and analyze data on your worksheet. Formula contains operands and operators as illustrated below. Formula:

Operand Example Constant value 2 Cell reference A1 Range reference A1:B2 Label Jan Sales Range name Revenue Function Sum(A1:B4)

Operators: Symbols Addition + Subtraction - Multiplication * Division / Percent % Exponential ^

Microsoft Excel syntax, includes an equal sign (=) followed by the operands and the operators like = 2 + 3 = A1 - B6 =3 * A5 =Sales / Months =5 ^ 4

Operand Operand

Operator

a + b



The order of the elements in a formula determines the final result of the calculation. Excel performs the operations from left to right according to the order of operator precedence. Operator precedence Operator Description : (colon) (single space) , (comma)

Reference operators

Negation (as in 1) % Percent ^ Exponentiation * and / Multiplication and division + and Addition and subtraction & Connects two strings of text (concatenation) = < > = Comparison

For example: = 2 + 3 * 2 equals 8 Not 10 because Excel calculates multiplication before addition. You can control the order of calculation by using parentheses to group operations that should be performed first. For example: = (2 + 3) * 2 equals 10



How to Enter a Formula Using Keyboard

1. Click the cell that you

want to place the formula.

2. Type = (equal sign) and the formula.

3. Press .

1. Click the cell B5. You want to enter a formula in this cell.

2. Type =B2+B3+B4. The formula always starts with = (equal sign) as illustrated below.

3. Press . The total is calculated. If you click at the cell B5, the formula appears in the formula bar.

How to Enter a Cell or a Range Reference Using Mouse



2. Type = (equal sign) and use the mouse to click at the cell or range references.

3. Press .

1. Click the cell B5. You want to enter a formula in this cell.

2. Type =, click B2, type +, click B3, type +, click B4. The formula appears, as shown below.

3. Press . The total is calculated.

SUMMARY

SUMMARY



How to Use AutoSum to Calculate Total Quickly



2. Click . 3. Press .

1. Click the cell B5. You want to calculate the contents in the cells above the active cell.

2. Click AutoSum button on the Standard toolbar. The formula is created automatically. Excel adds the number above the active cell. If no number is found above the active cell, the numbers on the left of the active cell are added up. The formula appears, as shown below.

3. Press . The total is calculated.

1) If the active cell is not located at the bottom or on the right of a list of numbers. You can still use the AutoSum button to calculate the total. Click at the cell and click the AutoSum button. Formula appears as displayed on the right. 2) Click to select the cells to be calculated.

3) Press to get the total.

SUMMARY



How to Use Relative References

1. Click the cell that

contains the formula you want to copy.

2. Use AutoFill or Copy command to copy the formula to another cell.

3. The destination cell contains references that are relative to the source formula.

If you drag the

AutoFill Handle down or copy the formula down, the range reference B2:B4 changes to B3:B5. This means the row number changes, while the column letter remains unchanged.

By default, when a formula is created, references to cells or ranges are usually based on their position relative to the cell that contains the formula.

When you copy the formula, Excel will automatically paste the adjusted references in the destination cells relative to the position of the formula. Before you begin, type in the data for Feb column, as shown below.

1. Click the cell B5.

You are going to copy the formula =SUM(B2:B4) in cell B5 to cell C5.

2. Drag the AutoFill Handle to the right to fill the cell C5 with the formula. AutoFill Handle is the small dot at the lower-right corner of the active cell B5.

The formula is copied from cell B5 to cell C5.

3. Click the cell C5 to see the formula =SUM(C2:C4). The range reference B2:B4 changes to C2:C4 when the formula is copied to the right. Since you drag the AutoFill to the right, the column letter changes relatively to the right but row number remains unchanged.

SUMMARY

TIPS



How to Use Absolute References

1. Click the cell that

contains the formula. 2. Edit the formula by

adding dollar signs `$' to the cell reference you want to set as an absolute reference.

Use absolute references if you do not want Excel to adjust the references in the formula when copying a formula to another cell.

Before you begin, type in the Sales column and the Unit Price, as shown below. The formula in cell C2 is =B2*B7. When you copy the formula to cell C3 using AutoFill, the formula changes from =B2*B7 to =B3*B8. However, you do not want B7 to change to B8 because the unit price is stored in cell B7. So, you need to set the cell reference B7 as an absolute reference. This prevents the cell reference from changing when you paste the formula to another cell. To set the cell reference as an absolute reference, add dollar signs ($) in the cell reference.

1. Click the cell C2. You are going to copy the formula =B2*B7 to C3:C5.

2. Click the AutoFill Handle at cell C2 and drag to fill C3:C5. This is to find the sales for Mary, Steve and Total. You will get wrong results as shown below.

3. Click the cell C3 to see the formula. The formula is =B3*B8, which is a relative reference of =B2*B7 in cell C2. However, the correct formula should be =B3*B7 instead.

SUMMARY



You may press

function key to apply $ to B7 instead of typing the dollar sign ($) manually in the formula bar. Click at B7 in the formula bar and press . If you press to

apply $, it will first change B7 to $B$7. If you press again, it changes to B$7 and then to $B7 and finally back to B7. $B sets the column B

to be absolute. $7 sets the row 7 to

be absolute.

4. Click the cell C2 again. Change the formula in cell C2 by adding dollar signs ($) to the cell reference B7. The new formula should be =B2*$B$7. This is to set the cell B7 to become an absolute reference. You can change the formula in the formula bar.

5. Click and drag the AutoFill handle down to cell C5. The sales are recalculated.

Click at cell C3, the formula appears to be =B3*$B$7. The cell reference B7 (absolute reference) remains unchanged while cell reference B2 (relative reference) changes to B3.

TIPS



Using Simple Functions Functions are used to simplify and shorten formula. Functions are predefined formulas that perform calculations with specific input (arguments) in a particular structure. For example: =A1+A2+A3+B1+B2+B3+C7 could be replaced using functions as below:

=SUM(A1:B3,C7) A function begins with the function name, followed by an opening parenthesis, the arguments (multiple arguments are separated using commas), and ends with a closing parenthesis. An Equal sign = is used if the function is at the beginning of a formula. Arguments can also be constants, formulas, or functions. For Example: The SUM function adds up the values found within the parentheses. =SUM(1,4,7) equals 12

Comma separators arguments

Equal sign

Function name

Arguments



Other commonly used functions are shown in the following table. Function Name Description Result

MAX()

Find the largest number from the arguments

=MAX(20,25,15) Result: 25

MIN() Find the smallest number from the arguments

=MIN(20,25,15) Result: 15

AVERAGE() Find the average for the arguments =AVERAGE(20,25,15) Result: 15

COUNT() Counts number of cells that contains number from the arguments

=COUNT(1,4,B,3,20) Result: 4

COUNTA() Counts number of cells that are not empty from the arguments

=COUNTA(1,4,B,3,20)Result: 5

Some functions do not need an argument in the parenthesis. Function Name Description Result

Today() Returns current date =Today() Result: 9/4/03

Now() Returns current date and time =Now() Result: 9/4/03 12:20



Managing Worksheet and Workbook Information


Move and copy data on worksheets

Insert and delete cells on worksheets

Rename worksheet

Add and organize worksheets

Copy and delete worksheets

Hide and unhide a worksheet

Hide and unhide a workbook

Format worksheet tab

Add worksheet background



Managing Worksheet Information Excel provides simple and easy ways to let you reorganized the data or the information on your worksheets. You can move or copy the cells to another location. You can also add or delete cells, rows or columns on your worksheets. How to Move Information Using Cut and Paste

1. Select the cells. 2. Click . 3. Click the destination

cell.

4. Click .

If you cant find the

button on the toolbar,

click on the far right end of the toolbar to expand the toolbar.


shortcut key to cut and paste. To cut cells: Press +. To paste cells: Press +.

1. Select the range A1:C4. You wish to move the selected range to A6.

2. Click Cut button on the Standard toolbar. The selected range is cut and stored on the clipboard.

3. Click cell A6. This is to specify where you want to paste the selected range.

4. Click Paste button on the Standard toolbar. The selected range is pasted in the destination, as illustrated below.

SUMMARY

TIPS



How to Move Information Using Drag and Drop

1. Select the cells. 2. Place your mouse

pointer at the thick outline of the highlighted range.

3. Click and drag the range to the destination.

4. Release the mouse button.

1. Select the range A1:C4. You wish to move the selected range to cell A6. If you have moved the range to cell A6 in previous lesson. You can either undo the previous actions or move the table back to the cell A1.

2. Place your mouse pointer at the thick outline of the highlighted range. The mouse pointer changes to an arrow as shown below.

3. Click and drag the range to the destination. A gray outline appears to indicate the new location.

4. Release the mouse button to drop the cells. The selected range moves to the new location.

SUMMARY



How to Copy Information Using Copy and Paste

1. Select the cells.

2. Click . 3. Click the destination

cell.

4. Click .

You can also use the shortcut key to copy and paste. To copy cells Press +. To paste cells Press +.

1. Select the range A1:C4. You wish to move the selected range to A6. If you have moved the range to cell A6 in previous lesson. You can either undo the previous actions or move the table back to the cell A1.

2. Click Copy button on the Standard toolbar. The selected range is copied onto the clipboard.

3. Click the cell A6. This is to specify where you want to paste the selected range.

4. Click Paste button on the Standard toolbar. The selected range is pasted as shown below. The paste option appears at the lower-right corner of the pasted range.

SUMMARY

TIPS



How to Use Paste Options

1. Click Paste

Options. 2. Click the paste option

you want from the list.

The cell A6 shows 0

(zero) because it contains a formula referring to cell A1, which is empty. You can delete the formula in cell A6 to remove the 0.

1. Click Paste Options. A list of paste options appears as shown below.

2. Click Link Cells option from the list. The destination table is linked to the original table. If you change the data in the original table, the data in the destination table changes as well. You can see that the destination table cells contain formulas referring to the original table. For example, the cell C9 is linked to cell C4.

SUMMARY

TIPS



How to Copy Information Using Drag and Drop

1. Select the range. 2. Place mouse at the

thick outline of the highlighted range.

3. Click and drag the range to the destination.

4. Hold down . 5. Release the mouse

button.

1. Select the range A1:C4. You wish to move the selected range to A6.

2. Place your mouse pointer at the thick outline of the highlighted range. The mouse pointer changes to an arrow as illustrated.

3. Click and drag the range to the destination. A gray outline appears to indicate the new location.

4. Hold down the key, before you release the mouse to drop the cells. A plus sign is shown on the upper-right corner of the arrow as illustrated. Hold down key is to copy the selected range instead of moving it.

5. Release the mouse button to drop the cells. The selected range is copied to the new location.

SUMMARY



How to Insert a New Row

1. Select the row, above

which you want to insert a row.

2. Right-click the highlighted area.

3. Click Insert.

To quickly open the

shortcut menu, you can right-click on the row 3 heading.

1. Click the row 3 heading to select the entire row. You wish to insert a new row above row 3.

2. Right-click the highlighted area. A shortcut menu appears.

3. Click Insert from the shortcut menu. A new row is inserted above the selected row as shown below.

SUMMARY

TIPS



How to Insert Multiple Rows

1. Select the rows,

above which you want to insert the rows.

2. Click Insert >> Rows.

The number of rows

you insert depends on the number of rows you have selected. You can also right-

click the highlighted range to display the shortcut menu. Then, click Insert from the shortcut menu.

1. Select the rows 5 and 6. If you select 2 rows, you will insert 2 rows.

2. From the menu, click Insert >> Rows. The Rows command is enabled if you have selected the rows. The rows are inserted above the selected rows.

SUMMARY

TIPS



How to Delete Rows

1. Select the rows you

want to delete. 2. Right-click at the

selected area. 3. Click Delete.

1. Select the rows 5 and 6. You want to delete the selected rows. Click the row headings to select the rows.

2. Right-click the selected area. A shortcut menu appears.

3. Click Delete from the shortcut menu. The selected rows are deleted.

SUMMARY



How to Insert a New Column

1. Select the column

where you want to insert a column.

2. Right-click at the selected area.

3. Click Insert.

1. Select the column B. You want to insert a new column on the left of column B. To select the entire column B, click the column B heading.

2. Right-click the highlighted area. A shortcut menu appears.

3. Click Insert from the shortcut menu. A new column is inserted.

SUMMARY



How to Insert Multiple Columns

1. Select the columns. 2. Click Insert >>

Columns.

1. Select the column D and E. You want to insert 2 new columns on the left of Feb.

2. From the menu, click Insert >> Columns. The Insert Columns command is only available if you have selected a column. The columns are inserted on the left of the selected columns, as shown below.

SUMMARY



How to Delete Columns

1. Select the columns

you want to delete. 2. Right-click the

selected area. 3. Click Delete.

1. Select the columns D and E. You want to delete the selected columns.

2. Right-click the selected area. A shortcut menu appears.

3. Click Delete from the shortcut menu. The selected columns are deleted.

SUMMARY



How to Insert a Cell

1. Right-click the cell. 2. Click Insert. 3. Click the option you

want. 4. Click the OK button.

1. Right-click the cell B2. You want to insert a blank cell at cell B2. A shortcut menu appears.

2. Click Insert from the shortcut menu. The Insert dialog box appears.

3. Click the Shift cells down option button. This will move the existing cells down.

4. Click the OK button. A cell is inserted as shown below.

SUMMARY



Managing Workbook Information Whenever you create a new workbook, Excel creates 3 worksheets by default, namely Sheet1, Sheet2 and Sheet3 in the new workbook. If you want, you can change the default number of worksheets created in a new workbook. 1. From the menu, click Tools >> Options. 2. Click the General tab. 3. In the Sheets in new workbook box, enter the number of worksheets you want. 4. Click the OK button.

You can also manage and organize your worksheets after the workbook is created. You can add new worksheets; delete existing worksheets; change worksheets' name; and move or copy the worksheets.



How to Rename Worksheet

1. Double-click the

sheet tab you want to rename.

2. Type the new worksheet name.

3. Press .

You can also right-

click at the sheet tab and click Rename from the shortcut menu to rename a worksheet.

1. Double-click the Sheet1 tab. You want to rename the worksheet. The sheet name is highlighted when you double-click on it.

2. Type Jan. The `Sheet1' is replaced by Jan.

3. Press . The worksheet name changes to Jan.

TIPS

SUMMARY



How to Add New Worksheet

1. Right-click the sheet

tab, where you want to insert a new sheet.

2. Click Insert from the shortcut menu.

3. Click the Worksheet icon.

4. Click the OK button.


menu to insert a worksheet. From the menu, click Insert >> Worksheet.

4. Right-click the Jan sheet tab. You want to insert a new sheet on the left on Jan sheet.

5. Click Insert from the shortcut menu. An Insert dialog box appears.

6. Click the Worksheet icon and click the OK button. A new sheet name Sheet4 is inserted on the left of Jan sheet as illustrated below.

SUMMARY

TIPS



How to Rearrange Worksheet

Click and drag the

worksheet tab to the left or right along the sheet tabs.

1. Click and drag the Sheet4 tab to the right along the row of sheet tabs to the right of Jan tab. You want to move the Sheet4 to the right of Jan sheet. The small arrow indicates the new location of the sheet as shown below.

.

2. Release the mouse button. The sheet moves to the right of the Jan tab.

How to Copy Worksheet

If you want to copy

the sheet to another workbook, right-click the Worksheet tab, click move or copy command. Select the destination workbook from the to Book list box. (The destination workbook must be opened before you copy the worksheet).

1. Hold down the key. This is to copy instead of moving the sheet.

2. Click and drag the Jan tab to the right along the row of sheet tabs. You want to copy the Jan sheet to the right of Sheet4. You can see that a small arrow appears on the upper right of Sheet4 tab as shown below. A plus sign appears in the mouse pointer, indicating that you are copying the worksheet.

3. Release the mouse button on the right of Sheet4 tab. Another copy of Jan sheet is created on the right of Sheet4 tab. The new worksheet will be named after the source worksheet with a number behind it as shown below.

SUMMARY

TIPS



How to Format Worksheet Tab

1. Right-click the

worksheet tab, for which you want to change color.

2. Click Format >> Sheet >> Tab Color.

3. Click the color you want.



menu to change the tab color. From the menu, click Format >> Sheet >> Tab Color.

1. Right-click the Jan worksheet tab. You want to change the Jan tab color.

2. Click Tab Color. The Format Tab Color dialog box appears.

3. Click Red. You can select another color if you want.

4. Click the OK button. A red underline appears in Jan sheet tab when the worksheet is still selected.

If you click at another sheet tab, the Jan sheet tab appears in red, as shown below.

SUMMARY

TIPS



How to Add Worksheet Background

1. Select the worksheet,

for which you want to change the background.

2. Click Format >> Sheet >> Background.

3. Select the image you want.

4. Click Insert.

To remove the

background. From the menu, click Format >> Sheet >> Delete Background.

1. Click the Sheet 4 tab to select sheet 4. You want to change the background of the worksheet.

2. From the menu, click Format >> Sheet >> Background.The Sheet Background dialog box appears.

3. In the Look in box, select Windows folder. Scroll to select the Feather Texture.bmp. Depending on your windows setup, you may not find Feather Texture.bmp in your Windows folder. Look for any other image file you can find in your computer.

4. Click the Insert button. The image appears as the background of the worksheet.

TIPS

SUMMARY



How to Delete Worksheet

1. Right-click the sheet

tab. 2. Click Delete from the

shortcut menu. 3. Click the OK button.

1. Right-click the Sheet3 tab. You want to delete Sheet3. The shortcut menu appears.

2. Click Delete from the shortcut menu. Excel will prompt you to confirm the deletion if the worksheet contains data. Else, the worksheet will be deleted immediately.

3. Click the Delete button to confirm the deletion. The selected worksheet is deleted.

SUMMARY


Formatting Worksheet


Adjust column width and row height

Hide / unhide rows and columns

Freeze panes

Split worksheet in panes

Format cells background and border

Change font and font size

Bold, Italic and underline

Align data in a cell

Merge cells and center data

Change number to percentage

Increase and decrease decimal points

Copy format using format painter

Double underline using menu option

Align vertically

Change text orientation

Wrap text in a cell

Format numbers

Format date

Change cell background color

Add outline border

Add double line border

Format table using AutoFormat



Adjusting Column Width / Row Height The column width and the row height of the worksheet could be modified to display the worksheet appearance. The row height is adjusted automatically when you change the size of the cell content. You can also adjust the appearance manually with the mouse or the menu options.

Adjusted column width

Adjusted row height



How to Adjust Column Width Using Mouse

1. Position your mouse

pointer at the boundary on the right of the column heading of which you want to adjust the width.

2. Click and drag to the width you want.

To AutoFit the width

of a column - double-click at the right boundary of the column heading or - select the column, then click Format >> Column >> AutoFit Selection. To adjust a few

columns' width at the same time, select the columns you want, and then drag any column-heading boundary within the selection.

1. Position your mouse pointer at the boundary on the right of the Column D heading. This is to adjust the column D width. The pointer is changed to a double-headed arrow, as shown below.

2. Click and drag to the width you want. The new column width appears when you drag the boundary.

3. Release the mouse button. The column width is adjusted as shown below.

SUMMARY

TIPS



How to Adjust Column Width Using Menu

1. Select the column, for

which you want to adjust the width.

2. Click Format >> Column >> Width.

3. In the Column width box, type the new width you want.


1. Select the Column B and Column C. Click and drag the column heading B and C to select the columns.

2. From the menu, click Format >> Column >> Width. In the Column width box, type 6. A Column Width dialog box appears, as shown below.

3. Click the OK button. The width of the selected columns is adjusted.

SUMMARY



How to Adjust Row Height Using Mouse

1. Position your mouse

pointer at the boundary below row heading of which you want to adjust the height.

2. Click and drag to the height you want.

To AutoFit the

height of a row - double-click at the boundary below the row heading or - select the row, then click Format >> Row >> AutoFit. To adjust a few

rows' height at the same time, select the rows you want, and then drag any row-heading boundary within the selection.

1. Position your mouse pointer at the boundary below the Row 2 heading. This is to adjust the row 2 height. The pointer changes to a double-headed arrow, as shown below.

2. Click and drag to the height you want. The new row height appears near the mouse pointer when you drag the boundary.

SUMMARY

TIPS



How to Adjust Row Height Using Menu

1. Select the row. 2. Click Format >>

Row >> Height. 3. In the Row height

box, type the new height you want.


1. Select the row 2. Click the row 2 heading.

2. From the menu, click Format >> Row >> Height. In the Row height box, type 40. A Row Height dialog box appears, as shown below.

3. Click the OK button. The row height is adjusted.

SUMMARY



How to Hide / Unhide Rows / Columns

To Hide Rows 1. Select the cells in the

rows that you want to hide.

2. Click Format >> Row >> Hide.

To Hide Columns 1. Select the cells in the

columns that you want to hide.

2. Click Format >> Column >> Hide.

To Unhide Rows 1. Select the cells on

either side of the hidden rows.

2. Click Format >> Row >> Unhide.

To Unhide Columns 1. Select the cells on

either side of the hidden columns.

2. Click Format >> Column >> Unhide.

1. Click cell A4. You want to hide the Mary's data.

2. From the menu, click Format >> Row >> Hide. The entire row 4 disappears.

3. Click and drag to select A3:A5. You need to select the cells on either side of the hidden row or column.

4. From the menu, click Format >> Row >> Unhide. Row 4 appears, as shown below.

SUMMARY



How to Freeze a Column / a Row

To Freeze Panes 1. Click the cell to the

right of the columns you want to freeze, or/and below the rows you want to freeze.

2. Click Window >> Freeze Panes.

To Unfreeze Panes 1. Click Window >>

Unfreeze Panes.

You will only see the

Unfreeze Panes command after freezing panes.

1. Click cell B3. You want to freeze column A and row 2, so that when you scroll your screen, the sales person names and the column titles remain on your screen.

2. From the menu, click Window >> Freeze Panes. You can see that black lines appear on the left and above the active cell.

3. Scroll down you screen. The column titles remain on your screen.

4. Scroll to the right. The sales person names remain on your screen.

5. From the menu, click Window >> Unfreeze Panes. The freeze panes are removed.

SUMMARY

TIPS



How to Split a Worksheet in Panes

1. Click the cell where

you want to split the worksheet in panes.

2. Click Window >> Split.

3. Click Window >> Remove Split to remove the panes.

Splitting worksheet in

panes is useful when you want to view different parts of the data on a large worksheet.

You can also remove

split or adjust the proportions of the panes by dragging the separators between the panes.

1. Click cell E8. The worksheet will split at the position of the active cell E8.

2. From the menu, click Window >> Split. The worksheet window splits into 4 panes, as shown below.

Use the vertical and horizontal scroll bar to scroll to show data in the empty panes. You can fill the other panes with the data from different parts of the worksheet.

3. From the menu, click Window >> Remove Split. The panes are removed.

SUMMARY

TIPS



Formatting Cell Using Toolbar Excel allows you to control the appearance of the cells. This includes the data format, font, alignment, border, and pattern of the cells. How to Change Font

5. Select the cell, which

you want to change the font.

6. Click the Font drop-down arrow on the Formatting toolbar.

7. Click the font you want from the list.

1. Select the range B2:D2, which you want to change the font. The cells are highlighted.

2. Click the Font drop-down arrow on the Formatting toolbar. A list of the font types appears.

3. Click Times New Roman from the list. Note that the font in range B2:D2 has changed.

SUMMARY



How to Change Font Size

How to Bold text: Click or press + How to Italic text: Click or press + How to Underline text: Click or press + How to Change Font Color:

Click drop-down arrow and click the color you want.

1. Select the range A3:A5. You want to format the sales persons names.

2. Click Font Size drop-down arrow on the Formatting toolbar. A list of the font sizes appears.

3. Click 12 from the list. The font size in the range A3:A5 has changed.

How to Bold

1. Click Bold button.

How to Italic

1. Click Italic button.

How to Underline

1. Click Underline button.

SUMMARY



How to Change Font Color

If the formatting

buttons are not visible on the formatting toolbar, you can click

at the right of the formatting toolbar to show more buttons.

1. Click the Font color drop-down arrow on the Formatting toolbar. The Font Color Palette appears.

2. Click the Blue color. The font is formatted as shown below.

TIPS



How to Align Data in a Cell

a. Select the range you

want to align. b. Click the Alignment

button you want on the Formatting toolbar.

To align center, click

To align left, click

To align right, click

You can also

Justify Align the data in the cell. Select the range. From the menu, click Format >> Cells. Click the Alignment tab. Under Text alignment, in the Horizontal box, click Justify.

1. Select the range B2:C2. You want to align center the content in the range.

2. Click Center button. The cell content is aligned to center horizontally.

Alignment Button Description

Align Center Align cell content to center.

Align Left Align cell content to left.

Align Right Align cell content to right.

SUMMARY

TIPS



How to Indent Data in a Cell

To Increase indent,

click To decrease indent,

click

1. Select the cell D2. You want to increase the indent for the content in the cell.

2. Click Increase Indent button. You can see that the indent in the cell has increased. Click the Increase Indent button a few times to increase the indent for the cell.

To decrease the indent, click decrease indent button.

SUMMARY



How to Merge Cells and Center Data

a. Select the cells you

want to merge.

b. Click on the Formatting toolbar.

1. Select the range A1:D1. You want to align the title of the table to the center of the entire table width.

2. Click Merge and Center button. The selected cells are merged and the title of the table is aligned to the center of the merged cells.

SUMMARY



How to Change Number to Percentage

1. Select the cells you

want to change to percentage.

2. Click .

1. Select the range D3:D5. The selected numbers need to be changed to percentage format.

2. Click Percentage button. Numbers are changed to percentage without decimal.

SUMMARY



How to Increase/ Decrease Decimal Points

To increase decimal points 1. Select the cells you

want to increase decimal points.

2. Click . To decrease decimal points 1. Select the cells you

want to decrease decimal points.

2. Click .

1. Select the range D3:D5. You want increase and decrease the decimal places.

2. Click Increase Decimal button twice. The numbers changes to 2 decimal points.

3. Click Increase Decimal button. The decimal numbers changes to 1 decimal point.

SUMMARY



How to Copy Format Using Format Painter

1. Click the cell, for

which you want to copy the format.

2. Click . 3. Select the cells, to

which you want to paste the format.

You can only paste

the copied format once if you click the Format Painter button once. If you want to paste

the format you copy to multiple non-adjacent cells or ranges, double-click the Format Painter when you copy the format. After finish pasting the format, press to disable the format painter.

1. Select the cell A3. You want to copy the format in the cell A3.

2. Click Format Painter button. The format of the cell A3 is copied and your mouse pointer changes to a format painter.

3. Click the cell A6 to paste the format. The format of cell A3 is pasted to cell A6, as shown below. You can also click and drag to paste the format onto a range of cells.

SUMMARY

TIPS



Formatting Cell Using Menu Options You can format the cell using Format Cells dialog box from the menu. From the menu, click Format >> Cells, the Format Cells dialog box appears as shown below.

Tab Description

Number Specify the format style of the context in a cell.

Alignment Specify text alignment, text control and text orientation.

Font Specify font, font style, font size, font color, font effect and underline.

Border Specify border color and line style.

Patterns Specify the cell pattern and color.

Protection Protect the cell to be locked to avoid changes and formula to be hidden from viewer.



How to Double Underline Using Menu Option

1. Select the cell. 2. Click Format >>

Cells. 3. Click the Font tab. 4. Click Underline drop-

down arrow and click Double.


1. Select the range A3:A5. You want to format the text to have double underline instead of one.

2. From the menu, click Format >> Cells. Click the Font tab. The Format cells dialog box appears.

3. Click the Underline drop-down arrow, and click Double from the list. The single underline is changed to double underline.

4. Click the OK button. The cells format changes as shown below.

SUMMARY



How to Align Vertically


Cells. 3. Click the Alignment

tab. 4. Click the Vertical

drop-down arrow and click Center.


1. Select the range B2:C2. You want to align the months to the middle of the cell.

2. From the menu, click Format >> Cells. Click the Alignment tab. The Format Cell dialog box appears.

3. Click the Vertical drop-down arrow, and click Center. You can also try other option, if you want.

4. Click the OK button. The months align to the middle of the cells.

SUMMARY



How to Change Text Orientation



tab. 4. In the Degrees box,

type the degrees you want.


1. Select the range B2:C2. You want to change the text orientation.


3. In the Degrees box, type 45. You can also click and drag the red diamond shape using the mouse to change the degrees.

4. Click the OK button. The months text orientation changes to 45 degrees anti-clock wise.

5. Change the Text Orientation again to 90 degrees anti-clock wise. The text orientation of the months changes, as shown below.

SUMMARY



How to Wrap Text in a Cell



tab. 4. Under Text control,

click to check the Wrap text check box.


If you want the word

`US to appear on the third line, click at the left of the word `US in formula bar and press + to insert a line break.

1. In cell A9, type Unit Price in US currency. Then click the confirm button on the formula bar. If you click the confirm button, the active cell remains in cell A9.


3. Under Text control, click to check the Wrap text check box. The Shrink to fit is disabled if the Wrap text is checked.

Text control options

Description

Wrap text Wrap text into multiple lines, depending on the column width and the length of the cell contents in a cell.

Shrink to fit Adjust the font size so that all data in a selected cell fits within the column.

Merge cells Combines two or more selected cells into a single cell.

4. Click the OK button. The text wraps within the cell A9.

SUMMARY

TIPS



How to Format Numbers

1. Select the cells. 2. Click Format >>

Cells. 3. Click the Number

tab. 4. In the Category box

click Currency from the list.


If the cell displays

####### as shown below,

you need to enlarge the column width in order to see the cell content.

1. Select the range C3:C6. You want to format the numbers to currency.

2. From the menu, click Format >> Cells. Click the Number tab. The Format Cell dialog box appears.

3. In the Category box, click Currency from the list. The details options for the category appear on the right. Change the options if necessary.

4. Click the OK button. The numbers changes to currency. Note: The column is too small to display the contents. You need to enlarge the column width to show the contents, as shown below.

SUMMARY

TIPS



How to Format Date

1. Select the cell that

contains a date. 2. Click Format >>

Cells. 3. Click the Number

tab. 4. In Category box,

click Date. 5. In the Type box, click

the format you want. 6. Click the OK button.

1. In the cell B10, type Report Date and in the cell C10, type 9/20/03. The cell changes to a date format automatically.

2. From the menu, click Format >> Cells. Click the Number tab. The Format Cell dialog box appears.

3. In the Category box, click Date. In the Type box, click 14-Mar-01 formats. You can see that the preview of the data appears in the Sample area.

4. Click the OK button. The date format changes, as shown below.

.

SUMMARY



How to Change Cell Background Color


Cells. 3. Click the Pattern tab. 4. Under Color, click the

color you want. 5. Click the OK button.

1. Select the range A6:C6. You want to change the cells background color to yellow.

2. From the menu, click Format >> Cells. Click the Pattern tab. The Format Cells dialog box appears.

3. Under Color, click Yellow from the color palette. You may choose the pattern if you want.

4. Click the OK button. The cells background changes to yellow.

SUMMARY



How to Add Outline Border


Cells. 3. Click the Borders

tab. 4. In the Line Style box,

click the line style you want.

5. Under Presets or the Border area, draw the border using the buttons available.


If you want to remove

all the borders, under Presets, click the None button.

1. Select the range A2:D6. You want to draw an outline for the selected range.

2. From the menu, click Format >> Cells. Click the Borders tab. The Format Cells dialog box appears.

3. In the Line Style box, click the thick line style. Click the Color drop-down arrow, select blue. Under Presents, click the Outline button. The selected options appear as shown below.

4. Click the OK button. An outline is added to the selected range.

SUMMARY

TIPS



How to Add Double Line Border


Cells. 3. Click the Borders

tab. 4. In the Line Style box,

click the Double Line style.

5. Under the Presets or Border area, draw the border using the buttons available.


To remove the

border, click the Border button again.

1. Select the range A3:D3. You want to draw a double border above the selection.

2. From the menu, click Format >> Cells. Click the Border tab. The Format Cells dialog box appears.

3. In the Line Style box, click Double Line style. Under Border, click Top Border button. The border settings are as shown below.

4. Click the OK button. A double borderline appears above the selection.

SUMMARY

TIPS



How to Format Table Using AutoFormat

1. Select the table

range. 2. Click Format >>

AutoFormat. 3. Click the table style

you want. 4. Click the OK button.

1. Select the table range A1:D6. You want to apply a predefine table format at the selected table.

2. From the menu, click Format >> AutoFormat. Click the Classic 2 table style. The AutoFormat dialog box appears.

3. Click the OK button. The Classic 2 style is applied to the selected table.

SUMMARY


Setting Up Page and Print


Insert and remove page break

Set print area

Setup Page Orientation

Change scaling and paper size

Control page margins

Align to center of page

Add header and footer

Preview the worksheet

Print active sheets

Print selected cells

Print charts



Setting Up Page Page formatting allows you to control features that will affect the entire page such as page orientation, the scaling, paper size, page margins, header and footer of the page etc. Your settings are applicable to the worksheet where you set the page properties.

Footer Footer

Left Margin

Right Margin

Top Margin

Bottom Margin

Page Orientation: Portrait Page Orientation: Landscape Scaling: 150% of original. Alignment: Center on page Vertically and Horizontally

Header



How to Insert and Remove Page Break

To Insert a Page Break 1. Click the cell, above

which you want to insert a page break.

2. Insert >> Page Break.

To Remove a Page Break 1. Click the cell, which is

below or on the right of the page break you want to remove.

2. Insert >> Remove Page Break.

1. Click cell A8. This is where you want to insert the page break.

2. From the menu, click Insert >> Page Break. A page break indicator (dashed line) appears above the active cell. If you print or preview the page, you will find that the first table is printed on the first page, while the second table is printed on the second page.

3. Click cell A8. You want to remove the page break. To remove a page break, you need to click at the cell below or on the right of the page break line.

4. From the menu, click Insert >> Remove Page Break. The page break is removed.

SUMMARY



How to Set and Clear Print Area

To Set Print Area 1. Select the range,

which you want to set as the Print Area.

2. Click File >> Print Area >> Set Print Area.

To Clear Print Area 1. Click File >> Print

Area >> Clear Print Area

If you want to set print

area, which is non-adjacent, press while selecting the cells.

1. Select the range A2:B6. You want to set this range as the print area. Whenever you print your worksheet, only the contents in the print area will be printed.

2. From the menu, click File >> Print Area >> Set Print Area. A dashed outline appears around the Print Area, as shown below

Click the print preview button to preview the printout. Only the cells in the print area would be printed.

3. From the menu, click File >> Print Area >> Clear Print Area. If you do not set a print area, the entire worksheet will be printed.

SUMMARY

TIPS



How to Change Page Orientation

1. Click File >> Page

Setup. 2. Click the Page tab. 3. Under Orientation,

click the option you want.


You will not be able to

preview your worksheet if you have not setup your printer.

1. From the menu, click File >> Page Setup. Click the Page tab. The Page Setup dialog box appears, as shown below.

2. Under Orientation, click the Landscape option. The default orientation is portrait.

3. Click the OK button. The orientation of the page is set to landscape (horizontal).

How to Change Scaling

1. Click File >> Page

Setup. 2. Click the Page tab. 3. Under Scaling, in the

Adjust to box, enter the percentage.


1. From the menu, click File >> Page Setup. Click the Page tab. Page Setup dialog box appears, as shown below.

2.

1-excel

Documents

new default excel workbook

reference guidemicrosoft

range of cells

single cell

entire column

entire row

entire worksheet

copyright notice copyright