toc

Team LiB
Next Section

Microsoft Excel Whiz 2002: Official Certification Guide for Microsoft Office Specialist Core Exam
Microsoft Excel Whiz 2002: Official Certification Guide for Microsoft Office Specialist Core Exam
by Innovative Training Works ISBN:0974142565
Innovative Training Works © 2003 (184 pages)

This book fully covers the Microsoft Office Specialist Excel 2002 Core Certification exam objectives; it is designed to be progressive and modular creating ease of use for beginners yet comprehensive enough as a reviewer.

Table of Contents
 | Microsoft Excel Whiz 2002—Official Certification Guide for Microsoft Office Specialist Core Exam
 | Chapter One | - | Introduction to Spreadsheets
 | Chapter Two | - | Data and Cells
 | Chapter Three | - | Formatting Worksheets
 | Chapter Four | - | Formulas and Functions
 | Chapter Five | - | Working with Worksheet and Workbooks
 | Chapter Six | - | Page Setup and Printing
 | Chapter Seven | - | Charts and Objects
 | Chapter Eight | - | Collaborating Workbooks
 | Skills Projects
 | Index
 | List of Figures
 | List of Examples
 | List of Sidebars

Team LiB
Next Section


backcover

Team LiB

Back Cover

This book is exactly what you need to increase proficiency on Microsoft Excel 2002.

The book fully covers the Microsoft Office Specialist Excel 2002 Core Certification exam objectives. Every chapter contains specific Learning objectives, Whiz Words, Lesson Summary, Study Help, Exercises and Activities. It is designed to be progressive and modular creating ease of use for beginners yet comprehensive enough as reviewer for the Microsoft Office Specialist certification exam.

Team LiB


Microsoft Excel Whiz 2002 - Official Certification Guide for Microsoft Office Specialist Core Exam

Team LiB

Microsoft Excel Whiz 2002—Official Certification Guide for Microsoft Office Specialist Core Exam

Innovative Training (I.T.) Works

Irvine, CA
http://www.itworkscentral.com

Microsoft ® Excel Whiz 2002

Copyright © 2003 Innovative Training (I.T.) Works USA Inc.

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher.

0-9741425-6-5

Visit us at http://www.itworkscentral.com for other Microsoft Office Specialist book titles and Computer Learning titles especially for K-12

Limit of Warranty/Disclaimer of Warranty: The publisher and authors have used their best efforts in preparing this book. The publisher and authors make no presentations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. There are no warranties which extend beyond the descriptions contained in this paragraph. No warranty maybe created or extended by sales representatives or written sales materials. The accuracy and completeness of the information provided herein and the opinions stated herein are not guaranteed or warranted to produce any particular results and the advice and strategies contained herein may not be suitable for every individual. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Neither is any liability assumed for damages resulting from the use of the information contained herein.

“Use of the Microsoft Office Specialist Approved Courseware Logo on this product signifies that it has been independently reviewed and approved in complying with the following standards:

‘Acceptable coverage of all content related to Microsoft Office exams entitled Microsoft Word Whiz 2002 Core and sufficient performance-based exercises and activities that relate closely to all required content, based on sampling of text.’

All trademarks, registered trademarks, copyrights are the property of their respective companies.

Acknowledgement

Innovative Training (I.T.) Works, Inc. acknowledges the creators of art works and/or clip arts used in this book. While I.T. Works reserves all rights as original creator of this book, some clip arts are originals of their creators and were used in whole or in part to enhance the total look and quality of this activity book for more effective learning.


Chapter One: Introduction to Spreadsheets

Lesson 1: Getting To Know the Spreadsheet

What Is a Spreadsheet?

A Spreadsheet or worksheet is a tool used whenever you need to perform calculations. These calculations can involve mathematical, financial, or scientific data. Some typical uses of spreadsheets are:

  • Household Taxes

  • Grading System

  • Income Taxes

  • Inventories

  • Mortgage Payments

Two Types of Spreadsheet

Manual Creating a worksheet on ledger paper or columnar using a paper, pencil, calculator, and eraser.

Electronic A computer-generated spreadsheet that makes the basic task much easier.

What is a Spreadsheet program?

A spreadsheet program is a program that generates a user's worksheet on-screen and enables him to embed hidden formulas that perform visible calculations.

The Story behind Spreadsheet

In 1978, a Harvard Business student named Dan Bricklin got tired of adding up columns of numbers - and adding them up again after making a few changes - just to assess the effect of the merger. Bricklin, who knew a little about computers from summer jobs at Wang and other firms, came up with the idea of a spreadsheet program running on a personal computer.

Bricklin's teachers and a programmer friend, Bob Frankston, produced VisiCalc. It is a program for Apple II computer. This marked an important new chapter in American enterprise, the Electronic Spreadsheet.

VisiCalc was a huge success. More than 700,000 copies of the program were sold. It was almost single-handedly responsible for the success of the Apple II personal computer. By 1984, it disappeared when IBM launched Lotus 1-2-3, an integrated program that combines analytical graphics and database management with what is clearly a clone of VisiCalc spreadsheet.

Many innovations did come out, especially during the 90's when the development of microchips was unstoppable, and there was rapid development in various application programs. Finally, the year of the GUI's (Graphical User Interface) desktop arrived. The next decade now belongs to "Microsoft's Software King" - Bill Gates, who gave birth to Microsoft Office software such as Microsoft Excel, which operates almost like Lotus 1-2-3 but is much faster and more user-friendly. It works on a graphical desktop environment with its electronic input device companion; the "witty-mouse".

Click To expand
Figure 1.1: The Electronic Spreadsheet

Elements of the Electronic Spreadsheet.

Row | It is a horizontal block of cells running across the breadth of the spreadsheet. Rows are numbered sequentially from the top.
Column | It is a vertical block of cells identified by a unique alphabetical letter.
Worksheet | It is made up of rows and columns where you enter labels, numbers, and formulas. It contains 256 columns with alphabet headings and 65,536 numbered rows.
Cell | It is the rectangle formed by the rows and columns. The combination of the column letter and the row number is called cell reference or cell address. You might notice a rectangular highlight that indicates the location on-screen of the active cell. This is called active cell indicator.
Workbook | It is also called the file. It contains multiple worksheets and chart sheets. You can switch between worksheets within a workbook easily. You can also name the sheets separately to easily distinguish them from one another.
Range | It is a block of selected cells. It is also a selection of multiple cells.
Cell Content | This is any data entered into the cell. This can be numbers, letters, formulas, date, and time.
Database | This is a collection of related information about a subject organized in a useful manner.
Whiz Words
Start example

Active Cell

Cell Address

Active Cell Indicator
End example

Lesson Summary

A spreadsheet program is an electronic spreadsheet. It is called a worksheet because it is a program designed with a matrix of rows and columns. It speeds up the process of making reports especially in numbers because it has the capability of making computations fast. Dan Bricklin, a student of Harvard University, together with his programmer friend Bob Frankston, produced VisiCalc, a program for Apple II computer, which began the use of spreadsheet programs in 1978. There are two types of spreadsheet, the manual and electronic spreadsheet.

Study Help
Start example

Directions: Answer the following.

  1. What is a spreadsheet?

  2. Give at least five elements of the spreadsheet.

  3. Who is Dan Bricklin? What is his contribution in the development of electronic spreadsheet?

End example
Start Sidebar
Activity 1
Click To expand
End Sidebar
Start Sidebar
Activity 2

Make a simple database.

Directions:

Collect the following information about your friends: Name, Address, Phone Number, School, and Year Level.

  1. Write them in your notebook in tabular form.

  2. Gather at least five records.

End Sidebar


Lesson 2: Introducing MS Excel

Starting Excel

To open MS Excel, follow these steps:

  1. Click the Start button, then point to Programs.

  2. In the Programs menu, look for MS Excel.

  3. Click the MS Excel item.

    Click To expand
    Figure 1.2: The MS Excel Program inside Program menu

After clicking MS Excel, the MS Excel window will appear.

Note

If MS Excel is not available in the Programs menu, you can use the Find command under the start menu and type the executable file excel.exe on the Named text box to open the program.
Click To expand
Figure 1.3: The MS Excel Window

MS Excel has two types of windows, the Application window which represents the Excel program itself, and the Worksheet window which is composed of sheets that contain things you create in Excel.

Parts of the MS Excel Application Window

  1. Title Bar - located at the top of the screen indicating the name of the program.

  2. Menu Bar - located below the title bar. It contains a group of selections that lets you perform most Microsoft tasks. In each menu item are numerous submenu commands.

  3. Toolbars - Allows you to execute a command without opening a menu or dialog box. There are three default toolbars; the standard, the formatting, and the drawing toolbar.

    • Standard Toolbar - contains the most commonly used buttons that perform common tasks, such as saving, checking spelling, starting a new document, etc.

    • Formatting Toolbar - contains buttons that provide shortcuts for choosing fonts, font size, borders, alignments, etc.

    • Drawing Toolbar - contains buttons that you can click to format and draw objects.

  4. Ask a Question Box - returns Help topics that answer questions you typed in the text box.

  5. Name Box - indicates the location of the active cell.

  6. Formula Bar - displays the data you typed in the cell. It also allows you to edit your formula easily.

  7. Task Pane - a window within an office application that provides commonly used commands. Its location and small size allow you to use these commands while working on your files.

  8. Status Bar - displays information about a command or toolbar button, an operation in progress, or the location of the insertion point.

Click To expand
Figure 1.4: The Application Window

Parts of the Worksheet Window

Click To expand
Figure 1.5: The Worksheet Application Window

  1. Column - the vertical areas of the spreadsheet labeled with letters of the alphabet from A to IV.

  2. Row - the horizontal areas of the spreadsheet labeled with numbers.

  3. Active Cell - a selected cell ready for data entry.

  4. Active Cell Indicator - indicates the location of the active cell.

  5. Sheet Tab - used to view a sheet.

  6. Scroll Bars - used to scroll other parts of the document.

To use MS Excel, both the application and worksheet windows should be active. Although you can open the application window without activating the worksheet window, you cannot work without the worksheet window.

Exiting MS Excel

Different ways to exit MS Excel:

  • Clicking the Close [image] Button in the Title bar.

  • Pressing Alt+F4 simultaneously.

  • Double-clicking the Application Control Icon.

  • Clicking File, then Exit.

In closing the worksheet window, do any of the following.

  • From the File menu, click Close.

  • Click the Close [image] Button on the right side of the menu bar.

  • Press Ctrl+F4 simultaneously.

Whiz Words
Start example
Application Window | Worksheet Window
Alt + F4 | Ctrl + F4
Application Control Icon | Ask a Question Box
End example

Lesson Summary

The standard way of opening MS Excel is using the Start button. Once MS Excel has been clicked, the MS Excel window will appear.

There are two types of windows in MS Excel: the Application window and the Worksheet window. There are different ways of Exiting Windows, but the easiest way is to use the shortcut key ALT+F4.

Study Help
Start example

Directions: Answer the following correctly.

  1. Differentiate application window from the worksheet window.

  2. Enumerate the parts of the MS Excel application window.

  3. What are the functions of the shortcut keys such as Alt+F4 and Ctrl+F4?

End example
Start Sidebar
Activity 1

Directions: Follow the following instructions to complete the tasks.

  1. Open MS Excel.

  2. Identify the following buttons by pointing the mouse pointer to the specific button.

    ____________________

    ____________________

    ____________________

    ____________________

    ____________________

    ____________________

    ____________________

    ____________________

    ____________________

    ____________________

  3. Close MS Excel without saving.

End Sidebar


Lesson 3: Managing Files

Saving Files for the First Time

To save a file for the first time, follow these steps:

  1. Do any of the following:

    • On the File menu, choose Save.

    • Press Ctrl+S or F12.

    • Click the Save button in the Standard toolbar.

    The Save As dialog box appears.

  2. In the Save in list, select the drive and folder where you want to save the workbook.

  3. In the File name text box, type the name you want to assign to your workbook. You can use long, descriptive filenames up to 255 characters.

  4. Click Save.

    Click To expand

You have to save every change or correction you make. Remember that every change you make and save in the screen will overwrite the document.

To update your existing file with the changes you made, click Save from the File menu, or press Ctrl+S.

Using Save As

Save As is used to save an existing file in a different location or to have a copy of the same file with a different filename.

Note

To save a copy in a different folder, click a drive in the Save In box, or click a folder name in the folder list, or both. To save a copy in a new folder, click Create New Folder.

Save a Copy of a Workbook

  1. Open the workbook you want to make a copy.

  2. On the File menu, click Save As or press F12. The Save As dialog box appears.

  3. In the Filename text box, type a new name for the workbook.

  4. Click Save.

There are additional options in the Save As dialog box that allow you to navigate to different locations.

These options include the following:

Click To expand

Views - Clicking the arrow beside the Views button will show different choices on how you want the list to appear.

Click To expand

Tools menu - Clicking the arrow beside the Tools menu allows you to delete, rename, print, and display the properties. It also allows the user to add the selected file or folder to the Favorites folder.

Click To expand

If you want your workbook to be protected from other users you may create a password on it. To create a password to your file, click on the Properties from the Tools menu of the Save As dialog box. You can also create backup files, and a read-only file.

To save a file with a password, follow these steps:

  1. Click the File menu, choose Save As.

  2. In the Save As dialog box, click the Tools menu and choose General Options.

    The Save Options dialog box appears.

    • If you want to save a backup copy of the workbook, click on the Always Create Backup check box.

    • If you want to create a password before opening the file, type a password in the Password to Open Textbox.

    • If you enter a password in the Password to Modify Text box, the user will not be able to make changes with the file unless he/she knows the password.

    • If you want to prompt the user that the file is read-only, check the Read-only recommended check box.

  3. Click OK and Save.

    Click To expand

Locate and Open an Existing Workbook

To locate and open an existing workbook, follow these steps:

  1. Open the Excel window.

  2. Do any of the following:

    • Click File, choose Open.

    • Press Ctrl+O.

    • Click the Open [image] button in the Standard toolbar.

      Click To expand

    • From the task pane, click More Workbooks from the Open a Workbook category.

    The Open dialog box appears:

  3. Locate the file by clicking the Look in drop-down arrow and choose the File directory. You may also use the Places bar at the left side of the Open dialog box.

  4. Select the file.

  5. Double-click on the file or press enter.

If you cannot find the workbook, search it by using the Search command.

To search files follow these steps:

  1. Click File, choose Open.

  2. On the Tools menu, click Search. The Search dialog box appears.

  3. In the Search Text text box, type the name of the file.

  4. In the Result should be drop-down list box, specify the type of file.

  5. In the Search In drop-down list box, specify to where the desired file will be searched.

  6. Click Search. After the search is completed, the computer will display the result in the Result list box.

  7. In the Result list box, select that file you want and then click OK.

    Click To expand

The selected file will be displayed in the Filename list box of the Open dialog box including its path. To open the file, click Open.

Create a Folder

To create a folder while saving or opening files follow these steps:

Do one of the following:

  • Click File from the menu bar, choose Save As.

  • Press F12.

The Save As dialog box appears.

  1. If necessary, search for a different drive or folder where you want to place your new folder.

  2. Click the [image] Create New Folder button or press Alt+5. The New Folder dialog box appears.

    Click To expand

  3. Type the name of the folder and click OK.

Create a New Workbook Using Templates

A template is a workbook that you create and use as the basis for other similar workbooks. You can create templates for workbooks and worksheets. It can contain text, formats, formulas, and macros that speed up the creation of a file.

Note

In this example, we will be using the Sales Invoice Template.

To create a new workbook based on a template, follow these steps:

  1. On the File menu, click New, and then click General Templates on the New Workbook task pane. The Templates dialog box appears.

  2. Click the Spreadsheet Solutions tab or the tab listing your custom templates, and then double-click the template for the type of workbook you want to create.

    Click To expand

The template opens with a default name based on the name of the template.

Click To expand

The figure shows an illustration of a new worksheet based on the Sales Invoice template. You will only be able to enter data to a cell that is not protected. A particular cell is not protected if it can be selected.

After entering data in the template, you can now save it and you will be prompted to assign a name for your new workbook.

Click To expand
Start Sidebar
Exercise

  1. Create a new workbook using a template.

  2. Select the Sales Invoice template and click OK.

    Click To expand

  3. Input the necessary information to complete the invoice. You can make use of the illustration below as an example.

  4. Save your worksheet as Invoice.xls

End Sidebar
Whiz Words
Start example

Template

Password

Search Command
End example

Lesson Summary

You can save your existing workbook to a new location or filename by clicking Save As and then specifying the desired location and filename before saving it. You can also create a new folder while in the Save or Open dialog box by just clicking Create New Folder.

The Workbook template allows you to create a workbook based on a pre-formatted workbook. You can choose any template you want in the New Workbook task pane in the General Templates options.

Study Help
Start example

Directions: Answer the following questions briefly.

  1. What is a template?

  2. How do you locate an existing workbook?

  3. How do you create a new folder while in the Save As dialog box?

  4. Why a password is recommended to important files?

  5. Enumerate the templates available in your workbook.

End example
Start Sidebar
Activity

Directions: Follow the instructions carefully.

  1. Open the MS Excel program.

  2. Display the Open dialog box.

  3. Select Drive C: in the Save In drop-down list box. Create a new folder named Excel-Activities.

  4. Search the Invoice.xls and open it.

  5. Have a copy of the Invoice.xls to the Excel-Activities folder.

  6. Put a password that will prompt the user before opening this file.

  7. After creating a password, click Save.

End Sidebar


Chapter Review

I. Identification. Identify the following.

__________1. | It is a tool used whenever you need to perform calculations. These calculations can involve mathematical, financial, or scientific data.
__________2. | It is a vertical block of cells identified by a unique alphabetical letter.
__________3. | It is also called a file. It contains multiple worksheets and chart sheets. You can switch between worksheets within a workbook easily.
__________4. | A computer-generated spreadsheet that makes the basic tasks much easier.
__________5. | It is made up of rows and columns where you enter labels, numbers and formulas. It contains 256 columns with alphabet headings and 65,536 numbered rows.
__________6. | It indicates the location of the active cell.
__________7. | A window within an office application that provides commonly used commands. Its location and small size allows you to use these commands while working on your files.
__________8. | It displays the data you typed in the cell. It also allows you to edit your formula easily.
__________9. | It is used to protect your files from other user.
__________10. | It is use to view a sheet.

II. Enumeration. Write what is asked.

  1. Different ways to exit MS Excel

  2. Parts of the Worksheet Window

III. Labeling: Label the parts of the MS Excel Window. Write your answer on the space provided below.

Click To expand

  1. ________________________

  2. ________________________

  3. ________________________

  4. ________________________

  5. ________________________

  6. ________________________

  7. ________________________

  8. ________________________

  9. ________________________

  10. ________________________


Chapter Two: Data and Cells

Lesson 1: Moving around the Worksheet

In entering data, you must first select the cell. The default location of the cursor upon opening MS Excel is cell A1 which is referred to as the cell address. The cell address is the combination of column heading and the row number. As the cursor is moved, the Name Box displays the new active cell. To move around the worksheet, you can use either the keyboard or the mouse.

Use the keyboard shortcuts to move to a desired cell.

Keyboard Shortcuts
To move right (one cell) | Press right arrow key (→) or Tab
To move left (one cell) | Press left arrow key (←) or Tab
To move down (one cell) | Press arrow down (↓)
To move up (one cell) | Press arrow up (↑)
To a specific cell | Press F5 then type the cell address
To the beginning of a row | Home
To the end of a row | End and arrow right or Ctrl+arrow right
To the beginning of a spreadsheet | CTRL+Home
To the end of a spreadsheet | CTRL+End

You can immediately know the address of the cell by looking at the Name Box; it displays the cell address or the location of the active cell in the active sheet as shown below.

Click To expand
Click To expand

Use the mouse to move around the worksheet.

  • To select a single cell, point to the desired cell (your mouse pointer changes to a thick cross) then click the cell.

The different forms of Mouse Pointer in the spreadsheet.

Arrow - used to select a command from the menu bar or toolbars.

Thick Cross - used to select a cell or range of cells.

I-beam - used to edit text within a formula bar. This pointer appears when your mouse pointer rests on the Name Box and Font Box.

Fill Handle - used to copy cell content.

Double Arrow - allows you to adjust the column width by dragging it to the left and right and the row height by dragging it up and down.
Whiz Words
Start example
Name Box | Ctrl+Home | Active Cell
Ctrl+End | F5 | Fill Handle
End example

Lesson Summary

There are different ways to move around in the worksheet, mouse, keyboard stroke, or the feature of MS Excel for navigating through the worksheet. The cell indicator determines the location of the active cell. You can also determine the exact location of the active cell by simply looking in the Name Box.

Study Help
Start example

Directions: Identify the following.

  • _______________1. It is used to copy cell content.

  • _______________2. The keyboard shortcut if you want to go the end of the spreadsheet.

  • _______________3. The default active cell upon opening MS Excel.

  • _______________4. To go to a specific cell.

  • _______________5. It is used to select a command from the menu bar or toolbars.

  • _______________6. The keyboard shortcut if you want to go to the beginning of the worksheet.

  • _______________7. It is use to select cells or range of cells.

  • _______________8. The key to be pressed if you want to go to the beginning of a row.

  • _______________9. The key to be pressed if you want to go to the end of a row.

  • _______________10. The key to be pressed if you want to move one cell.

End example
Start Sidebar
Exercise

