Introduction To MS EXCEL

Introduction to MS EXCEL
Dr A.A olorukooba
Department of community medicine
ABU Zaria.
outline
 Objectives
 Getting started with Excel
 Entering data
 Editing data
 Formatting data
 Functions
 Printing
 Charts
 Data manipulation
2 department of community medicine. 5/11/2018

objectives
After this session you should be able to:
 Create and maintain spreadsheets
 Create and edit formulae
 Format and print worksheets
 Perform basic data management tasks.

Getting started with Excel
 What is Excel?
 The Excel 2007 window
 Starting Excel
 Closing Excel
 Navigating within a worksheet
 Selecting cells
 Selecting rows or columns

What is excel?
 Excel and Word have a lot in common, since they both belong to the
MS Office suite of programs.
 This means that if you are familiar with Word, then you already know
how to use several Excel features!
 In the Word section of our past sessions, you’ll be able to find more
information and guidance on:
-Starting the program

-The Office button and ribbon
-Character formatting
-Opening, saving and printing files
-Accessing Help

What is excel?
 Excel is all about numbers!
 There’s almost no limit to what you can do with

numbers in Excel,
 including sorting, advanced calculations, and graphing.
 In addition, Excel’s formatting options mean that

whatever you do with your numbers, the result will
always look professional!

What is excel? contd
 Data files created with Excel are called workbooks (in the
same way as Word files are called documents).
 But where Word starts up with a single blank page, Excel

files by default contain three blank worksheets.
 This gives you the flexibility to store related data in

different locations within the same file.
 More worksheets can be added, and others deleted, as

required.

What is excel? contd
 You’ll often hear Excel files referred to as
spreadsheets.
 This is a generic term, which sometimes means a

workbook (file) and sometimes means a worksheet (a
page within the file).
 For the sake of clarity, I’ll be using the terms

workbook and worksheet in this session .

The Excel 2007 window
 As in Word 2007, the old menu system has been
replaced by the Ribbon and the Office button.
 The title bar displays the name of your current

workbook.
 Tabs at the bottom of the screen identify the different
worksheets available to you.

The Excel 2007 window contd
 Notice how the working area of the screen is divided into
rows (1, 2, 3, 4, ...) and columns (A, B, C, D, …).
 Together these provide an address, such a C10 or G21, that

uniquely identifies
each cell in the worksheet.
 A range of cells extends in a rectangle from one cell to

another, and is referred to by using the first and last cell
addresses separated by a colon.
 For example, the group of cells from A3 to G4 would be

written as A3:G4.

The Excel 2007 window contd
 In the example above, the current or active cell is B7.
 It is surrounded by a heavy blackborder, and its address is

displayed in the name box above column A.
 Its row and column numbers are also highlighted.
 On the right of the name box is the formula bar.
 This displays the value stored in the active cell, and is also
the place where you would enter a new data value or
formula into that cell.
department of community medicine.
12 5/11/2018
Starting Excel
 If you have an icon on the desktop for Excel, then all

you have to do is double-click it to open Excel.
 Alternatively, click the Start button and then select All
Programs, Microsoft Office, Microsoft Excel.

Navigating within a worksheet
Using the mouse:
 Use the vertical and horizontal scroll bars if you want
to move to an area of the
screen that is not currently visible.
 To move to a different worksheet, just click on the tab

below the worksheet.
Using the keyboard:

 Use the arrow keys, or [PAGE UP] and [PAGE
DOWN], to move to a different
area of the screen.

Navigating within a worksheet contd.
 [CTRL] + [HOME} will take you to cell A1.
 [CTRL] + [PAGE DOWN] will take you to the next

worksheet, or use [CTRL] +
[PAGE UP] for the preceding worksheet.
 You can jump quickly to a specific cell by pressing

[F5] and typing in the cell address.
 You can also type the cell address in the name box
above column A, and press [ENTER].

