Online classes notes for COPA Students

Microsoft Excel

Microsoft Excel is an electronic spreadsheet. We can use it to organize data

into rows and columns. We can also use it to perform mathematical calculations quickly.
In excel….we can
Enter text and numbers in a spreadsheet
Enter Excel formulas
Format data
Create Excel functions
Fill cells automatically
Print results
Create Charts, and
Enter advanced Excel formulas

1.1 Entering Text and Numbers In Excel Window

We can start Microsoft Excel 2007 as follows:
1. Click on Microsoft Start Button
2. Point the mouse on All Programs
3. Click on Microsoft Office
4. Click on Microsoft Excel 2007

The Microsoft Excel window appears and screen looks similar to the one shown here.
The Microsoft Office Button
In the upper-left corner of the Excel 2007 window is the Microsoft Office button.
When you click the button, a menu appears. You can use the menu to create a new
file, open an existing file, save a file, print and perform many other tasks.
The Quick Access Toolbar
Next to the Microsoft Office button is the Quick Access toolbar. The Quick Access
toolbar gives you quick access to commands you frequently use.

The Title Bar

Next to the Quick Access toolbar is the Title bar. On the Title bar, Microsoft Excel
displays the name of the workbook you are currently using. At the top of the Excel
window, you should see "Book 1 - Microsoft Excel" or a similar name.
The Ribbon
In Microsoft Excel 2007, you use the Ribbon to issue commands. The Ribbon is
located near the top of the Excel window, below the Quick Access toolbar.

Worksheets
Microsoft Excel consists of worksheets. Each worksheet contains columns and rows.
The columns are lettered A to Z and then continuing with AA, AB, AC and so on; the
rows are numbered 1 to 1,048,576.
The combination of a column coordinate and a row coordinate make up a cell
Online classes notes for COPA Students

address. For example, the cell located in the upper-left corner of the worksheet is
cell A1, meaning column A, row 1. Cell E10 is located under column E on row 10.
You enter data into the cells on the worksheet.

The Formula Bar

If the Formula bar is turned on, the cell address of the cell you are in displays in the
Name box which is located on the left side of the Formula bar. Cell entries display
on the right side of the Formula bar.

The Status Bar

The Status bar appears at the very bottom of the Excel window and provides such
information as the sum, average, minimum, and maximum value of selected
numbers.

Wrap Text: When you type text that is too long to fit in the cell, the text overlaps the next cell.
If you do not want it to overlap the next cell, you can wrap the text.

1. Move to cell A2.

2. Type Text too long to fit.
3. Press Enter.
4. Return to cell A2.
5. Choose the Home tab.
6. Click the Wrap Text button . Excel wraps the text in the cell.

Entering Excel Formulas and Formatting Data

A major strength of Excel is that we can perform mathematical calculations and format data. In this
Section, we can learn how to perform basic mathematical calculations and how to format text
and numerical data. To start this Section, open Excel.

Perform Mathematical Calculations

In Microsoft Excel, you can enter numbers and mathematical formulas into cells.
Whether you enter a number or a formula, you can reference the cell when you
perform mathematical calculations such as addition, subtraction, multiplication, or
division. When entering a mathematical formula, precede the formula with an equal
(=) sign. Use the following to indicate the type of calculation you wish to perform:
+ Addition
– Subtraction
* Multiplication
/ Division
^ Exponential
In the following exercises, we can practice some of the methods we can use to
perform mathematical calculations.

Addition, Subtraction, Multiplication and Division of Numbers

Online classes notes for COPA Students

