Introduction to
Spreadsheet
STID 1103 – COMPUTER APPLICATION IN MANAGEMENT
2
Introduction to
Spreadsheet
• Designed to provide a digital form of the
paper-based worksheet .
• It is arranged in the rows and columns of
a grid and can be manipulated and
used in calculations.
• Most popular uses of the personal
computer. Microsoft Excel and Lotus 1-2-
3 are among the most popular
spreadsheet applications.
3
Introduction to
Spreadsheet
• The functions such as:
Numerous rows and columns for data
and values storage
Support for mathematical formulas and
calculations
Data sorting and analysis
Multiple worksheets and their interlinking
Integration and visualization of data in
the form of graphs and charts
Advantage Worksheet 4
• The data can be entered quickly and
accurately
• Recalculation can be done easily and
quickly
• Can do with simple charts
• Able to share information with other users
• Can produce a new worksheet easily
• Able to manage important information in
a more orderly
Excel’s Window Elements
Title bar Panel Tab
Name box Formula Bar Toolbar
cell
Column
Row
Navigation Tab Tab Sheet
Basic Formula 6
• Excel allows us to create numerous of
formulas.
• Example multiplication, addition,
subtraction and other methods.
• Below is a part of reference formula
panel in Excel
7
Entering Data
Type of data
Text
Decimal Auto Sum
Number
Date
Time
Formula
Function
Comma
Currency
Percent
Relative and Absolute Reference
When user create a formula, it must refer from another cells.
This cell reference can either relative or absolute reference or a mixed
reference
• Relative reference – A1
• Absolute reference – $A$1
• Mixed reference – A$1 or $A1
9
Relative and Absolute Reference
10
Creating Cell Name
• In Microsoft Excel, user can name a
single cell or a range of cells to make
formulas easier to read and
remember.
• If the user have row and column
labels on a worksheet, user can refer
to them directly in a formula,
• or user can use them as names that
represent the associated cells.
• User can also create a name that
represents the same cell or range of
cells on more than one worksheet.
11
Creating Cell Name
• Steps
1. Select the cell, range of cells, or
nonadjacent selections that you want
to name.
2. Click the Name box at the left end of
the formula bar.
3. Type the name for the cells.
4. Press ENTER.
5. Note: You cannot name a cell while
you are changing the contents of the
cell.
12
Formula and Functions
• An equals sign ( = ) as the first character in
a cell that contains a formula.
• The = sign tells excel that the contents of
Priority Ordering
the cell is a formula
• Without the = sign, the formula will not • Power or equivalent in brackets: ^ and ( )
calculate anything. It will simply display the • Multiply and divide : * and /
text of the formula • Add and minus : + and -
The formula icon Operation Formula Explanation
^ Power =A1^3 Value in cell A1 power 3
+ Add =A1+A2 Total A1 and A2
- Minus =A1-A2 Value in cell A1 minus A2
* Multiply =A1*3 Value in cell A1 multiple with 3
/ Divide =A1/50 Value in cell A1 divide 50
Mix =(A1+A2+A3)/3 Average total value in cell A1, A2 and A3
Explicit values and cell The SUM Function 13
references
Examples
• Formula with only cell references:
=a1*b1 Function Result
=SUM(1,2,3,4,5) 15
• Formula with only literal values:
=SUM(a1,b1,c1) a1+b1+c1
=100/27
=SUM(9,a1,b2,5,c1) 9+a1+b2+5+c1
• Formula with both cell references and
literal values:
=a1/100
STANDARD LOOKUP
14
IF / AND / OR VLOOKUP
For conditionals and control flow Easier to use than index match,
but there are limitations hence ..
IFERROR
Used commonly to silence errors INDEX(MATCH)
and show a blank cell instead
The most used formula SUM / COUNT / AVERAGE / SUMIFS
in Excel 2016 / COUNTIFS
TEXT MANIPULATION ADVANCE
LEFT (LEN) ARRAY FORMULA
Used for cutting long texts Makes the spreadsheet neater and more efficient (I
MID (SEARCH) hesitate to put it here, since it is not really a function
Used for extracting sub strings from but a wrapper in excel.)
text
INDIRECT
SUBSTITUTE Used to create formulas with dynamic referencing
15
Standard formula - IF, IFERROR, IF(AND
, IF(OR
16
LOOKUP Function – VLOOKUP and
HLOOKUP
VLOOKUP HLOOKUP
17
Text Manipulation (SUBSTITUTE)
18
Array Function and Formula
19
INDIRECT Function
20
Sorting is any process of arranging items systematically,
and has two common, yet distinct meanings:
a. ordering: arranging items in a sequence ordered by
some
Sorting and Querying b. criterion: categorizing: grouping items with similar
properties.
The most common uses of sorted sequences are:
• making lookup or search efficient;
• making merging of sequences efficient.
• enable processing of data in a defined order.
How to sort in Excel?
21
• Select the data to sort
Select a range of tabular data, such as
A1:L5 (multiple rows and columns) or
C1:C80 (a single column). The range can
include the first row of headings that
identify each column.
• Sort quickly and easily
Select a single cell in the column user want
to sort. On the Data tab, in the Sort &
Filter group, click to perform an
ascending sort (from A to Z, or smallest
number to largest).
Click to perform a descending sort
(from Z to A, or largest number to smallest).
How to sort in Excel? 22
• Sort by specifying criteria
Use this technique to choose the column
you want to sort, together with other
criteria such as font or cell colors.
Select a single cell anywhere in the range
that you want to sort.
On the Data tab, in the Sort & Filter group,
click Sort to display the Sort popup window.
In the Sort by dropdown list, select the first
column on which you want to sort.
In the Sort On list, choose Values, Cell
Color, Font Color, or Cell Icon.
In the Order list, choose the order that you
want to apply to the sort operation—
alphabetically or numerically, ascending or
descending (that is, from A to Z (or Z to A)
for text, or lower to higher, or higher to
lower for numbers).
Example of Sorting and 23
Querying
From the figures, there are 18 peoples
with 17 peoples suffered abrasions. Try
to get information above.
First user will need to specify the data
to be sorted.
To sort the whole of the data use Ctrl A
to select all the data.
Otherwise highlight the section to be
sorted.
Click on the Sort and Filter icon.
Then select Custom Sort.
Example of Sorting
24
• A dialogue box will appear
• Select ‘injuries’ from the pull down list.
Make sure that My data has headers is
selected, then click on OK.
Exercises
• How many people aged 60 and over
suffered accidents?
• How many accidents were there in
September?
• How many accidents involved a car?
25
Sort
Generating a Pivot Table
26
Pivot Table: an interactive worksheet that
allows you to summarize large amounts of
information.
(The graphical representation of the Pivot
Table is a Pivot Chart.)
Data suitable for Pivot Table analysis:
•columns are considered fields,
•column headings are field names, and
•rows are records.
Important: data should have no blank columns or rows
27
Pivot Table
Creating Dynamic
28
Dashboard
• The dashboard also demonstrates the
standard approach I use in all of my
models which is to incorporate three key
sheets in addition to the data and analysis
tabs.
• Integrates information from multiple
sources and presents it in a unified,
understandable format as charts and
graphs
• Offers up-to-the minute snapshots of
information
• Assists decision makers in identifying trends
and potential problems
• These are:
Home page
Inputs (or drivers)
Helpsheet
29
Spreadsheet to support
Decision Making
• An information system that supports business or
organizational decision-making activities
• Example:
• Online Management Training Inc. (OMT Inc.),
sells online management training books and
streaming online videos to corporations and
individuals
• Records of online transactions can be
analyzed using Excel to help business
decisions, e.g.:
• Where do most customers come from?
• Where are average purchases higher?
• What time of day do people buy?
• What kinds of ads work best?
30
Tips and Tricks Microsoft Excel
31
Tips and Tricks Microsoft Excel
(advance)
STID1103 – Computer Application in
Management
Thank You
