MS-EXCEL

Lecture note 2

Mr. Hasala Sithum

2 Entering Excel Formulas and Formatting Data
In previous lesson familiarized you with the Excel window, taught you how to move
around the window, and how to enter data. A major strength of Excel is that you
can perform mathematical calculations and format your data. In this Section, you
will learn how to perform basic mathematical calculations and how to format text
and numerical data. To start this Section, open Excel.
2.1 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, you practice some of the methods you can use to
perform mathematical calculations.
2.2 EXERCISE 1
2.2.1 Addition, Subtraction, Multiplication and Division of Numbers

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

MS-EXCEL LECTURE NOTE 01

LPEC
Mr. Hasala Sithum Page 2
 6. Type: = C2 + C3 in cell A5 and press Enter
7. Type: = D2 + D3 in cell A5 and press Enter
When creating formulas, you 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

2.2.2 Perform Advanced Mathematical Calculations

When you 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.
2.3 EXERCISE 2 Advanced Calculations
1. Move to cell A1.
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 A1.

To change the order of calculation, use parentheses. Microsoft Excel calculates the
information in parentheses first.
1. Double-click in cell A1.
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 A1.

MS-EXCEL LECTURE NOTE 01

LPEC
Mr. Hasala Sithum Page 3
2.4 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.
2.5 EXERCISE 3 AutoSum
The following illustrates AutoSum:

1. Go to cell F1.
2. Type 3.
3. Press Enter. Excel moves down one cell.
4. Type 3.
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.

MS-EXCEL LECTURE NOTE 01

LPEC
Mr. Hasala Sithum Page 4
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.
2.6 Align Cell Entries
When you type text into a cell, by default your entry aligns with the left side of the
cell. When you type numbers into a cell, by default your entry aligns with the right
side of the cell. You can change the cell alignment. You can center, left-align, or
right-align any cell entry. Look at cells A1 to D1. Note that they are aligned with the
left side of the cell.

2.7 EXERCISE 4
To center cells A1 to D1:

1. Select cells A1 to D1.

2. Choose the Home tab.
3. Click the Center button in the Alignment group. Excel centers each cell's
content.
Note that left and right alignment can be carried out in a similar manner.

MS-EXCEL LECTURE NOTE 01

LPEC
Mr. Hasala Sithum Page 5
2.8 Copy, Cut and Paste
You can copy or cut data from one area of a worksheet to another.

1. Select cells D9 to D12

2. Choose the Home tab.
3. Click the Cut button.
4. Move to cell G1.

5. Click the Paste button . Excel moves the contents of cells D9 to D12 to
cells G1 to G4.
2.9 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
MS-EXCEL LECTURE NOTE 01
LPEC
Mr. Hasala Sithum Page 6
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.
2.10 EXERCISE 5
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 your 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 your selection.

MS-EXCEL LECTURE NOTE 01

LPEC
Mr. Hasala Sithum Page 7
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 your 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 your selection.
2.11 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
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.
2.12 EXERCISE 6 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.
2.13 Change A Column's Width
You can increase column widths. Increasing the column width enables you to see
the long text.
MS-EXCEL LECTURE NOTE 01
LPEC
Mr. Hasala Sithum Page 8
2.14 EXERCISE 7 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 your 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.
2.15 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.

MS-EXCEL LECTURE NOTE 01

LPEC
Mr. Hasala Sithum Page 9
2.16 EXERCISE 8

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.

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
your 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.

MS-EXCEL LECTURE NOTE 01

LPEC
Mr. Hasala Sithum Page 10
 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.

MS-EXCEL LECTURE NOTE 01

LPEC
Mr. Hasala Sithum Page 11