1. Type: Add, Subtract, Multiply, and Divide in cells A1, B1, C1, and D1
respectively
2. Type: 12, 25, 11 and 75 in cells A2, B2, C2 and D2 respectively
3. Type: 8, 13, 6 and 5 in cells A3, B3, C3 and D3 respectively
4. Type: = A2 + A3 in cell A5 and press Enter
5. Type: = B2 + B3 in cell A5 and press Enter
6. Type: = C2 + C3 in cell A5 and press Enter
7. Type: = D2 + D3 in cell A5 and press Enter
When creating formulas, we can reference cells and include numbers. All of the
following formulas are valid:
(a) =A2/B2; (b) =A2+12-B3; (c) =A2*B2+12; (d) =24+53/B2

Perform Advanced Mathematical Calculations

When we perform mathematical calculations in Excel, be careful of precedence.
Calculations are performed from left to right, with multiplication and division
performed before addition and subtraction.

Advanced Calculations
1. Move to cell A7.
2. Type =3+3+12/2*4.
3. Press Enter.
Note: Microsoft Excel divides 12 by 2, multiplies the answer by 4, adds 3, and then
adds another 3. The answer, 30, displays in cell A7.
To change the order of calculation, use parentheses. Microsoft Excel calculates the
information in parentheses first.
1. Double-click in cell A7.
2. Edit the cell to read =(3+3+12)/2*4.
3. Press Enter.
Note: Microsoft Excel adds 3 plus 3 plus 12, divides the answer by 2, and then
multiplies the result by 4. The answer, 36, displays in cell A7.

AutoSum
You can use the AutoSum button on the Home tab to automatically add a
column or row of numbers. When you press the AutoSum button , Excel selects
the numbers it thinks you want to add. If you then click the check mark on the
Formula bar or press the Enter key, Excel adds the numbers. If Excel's guess as to
which numbers you want to add is wrong, you can select the cells you want.

AutoSum
The following illustrates AutoSum:
1. Go to cell F1.
2. Type 3.
3. Press Enter. Excel moves down one cell.
4. Type 3.
Online classes notes for COPA Students

5. Press Enter. Excel moves down one cell.

6. Type 3.
7. Press Enter. Excel moves down one cell to cell F4.
8. Choose the Home tab.
9. Click the AutoSum button in the Editing group. Excel selects cells F1
through F3 and enters a formula in cell F4.
10.Press Enter. Excel adds cells F1 through F3 and displays the result in cell F4.
Note that you can click on the arrow next to AutoSum to access other automatic
calculations like average, minimum and maximum values, count numbers, etc.

Insert and Delete Columns and Rows

You can insert and delete columns and rows. When you delete a column, you delete
everything in the column from the top of the worksheet to the bottom of the
worksheet. When you delete a row, you delete the entire row from left to right.
Inserting a column or row inserts a completely new column or row.

Insert and Delete Columns and Rows

To delete columns F and G:
1. Click the column F indicator and drag to column G.
2. Click the down arrow next to Delete in the Cells group. A menu appears.
3. Click Delete Sheet Columns. Excel deletes the columns you selected.
4. Click anywhere on the worksheet to remove selection.

To delete rows 7 through 12:

1. Click the row 7 indicator and drag to row 12.

2. Click the down arrow next to Delete in the Cells group. A menu appears.
3. Click Delete Sheet Rows. Excel deletes the rows you selected.
4. Click anywhere on the worksheet to remove selection.

To insert a column:
1. Click on A to select column A.
2. Click the down arrow next to Insert in the Cells group. A menu appears.
3. Click Insert Sheet Columns. Excel inserts a new column.
4. Click anywhere on the worksheet to remove selection.
To insert rows:
1. Click on 1 and then drag down to 2 to select rows 1 and 2.
2. Click the down arrow next to Insert in the Cells group. A menu appears.
3. Click Insert Sheet Rows. Excel inserts two new rows.
4. Click anywhere on the worksheet to remove selection.

Work with Long Text

Whenever you type text that is too long to fit into a cell, Microsoft Excel attempts to
display all the text. It left-aligns the text regardless of the alignment you have
Online classes notes for COPA Students

assigned to it, and it borrows space from the blank cells to the right. However, a
long text entry will never write over cells that already contain entries—instead, the
cells that contain entries cut off the long text. The following exercise illustrates this.

