Module 4:

Microsoft Excel in Business

Introduction to MS Excel
MS-EXCEL is a part of Microsoft Office suite software. It is an electronic spreadsheet with
numerous rows and columns, used for organizing data, graphically representing data(s), and
performing different calculations. It consists of 1048576 rows and 16384 columns; a row and
column together make a cell. Each cell has an address defined by column name and row number
example A1, D2, etc. This is also known as a cell reference.
What is MS Excel
Microsoft Excel is a software application designed for creating tables to input and organize data.
It provides a user-friendly way to analyze and work with data. The image below provides a visual
representation of what an Excel spreadsheet typically appears like

Features of MS Excel
 Ribbon
The Ribbon in MS-Excel is the topmost row of tabs that provide the user with different
facilities/functionalities. These tabs are:
  Home Tab
It provides the basic facilities like changing the font, size of text, editing the cells in the
spreadsheet, autosum, etc.
 Insert Tab
It provides the facilities like inserting tables, pivot tables, images, clip art, charts, links, etc.
 Page layout
It provides all the facilities related to the spreadsheet-like margins, orientation, height, width,
background etc. The worksheet appearance will be the same in the hard copy as well.
 Formulas
It is a package of different in-built formulas/functions which can be used by user just by selecting
the cell or range of cells for values.
 Data
The Data Tab helps to perform different operations on a vast set of data like analysis through
what-if analysis tools and many other data analysis tools, removing duplicate data, transpose the
row and column, etc. It also helps to access data(s) from different sources as well, such as from
Ms-Access, from web, etc.
 Review
This tab provides the facility of thesaurus, checking spellings, translating the text, and helps to
protect and share the worksheet and workbook.
 View
It contains the commands to manage the view of the workbook, show/hide ruler, gridlines, etc,
freezing panes, and adding macros.
What is a Cell?
A spreadsheet takes the shape of a table, consisting of rows and columns. A cell is created at the
intersection point where rows and columns meet, forming a rectangular box. Here’s an image
illustrating what a cell looks like:

What is Cell Address or Cell Reference

The address or name of a cell or a range of cells is known as Cell reference. It helps the software
to identify the cell from where the data/value is to be used in the formula. We can reference the
cell of other worksheets and also of other programs.
 Referencing the cell of other worksheets is known as External referencing.
 Referencing the cell of other programs is known as Remote referencing.
There are three types of cell references in Excel:
1. Relative reference.
2. Absolute reference.
3. Mixed reference.

Formatting Cells
What are Format Cells in Excel?

The Excel's Format Cells feature allows users to adjust or modify the formatting of one or more
cells and/or their values' appearance in the sheet, but without changing the number themselves.
The Format Cells provide various control options that enable users to change the view of the
displayed data within the cells.

We can use the Format Cells to change the date style time style, add/ remove colors in fonts or
background, insert the border of a specific style, protect the cells, and many more. Using Excel
formatting, we usually add finishing or final touches to prepare and present the data accurately.

For example, suppose we have a worksheet with extremely large data, including some dates and
prices. We can add currency signs (e.g., $) to cells containing prices and configure dates format to
represent standard dates (xx/xx/xxxx). In this way, cells' formatting helps us draw attention to
certain data and highlight essential content. However, there are various formats under the Format
Cells window.

What Is Data Validation in Excel?

Data validation is a feature in Excel which is used to control what users can enter into a cell. It
allows you to dictate specific rules. It also allows users to display a custom message if users try to
enter invalid data. Data validation in Excel is a technique that restricts user input in a worksheet.
It is often used to limit user entry.

What Is Protecting Excel Sheet?

Protect Sheet in Excel is a feature that locks the workbook using the protect option or using a
password. It ensures that no other user can make changes to our worksheet, especially in a
worksheet shared with multiple users.

