INTRODUCTION TO

Microsoft EXCEL
COMPUTER APPLICATIONS IN MODERN INFORMATION SOCIETIES
DEPARTMENT OF SOCIO-COMPUTING
Table of Content
Module # Content
Module 1: Introduction to Excel - Handling Excel Components
- Handling Excel Data
- Preparing Excel Sheets
Module 2: Data Visualization - Conditional Formatting
- Charts
- Pivot Tables and Charts
- Dynamic Dashboards
Module 3: Data Analysis - Data Analysis Add In
- Sensitivity Analysis
- What If Analysis
Module 4: Exploring Data using - Exploring Data and Building Reports
Microsoft Power BI
 Module 1: Introduction to Excel
HANDLING EXCEL COMPONENTS
What is Excel?
Excel is a spreadsheet program from
Microsoft and a component of its
Office product group for business
applications.
Excel enables users to format,
organize and calculate data in a
spreadsheet.
Starting
the
Program
Start Button > All
Programs> Microsoft
Office > Microsoft Excel
You can create a desktop
shortcut by:
Right Click (on Microsoft
Excel) > Send To: Desktop
(Create Shortcut)
The Excel
Screen
The Ribbon and the Command Line

The Ribbon

The Command Line

Formula Bar
Moving Around the
Worksheet
✔Using Mouse
✔Using Keyboard

Button Action
Tab Move one cell to the right
Shift + Tab Move one cell to the left
Enter Move one cell down
Shift + Enter Move one cell up
Crtl + Home Move to cell A1
Crtl + Arrow Move to the last cell to the direction of
Key the arrow
Saving Your Work
✔While you are working, you can click on Save from the Quick Access Toolbar

✔ While working, you can click on Ctrl+S to quickly save your work.

2
✔You can click on File Tab, then Save or Save As
1

✔You can read more on when to use each of them (Click Here)
Saving Your Work (On Drive)
✔If this is the first time you save your
workbook or you click on Save As,
choose your PC or your Cloud.

✔Then, Go to the directory in which

you want to save your file.

✔After that, write the name of your

workbook in the Name Field.

✔Finally, Click Save.

 Module 1: Introduction to Excel
HANDLING EXCEL DATA: FORMATTING
Basic Formatting
✔Assume you were given the following data for the sales made by 5 employees

1. Type in the above data in an excel sheet.

Changing Appearance
2. Change the appearance of the cells. (Font, Size, Color)
Font Face

Font Size

Font Color

Background Color

Font Style
Changing Cell Sizes
3. Change the column width for the data to fit-in.

Double Click here

Alignment
4. Align the cell contents.
Adding Rows/Columns
5. Add a row above the products

How to delete a row/column?

Merging Cells
6. Merge the cells of the row you’ve created and type in the title “Sales for the
Month”

2
Adding Borders
7. Add borders to the table.

2
Format Painter
8. In the empty cell type “Employees” and using the Format Painter, change its
format to be similar to the names’ format.
Format Painter (CONT’D)

Click on Format 2
Painter

1 Select the
formatted cell
Format Painter (CONT’D)

Click on Cell you want to

format
Clearing Format
✔To Clear Format
Formatting Numbers
9. Format the numbers to currency or accounting.
Formatting Numbers (CONT’D)
 Module 1: Introduction to Excel
HANDLING EXCEL DATA: AUTO-FILLING
Auto-Fill
✔Assume you want to create an excel sheet to record the monthly sales of a
company for the years 2001 to 2007.
✔Create the sheet to look like this.
 Auto-Fill (CONT’D)
1. Instead of typing in all the data, you can use the auto-fill option

Type 2001 in cell A3

You can drag down (without holding ctrl

button) to cell A9 then click here and
choose Fill Series
Hold ctrl button and
drag down to cell A9
Auto-Fill (CONT’D)
Type January in cell B2

Drag to fill in the

series
Formatting
2. Format your table and type in the sales for the company.
AutoSum
3. Calculate the sum of sales for each year. To calculate the sales for year 2001 you can use the
AutoSum function. Select cell N3 and click
AutoSum. Drag the range of numbers you want to
calculate, and press Enter

In a new column type Sum

AutoSum (CONT’D)

Instead of using the AutoSum function for

every row, you can drag down to copy the
formula to the rest of the cells and excel will
automatically calculate for the rest of the rows.
Storing Number as Text

You can add ‘ before 2001 so that

excel reads it as a text not a number
and does not include it in any
calculation

Drag down to apply the same for

the rest of the years
 Module 1: Introduction to Excel
