Business Analytics: Methods,

Models, and Decisions

Third Edition, Global Edition

Chapter A1
Basic Excel Skills

Copyright © 2021 Pearson Education Ltd. Slide - 1

Basic Excel Skills
• Opening, saving, and printing files

• Using workbooks and worksheets

• Moving around a spreadsheet

• Selecting cells and ranges

• Inserting/deleting rows and columns

• Entering and editing text, data, and formulas

• Formatting data (number, currency, decimal)

• Working with text strings

• Formatting data and text

• Modifying the appearance of a spreadsheet

Copyright © 2021 Pearson Education Ltd. Slide - 2

Excel 2016 Ribbons
Windows

Mac

Copyright © 2021 Pearson Education Ltd. Slide - 3

Excel Add-Ins
• Analysis Toolpak (Data Analysis), which contains a
variety of tools for statistical computation
• Solver, which is used for optimization
– If they do not appear in the Data tab, see the
instructions in Appendix A1.

Copyright © 2021 Pearson Education Ltd. Slide - 4

Excel Formulas
• Common mathematical operators are used.
– addition (+)
– subtraction (−)
– multiplication (*)
– division  / 
– exponentiation   

Copyright © 2021 Pearson Education Ltd. Slide - 5

Relative and Absolute References
• Cell references can be relative or absolute. Using a dollar sign before
a row and/or column label creates an absolute reference.
– Relative references: A2, C5, D10
– Absolute references: $A$2, $C5, D$10
• Using a $ sign before a row label (for example, B$4) keeps the
reference fixed to row 4 but allows the column reference to change if
the formula is copied to another cell.
• Using a $ sign before a column label (for example, $B4) keeps the
reference to column B fixed but allows the row reference to change.
• Using a $ sign before both the row and column labels (for example,
$B$4) keeps the reference to cell B4 fixed no matter where the
formula is copied.

Copyright © 2021 Pearson Education Ltd. Slide - 6

Example A1.1: Implementing Price-
Demand Models in Excel
Two models for predicting demand as a function of price
Linear

D  a  bP
Formula in cell B8:
 $ B$4  $ B$5*$ A8
Nonlinear
D  cP  d
Formula in cell E8:
 $ E $4* D8 ^ $ E $5

Note how the absolute addresses are used so that as these formulas are
copied down, the demand is computed correctly.
Copyright © 2021 Pearson Education Ltd. Slide - 7
Copying Formulas
Formulas in cells can be copied in many ways.
• Use the Copy button in the Home tab, then use the
Paste button
• Use Ctrl - C, then Ctrl - V
• Drag the bottom right corner of a cell (the fill handle)
across a row or column

Copyright © 2021 Pearson Education Ltd. Slide - 8

Other Useful Excel Tips
• Split Screen
• Column and Row Widths
• Displaying Formulas in Worksheets
• Displaying Grid Lines and Column Headers for
Printing
• Filling a Range with a Series of Numbers

Copyright © 2021 Pearson Education Ltd. Slide - 9

Basic Excel Functions
• =MIN(range)

• =MAX(range)

• =SUM(range)
• =AVERAGE(range)
• =COUNT(range)
• =COUNTIF(range,criteria)
– Excel has other useful COUNT-type functions: COUNTA counts the number of
nonblank cells in a range, and COUNTBLANK counts the number of blank cells in
a range. In addition,
COUNTIFS(range1,criterion1,range2,criterion2,...range_n,criterion_n)
finds the number of cells within multiple ranges that meet specific criteria for each
range.

Copyright © 2021 Pearson Education Ltd. Slide - 10

Example A1.2: Using Basic Excel
Functions

Copyright © 2021 Pearson Education Ltd. Slide - 11

Other IF-Type Functions
• SUMIF, AVERAGEIF, SUMIFS, and AVERAGEIFS
can be used to embed IF logic within
mathematical functions.
• For instance, the syntax of SUMIF is
– SUMIF(range, criterion, [sumrange]). “Sum range” is
an optional argument that allows you to add cells in a
different range.

Copyright © 2021 Pearson Education Ltd. Slide - 12

SUMIF Example
• In the Purchase Orders database, to find the total
cost of all airframe fasteners, use
=SUMIF(D4:D97,"Airframe fasteners", G4:G97)

Copyright © 2021 Pearson Education Ltd. Slide - 13

Functions for Specific Applications
• Net Present Value (or discounted cash flow) measures the worth of a
stream of cash flows, taking into account the time value of money.

n ft
NPV  
t 0 1  i t
• Excel function:
=NPV(rate,value1,value2,...)
– F is the cash flow ($).
– Rate (i) is the discount rate.
– value1,value2,... are equally-spaced payments or
income values.

– t is a time period.

Copyright © 2021 Pearson Education Ltd. Slide - 14

Example A1.3: Using the NPV
Function
Cell B8:
=NPV(B6,C4:H4)-B5

