INFORMATION TECHNOLOGY FOR BUSINESS

Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Module-4
Microsoft Excel in Business

Introduction to MS Excel
 Any grid or array of numbers and / or text in rows and columns is called a Spreadsheet.
 This array or grid is a place to write down numbers and calculate them easily.
 The electronic spreadsheet is an extremely useful application program for both the
business and the home user.
 One of the popular spreadsheet packages is MS Excel. It is made up of horizontal rows
and vertical columns in the data can be stored.
 Spreadsheet software makes it possible to enter data into a table format, manipulate
them, store them, print them and create reports and graphs using them. All this can be
done with relative ease compared to working by hand with the same rows and columns
of data.
 In a spreadsheet the rows are labeled with numbers and columns with alphabets.
 The interaction of a row and column is called a cell.
 The interaction of row 3 with column C is cell C3. This naming convention is followed
in all spreadsheets.
 For example, the first cell, the cell obtained from the interaction of row 1 with column
A is called A1
 There are many software companies authorizing spreadsheet programs. Some of the
most popular ones are: Lotus 1-2-3, MS-Excel, Quatro-Pro, VP Planner, Multiplan, and
Soft Calc.

With the spreadsheet, can be prepared the following:

 Budgets
 Annual reports
 Accounts payable and receivable
 Invoices
 Income statements
 Loan analysis
 Tax statements
D.M.S. Mandal’s College of Business Administration, Belgaum. Page 1
INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

 Developing Business plan

 Managing Inventory
 Maintaining data on employee or clients
 Performing standard accounting functions

Advantages of an electronic spreadsheet

The following important advantages are-
 Large spreadsheets can be stored effortlessly on the computer.
 To make changes of data is very simple.
 Entering data is fast and easy.
 Calculations on the electronic spreadsheet are almost error-free that is, if the data entered
is correct, then the results are definitely correct.
 Extracting meaningful information is easy.

Applications of Spreadsheet
1) Budgets:
Spreadsheets are commonly used to develop and monitor budgets. The budgets items
are usually listed across the row. The column indicates various budget periods.

2) Inventory Management:
Many small business use spreadsheets to keep track of inventory. The various
inventory items are listed down the rows. The columns give the number or items
shipped and the number of items remaining in the inventory at particular times.

3) Portfolio Management:
Spreadsheets are used to keep track of investment portfolios. Each investment
occupies a single line.

4) Management Decision Support:

Spreadsheets are commonly used to make projections of business condition. They are
used in analyzing the effects of changes in various conditions.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 2

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Characteristics of a spreadsheet
Although spreadsheet software organize data into rows and columns

Characteristics include:
 Table Format
 Data Forms
 Recalculations
 Storage and Retrieval
 Presentation
 Standard Format

Functions of spreadsheet
A spreadsheet is a very big package and is made for general purpose. Its functions can be
classified into three categories.
1) Word Processing: Using this facility it can do almost all the functions provided by a
word processor.
For example, writing texts, changing its fonts, formatting text, cut, copy, paste, save
etc.

2) Database: Using this facility it can do almost all the functions provided by a
database package.
For example, making lists, records, adding and deleting records, query, data storing,
data filling etc.

3) Graphics: Using this facility it can present the data in terms of some picture. There
are more than enough graphs / charts available in MS-Excel, by which we can
represent our data.

Features of Microsoft Excel

Important features of Microsoft Excel 2010 are as follows:
1) Conditional Formatting:
Conditional formatting in Excel 2010 gives you the ability to define formatting when
the values in cells meet certain conditions. You can now instantly apply one of many

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 3

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

different data Bars, Color Scales and Icon Sets to the cell selection merely by
clicking the set’s thumbnail in the respective pop-up palettes.

2) Cell Styles:
Excel 2010 offers more than 40 colorful ready-made styles. These are styles you can
preview in the worksheet with live preview before you apply them. You apply a cell
style to the cell selection by quickly and easily clicking its thumbnails in the cells
styles gallery.

3) Formatting and editing from the Home tab:

The Home tab of the Excel Ribbon literally brings home all the commonly used
formatting and editing features.

4) Charts from the Insert tab:

Excel 2010 retires the chart wizard and offers you direct access to all the major types
of charts on the Ribbons Insert tab. simply select the data to chart, click the command
button for the chart type on the Insert tab and then select the style you want for that
chart type. And with a little help from the many command buttons and galleries on
the Design, Layout and format tabs on its chart tools contextual tab, you have a really
professional looking chart ready for printing!