HANDLING EXCEL DATA: SORTING & FILTERING
Sorting Data
✔Sort the data according to the sum of sales from largest to smallest.
✔ Select the column with the data you want to sort.
✔ Click Sort & Filter then Sort Largest to Smallest
Filtering Data
✔Select the column with the data you want to filter through.
✔Click Sort & Filter then Filter

3
1
Filtering Data (CONT’D)

4
Filtering Data (CONT’D)

6
5
Filtering Data
And / Or are logic
operators.
✔ And: returns the
data where both
conditions are true.
✔ Or: returns data that
satisfies either one
of the conditions or
both.
Filtering Data (CONT’D)
Filtering Data (CONT’D)
Filtering Data (CONT’D)
Filtering Data (CONT’D)
Filtering Data (CONT’D)
Filtering Data (CONT’D)
Filtering Data (CONT’D)

- United Arab Emirates

- United States of
America
- United Kingdom
Filtering Data (CONT’D)
✔Clear Filter
Exercise (1)
1. Create the following sheet.
Exercise (1)(CONT’D)
2. Calculate the Total for each employee. Use Autofill option.
(Total = Weekly Hours * Rate of Pay)

Type in =B2*C2 and

press Enter

Hold and drag down to

apply the formula to the
rest of the cells
 Exercise (1)(CONT’D)
3. Sort Employees’ names in an ascending order.

Click on any cell in Click Sort & Filter

the column you Button then Sort A to Z
want to sort
 Module 1: Introduction to Excel
HANDLING EXCEL DATA: FORMULAS & FUNCTIONS
Excel Formulas
✔Adding Numbers
1. Type in the following:

2. To add the two numbers :

Type =A2+A3 in cell A4
Press Enter
Excel Formulas (CONT’D)
✔ Subtracting Numbers:
1. Type in the following:

2. To subtract the two numbers:

Type =B2-B3 in cell B4
Press Enter
Excel Formulas (CONT’D)
✔Multiplying Numbers:
1. Type in the following

2. To multiply the three numbers :

Type =D2*D3*D4 in cell D5

Press Enter
Excel Formulas (CONT’D)
✔Dividing Numbers:
1. Type in the following
Try changing the
values of the numbers

2. To divide the two numbers :

Type =C2/C3 in cell C4

Press Enter
Functions
✔ Excel functions are used to perform mathematical calculations.

✔ Commonly used functions:

Function Example Description
SUM =SUM(A1:100) finds the sum of cells A1 through A100
AVERAGE =AVERAGE(B1:B10) finds the average of cells B1 through B10
returns the highest number from cells C1
MAX =MAX(C1:C100)
through C100
returns the lowest number from cells D1 through
MIN =MIN(D1:D100)
D100
SQRT =SQRT(D10) finds the square root of the value in cell D10
Functions (CONT’D)
✔The following table represents the grades of 10 students in 5
subjects.

Use Autofill to fill in the

Student ID column
Functions (CONT’D)
1. Calculate the sum of grades for each student.

In cell G2 type in =SUM( and select the

range of values you want to add using
mouse. Close the bracket ) and click
Enter
Functions (CONT’D)
Why do you think such an
error appeared?

Hold and drag to apply the function to

the rest of the cells
Functions (CONT’D)
2. Calculate the average of grades for each student.

In cell H2 type in =Average( and select

the range of values you want to get the
average for using mouse. Close the
bracket ) and click Enter
Functions (CONT’D)

Hold and drag to apply the function to

the rest of the cells
Functions (CONT’D)
3. Find minimum and maximum grade for each subject.

In cell B13 type in =MIN( and select the range of

values you want to get the min value for using
mouse. Close the bracket ) and click Enter
Functions (CONT’D)

Hold and drag to apply the function to the rest of the cells
Functions (CONT’D)

In cell B14 type in =MAX( and select the range of

values you want to get the max value for using
mouse. Close the bracket ) and click Enter
 Functions (CONT’D)

You can
select all
previous
functions
from here

Hold and drag to apply the function to the rest of the cells
Functions (CONT’D)
4. If you know that each
subject is out of 20
marks. Calculate the
total percentage grade
for each student.

In a distant cell, Calculate the

total of grades for the 5
subjects =5*20
Functions (CONT’D)

Rename the cell

to TotalGrade
Functions (CONT’D)

The formula for calculating the

percentage grade is
(Sum of grades/Total sum of
grades) * 100%
Functions (CONT’D)

Hold and drag to apply the formula to

the rest of the cells
Functions (CONT’D)
5. Sort students according to percentage grade.