The Excel Protect Sheet can be used in various ways where we can allow users to perform some
tasks but not make changes, such as they can select cells to use an AutoFilter but can’t make
changes to the structure, or select from the drop-down instead of adding or deleting data.
For example, to protect an Excel worksheet, we must select the “Review” tab – go to the “Protect”
group – click the “Protect Sheet” option, as shown below.

Then, we can complete the further steps, and protect the sheet. However, protecting using a
password is recommended.

Data analysis is a critical process that involves inspecting, cleaning, transforming, and modeling
data to discover valuable information, draw conclusions, and support decision-making. This
process can involve numerous methods, all with the goal of understanding the patterns and trends
within the data.
 A. Sort and Filter
Sort and Filter are fundamental tools in Excel for data analysis. They allow you to organize and
sift through your data easily.
1. Sort: This feature helps arrange your data in a particular order, ascending or
descending, based on one or more columns. To sort, you can select a single column’s
data and click on either “Sort Ascending” or “Sort Descending” in the “Sort & Filter”
group on the Home tab.
2. Filter: Filtering is the process of displaying only the data that meets certain criteria.
The Filter command is found in the “Sort & Filter” group on the Home tab. By selecting
this command, drop-down arrows will appear at the top of each column in your data.
Clicking these arrows allows you to set filtering criteria.
B. Conditional Formatting
Conditional formatting is a powerful feature in Excel that allows you to change the formatting
of cells based on their data. This can help to visually analyze and understand your data.
For instance, you can highlight all the cells that contain a value above a certain threshold or color-
code cells to represent different categories. To use conditional formatting, select the cells you want
to format, then choose “Conditional Formatting” in the Styles group on the Home tab.

C. Charts and Graphs

Visualizing data through charts and graphs is one of the most effective ways to analyze and
understand your data. Excel provides a wide variety of chart types, including bar charts, line charts,
pie charts, and more.
To create a chart, select your data and then choose the desired chart type from the “Insert” tab’s
“Charts” group. You can then customize the chart’s design and formatting to suit your needs.

Pivot Table:
A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see
comparisons, patterns, and trends in your data. PivotTables work a little bit differently depending
on what platform you are using to run Excel.

Creating a Pivot Table:

1. Select the cells you want to create a PivotTable from.

 Note: Your data should be organized in columns with a single header row. See the
Data format tips and tricks section for more details.

2. Select Insert > PivotTable.

3. This creates a PivotTable based on an existing table or range.

Note: Selecting Add this data to the Data Model adds the table or range being used
for this PivotTable into the workbook’s Data Model. Learn more.

4. Choose where you want the PivotTable report to be placed. Select New Worksheet to
place the PivotTable in a new worksheet or Existing Worksheet and select where you
want the new PivotTable to appear.

5. Select OK.

Introduction to What-If Analysis

What-If Analysis is the process of changing the values in cells to see how those changes will affect
the outcome of formulas on the worksheet.

Three kinds of What-If Analysis tools come with Excel: Scenarios, Goal Seek, and Data Tables.
 A. What is Goal Seek in Excel?

The Goal Seek in excel is a “what-if-analysis” tool that calculates the value of the input cell
(variable) with respect to the desired outcome. In other words, the tool helps answer the question,
“what should be the value of the input in order to attain the given output?”
For example, the cost price of a product is $15 and the business wants to earn revenue of $50,000.
The Goal Seek function can determine the number of units of the product to be sold to achieve the
given target of $50,000.

The Goal Seek changes the variable (cause) to study the variations in the result (effect). It shows
the impact of change in one value on the other value. This is why it is helpful in cause and effect
analysis.

Goal seek function in excel is used in financial modeling and forecasting scenarios.

B. A scenario is a set of input values that you can substitute in a worksheet to perform what-
if analysis. For example, you could create scenarios to show various interest rates, loan
amounts, and terms for a mortgage. Excel’s scenario manager lets you create and store
different scenarios in the same worksheet.

Use Scenario Manager

1. Select the cells that contain values that could change.