5) Format as Table:
This feature is a real keeper. By formatting a table of data with one of the many table
styles available on the Table styles drop-down gallery, you’re assured that all new
entries made to the table are going to be formatted in the same manner as others in
similar positions in the table. Better yet, all new entries to the table are considered
part of the table automatically when it comes to formatting, sorting, and filtering.

6) Page Layout View:

When you turn on this view by clicking the page layout view button on the status bar,
excel does not just show the page breaks as measly dotted lines as in earlier versions
but as actual separations. Additionally, the program shows the margins for each page,
including header and footers defined for the report (which you can both define and
edit directly in the margin areas while the program is in this view).
D.M.S. Mandal’s College of Business Administration, Belgaum. Page 4
INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

7) The Ribbon:
The Ribbon is the heart of the new Excel 2010 user interface. Based on a core of
standard tabs to which various so-called contextual tabs are added as needed in
formatting and editing of specific elements (such as data tables, charts, pivot tables
and graphic objects), the Ribbon brings together most every commands you’re going
to need when performing particular tasks in Excel.

Benefits of Microsoft Excel

Benefits of Microsoft Excel are as follows.

1. Build great charts:

 Excel allows business users to unlock the potential of their data, by using formulas
across a grid of cells.
 Data is inserted into individual cells in rows or columns, allowing it to be sorted
and filtered, and then displayed in a visual presentation.
 Using different types of charts (pie chart, bar chart, column chart, line chart, area
chart, bubble chart etc.) it is used for business reports.
 Excel recommends chart most suitable for the type of data being presented on X
and Y axis.

2. Use Conditional Formatting:

 Excel users can format their spreadsheets using different colour shades, bolds and
italics, to differentiate between columns and brings the most important data to the
fore.
 This function could be useful when presenting accounting information, such as the
pre-tax profit and the balance being carried forward by the company.
 Users can select an appropriate coloring scheme via the Quick Analysis button,
and then by utilizing the Formatting tab.

3. Help Identify Trends

 When presenting data in the form of charts or graphs, it can be helpful to
include average lines, which explicitly detail the key trends emerging from the
information.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 5

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

 This may help demonstrate the key points to other users in a straightforward
manner for instance, an executive from a different department during a board
meeting. Excel allows trend lines to be extended beyond the graph, to offer
predictions of future activity and such forecasts can help businesses develop
their future strategy.

4. Bring Data Together:

 Excel can be used to bring information from various files and documents
together, so that it exists in a single location.
 As well as raw data and information from other spreadsheets, it is possible to
import text and images.
 Other objects can be added using the Insert tab or additional spreadsheets can
be added to the file.

Drawbacks of Microsoft Excel

Spreadsheets aren’t the right tool for that job because:

1) Most people don’t like them:

 This is for whatever reason Excel has a bad rap, especially among employees who
don’t use it often.
 People in other departments of the company may find them confusing or
intimidating.
 Many people won’t even open an email attachment in Excel, let alone get excited
about analyzing them to find insights.

2) Important data is hidden:

 Because you’re seeing all the raw data at once, it’s difficult to interpret what’s
important and what’s not.
 Visualization tool can be used to highlight the important aspects of a data set or
results, but in spreadsheets, it’s hard to see the forest for the trees.
 You are missing the point if your team finds it hard to identify the messages or
see how the data relates to their to their day job.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 6

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

3) They are difficult to analyze:

 Again, especially for lay people, spreadsheets can be difficult to analyze.
 Worse, the volume of data presented can lead to misinterpretation, and your team
could make poor choices or take wrong actions based on that misinterpretation.
 In addition, people only familiar with spreadsheets and their associated tools tends
to make charts and graphs that often distort data, including 3D graphs and pie
charts, simply because the tool (like Excel) makes them easy to create.

4) Loss of Historical data:

 Spreadsheets aren’t designed to store historical data, so often, in an attempt to
keep the size of them manageable, they are “updated” and companies lose their
historical data.
 This makes is hard or impossible to spot trends over time and compare data across
longer time horizons.

5) It is difficult to share:
 Even with cloud computing solutions, it’s difficult to share a spreadsheet among
many team members.
 And, because of the possibility that data could accidentally be deleted or changed,
