Computer Education Chapter No 9

(Microsoft Excel)

Over View

Microsoft Excel is a spreadsheet application used to process business and financial transactions of various kinds. It is an important software product as a Windows application. It is an equivalent of paper ledger sheet consisting of grid made from columns and rows. It is an environment that can make number manipulation easy and somewhat painless. To carry its various tasks, Microsoft Excel organizes each one of its workbooks into worksheets. You can have just one worksheet in a workbook or you can have a lot of worksheets in a workbook. Versions 97 and 2000 have only slight differences, but if you have used version 2000, you will have a very smooth transition. If you are new to this kind of spreadsheet application and are using 2000, you will feel comfortable in no time.

The nice thing about using a computer and spreadsheet is that you can experiment with numbers without having to RE-DO all the calculations. Once you have the formulas setup, spreadsheets are instantly updated if one of the entries is changed. NO erasers! NO new formulas! NO calculators! So let's get started digging into what makes a spreadsheet work. Spreadsheets are made up of

a.       Columns.    In a spreadsheet the vertical space that is going up and down the window is called Column. Letters (A, B, C etc) are used to designate each COLUMN'S location.

b.       Rows.         In a spreadsheet the ROW is defined as the horizontal space that is going across the window. Numbers (1, 2 3 etc) are used to designate each ROW'S location.

c.       Cell.            In a spreadsheet the CELL is defined as the space where a specified row and column intersect. In other words we can say the intersection of column and row is called Cell. Each CELL is assigned a name according to its COLUMN letter and ROW number.

Types of Data

a.       Labels (text with no numerical value).

b.       Constants (just a number -- constant value).

c.       Formulas (a mathematical equation used to calculate).

Data types	Examples	Descriptions
LABEL	Name or Wage or Days	Any thing that is just text
CONSTANT	5 or 3.75 or -7.4	Any number
FORMULA	=5+3 or = 8*5+3	Math equation

Note: ALL formulas MUST begin with an equal sign (=).

Labels

Labels are text entries. They do not have a value associated with them. We typically use labels to identify what we are talking about. Example:
a.       Computer ledger.
b.       Car loan.
c.       Interest.
d.       Of payments.
e.       Monthly Pmt.

Constants

Constants are entries that have a specific fixed value. If someone asks you how old you are, you would answer with a specific answer. Sure, other people will have different answers, but it is a fixed value for each person. Example:
a.       $12,000.
b.       9.6%.
c.       60.

As you can see from these examples there may be different types of numbers. Sometimes constants are referring to dollars, sometimes referring to percentages, and other times referring to a number of items (in this case 60 months). These are typed into the computer with just the numbers and are changed to display their type of number by formatting

Formulas

Formulas are entries that have an equation that calculates the value to display.  This equation will be updated upon the change or entry of any data that is referenced in the equation. Formulas are mathematical equations.

Formulas or Functions MUST BEGIN with an equal sign (=).

There are many functions built into many spreadsheets. One of the first ones that we are going to discuss is the Average function. The average function finds the average of the specified data. (Simplifies adding all of the indicated cells together and dividing by the total number of cells.) The syntax is as follows.

=Average (first value, second value, etc.)

Text fields and blank entries are not included in the calculations of the Average Function.

Let’s use the table here for the discussion that follows: We will look at several different specific examples that show how the average function can be used!

A 1 25 2 50 3 75 4 100 5

Example	Cells to average	Answer
=Average (A1:A4)	A1, A2, A3, A4	62.5
=Average (A1:A4, 300)	A1, A2, A3, A4 and 300	110
=Average (A1:A5)	A1, A2, A3, A4, A5	62.5
=Average (A1:A2, A4)	A1, A2, A4	58.33

The next function we will discuss is Max (which stand for Maximum). This will return the largest (max) value in the selected range of cells.

Blank entries are not included in the calculations of the Max Function.

Text entries are not included in the calculations of the Max Function.

Let’s use the table here for the discussion that follows. We will look at several different specific examples that show how the Max functions can be used!

A 1 10 2 20 3 30 4 Test 5