2. Click the Data tab on the ribbon.

3. Click the What-If Analysis button.
4. Select Scenario Manager.

The Scenario Manager dialog box appears with the message “No Scenarios defined.
Choose Add to add scenarios.

5. Click the Add button to add a new scenario.

6. Type a name for the new scenario.

If you already have cells selected, the Changing cells field will already be populated
with your selection. If you didn’t select cells up front, you’ll have to specify the
cells here.

7. Click OK.

The Scenario Values dialog box appears, showing each of the variable cells you
selected.

If you name the worksheet cells you're changing, the cell names appear here, making
it easy to tell what value you're working with.

8. Update any values you want to see for the given scenario.

To make sure you don’t lose the original values for the changing cells, use the
original cell values in the first scenario you create.

9. Click OK.
 The scenario is added and is listed in the Scenario Manager. If you click Add, the
Add Scenario dialog box appears again so you can add another scenario.

Repeat steps 5-9 to add all the desired scenarios.

10. Select the scenario you want to view.

11. Click the Show button.

The worksheet’s values are changed to the values you specified in the scenario. You can select any
scenario here to update the values in your spreadsheet.

Financial Functions:

1. PV Function

The PV function returns the present value of an investment. The PV function calculates the value
of a series of future payments in today’s dollars. It assumes equal payments and a constant interest
rate.

=PV (rate, nper, pmt, [fv], [type])

  rate: interest rate per period
 nper: total number of payment periods
 pmt: payment made each period
 fv: [optional] the balance after the last payment is made; default is 0
 type: [optional] when payments are due; 0 = end of period, 1 = beginning of period; default
is 0

2. FV function

The FV function returns the future value of an investment. The FV function calculates the future
value of an investment, assuming equal payments and a constant interest rate.

=FV (rate, nper, pmt, [pv], [type])

 rate: interest rate per period.

 nper: total number of payment periods.
 pmt: payment made each period. Must be entered as a negative number.
 pv: [optional] The present value of future payments. If omitted, it is assumed to be zero.
Must be entered as a negative number.
 type: [optional] When payments are due. 0 = end of period, 1 = beginning of period. The
default is 0.

3. PMT function

The PMT function is a financial function that returns the periodic payment for a loan. The PMT
function also calculates payments needed for a future value, such as an amount needed for
retirement.

=PMT (rate, nper, pv, [fv], [type])

 rate: interest rate for the loan

 nper: total number of payments for the loan
 pv: present value, or total value, of all loan payments now
  fv: [optional] future value after the last payment is made; default is 0
 type: [optional] When payments are due. 0 = end of the period. 1 = beginning of the period;
default is 0

4. RATE function

The Excel RATE function is a financial function that returns the interest rate per period of an
annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to
derive the annual interest rate.
The RATE function calculates by iteration.
=RATE (nper, pmt, pv, [fv], [type], [guess])
nper: The total number of payment periods
pmt: The payment made each period
pv: present value now or the value at the beginning
fv: [optional]; future value or balance after last payment; default is 0

5. NPV function

The Excel NPV function calculates the net present value of an investment, based on a supplied
discount rate, and a series of future payments and income.
=NPV (rate, value1, value2,…)
rate: is the rate of discount over the length of one period.
value1: value1, value2,… are 1 to 254 payments and income, equally spaced in time and
occurring at the end of each period.
value2: value1, value2,… are 1 to 254 payments and income, equally spaced in time and
occurring at the end of each period.

6. IRR function

The Excel IRR function returns the internal rate of return for a supplied series of periodic cash
flows. This means an initial investment and a series of net cash flow values.
The initial cash flow is negative, and the series of cash flows can be positive or negative.
The IRR function assumes equal periods.
=IRR (values, [guess])
  Values: an array or a reference to cells that contain numbers for which you want to calculate
the internal rate of return.
 Guess: [optional]; a number that you guess is close to the result of IRR; 0.10 or 10% if