the spreadsheet that is shared is rarely “live” or real-time. At best it might get
emailed once a week which poses the problem of the information gets lost in team
members inboxes.

Microsoft Excel 2007

Introduction to Microsoft Excel 2007

What is Microsoft Excel?

 Microsoft Excel is a spread sheet program used to record and analyze numerical
and statistical data.
 Microsoft Excel provides multiple features to perform various operations like
calculations, pivot tables, graph tools, macro programming, etc.
 It is compatible with multiple OS like Windows, mac OS, Android and iOS.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 7

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

 A Excel spread sheet can be understood as a collection of columns and rows that
form a table.
 Alphabetical letters are usually assigned to columns, and numbers are usually
assigned to rows.
 The point where a column and a row meet is called a cell.
 The address of a cell is given by the letter representing the column and the number
representing a row.

Excel is typically used for:

• Data entry
• Data management
• Accounting
• Budgeting
• Data analysis
• Visuals and graphs
• Financial modelling
• And much, much more!

How to Open Microsoft Excel?

• Click on start menu

• Point to all programs
• Point to Microsoft Office
• Click on Microsoft Excel

What is the file extension of MS Excel 2007?

• File extension of Excel (2007/2010) is .xlsx

• Ex: name.xlsx

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 8

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Parts of MS-Excel

Different types of Bars

Title bar

The title bar displays both the name of the application and the name of the spreadsheet.

Menu bar

The menu bar displays all of the menus available for use in Excel XP. The contents of any
menu can be displayed by left-clicking the menu name.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 9

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Tool bar

Some commands in the menus have pictures or icons associated with them. These pictures
may also appear as shortcuts in the toolbar.

Column headings

Each Excel spreadsheet contains 256 columns. Each column is named by a letter or
combination of letters.

Row headings

Each spreadsheet contains 65,536 rows. Each row is named by a number.

Name box

This shows the address of the current selection or active cell.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 10

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Formula bar

The formula bar displays information entered—or being entered as you type—in the current
or active cell. The contents of a cell can also be edited in the formula bar.

Cell

A cell is an intersection of a column and row. Each cell has a unique cell address. In the
picture above, the cell address of the selected cell is B3. The heavy border around the
selected cell is called the cell pointer.

Navigation buttons and sheet tabs

Navigation buttons allow you to move to another worksheet in an Excel workbook. They
are used to display the first, previous, next, and last worksheets in the workbook.

Sheet tabs separate a workbook into specific worksheets. A workbook defaults to three
worksheets. A workbook must contain at least one worksheet.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 11

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Status Bar:

It is a thin bar at the bottom of the Excel window. It will give you an instant help once
you start working in Excel.

Standard Toolbar:

1. New - Creates a new empty workbook with a given number of worksheets. Alternatively
(File > New). (Ctrl + N)
2. Open - Displays the (File > Open) dialog box to open existing workbooks. (Ctrl + O).
3. Save - Saves the active workbook. Displays the (File > Save As) dialog box the first
time a workbook is saved. (Ctrl + S).
4. Permission - (Added in 2003). Used in conjunction with Windows Rights Management
services.
5. E-mail - Emails the active workbook or active worksheet.
6. Search - (Removed in 2003). Finds files, Web pages, and Outlook items based on the
search criteria you enter.
7. Print - Prints the active worksheet. There is NO prompt. (Ctrl + P).
8. Print Preview - Displays the (File > Print Preview) dialog box.
9. Spelling - Displays the (Tools > Spelling) dialog box. (F7).
10. Research - (Added in 2003). Opens the Research task pane allowing you to access
online information.
D.M.S. Mandal’s College of Business Administration, Belgaum. Page 12
INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