Selecting cells
Using the mouse:
 Click on a cell to select it.
 You can select a range of adjacent cells by clicking on the first one,
and then dragging the mouse over the others.
 You can select a set of non-adjacent cells by clicking on the first one,
and then holding down the [CTRL] key as you click on the others.
Using the keyboard:
 Use the arrow keys to move to the desired cell, which is automatically
selected.
 To select multiple cells, hold down the [SHIFT] key while the first cell
is active, and then use the arrow keys to select the rest of the range.

Selecting rows or columns
 To select all the cells in a particular row, just click on
the row number (1, 2, 3, etc) at the left edge of the
worksheet.
 Hold down the mouse button and drag across row

numbers to selectmultiple adjacent rows.
 Hold down [CTRL] if you want to select a set of non-

adjacent rows.
 Similarly, to select all the cells in column, you should

click on the column heading (A, B, C, etc) at the top
edge of the worksheet.

Selecting rows or columns
 Hold down the mouse button and drag across column
headings to select multiple adjacent columns.
 Hold down [CTRL] if you want to select a set of non-

adjacent columns.
 You can quickly select all the cells in a worksheet by

clicking the square to the immediate left of the Column
A heading (just above the label for Row 1).

Entering data
 First you need a workbook.
 Overview of data types
 Data entry cell by cell
 Deleting data
 Moving data
 Copying data
 Using Autofill
 Saving a workbook

First you need a workbook
 Before you start entering data, you need to decide
whether this is a completely new project deserving a
workbook of its own,
 Or whether the data you are going to enter relates to an

existing workbook.
 Remember that you can always add a new worksheet

to an existing workbook, and you’ll find it much
easier to work with related data if it’s all stored in the
same file.

First you need a workbook contd
If you need to create a new workbook from inside Excel:
1. Click on the Office button, select New and then Blank
Workbook.
2. Sheet 1 of a new workbook will be displayed on your
screen, with cell A1 active.
To open an existing workbook from inside Excel:

1. Click on the Office button, click Open, and then navigate
to the drive and folder containing the file you want to open.
2. Double-click on the required file name.

Overview of data types
 Excel allows you to enter different sorts of data into
the cells on a worksheet, such as dates, text, and
numbers.
 If you understand how Excel treats the different types

of data, you’ll be able to structure your worksheet as
efficiently as possible.
 Numbers lie at the heart of Excel’s functionality.
 They can be formatted in a variety of different ways

– we’ll get to that later.

Overview of data types contd
 You should generally avoid mixing text and numbers
in a single cell, since Excel will regard the cell
contents as text,
 and won’t include the embedded number in

calculations.
 If you type any spaces within a number, it will also be

regarded as text.

 Note that dates and times are stored as numbers in
Excel, so that you can
 calculate the difference between two dates. However,
they are usually displayed
 as if they are text.
 If a number is too large to be displayed in the current
cell, it will be displayed as
 “#####”. The formatting section of this manual
explains how to widen a column

 Text consist mainly of alphabetic characters, but can
also include numbers,
marks and special characters (like the check mark in
the example
above).
 Text fields are not included in numeric calculations.
 If you want Excel to treat an apparent number as text,

then you should precede the number with a single
quotation mark (‘).

 This can be useful when entering for example a phone
number that starts with 0
 since leading zeros are not usually displayed for Excel

numbers.
 If a text field is too long to be displayed in the current

cell, it will spill over into the next cell if that cell is
empty, otherwise it will be truncated at the cell border.

Overview of data types contd.
 Formulas are the most powerful elements of an Excel
spreadsheet.
 Every formula starts with an “=” sign, and contains at least

one logical or mathematical operation (or special function),
combined with numbers and/or cell references.
 We’ll discuss formulas and functions in more detail later in

the manual.

Data entry cell by cell
To enter either numbers or text:
1. Click on the cell where you want the data to be stored,
so that the cell becomes active.
2. Type the number or text.
3. Press [ENTER] to move to the next row, or [TAB] to