omitted.
What is the DB Function?

The DB Function[ 1 ] is an Excel Financial function. This function helps in calculating the
depreciation of an asset. The method used for calculating depreciation is the Fixed Declining
Balance Method for each period of the asset’s lifetime.

Formula

=DB(cost, salvage, life, period, [month])

The DB function uses the following arguments:

Cost (required argument) – This is the initial cost of the asset.

Salvage (required argument) – The value of the asset at the end of the depreciation.

Life (required argument) – This is the useful life of the asset or the number of periods for which
we will be depreciating the asset.

Period (required argument) – The period for which we wish to calculate the depreciation for.

Month (optional argument) – Specifies how many months of the year are used in the calculation
of the first period of depreciation. If omitted, the function will take the default value of 12.

SLN () Function:
SLN () Function in MS Access is used to calculate the straight-line depreciation of an asset for
a single period.

Syntax:

SLN (cost, salvage, life)

Parameter: This method accepts three parameters as mentioned above and described below:

cost: It specify the initial cost of the asset.

salvage: It specify the value of the asset at the end of its useful life.

life: It specify the length of the useful life of the asset.

Returns: It returns depreciation of an asset.

SYD () Function:
SYD () Function in MS Access is used to calculate the sum-of-years’ digits depreciation of an
asset for a specified period.

Syntax:

SYD (cost, salvage, life, period)

Parameter: This method accepts four parameters as mentioned above and described below:

cost: It specify the initial cost of the asset.

salvage: It specify the value of the asset at the end of its useful life.

life: It specify the length of the useful life of the asset.

period: It specify the period for which asset depreciation is calculated.

Returns: It returns depreciation of an asset for a specified period.

Logical Functions:
It is a feature that allows us to introduce decision-making when executing formulas and functions.
Functions are used to;

 Check if a condition is true or false

 Combine multiple conditions together

If
The IF function checks whether a condition is met, and returns one value if true and another value
if false.

1. For example, take a look at the IF function in cell C2 below.

Explanation: if the score is greater than or equal to 60, the IF function returns Pass, else it returns
Fail. Visit our page about the IF function for many more examples.

And
The AND Function returns TRUE if all conditions are true and returns FALSE if any of the
conditions are false.

1. For example, take a look at the AND function in cell D2 below.

Explanation: the AND function returns TRUE if the first score is greater than or equal to 60 and
the second score is greater than or equal to 90, else it returns FALSE.
Or
The OR function returns TRUE if any of the conditions are TRUE and returns FALSE if all
conditions are false.

1. For example, take a look at the OR function in cell D2 below.

Explanation: the OR function returns TRUE if at least one score is greater than or equal to 60, else
it returns FALSE. Visit our page about the OR function for many more examples.
LOOKUP function

What Is VLOOKUP?

The VLOOKUP function is used when we have a set of vertical data. It allows us to search a range
of data references with columns, and retrieves the right information we are looking for.

What Is HLOOKUP?

The HLOOKUP function is used when we have a set of horizontal data. It allows us to search a
range of data references with rows, and retrieves the right information we are looking for.

Mathematical functions in Excel

SUM:

This function is used to adds all the values within a cell range.

ROUND:

The round function is used to round a number to a specified number of digits.

INT:

This function is used to converts a decimal number to integer lower than it.

ABS:
The abs function is used to return the absolute value of a given number. The number may be
positive or negative.

SQRT:

The SQRT Function[ 1 ] is an Excel Math and Trigonometry function. It will provide the square root
of a positive number.

Text Functions:

The TEXT function lets you change the way a number appears by applying formatting to it
with format codes. It's useful in situations where you want to display numbers in a more readable
format, or you want to combine numbers with text or symbols.

In its simplest form, the TEXT function says:

 =TEXT(Value you want to format, "Format code you want to apply")

Here are some popular examples, which you can copy directly into Excel to experiment with on
your own. Notice the format codes within quotation marks.