11. Cut - Cuts the current selection to the clipboard. (Ctrl + X).
12. Copy - Copies the current selection to the clipboard. (Ctrl + C).
13. Paste - Pastes the entry from the clipboard. (Ctrl + V). The Paste Options smart tag
provides quick access to some of the Paste Special options.
14. Format Painter - Copies the format from a cell or range allowing it to be pasted
elsewhere.
15. Undo - Undoes the last 16 actions you performed. (Ctrl + Z).
16. Redo - Redoes the last 16 actions you performed.
17. Insert Hyperlink - Displays the (Insert > Hyperlink) dialog box. (Ctrl + K).
18. AutoSum - Inserts the SUM () function.
19. Sort Ascending - Sorts data alphabetically from A to Z and numerically from the lowest
number to the highest. Holding down Shift will toggle to a Descending sort.
20. Sort Descending - Sorts the data alphabetically from Z to A and numerically from the
highest number to the lowest. Holding down Shift will toggle to an Ascending sort
21. Chart Wizard - Displays the (Insert > Chart) dialog box.
22. Drawing - Toggles the display of the Drawing toolbar.
23. Zoom - Adjusts the size that the worksheet appears on the screen.
24. Microsoft Excel Help - Displays the (Help > Microsoft Excel Help) dialog box. (F1).

THE MICROSOFT OFFICE BUTTON

The office button is at the top left-hand corner of the Ribbon. It replaces the File Menu of
earlier versions of Office and Excel. It is used to Open, Save, Print and Close files.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 13

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

THE RIBBON

The commands are organized by Tab and Group. At the highest level is the Ribbon for each
Tab. Here this is shown for the Home Tab.

Eventually we want to get to commands that we use. From the Ribbon the sequence is

1. Tabs:
- The seven tabs are Home, Insert, Page Layout, Formulas, Data, Review, View.
- The above example uses the Home tab.
2. Groups:
- These show related items together.
- For example, there is the Font group (within the Home tab).
3. Commands
- A command is a button, a box to enter information, or a menu.
- For example, there is the B button (in the Font group in the Home tab) to make text
bold.
- The Dialog Box launcher is a small arrow that gives additional commands (e.g. at
bottom right of the Font group).

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 14

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

THE RIBBON FOR EACH OF THE SEVEN TABS

For each of the seven tabs the Ribbon looks as follows on my computer (the look of the
ribbon varies with screen resolution and with the active Excel document).
The most useful Groups for basic data analysis are highlighted.

1. Home Tab - Use to read in data and save data in various formats.

2. Insert Tab - Use to create Charts.

3. Page Layout Tab – Use to page formatting options

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 15

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

4. Formulas Tab – Use to write Formula

5. Data Tab - Use for sort & filter data and for Data Validation

6. Review Tab – Use for spelling checks

7. View Tab – Use for Macros

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 16

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Data Validation

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 17

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Cell Reference
 A cell reference refers to a cell or a range of cells on a worksheet and can be used
in a formula so that Microsoft Office Excel can find the values or data that you want
that formula to calculate.
 Data from one or more contiguous cells on the worksheet.

There are three types of Cell Reference

1) Relative Reference
2) Absolute Reference
3) Mixed Reference

1. Relative Reference:
o By default, a cell reference is a relative reference, which means that the
reference is relative to the location of the cell.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 18

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

o For example, you refer to cell A2 from cell C2, you are actually referring to a
cell that is two columns to the left (C minus A) in the same row (2).

o Apply formulae in one cell & just drag it is called vertical Relative Reference.

2. Absolute Reference:
 An absolute reference in Excel is a reference that cannot be changed when
copied, so you won't see changes in rows or columns when you copy them.
 Absolute references are used when you want to fix a cell location.
 Formulae: C5*$C$2 used in following table.

3. Mixed Reference:
 Mixed Reference is a type of Absolute reference in which either the column is
made constant or the row is made constant.
 When we make any column or row constant then the column name or row
number does not change as we copy the formula to other cells.
 Formulae: =$C5*(1-E$4) used in following table.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 19

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Format Cells:

 Cell formats allow you to only change the way cell data appears in the spreadsheet. It
is important to keep in mind that it only alters the way the data is presented, and does
not change the value of the data.
 The formatting options allow for:

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 20

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Wrap Text (Word Wrap):

This is the facility available in ‘format>cells>Alignment>Wrap Text’, by which we can

manage text flow inside a cell. Using ‘wrap text’ facility, the height of row will be
increased (not its width) and size of text remains same.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 21

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Shrink to fit:

When we select ‘Shrink to fit’, increases or decreases the font size in the cell in order to
make the current worksheet fit in the page. If we select ‘Auto fit’, the row height is
increased or decreased but fonts are unchanged.

Merge Cells:

Making two or more cells into one cell is called merging cells. It is done when we need two
more cells as a single cell. Generally, it is used for headings.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 22

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Strikethrough:

Strikethrough is a typographical presentation of words with a horizontal line through their