move to the next column.
Until you’ve pressed [ENTER] or [TAB], you can cancel

the data entry by pressing [ESC].

Data entry cell by cell
 To enter a date, use a slash or hyphen between the day,
month and year, for example
 14/02/2009. Use a colon between hours, minutes and

seconds, for example 13:45:20.

Deleting data
You want to delete data that’s already been entered in a
worksheet? Simple!
1. Select the cell or cells containing data to be deleted.
2. Press the [DEL] key on your keyboard.
3. The cells remain in the same position as before, but

their contents are deleted

Moving data
You’ve already entered some data, and want to move it to a
different area on the worksheet?
1. Select the cells you want to move (they will become

highlighted).
2. Move the cursor to the border of the highlighted cells.

When the cursor changes from a white cross to a four-
headed arrow (the move pointer), hold down the left
mouse button.

Moving data
3.Drag the selected cells to a new area of the worksheet,
then release the mouse button.
 You can also cut the selected data using the ribbon
icon or [CTRL] + [X],
 then click in the top left cell of the destination area and
paste the data with the ribbon icon or [CTRL] + [V].

Copying data
To copy existing cell contents to another area on the
worksheet:
1. Select the cells you want to copy (they will become
highlighted).
2. Move the cursor to the border of the highlighted cells

while holding down the [CTRL] key.
When the cursor changes from a white cross to a hollow

left-pointing arrow (the copy pointer), hold down the left
mouse button.
3. Drag the selected cells to a second area of the worksheet,

then release the mouse button
Copying data contd
 You can also copy the selected data using the ribbon icon
or [CTRL] + [C], then click in the top left cell of the
destination area and paste the data with the ribbon icon
or [CTRL] + [V].
 To copy the contents of one cell to a set of adjacent cells,

select the initial cell and then move the cursor over the
small square in the bottom right-hand corner (the fill
handle).

Copying data contd
 The cursor will change from a white cross to a black cross.
 Hold down the mouse button and drag to a range of

adjacent cells.
 The initial cell contents will be copied to the other cells.
 Note that if the original cell contents end with a number,

then the number will be incremented in the copied cells.

Using Autofill
 This is one of Excel’s niftiest features!
 It takes no effort at all to repeat a data series (such as

the days of the week, months of the year, or a numbers
series such as odd numbers) over a range of cells.
1. Enter the start of the series into a few adjacent cells

(enough to show the underlying pattern).
2. Select the cells that contain series data.

Using Autofill contd
3 . Move the cursor over the small square in the bottom

right-hand corner of the selection (the fill handle).
4. Hold down the mouse button and drag to a range of

adjacent cells.
5. The target cells will be filled based on the pattern of

the original series cells.

Editing data
 Editing cell contents
 Inserting or deleting cells
 Inserting or deleting rows
 Inserting or deleting columns
 Inserting or deleting a worksheet
 Moving or copying a worksheet
 Renaming a worksheet

Editing data
 In data entry mode, when you move the cursor to a new cell,
anything you type replaces the
 previous cell contents.
 Edit mode allow you to amend existing cell contents without

having to retype the entire entry.
 Note that while you are in edit mode, many of the Ribbon
commands are disabled.

Editing cell contents
 There are two different ways to enter edit mode: either
double-click on the cell whose contents you want to edit,
 or else click to select the cell you want to edit, and then
click anywhere in the formula bar.
 To delete characters, use the [BACKSPACE] or [DEL] key.
 To insert characters, click where you want to insert them,

and then type.
 You can toggle between insert and overtype mode by

pressing the [INSERT] key.

 You can force a line break within the current cell
contents by typing [ALT] +[ENTER].
 Exit edit mode by pressing [ENTER].
 Your cell contents look correct in the formula bar, but

don’t display correctly in the worksheet?
 Check whether either of these common problems is the