Click Sort & Filter

Click on any cell in Button then Sort
the column you Largest to Smallest
want to sort
Functions (CONT’D)

Final Solution
Exercise (3)
1. Type in the following: Entering Data

2. If you know that Profit = Income – Entering simple

Cost. Type in the following: formula

3. Rename Profit to “Profit before

tax”
Resize
4. Change income to 25000 column

Change data
Exercise (2)- Naming a Cell
5. Move away from your work and type in
30% in cell A10. Rename the cell to
“taxrate” instead of A10.

Naming a Cell

6. Calculate the amount of tax

(tax = profit * taxrate)
Exercise (2) (CONT’D)
7. Change the format of the number to currency.

8. Calculate the profit after tax

(profit after tax = profit before tax – tax)
IF Function
✔ The IF function is one of the most popular functions in Excel

✔ Allows you to make logical comparisons between a value and what you expect.
✔ Depending on whether or not certain criteria are met, you can have the function
place different results in one or more cells in the worksheet.
✔ IF statement can have two results.
✔ The first result is if your comparison is True, the second if your comparison is False.
IF Function (CONT’D)
✔ How to write an IF Function?
If (the condition that we want, return this when true, otherwise -when false- return that)
IF Function (CONT’D)
In the example, we want to know whether or not the students have
passed the course (knowing that the condition for passing is to get 60 or
above out of 100 at FEPS)

Our IF Function should look like this:

IF(the student’s mark is more than 60, then return “Passed”,
otherwise return “Did not Pass”)
Which we can write in Excel as follows:
IF(CELL NUMBER (that has the student’s mark)>60,”Passed”,”Did not Pass”)
IF Function (CONT’D)
IF Function
(CONT’D)
✔Go to the cell
where you want
your results to show

✔type in “=” then go

to the Name Box
and look for the IF
Function as shown.
IF Function (CONT’D)
IF Function (CONT’D)
IF Function (CONT’D)
✔Press OK to get the results of your
condition

✔Then, drag down the arrow to

apply the function to the following cells
Exercise (3)
1. Use Autofill to fill in the profit before tax, tax and profit after tax for
the years 2011 and 2012

In year 2011, the company is

making loss. Tax cannot be in
negative (the tax collector
doesn’t give you money)
Exercise (3) (CONT’D)
2. Use the IF function to correct the tax formula so that when the
company is making loss, the tax will be zero.
Exercise (3) (CONT’D)

Here, you write the condition.

If profit before tax is greater than or
equal zero ( C4>=0)
Exercise (3) (CONT’D)

Here you write what will

happen if condition is
true. If company makes
profit, there will be taxes
imposed.
Tax (the cell selected) = Here you write what will happen if
Profit before tax * 30% condition is false. . If company makes
( C4 * taxrate ) loss, there will be NO taxes imposed.
Tax (the cell selected) = zero
(0)
RANDBETWEEN Function
✔Returns a random integer number between the numbers you specify.
✔Assume you want to generate random values for the grades of the
students in the table below (grades range between 0 and 20)
RANDBETWEEN Function
(CONT’D)
2

Select cell. Click on the Functions icon. A

new menu appears. Select All in the
Category drop down menu.
RANDBETWEEN Function
(CONT’D)

Scroll down till you find the function

RANDBETWEEN or type in RANDBEWTEEN
in the search field
RANDBETWEEN Function
(CONT’D)

Type in the lowest and the highest number

you want to generate the random numbers
between
RANDBETWEEN Function
(CONT’D)

Excel generates a number between the range you’ve specified

(between 0 and 20). Drag to autofill the rest of the cells.
RANDBETWEEN Function
(CONT’D)

Instead of dragging to autofill, you can Select the rest of cells you want to copy the
select the cell with the formula/function formula/function to and click Paste
you want to copy and click Copy
RANDBETWEEN Function
(CONT’D)
✔You can use this copy/paste
method to autofill
formulas/functions instead of
the dragging method.
✔One of the properties of the
RANDBETWEEN function is
that the numbers change with
every modification you make
in the excel sheet
RANDBETWEEN Function
(CONT’D)
To stop the numbers from changing every
time you modify something in the sheet,
select the cells with the numbers and click
Copy

Click the small arrow on the Paste icon

and choose Paste Values. The numbers
will be stored with the current values
and will stop changing
 Module 1: Introduction to Excel
HANDLING EXCEL DATA: CELL REFERENCING
Cell Referencing
✔There are three types of cell
referencing:
1. Relative Referencing.
2. Fixed Referencing.
3. Mixed Referencing.
Relative Referencing
✔By default, a cell reference is relative.