center, resulting in text like this.

Superscript:

A superscript is a number or letter that's written slightly above another character. When you
write out the mathematical expression "x squared," you write the 2 as a superscript, smaller
than and raised above the x, like so: x2.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 23

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Subscript:

A subscript is a character, usually a letter or number, that's printed slightly below and to the
side of another character. Subscripts are commonly used in chemical formulas. A scientist
would write the formula for water, H2O, so that the 2 appears lower and smaller than the
letters on either side of it. H2O

Font Management:
Using this facility, we change font types, its size, and color, type of underline and font
effect.

Border and Frames:

In this option, we can add or remove boarders (grids) in the worksheets, change width of
border lines, color of borders.

Patterns:
By using this option, we can supply background color, pattern and design or any pictures in
the cell, selected range or to the whole worksheet.

Formatting Row and Columns:

Using this facility, we can change width of column/row, hide the column/row and its
content and unhide column/row etc.

Formatting Sheet:
By using this, we can rename sheet, hide sheet, unhide sheet and supply a background
picture or pattern to the whole sheet. but the background provided will not be available for
printing. This is called watermark.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 24

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Data Validation:
 Data validation in Excel is a feature that allows you to control the type of data
entered into your worksheet.

OR

 Data validation in Excel is a technique that restricts user input in a worksheet. It is

often used to limit user entry.
 For example, Excel data validation allows you to limit data entries to a selection
from a dropdown list and to restrict certain data entries, such as dates or numbers
outside of a predetermined range.

Data Analysis in Excel:

Sort:

Sorting is the process of arranging data into meaningful order so that you can analyze it
more effectively.

There are two types of Sort

1) Ascending (A to Z)
2) Descending (Z to A)

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 25

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Filter:
 The FILTER function allows you to filter a range of data based on criteria you define.
 Filter in Excel is a valuable tool to analyze data quickly.

Conditional Formatting:

Conditional formatting makes it easy to highlight certain values or make particular cells
easy to identify. These changes the appearance of a cell range based on a condition (or
criteria). You can use conditional formatting to highlight cells that contain values which
meet a certain condition.

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 26

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Preparing Charts:

What is Chart?
Charts are used to make a graphical representation of any set of data. A chart is a visual
representation of data, in which the data is represented by symbols such as bars in a bar
chart or lines in a line chart.

Types of Charts in Excel

1) Line Chart
2) Bar Chart
3) Column Chart
4) Area Chart
5) Pie Chart
6) Area Chart
7) Scatter Chart
8) Bubble Chart

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 27

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

What is Pivot table?

 A pivot table is a statistics tool that summarizes and reorganizes selected columns
and rows of data in a spreadsheet or database table to obtain a desired report.
 The tool does not actually change the spreadsheet or database itself, it simply
“pivots” or turns the data to view it from different perspectives.
D.M.S. Mandal’s College of Business Administration, Belgaum. Page 28
INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 29

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Functions in MS-Excel
 An Excel function performs a calculation and returns a value.
 Excel contains many built-in functions that perform a number of standard worksheet
calculations.
 Each function has a name followed by parentheses.
 The arguments of a function are placed within parentheses that immediately follow
the function name.
 The values given to a function to perform its actions are called the arguments.
 An argument can be a constant, a cell reference, a logical value, another function or
an expression.
 Commas separate one argument from another.
 Functions carry some specific work. It minimizes a large work.

Mathematical Functions:
 The Mathematical Functions in Excel have various built-in functions used to
calculate numerical data. The commonly used functions are SUM, SUMIF, MOD,
INT, ROUND, SQRT, POWER etc.
 The Mathematical Functions are categorized under the Math & Trig functions group
under the Formula tab.

Sum ( ), Sum if ( ), Mod ( ), Int ( ), Round ( ), Sqrt ( ), Power ( )

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 30

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

1) Sum Function: Sum ( )

Add the numbers in a range or multiple ranges of cells.
=Sum (Select Number1, Number 2) Press Enter

2) Sumif Function: Sumif ( )

SUMIF adds the matching values in a range.
=Sumif ((Select Column1, Select Item, Select Column2) Press Enter

3) Modulus Function: Mod ( )

The MOD Function [1] is categorized under Excel Math and Trigonometry functions.
The function helps find a remainder after a number (dividend) is divided by another
number (divisor).
=Mod (Select Number, Select Divisor) Press Enter