culprit:

 * If a cell contains a number but displays #####, then
the column is not wide enough to show
 the full data value. You need to make the column wider
(see formatting).
 * If a cell contains text but chops off the display at the

edge of the column, then you need to either widen the
column or wrap the text within the column (see
formatting).

Inserting or deleting cells
 You can insert a new cell above the current active cell,
in which case the active cell and those below it will
each move down one row.
 You can also insert a new cell to the left of the current
active cell, in which case the active cell and those on
its right will each move one column to the right.

Inserting or deleting cells contd
To insert a cell:
1. Select the cell next to which you want to insert a new

cell.
2. On the Home ribbon, find the Cells group and click

Insert followed by Insert Cells.

Moving or copying a worksheet contd
 The To Book field allows you to move or copy the current
worksheet to another workbook.
 The Before Sheet field allows you to specify the new position of
the worksheet.
 The Create a Copy checkbox lets you specify whether the

worksheet should be moved or copied.
 Renaming a worksheet
 Right-click on the worksheet tab, and select Rename from the

pop-up menu.
 Type the new worksheet name and press [ENTER].

Formatting data
 Cell formatting
 Formatting rows and columns
 Hiding rows and columns
 Keeping row and column headings in view
 Creating a formula
 How formulas are evaluated

 Relative cell referencing
 Absolute cell referencing
Cell formatting contd.

Cell formatting contd



Keeping row and column headings in
view
 If you scroll through a lot of data in a worksheet, you’ll
probably lose sight of the column headings as they
disappear off the top of your “page”.
 This can make life really difficult –imagine trying to

check a student’s result for tutorial 8 in row 183 of the
worksheet!
 And it’seven more difficult if the student’s name in

column A has scrolled off the left edge of the window.
 And it’s easy to do!

department of community medicine. 5/11/2018
57
view
 The Freeze Panes feature allows you to specify
particular rows and columns that will always remain
visible as you scroll through the worksheet.
 Select a cell immediately below the rows that you want

to remain visible, and immediately to the right of the
columns that you want to remain visible.
 For example, if you want to be able to see Rows 1 and

2, and column A, then you would click on cell B3.

view contd
 For example, if you want to be able to see Rows 1 and
2, and column A, then you would click on cell B3.
 If Freeze Panes has already been applied, then the

ribbon option automatically changes to Unfreeze
Panes.

Formulas
 Formulas are the key to Excel’s amazing power and
versatility!
 By using a formula, you can find the answer to

virtually any calculation you can think of!
 In this section I’m going to explain how to construct a

formula,
 and give you some guidelines to ensure that your

formulas work correctly.

Creating a formula
 Rule number one: a formula always starts with an equals
sign (“=”).
 This lets Excel know that it’s going to have to work

something out.
 In the body of the formula, you’re going to tell Excel what

you want it to calculate.
 You can use all the standard maths operations, like addition
and multiplication,
 and you can include numbers, cell references, or built in
functions (which are covered in the next section of this
manual).

Creating a formula contd
 For example, suppose you have a retail business. You
buy stock at cost price,
 and add a 25% markup to calculate your selling price.
VAT must be added to that at 14%.
 You give a 5% discount to long-standing customers

who pay their accounts promptly.
 Let’s look at how formulas can make the calculations

simple for you



How formulas are evaluated
 now let’s look at some of the rules for creating
formulas:
The operators that you need to know are
 + addition
 - subtraction
 * multiplication
 / division
 ^ exponentiation (“to the power of”)
 & to join two text strings together

 These operations are evaluated in a particular order of
precedence by Excel:
 Operations inside brackets are calculated first
 Exponentiation is calculated second.
 Multiplication and division are calculated third.
 Addition and subtraction are calculated fourth.
 When you have several items at the same level of

precedence, they are calculated from left to right.

Relative cell referencing
 Remember in the pricing example above, how you just