Copyright © 2021 Pearson Education Ltd. Slide - 15

Insert Function
• Click the Insert

function button f x .
• You may type in a
description or
search.

Example for
COUNTIF
function:
Copyright © 2021 Pearson Education Ltd. Slide - 16
Date and Time Functions
• Excel can display a date in a variety of formats, such
as2/14/17 or 14-Feb-17. Choose the standard date
format  for example, 2/14/17  by selecting Date in
the Number formatting box or select a custom format by
selecting Custom in the Number box.
• DATEDIF(startdate, enddate, time unit )
– Time unit can be “y”, “m”, or “d”.
– DATEDIF(4/26/89, 2/14/17, “y”) will return 27
(years), while DATEDIF  4/26/89, 2/14/17, “m” 
will return 333 (months).
Copyright © 2021 Pearson Education Ltd. Slide - 17
Range Names
• A range name is a descriptive label assigned to a
cell or range of cells. There are several ways to
create range names in Excel.
– Name box
– Create from Selection
– Define Name

Copyright © 2021 Pearson Education Ltd. Slide - 18

Example A1.5: Using the Name Box

• Define range names for each of the numerical cells

that correspond to the labels on the left. That is, we
will name cell B3 Fixed cost, cell B4 Unit variable cost,
and so on. Click on cell B3; in the Name box,
type the name Fixed_cost (note the underscore;
blanks are not permitted), and then press Enter.
Copyright © 2021 Pearson Education Ltd. Slide - 19
Example A1.6: Using Create from
Selection

• Use the text labels to the left of the numerical

inputs as the range names. First, highlight the

range A3:B5. Then, on the Formulas tab, choose

Create from Selection. The box for the left column
will automatically be checked. Click OK.
Copyright © 2021 Pearson Education Ltd. Slide - 20
Example A1.7: Using Define Name

• Select cell B3. Click Define Name on the Formulas

tab. This will bring up a dialog that allows you to
enter a range name. Click OK.

Copyright © 2021 Pearson Education Ltd. Slide - 21

Name Manager

• Displays a summary of range names for editing. (Note:

The Name Manager button in the Formulas tab is only
available in Windows. On a Mac, click Define Name to see
a list of range names.)
Copyright © 2021 Pearson Education Ltd. Slide - 22
Applying Range Names
• This replaces the cell references by the names. Click on
the drop-down arrow next to Define Name and select

Apply Names . . .. Select all the names you wish to use

and click OK. In the figure, the original formula for
cell B7(=B3+B4*B5) now displays the names.

Copyright © 2021 Pearson Education Ltd. Slide - 23

VALUE Function
• It’s not unusual to download data from the Web that
looks numerical but is actually expressed as text; this
is often true of data from U.S. government Web sites.

•
VALUE text  converts text data to numerical values.

Copyright © 2021 Pearson Education Ltd. Slide - 24

Paste Special
• Copy the range of cells of
interest and click on the cell
where you want to paste the
results. Examples:
– To paste only the values in
cells (not the formulas),
select Values and then click
OK.
– To transpose data in
columns to rows and vice
versa, use Transpose.

Copyright © 2021 Pearson Education Ltd. Slide - 25

Example A1.8: Currency Conversion
• Assume that 1 euro = $1.117.
To convert euros into U.S.
dollars, first copy the data to
column C. Select cell C3
corresponding to the
conversion factor. Next, select
the range of data in column C
and open the Paste Special
dialog. Select Multiply.

Copyright © 2021 Pearson Education Ltd. Slide - 26

Concatenation
• To concatenate means to join. In many applications, you
might wish to take text data that are in different columns
and join them together (for example, first and last
names). The Excel function

CONCATENATE(text1, text 2, . . . , text 30) can be

used to join up to 30 text strings into a single string.
– Suppose that cell A1 contains the last name Smith, and
cell B1 contains the first name John. Then
CONCATENATE(B1," ",A1 ) will result in the text
string John Smith.
Copyright © 2021 Pearson Education Ltd. Slide - 27
Error Values
• #DIV/0! -A formula is trying to divide by zero.
• #N/A -“Not available,” meaning that the formula could not return
a result.
• #NAME? -An invalid name is used in a formula.
• #NUM! -An invalid argument is used in a function, such as a negative
number in SQRT.
• #REF! -A formula contains an invalid cell reference.

• #VALUE! -Excel cannot compute a function because of an invalid

argument.

Copyright © 2021 Pearson Education Ltd. Slide - 28

IFERROR Function
• IFERROR(value, value_if_error) displays a

specific value if an error is present.

• For example, if you are computing A1/ B1,
then if
B1 is zero or blank, then
= IFERROR(A1/B1, “ ”)
will display a blank cell instead of #DIV/0!;

Copyright © 2021 Pearson Education Ltd. Slide - 29