4) Integer Function: Int( )

Integer function may refer to: Integer-valued function, an integer function. Floor
function, sometimes referred as the integer function, INT. Arithmetic function, a term
for some functions of an integer variable.
=Int (Select Number) Press Enter

5) Square Root Function: Sqrt( )

In mathematics, a square root function is defined as a one-to-one function that takes a
positive number as an input and returns the square root of the given input number.
For example, if x=4, then the function returns the output value as 2.
Note: The square root of a negative number represents a complex number.
=Sqrt (Select Number) Press Enter

6) Power Function: Power( )

A power function is a function with a single term that is the product of a real number,
a coefficient, and a variable raised to a fixed real number.
=Power (Select number, power number) Press Enter

7) Round Function: Round( )

The ROUND function rounds a number to a specified number of digits.
=Round (Select number, 0) Press Enter

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 31

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

Text Functions:
Upper ( ), Lower ( ), Proper ( ), Concatenate ( )

1. Upper ( ) :
The UPPER Function is an Excel Text function that will convert text to all capital
letters (UPPERCASE). Thus, the function converts all characters in a supplied text
string into upper case.
=Upper (Select Name) Press Enter

2. Lower ( ):
The LOWER Function is an Excel Text function that will convert text to all small
letters (LOWERCASE). Thus, the function converts all characters in a supplied text
string into lower case.
=Lower (Select Name) Press Enter

3. Proper ( ):
The PROPER Function is categorized under Excel Text functions. PROPER will
capitalize the first letter in a text string and any other letters in text that follow any
character other than a letter.
=Proper (Select Name) Press Enter

4. Concatenate ( ):
The word concatenate is just another way of saying "to combine" or "to join
together". The CONCATENATE function allows you to combine text from different
cells into one cell. In our example, we can use it to combine the text in column A and
column B to create a combined name in a new column.
=Concatenate (Select Name, Select Surname) Press Enter

Date Functions

Today ( ), Now ( ), Weekday ( )

1. Today ( ):

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 32

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Prof. Miss. Poonam M. Patil

The TODAY Function is categorized under Excel Date and Time functions. It
will calculate and give the current date. It is updated continuously whenever a
worksheet is changed or opened by a user. The function's purpose is to get today's
date.
=Today ( ) Press Enter

2. Now ( ):
The NOW () function returns the current date and time. Note: The date and time is
returned as "YYYY-MM-DD HH-MM-SS" (string) or as YYYYMMDDHHMMSS.
(Numeric).
=Now ( ) Press Enter

3. Weekday ():
The WEEKDAY Function is an Excel DATE and TIME Function. The function will
return an integer that is a representation of the day of the week for a given date.
WEEKDAY is quite useful in financial analysis.
=Weekday (Select Date) Press Enter

4. Day ( ):
Returns the day of a date, represented by a serial number. The day is given as an
integer ranging from 1 to 31.
=Day (Select Date) Press Enter

5. Month ( ):
The MONTH Function is categorized under Excel DATE/TIME functions. The
function will return the month portion of a date as a number. The number will be
between 1 to 12.
=Month (Select Date) Press Enter

6. Year ( ):
The YEAR Function is an Excel Date/Time function that is used for calculating the
year number from a given date. The function will return an integer that is a four-digit
year corresponding to a specified date.
=Year (Select Date) Press Enter
D.M.S. Mandal’s College of Business Administration, Belgaum. Page 33
INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Miss. Poonam M. Patil

Logical Functions:
Logical functions test cells & range and can return only either “True” or “False”. There
are so many logical functions but commonly used logical functions are

IF ( ), with logical operator AND, OR, NOT

IF Function:
 The IF ( ) function decide the contents of a cell on a spreadsheet based on whether a
test condition is true or false.
 It returns a value if one condition is True, and returns another value or result if the
condition is False.

Syntax: IF ( ):
=IF (logical_test, value_if: true, value_if: false)

The operator in the logical_test of the IF function may be:

= Equal to
< > Not equal to
> Greater than
> = Greater than or equal to
< Less than
< = Less than or equal to

Note: Nested IF ( ) Function

If you wish to test more than one condition, you can nest an if ( ) function within another
= if (logical_test, result_if_true, if (logical_test, result_if_true, if (logical_test,
result_if_true, result if false))

If (), with logical operator AND, OR, NOT

1) AND Function:
Returns “True” if all the logical arguments are true.
Returns “False” if one or more arguments are false.
Syntax: AND (logical1, logical2……)