had to copy the formulas in row 2 and the remaining
values were automatically calculated?
 Look again in the formula bar of that screenshot. Although

the formula that you copied from cell G2 was “=F2 *
95%”, the formula in cell G5 reads “=F5 * 95%”.
 The original reference to row 2 in the formula has
changed to a reference to row 5.
 This is called relative addressing, and it’s an important

concept.

Relative cell referencing contd
 Relative addressing is what Excel uses by default.
 This means that when you copy or move a formula to

a new location in a worksheet (or even to another
workbook),
 Excel automatically adjusts the cell references in the

copied formula to be consistent with the original
formula.
 If the original formula referenced a value five columns

to the left and two rows down, then the copied formula
will do the same.

Absolute cell referencing
 Now we’re going to improve the structure and usability of our
pricing model.
 After all, there’s no guarantee that your markup will always be

25% -
 in these tough economic times you may need to reduce it to

remain competitive.
 And what if the VAT rate changes? Or maybe you

decide to increase the discount to encourage customers to pay
promptly?
 The following example shows you how to construct the

worksheet so that it allows for future changes in the business.

Absolute cell referencing contd

 Now my formulas need to change, so that they refer to the values in
cells B1 to B3 instead of physically typing the actual percentages in
each formula.
 The formula in cell C5 will become “=B5 * B1”, instead of the

previous “=B5 *25%”.
 This multiplies the value in cell B5 (cost price) by the value in cell B1
(markup rate), and displays the result in cell C5 (markup).
 The formula in cell E5 will become contains “=D5 * B2”, instead of

the previous “=D5 * 14%”.
 This multiplies the value in cell D5 (retail price) by the value in cell
B2 (VAT rate), and displays the result in cell E5 (selling price).

 Check that you follow the logic so far. If not, please go through it
again.
 BUT… Stop right here! Do you see the problem ahead?
 When I copy my new formulas from row 5 into rows 6 to 8,

Excel is going to use relative addressing.
 The formula in cell C5 refers to cell B1 (the markup rate). When

I copy the formula to the next row, it will want to refer to cell B2
(the VAT rate), instead of cell B1 (the markup rate).
 By the time I’ve finished copying, my spreadsheet will look like

this: department of community medicine.
74 5/11/2018
 Clearly something is very wrong!
 Relative addressing makes working with formulas really

easy,
 but sometimes you don’t want the referencing in the

formula to change as the formula is copied.
 Then you need to use absolute addressing

 . Absolute addressing fixes a cell reference so that
regardless of where the formula is copied to, it will always
reference the same original cell.
 This feature takes the versatility of formulas to the next

level!
 To use absolute addressing in a formula, all you need do is

to click on the cell reference that you want to remain fixed,
and then press the [F4] key.

 Using relative addressing, the formula looked like this: = B5 *B1
1. Double click on cell C5 to enter edit mode.
2. In the formula bar, click on the cell reference “B1” and press
[F4] on the keyboard.
3. The formula changes to: =B5 * $B$1. Note that cell reference B5
must retain its relative addressing, since you want to multiply
each different cost price by the same fixed markup rate.
4. Press [ENTER] to accept the change.

 Instead of using the [F4] key to make a cell reference absolute,
you can simply type the dollar signs if you prefer to do so.
 Once all the formulas in row 5 have been corrected to use

absolute referencing, you can copy them into rows 6 to 8.
 The following screenshot shows the result – now it all works

perfectly

 Any future changes to the markup rate, VAT rate or discount rate
need only be entered once in cells B1 to B3 (where they are
clearly visible), and the calculated values from row 5
downwards will automatically be updated!

Functions
 Excel provides a wide range of built-in functions that can
be included in your formulas to save you the effort of
having to specify detailed calculations step-by-step.
 Each function is referred to by a specific name, which acts

as a kind of shorthand for the underlying calculation.
 Because a function is used inside a formula, you’ll still

need to start off with an equals sign to show Excel that a
calculation is required.