Example of Max	Cells to look at	Ans. Max
=Max (A1:A4)	A1, A2, A3, A4	30
=Max (A1:A4, 100)	A1, A2, A3, A4 and 100	100
=Max (A1, A3)	A1, A3	30
=Max (A1, A5)	A1, A5	10

The next function we will discuss is Min (which stands for minimum). This will return the smallest (Min) value in the selected range of cells.

Blank entries are not included in the calculations of the Min Function.

Text entries are not included in the calculations of the Min Function.

Let’s use the table here for the discussion that follows. We will look at several different specific examples that show how the min functions can be used!

A 1 10 2 20 3 30 4 Test 5

Example of min	Cells to look at	Ans. Min
=Min (A1:A4)	A1, A2, A3, A4	10
=Min (A2:A3, 100)	A2, A3 and 100	20
=Min (A1, A3)	A1, A3	10
=Min (A1, A5)	A1, A5 (displays the smallest number)	10

The next function we will discuss is Count. This will return the number of entries (actually counts each cell that contains number data) in the selected range of cells.

Blank entries are not counted.

Text entries are NOT counted.

Let’s use the table here for the discussion that follows. We will look at several different specific examples that show how the Count functions can be used!

A 1 10 2 20 3 30 4 Test 5

Example of Count	Cells to look at	Answer
=Count (A1:A3)	A1, A2, A3	3
=Count (A1:A3, 100)	A1, A2, A3 and 100	4
=Count (A1, A3)	A1, A3	2
=Count (A1, A4)	A1, A4	1
=Count (A1, A5)	A1, A5	1

The next function we will discuss is Count A. This will return the number of entries (actually counts each cell that contains number data OR text data) in the selected range of cells.

Blank entries are not counted.

Text entries ARE Counted.

Let’s use the table here for the discussion that follows. We will look at several different specific examples that show how the Count A functions can be used!

A 1 10 2 20 3 30 4 Test 5

Example of Count A	Cells to look at	Answer
=Count A (A1:A3)	A1, A2, A3	3
=Count A (A1:A3, 100)	A1, A2, A3 and 100	4
=Count A (A1, A3)	A1, A3	2
=Count A (A1, A4)	A1, A4	2
=Count A (A1, A5)	A1, A5	1

The next function we will discuss is IF. The IF function will check the logical condition of a statement and return one value if true and a different value if false. The syntax is

=IF (condition, value-if-true, value-if-false)

Value returned may be either a number or text

If value returned is text, it must be in quotes

Let’s use the table here for the discussion that follows. We will look at several different specific examples that show how the IF functions can be used!

A B 1 Price Over a dollar? 2 $.95 No 3 $1.37 Yes 4 Comparing # Returning # 5 14000 0.08 </TD< tr> 6 8453 0.05

Example of IF typed into column B	Compares	Answer
=IF (A2>1,"Yes","No")	Is (.95 > 1)	No
=IF (A3>1, "Yes", "No")	Is (1.37 > 1)	Yes
=IF (A5>10000, .08, .05)	Is (14000 > 10000)	.08
=IF (A6>10000, .08, .05)	Is (8453 > 10000)	.05

Changing the Zoom Settings

The zoom combo box on the Standard toolbar helps to increase or decrease the view of the interface. Although it doesn't affect the actual display of the characters sizes or cells contents, using the zoom setting can make the worksheet a little easier to read. To change the zoom setting, you can click the arrow of the Zoom combo box and select one of the values in the list. On the other hand, you can click the zoom value itself, type an integer (natural number) like 128, and press Enter.

Control the Interface’s Zoom Settings
a.       On the Standard toolbar, click the arrow of the Zoom combo box and select 200%. Notice that the worksheet displays with everything big now.
b.       Click the 200% number on the Zoom combo box, type 145, and press Enter. Now the display is smaller than the previous setting.
c.       Press Alt, v, z. That will call the Zoom dialog box
d.       Click the text box right to Custom, delete its content, type 68, and press Enter. Now the display is even smaller.
e.       On the main menu, click View -> Zoom...
f.        Click the 100% radio button and click OK. 
A Microsoft Excel workbook gets saved like any traditional Windows application file. Two issues are important here. Whenever you decide to save a file for the first time, you need to provide a file name and a location. The file name helps the computer identify that particular file and registers it in the computer’s database (the Registry).