Work with Long Text

1. Move to cell A6.
2. Type Now is the time for all good men to go to the aid of their army.
3. Press Enter. Everything that does not fit into cell A6 spills over into the adjacent cell.
4. Move to cell B6.
5. Type Test.
6. Press Enter. Excel cuts off the entry in cell A6.
7. Move to cell A6.
8. Look at the Formula bar. The text is still in the cell.

Change A Column's Width

You can increase column widths. Increasing the column width enables you to see
the long text.

Change Column Width

1. Make sure you are in any cell under column A.
2. Choose the Home tab.
3. Click the down arrow next to Format in the Cells group.
4. Click Column Width. The Column Width dialog box appears.
5. Type 55 in the Column Width field.
6. Click OK. Column A is set to a width of 55. You should now be able to see all
of the text.

Change a Column Width by Dragging

You can also change the column width with the cursor.
1. Place the mouse pointer on the line between the B and C column headings.
The mouse pointer should look like the one displayed here , with two
arrows.
2. Move mouse to the right while holding down the left mouse button. The
width indicator appears on the screen.
3. Release the left mouse button when the width indicator shows approximately
20. Excel increases the column width to 20.
Change a Column Width by AutoFit Column Width
1. Select the column or column you want to change the column width.
2. Choose the Home tab.
3. Click the down arrow next to Format in the Cells group.
4. Click on AutoFit Column Width. You should now be able to see all of the text.
Online classes notes for COPA Students

Format Numbers
You can format the numbers you enter into Microsoft Excel. For example, you can
add commas to separate thousands, specify the number of decimal places, place a
dollar sign in front of a number, or display a number as a percent.

1. Move to cell B8.

2. Type 1234567.
3. Click the check mark [ √ ] on the Formula bar.
4. Choose the Home tab.
5. Click the down arrow next to the Number Format box. A menu appears.
6. Click Number. Excel adds two decimal places to the number you typed.
7. Click the Comma Style button . Excel separates thousands with a comma.
8. Click the Accounting Number Format button . Excel adds a dollar sign to
number.
9. Click twice on the Increase Decimal button to change the number format
to four decimal places.
10.Click the Decrease Decimal button , if you wish to decrease the number of
decimal places.

Change a decimal to a percent.

1. Move to cell B9.
2. Type 0.35 (note the decimal point).
3. Click the check mark [ √ ] on the formula bar.
4. Choose the Home tab.
5. Click the Percent Style button . Excel turns the decimal to a percent.
This is the end of Section 2. You can save and close file. See Section 1
(Subsections 1.23 and 1.24) to learn how to save and close a file.

Understanding Functions
Functions are prewritten formulas. Functions differ from regular formulas in that
you supply the value but not the operators, such as +, -, *, or /. For example, you
can use the SUM function to add. When using a function, remember the following:

1. Use an equal sign to begin a formula.

2. Specify the function name.
3. Enclose arguments within parentheses. Arguments are values on which you
want to perform the calculation. For example, arguments specify the
numbers or cells you want to add.
4. Use a comma to separate arguments.
Here is an example of a function:
=SUM(2,13,A1,B2:C7)
In this function, known as the SUM function:
1. The equal sign begins the function.
Online classes notes for COPA Students

2. SUM is the name of the function.

3. 2, 13, A1, and B2:C7 are the arguments. Parentheses enclose the arguments.
4. Commas separate the arguments.

After we type the first letter of a function name, the AutoComplete list appears.
You can double-click on an item in the AutoComplete list to complete entry
quickly. Excel will complete the function name and enter the first parenthesis.

Functions
The SUM function adds argument values.
1. Open Microsoft Excel.
2. Type 12 in cell B1.
3. Press Enter.
4. Type 27 in cell B2.
5. Press Enter.
6. Type 24 in cell B3.
7. Press Enter.
8. Type =SUM(B1:B3) in cell A4.
9. Press Enter. The sum of cells B1 to B3, which is 63, appears.

