false

Class Notes
(834,627)

Canada
(508,661)

University of Ottawa
(33,386)

Administration
(2,694)

ADM1370
(108)

Umar Ruhi
(38)

Lecture

Unlock Document

Administration

ADM1370

Umar Ruhi

Winter

Description

Lecture 9
Why are we learning Excel?
Why Learn Excel?
Excel is the most popular spreadsheet tool today.
Excel has been regarded as the true “killer app” by many experts.
You probably already use or will soon be using Excel at work, and possibly at
home.
Excel can be used for simple data management and problem-solving, as well
as complex decision making
From a learning standpoint: excel can be used to cultivate Critical Thinking
and Decision Making Skills
Based on Robert Reich (former US Secretary of Labour):
Four Skills required for future job roles:
A good information Systems education is
comprehensive across these skills
Introduction to Microsoft Excel
Microsoft Office Excel 2010 (Excel):
A computer program used to enter, analyze, and present
(quantitative) data
A spreadsheet program
A spreadsheet is a collection of text and numbers laid out in a
rectangular grid.
Often used in business for accounting, budgeting, financial analysis,
inventory management and other functions.
An electronic spreadsheet program such as Excel aids a multitude of problem-
solving & decision-making processes through providing:
data management features
automatic calculation functions
presentation tools
decision analysis functions
Worksheets and Workbooks
Excel
a computerized spreadsheet application used to build and manipulate
worksheets and workbooks
Worksheet
a spreadsheet that may contain data including text, numbers, formulas,
charts etc.
Sometimes a charts based worksheet is referred to as a “Chartsheet”
Workbook
a collection of related worksheets within one file
ADM1370 QUIZ #2 1 Basic Components of the Excel Interface
File Tab:
Consists of file operations commands such as opening, closing, saving,
printing, and sharing files.
The options and features available here are part of the “Backstage view”
Tabs & Ribbon:
Each tab corresponds to sets of features displayed horizontally as a ribbon.
A ribbon consists of groupings, and controls.
Tabs are designed to be task-oriented and consist of several logical groupings
of controls that perform similar functions.
Quick Access Toolbar:
Contains controls / commands that are most commonly used. Additional
controls can be added through Excel Options settings through the Office
Button.
Select All Button:
Used to select all elements of the worksheet.
Status Bar:
Displays information about a selected command or operation in progress.
Also displays basic summary information about a selected range of values.
HANDY HACK
You can customize the status bar to show the types of summary
information you are interested in.
Just right-click the status bar and select the options you wish to use.
Planning for Good Workbook and Worksheet Design
Plan before you start entering data
Decide on the purpose of the spreadsheet and how it will be constructed
Make it obvious where data is to be entered:
Use titles, headings, instructions, color schemes, and forms to designate
areas for data-entry.
Wherever possible, setup formulas and use cell references for calculations
Allow Excel to do what it was designed for, i.e., automatic calculations
o Try to minimize hard-coded numbers in your calculations.
Test multiple times to make sure the results are what you expect:
Check your results against your mental model of what the results should be.
Format the worksheet so it looks aesthetically pleasing.
Document the worksheet as thoroughly as possible:
Provide summaries or comments in a separate worksheet including ranges
being used for calculations and analysis.
Save your work regularly:
Modify the default save intervals if necessary.
Cells, Ranges, and References
Each cell is referenced by its intersecting column (letter) and row (number):
2 ADM1370 QUIZ #2 e.g. cell A1 is the topmost left cell in the spreadsheet.
The cell reference is shown in the name box beside the formula bar.
A range is a rectangular group of cells in a worksheet:
can be one single cell or may be entire worksheet.
Selecting a range:
Click and hold left mouse button and drag from beginning of range to end
Select first cell, then hold the Shift key while clicking the last cell
Can consist of contiguous (together) or non-contiguous (not together) cells:
Hold the Ctrl key to select non-contiguous ranges.
A contiguous range of cells is referenced by the top left cell reference and
the bottom right cell reference:
e.g. B2:D5
Entering & Editing Data
Entering Data in Cells:
Text, Numbers, Formulas can be entered either directly in each cell or
through the formula bar.
Editing Data in Cells:
Data in cells can be modified using one of three ways:
Selecting the cell you want to edit, clicking the Formula Bar, making
changes, pressing Enter or the Check symbol.
Double clicking the cell to be edited, making the changes, pressing Enter
Selecting the cell, pressing the F2 key, making the changes, pressing Enter
Auto-Fill Feature:
Used to repetitively copy contents of one cell or a range of cells.
Used to complete a sequence, pattern or lists like numbers, increments,
months
Enables you to copy or continue the content of a cell or a range of cells to its
adjacent cells (below or to the right).
Select a cell or range of cells to be copied or continued,
Drag the “fill handle” over an adjacent cell or range of cells
The fill handle is a small black square appearing in the bottom-right
corner of a cell
HANDY HACK:
You can define your own custom lists in Excel to give more power to the
Auto-Fill feature.
Use the Excel Options control in the backstage view and look under
Advanced Edit Custom Lists.
Using Formulas
Formulas are used to perform operations and arrive at a calculated result.
A formula is an expression that returns a value through performing
operations on literal values specified in the formula itself or referenced
values from other cells
Must begin with an equals (=) sign.
ADM1370 QUIZ #2 3 Formulas mostly contain mathematical operators.
Used to automate calculations that were once done manually.
Rather than typing a cell address, use an alternative method that involves
minimal typing:
Pointing uses the mouse or arrow keys to select the cell directly when
creating a formula.
Auto-Fill feature can also be used to duplicate formulas:
Formula references are updated according to the target cell for the formula
results.
What are they?
A formula is an expression that returns a value through performing
operations on literal values specified in the formula itself or referenced values
from other cells
The most commonly used operators are arithmetic operators
Complex Formulas:
Formulas follow “order of precedence” (BEDMAS)
A set of predefined rules used to determine the sequence in which
operators are applied in a calculation
Copying and Pasting Formulas
With formulas, Excel adjusts the formula’s cell references to reflect the new
location of the formula in the worksheet
To view formulas in a workbook, switch to formula view:
press the Ctrl and ~ keys
A view of the workbook contents that displays formulas instead of the
resulting values
Using Functions
What are they?
A function is a named operation (predefined formula) that returns a value.
A function take values (literal values or referenced values), performs
operations, and returns results:
The values are passed to the function as parameters enclosed in brackets.
For example, to add the values in the range A1:A5,
You could enter the following long formula:
=A1+A2+A3+A4+A5
OR
You could use the SUM function to accomplish the same thing by entering:
=SUM(A1,A2,A3,A4,A5)
=SUM(A1:A5)
Creating Effective Formulas:
Do not embed important data in a formula (rather, put that data in a separate cell)
Using referenced values is better than using literal values
This prevents hiding important data
This prevents easily changing important data without the need to edit
formulas
4 ADM1370 QUIZ #2 Keep formulas simple
Sometimes it’s useful to break formulas into intermediate results
Ranges and Excel Tables
A range of cells with column headings (arranged as a flat table) is a good
starting point for analysis of data in the range.
An Excel Table can enhance the presentation and facilitate sorting and
filtering of data in the ranges.
Steps to Convert a Range into an Excel Table:
Make sure the range is arranged as a flat table with column headings.
Click anywhere inside the range, and click the Table button in the Insert tab.
Dropdown handles with every column heading allow the use of sorting and
filtering functions.
Steps to Convert an Excel Table back into a Range:
Click anywhere in the excel table.
Select: Table Tools Design Convert to Range (Table Tools)
Sorting and Filtering
Sort data in ascending or descending order
Filter data to display the portion that meets the criteria specified
Data can be sorted or filtered by selecting the sorting arrow filtering arrow.
ADM1370 QUIZ #2 5 Lecture 10
Relative & Absolute Cell References
Relative Cell References:
Cell addresses that are adjusted as a formula containing these addresses is
copied within the spreadsheet
Used by default in most spreadsheet calculations
e.g. B6 copied over two columns to the right will become D6
e.g. K12 copied over one column to the left and one row above will become J11
Absolute Cell References:
Cell addresses that stay fixed & are not adjusted as a formula is copied
Used when the formulas should always use the value in a particular cell
Used for constant values (e.g. sales tax rate etc.)
e.g. $B$6
Mixed Cell References:
Cell addresses where either the row or the column is fixed
e.g. $B6 refers to a cell address where the column is fixed to B but the row
can increase or decrease as the formula is copied vertically
e.g. J$11 refers to a cell address where the column can vary as the formula is
copied horizontally but the row is fixed to 11.
Data Delivery Functions in Excel
Data Delivery functions do not perform calculations per say:
These functions can be used to verify data, search for data, transform data or
deliver data from one point to another.
e.g. Filter Unique Values:
Used to filter and deliver unique values from a list
e.g. Data Validation & Drop-Down Menus:
Used to allow end-users to select from a predefined lists of values
e.g. Lookup Functions:
Used to retrieve values from tables based on lookup values
Using Lookup Tables and Functions
A lookup table is a table that organizes data you want to retrieve into
different categories
The categories for the lookup table, called compare values, are located in the
table’s first column or row
To retrieve a particular value from the table, a lookup value (the value you
are trying to find) needs to match the compare values
=VLOOKUP(lookup_value,table_array,col_index_num[,range_lookup])
=HLOOKUP(lookup_value,table_array,row_index_num[,range_lookup])
The last parameter (shown in square brackets) is optional:
By default, it is assumed that you need a closest match to the compare value
Set this parameter to FALSE if you need an exact match to the compare value.
6 ADM1370 QUIZ #2 Lecture 11
More Examples of Functions:
Statistical Functions
=AVERAGE(number1,number2,..)
Excel sums the values in the range and then divides by the number of
non-blank cells in the range
=STDEV(number1,number2,..)
=MAX(number1,number2,..)
=MEDIAN(number1,number2,..)
=PEARSON(number1,number2,..)
Pearson correlation coefficient
Counting Functions
=COUNT(range)
Counts the number of cells that contains numbers
=COUNTA(range)
Counts the number of cells that are not empty
Both numeric and text entries are included
=COUNTBLANK(range)
Counts the number of empty cells
=COUNTIF(range,criteria)
Counts the number of cells within a range that meets the condition
Summarizing Data Through Subtotals & Grouping
Subtotals:
Excel provides a number of features that enable you to organize large groups
of data into more manageable groups.
Data in a list can be summarized by inserting a subtotal.
Before you can subtotal, however, you must first sort the list by the field on
which you want the list subtotaled.
Grouping:
If you have a list of data that you want to group and summarize, you can
create an outline.
Grouping refers to organizing data so that it can be viewed as a collapsible
and expandable outline.
To group data, each column must have a label in the first row and the column
must contain similar facts. The data must be sorted by the column or
columns for that group.
Problem-Solving & Modeling Process
Phases in Problem-Solving & Modeling Process:
Formulation
Transformation of a real problem scenario into a mathematical model
ADM1370 QUIZ #2 7 Solution
Solving the model to obtain the optimal solution
Interpretation
Analyzing results and implementing solution
Modeling Approach to Problem-Solving
Models are usually simplified versions of the things they represent:
a valid model accurately represents the relevant characteristics of the object
or decision being studied
Types of models:
Mental (e.g. arranging furniture)
Visual (e.g. blueprints, road maps)
Physical/Scale (e.g. aerodynamics, buildings)
Mathematical (e.g. financial analysis)
A Computer Model:
A set of mathematical relationships and logical assumptions implemented in a
computer as an abstract representation of a real-world object or phenomenon.
Spreadsheets provide the most convenient way for business people to build
computer models.
Benefits of the Modeling Approach:
Economy:
It is often less costly to analyze decision problems using models.
Timeliness:
Models often deliver needed information more quickly than their real-
world counterparts.
Feasibility:
Models can be used to do things that would be impossible.
Models give us insight & understanding that improves decision making.
Example of a Mathematical Model:
Specifically:
Profit = Revenue – Expenses
Or
Profit = f (Revenue, Expenses)
Or
Y = f (1 ,2X )
Generically:
Y = f(X 1 X 2…,X n
Y = dependent variable
Xi= independent variables (inputs having an impact on Y)
f() = function defining the relationship between the X & Y
i
Most spreadsheet models are very similar to the generic mathematical model:
= f(X 1 X 2…,X )n
Most spreadsheets have input cells (representing X) toiwhich mathematical
functions ( f()) are applied to compute an output variable (or Y).
8 ADM1370 QUIZ #2 Examples of Functions
Financial/Annuity Functions
An annuity is a series of constant cash payments made over a continuous period
A car loan or a mortgage is an annuity
Benefits received each period is an annuity
Cash you pay out is represented by a negative number; cash you receive is
represented by a positive number
A $1,000 deposit to the bank would be represented by the argument -
1000 if you are the depositor
A $1,000 deposit to the bank would be represented by +1000 if you are
the bank
Future Value Function:
Syntax
=FV(rate,nper,pmt,pv,type)
Rate
Interest rate per period – e.g. 8 percent annual interest rate
Nper
Total number of payment periods in an annuity – e.g. 10 years
Enter 10 into the formula for nper
Pmt
Payment made each period
Cannot change over the life of the annuity
Pv
(optional) present value or the lump-sum amount that a series of future
payments is worth right now
Type
(optional) the number 0 or 1 indicates when payments are due. If type is
omitted, it is assumed to

More
Less
Related notes for ADM1370

Join OneClass

Access over 10 million pages of study

documents for 1.3 million courses.

Sign up

Join to view

Continue

Continue
OR

By registering, I agree to the
Terms
and
Privacy Policies

Already have an account?
Log in

Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.