The cell references in the formula A4+B4

or function change to reflect the
function's new location A6+B6
Absolute Referencing
We put the dollar sign $ before the
cell we want to fix.
In this case cell B3 (10).
In formula bar add $ before B and 3 to
become $B$3

As you continue to copy the

formula down, the $B$3 will
not change because it is an
absolute reference.
A5+$B$3
( 3 + 10 )
A7+$B$3
( 5 + 10 )
Mixed Referencing
A mixed cell reference is a
combination of relative and absolute
cell references.
In this case we want to fix the column
letter B. In formula bar add $ before B
to become $B3
As you continue to copy
the formula down, the
column value will remain
fixed
A5+$B5
( 3 + 30 )
A5+$B7
( 5 + 50 )
 Mixed Referencing (CONT’D)
Here, we want to fix the row
number 3. In formula bar add $
before 3 to become B$3

As you continue to copy

the formula down, the A5+B$3
row value (10) will remain ( 3 + 10 )
fixed

A7+B$3
( 5 + 10 )
Exercise (2)
✔Add a new row to the below
table
Insert new row

Convert to text
Auto-fill

✔Type in year 2008 in B1 and

change it into text.
✔Use Autofill to fill till year 2011.
Exercise (2)(CONT’D)
✔If income and costs increase by
20% in 2009 from their figure in
2008. Calculate figures in 2009
using formula.
Type in
formula
Instead of
dragging, you
can click Copy.
Then click Enter
in the cell you
want to copy
the formula to
Exercise (2) (CONT’D)
✔Calculate Profit Before Tax, Tax and Profit After Tax for 2009 (formulas are
the same as 2008).

Select the cells that

contain the formulas
you want to copy.
Click Copy. In cell C4
press Enter
Exercise (2)(CONT’D)
✔If income and costs increase by 50% in 2010 and 2011 from their figure in
2008. Calculate figures for 2010 and 2011.

Using Fixed
Referencing
Exercise (2)(CONT’D)

Using Mixed
Referencing
Exercise (2)(CONT’D)

Use Autofill to complete the missing data

 Module 1: Introduction to Excel
HANDLING EXCEL DATA: PREPARING EXCEL SHEETS
PAGE LAYOUT
Page Layout

To be able to see the different options and their

impact on our sheet, click on the page layout icon
Page Layout: Data Direction
✔Excel’s default is
Sheet Left-to-Right,
like below:
To Switch, You
can use this Icon
Page Layout: Data Direction
(CONT’D)
✔But it can also be
displayed from Right-
to-Left, like the
following:
Page Layout: Gridlines
Page Layout- Headings
 Module 1: Introduction to Excel
HANDLING EXCEL DATA: PREPARING EXCEL SHEETS
PAGE SETUP
Page Setup: Margins
✔After 1
opening
Page
Layout
View: 2
Page Setup: Margins
(CONT’D)
Page Setup: Orientation
1

2
Page Setup: Size
1

2
 Module 1: Introduction to Excel
HANDLING EXCEL DATA: PREPARING EXCEL SHEETS
PRINTING AREA
Setting Print Area
1

✔Note that you must first

select the data before 2
clicking on “Set Print
Area”
Print Area
✔As clarified below, the
borders of the Print Area is
now marked.
✔With Gridlines, it looks
like the borders are in
bold.
Print Area (CONT’D)
✔Without Gridlines, we can
note the borders are now
marked, while everything else
still does not have any borders
or grids in any way.
Clearing Print Area
Adding to Print Area
✔We can also
add more cells to
the Print Area by
selecting new
cells and then
clicking on “Add
to Print Area”:
Page Break Preview
✔The Page
Break Preview
can help us
better identify
the Print Area:
 Module 1: Introduction to Excel
HANDLING EXCEL DATA: PREPARING EXCEL SHEETS
PRINTING TITLES
Print Titles
1

2
Print Titles (CONT’D)
Click on the row you are interested in repeating, so Excel
can help you reference it:
Print Titles (CONT’D)
More Options for Printing
Print Preview

You can also access Print

Preview through:
File > Print
Print Preview (CONT’D)
Print Preview (CONT’D)
✔I saved it as a
PDF to give you a
clearer view of
how the sheet will
look like after
being printed:
Bonus: Page Breaks
✔Without setting 1
the Print Area, we
can print all of our
Excel Sheet, and we
2
might be interested
to look at the
different pages:

Page Break Preview