A file name can consist of up to 255 characters; you can include spaces and dashes in a name. Although there are many characters you can use in a name (such as exclamation points, etc), try to avoid from fancy names. Give your workbook a name that is easily recognizable, a little explicit. For example such names as Time Sheets, Employee's Time Sheets, and Global EX First Invoice are explicit enough. Like any file of the Microsoft Windows operating systems, a Microsoft Excel file has an extension, which is .xls but you don't have to type it in the name.

The second important piece of information you should pay attention to when saving you workbook is the location. The location is the folder where the file will be saved. By default, Microsoft Excel saves its workbooks in the C:\My Documents folder. You can change that very easily in the Save As dialog box. Just click the arrow of the Save In combo box and select the folder you want.

Microsoft Excel allows you to save its files in a type of your choice. To save a workbook in another format than the default Microsoft Excel workbook, from the Save As dialog box, click the arrow of the Save As Type combo box and select a format of your choice. There are other things you can do in the Save As dialog box, we will address them as we move along.

To save a file for the first time, you can click File on the main menu, then click Save (if the file has not been saved before, the File -> Save menu will call the Save As dialog box). You can also click the Save button on the Standard toolbar. You can as well press Ctrl + S. Other alternatives include pressing F12, Alt + F2, or Shift + F12.

Using the Open Dialog
a.       On the main menu, click File -> Open...
b.       In the Open dialog box, click the arrow of the Look In combo box, select (C:); the (C:) represents your hard drive.
c.       Double-click My Documents. That puts the My Documents folder in the Look In combo box.
d.       Double-click MS Excel Exercises. The Look In combo box now displays MS Excel Exercises.

Editing Cells

As you are aware, when Microsoft Excel starts, it opens a workbook made of three worksheets. A worksheet is simply made of cells that are patiently waiting for you to enter data. You should first practice typing different kinds of items and see what happens.

At all times, at least one of the cells on the worksheet you are working on has focus. That cell is referred to as the active cell. That cell is surrounded with a thicker border. If you type something, it goes into the active cell. If you click a cell and start typing, the new entry will replace the content of that cell, whether that cell had data or not, this could be advantageous or disastrous.

To prevent a cell from being edited by the user, you can protect it and lock its content.

Data you type in a cell can consist of any kinds of characters, letters, numbers, etc. Sometimes, a long text will look like covering more than one cell; unless you merge cells, the text you type goes into one cell regardless of the length of the text.

A cell can contain as many as 64000 characters.

If you have done word processing before, you are probably familiar with techniques of cutting, copying, pasting, and/or moving text from one part of a document to an other, or from one document to another. Although these techniques are available here, Microsoft Excel doesn't quite imitate a word processor. Again, spending time with the software will increase your experience.

When you copy or cut something, it goes in a temporary storage area where it will wait for you to send it somewhere else, an action called pasting. The storage area where the cut or copied data is sent is called the clipboard. In Microsoft Excel 97, you can store only one item at a time in the clipboard. If you cut or copy something, it will replace the content of the clipboard with the new selection. In Microsoft Excel 2000, the clipboard can contain up to twelve items. When the Clipboard toolbar is functional, you can select copied or cut items from its list of buttons. If you use more than 12 stored items, the toolbar functions on a first-in first-out basis.

The Clipboard toolbar can be moved to any location of your choice on the screen. You can also hide/close it if you don't need its services. For this exercise, when the Clipboard toolbar appears, don't close it. Even if you are not preparing for the MOUS exam, you should learn to take advantage of the new clipboard. In case you are not aware, you can use the same Clipboard toolbar in different applications; for example, you can have Microsoft Excel, Microsoft Access, Microsoft Word, and Microsoft Outlook all of them opened at the same time and sharing the same clipboard items.