University of Babylon 2ed lecture, 2ed stage

Collage of Dentistry 2ed course,2020

Dr. Jameela Alkrimi
Introduction to Excel

Entering Excel Formulas and Formatting Data

lecture 1 familiarized you with the Excel 2007 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 lecture, you will learn how to perform basic mathematical calculations and how to format text and numerical
data.

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.

EXERCISE 1

How can apply the Addition, Subtraction, Multiplication and Division of Numbers in Excel.

 Type: Add, Subtract, Multiply, and Divide in cells A1, B1, C1, and D1 respectively
 Type: 12, 25, 11 and 75 in cells A2, B2, C2 and D2 respectively
 Type: 8, 13, 6 and 5 in cells A3, B3, C3 and D3 respectively
 Type: = A2 + A3 in cell A5 and press Enter
 Type: = B2 + B3 in cell A5 and press Enter
 Type: = C2 + C3 in cell A5 and press Enter
 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

1
University of Babylon 2ed lecture, 2ed stage
Collage of Dentistry 2ed course,2020
Dr. Jameela Alkrimi
Introduction to Excel

(b) = A2+12-B3

(c) = A2*B2+12

(d) = 24+53/B2

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.

EXERCISE 2.

Divides 12 by 2, multiplies the answer by 4, adds 3, and then adds another 3. The answer displays in cell A7.

1. Move to cell A7.

2. Edit the cell to read=(3+3+12)/2*4.
3. Press Enter.

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

EXERCISE 3. Find the auto sum for Colom f1:f3 and put the result in cell F4?
1. Go to cell F1.
2. Type3.
3. Press Enter, Excel moves down one cell.
4. Type3.
5. Click the AutoSum button in the Editing group. Excel selects
cells F1 through F3 and enters a formula in cell F4
6. 10.Press Enter. Excel adds cells F1 through F3 and displays the
result in cell F4.
7. Click the AutoSum button in the Editing group. Excel selects

2
University of Babylon 2ed lecture, 2ed stage
Collage of Dentistry 2ed course,2020
Dr. Jameela Alkrimi
Introduction to Excel

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

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

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

3
University of Babylon 2ed lecture, 2ed stage
Collage of Dentistry 2ed course,2020
Dr. Jameela Alkrimi
Introduction to Excel

5. Copy, Cut and Paste

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

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

EXERCISE 5.
Insert and Delete Columns and Rows To delete columns F and G:

4
University of Babylon 2ed lecture, 2ed stage
Collage of Dentistry 2ed course,2020
Dr. Jameela Alkrimi
Introduction to Excel

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

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

EXERCISE 6. Work with Long Text

5
 University of Babylon 2ed lecture, 2ed stage
Collage of Dentistry 2ed course,2020
Dr. Jameela Alkrimi
Introduction to Excel

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.

3. Change A Column's Width

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

EXERCISE7. 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. Type55in 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.

6
University of Babylon 2ed lecture, 2ed stage
Collage of Dentistry 2ed course,2020
Dr. Jameela Alkrimi
Introduction to Excel

Change a Column WidthbyAutoFit 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.

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

EXERCISE 8.
1. Move to cell B8.
2. Type1234567.
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 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

7
University of Babylon 2ed lecture, 2ed stage
Collage of Dentistry 2ed course,2020
Dr. Jameela Alkrimi
Introduction to Excel

5. Change a decimal to a percent.

1. Move to cell B9.

2. Type0.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.

8
University of Babylon 2ed lecture, 2ed stage
Collage of Dentistry 2ed course,2020
Dr. Jameela Alkrimi
Introduction to Excel

REFERENCE

1. Pawlowsky-Glahn, V., Egozcue, J. J., & Tolosana Delgado, R. (2007). Lecture notes on compositional
data analysis.
2. https://owerricbt.files.wordpress.com/2017/02/excel-lecture-notes3.pdf
3. Evans, J. R., & Olson, D. L. (2001). Introduction to simulation and risk analysis. Prentice Hall PTR.
4. Remenyi, D., Onofrei, G., & English, J. (2009). An introduction to statistics using Microsoft Excel.
Academic Conferences Limited.