2) OR Function:
Returns “False” if all the logical arguments are false.
Returns “True” if one or more arguments are true.
D.M.S. Mandal’s College of Business Administration, Belgaum. Page 34
INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Miss. Poonam M. Patil

Syntax: OR (logical1, logical2……)

3) NOT Function:
Returns the opposite of the logical value
Syntax: NOT (logical)

Statistical Functions:

Statistical functions are primarily used to mathematically process a specified set of cells in
a worksheet. For example, to add values occupying a large group of cells, you need to use
the SUM function. Functions are more extensively used than formulas when a
mathematical process is applied to a group of cells.

Count ( ), Count if ( ), Average ( ), Max ( ), Min ( )

1. COUNT function In Excel:

The COUNT function is used for counting the number of cells that have a number.
This function solely counts the number.

Syntax for the COUNT function = COUNT (value1, [value2], …)

2. COUNTIF function In Excel:

This is the most used statistical function in Excel that works on one or multiple
conditions in a specific range and counts the cell(s) that meet the said condition.

Syntax for the COUNTIF function = COUNTIF (range1, criteria1, [range2]

3. AVERAGE function in Excel:

The arithmetic mean of a set of numbers is determined by this function. Using

Syntax for the AVERAGE function =AVERAGE (A1:A10)

as an example, you may get the average of the values in cells A1 through A10.

4. MAX function In Excel:

This function returns the highest numeric value present in a given data set or array.

Syntax for the MAX function = MAX (number1, [number2], …)

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 35
INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Miss. Poonam M. Patil

5. MIN function In Excel:

The MIN function is the exact opposite of the MAX function in Excel. It returns the
smallest numeric value in a given data set or array.

Syntax for the MIN function = MIN (number1, [number2], …)

Financial Function:
 Financial functions calculate financial information, such as net present value
and payments. For example, you can calculate the monthly payments required
to buy a car at a certain loan rate using the PMT function.

NPV ( ), PMT ( ), PV ( ), FV ( ), Rate ( ), IRR ( ), DB ( ), SLN ( ), SYD ( )

1) NPV ( )
Returns the net present value of an investment based on a series of periodic cash
flows and a discount rate
Syntax: NPV (rate, value1, value2 …)

2) PMT ( )
Returns the periodic payment for an annuity
Syntax: PMT (rate, nper, pv, fv, type)

3) PV ( )
Returns the present value of an investment
Syntax: PV (rate, nper, fv, type)

4) FV ( )
Returns the present value of an investment
Syntax: FV (rate, nper, pmt, pv, type)

5) Rate ( )
Returns the interest rate per period of an annuity
Syntax: RATE (nper, pmt, pv, fv, type, guess)

6) IRR ( )
Returns the interest rate per period of an annuity
Syntax: IRR (values, guess)

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 36

INFORMATION TECHNOLOGY FOR BUSINESS
Semester: Vth (BBA) Miss. Poonam M. Patil

7) DB ( )
Returns the depreciation of an asset for a specified period by using the fixed-
declining balance method
Syntax: (cost, salvage, life, period, month)

8) SLN ( )
Returns the straight-line depreciation of an asset for one period
Syntax: SLN (cost, salvage, life)

9) SYD ( )
Returns the sum-of-years' digits depreciation of an asset for a specified period
Syntax: SYD (cost, salvage, life, per)

Lookup Functions:
The function performs a rough match lookup either in a one-row or one-column
range and returns the corresponding value from another one-row or one-column
range.

There are two types of Lookup Functions

1) V Lookup
2) H Lookup

1) V Lookup:
VLOOKUP stands for "Vertical Lookup" and is used to search for a specific
value in the first column of a dataset and retrieve a corresponding value from a
different column within the same row.

Syntax:
VLOOKUP (lookup_value, table_array, col_index_num,[range_lookup])

2) H Lookup:
HLOOKUP stands for Horizontal Lookup and can be used to retrieve
information from a table by searching a row for the matching data and outputting
from the corresponding column. While VLOOKUP searches for the value in a
column, HLOOKUP searches for the value in a row.

Syntax:
HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

D.M.S. Mandal’s College of Business Administration, Belgaum. Page 37