Directions: Follow the instructions carefully.

  1. Open MS Excel.

  2. Move the active cell indicator to cell G5 using the arrow keys.

  3. Click the mouse pointer to D7.

  4. Press F5 to move the active cell indicator to X25.

  5. Perform the following shortcut keys and observe where the active cell indicator will be placed.

    1. CTRL+Home

    2. CTRL+Dow Arrow Key

    3. CTRL+Right Arrow Key

  6. Press F5 to move the active cell indicator to cell A1.

End Sidebar


Lesson 2: Entering Data

There are two basic types of data you can enter in a cell, label and value. The first character in a cell determines whether the cell entry is a numeric data (value) or text (label).

Entering (text) Labels

Labels are any type of data not used in any calculations. Labels are used for column headings, spreadsheet, titles, and descriptions in the spreadsheet and they are automatically left aligned. They also consist of numerical data not used in any calculations, such as dates and serial numbers.

Start Sidebar
Exercise 1

Directions: Follow the instructions carefully.

  1. Open MS Excel.

  2. Make cell A1 the active cell.

  3. Type Name.

    Press the <Backspace> if you make a mistake while typing a cell entry to delete a character on the left side of the cursor one at a time.

    While typing, you will notice that the text appears both in the active cell and in the Formula bar. To confirm the cell entry, press Enter, or click on the Enter button [image] on the Formula bar or press Tab.

  4. Enter the following labels as shown below:

    Click To expand

    Notice that some of the labels do not fit in the cell. Do not worry, you can adjust the column width by double-clicking on the line between the column heading that you want to expand.

    Click To expand

  5. After typing the title labels, input at least fifteen records. Use the data you gathered in Activity 2, Lesson 1, Chapter 1.

    Note

    Excel will recognize your entry as a label if it begins with a letter. If you want Excel to recognize numeric as a label, type an apostrophe (') before it.

  6. Save your work using the filename Information.

    Note

    You can ask for your teachers' assistance to help you save your work in case you do not know how.
End Sidebar

Entering Values

Values are numbers, dates and other numerical data that are usually used in calculations. A data is considered a value if it belongs to this type of numerical data: numbers, dates, times, fractions, percentages, and currencies. When you enter a value, it will automatically be right-aligned.

Start Sidebar
Exercise 2

  1. Open MS Excel.

  2. Open the file Information.

    Note

    Ask for the teachers' help if you don't know how.
    Click To expand

  3. Add another two labels at the end of the label Year Level as shown below.

  4. Input the weekly allowance of each student and his/her expenses every week.

  5. Click Save and exit MS Excel.

End Sidebar

Entering Date and Time

To enter date:

  • Type the date and press Enter or Tab.

  • Press Ctrl+; (semicolon).

MS Excel has different formats in entering dates; it may include the following:

12/5/01 | M/d/yy
12/5/2001 | M/d/yyyy
5-Dec-01 | d-mmm-yy
05-Dec-2001 | dd-mmm-yyyy

Any date you type will be formatted according to the selected date format. You can change the display formatting of the cell by following these steps:

  1. Choose Format, click Cells.

  2. Select the Numeric tab and choose Date in the Category list.

  3. Select format in the Type column list box.

  4. Click OK.

To enter time:

  • Type the Time and press Enter or Tab.

  • Press Ctrl+: (colon).

Working with a Series (Auto fill)

Click To expand

In Excel you can fill adjacent cells with data or formulas. You can do this with the Excel feature, which is called Auto fill. The auto fill uses a fill handle that can be recognized as a thin black cross at the lower right corner of the selected cell or range. Dragging the fill handle across the cells can fill the cells with copies or data series.

Click To expand

  • Using the fill handle with a series of text will only copy the entire text.

  • Using the fill handle with a text and numeric data will copy the text and increment the numeric data by one.

  • Using the handle with numeric data will increment it by one while pressing down the Ctrl key.

Click To expand
Whiz Words
Start example
Auto fill | Values |
Fill Handle |  | Labels
End example

Lesson Summary

There are two basic types of information you can enter in a cell. These are the labels and values. Labels are dates, numbers and characters not used in calculations. Values are numbers, dates and other numerical information that are usually used in calculations.

Date and time can be entered in different ways by using the menu bar and by using the shortcut key. You can also format on how the date and time will appear on your worksheet by going to the Format menu, Cells and choosing the Number tab and Date or Time category.

Another feature of Excel is Auto Fill. It copies the content of the cell to other adjacent cells either vertically or horizontally.

Study Help
Start example

Directions: Answer the following correctly. Place your answer on a sheet of paper.

  1. What are the two basic types of data in MS Excel?

  2. Define the two basic types and give examples for each.

  3. What is Auto fill? Fill handle?

  4. How do you format the date and time in your worksheet?

  5. What would you do to increment a number by one using the auto fill?

End example
Start Sidebar
Activity 1

  1. Create a worksheet that will display the list of Top 20 songs of the month.

  2. The title should be "Top 20 Songs of the Month". The label of each column should be as follows:

    Column A

    -

    Rank

    Column B

    -

    Title

    Column C

    -

    Singer

    Column D

    -

    Type (song)

  3. Complete the worksheet by entering the necessary data for each column.

  4. Save your work with a filename Top 20 Songs.

End Sidebar


Lesson 3: Editing Data

Editing Cell Content

To edit the content of a cell, press Delete to remove the entire entry. Use any of the following steps to change the content of a cell.

  • Double-click the cell that contains the data you want to edit.

  • Press F2 to activate the cursor inside the cell.

  • Click on the Formula bar where you want to make changes on the cell content.

    Click To expand

If you want to replace the whole content of the cell, just select it and start typing. The text that you will enter into the cell will overwrite the previous one.

Using Undo and Redo

If you have made a mistake while doing something, the Undo feature of Excel will enable you to recover from the mistake. It also has a Redo feature by which you can reverse the action of the Undo command.

You can find the Undo and Redo button on the Standard toolbar and it has two parts: the button itself and the arrow next to it.

There are some actions you cannot Undo and Redo such as saving, closing, and opening files; as well as maximizing, minimizing, and arranging.

You can do any of the following actions to undo the last action you have made.

  • Click [image] Undo on the Standard toolbar.

  • Press Ctrl+Z

  • Click Edit, choose Undo

  • Click down arrow beside the Undo button and choose the actions you want to Undo

Note

You can undo up to the last 16 actions.

To cancel Undo immediately, you perform any of the following actions

  • Click Redo [image] on the Standard toolbar.

  • Press Ctrl+Y.

  • Click Edit and choose Redo.

  • Click down the arrow beside the Redo button and choose the actions you want to redo from the list.

Cut or Move, Copy, and Paste

When you cut or copy an object, graphics or text, it goes to the Windows Clipboard. You can paste any data from the clipboard to any programs you want. There are twenty-four (24) items that you can collect from any programs and store it at the New Office Clipboard wherein you can paste them one at a time or paste them all whenever you need them.

There are many ways to copy, cut, or move and paste cell/s in your worksheet. You can do any of the following:

  1. Select the cell or range of cells, then do either of the following:

    To Copy:

    To Cut:

    • Click Edit, choose Copy.

    • Press Ctrl+C.

    • Click the Copy [image] button in the Standard toolbar.

    • Right-click, choose Copy.

    • Click Edit, choose Cut.

    • Press Ctrl+X

    • Click the Cut [image] button in the Standard toolbar.

    • Right-click, choose Cut.

  2. Select the cell or range of cells where you want the cell/s or object/s to be copied.

  3. To Paste, do any of the following:

    • Click Edit, choose Paste.

    • Press Ctrl+V.

    • Click the Paste [image] button in the Standard toolbar.

    • Right-click, choose Paste.

After pasting the content, the Paste option button will appear.

Click To expand

The Paste Options drop-down list displays choices on how your copied text will be formatted. You can turn the Paste Option on or off by doing these steps:

  1. Click Options on the Tools menu, and then click the Edit tab.

  2. Click on the Shadow Paste Options buttons check box.

When you use the Paste command and the desired object or text does not appear, you may do this:

  1. On the Edit menu, click Office Clipboard. The Task pane will display the Office Clipboard.

The Office Clipboard allows you to store up to 24 items of objects. You can paste the objects on the clipboard all at the same time by clicking the Paste All button. You can also delete the content of the clipboard all at once by clicking the Clear All button.

To paste or delete a particular object on the clipboard, follow these steps.

  1. Point to the object that you want to delete or paste. Notice that a drop-down arrow appears.

  2. Click the drop-down arrow and from the available options, click the action you want to perform.

In Excel, the range of cells should be of the same size as the cell range to be copied.

Click To expand

Another way to cut, copy, and paste is to use the drag and drop method. To do this, follow the steps below:

  1. Select the cell or range of cells.

  2. Place the mouse pointer to the border of the selected cell/s. The pointer becomes a left-facing white arrow.

  3. Do any of the following:

    To Move or Cut:

    1. Drag the border to the new location to move the selection.

    2. The new location is indicated by a screen tip showing the cell reference before you release the left mouse button.

      Click To expand

To Copy:

  1. Hold down the Ctrl key and drag the border to the new location. The white arrow mouse pointer displays a plus sign to indicate that the item is to be copied.

The cell or range of cells you drag can be of different location or even at the Windows desktop.

Paste Special

Instead of copying entire cells, you can copy specified contents from the cells, for example, you can copy the format of the cell without copying its content. You can do this by using Paste Special. To do this, follow the steps below:

  1. Select the cells you want to copy.

  2. Click Copy.

  3. Select the upper-left cell of the paste area. Paste Area is the target destination of the selected cells that have been copied or cut.

  4. On the Edit menu, click Paste Special or right-click then choose Paste Special. The Paste Special dialog box appears.

  5. Click OK.

    Click To expand

Clear Cell Contents and Formats

While editing, you may erase the content of your cell. You may use the following steps to clear the contents of a cell.

  1. Select the cells, rows or columns you want to clear.

  2. On the Edit menu, point to Clear and choose Contents or press Delete.

The Clear Command has other options that will enable you to clear the formats and comments of a cell. When you choose All it will erase the content, format, and comment of a cell. Formats will only erase the format of the cell and will not erase its content. Choosing Comments will only remove the comments of a cell.

Click To expand

Selecting a Range of Cells

A range is a rectangular block of cells referred to by its upper-left and lower-right diagonal corner cells. For example, the range whose upper-left corner is cell C2 and whose lower-right corner is cell D5 would be referred to as cell range C2:D5. A range can be as small as a single cell or as large as the entire worksheet.

To select a range:

To select a range, click the cell where the selection will start and using the mouse, extend the selection to cover the desired range. Suppose you want to select the range C2:D5.

  1. Click the cell C2.

  2. Hold down the mouse button and drag down to row 5 and across column D to select the range, and then release the mouse button.

    Click To expand
Note

You can also use the SHIFT+ARROW keys to select a range of cells.

Selecting an Entire Column or Row

Selecting an entire column or row is easy; just click the column or row heading:

Example: To select the entire column G and entire row 7:

  1. Click the column heading (letter) G. This selects the entire column G from G1:G65536.

  2. Click the row heading (number) 7. This selects the entire row 7 from A7:IV7.

    Click To expand

Selecting Non-adjacent Cells

To select cells that are apart from each other, follow the steps below.

  1. Select the first cell in the cell range.

  2. Press down the Ctrl key while selecting non-adjacent cells.

  3. Release your hold on the Ctrl key after you are through with your selections.

You can also select non-adjacent rows and columns by selecting the column or row heading then hold down Ctrl key while selecting other headings not adjacent to the first selection.

Inserting and Deleting Selected Cells

If you want to add cell/s to your worksheet, you can select a range of existing cells where you want to insert the new blank cells. In the same way, you can also delete cell/s by selecting the range to be deleted and moving the cells up or to the left.

Selecting non-adjacent cells

Click To expand

To insert cell/s, follow these steps:

  1. Move to the cell or range of cells where you want to add cells.

  2. Click Insert and choose Cells. The Insert dialog box will appear.

  3. You can pick any of the following options:

    • Shift cells right - moves all the cells that follow the selected cells to the right.

    • Shift cells down - moves all the cells that follow the selected cells down.

    • Entire row - adds the same numbers of rows. as there are selected rows in your range.

    • Entire column - adds the same number of columns, as there are columns in your selected range.

  4. Click OK or press Enter.

    Click To expand
Note

You can also use the right-click method to display the Insert dialog box.

When you delete cells, Microsoft Excel removes them from the worksheet and shifts the surrounding cells to fill the space.

To delete cell/s and to move the contents of the other cell/s, follow these steps:

  1. Select the cell or range of cells you want to delete.

  2. From the Edit menu, choose Delete. The Delete dialog box appears.

  3. Choose any of the following:

    Shift cells left, Shift cells up, Entire row, and Entire column

  4. Click OK or press Enter.

Note

You can also use the right-click method to display the Delete dialog box.

Using Find and Replace

If you do not know the exact location of a text in a worksheet, you can make use of the Find and Replace feature of MS Excel. In the same way, it helps you edit text that you entered incorrectly by using the Replace command.

To find a text, follow these steps:

  1. Activate the Find dialog box:

    • Press Ctrl+F.

    • From the Edit menu, choose Find. The Find dialog box opens.

  2. Type the text you want to look for in the Find What text box.

  3. Click Find. Notice that the active cell indicator goes to the cell that contains the text you are looking for.

  4. When you have finished, click Close.

    Click To expand

To replace a text, follow these steps:

  1. Do any of the following:

    • Press Ctrl+H

    • From the Edit menu, choose Replace. From the Find and Replace Dialog box, click the Replace tab.

  2. Type the text you want to be replaced in the Find What text box.

  3. In the Replace with box, enter the replacement text with specific formats if necessary. To format it, just click on the Option button then format the text.

  4. Click Replace or Replace All if you want to change that same text in the entire worksheet.

  5. Click Close.

Find and Replace Formats

The Find and Replace feature of Excel not only allows you to find and replace data. It also finds and replaces one type of formatting with another throughout the worksheet. To do this, do the following:

  1. Select a range of cells or press CTRL+HOME to move to the beginning of the worksheet.

  2. Click Edit, then click Replace.

  3. Click the Options button and set options as desired.

  4. Click the arrow on the Find what Format button and select Choose Format From Cell.

  5. Click a cell in the worksheet that has the format you want to change.

  6. Click the Replace with Format button. Select the format you want to use as replacements.

  7. Select Replace or Replace All.

  8. Click Close.

Going to a Specific Cell

The column letter and row number also known as cell reference or cell address identifies a cell. You can move to any cell you want by using the scroll bars and the arrow keys on the keyboard.

If you want to move to a cell that is not currently shown on the screen, you can use any of the following methods:

Click To expand

  1. Click the Name box beside the Formula bar.

  2. Type the cell address that you want and press Enter.

Note

You can also move to a cell of another worksheet by typing the sheet reference followed by an exclamation point and cell address (for example: Sheet2!H31).

Another method is by using the Go To command.

  1. From the Edit menu, click Go To or press Ctrl+G. The Go To dialog box appears.

  2. Type the cell address in the Reference text box. The Go To list box shows you the previous location of the active cell indicator.

  3. Press OK.

    Click To expand
Note

You can also press F5 to open the Go To dialog box.
Whiz Words
Start example
Find and Replace | F2
Undo and Redo | Paste Special
End example

Lesson Summary

There are different ways of editing the content of a cell. Before editing the content, you must first select the text you want to edit. You can use the Delete key to overwrite the content of a cell. You can also use the Formula bar, F2, or the double-click method to edit partial content of the cell. And if you want to repeat the last action you take, you can use the Undo and Redo features. You can also remove the format of a cell and its comments. From the Edit menu, click Delete options.

Every time you use Cut and Copy, the Paste command is always needed to complete the task of cutting and copying. There are different ways of performing these tasks. You can use the Menu bar, Shortcut keys, Auto fill, and the click and drag method. Aside from copying entire cells, by using Paste Special you can copy specified contents from the cells, e.g., formats, values, operations, formulas and so on.

You can select adjacent and non-adjacent cells using the mouse or the keyboard. To add cells in a worksheet, you have to select a range of existing cells where you want to insert the new blank cells.

To look for a specific content in a cell, you can use the Find and Replace command. To go to a specific cell, use the Go To command or F5.

Study Help
Start example

Directions: Identify the following. Write your answer on the space provided below.

  • _______________1. The key to be pressed if you want to edit the partial content of a cell.

  • _______________2. The shortcut for the Undo command.

  • _______________3. The shortcut for the Redo command.

  • _______________4. It is identified by its column heading and row number.

  • _______________5. This feature of MS Excel allows you to locate and replace text.

  • _______________6. It is a rectangular block of cells referred to by its upper-left and lower-right diagonal corner cells.

  • _______________7. You store this number of items in the Clipboard.

  • _______________8. The key to press if you want to go to a specific cell.

  • _______________9. To select non-adjacent cells, you have to press this while selecting cells.

  • _______________10. The key to press to activate the Find dialog box.

End example
Start Sidebar
Activity 1

  1. Open the file Information.xls.

  2. Insert a row before row1

  3. On cell A1, type "Date".

  4. On cell B1, input the current date with this format "March 14, 1998".

  5. Insert a column before A1.

  6. On cell A3, type "1".

  7. Using the auto fill, increment the number by 1.

  8. Edit cell D2 so that the text will appear as Tel. Number.

  9. Undo the last action you made.

  10. Select A1 and B1.

  11. Move the selected cells to cell D1:E1.

  12. Type "My Friends List" cell A1.

  13. Delete the cells that contain the eleventh record.

  13. Delete the last four records.

  14. Save your work.

End Sidebar


Lesson 4: The Language Bar

The Language bar appears on your desktop automatically when you add handwriting recognition, speech recognition, or an Input Method Editor (IME) as a method of entering text. You can use the buttons on the Language bar to perform tasks related to these text services.

Click To expand

You can move the Language bar out of the way by temporarily hiding it. You can also close the Language bar so that it no longer appears. If you close the Language bar, you cannot use the features on the Language bar.

Voice Command and Dictations

Voice Command and Dictations are the new features of Microsoft Officexp, which are also available in MS Excel 2002.

These features are available in the Simplified Chinese, English (U.S.), and Japanese language versions of Microsoft Office.

Switching from Dictation to Voice Command modes:

If the Voice Command and Dictation button is not visible in the Language bar, click Microphone.

Using Dictation Mode

  • Click on Dictation in the Language bar to convert the words you speak into text.

Using Voice Command Mode

  • Click on the Voice Command in the Language bar to select menu, toolbar, dialog box, and task pane items.

To configure your microphone follow these steps:

Click To expand

  1. Click Microphone on the Language bar. A dialog box will appear prompting you to adjust your microphone and to train Office for speech recognition.

    Click To expand

  2. Click Next. The Microphone Wizard will help you to test your microphone for use with Speech Recognition.

  3. Click Next.

  4. Read the following statement in your everyday tone of voice.

    "I am using the Microphone Wizard. It is adjusting the volume of my microphone".

    Click To expand

  5. Click Next.

  6. Read the following:

    "This papaya tastes perfect".

  7. When you hear your voice and it sounds OK, click Finish.

    Another dialog box will appear, prompting you to train your voice.

    1. Click Next to start the training. Another dialog box will appear.

    2. Fill in the following instruction and then click Next.

    3. Click Next again.

    4. Click Next to begin training.

      Click To expand

    5. Read the following phrases:

      Click To expand

  8. When done reading, click Finish.

    Click To expand

Now, your microphone is already configured. You can now practice the Voice Command and Dictation.

Click To expand

Using Handwriting

Instead of using the keyboard, you can now write the text you want using the mouse to enter in any MS Office program by using the Handwriting Recognition in the Language bar.

Anything you write will be converted to typed characters and inserted inline with any existing text, at the same size as the existing text.

To use handwriting, follow these steps:

  1. Place the insertion point where you want to insert handwriting.

  2. Select a writing area.

  3. On the Language bar, click Handwriting and choose any of the following:

Click To expand

The Writing Pad

  • On the Handwriting options, click Writing Pad.

    Click To expand

  1. To increase or decrease the size of the Writing Pad box, move the pointer to an outside edge of the box until it changes into a double-headed arrow, and then drag to the desired size.

  2. Move the insertion point into the Writing Pad box. When it changes to a pen, you are in an area that you can write in.

Note

You can move the Writing Pad box to different areas on the screen by dragging its title bar.

Write anywhere on the screen

You can also write anywhere in your worksheet using the Write Anywhere option.

Click To expand

  1. On the Language bar, click Write Anywhere.

    Note

    You can move the Write Anywhere toolbar to different areas on the screen by dragging its title bar.

  2. To write, use a Handwriting input device, or move your mouse on your mouse pad to form words.

On-screen Keyboard

The Handwriting has another option which is the on-screen keyboard.

Click To expand

The on-screen keyboard works like the actual keyboard. To enter text, click the buttons on the on-screen keyboard. If you want to type in capital letters, click on the Caps button.

Whiz Words
Start example

Language bar

Handwriting

On-screen Keyboard
End example

Lesson Summary

The Language bar is a new feature of Microsoft Office$xp$ (2002 version). It has a Voice Command feature allowing you to give oral commands to your computer when using MS Office applications and to input anything you say into the computer using the Dictation feature.

It also has a Handwriting recognition allowing you to enter text in the computer without using the actual keyboard.

Study Help
Start example

  1. In what applications does the Language bar take effect?

  2. What is the first thing we should do before we can use the Voice Command and Dictation features?

  3. What are the options available in the Handwriting recognition?

End example
Start Sidebar
Activity 1

  1. Open a new workbook.

  2. On the Language bar, click Microphone. Make sure you have a microphone attached and configured to your computer.

  3. Activate Voice Command.

  4. Practice the Voice Command by giving verbal commands to the computer.

    Example: File. Close.

  5. After practicing the Voice Command, practice Dictation.

  6. When done, try to use the Handwriting tools.

  7. When finished using the Language bar, exit MS Excel and do not save.

End Sidebar


Chapter Review

I. Multiple Choice: Encircle the letter with the correct answer.

  1. This form of a mouse pointer is used to copy cell content.

    1. I-Beam

    2. Fill Handle

    3. Cursor

    4. Thick Cross

  2. The command that enables you to recover from the mistake you have made.

    1. Undo

    2. Ctrl+Y

    3. Redo

    4. Edit

  3. A keyboard shortcut that allows you to go back to the beginning of the spreadsheet.

    1. Ctrl+End

    2. Home

    3. Ctrl+Home

    4. Ctrl+Arrow Up

  4. A rectangular block of cells referred to by its upper-left and lower-right diagonal corner cells.

    1. Column

    2. Range

    3. Row

    4. Cell

  5. A keyboard shortcut that allows you to go back to the beginning of a row.

    1. F5

    2. Arrow Up

    3. Home

    4. Ctrl+Home

  6. The Office Clipboard allows you to store up to this number of items.

    1. 24

    2. 12

    3. 16

    4. 20

  7. It is the target destination of the selected cells that have been copied or cut.

    1. Destination area

    2. Target area

    3. Paste area

    4. None

  8. These are dates, numbers, and characters not used in calculations.

    1. Values

    2. Characters

    3. Labels

    4. Numbers

  9. The mouse pointer that allows you to edit text within a formula bar.

    1. I-Beam

    2. Double Arrow

    3. Fill Handle

    4. Thick Cross

  10. The default active cell upon opening MS Excel.

    1. B1

    2. A

    3. A1

    4. B

II. Write down the shortcut key/s of the following commands.

  1. Copy ----------------------

  2. Cut ----------------------

  3. Paste ----------------------

  4. Find ----------------------

  5. Go To ----------------------

  6. Undo ----------------------

  7. Redo ----------------------

  8. Date ----------------------

  9. Time ----------------------

  10. Replace ----------------------

III. Draw at least five (5) forms of Mouse Pointer and identify its name. (2 pts. Each)

Click To expand


Chapter Three: Formatting Worksheets

Lesson 1: Enhancing Worksheet Appearance

Changing Character Fonts, Sizes, Styles, and Colors

Excel can use different styles on a worksheet. It can be bold, italic, or underlined.

Use any of these methods to change the appearance of your data:

Using the Toolbar:

The toolbar gives you quick access to frequently used commands. To use a Formatting tool, do the following:

  1. Select the cell or the cell range you want to format.

  2. Click the tool in the formatting toolbar you want to use.

    Click To expand

You can use some of the additional formatting tools that you can add to any toolbar. To see these tools, do the following:

  1. Click the Toolbar options button.

  2. Point to Add or Remove buttons, then Formatting.

  3. Click the button you want to add in the toolbar.

    Click To expand

Formatting Characters with the Menu

To change the font size or font style of characters, follow these steps:

  1. Select the cell, range, or multiple ranges.

  2. Click Cells in the Format menu or press Ctrl+1. The Format Cells dialog box appears. Click the Font tab to display the dialog box.

    • Font List - allows you to select the font type you want to apply. Look at the preview box to see how the font will appear in the document.

    • Font Style List - allows you to select the font styles.

    • Size List - allows you to select the font size. Remember that approximately 72 points is equal to 1 inch of height.

      Click To expand

      You can also apply effects such as strikethrough, superscript, and subscript. Click the check box beside the effect you want to apply.

    • Color List - allows you to select colors.

      Click To expand

  3. Click OK or press Enter.

Using Shortcut Keys

To format cells quickly, select the cell, range or text then, press the correct shortcut key. The table below shows which shortcut key combination applies to which format.

Format | Shortcut Keys
Bold (Toggle on/off) | Ctrl+2 or Ctrl+B
Italic (Toggle on/off) | Ctrl+3 or Ctrl+I
Underline (Toggle on/off) | Ctrl+4 or Ctrl+U
Strikeout (Toggle on/off) | Ctrl+5

Formatting Cell Content

Applying Number Formats

When the number is formatted, its value does not change but its appearance changes. A formatted number is often easier to read than an unformatted number.

You can format numbers through the toolbar, but you have more options when you use the Number tab in the Format Cells dialog box.

Using the Toolbar

Currency Style - adds a dollar sign, commas, and two decimal places.

Percent Style - multiplies the number by 100 and adds a percent sign.

Comma Style - adds a comma for every three digits to the left of the decimal place and two decimal places.

Number Formats | Shortcut Keys
Currency | Ctrl+Shift+$
Percent | Ctrl+Shift+%
Comma | Ctrl+Shift+!

The Format Cells dialog box gives you more options than the options in the toolbar.

  1. Select the cell or range of cells.

  2. Press Ctrl+1 or click the Format menu and choose Cells. The Format Cells dialog box appears.

  3. Click the Number tab.

  4. Choose a Category. The right side of the dialog box changes depending on the choice.

  5. Click OK or press Enter.

Designing Custom Number Formats:

To create custom formats on the worksheet, do the following:

  1. Select the cells to which you want to apply the custom format.

  2. Choose Format from the menu bar. Select Cells and click the Number tab.

  3. If the existing format is closed to the custom format you want to create, select that format by choosing the appropriate category from the list.

  4. In the Type box, edit the number format codes to create the format you want.

  5. Click OK or press Enter.

    Click To expand

Formatting Date and Time

Type dates and times in the cell the way you are accustomed to in writing. Excel recognizes dates and times entered in any format.

Predefined Excel Date and Time Formats:

Date Format | Example
m/d/yy | 9/3/01
d-mmm-yy | 3-Sep-01
d-mmm | 3-Sep
mmm-yy | Sep-01
m/d/yy h:mm | 9/3/01 0:00
Time Format | Example
h:mm AM/PM | 1:54 PM
h:mm:ss AM/PM | 1:54:00 PM
h:mm | 13:54
h:mm:ss | 13:54:00
mm:ss | 54:00
mm:ss:0 | 54:00:0
[h]:mm:ss | 13:54:00

  1. Select the cell containing dates or times you want to format.

  2. From the Format menu click Cells. Click the Number format.

    • Click Date in the category list to format the date.

    • Click Time in the category list to format time.

  3. Click OK.

You can also choose any format you like from the Custom category.

Click To expand

Adjusting Decimal Place

On a worksheet:

Select the cells you want to format. To display more or fewer digits after the decimal point, click Increase Decimal [image] or Decrease Decimal [image] on the Formatting toolbar.

In a built-in number format:

On the Format menu, click Cells, and then click the Number tab. In the Category list, click Currency, Accounting, Percentage, or Scientific. In the Decimal places box, enter the number of decimal places you want to display. If you want to change the currency symbol, click the symbol you want to use in the Symbol list.

Applying Cell Borders and Shading

Adding Borders

To make your spreadsheet look more presentable you may add visual enhancement to it. Excel allows adding borders which enable the readers to focus on certain areas.

To add borders, follow the steps below:

  1. Select the cell or range of cells where you want to add borders.

  2. Do any of the following:

    • To apply the most recently selected border style, click Borders [image] on the Formatting toolbar.

    • To apply a different border style, click the arrow next to Borders [image] , then click a border on the palette.

To apply more border settings, click Cells on the Format menu, and then click the Border tab.

  1. Click the Line Style you want.

  2. Click a button to indicate the border placements.

    Click To expand

Removing Borders

  • On the Formatting toolbar, click the arrow next to Borders, [image] then click No Border [image] on the palette.

Adding Shadings or Patterns

Shading a cell means adding patterns to cell to mark important information.

Click To expand

The following steps will help you to add shading:

  1. Select the cell or range of cells.

  2. From the Format menu, choose Cells.

  3. Choose the Pattern tab from the dialog box.

  4. Choose a color from the color list.

  5. Click OK or press Enter.

You can also use the Fill Color [image] button on the Formatting or Drawing toolbar.

Using Format Painter

The Format Painter [image] is located in the Standard toolbar. It copies the format from a selected object or text and applies it to the object or text you click. To copy the formatting to more than one item, double-click the Format Painter button, and then click each item you want to format. When you are finished, press ESC or the Format Painter button to turn it off.

Apply or Remove AutoFormats

  1. Select the range you want to format or remove the auto format.

  2. On the Format menu, click AutoFormat.

Do one of the following:

  • Apply an Autoformat - Click the format you want. If you want to use only selected parts of the autoformat, click Options, and then clear the check boxes for the formats you don't want to apply.

  • Remove an Autoformat - At the bottom of the list, click the format above None.

    Click To expand
Whiz Words
Start example
Format Painter | AutoFormat
Shading | Fill Color
End example

Lesson Summary

Enhancing worksheet appearance has a great part to make our worksheet more presentable. The Format Cells dialog box allows you to format font type, size, color, underline, and add effects. The Number tab allows you to format Date and Time. It also allows you to add borders and shading to your worksheet. You can also access these formatting tools in the Formatting toolbar.

Study Help
Start example

Directions: Answer the following questions briefly.

  1. What tab in the Format Cell dialog box allows you to change the font type?

  2. What is the importance of adding patterns to our worksheet?

  3. Enumerate the steps to:

    1. Change the appearance of the border.

    2. Add patterns to your worksheet.

    3. Format date and time.

End example
Start Sidebar
Activity 1

Directions: Perform the following instructions carefully.

  1. Open the file Information.xls.

  2. Add borders to your table.

  3. Format the following:

    • Title - Algerian, 15, Bold

    • Column Headings - Arial 12, Bold, Italic

    • Records - Times New Roman, 12, Normal

  4. Add shading to your worksheet.

  5. Insert the Date and Time at the end of your table using this format (m/d/yy h:mm).

  6. Save your work as Formatted Information.xls in Excel-Activities folder.

End Sidebar
Start Sidebar
Activity 2

Directions: Perform the listed tasks below.

  1. Copy the Calendar of Activities as shown below.

Click To expand
AUGUST CALENDAR OF Activities 2002 |  | Days (Sun-Sat)
 | Font - Arial or Tahoma Size - 26 Font Color - Yellow Fill Color - Black Row Height - 33.75 |  | Font - Arial or Tahoma Size - 10 Fill Color - White Font Color - Sea Green
18 |  | 21
 | Font - Arial Style - Bold, Underlined, Italic Fill Color - Pale Blue Text Color - Green |  | Font - Arial Size - 10 Style - Bold Fill Color - Plum Text Color - Yellow
Dates (1-31) |  | 25
 | Font - Arial or Tahoma Size - 10 Font Color - Black |  | Font - Arial Size - 10 Style - Bold Fill Color - Light Blue Text Color - White
12, 13, 14 |  | Legend
 | Font - Arial Style - Bold, Italic, Underlined Fill Color - Light Green |  | Font - Arial Size - 10 Style - Bold, Italic Font Color - Dark Red
15, 16, 17 |  |  |
 | Font - Arial Style - Bold and Underlined Fill Color - Lime Text Color - Blue |  |
 |  |  | Font - Arial Size - 12 Style - Bold, Underlined Font Color - Red

  2. Use Find and Replace to change the format of days (Sun, Mon …) to Times New Roman, Italic.

  3. Use Find and Replace to change the word "Achievement Holiday" to "Rest Day".

  4. Save your work as Calendar.xls in Excel-Activities folder.

End Sidebar


Lesson 2: Adjusting Rows and Columns

You can adjust one or more column widths to best fit your need. If a column is not wide enough to display data, especially number, date, and time, it displays the data you typed as ##### characters rather than the one you entered. The standard width of a column is 8.43 characters, based on font defined for normal style.

Adjusting Column Width

  1. Select multiple columns you want to adjust. If you want to select non-adjacent columns, press Ctrl key and then click the left mouse button

  2. Move the pointer to the vertical line to the right of the column heading you want to adjust until its shape changes.

  3. Click and hold down the left mouse button then drag the column to the left or right until the desired width is met.

You can also do the following to adjust the column width:

Click To expand

  • Double-click the column heading separator on the right side to fit its widest content.

  • Select the column you want to adjust. Click the Format menu, choose Column and then click Width. The Column Width dialog box appears. Input the width you want and then click OK or press Enter.

  • Right–click on the column you want to adjust and then click Column Width. The Column Width dialog box appears. Input the width you want and click OK or press Enter.

    Click To expand

Adjusting Row Height

To adjust row height, follow these steps:

To change the height of a single row:

  • Drag the boundary below the row heading until the desired row height is achieved.

To change the height of multiple rows:

Click To expand

  1. Select the rows you want to change.

  2. Drag a boundary between the selected row headings.

    Click To expand

To change the height of all the rows in the worksheet, do the following:

  1. Click the Select All button.

  2. Drag the boundary below any row heading.

    Click To expand

To fit the contents to a specified height:

  • Click the boundary below the row heading and specify the height you want.

  • Select the column, point to Row on the Format menu, click Height, then enter a number.

Options Button

When you copy and paste the data to another cell, the Paste Options button allows you to keep the Source Column widths. To do this, follow the steps below:

  1. Select the cell that contains the data you want to copy.

  2. Click Copy.

  3. Select the cell where you want the data to be pasted.

  4. Click Paste. The Paste Options button appears.

  5. Click the Paste Options button, and then click Keep Source Column Widths if you want to copy the column width.

    Click To expand
Whiz Words
Start example
Paste Options button | Rows
Columns | Select All button
End example

Lesson Summary

Formatting Rows and Columns can help you make your worksheet look more presentable. These includes adjusting column width and row height. To adjust the column width, place the mouse pointer to the right side of the column heading to which you want to adjust the column. Drag the handle until you reach the desired width. To adjust the row height, place the mouse pointer below the row heading to which you want to adjust the height. Drag the handle until you reach the desired height.

Study Help
Start example

Directions: Answer the following:

  1. Why do we have to change the column width?

  2. How do we adjust a row height?

  3. What is the use of the Options buttons?

  4. What happens if a set of data does not fit in the column width?

  5. How do you change the height of the entire rows?

End example


Lesson 3: Aligning Cell Content

In an unformatted cell, text aligns at the left of the column, and numbers align at the right edge. To enhance your worksheet, you can align values or formula results so that they are aligned to the left, right, or center of the cell.

Click To expand

Aligning Cell Content

  1. Select the cell/s containing the data you want to align.

  2. Click the appropriate alignment button from the formatting toolbar.

Alignment | Shortcut Keys
Align-Left | Ctrl+L
Align-Right | Ctrl+R

To remove the alignment click again the alignment button you want. These buttons can be toggled on and off.

Click To expand

Aligning Data at the Top, Center, or Bottom of a Cell

  1. On the Format menu, click Cells, and then click the Alignment tab.

  2. In the Vertical box, click the option you want.

Merging and Splitting Cells

Cells can be merged to align a title across a selection of columns. This enables you to center a heading over a table or report.

To merge cells, follow the steps below:

  1. Select the cells you want to merge.

  2. Click the Merge and Center [image] button.

To split merged cells, follow these steps:

  1. Select the merged cells that you want to split.

  2. Click the Merge and Center [image] button.

Rotating Text and Changing Indents

Click To expand

To rotate text in a cell:

  • On the Format menu, click Cells, and then click the Alignment tab.

  • In the Orientation box, click a degree point, or drag the indicator to the angle you want.

  • To display text vertically from top to bottom, click the Vertical Text box under Orientation.

To increase or decrease indented text from the left edge of a cell:

  • On the Formatting toolbar, click Increase Indent or Decrease Indent.

Click To expand

You can also use the Format Cells dialog box to change the indentation of text.

  1. Select the cells that contain the text you want to indent.

  2. Press Ctrl+1 or click Format and choose Cells. The Format Cells dialog box appears.

  3. Click on the Alignment tab.

  4. Change the Horizontal to Left (Indent) or Right (Indent).

  5. Type the number of characters to indent or use the up and down arrows in the Indent Drop-down list box.

  6. Click OK.

Whiz Words
Start example
Merge and Center | Alignment
Increase Indent | Decrease Indent
End example

Lesson Summary

You can align cell content in three ways: Align Left, Center, and Align Right. To combine series of adjacent cells together, use the Merge and Center button. Remember that all these buttons can be toggled on or off.

You can also change the direction of your text in a cell. On the Format menu, click Cells and choose the Alignment tab.

Study Help
Start example

  1. How do you align the content of a cell?

  2. How do you center text across columns?

  3. How do you rotate text in a cell?

  4. What are the buttons to be clicked to change indentations?

  5. What are the different alignments available in MS Excel?

End example
Start Sidebar
Activity 1

  1. Open a new workbook.

  2. Copy the data shown below and do the following: Check the box if task is taken.

    • Format cell A1 with 18-point Arial MT, the rest should be Arial, 10.

    • Bold cells A3, A5, A9, and A13.

    • Italicize A6:A8, A10:A12, and A14:A16

      Click To expand

  3. Format the worksheet like the one shown below.

    Click To expand

    • Rotate B4:N4 to 75 degrees.

    • Change the column widths of B:N to five (5) characters.

    • Center the title in A1 in the range A1:N1.

    • Change the column width of column A to automatically fit the widest entry.

    • Center the range B6:N16.

    • Set the row height or rows 5, 9 and 13 to 24 points.

    • Set the row height of row 2 to 6 points.

    • Set the row height of row 1 to 40 points and center the title vertically.

    • Center ranges A6:A8, A10:A12, and A14:A16.

    • Apply a thick box border around the ranges A5:N8, A9:N12, and A13:N16.

    • Apply blue font color to cells A1 and B4:N4.

    • Change the size of A1 to 12 points.

    • Add yellow fill color to cells A1:N1, A5:N5, A9:N9, and A13:N13.

    • Apply a pattern that is 50% gray and yellow to the ranges N4, N6:N8, N10:N12, and N14:N16.

    • Save your work as Formats.xls on the folder Excel-Activities.

End Sidebar


Lesson 4: Applying Styles

A style is a combination of formatting characters, such as font, font size, and indentation, that you name and store as a set. When you apply a style, all of the formatting instructions in that style are applied at one time.

Applying a Style

  1. Select the cells you want to format.

  2. On the Format menu, click Style. The Style dialog box appears.

    Click To expand

  3. In the Style name list box, click the style you want.

  4. Click OK.

To define the style without applying it, click Add, and then click Close.

Note

To apply common number styles to selected cells, click Comma, Currency or Percent style on the Formatting toolbar.

Creating a New Style

  1. Select a cell that has the combination of formats you want to include in the new style.

  2. On the Format menu, click Style.

  3. In the Style name box, type a name for the new style. To change formats for an existing style, click the style you want to change.

  4. Click Modify.

  5. On any of the tabs in the dialog box, select the formats you want, then click OK.

  6. Clear the check box for any type of formats that you don't want the style to include.

  7. To define and apply the style to the selected cells, click OK. To define the style without applying it, click Add, and then click Close.

Removing a Style from Selected Cells

  1. Select the cells that are formatted with the style you want to remove.

  2. On the Format menu, click Style.

  3. In the Style name box, click Normal.

Deleting a Style from the Style List

  1. On the Format menu, click Style.

  2. In the Style name box, click the style you want to delete.

  3. Click Delete.

Note

You cannot delete the Normal style. If you delete the Currency, Comma, or Percent style, you will not be able to use the Comma Style [image] , Currency Style [image] , or Percent Style [image] button on the Formatting toolbar.
Whiz Words
Start example
Style | Currency
Percent | Comma
End example

Lesson Summary

Styles contains a mixture of font, alignment, border, and pattern formatting. It also adds one or more formats to a range. You can define and modify the formats associated with a style.

Study Help
Start example

  1. What is a style?

  2. How do we remove a style from selected cells?

  3. How do we delete a style form the style list?

End example
Start Sidebar
Activity 1

  1. Open a new workbook.

  2. Create a new style named My Style.

  3. The Style should include the following:

    • Number - Date

    • Alignment Center, Bottom

    • Font Times New Roman, 12

    • Borders No Border

    • Shading Lime Green

    • Protection Locked

  4. Click Add, and then Close.

  5. Display the Style dialog box again.

  6. Look for the style you have created in the Style name list box.

  7. Click Close.

  8. Exit MS Excel without saving it.

End Sidebar


Chapter Review

I. Identification: Identify the following. Write your answer on the space provided before each number.

  • __________________1. It is a combination of formatting characters, such as font, font size, and indentation, that you name and store as a set.

  • __________________2. This means adding patterns to cell to mark important information.

  • __________________3. It copies the format from a selected object or text and applies it to the object or text you click.

  • __________________4. It gives you quick access to frequently used commands.

  • __________________5. This option adds a dollar sign, commas, and two decimal places.

  • __________________6. This button allows you to keep the source Column widths.

  • __________________7. This option multiplies the number by 100 and adds a percent sign.

  • __________________8. The action to be made if you want to copy the formatting to more than one item using the format painter.

  • __________________9. This option adds a comma for every three digits to the left of the decimal place and two decimal places.

  • __________________10. The standard width of a column.

II. Write down the shortcut keys of the following commands/options.

1. Format dialog box | __________________
2. Bold | __________________
3. Italic | __________________
4. Underline | __________________
5. Strikeout | __________________
6. Currency | __________________
7. Percent | __________________
8. Comma | __________________
9. Left Align | __________________
10. Right Align | __________________

III. Fill in the Blanks. Fill in the missing word/s to complete the following steps:

  1. Changing Indentations using Format dialog box.

    1. Select the ____________ that contain the text you want to indent.

    2. Press _________ or click Format and choose cells. The Format dialog box appears.

    3. Click on the __________________ tab.

    4. Change the _____________________ to Left (indent) or Right (indent)

    5. Type the number of characters to indent or use the up and down arrows in the Indent Drop-down list box.

    6. Click ______.

  2. Adding Shading.

    1. Select the cell or ____________ of cells.

    2. From the Format menu, choose __________________.

    3. Choose the __________________ from the dialog box.

    4. Choose a _________ from the color list.

    5. Click OK or press _________.


Chapter Four: Formulas and Functions

Lesson 1: Using Formulas

One of the most valuable features of Excel is its ability to store mathematical formulas in individual cells. A formula is a data entry capable of performing mathematical and logical operations automatically.

There are several things we need to consider before entering a formula.

  1. Determine first where to put the formula. The location of the formula must be in the cell where you want to see the results of the computations.

  2. Formulas in Excel begins with an equal (=) sign.

    Click To expand

In the example above, the formula was written in the manner of writing it ordinarily. The only difference is that the = (equal sign) is in the beginning of the formula.

In MS Excel, instead of the value, the cell address of the value is usually used in writing the formula. In this way, any changes you make on the value will also take effect on the results of the formula.

Click To expand

A formula can also contain any or all of the following: functions, references, operators, and constants.

Note

It is better to use cell references instead of numbers when you build Excel formulas because it automatically calculates a new answer whenever you change a value that is referenced to a formula.

Working within a Cell or using the Formula Bar

In creating a working formula that can produce accurate results and outcome, one must know the rule of hierarchy in mathematical operators followed generally by your computer system. There are several operators like addition, subtraction, multiplication, and division. There is no problem when you are just using one operator at a time; but the difficulty begins when you put several operators together in a single formula. With more than one operators present in a formula, the logical question is which among the operators will the computer perform first?

To give you a clear picture, consider the following instructions;

Which among the two equations was correctly computed?

Equation 1 | Equation 2
5*2-4/2=3 | 5*2-4/2=8

Both are identical equations, but they can have different outputs depending on your approach to them. Equation 1 was performed by multiplying 5 and 2 then subtracting 4 from its product and finally dividing the difference by 2, thus we have 3 as the final answer.

On the other hand, Equation 2 was computed by multiplying 5 and 2 first, then going to the right part of the equation by dividing 4 and 2. Then the quotient from the second part was subtracted from the product of the first part, giving as 8 as the final result. Which one of the approach is correct? Or which one will the computer use as a means of calculating your formula?

There is no problem at all once you have familiarized yourself with the order of mathematical operators in Excel. The following operators will be calculated first in subsequent order:

  1. Operators enclosed in a parenthesis [( )]

  2. Percentage [%]

  3. Exponential [⁁]

  4. Multiplication [*] and Division [/]

  5. Addition [+] and Subtraction [−]

  6. Concatenation (of strings) [&]

  7. Comparisons of logical operators [=, <>, ><, <=, >=].

Note

When a formula both have a plus (+) or minus (−) operators, or both multiplication (*) or division (/), calculation always proceeds from left to right and never the other way around. In computer computations, the (*) is the sign for multiplication, slash (/) for division, and caret (˘) for the exponents.

To enter a formula in a cell or Formula bar, do the following:

  1. Move the active cell indicator where you want the result to be displayed.

  2. Do one of the following:

    • Click the Formula bar then type the = (equal sign).

    • Type = (equal sign) on the active cell to indicate the beginning of a formula.

  3. Type the cell reference of the first item to include in the formula.

  4. Press the math symbol that corresponds to the math operation you wish to perform.

  5. Type the cell reference of the second item to include in the formula.

  6. Repeat steps 4 and 5 until all the cell references you wish to include are in the formula.

  7. Do one of the following:

    • Press Enter.

    • Press Tab or an arrow to move to another cell.

    • Click [image] Enter.

Note

If you don't want to enter your formula, press Esc or click [image] Cancel.
Start Sidebar
Exercise 1

  1. Open a new workbook.

  2. Copy the data as shown below.

  3. You can copy the formatting of the data as per instruction of your teacher.

  4. Using the formula bar, type the formula in column E to get the cost of rentals.

    Click To expand

  5. Save your work as Formula1.xls on the Excel-Activities folder.

End Sidebar

Enter a Range within a Formula by Dragging

Aside from typing the cell address needed in creating formulas, you can make use of the mouse pointer to select cells while typing.

To create a formula using the mouse pointer, follow these steps:

  1. Select the cell where you want the result of the formula to appear or be displayed.

  2. Type = (equal sign) to begin with the formula.

  3. Click a cell or type a number.

    Click To expand

  4. Type an operator (+, −, *, /, ⁁).

  5. Click another cell or type a number.

  6. Repeat steps 3-5 if needed.

  7. Do one of the following to complete the formula:

    • Press Enter.

    • Press Tab or an arrow key.

    • Click Enter.

Aside from that, you can also make use of the functions that are built-in on your computer.

Note

Formulas can also be copied using Auto fill. If cells are adjacent to one another and have the same formula, you can make use of the fill handle to copy the formula instead of typing it in every cell.
Start Sidebar
Exercise 2

  1. Open the file Formula1.xls.

  2. Delete all data under the Cost column.

  3. Enter a formula on cell E7 then perform dragging to copy the formula to the cells below.

  4. Click Save and close MS Excel.

Edit Formulas

To edit formulas, do the following:

  1. Select the cell that contains the formula to be edited.

  2. Do one of the following:

    • Click the Formula bar with the I-beam mouse pointer to place the insertion point.

    • Double-click the cell to place the insertion point within the cell.

    • Press F2.

  3. Make the necessary changes you want in the formula.

  4. Click Enter when finished.

End Sidebar
Whiz Words
Start example
Formula | Range
Operators | Marquee
End example

Lesson Summary

Formulas allow you to create calculations for your data. In MS Excel, formulas always begin with an equal (=) sign followed by a cell address, operators, and numbers needed. Operators include the four basic operations and other mathematical operators.

There are different ways of editing formulas. You can double-click, press F2, or click the Formula bar to place the Insertion point on the cell that contains the formula you want to edit.

Study Help
Start example

Directions: Answer the following.

  1. What is a marquee?

  2. Define formula.

  3. How do you edit a formula?

End example
Start Sidebar
Activity 1

  1. Open a new workbook.

  2. Copy the data as shown below.

  3. Type a formula to compute the ff.

    • Total Expenses for Transportation

    • Total Expenses for Lunch

    • Total Expenses for Others

    • Amount Consumed

      • Daily

      • Weekly

      • Monthly

      • In a Year

  4. Save your work as My Budget.xls on Excel-Activities.

    Click To expand
End Sidebar


Lesson 2: Use References (Absolute and Relative) and Autosum

A reference identifies a cell or range on a worksheet and tells MS Excel where to look for the values or data you want to use in a formula. There are three kinds of references: relative, absolute, and mixed.

Relative References

A relative reference stores the exact location of the cells to which the formula refers while taking careful note of where those cells are located in relation to the cell that contains the formula. Using a relative reference allows Excel to automatically adjust the cell references to reflect their position to the new location as you copy or move a formula.

Absolute Reference

An absolute reference does not adjust the cell reference when you move or copy a formula. To specify an absolute reference, use dollar sign ($) within the cell address. For example, when you type $A$1 as part of a formula, Excel will look for the value on cell A1 even if you move or copy the formula to another location.

Mixed Reference

These are combinations of relative and absolute reference such as relative column and absolute row or absolute column and relative row.

The following Exercises will help you understand Absolute and Relative References

Start Sidebar
Exercise 1

Scenario: Suppose you are a student assistant in the Registrar's Office and you are advised to get the total number of high school enrollees from 1996-2000. Create a worksheet having the following data: Year Level, School Year, and Total Number of Enrollees. Refer to the sample given below:

Click To expand

  1. Compute the total number of enrollees from First Year to Fourth Year for the school year 1996. Thus, your formula will be =B5+B6+B7+B8.

  2. Copy the formula in cell B10 to cell C10 to get the total number of enrollees for the SY 1997.

    Note

    If you copy the formula in cell B10 to C10, Excel assumes you want to total the numbers in column C, so it adjusts the formula accordingly, to =C5+C6+C7+C8.

  3. Move the formula to the right using the fill handle to get the total number of enrollees in all school year. Thus, a reference changes from C10 to F10.

  4. Save your work as Enrollees (relative).xls in the Excel-Activities folder.

End Sidebar
Start Sidebar
Exercise 2

Scenario: Suppose you love to collect CD's and you save money for them every month. You want to know how much money you have spent or saved from your monthly allowance. Create a worksheet that includes the following data: Price of CD, Months, Monthly Allowance, Expenses, and Savings. Refer to the example given next page:

  1. Compute your expenses for the month of January. Your formula should be =SUM($C$4,C7,D7)

  2. Copy the formula in E7 to compute the expenses for the month of February.

  3. Using fill handle, copy the expenses for the remaining months.

  4. Compute your Savings for the month of January by subtracting Expenses to Monthly Allowance.

    Click To expand

  5. Using the fill handle, compute the savings for the rest of the months.

  6. Save your work as Financial Statement(absolute).xls in the Excel-Activities folder

End Sidebar
Note

When you type $C$4 as part of the formula, Excel looks for the values in cell C4 even if you move the formula or copy it to another location.

Using AutoSum

AutoSum allows you to quickly add a range of cells together. You can use AutoSum in a single cell, a row or column of cells, and in a range.

Use AutoSum in One Cell

  1. Select the cell where the formula will be placed. Usually, this will be below a column of numbers or to the right of the column numbers.

  2. Do one of the following:

    • Click AutoSum.

    • Press Alt+= (equal sign)

    Excel might suggest a range of numbers by displaying a marquee. It also automatically adds an equal sign (=), SUM, and an open and close parenthesis around the range.

  3. If the suggested range is not correct or does not display, drag the mouse pointer to select the range or type in the cell reference.

    Click To expand

  4. If the selected cells are correct, Press Enter or click [image] .

Using AutoSum in a selected rows or columns

To use AutoSum in selected rows or columns, follow these steps:

  1. Select the rows below or the column to the right of data.

  2. Click [image] AutoSum.

    Click To expand

Using AutoSum in a selected range

You can get both the sum of rows and columns if your range is in a rectangular form.

  1. Select the data, a blank row below, and a blank column to the right.

  2. Click [image] AutoSum.

    Click To expand

Extended AutoSum Functionality

The functionality of AutoSum has expanded to include a drop-down list box of the most common functions.

For example, you can click Average from the list to calculate the average, or connect to the Function Wizard for more options.

To use the expanded functionality of AutoSum, follow these steps:

  1. Select the cell/s where you want the result of the formula to be displayed.

  2. Click the drop-down list box beside the AutoSum button.

  3. Choose the desired function from the available options.

  4. Check if the selected cells are correct for the computation.

  5. Press Enter.

    Click To expand
Whiz Words
Start example
Absolute Reference | Relative Reference
Fill Handle | Reference
AutoSum | Alt+=
End example

Lesson Summary

Relative Referencing means that when a formula is copied, the cell address in the formula changes to its new location.

Absolute Referencing means that when a formula is copied, the cell reference remains constant.

AutoSum allows you to quickly total a range of cells together. You can use AutoSum in a single cell, a row or column of cells, and in a range.

Study Help
Start example

  1. What do you mean by reference?

  2. What is the difference between absolute and relative reference?

  3. What is AutoSum?

End example
Start Sidebar
Activity 1

  1. Create a worksheet that would show your savings for a week.

  2. Enter your daily allowance at cell A2.

  3. Use the illustration below as your guide.

    Click To expand

  4. Using the AutoSum, get the total amount of your expenses per day.

  5. On cell B9, subtract the total amount of expenses from your allowance.

  6. Make the first cell address of the formula on cell B9 absolute so that you can copy the formula using Auto fill.

  7. Compute for the Total Amount Saved to get your savings per week.

  8. Save your work Allowance.xls in the Excel-Activities folder.

End Sidebar


Lesson 3: Entering Functions

Use Insert Function to Use a Function

There are many functions to use to speed up calculation of your data. To create other functions, you can type them in a cell or use the Insert Function dialog box.

To use a function with the Insert Function, do the following:

Click To expand

  1. Select the cell that will contain the function.

  2. Do one of the following to display the Insert Function dialog box.

    • Click [image] to insert a function.

    • Click Insert, and then choose Function.

    • Press Shift+F3.

  3. Choose a function category and a function name. The Most Recently Used Category allows you to choose from functions you use often.

    To look for a function, type the function name in the Search a Function text box and then click Go.

    If you need help on a function, select the function and then click the Help on this function link.

  4. Click OK. The Formula Palette opens.

    The Formula Palette displays the arguments that the function needs.

  5. Fill each argument using any of the following ways:

    Click To expand

    • Drag the cell if it is visible on the screen.

    • Type a cell address or cell range.

    • Click the [image] Collapse Dialog button; select the range on the worksheet. When you are finished, click the [image] Redisplay Dialog button.

  6. Repeat Step 5 for any arguments you are using, and choose OK when finished.

Entering Functions using the Formula Palette

Aside from using the Insert Function dialog box to enter the formula palette, you can also enter it directly through a shortcut key and the most commonly used function drop-down list on the formula bar.

To enter a function through the Formula Palette, do the following:

  1. Select the cell where you want the function to appear.

  2. Type = equal sign.

  3. Click the drop-down list on the left edge of the Formula bar.

    Click To expand

  4. Choose one function on the list or select More Functions to go to the Insert Function dialog box.

  5. The Formula Palette opens, displaying the arguments for the function.

    Click To expand

    Fill each argument using any of the following ways:

    • Drag the cell if it is visible on the screen.

    • Type a cell address or cell range.

    • Click the [image] Collapse Dialog button; select the range on the worksheet. When you are finished, click the [image] Redisplay Dialog button.

  6. Repeat Step 5 for any arguments you are using, and choose OK when finished.

    Note

    You can also type an equal sign and the function name (for example; =SUM) and press Ctrl+A to display the Formula Palette.

Use Basic Functions

Functions are built-in formulas that perform special calculations automatically. It may be used by itself, or it may be combined with other functions.

Excel provides multiple functions; some are used for statistical and financial analysis, while others are used in database operations.

A function may be entered using the keyboard, the mouse or the combination of both.

The Sum Function

The Sum function adds a value in a range. If you were adding the cells B5, C5, D5, E5 and F5, the function would read as =SUM(B5:F5).

Click To expand

AutoSum - Because the SUM function is used frequently, Excel provides the AutoSum button on the Standard toolbar.

To use the Sum function, follow these steps:

  1. Select the cell where you want to enter the function.

  2. Type =SUM(.

  3. Enter the range of cells to sum up and then type the close parenthesis ()).

  4. Press Enter.

Note

You can enter the range by dragging the mouse. If the cells are non-adjacent, hold down the Ctrl key and then click the cells.
Start Sidebar
Exercise 1

  1. Open a new workbook.

  2. Type the following data as shown in the illustration.

    Click To expand

  3. Add formatting to your worksheet.

  4. Using the Sum function, compute for Total Quiz of each student.

  5. Save your work as Sum Function.xls at Excel-Activities folder.

End Sidebar

Average Function

The Average function gets the average of the values in range. For example, to get the average of the values in A1, B1, and C1, the formula would appear as:

Click To expand

To use the Average function using the keyboard, follow these steps:

  1. Select the cell where you want the result to appear.

  2. Type =AVERAGE(.

  3. Enter the range of cells, and then end it with the close parenthesis.

  4. Press Enter.

To use the Average function using the Insert Function, do the following:

  1. Select the cell where you want the result to appear.

  2. Do any of the following:

    • Click the Insert Function button on the Formula bar.

    • Click the drop-down arrow beside the AutoSum button and choose Average.

    • Type = equal sign and then choose Average from the drop-down list beside the Formula bar.

      The Insert Function dialog box appears.

      Click To expand

  3. From the list of Most Recently Used Function Category, choose Average.

  4. Click OK. The Function Arguments dialog box appears.

    Click To expand

  5. Enter the range of cells.

  6. Click OK.

Start Sidebar
Exercise 2

  1. Open the file SUM Function.xls

  2. Add another column beside Total Quiz. Name the column Average.

    Click To expand

  3. Compute for the average of the quizzes of each student.

  4. Make sure the Heading is in the middle of the table.

  5. Save your work.

End Sidebar

Statistical Functions

MIN (Minimum) | - Indicates the lowest value in a range.
MAX (Maximum) | - Indicates the highest value in a range.
MEDIAN | - Returns the median of the given numbers. The median is the number between the middle of a set of numbers.

Example: Median (1,2,3,4,5) equals 3

The methods to use these functions are similar in using Sum or Average functions. But the easiest way is to type in the function then select the range of cells.

Start Sidebar
Exercise 3

  1. Open the file Sum Function.xls.

  2. Type MIN in cell B18, MAX in cell B19 and MEDIAN in cell B20.

  3. Compute for MIN, MAX and MEDIAN.

    Click To expand

  4. Save your work.

End Sidebar
Whiz Words
Start example
Sum | Average
MIN | MAX
MEDIAN | Function
End example

Lesson Summary

Excel provides multiple functions. Functions are built-in formulas that perform special calculations automatically. Some of the basic functions in Excel are Sum, Average, Min, Max and Median.

Study Help
Start example

Directions: Answer the following correctly.

  1. What is a function?

  2. What do you think is the easiest way in using the Sum function? Explain your answer.

  3. What is a median?

  4. What is the use of the Average function?

  5. What is the difference between MIN and MAX?

End example
Start Sidebar
Activity 1

  1. Create a Grading Sheet. Use the illustration below as your guide.

  2. Encode at least 10 students with their corresponding grade in each subject. Before you compute the final grade, you should get first their final grade for PEHM, by getting their grade in PE, Health, and Music.

    Click To expand

  3. Get the MIN, MAX, and MEDIAN of each subject including the final grade.

  4. Save your work as Summary of Functions.xls in the Excel-Activities folder.

End Sidebar


Lesson 4: Using Other Functions

Using Date Functions (NOW and DATE)

Using NOW

You may use today's date in a worksheet if, for example you want to find the number of days since your friend sent an e-mail to you. The formula would be today's date minus the e-mail date.

If you want to calculate the current date and time (that is your system clock), use =NOW(). If you want to include just the date, use =TODAY(). You do not include any arguments in the parenthesis.

Using Date

The Date function requires three integer arguments (that is, Year, Month, and Day). If the number is not formatted, it will look strange. However, when formatted as a date it can look more meaningful.

To enter Date, follow these steps:

  1. Select the cell where you want the Date to appear.

  2. Click the Insert Function button.

  3. From the Function Category, choose Date & Time, then select the Date function.

  4. Click OK. The Function Arguments appears.

  5. Complete the Arguments.

  6. Click OK.

    Click To expand

Use Financial Functions (FV and PMT)

The financial functions help make decisions regarding borrowing and lending money. It deals with interest, rate, and time.

The following are the common arguments that you will see in the financial functions:

  • Rate - is the interest rate per period. Usually, payments are calculated monthly, so annual rate would be divided by 12.

  • Nper - is the total number of payment periods in an annuity. Monthly payments are usually dealt with and the term is often expressed in years, you multiply years and times it to 12.

  • PMT - is the payment made each period; it cannot change over the life of the annuity. Typically, PMT contains principal and interest but no other fees or taxes. If PMT is omitted, you must include the PV payment.

  • PV - is the present value, or the lump-sum amount that a series of future payments is worth right now. If PV is omitted, it is assumed to be zero (0), and you must include the PMT argument.

  • FV - is the future value of the investment after the term is up at the interest rate calculated. Future Values is used to compute for the future value of an investment based on recurring periodic payments at an unvarying rate of interest

To use FV, here is an example:

  • Suppose you save P200.00 a month for three years with an interest rate of 5%, you will have P7,750.67 at the end of the period

    Click To expand

The PMT function allows you to compute the periodic payment amount for a loan on a car, house, or any other item.

To use PMT, here is an example:

  • Suppose you loan money to a bank amounting to P100,000.00. And you have to pay this amount in 20 years with an interest rate of 3%. The monthly payment will be P554.60.

    Click To expand

    The PMT requires you to enter the interest rate, length of loan (number of periods), and PV (Present Value, the amount you are borrowing or lending).

Use Logical Functions (IF)

Logical function is a powerful worksheet function that enables you to add decision-making and logical preference for your worksheets.

  • IF() Function

  • IF() statement is very useful for testing a cell and making decisions based on cell content.

  • Syntax: IF(logical_test, value_if_true,value_if_false)

  • IF statement requires three arguments:

  • Logical_Test - is any value or expression that can be evaluated to TRUE or FALSE.

  • Value_if_true - is the value that is returned if logical_test is TRUE.

  • Value_if_false - is the value that is returned if Logical_test id FALSE.

    Click To expand

To use the IF function using the keyboard, follow these steps:

  1. Select the cell where you want the result to appear.

  2. Type = (equal sign) and the function IF and an open parenthesis.

  3. Type the correct arguments for the function, then type the close parenthesis.

Start Sidebar
Exercise 1

  1. Create a new worksheet. Input 10 students' name and their scores from Quiz 1 to 5 (each quiz is 20 items).

  2. Compute for the total score of each student in column 6.

  3. Create a formula in column H in order to determine if the students passes or failed. 50 and above = passed, 49 below = failed.

  4. Use the illustration below as your guide.

    Click To expand
End Sidebar

Nested Ifs

You can have one nested function inside another one. In the first argument, you will test a condition. The second or third arguments, the value if true or false could have another If statement.

You can type nested Ifs in the Formula bar.

Note

Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests.
Whiz Words
Start example
Future Value | IF Function | Logical Functions
PMT | Arguments | Present Value
End example

Lesson Summary

=NOW() function allows you to put current day and time to your cell.

To calculate a date value, it requires three arguments such as year, month and day.

Financial functions generally deal with the time value of money and need the interest rate, number of payments loan or investment amount, and payment amount. FV (Future Value) computes for the value of an investment given periodic payments, an interest rate, and a term. PMT (payment) finds how much money you will have to pay if you borrow a certain amount at an interest rate and term.

IF() function allows you to look at a condition and place one result in the cell if the condition is true and a different answer if the condition is false.

Study Help
Start example

  1. What is the difference between PMT and FV?

  2. What are the three arguments needed in the IF function?

  3. What is a logical function?

  4. What are the three arguments needed in the Date function?

  5. What is Nper?

End example
Start Sidebar
Activity 1

  1. Create a worksheet that would serve as a cash register.

  2. Copy the illustration below and add formatting to it.

    Click To expand

  3. The cells under the CD Title, Price and Amount should have a nested IF formula so that once you input a code, it will automatically display the CD Title, Price and Amount.

  4. Create a formula in E5 that computes for the Total Amount.

  5. In Cell B27, the change should automatically be displayed once the amount of payment was entered on cell B26.

  6. Save your work as CD Bar.xls at Excel-Activities folder.

End Sidebar


Chapter Review

I. IDENTIFICATION: Identify the following: Write your answer on the space provided below.

  • __________________1. It is a data entry capable of performing mathematical and logical operations automatically.

  • __________________2. It is a moving broken-dashed line that goes around the cell you click, its cell reference will be entered into the active cell and in the formula bar as shown in the illustration.

  • __________________3. Formulas always begin with this sign followed by a cell address, operators, and numbers needed.

  • __________________4. It identifies a cell or range on a worksheet and tells MS Excel where to look for the values or data you want to use in a formula.

  • __________________5. It allows you to quickly add a range of cells together.

  • __________________6. It means that when a formula is copied, the cell reference remains constant.

  • __________________7. It means that when a formula is copied, the cell address in the formula changes to its new location.

  • __________________8. It displays the arguments that the function needs.

  • __________________9. This function gets the average of the values in range.

  • __________________10. It indicates the lowest value in a range.

  • __________________11. The number between the middle of a set of numbers.

  • __________________12. It indicates the highest value in a range

  • __________________13. It requires three integer arguments (that is, Year, Month, and Day).

  • __________________14. It helps make decisions regarding borrowing and lending money.

  • __________________15. It allows you to compute the periodic payment amount for a loan on a car, house, or any other item.


Chapter Five: Working with Worksheet and Workbooks

Lesson 1: Working with Rows and Columns

Inserting Rows and Columns

Inserting rows and columns is necessary if you have to enter a series of text in a table and there are no enough rows and columns for it.

To insert rows or columns, follow these steps:

  1. Do one of the following:

    Insert a single row.

    Click a cell in the row immediately below where you want the new row.

    Example: To insert a new row above row 5, click the row heading number 5, and then right-click and choose Insert.

    Insert multiple rows.

    Select rows immediately below where you want the new rows. Select the same number of rows as you want to insert.

    Insert a single column.

    Click a cell in the column immediately to the right of where you want to insert the new column.

    Example: To insert a new column to the left of column B, click a cell in column B.

    Insert multiple columns.

    Select columns immediately to the right of where you want to insert the new columns. Select the same number of columns you want to insert.

  2. Do one of the following:

    • On the Insert menu, click Rows, or Columns.

    • Right-click the row heading and choose Insert.

Deleting Rows or Columns

Deleting rows or columns removes them from the worksheet and move the cells to fill the space where they were.

To delete rows or columns, follow these steps:

  1. Select the row or column headings you want to delete.

  2. Do one of the following:

    • Right-click row or column heading and choose Delete.

    • Click the Edit menu and choose Delete.

Hiding Rows or Columns

Hiding rows and columns helps you organize a large worksheet.

Hide rows or columns

  1. Select the row or column heading you want to hide.

  2. Do one of the following:

    • Click the Format menu, choose Row or Column, and then choose Hide.

    • Right-click the row or column, then click Hide.

    • Press Ctrl+9 to hide rows or Ctrl+0 to hide columns.

      Click To expand

Unhide rows or columns

  1. To display hidden rows or columns, do any of the following:

    • Select the row heading number above and below the hidden area to unhide rows.

    • Select the column heading letters to the left and right of the hidden area to unhide columns.

    • Click the Select All button or press Ctrl+A to unhide all rows and columns.

  2. Do one of the following:

    • Click the Format menu, choose Row or Column, and then click Unhide.

    • Press Ctrl+Shift+( (open parenthesis) to unhide rows or Ctrl+Shift+) (close parenthesis) to unhide columns.

    • Right-click the row or column headings, and choose Unhide.

      Click To expand

Freeze and Unfreeze Rows and Columns

One way of viewing the parts of your worksheet while working on a large worksheet is by using the option to freeze rows and columns. In the Window menu, you have an option to immobilize your rows and columns using the Freeze Panes option. Freezing panes will either lock the top and/or upper-left panes depending on where you have placed your pointer.

To freeze panes, follow the steps below:

  1. Move the cell pointer below any rows and to the right of any columns you want to freeze.

  2. Click the Window menu and choose Freeze Panes.

To unfreeze rows and columns, click the Windows menu and choose Unfreeze Panes.

Splitting Worksheet

Freezing rows and columns allows you to view some parts of the worksheet. Another way to make it easier for you to view some portions of the worksheet is to split it. This will enable you to view different sections of the worksheet side by side in separate panes. Panes are the sections of the worksheet.

An alternative to freezing the rows or columns is to split the worksheet. A split worksheet enables you to view different sections of the worksheet side-by-side in separate panes. Each section of a worksheet is referred to as a pane.

To split the worksheet, do the following:

  1. Move the cell pointer below any rows and to the right of any columns where you want the split to occur.

  2. Click the Window menu and choose Split.

  3. Click the mouse pointer in one of the panes and use the scroll bars to move to that pane.

    Click To expand

    To remove a split window:

    • Click Window from the menu bar, then choose Remove Split.

    • Press Alt+W and then press S.

Note

To adjust or remove panes, you simply rest your pointer to the split bar until it becomes a double arrow. Drag it to a new location or to row or column heading.
Whiz Words
Start example
Panes | Freeze Panes
Column | Row
Split
End example

Lesson Summary

Inserting and deleting rows and columns helps you add space for or get rid of extra data. Select one or more rows or columns, right-click a row or column header, and choose Insert or Delete.

Hiding rows and columns helps you organize a large worksheet.

Excel allows you to view multiple panes in your worksheet. You can also freeze and unfreeze a portion of your worksheet. To split a window, move the active cell where you want the worksheet to split, and then choose the Split option in the Window menu.

Study Help
Start example

  1. What is a pane? When are we going to freeze panes?

  2. What keys should we press to remove the split of a Worksheet?

  3. How to add rows and columns? Give two ways to add rows and columns.

End example
Start Sidebar
Activity 1

  1. Open the file Sum Function.xls.

  2. Hide the column that contains the Total Quizzes.

  3. Delete columns 18, 19 and 20.

  4. Make cell C6 the active cell.

  5. Click Freeze Panes in the Windows menu.

  6. Save your work as Freeze.xls at Excel-Activities folder.

End Sidebar


Lesson 2: Modifying Worksheets

Color-coded Worksheet Tabs

Coloring worksheet tabs helps you organize your work by adding color to your worksheet tabs.

To add color to sheet tabs, do the following:

  1. Select the sheet you want to color.

  2. Do one of the following:

    • On the Format menu, point to Sheet and the click Tab Color.

    • Right-click on the sheet tab, and then choose Tab Color.

      The Format Tab Color appears.

  3. Click the desired color, and then choose OK.

    Click To expand
    Click To expand

Control Automatic Change with Smart Tags

Smart Tags allows you to immediately adjust how information is pasted or how automatic changes occur in your Office programs.

These are buttons that appear automatically on your worksheet which help you with tasks such as automatic correction options, paste options, automatic fill options, insert options, and formula error checking.

By clicking a button, you can choose from options related to your task without leaving the worksheet or the cells you are working on.

Click To expand

  • Paste Options button - It appears just below your pasted selection after you paste text or data. When you click the button, a list appears that lets you determine how the information is pasted into your worksheet.

    The available options depends on the type of content you are pasting, the program you are pasting from, and the format of the text where you are pasting.

  • Trace Error button - It appears next to the cell in which a formula error occurs, and a green triangle appears in the upper-left of the cell. When you click the arrow next to Trace Error, a list appears to give you options for error checking.

  • Insert Options button - It appears next to your inserted cells, rows, or columns. When you click the arrow next to Insert Options, a list of formatting options appears.

  • Auto Fill Options button - It appears just below your filled selection after you fill text or data in a worksheet. When you click the button, a list appears to give you options for how to fill the text or data.

    The available options depends on the content you fill, the program you fill from, and the format of the text or data you are filling.

Change Zoom Setting

To increase the view of a worksheet so the text is larger or to reduce its size to see more cells, you can use the zoom feature.

There are two ways to change the zoom setting of your worksheet:

  1. Using the Standard toolbar in the [image] click the size you want, or enter a number from 10 to 400.

  2. Using the menu bar:

    1. in the View menu, click Zoom. The Zoom dialog box appears.

    2. In the Zoom dialog box, click a percent option, choose Fit Selection, or type a value in the Custom box.

    3. Click OK.

      Click To expand

      To return the zoom to its original size, click 100% in the Zoom box on the Standard toolbar.

Check Spelling

Make sure that all words are spelled correctly. Misspelled words can distract the reader and damage your credibility.

Click To expand

To check the spelling in your worksheet, follow these steps:

  1. Press Ctrl+Home to start the spell check at the beginning of the worksheet.

  2. Do one of the following:

    • Press F7.

    • Click [image] Spelling on the Standard toolbar.

    • Click the Tools menu, and then choose Spelling.

  3. If there are any misspelled words, the Spelling dialog box is displayed.

    The misspelled word appears in the Not in Dictionary text box.

  4. Do any of the following to correct the word:

    • Click Change to change the misspelled word with the selected suggestion.

    • Click Change All to change the misspelled word with the selected suggestion in the entire worksheet.

  5. Do any of the following to manage the word displayed in the Not in Dictionary text box.

    • Click Ignore Once to skip the current occurrence of the word or Ignore All to skip all occurrences of the word in the worksheet.

    • Click Add to Dictionary to include the word in the dictionary. The word will no longer be considered misspelled when you check spelling again for any workbook.

    • Click AutoCorrect to add the word as an AutoCorrect entry.

  6. If you accidentally changed the wrong word, click Undo Last.

  7. When finished, click Close.

If you reach the end of the worksheet, a prompt will appear asking you if you want to continue from the beginning of the worksheet. Click Yes to continue.

Click To expand

After completing the spelling check, you will be prompted that the spelling check is complete.

Click To expand
Whiz Words
Start example
Spelling Check | Tabs
Smart Tags | Zoom
Paste Options Button | Insert Options Button
AutoFill Options Button | Trace Error Button
End example

Lesson Summary

Adding color to your worksheet tabs helps you organize your work. To add color to your tab, click the worksheet tab you want to color. Right-click, then click on Tab Color, when the Format Tab Color appears, select the desired color, then click OK.

Smart Tags allow you to adjust how information is pasted or how automatic changes occur in your Office programs.

The Zoom feature of Excel allows you to magnify or shrink the view of the worksheet. The easiest way to change the Zoom setting of your worksheet is to click on the Zoom box, then type a value (10-400).

Spell Check corrects spelling errors in your work.

Study Help
Start example

Directions: Answer the following:

  1. What is a smart tag? When does it appear?

  2. How to check the spelling in the worksheet?

  3. What is the importance of putting colors to the worksheet tab?

  4. Why do we have to change the zoom setting of our worksheet?

End example
Start Sidebar
Activity 1

  1. Open the file Freeze.xls

  2. Rename Sheet 1 to Freeze.

  3. Spell Check the worksheet. Correct all misspelled words.

  4. Change the zoom setting to 200.

  5. Click Save.

End Sidebar


Lesson 3: Moving Around the Workbook

Switch between Worksheets in a Workbook

There are three sheets available in a workbook: Sheet1, Sheet2, and Sheet3. To switch between worksheets in a workbook, do any of the following

  • Click the sheet tab you want to open.

  • Press Ctrl+Page Down to move to the next worksheet or Ctrl+Page Up to go to previous.

  • Right-click on the tab scrolling buttons, then click the sheet you want to open.

Click To expand

You can also use the GO TO command to go to another worksheet.

  1. Press F5. The GO TO dialog box appears.

  2. In the Reference text box, type the Sheet Name with a quote, an Exclamation Point (!), and a cell address.

  3. Click OK.

    Click To expand

Rename a Worksheet

The default names for the worksheets are Sheet1, Sheet2, and Sheet3.

To rename a worksheet, do one of the following:

  • Double-click the Sheet tab.

  • Right-click the Sheet Tab and then choose Rename.

  • Click the sheet tab you want to rename. In the Format menu, click Sheet, then choose Rename.

    Click To expand

The current name is highlighted. Type the new name to overwrite the current name, and then press Enter.

Click To expand

Insert and Delete Worksheet

If you need another worksheet to your workbook, you can add another worksheet by following these steps:

Click To expand

  1. Select a sheet tab, then right-click. A list of options will appear.

  2. Click on the Insert option. The Insert dialog box appears.

    Click To expand

  3. Choose Blank Worksheet, then click OK.

    You can also use the menu bar.

    • Click Insert menu, then choose Worksheet.

Delete a Worksheet

If you want to remove unnecessary worksheet on your workbook, follow the steps below:

  1. Select the sheet you want to delete.

  2. Do any of the following:

    • Right-click, then choose Delete.

    • On the Edit menu, click Delete Sheet.

Moving and Copying Worksheet

If you want to rearrange your worksheet according to your own preference, do the following:

  1. Select the worksheet you want to move.

  2. Do any of the following:

    • Click on the sheet tab, then drag the worksheet where you want it located.

    • Right-click on the sheet tab, then click Move. The Move and Copy dialog box appears.

    • Click on Edit menu then choose Move or Copy Sheet.

  3. Select the sheet where you want the selected sheet to be moved.

  4. Click OK.

    Click To expand

In some cases, you need to copy the content of the entire worksheet. Instead of copying only the content, you can copy the whole worksheet, then rename it.

To copy a worksheet, do the following:

  1. Select the worksheet you want to copy.

  2. Press Ctrl key while dragging to the right of the sheet you want to copy. A small triangle indicating the initial position of the copied worksheet appears. Release the mouse button if you reached the desired location.

    Click To expand

The name of the copied worksheet will be based on the worksheet that you copy with a number inside a parenthesis.

You can also use the Move and Copy dialog box to copy a worksheet.

  1. Select the sheet you want to copy.

  2. Right-click, then choose Move and Copy. The Move and Copy dialog box appears.

  3. Select the name of the sheet you want to copy.

  4. Make sure that the Create a copy check box is marked.

  5. Click OK.

    Click To expand

Link Worksheet and Consolidate Data Using 3-D References

When using a formula, you may need a data from other worksheet in your workbook. You can use references in different worksheets. This is called 3-D reference. It is a reference to a range that spans two or more worksheets in a workbook.

To create a formula with a 3-D range, do the following:

  1. Select the cell where you want to place the formula.

  2. Type an equal sign.

  3. Click the cell that contains the contents to be referenced. If the cell is located on a different worksheet, click the sheet tab, then the cell containing the contents.

  4. Type an operator.

  5. Repeat steps 3 and 4 if needed.

  6. Press Enter.

Use 3-D Reference in a Function

When using a function, you can also refer to another worksheet.

To use 3-D reference in a function, do the following:

  1. Select the cell where the function is to be placed.

  2. Click Insert Function [image] on the Formula bar. The Insert Function dialog box appears.

  3. Choose the Function Category and Function Name.

  4. Type the sheet name and cell reference in each argument box of the Formula Palette or click the Collapse Dialog Box button and click the desired sheet tab.

    Click To expand

  5. Repeat step 4 for each argument in the function.

  6. Click OK when finished.

Whiz Words
Start example
3-D Reference | Sheet Tab
Worksheet | Workbook
End example

Lesson Summary

There are three sheets available in a worksheet when you open Excel. The worksheets default names are Sheet1, Sheet2, and Sheet3. To rename these sheets, simply double-click on the sheet name then type the desired new name for the sheet and press Enter.

3-D Reference is a reference to a range that spans two or more worksheets in a workbook.

Study Help
Start example

Directions: Read carefully each group of procedures. Determine which should be first, second and so on to make out a well-organized procedure.

  1. To Move a Worksheet

    ___ Drag the sheet tab to the right or left.

    ___ A small triangle will appear on the upper side of the sheet tab.

    ___ Click the sheet tab you want to move.

    ___ Release your hold on the mouse button.

  2. To Copy a Worksheet

    ___ Press CTRL key.

    ___ Select the sheet tab you want to copy

    ___ Drag the sheet to the right or left.

    ___ A small triangle will appear.

    ___ Release your hold on the mouse as well as the Ctrl key.

  3. To Add a Sheet

    ___ Choose the Insert option.

    ___ Choose the sheet tab where you want the new sheet to be inserted.

    ___ Click the right mouse button.

    ___ A pop-up menu will appear showing a list of options to choose from.

    ___ In the Insert dialog box, click the Worksheet under the General tab.

    ___ Click the OK button.

End example
Start Sidebar
Activity 1

  1. Open a new workbook.

  2. Rename the sheets on your workbook with the following:

    • Sheet1 First Grading

    • Sheet2 Second Grading

    • Sheet3 Third Grading

  3. Color the following sheets with the following:

    • First Grading - Blue

    • Second Grading - Green

    • Third Grading - Red

  4. Add another three sheets on your workbook.

    • Sheet4 - Fourth Grading, Tab color - Yellow

    • Sheet5 - Final, Tab color - Gray

  5. Delete the unnecessary worksheet.

  6. Rearrange the worksheet if necessary.

  7. Save your work as Grading System at Excel-Activities folder.

End Sidebar


Lesson 4: Adding Comments to Your Worksheet

Adding Comments

A comment is a note that you attach to a cell, separate from other cell content. Comments are useful as reminders or to provide feedback to other users.

To add comments to a cell, follow these steps:

  1. Select the cell where you want to insert a comment.

  2. Do any of the following:

    • From the Insert menu, click Comment.

    • Right-click on the selected cell and then choose Comment. The Comment text box appears prompting you to type your comment. It also displays the user name of the computer.

Once you inserted a comment, it will always be visible on your worksheet. To hide your comment so that it will only appear when you point to the cell that has a comment, click on View menu and then click Comment.

Click To expand

Viewing Comments

Microsoft Excel provides several ways to view comments. Cells with comments have indicator triangles in the upper-right corners. If you rest the pointer over a cell that has an indicator, its comment appears. You can also display comments continuously either individual comments or all the comments on the worksheet. The Reviewing toolbar steps you through each comment in a workbook in sequence. You can also print out the comments, either in place on the worksheet or as a list at the end of the printout.

Editing Comments

To edit comments, do the following:

  1. Select the cell that contains the comment you want to edit.

  2. On the Insert menu. Click Edit Comment.

  3. The Comment Text box appears and from there you can edit your comment.

You can also right-click on the cell and the click Edit Comment.

Deleting Comments

  1. Select the cell that contains the comment you want to remove.

  2. Do any of the following:

    • On the Edit menu, choose Clear and then click Comments.

    • Right-click and then click Delete Comment.

Creating and Responding to Discussion Comments

Discussion comments are remark or topic of discussion that is associated with a web page or MS Office files and stored on a discussion server.

Note

You can only use the Web Discussion feature if you have a discussion server.

Creating Discussion Comments

  1. On the Tools menu, point to Online Collaboration, and click Web Discussions.

  2. If the Add or Edit Discussion Servers dialog box appears, select a discussion server.

  3. On the Web Discussions toolbar, click Insert Discussion about the Document.

  4. Under Discussion subject, type a name for the subject of the discussion.

  5. Under Discussion text, type your comments.

  6. When you click OK, your comment appears in the Discussion pane at the bottom of the window.

Reply, Edit, and Delete Web Discussions

Click To expand

Web Discussion Toolbar

  1. At the end of the discussion comment, click Show a menu of actions and then click Reply, Edit, or Delete.

  2. If you are replying to or editing a comment, under Discussion text, type your reply or type any changes you want to make to the text.

Filtering List using AutoFilter

AutoFilter is used if your worksheet is setup like a database. AutoFilter enables you to select only items you want to see in a list.

Click To expand

To activate AutoFilter, do the following:

  1. Position the active cell indicator in the list.

  2. On the Data menu, choose Filter and then click AutoFilter.

    Click To expand

Notice that each field name now has an arrow that you can use to select the data you want to see.

When the AutoFilter is activated, you can display the data you want by doing any of the following:

  • Click the filter arrow and choose a value in the list.

  • You can also press Alt+Down to activate the filter list but be sure that the active cell indicator is on the desired field.

  • To display all the values for the field, click All.

  • To select the largest or smallest values in a numeric field, click the filter arrow and choose Top 10. A dialog box appears.

Choose the desired option from the drop-down list and then click OK.

  • You can also set your own criteria; just choose Custom from the filter list. And the Custom Auto Filter dialog box appears prompting you to customize the filtering of your data.

    Click To expand
Whiz Words
Start example

Comments

AutoFilter
End example

Lesson Summary

Comments are notes that you enter for a cell. You can easily determine that a cell has a comment by the indicator triangles in its upper-right corner.

AutoFilter allows you to view a portion of your data in a worksheet if it is setup as a database.

Study Help
Start example

  1. Define comment.

  2. How can you determine that a cell has a comment?

  3. What is the use of AutoFilter?

End example
Start Sidebar
Activity 1

  1. Create a new worksheet.

  2. Copy the illustration show below.

    Click To expand

  3. Insert a comment to the State field. Choose only one per state. Write the following comments: AL-stands for Alabama, AK-stands for Arkansas, and AZ-stands for Arizona.

  4. Apply the 3-D Effects 2 format to your table.

  5. Apply AutoFilter to the table.

  6. Save you work as Sales Report in Excel-Activities folder.

End Sidebar


Chapter Review

  1. TRUE OR FALSE. Write T if the statement is true and F if it is false.

    • ___1. Inserting rows and columns is necessary if you have to enter a series of text in a table and there are enough rows and columns for it.

    • ___2. Hiding rows and columns helps you organize small worksheet.

    • ___3. Freezing panes will either unlock the top and/or upper-left panes depending on where you have placed your pointer.

    • ___4. A split worksheet enables you to view different sections of the worksheet side-by-side in separate panes.

    • ___5. Each section of a worksheet is referred to as a pane.

    • ___6. Tabs helps you organize your work by adding color to your worksheet tabs.

    • ___7. Smart Tags allows you to immediately adjust how information is pasted or how automatic changes occur in your Office programs.

    • ___8. The Zoom feature of Excel allows you to magnify or shrink the view of the worksheet.

    • ___9. 2-D reference is a reference to a range that spans two or more worksheet in a workbook.

    • ___10. Spell Check corrects spelling errors in your work.

  2. Find the Difference: Encircle the letter with the correct answer.

    1. To select the worksheet you want to move, do any of the following except:

      1. Click on the tab key, then drag the worksheet where you want it located.

      2. Right-click on the sheet tab, then click Move. The Move and Copy dialog box appears.

      3. Click on Edit menu then choose Move or Copy Sheet.

    2. To select the sheet you want to delete, do any of the following except:

      1. Right-click, then choose Delete.

      2. On the Edit menu, click Delete Sheet.

      3. Press the Delete key.

    3. To rename a worksheet, do one of the following except:

      1. Right-click the Sheet tab and then choose Rename.

      2. Double-click the Status bar.

      3. Click the Sheet tab you want to rename. In the Format menu, click Sheet, then choose Rename.

    4. To switch between worksheets in a workbook, do any of the following except:

      1. Click the Sheet tab you want to open.

      2. Press Ctrl+Page Down to move to the next worksheet or Ctrl+Page UP to go to previous.

      3. Right-click on the status bar, then click the workbook you want to open.

    5. To remove a split window, do any of the following except:

      1. Click Window from the menu bar, then choose Remove Split.

      2. Press Alt+W and then press S.

      3. Press Tab and then Ctrl+R.


Chapter Six: Page Setup and Printing

Lesson 1: Printing Your Work

Previewing your Document

After saving your worksheet or before finally printing your work, it is better to view your sheet to see on how will it appear on the page. Previewing your document before printing will help you to at least make last-minute corrections before you print it.

Click To expand

By default, the Print Preview command gives you a full page view of your document. In the Print Preview window, your pointer becomes a magnifying glass. To take a closer look on a portion of a worksheet, position the magnifying glass and click the left mouse button. To return to a full-page view, click the mouse again.

To view your document before printing, follow these steps:

  1. Do any of the following:

    • On the File menu, click Print Preview.

    • Click the Print Preview button [image] in the Standard toolbar.

    • Press ALT + F + V

    The window changes to Print Preview.

    Click To expand

  2. The Print Preview window has buttons that allows you to do the following:

    • Click Next to go to the next page.

    • Click Previous to go to the preceding page.

    • Click Zoom or the magnifying glass to enlarge the view of the document. Click again the Zoom button or anywhere around the document to return the screen to whole page view.

    • Click Print to display the Print dialog box and print the document.

    • Click the Page Setup button to display the Page Setup dialog box.

    • Click Margins to display the margins of the document. You can drag the markers to change the margins of the column width. Click again the Margins button to hide the margins.

      Click To expand

    • Click the Page Break Preview to see where the pages will break.

    • Click the Close button when finished with the Print Preview window.

Printing your Document

The output that the printer produces is called a hardcopy. When you are ready printing the active worksheet, follow these steps:

Printing a Worksheet

To print a worksheet, follow the steps below:

  1. Select the sheet to be printed.

  2. Do one of the following:

    • Click the Print button.

    • Press Ctrl+P and then click OK.

    • On the File menu, click Print and then OK.

Printing a Workbook

Printing a workbook means that the whole worksheet in the workbook will be printed.

To print a workbook, do the following:

  1. On the File menu, click Print. The Print dialog box appears.

  2. Click the Entire Workbook option.

  3. Click OK if you do not have any more corrections.

    Click To expand

Print a Selection

There are times that you don't want to print the whole content of your worksheet. In Excel, you can select a range to be printed. Do the following:

  1. Select the range you want to print by doing any of the following:

    • Drag the mouse pointer across the range.

    • Hold down the Shift key and use the arrow keys to select the range.

  2. From the File menu, click Print.

  3. From the Print What category, choose Selection.

  4. Choose OK.

Preview and Print Non-adjacent Selections of Worksheet in a Workbook

To Preview and Print non-adjacent selections of worksheets, do the following:

  1. Click the sheet that you want to be active.

  2. Hold down the CTRL key to select another worksheet that are not adjacent to each other.

  3. Click the Print Preview.

    Notice that the preview shows two selected worksheet successively

  4. Click File, then Print. The selected worksheets will automatically be printed.

Whiz Words
Start example
Print | Print Preview
Print Preview Window
Hardcopy | Workbook
End example

Lesson Summary

  • To see the preview of your work, click on the Print Preview button for the Standard toolbar or go to the File menu, click Print.

  • Press Ctrl+P.

  • Click the Print button in the Standard toolbar.

Study Help
Start example

  1. Why do we have to preview our document before printing?

  2. What are the steps in printing a selection?

  3. Enumerate the ways to print a document.

End example
Start Sidebar
Activity 1

  1. Open the file Freeze.xls

  2. Preview the file to see how your document will look like before printing.

  3. Make necessary changes if needed.

  4. Print your work.

End Sidebar


Lesson 2: Printing Options

Printing Options

The Print dialog box displays options for you to specify the number of copies to be printed, specific pages and choose printer.

Click To expand

Setting and Clearing the Print Area

Print Area allows you to choose multiple ranges, and that each range can be printed on a separate page each time you issue a print command. The Print Area will not be moved or changed unless specified or cleared.

To set a print area, follow these steps:

  1. Select a range or multiple ranges to print.

  2. On the File menu, point to Print Area and then click Set Print Area.

You can also set the print area through the Page Break Preview mode.

  1. On the View menu, click Page Break Preview.

  2. Highlight your defined print area.

  3. Right-click and then choose the option Set Print Area.

If you preview your document, only the selected range that you set as print area will be visible.

To set change or set the print area again, repeat steps 1 and 2.

You can also use The Page Setup dialog box to set the print area. To do this, follow these steps:

  1. On the File menu, click Page Setup.

  2. On the Page Setup dialog box, click on the Sheet tab.

    Click To expand

  3. Do any of the following:

    • Type the range in the Print Area.

    • Use the Collapse dialog button to select the range.

    • Click again the Collapse button.

  4. Click OK.

To clear the Print Area, do the following:

On the File menu, point to Print Area and then click Clear Print Area.

If you try to preview again your document, you will see the entire content of the worksheet.

Print the Print Area

Printing the Print Area is almost the same as printing the worksheet. Once the print area has been set, perform the process of printing. That is, click File, then Print.

Set Page Margins and Centering

Margins are blank spaces outside the printing area on a page. Using the Page Setup dialog box, you can set the margins and center the page.

Click To expand

To change the margins, follow these steps:

  1. On the File menu, click Page Setup.

  2. Click the Margins tab.

  3. To change the margins on top, bottom, left, and right of the page, do any of the following:

    • Type the desired size on the text box.

    • Use the Increment button (up and down) to increase or decrease the size of the margins.

  4. To center the page, click the Horizontally check box under Center on Page category.

  5. When finished with the settings, click OK.

Change Page Orientation and Scaling

There are two kinds of page settings you can readily adjust before printing your work, Page Orientation and Scale. The former enables you to specify a portrait or landscape orientation. The latter allows you to resize the printed contents of your sheet according to percentage based on its normal size.

To do these, follow the steps below:

  1. On the File menu, click Page Setup.

  2. Click on the Page tab.

  3. To set the orientation, click between Portrait or Landscape.

  4. To scale your document, choose any of the following:

    • Click Fit to to fit the document on one page and leave 1 page(s) wide by 1 tall.

    • Type a number in the Adjust to to reduce or enlarge the text or use the increment button.

  5. When finish with the setting, click OK.

    Click To expand

Setting up Headers and Footers

Headers are located at the top of each page, and footers are located at the bottom of each page. Headers and Footers often contain page numbers and document information such as the filename, author, company, and so on.

To add header and footer text to a sheet for printing, follow these steps:

  1. Click the Header/Footer tab in the Page Setup dialog box.

  2. Do any of the following:

    • Click the Header or Footer drop-down arrow and choose from a list of built-in headers or footers.

    • Click the Custom Header or Custom Footer button.

      Click To expand

  3. Type or use the buttons for the text you want in the Left Section, Center Section, and Right Section of the page. When you click a button, a code appears in the header or footer.

  4. Click OK when you are finish with the Header and Footer dialog box.

  5. Do any of the following:

    • Click OK to save these settings

    • Click Print Preview to see how the documents will look like when printed with a header and footer.

    • Click Print to print the document.

Click To expand

Insert and Remove a Page Break

Page Breaks indicates the location where the printed pages end and new pages start. If you want to print a worksheet with multiple pages, the other page might start in an awkward position in the middle of a section.

To set a page break, do the following:

  1. Click Print Preview.

  2. Click Close. The automatic page break displays as dotted lines on the worksheet.

    Click To expand

  3. Select a cell at any location where you want the page to break.

  4. On the Insert menu, click Page Break. Longer dashes show a set of page breaks.

  5. Preview your document and if you are satisfied with page break, click Print.

To remove a page break, click Remove Page Break on the Insert menu.

Using Page Break Preview

The Page Break Preview allows you to see page numbers as well as manually moves the page break lines.

Click To expand

  1. Click on the worksheet where you want to set a page break.

  2. On the View menu, click Page Break Preview. The view displays more of the worksheet, where the current page breaks are located, and page numbers.

  3. Start setting your sheet options:

    • If you want to print your sheet's gridlines, do not remove the check before the Gridlines, otherwise, uncheck it.

    • To print in black and white, check the Black and White box.

    • To print in draft quality, check the Draft Quality box.

    • To print row and column headings, check the Row and Column Headings box.

    • To set the print order, choose between Down, then Over and Over, then Down.

    • If you want to print comments, choose between At the end and As displayed on Sheet in the Comments list box.

  4. Click OK when done.

    Click To expand
Whiz Words
Start example
Print | Page Setup
Page Break | Margins
Print Area | Clear Print Area
Landscape | Portrait
Headers and Footers
End example

Lesson Summary

To set the Print Area, go to the File menu and then select the Print Area option. You can also select the Print Area through your Page Preview mode. Highlight your defined print area and right click. From the shortcut menu, choose the option set print area.

To set the margins, click the button from the Preview toolbar or you can click the Page Setup button and from its dialog box, set the margins.

To set your orientation, click the Page tab of your Page Setup dialog box, and choose Portrait or Landscape. Scale data to fit on a specific number of pages. In the Page sheet of the Page Setup dialog box, choose Fit to by selecting a number in the page(s).

To set headers and footers, open the Page Setup dialog box and then click the Header/Footer tab. You select predefined headers and footers from its drop-down list box. If you want to create your own header and footer, click on the Custom Header/Custom Footer button.

Page Breaks allows you to set the location where the printed pages end and new pages start.

Study Help
Start example

  1. How do you set your print area through Page Break Preview?

  2. What do you call the output on paper produced by the printer?

  3. What is the importance of viewing your worksheet before printing?

  4. What is the tab from the Print Dialog box that allows you to change or to set up the margin?

End example
Start Sidebar
Activity 1

  1. Create a worksheet that has a list of 15 students.

  2. The students name should be in the first column.

  3. The worksheet should contain a summary of 3 quizzes, grades for the assignment, hands-on, monthly test, quarterly test, and the final grades for the first quarter. You can refer to the illustration below.

    Click To expand

  4. Input data to the worksheet and compute for the final grades.

  5. Save your work as Final.xls at Excel-Activities folder.

  6. After completing the worksheet, click the Print Preview button.

  7. Change the orientation to landscape.

  8. Set the margins with the following specifications:

    Left Margin

    − 1.25″

    Top Margin

    − 1″

    Right Margin

    − 1″

    Bottom Margin

    − 1″

  9. Use "Grading Sheet SY 2001-2002" as your header.

  10. Make your gridlines invisible.

  11. Print your activity.

End Sidebar
Start Sidebar
Activity 2

  1. Open the file Sales Report.xls.

  2. Select the range A3:G9. Set the range as the Print Area.

  3. Preview the Print Area.

  4. Print the Print Area.

  5. Close the file and do not save.

End Sidebar


Chapter Review

  1. Identification. Identify the following. Write your answer on the space provided before each item.

    • ___1. It indicates the location where the printed pages end and new pages start.

    • ___2. It often contains page numbers and document information such as the filename, author, company, and so on.

    • ___3. These are blank spaces outside the printing area on a page. Using the Page Setup dialog box, you can set the margins and center the page.

    • ___4. It allows you to choose multiple ranges, and that each range Can be printed on a separate page each time you issue a print Command.

    • ___5. It is the output that the printer produces.

    • ___6. This option allows you to view your document before printing.

    • ___7. It is the shortcut for Print.

    • ___8. It allows you to resize the printed contents of your sheet according to percentage based from its normal size.

    • ___9. This button enlarges the view of the document while in the Print Preview.

    • ___10. It enables you to specify a portrait or landscape orientation.

  1. Labeling. Identify the functions of the following.

    1. Print Dialog Box

      Click To expand

    2. Header Dialog Box

      Click To expand


Chapter Seven: Charts and Objects

Lesson 1: Creating a Chart

A chart is a graphical representation of data in a worksheet. It is used to effectively display the result of your data or information. It is a way of visually presenting spreadsheet data, helping you see trends and relationships between data more clearly.

Excel gives you a variety of ways to graphically represent your worksheet data. You can choose from different two-dimensional and three-dimensional chart types, including area, column, pie, line and combination formats. You can choose from several variations in each category.

Click To expand

  1. Chart Area - includes the font style for chart text, background color and when cells around it alters, the chart moves and resizes. The chart area is actually the space of the whole chart.

  2. Legend - interprets the symbols in the chart.

  3. Legend Key - a small object in the legend box whose color or pattern corresponds to a series in the chart.

  4. Plot Area - the area where the axis and series data are drawn.

  5. Category Axis (X-axis) - This is where the categories of data are plotted.

  6. Value Axis (Y-axis) - It is composed of lines that defines the value of a series point in the plot area.

  7. Series - a group of data markers or series points that shows you the description of the values you have plotted.

  8. Series Point - describes a single piece of data for one category of a series.

  9. Value Axis Major Gridlines - allows you to see the measurement of a given series point in the chart.

Types of Charts

There are many types of charts; but we will only discuss the most common ones.

Line Chart uses data points to represent the values in the spreadsheet. Trends are easily illustrated since lines connect data points and show changes over time.

Click To expand

Column Chart uses rectangles to represent the relationship between two or more values in the spreadsheet. The height of each bar is proportional to its corresponding value.

Click To expand

Pie Chart uses a segmented circle to show the relationship of each value in a data range to the entire data range. The size of each block represents the percentage each value contributes to the total. Only one numerical data range (value (Y) series) may be used in a pie chart to illustrate the values represented by pie slices.

Click To expand

Creating a Chart

You can create a chart in two ways:

Quick Chart, the quickest way of creating a chart is to use the function key F11. To do this, follow these steps:

  1. Select the data you want to include in the chart.

  2. Press F11 or Alt+F1.

A chart will be created in a new sheet with a default name Chart 1. You can also modify this chart.

  • Chart Wizard it guides you through a step by step instructions in creating the basic formatted chart.

To create a chart using the Chart Wizard, follow these steps:

  1. Select the data you want to include in the chart.

  2. Do any of the following:

    • Click Insert, then click Chart.

    • Click the Chart Wizard button in the Standard toolbar.

    The Chart Wizard dialog box appears:

    Click To expand

    This is the step in the Chart Wizard where you will be prompted to choose the type of chart you want and its subtype. You can choose from the Standard or Custom type.

    To preview your chart with the chosen chart type, click on the Press and Hold to View Sample button.

    The Chart Wizard dialog box will display the appearance of your chosen chart.

  3. Click Next, when you already picked a chart type.

    This is the Chart Wizard Step 2 of 4 Chart Source Data. This is where you change the range of data to plot the orientation of your data series through the Data Range tab.

  4. Click Next.

    This is called the Chart Options Window where several sets of options are available under each tab. It has six tabs namely Titles, Axes, Gridlines, Legend, Data Labels, and Data Table.

    Click To expand

  5. Click Next or the Finish button.

    Click To expand

    It is the last step in the Chart Wizard. This step allows you to choose the location of the chart, whether you want it placed as a new sheet or as an object in a specific worksheet.

    Click To expand

Chart Options

The Chart Options window has six Tabs.

  1. Titles tab - This tab allows you to input the title of the chart, category (X-axis) and category (Y-axis).

    Click To expand

  2. Axes tab - If you don't want labels and values for the axes, clear the Category (X) Axis or Value (Y) Axis check box.

    Click To expand

  3. Gridlines - To help you identify data, you can add and remove horizontal and vertical lines in the Gridlines tab.

    Click To expand

  4. Legend - The legend tab gives you the options to show a legend or not. You can also specify the location of legend in the chart by clicking on the options under Placement.

    Click To expand

  5. Data Labels - These are descriptive texts or values that appear next to bar, line, or chart items.

    Click To expand

  6. Data Table - If you want to see the data with the chart, click on this tab. Click the Show Data Table check box to see the data.

    Click To expand

Modifying Charts

After creating a chart, you may want to make some changes to it. You can use the Chart toolbar, Chart Wizard or Chart menu to change the chart.

To modify a chart, follow these steps:

  1. Click the Chart Area to select the chart.

  2. Right–click on any of the toolbar and click Chart to display the Chart toolbar. The Chart toolbar appears.

    Click To expand

To use the Chart toolbar to modify the chart, click the Chart Objects drop-down list and select from the list.

To format the chosen object, click this [image] button from the Chart toolbar. This button displays different dialog boxes depending on the selected chart object.

Another way of modifying your chart is by double-clicking any of the chart objects. By doing this, a Format dialog box appears prompting you to make changes to your selected object in the chart.

The [image] Chart Type button allows you to select a different chart type.

The [image] Legend button enables you to toggle on/off the chart legend display.

The [image] Data Table button enables you to turn on or off the display of chart data contained in a grid attached to the bottom of the chart.

The [image] By Row button changes the data series to rows of data.

The [image] By Column changes the data series to columns of data.

To add new text to the chart, follow these steps.

  1. Select the chart area.

  2. Type the text you want to add then press Enter.

The new text appears in the middle of the chart, move the new text to the desired location when the mouse pointer changes into a four-headed arrow.

You can also use the Chart menu to make changes to your chart.

Clicking on any of these chart options will prompt the Chart Wizard thus allowing you to make modification to your chart.

Whiz Words
Start example
Chart | Chart Area | Chart Wizard
Legend | Series | Line Chart
Pie Chart | Column Chart | Value Axis
Category Axis | Plot Area | Gridlines
End example

Lesson Summary

In Excel, you can create and store a chart next to the worksheet; this is called an embedded worksheet. You can also create a chart in its own chart sheet.

You can use the Chart Wizard to create both an embedded chart and a chart sheet.

To format an embedded chart and a chart object, you must activate it by double-clicking anywhere inside the chart.

To display the Chart toolbar, click on the View menu, point to Toolbars and then click Chart or right-click on the toolbar and then press Chart.

Study Help
Start example

Directions: Identify the following:

  • ___1. It is actually the space of the whole chart.

  • ___2. It guides you through step by step instructions in creating basic formatted chart.

  • ___3. It uses a segmented circle to show the relationship of each value in a data range to the entire data range.

  • ___4. It uses rectangles to represent the relationship between two or more values in the spreadsheet. The height of each bar is proportional to its corresponding value.

  • ___5. It interprets the symbols in the chart.

  • ___6. The area where the axis and series data are drawn.

  • ___7. It uses data point to represent the values in the spreadsheet. Trends are easily illustrated since lines connect data points and show changes overtime.

  • ___8. It is a graphical representation of data in a worksheet.

  • ___9. A group of data markers or series points that shows you the description of the values you have plotted.

  • ___10. It allows you to see the measurement of a given series point in the chart.

End example
Start Sidebar
Activity 1

  1. Open a new worksheet.

  2. Type the following:

    Click To expand

  3. Create a line chart using the data above and embed it in the worksheet.

  4. Create a column chart using the data above and place it in a new sheet.

    Your output should look like this:

    Click To expand

  5. Format one of your charts with the following:

    • Chart Area: Add a blue pattern

    • Category Axis: Change the alignment to 90 degrees.

    • Title Change: the font to Allegro BT, font size 16.

  6. Save your work as Charts.xls in the Excel-Activities folder.

End Sidebar


Lesson 2: Inserting Objects

Aside from charts, you can also insert pictures, word art, organizational charts, and other items on your worksheet or chart sheets.

To insert pictures or clipart on your worksheet, do the following:

  1. Do any of the following:

    • On the Insert menu, point to Pictures, then click Clipart. The Insert Clipart task pane appears.

    • From the Drawing toolbar, click the [image] Insert Clipart button.

  2. In the Search text text box, type the clipart you want to search, then click Search.

    Click To expand

    After clicking Search, the task pane will display all clipart that refers to the one you have searched.

  3. Click any clipart on the list and it will automatically appear on the worksheet.

    Click To expand

You can also insert pictures from the file.

Click To expand

  1. Do any of the following:

    • Click Insert, point to Pictures, then click From File.

    • Click Insert Picture [image] button from the Drawing toolbar. The Insert Picture dialog box appears.

  2. Look for the picture you want to insert and then click Insert.

Move an Object

You can move the inserted clipart or picture by doing any of the following:

  1. Click on the object and drag it to the desired location.

  2. Cut the object, select a new location, and then click paste.

Delete an Object

To remove an object from your worksheet, do the following:

  1. Select the object you want to delete.

  2. From the Edit menu, click Delete or press Delete on the keyboard.

    Click To expand

If you want to delete all objects in your worksheet, do the following:

  1. On the Edit menu, click Go To or press Ctrl+G or F5. The Go To dialog box appears.

  2. Click the Special button. The Go To Special dialog box appears.

    Click To expand

  3. Click Objects from the selection, and then choose OK.

  4. Notice that all objects on your worksheet are selected.

    Note

    You can also use the Select Object [image] button under the drawing toolbar to select all the objects.
    Click To expand

  5. From the Edit menu, click Delete or press Delete on the keyboard

Resizing Objects

There are times that you want to change the size of the objects on your worksheet. To do this, follow the steps below:

  1. Select the object you want to resize.

  2. Position the mouse pointer to the border of the object until it changes its shape.

  3. Drag the pointer until the object reaches the desired size. Notice that a broken-dashed line surrounds the objects. While you drag the pointer it changes its size.

    Click To expand

Create and Modify Lines and Objects

In addition to clipart and pictures, you can also draw lines and shapes on your worksheet or chart.

To draw lines or shapes, do the following:

  1. If the Drawing toolbar is not displayed, click the Drawing [image] button in the Standard toolbar. Remember, this button can be toggled on or off.

  2. Choose the following tools in the Drawing toolbar:

Click To expand

Line - draws a line

Arrow - drag the arrow from tail to head

Rectangle - used to draw a rectangle. To create a square, hold down shift key as you drag a square.

Ellipse - used to create ellipse. To create a circle, hold down Shift key as you drag a circle.

Text Box - used to insert text in a movable box.

AutoShapes - click the drop-down arrow to choose a category you want. Drag to draw the shape.

Click To expand

You can change the style of the selected shape by applying any of the following options below. But before that, you have to make sure that the objects are selected.

Fill with Color - used to add color to your shape.

Clicking on the drop-down arrow beside the Fill with Color button allows you to choose any color from the Fill Color box.

Line Color - used to change the color of your line.

Clicking on the drop-down arrow beside the Line Color button allows you to choose any color from the Line Color box.

Font Color - allows you to change the color of your font.

Clicking on the drop-down arrow beside the Font Color button allows you to choose any color from the Font Color box.

Line Style - Click to select a thickness for a line or border.

Dash Style - Click to select a pattern for a line or border.

Arrow Style - Click to select different arrow style, one or two arrows for lines and arrows.

Click To expand

Shadow - Click to add a shadow to an object and direction for the shadows.

3-D - Click to turn the object into 3-D and choose direction of the third dimension.

Click To expand

Modifying Lines and Objects

After creating lines and objects, you can make changes by doing any of the following:

  • Drag the four-headed arrow to move the shape to the desired location.

  • Drag the handle pointing to the shape to change its size.

  • Press Delete to remove the objects.

  • Use the buttons on the drawing toolbar that allows you to change the styles of the lines and objects.

Whiz Words
Start example
Object | Clipart | Picture | Task Pane
Shadow | 3-D | Arrow | AutoShapes
End example

Lesson Summary

You can insert objects on your worksheet using the clipart or pictures from file. To move, delete, or resize an object you have to select it first.

You can also insert lines and shapes using the Drawing toolbar. If the Drawing toolbar is not visible, click on the Drawing button in the Standard toolbar. You can toggle this button on and off.

You can make changes to your drawing by applying different styles using the Drawing toolbar.

Study Help
Start example

Directions: Identify the following:

  • ___1. This bar allows you to create lines and shapes and make changes to it.

  • ___2. It is used to change the color of your line.

  • ___3. It is used to add color to your shapes.

  • ___4. A button that allows you to select the thickness for lines and borders.

  • ___5. You can click this button to add shadow to your drawing.

  • ___6. This button allows you to change the color of your font.

  • ___7. This button allows you to put arrow head to your line.

  • ___8. This button allows you to create a circle with the help of the Shift key.

  • ___9. This button allows you to create a square with the help of the Shift key.

  • ___10. Click this button to add a shadow to an object and direction for the shadows.

End example
Start Sidebar
Activity 1

  1. Open a new workbook.

  2. Insert the clipart named computer.

  3. Change the size of the object to 300 X 300. See the scale on the Name Box.

    Click To expand

  4. Insert the following lines and shapes.

    Click To expand

  5. Modify these objects, to come with the illustration above.

  6. Save your work as Objects.xls at Excel-Activities folder.

End Sidebar


Lesson 3: Printing Charts

Previewing your Chart

If your chart is embedded to your worksheet, you have to select it first before previewing. If not, this is how it will look like:

Click To expand

Notice that the Preview window shows the first page of the worksheet and a portion of the chart.

To view only the chart, you have to select it first and see what it will look like.

Only the selected chart will be viewed if you click the Print Preview button. After previewing your chart, changes can be made if needed.

If you have placed your chart on a new sheet, you donot have to select it to preview. Simply click on the Print Preview button and it will show how your chart looks just like the illustration on the right side.

Click To expand

While on the Preview Window, you can change the orientation, margins, add header and footer and set a new settings.

Printing your Chart

After previewing and making necessary changes, you can now print it.

Do any of the following:

  • If your chart is embedded to your worksheet, click the Print button and it will automatically print the chart.

  • If it is in the Preview Window, click Print. The Print dialog box appears.

    Click To expand

Set the Print options you want, then click OK when you are ready to print your chart.

Whiz Words
Start example

Chart Print Preview Print

End example

Lesson Summary

To preview an embedded chart, click it to select and then click the Preview button. If your chart is placed in a new sheet, just click on the Print Preview button.

You can print your chart while it is in the Print Preview window and it will display the Print dialog box which gives you options on how to print your chart. If you click Print on an imbedded chart, it will automatically print your chart.

Study Help
Start example

  1. How do we preview an embedded chart?

  2. How do we preview a chart that is placed in a new sheet?

  3. Enumerate the steps on how to print a chart whether it is embedded or not.

End example
Start Sidebar
Activity 1

  1. Open the file Charts.xls

  2. Print the two charts available on you workbook.

  3. Click close when done printing.

End Sidebar


Chapter Review

  1. Identification: Identify the following. Write your answer on the space provided before each item.

    • ___1. It is actually the space of the whole chart.

    • ___2. It interprets the symbols in the chart.

    • ___3. It is a graphical representation of data in a worksheet.

    • ___4. It uses data point to represent the values in the spreadsheet.

    • ___5. It uses a segmented circle to show the relationship of each value in a data range to the entire data range.

    • ___6. It uses rectangles to represent the relationship between two or more values in the spreadsheet.

    • ___7. It guides you through step by step instructions in creating basic formatted chart.

    • ___8. This button allows you to add colors to your shape.

    • ___9. It is a group of data markers or series points that shows you the description of the values you have plotted.

  2. Matching Type: Match the following buttons with their names. Write your answer on the space provided.

    Click To expand


Chapter Eight: Collaborating Workbooks

Lesson 1: Sending Files

In some instances you may want to send your workbook to a friend through e-mail or you want it published on the world wide web. In this lesson, you will learn how to send files through e-mail and publish it as a web page.

Send a Workbook via E-mail

Note

In this example we will use the Mail Recipient option.

To send a workbook through e-mail, you can use the Outlook's e-mail feature from within a cell.

To e-mail your workbook, do the following:

  1. Open the workbook or worksheet you want to send.

  2. Do one of the following:

    • Send the entire workbook as an attachment.

      1. On the File menu, point to Send To and then click Mail Recipient (as an Attachment).

        Click To expand

    • Send a Selection or a Worksheet as a message.

      Click To expand

  1. To send specific cells on a worksheet, select the cell you want to send.

  2. On the File menu, point to Send To and then click Mail Recipient.

  3. In the To and Cc text boxes, type in the e-mail address or click the To button and choose the address.

  4. If desired, edit the Subject text box.

  5. To send the worksheet, click the Send this sheet button.

You can also send your worksheet through e-mail by using the E-mail [image] button in the Standard toolbar. After clicking this button, the Outlook toolbar will appear and you will be prompted to type in the e-mail address of the person whom you want to send the worksheet.

Use Web Page Preview

If you want to convert your worksheet to a web page, you will probably want to see how the worksheet will look like before going through the procedure.

To preview the worksheet as a web page, do the following:

  1. Open the worksheet that you want to preview.

  2. From the File menu, click Web Page Preview.

    Internet Explorers opens and displays a temporary copy of the worksheet.

  3. If you want to save it as a web page, perform the process otherwise made some modifications first before doing so.

  4. When finished with the preview, click Close.

Saving Worksheet/Workbook as a Web Page

You can display your worksheet/workbook to the web and you can also make it interactive.

To save a workbook as a Web Page, follow these steps:

  1. Open the workbook that you want to save as a Web Page.

  2. From the File menu, choose Save as Web Page. The Save As dialog box appears.

    Click To expand

  3. In the Save options, click Entire Workbook.

    Click To expand

  4. Choose a location where you want to save your Web page.

    Click To expand

    • If you want your workbook to be interactive so that it can be edited while on the web, click the Add Interactivity check box.

  5. Assign a filename to your workbook.

    • If you want your workbook to have a title while in the Web, click the Change Title button.

    • Type the desired title and click OK.

  6. Click Publish. The Publish as Web Page dialog box will appear.

Under the Item to Publish category, choose an item or the entire workbook you want to be published as a web page.

  1. Click the Open to published web page browser check box.

  2. Click the Publish button.

After following the steps given above, you will see your worksheet in the Web Browser with some Excel functions.

You can view the different worksheets in the web by clicking the arrow beside the sheet title.

Click To expand

You can do common tasks with the interactive workbook/worksheet just like what you do to an ordinary workbook/worksheet.

By clicking the Commands and Options [image] button, a window will appear displaying some of Excel's features.

In this window, you can format the cell content, edit formulas, search, add, and delete worksheets.

Click To expand
Whiz Words
Start example

Web Page Email Interactive

End example

Lesson Summary

There are two ways to send a workbook through e-mail.

  1. Using the File menu.

  2. Using the e-mail button in the Standard toolbar.

To display your existing workbook/worksheet as a Web page, save it using the Save as Web Page option in the File menu. You can make it interactive while in the web by clicking the Add Interactivity check box. Workbook/Worksheet in the web is formatted like the ordinary workbook/worksheets.

Study Help
Start example

Directions: Answer the following completely.

  1. What is an e-mail?

  2. What are the different ways of sending e-mail?

  3. Enumerate the steps on how to save your workbook/worksheet as a web page.

End example
Start Sidebar
Activity 1

Directions: Follow the instructions carefully.

  • Open the file Top 20 Songs.xls.

  • Preview it as a web page.

  • Make some modifications if necessary.

  • Save this worksheet as a web page and make it interactive.

  • Assign a new name for your web. Use My Favorite Songs as a filename.

End Sidebar


Lesson 2: Linking Files

What Is a Hyperlink?

Hyperlink is an object such as graphic or colored underlined text that represents a link to another location in the same file or in a different file. It can usually be seen in a Web page and when you click it, it will lead you to another web page. Hyperlinks are one of the key elements of HTML (Hypertext Mark-up Language) documents. You can create hyperlinks in workbooks/worksheets by using either texts or graphics that link to charts, another workbook/worksheet, web pages or other files.

Inserting Hyperlinks

The easiest way to create a hyperlink is to type it in a cell. If you enter an e-mail address mjs@hotmail.com or a website (http://www.bluemountain.com) on your worksheet, it will automatically be formatted as a hyperlink.

To create Hyperlinks follow these steps:

  1. Select the cell or text where you want to insert a hyperlink.

  2. Do one of the following:

    • Click Insert Hyperlink button on the Standard toolbar.

    • Press Ctrl+K.

    • Click the Insert menu and choose Hyperlink.

    • Right-click and choose Hyperlink

      Click To expand

      When the Hyperlink Screen Tip dialog box appears, type the text that you want to appear when you hover over the hyperlink with the mouse pointer.

      Click To expand

      If you do not specify a Screen Tip, the path of the linked-to-file becomes the Screen Tip.

  3. In the Address box, type the file or web page you want to link to your worksheet.

    Click To expand

    If you do not know the path of the file you want to link, search in the Look in drop-down list box or make use of the Place Holders to search for a particular file, web page, e-mail address or you can even create a new document to be link on your worksheet.

  4. Click OK.

    Click To expand

    The hyperlink appears in your worksheet as underlined colored text. To go to the linked file, point to the hyperlink text and when the mouse pointer changes into a hand, click it.

To edit hyperlink text, follow these steps:

  1. Select the hyperlink text.

  2. Do any of the following:

    • Click Insert, then choose Hyperlink.

    • Press Ctrl+K.

    • Click the Insert Hyperlink button on the Standard toolbar.

    • Right–click and then choose Edit Hyperlink.

The Edit Hyperlink dialog box appears.

Click To expand

You can now make necessary changes on your hyperlink.

Remove Hyperlinks

To remove the hyperlink, do any of the following:

  • Click the Remove Hyperlink button from the Edit Hyperlink dialog box.

  • Point the mouse pointer over the hyperlink text, right–click, and then click the Remove Hyperlink option.

Whiz Words
Start example
Hyperlink |  |
 | HTML |
 |  | Ctrl + K
End example

Lesson Summary

A Hyperlink is a link that points to a file, a specific location in a file, or a Web Page on the internet or intranet.

To Insert a Hyperlink: Select the cell you want to use for the hyperlink and click the Insert Hyperlink button from the Standard toolbar or use other methods in displaying the Hyperlink dialog box. Select or type the file or Web address for the hyperlink's destination and click OK.

To Edit Hyperlink: Select the hyperlink text or object, and use any of the ways in displaying the Edit Hyperlink dialog box. Make the changes you want and click OK.

To Remove Hyperlink: Select the hyperlink text or object. From the Edit Hyperlink dialog box, click the Remove Hyperlink button and then click OK or right-click over the hyperlink and then click Remove Hyperlink.

Study Help
Start example

Directions: Answer the following.

  1. What is a Hyperlink?

  2. What does HTML mean?

  3. How do you remove a hyperlink?

  4. What is the shortcut key for displaying the Hyperlink dialog box?

  5. How can you edit a hyperlink?

End example
Start Sidebar
Activity 1

Directions: Follow the instructions carefully.

  1. Open a New Workbook.

  2. On cell A1 type Hyperlink Texts.

  3. On cell A3, type your e-mail address.

  4. On cell A4, type a sample of Web address.

  5. On cell A5, type the Information.

  6. Insert a hyperlink on cell A5 that will lead to the file Information.xls.

  7. On cell A6, type the text My Favorite Songs.

  8. Insert a hyperlink on cell A6 that will lead to the file My Favorite Songs.htm.

  9. Save the workbook as Hyperlinks on the Excel-Activities folder.

  10. Open the file Sales Report.xls.

  11. Copy its content to Sheet 2 of Hyperlinks.xls.

  12. Select the range A4:G21. Move it to A5:G22.

  13. Copy the data as shown in Sample 1 to A3:D23 of Sheet 3.

    Click To expand

  14. Add Another field named Total Sales.

  15. Get the data in Sheet 2 to compute for the Total sales. Use 3-D cell references in the formula.

  16. At cell A1, type Date.

  17. In cell B1, enter the formula the will display the current date including the time.

  18. Save your work.

  19. Select Sheet 1 and Sheet 3.

  20. Print it.

End Sidebar


Chapter Review

  1. Fill in the Blanks:

    1. Saving a Workbook as a Web Page

      Steps:

      1. Open the workbook that you want to save as ___.

      2. From the File menu, choose ___. The Save As dialog box appears.

      3. In the Save options, click ___.

      4. Choose a location where you want to save your web page.

        • If you want your workbook to be interactive so that it can be edited while on the web, click the ___ check box.

      5. Assign a filename to your workbook.

        • If you want your workbook to have a title while in the Web, click the ___ button.

        • Type the desired Title and click OK.

      6. Click Publish. The ___ dialog box will appear.

      7. Click ___ check box.

      8. Click the ___ button.

    2. Creating Hyperlinks

      1. Select the ___ where you want to insert a hyperlink.

      2. Do one of the following:

        • Click ___ button on the Standard toolbar.

        • Press ___

        • Click the Insert menu and choose ___.

        • ___ click and choose Hyperlink.

      3. In the ___ box, type the file or web page you want to link to your worksheet.

      4. Click ___.


Skills Projects

Project 1 Campaign Hot Buttons

Summary:

This activity asks students to work as staffers for a politician (played by the teacher). The students are assigned to design, administer, and analyze a simple poll on an important community issue; to develop a position statement based on poll results; and to persuasively present the candidate's stand on the issue.

Objectives:

  1. To sharpen understanding of the role of public opinion polling in the political process

  2. To develop data design, collection, and analysis skills using spreadsheet tools

Description:

For this activity, imagine you and your partner are key policy strategists for a local politician (played by your teacher). This politician has a hunch that water quality is an issue of great concern to citizens in your town, and that they might support a proposal to build small-scale water- purification plants at strategic locations.

What to do:

STEP 1 Brainstorming Polling Questions

  1. Your candidate believes that his/her water purification proposal would have three important benefits: the protection of sources of drinking water, the rehabilitation of wildlife habitants, and the creation of a swimming and recreation area on the outskirts of your town. Your poll should help determine how the public feels about these three goals, and what problems they might foresee with your candidate's proposal.

  2. The key is for you to design questions that can be analyzed statistically. That means asking questions that can be answered Yes or No.

  3. Provide a scale for answers, such as 1 to 5 with 1 being Strongly Agree and 5 being Strongly Disagree; or providing multiple choice answers.

STEP 2 Designing your Poll Form

  1. Create a basic spreadsheet document in Microsoft Excel that you can print out for conducting your poll and then enter your collected data into.

  2. Use the illustration on the next page as a guide in creating your poll form.

    Click To expand

  3. After creating the poll form, save and print it.

  4. Now, poll your classmates. Pencil in the information under each heading, including the numerical answer to each of your survey questions.

STEP3 Analyzing Your Data

  1. Open your worksheet. Enter and save your data.

  2. Get the average answer to your questions. You can place the average at the end of each column.

  3. To find the percentage represented by each answer, first find the total number of answers by having Excel count them. Select a cell before any column of numbers - for example, B19 - and type the formula =COUNT(B9:B18). On a separate area of your worksheet, set up a table that shows the frequency of each answer from 1 to 5 for Question 1, and have Excel divide each frequency by your total number of answers.

  4. Create a graph of your percentages by selecting your percentage cells, choose ChartWizard button on the toolbar.

    Click To expand


Project 2 Making Ends Meet

Summary:

In this activity, students create a monthly budget, showing income earned from an imaginary job versus estimated monthly expenses. They then encounter unexpected monthly expenses and must adjust their budget to compensate for those expenses.

Objectives:

  1. To prepare a monthly budget using Microsoft Excel

  2. To estimate a monthly food bill based on a weekly food menu

What to do:

STEP 1 Prepare Budget Worksheet

  1. Open MS Excel.

  2. Create a monthly budget template, following the example below.

    Click To expand

  3. Be sure to include the following expense categories; Rental, Utilities, Transportation, Food, Medical Expenses, Clothing, Leisure Activities, and Miscellaneous.

STEP 2 Estimate Expenses

  1. Consult your local newspaper or real state magazines to get an idea of how much it costs to rent a house or an apartment in an area you would like to live.

  2. Type in the monthly rent and the estimated cost of utilities (gas and electricity) in your worksheet.

  3. Assume the cost of transportation (car payment, insurance, gas) to be $250.00.

  4. For the other items in your budget (other than food), estimate your monthly expense and type in that amount in the appropriate cell.

STEP 3 Create a Food Budget

  1. To determine your monthly food budget, open a new spreadsheet.

  2. Design a Menu table similar to the example below.

    Click To expand

  3. Use this table to write down what you plan to eat and drink at each meal for one week. Be sure to include snacks.

  4. Figure out what you must buy to produce this menu. List down the items needed according to categories such as fruit, meat, dairy, and the quantity of each item in a spreadsheet.

  5. Print out a copy of your food list.

  6. Visit a local supermarket and list the prices of the items on your list according to quantity.

  7. Add the total cost of your weekly budget. Multiply by four and a half weeks to get an estimate of your monthly food budget. Type this number into the food category of your monthly budget.

STEP 4 Evaluate Your Budget

Once you have typed in all your monthly expenses, your worksheet will show whether or not you are "making ends meet". Do your expenses exceed your income? If so, you must decide where to cut back so that your expenses do not exceed your income.


Project 3 Weights and Measures

Summary:

Students learn best when they make a personal connection to the subject at hand. In this activity, they become their own raw data to analyze, compare, and convert.

Objectives:

  1. To master the gathering, averaging, and graphing of data

  2. To practice conversion to metric measures

  3. To gain fluency in use of technology tools for math operation

What to do:

Teachers' Preparation

  1. Open MS Excel.

  2. Create these headings: Name, Gender, Weight, Height.

  3. Enter each of your student's names on a separate row under Name.

  4. Save as Class Data.

  5. Position the scale and tape measure near the computer on which this spreadsheet is loaded.

  6. Gather the students around this computer, and explain that each person will work with a partner to weigh and measure themselves, then enter the results in the Class Data. When all the data is complete, each student will get a copy of the Class Data workbook to analyze, compare, graph, and convert, following the steps in the student activity.

    Click To expand

  7. After students have completed Step 1, make a copy of the filled out worksheet for each student, or save it in a common folder on your network so each students can copy it for use on his or her own workstation.

What to do: Student's Activity

STEP 1 Measure It

  1. Go to the computer/weights and measures station your teacher has set up.

  2. Find your name in the Excel workbook your teacher created. Under Gender, type F if you are a girl or M if you are a boy.

  3. Weigh yourself on the scale, and enter your weight in the Weight column next to your name.

  4. Choose a partner, and have him or her use the tape measure to find your height in inches. Enter this number in your Height column.

  5. Let your partner find and enter his or her own weight. Then measure his or her height and enter this figure. Save the workbook.

STEP 2 Convert It

  1. When everyone has entered weight and height data, you will get your own copy of the class Excel workbook.

    Click To expand

  2. Go to cell E1 and type the heading, Height/Feet. In cell E2, create a formula to convert the first student's height in inches to height in feet by typing =(D2/12), which means "divide the figure in cell D2 by 12". Press Enter, and double-click the figure you see. You can now Autofill this same formula to convert all the heights to feet by clicking in cell E2, pointing to the "handle" in its bottom right corner, and dragging to the end of column E. To simplify your results, click at the top of column E, go to Cells, click on the Number tab, choose Number from the list, and type 1 for Decimal Places.

  3. Now add three more columns: Weight in Grams, Height in Centimeters, and Height in Meters. Use the information in the Metric Measures box to create formulas that will convert each student's measurements into these metric numbers.

  4. Save and print your work. Pair up with classmates to share and check results.

STEP 3 Average It

  1. Get the average height and weight in your class.

  2. In your class data workbook, add a row at the bottom of the class list. In the Name column, type Average.

  3. In the Weight cell that shows weight in pounds, create a formula that will find the average weight of all the students in your class.

  4. Check your results to make sure your formula is correct. If it is correct, Autofill it across the row. Save and check your work.

METRIC MEASURES

1 inch = 2.54 centimeters

1 foot = 1/3 of a meter, or .0305 meters

1 pound = 453.592 grams

STEP 4 Break It Down!

  1. Figure out separate averages just for boys and for girls.

  2. Select all your cells, including your headings. Filter it.

  3. In the Gender column, click in the small box and a menu will appear. Select F.

  4. Now, the only data you will see is for the girls. Select all the cells, go to Edit, and choose copy.

  5. Go to Sheet 2. Make A1 the active cell and click paste.

  6. Go back to Sheet 1. Click All from the filter box in the Gender column.

  7. Repeat Step 3. This time, select M. Paste the boy's data in Sheet 3.

  8. In Sheet 2, add a row labeled Average, find the girls' averages.

  9. Do the same for the boys in Sheet 3. Save and check your work.

STEP 5 Graph It

  1. To graph all the height in your class - including the average - select the Name column, then press Ctrl and select the Height column. Go the Insert and choose Chart.

  2. Choose the desired chart type you want.

  3. Now, try to create other charts. How about one that compares girl's weights to boys' weights? A chart of all class heights in centimeters? One that graph weight and height together?


Index

Numbers

3-D, 142
3-D Reference, see Worksheets


Index_A

Team LiB

Index

A

Absolute Reference, see Cell Reference
Active Cell, 3-4, 8
Active Cell Indicator, 3-4, 8
Address, Cell, see Cell Address
Aligning Cell Content, see Cell Content
Application Window, 6, 9
Arguments, see Financial Functions
Arrow, 141
Ask a Question Box, 7
Auto fill, 25-26, 36
Auto fill options, 97
AutoFilter, 108
Filtering List using AutoFilter, 108
Autoformats
Apply or Remove AutoFormats, 50
AutoShapes, 141
Autosum, 71, 73, 79-80
Extended AutoSum Functionality, 75
Use AutoSum in one Cell, 73
Use AutoSum in a selected Range, 74
Using AutoSum, 73
Average function, see Functions
Axes tab, see Charts


Index_B

Team LiB

Index

B

Bricklin, Dan, 3
Bold text, see Formatting
Borders
Applying, 49
Adding Borders, 49
Removing Borders, 50
Buttons
AutoFill Options button, 97
Select All button, 54
Insert Options button, 97
Paste Options button, 55, 97
Trace Error button, 97


Index_C

Team LiB

Index

C

Cell, 3-4
Formatting, 48
Inserting and Deleting Cells, 32-33
Shift Cells Down, 33
Shift Cells Right, 33
Merging and Splitting, 57
Moving Selected Cells, 28-89
Working in using the Formula bar, 66-68
Cell Address, 4, 20, 34
Cell Content, 4
Aligning Cell Content, 56
Aligning Data at the Top, Center, or Bottom of a Cell, 57
Clear Cell Contents and Formats, 31
Editing Cell Content, 27
Formatting, see Formatting Cells
Indenting Text, 58
Merge and Center button, 57
Rotating text, 57
Selecting Non-adjacent Cells, 32, 36
Cell Reference, 34
Absolute, 71
Mixed, 71
Relative, 71
Character Fonts, Size, Styles and Colors, see Formatting Characters
Charts, 130
Axes tab, 134
Category Axis, see X-axis
Chart Wizard, 132
Chart Wizard Dialog box, 132-133
Column, 131
Creating, 130-131
Data labels, 134
Data Table, 134
Gridlines, 134
Legend, 134
Legend Key, 134
Line, 131
Modifying, 135
Options, 133
Pie, 131
Plot Area, 130
Printing, see Printing
Quick Chart, 131
Series, 131
Series point, 131
Titles tab, 133
Types, 131
Value Axis, 131
Value Axis Major Gridlines, see Gridlines
X-axis, 130, 134
Y-axis, 130, 134
Clear Cell Contents and Formats, see Cell Content
Clear Command, 31
Clipart, see Objects
Clipboard, 28-29
Closing Worksheet window, 9
Color-code worksheet tabs, 96
Column, 3-4
Adjusting Columns, 53
Adjusting Column Width, 53-54
Deleting Rows or Columns, 92
Freeze/Unfreeze, 94
Hide/Unhide, 93
Inserting, 32-33, 92
Modify Column Widths, 53
Moving, 28-31
Selecting, 32
Comma Style, see Formatting Numbers
Comments, 106-108
Adding Comments, 106
Creating and Responding to Discussion Comments, 107-108
Deleting Comments, 107
Editing Comments, 107
Viewing Comments, 107
Consolidate data using 3-D References, see Worksheets
Copy, 29-30
Currency Style, see Formatting Numbers
Cut or Move, Copy, and Paste, 28-29, 36
Cut, 29-30, 36 see also Paste, 29-30


Index_D

Team LiB

Index

D

Data
Editing Data, 27
Entering Data, 23
Date Formats, 25
Enter Data, 25
Entering (Text) Labels, 23
Entering Date and Time, 25
Entering Functions, 77
Entering Values, 24
Database, 4
Data Labels, see Charts
Data Table, see Charts
Dates, see Data - Entering Date and Time
Date Function, see Functions
Decimal Places, Adjusting, 4
Delete Cells, 33
Deleting Columns, see Columns
Deleting Rows, see Rows
Delete key, 35
Dictation, see Language Bar
Discussion Comments, see Comments
Draw Line or Shapes, 140-141


Index_E

Team LiB

Index

E

Electronic Spreadsheet, 3-4
Cell, 4
Column, 4
Content, 4
Database, 4
Elements of, 3
Range, 4
Row, 4
Workbook, 4
Worksheet, 4
Exiting MS Excel, 8


Index_F

Team LiB

Index

F

Find, 33-34
Find Formats, 34
Find Text, 34
Folders
Create a Folder, 11, 13-14
New Folder, 14
Footers, see Page Setup
Formatting
Cells, 48
Cell Content, 47
Charts, 135
Characters with the Menu, 46
Color List, 47
Font List, 46
Font Style List, 46
Size List, 46
Using the Toolbar, 46
Date and Time, 48
Pre-defined Date and Time Formats, 48
Dialog box, 48
Numbers
Number Formats, 47
Comma Style, 47
Current Style, 47
Designing Custom Number formats, 48
Percent Style, 47
Using the toolbar, 47
Worksheet Tabs, 96
Format Painter, 50-51
Formulas, 66
Creating and Editing Formulas using the Formula Bar, 7, 66-67
Edit Formulas, 70
Enter a Range within a Formula by Dragging, 69
Marquee, 69
Formula Palette, 78
Frankston, Bob, 3
Freeze and Unfreeze Rows and Columns, 94
Freeze Panes, 94
Functions, 77-80
Arguments, 85
FV, 85
Nper, 85
PMT, 85
PV, 85
Rate, 85
Autosum, 79-80, see also Autosum
AVERAGE function, 80-81
Basic, 79
Date Functions, 84-85
Entering Functions, 77
Financial functions, 85
FV, 85
PMT, 85-86
Insert Function, 77
Logical Functions, 86
IF Function, 86-87
Nested Ifs, 87
Shortcut Keys, 47
Statistical functions, 82
Maximum, 82
Median, 82
Minimum, 82
SUM function, 79-80
Future Value (FV), see Financial Functions
FV, see Financial Functions


Index_G

Team LiB

Index

G

Go To command, 35, 101
Go To a Specific Cell, 34
Graphics
Creating, Modifying and Positioning, 138-143
Gridlines, see Chart Options


Index_H

Team LiB

Index

H

Handwriting, see Language Bar
Hardcopy, see Printing
Headers, see Page Setup
Hiding Rows or Columns, 93
Unhide rows or columns, 93
Hyperlinks, 154-157
Inserting Hyperlinks, 154-155, 157
Edit Hyperlinks, 156, 157
Insert Hyperlink Dialog box, 155, 157
Remove Hyperlinks, 156, 157


Index_I-K

Team LiB

Index

I-K

Indents, 57
Increase or Decrease Indented Text, 57
Insert Options, 97
Introducing Excel, 6


Index_L

Team LiB

Index

L

Labels, 23, 26
Entering, see Data – Entering Labels
Language Bar, 37
Configure Microphone, 38-39
Input Method Editor (IME), 37
On-screen Keyboard, 41
Using Handwriting, 39
Voice Command, 37
Write anywhere on the screen, 40
Writing Pad, 40
Legend, see Chart Options
Line, Draw, 140-141
Line Objects, 140-143
Create and Modify, 140-143
Linking Files, 154
Locate and Open an Existing Workbook, 12


Index_M

Team LiB

Index

M

Managing Files, 10
Margins, see Page Setup
Menu Bar, 7
Microphone configuration, see Language Bar
Mixed Reference, see Cell Reference
Mouse Pointer Different forms of mouse pointer, 21


Index_N

Team LiB

Index

N

Name box, 7
Nper, see Financial functions
Numbers
Applying Number Formats, see Formatting Numbers
Designing Custom Number formats, 48


Index_O

Team LiB

Index

O

Objects
Delete an Object, 139-140
Inserting Objects, 138
Move an Object, 139
Resizing Objects, 140
Office Clipboard, see Clipboard
Open Dialog box, 12
Opening a Workbook from a toolbar created for Workbook
Storage, 12
Options Button, 54
On-screen Keyboard, see Language Bar


Index_P-Q

Team LiB

Index

P-Q

Page Breaks, 121-122, 123
Insert and Remove a Page Break, 121
Remove Page Break, 122
Preview, 122
Page Setup, 118-120
Centering and Margins, 119, 123
Headers and Footers, 120, 123
Page Setup Dialog box, 118
Page Orientation and Scaling, Changing 119, 123
Print Area, see Printing
Parts of the MS Excel Application Window 7
Ask a Question box, 7
Drawing toolbar, 7
Formatting toolbar, 7
Formula bar, 7
Menu bar, 7
Name Box, 7
Standard toolbar, 7
Status bar, 7
Task pane, 7
Title bar, 7
Toolbars, 7
Parts of the Worksheet Window, 8
Active Cell, 8
Active Cell Indicator, 8
Column, 8
Row, 8
Scroll bars, 8
Sheet Tab, 8
Passwords
Save a file with, see Saving
Paste, 29-30
Paste Options, 29, 97
Paste Special, 30-31, 36
Patterns
Adding Shading or Patterns, 50
Shading, 50
Percent Style, see Formatting Numbers
Pictures, see Objects
PMT, see Financial functions
Previewing your Chart, 144-145
Present Value (PV), see Financial functions
PV, Financial functions
Preview
Document, 114-115
Page Break, see Page Breaks
Print Preview Window, 115
Preview and Print Non-adjacent Selections of Worksheet in a workbook, 116
Print, 114
Printing
Charts, 144-145
Document, 115
Hardcopy, 115
Print Area, 118, 123
Set and Clear Print Area, 118, 123
Print the Print Area, 118
Print Dialog box, 117
Print Options, 117
Print a Selection, 116
Print Non-adjacent Selections of Worksheet in a workbook, 116
Workbook, 115
Worksheet, 115
Work, 114
Print Preview, 114
Publish a Web Page, see Workbooks
PV, see Financial functions


Index_R

Team LiB

Index

R

Range, 4, 31
Redo, 28, 35
Relative Reference, see Cell Reference Rename a Worksheet, see Worksheets
Replace, 33-34
Format, 34
Text, 34
Rotate Text in a cell, 57
Rows, 3-4
Adjusting Rows and Columns, 53
Adjusting Row Height, 54
Deleting Rows, 92
Freeze/Unfreeze, 94
Hide/Unhide, 93
Inserting Rows, 32-33, 92
Modify Row Height, 53
Moving Rows, 28-31
Selecting, 32


Index_S

Team LiB

Index

S

Saving
Save As, 10-11
Save a Copy of a Workbook, 11
Saving Files for the first time, 10
Save a file with a password, 12
Save Options, 12
Saving Worksheet/Workbook as a Web Page, 151-152
Scaling
Changing Page Orientation and Scaling, 19
Scroll Bars, 8
Search Command, 13, 138
Find a workbook using, 13
Selecting Entire Column or Row, 32
Selecting non-Adjacent Cells, 32
Selecting Range of Cells, 31
Range, 31
Send a selection or a worksheet as a message, 150-151
Sending Files, 150
Send a Workbook via E-mail, 150
Send workbook as an attachment, 150
Series
Working with, 25
AutoFill, 25
Fill Handle, 25-26
Set Page Margins and Centering, 119
Margins, 119
Shading, 50
Adding, 50
Applying Cell Borders and Shading, 49
Shadow, 142
Share Data among worksheets using 3D Reference in Formulas, 104
Shapes, Draw, 140-141
Sheet Tabs, 8
Shortcut Keys, 47
Smart Tags, 96, 100
Control Automatic Change with Smart Tags, 96
Spelling
Check Spelling, 98-99, 100
Spelling Dialog box, 98
Splitting Worksheet, 94
Remove a Split Window, 95
Spreadsheet, 2
Spreadsheet Program, 2
Starting Excel, 6
Status Bar, 7
Story behind Spreadsheet, 3
Styles
Applying Styles, 60
Creating a New Style, 60
Deleting a Style from the Style List, 61
Remove a Style from Selected Cells, 61
Style Dialog box, 60
Sum function, see Functions
Switch between Worksheets in a Workbook, 101


Index_T

Team LiB

Index

T

Task Pane, 7
Templates, 14
Text Box, 141
Title Bar, 7
Titles tab, see Chart Options
Toolbars, 7
Drawing, 7
Formatting, 7
Standard, 7
Tools menu, 12
Trace Error button, 97
Type of Spreadsheet, 2
Electronic, 2
Manual, 2
Types of charts, 131
Column Chart, 131
Line Chart, 131
Pie Chart, 131


Index_U

Team LiB

Index

U

Undo, 28, 35


Index_V

Team LiB

Index

V

Values, 24, 26
Views, 12
VisiCalc, 3
Voice Command, see Language Bar


Index_W-Y

Team LiB

Index

W-Y

Web Discussion, Reply, Edit, and Delete, 108
Web Discussion toolbar, 108
Writing Pad, see Language Bar
Workbooks, 4
Interactivity, 151-152
Collaborating, 149-159
Create a New Workbook using Templates, 14
Find a workbook, see Search Command
Locate and Open, 12
Navigating in, 20, 101
Publish as a Web Page, 152
Sending, 150
Selection or Worksheet, 150
Saving as a Web Page, 11
Web Page Preview, 151
Worksheets, 4
3-D Reference, Consolidate using, 104
Copying, 102-103
Delete, 102
Formatting, 46-50
Insert, 102
Link, 104
Modifying Worksheets, 96-100
Moving, 102-103
Navigating in, 20-21
Remove a split, 95
Rename, 101
Splitting, 94
Window, 8-9


Index_Z

Team LiB

Index

Z

Zoom Setting, Changing 98, 100


List of Figures

Chapter One: Introduction to Spreadsheets

Figure 1.1: The Electronic Spreadsheet
Figure 1.2: The MS Excel Program inside Program menu
Figure 1.3: The MS Excel Window
Figure 1.4: The Application Window
Figure 1.5: The Worksheet Application Window


List of Examples

Chapter One: Introduction to Spreadsheets

Whiz Words
Study Help
Whiz Words
Study Help
Whiz Words
Study Help

Chapter Two: Data and Cells

Whiz Words
Study Help
Whiz Words
Study Help
Whiz Words
Study Help
Whiz Words
Study Help

Chapter Three: Formatting Worksheets

Whiz Words
Study Help
Whiz Words
Study Help
Whiz Words
Study Help
Whiz Words
Study Help

Chapter Four: Formulas and Functions

Whiz Words
Study Help
Whiz Words
Study Help
Whiz Words
Study Help
Whiz Words
Study Help

Chapter Five: Working with Worksheet and Workbooks

Whiz Words
Study Help
Whiz Words
Study Help
Whiz Words
Study Help
Whiz Words
Study Help

Chapter Six: Page Setup and Printing

Whiz Words
Study Help
Whiz Words
Study Help

Chapter Seven: Charts and Objects

Whiz Words
Study Help
Whiz Words
Study Help
Whiz Words
Study Help

Chapter Eight: Collaborating Workbooks

Whiz Words
Study Help
Whiz Words
Study Help


List of Sidebars

Chapter One: Introduction to Spreadsheets

Activity 1
Activity 2
Activity 1
Exercise
Activity

Chapter Two: Data and Cells

Exercise
Exercise 1
Exercise 2
Activity 1
Activity 1
Activity 1

Chapter Three: Formatting Worksheets

Activity 1
Activity 2
Activity 1
Activity 1

Chapter Four: Formulas and Functions

Exercise 1
Exercise 2
Activity 1
Exercise 1
Exercise 2
Activity 1
Exercise 1
Exercise 2
Exercise 3
Activity 1
Exercise 1
Activity 1

Chapter Five: Working with Worksheet and Workbooks

Activity 1
Activity 1
Activity 1
Activity 1

Chapter Six: Page Setup and Printing

Activity 1
Activity 1
Activity 2

Chapter Seven: Charts and Objects

Activity 1
Activity 1
Activity 1

Chapter Eight: Collaborating Workbooks

Activity 1
Activity 1

Team LiB
Previous Section