Alternate Method: Enter a Function with the Ribbon

1. Type 150 in cell C1.
2. Press Enter.
3. Type 85 in cell C2.
4. Press Enter.
5. Type 65 in cell C3, and Press Enter
6. Choose the Formulas tab.
7. Click the Insert Function button. The Insert Function dialog box appears.
8. Choose Math & Trig in the Or Select A Category box.
9. Click Sum in the Select A Function box.
10.Click OK.
11.The Function Arguments dialog box appears with C1:C3 displayed in the
Number1 field.
12.Type C1:C3 in the Number1 field, if it does not automatically appear.
13.Click OK. The sum of cells C1 to C3, which is 300, appears.

Calculate an Average
You can use the AVERAGE function to calculate the average of a series of numbers.
1. Move to cell A6.
2. Type Average. Press the right arrow key to move to cell B6.
3. Type =AVERAGE(B1:B3).
4. Press Enter. The average of cells B1 to B3, which is 21, appears.
3.5 Find the Lowest Number
You can use the MIN function to find the lowest number in a series of numbers.
Online classes notes for COPA Students

1. Move to cell A7.

2. Type Min. Press the right arrow key to move to cell B7.
3. Type =MIN(B1:B3).
4. Press Enter. The lowest number in the series, which is 12, appears.

3.6 Find the Highest Number

we can use the MAX function to find the highest number in a series of numbers.
1. Move to cell A8.
2. Type Max. Press the right arrow key to move to cell B8.
3. Type =MAX(B1:B3).
4. Press Enter. The highest number in the series, which is 27, appears.
3.7 Count the Numbers in a Series of Numbers
You can use the count function to count the number of numbers in a series.
1. Move to cell A9.
2. Type Count. Press the right arrow key to move to cell B9.
3. Type =COUNT(B1:B3).
4. Press Enter. The number of items in the series, which is 3, appears.

Set the Page Layout (Margins)

1. Choose the Page Layout tab.
2. Click Margins in the Page Setup group. A menu appears.
3. Click Wide. Excel sets margins to the Wide settings.

Set the Page Orientation

1. Choose the Page Layout tab.
2. Click Orientation in the Page Setup group. A menu appears.
3. Click Landscape. Excel sets page orientation to landscape.

Set the Paper Size

1. Choose the Page Layout tab.
2. Click Size in the Page Setup group. A menu appears.
3. Click the paper size you are using. Excel sets page size.

Print
The simplest way to print is to click the Office button, highlight Print on the menu
that appears, and then click Quick Print in the Preview and Print the Document
pane. Dotted lines appear on screen, and document prints. The dotted
lines indicate the right, left, top, and bottom edges of printed pages. To print
from Microsoft Excel, you can proceed as follows:
1. Click on Microsoft Office Button
2. Highlight or point the mouse on Print.
3. Click on Print.
4. In the Name box, under Printer option, choose an appropriate printer.
Online classes notes for COPA Students

5. Under the Print Range option, choose appropriate range of pages to be printed.
6. Under Copies option, choose appropriate number of copies of each page to be printed.
7. Click on OK when ready.

Print Preview
1. Click the Office button. A menu appears.
2. Highlight Print. The Preview and Print The Document pane appears.
3. Click Print Preview. The Print Preview window appears, with document in the center.

Print
1 Click on Microsoft Office Button
2 Highlight or point the mouse on Print.
3 Click on Print. The Print dialog box appears
4. In the Name box, under Printer option, choose an appropriate printer.
5. Under the Print Range option, choose appropriate range of pages to be printed.
6. Under Copies option, choose appropriate number of copies of each page to be printed.
7. Click on OK when ready.
This is the end of Section 3. You can save and close file.
u have reached the end of Section 5. You can save and close file.