Using AutoSum
 Because addition is the most frequently used Excel
function, a shortcut has been provided to quickly add a set
of numbers:
1. Select the cell where you want the total to appear.
2. Click on the Sum button on the Home ribbon.
3. Check that the correct set of numbers has been selected

(indicated by a dotted line).
If not, then drag to select a different set of numbers.
4. Press [ENTER] and the total will be calculated.

The IF() function
 The IF() function is getting a section all of its own,
because for many people it’s not as intuitive to
understand as the common maths and stats functions.
 The IF() function checks for a specific condition. If the

condition is met, then one action is taken;
 if the condition is not met, then a different action is

taken. For example, you may be reviewing a set of
tutorial marks.

The IF() function
 If a student’s average mark is below 50, then the cell

value should be FAIL;
 so the condition you are checking is whether or not

the average result is below 50.
 If this condition is not met (that is, the average result

is 50 or more), then the cell value should be PASS.

Let’s see this in action:
 The structure of an IF() function is:

=IF (condition, result if true, result if false)
 Using English to describe our example as an IF

statement: IF the average mark is less than
 50, then display the word “FAIL”, else display the
word “PASS”.
 Now for a real worksheet example. Look at the

formula bar in the screenshot below

Introduction To MS EXCEL

Uploaded by

Document Informationclick to expand document information

Copyright:

Available Formats

Introduction To MS EXCEL

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Introduction To MS EXCEL

Uploaded by

Copyright:

Available Formats

Introduction to MS EXCEL

2 department of community medicine. 5/11/2018

 Create and maintain spreadsheets

 Create and edit formulae

 Format and print worksheets

 Perform basic data management tasks.

3 department of community medicine. 5/11/2018

4 department of community medicine. 5/11/2018

-Starting the program

5 department of community medicine. 5/11/2018

 There’s almost no limit to what you can do with

 including sorting, advanced calculations, and graphing.

 In addition, Excel’s formatting options mean that

6 department of community medicine. 5/11/2018

 But where Word starts up with a single blank page, Excel

 This gives you the flexibility to store related data in

 More worksheets can be added, and others deleted, as

7 department of community medicine. 5/11/2018

 This is a generic term, which sometimes means a

 For the sake of clarity, I’ll be using the terms

8 department of community medicine. 5/11/2018

 The title bar displays the name of your current

9 department of community medicine. 5/11/2018

 Together these provide an address, such a C10 or G21, that

 A range of cells extends in a rectangle from one cell to

 For example, the group of cells from A3 to G4 would be

11 department of community medicine. 5/11/2018

 It is surrounded by a heavy blackborder, and its address is

 Its row and column numbers are also highlighted.

 On the right of the name box is the formula bar.

 If you have an icon on the desktop for Excel, then all

13 department of community medicine. 5/11/2018

 To move to a different worksheet, just click on the tab

Using the keyboard:

14 department of community medicine. 5/11/2018

 [CTRL] + [PAGE DOWN] will take you to the next

 You can jump quickly to a specific cell by pressing

15 department of community medicine. 5/11/2018

Using the keyboard:

16 department of community medicine. 5/11/2018

 Hold down the mouse button and drag across row

 Hold down [CTRL] if you want to select a set of non-

 Similarly, to select all the cells in column, you should

17 department of community medicine. 5/11/2018

 Hold down [CTRL] if you want to select a set of non-

 You can quickly select all the cells in a worksheet by

18 department of community medicine. 5/11/2018

19 department of community medicine. 5/11/2018

 Or whether the data you are going to enter relates to an

 Remember that you can always add a new worksheet

20 department of community medicine. 5/11/2018

To open an existing workbook from inside Excel:

2. Double-click on the required file name.

21 department of community medicine. 5/11/2018

 If you understand how Excel treats the different types

 Numbers lie at the heart of Excel’s functionality.

 They can be formatted in a variety of different ways