INFORMATION SYSTEM AND COMPUTERS

2023

5.ELECTRONIC SPREADSHEET

Definition of Spreadsheet:
A spreadsheet or worksheet is a file made of rows and columns that help
sort, organize, and arrange data efficiently,and calculate numerical data.

Top 10 Spreadsheets Software

 Microsoft Excel.
 Google Workspace.
 Quip.
 Apple Numbers.
 Zoho Sheet.
 Minitab Statistical Software.
 LibreOffice.
 WPS Spreadsheets.

. Here are some common uses of spreadsheets in Excel:

1. Financial analysis: Spreadsheets can be used to create financial

models, budgeting, forecasting, and accounting, as well as to track
income, expenses, and investments.
2. Data visualization: Spreadsheets can be used to create charts,
graphs, and other visual aids to help make data more understandable
and actionable.
3. Data management: Spreadsheets can be used to organize, sort, and
filter large amounts of data, as well as to perform calculations and
analyses on that data.
4. Project management: Spreadsheets can be used to create project
timelines, track progress, and allocate resources.
5. Inventory management: Spreadsheets can be used to track
inventory levels, manage stock, and reorder products.
6. Business intelligence: Spreadsheets can be used to process and
analyze large amounts of data in order to gain insights and make
data-driven business decisions.

Adarsha College Of Management And Science,Kolar Page 1

 INFORMATION SYSTEM AND COMPUTERS
2023

7. CRM: Spreadsheets can be used to manage customer information,

track sales, and perform customer analysis.
8. Scheduling and Planning: Spreadsheets can be used to create
schedules, manage appointments, and plan events.
9. Statistical analysis: Spreadsheets can be used to perform statistical
analysis on data, such as calculating mean, median, mode, and
standard deviation.
10.Mail Merging: Spreadsheets can be used to create mailing lists and
merge them with letters, envelopes, and labels.

Advantages of Spreadsheets

1. Spreadsheets are free.

2. Spreadsheets require minimal training.
3. Spreadsheets are customizable.
4. Spreadsheets can be more collaborative than other tools.
5. It’s easy to manipulate and analyze data.
6. You can integrate spreadsheets with certain tools.
7. Spreadsheets are quick and easy to add into a workflow.
8. Spreadsheets are fantastic tools for financial documents.
9. You have access to countless spreadsheet templates.
10.You can visualize data (with caveats).

Applications of spreadsheet
 Business Data Storage
 Accounting and Calculation Uses
 Budgeting and Spending Help
 Assisting with Data Exports
 Data Sifting and Cleanup
 Generating Reports and Charts
 Business Administrative Tasks

Adarsha College Of Management And Science,Kolar Page 2

 INFORMATION SYSTEM AND COMPUTERS
2023

Characteristics or features of spreadsheet

 Rows and columns. All of your information is neatly organized in one easy-
to-read space through a spreadsheet's grid system of rows and columns.
 Formulas and functions. ...
 Data filtering and visualization. ...
 Custom formatting. ...
 Accounting. ...
 Analytics. ...
 Presentations. ...
 Project management.

Different types of BARS

Title bars
Menu bar
Formula bar
Status bar
Tool bar
Formulas: calculate values in a specific order. A formula always begins with an
equal sign (=). Excel for the web interprets the characters that follow the equal sign
as a formula.

Adarsha College Of Management And Science,Kolar Page 3

 INFORMATION SYSTEM AND COMPUTERS
2023

Types functions in excel:

User defined functions

System defined functions

User defined functions:

These are defined by the user for their specific purpose,which is not available for
other users.

System defined functions:

System Defined Functions: A function is already created by system it is a

reusable piece or block of code that performs a specific action. Functions can
either return values when called or can simply perform an operation without
returning any value.

Ex:Max(),MIN(),Sqrt() etc

Types of charts in excel

Excel offers the following major chart types −

 Column Chart
 Line Chart
 Pie Chart
 Bar Chart
 Area Chart
 XY (Scatter) Chart
 Bubble Chart
 Stock Chart
 Surface Chart
 Radar Chart
 Combo Chart

Adarsha College Of Management And Science,Kolar Page 4

 INFORMATION SYSTEM AND COMPUTERS
2023

Column Chart
A Column Chart typically displays the categories along the horizontal (category)
axis and values along the vertical (value) axis. To create a column chart, arrange
the data in columns or rows on the worksheet.

Line Chart
Line charts can show continuous data over time on an evenly scaled Axis.
Therefore, they are ideal for showing trends in data at equal intervals, such as
months, quarters or years.

Pie Chart
Pie charts show the size of items in one data series, proportional to the sum of the
items. The data points in a pie chart are shown as a percentage of the whole pie. To
create a Pie Chart, arrange the data in one column or row on the worksheet.

A Pie Chart has the following sub-types −

 Pie
 3-D Pie
 Pie of Pie
 Bar of Pie

bar Chart
Bar Charts illustrate comparisons among individual items. In a Bar Chart, the
categories are organized along the vertical axis and the values are organized along
the horizontal axis. To create a Bar Chart, arrange the data in columns or rows on
the Worksheet.
Adarsha College Of Management And Science,Kolar Page 5
 INFORMATION SYSTEM AND COMPUTERS
2023

A Bar Chart has the following sub-types −

 Clustered Bar
 Stacked Bar
 100% Stacked Bar
 3-D Clustered Bar
 3-D Stacked Bar
 3-D 100% Stacked Bar

Area Chart
Area Charts can be used to plot the change over time and draw attention to the total
value across a trend. By showing the sum of the plotted values, an area chart also
shows the relationship of parts to a whole. To create an Area Chart, arrange the
data in columns or rows on the worksheet.

XY (Scatter) Chart
XY (Scatter) charts are typically used for showing and comparing numeric values,
like scientific, statistical, and engineering data.

A Scatter chart has two Value Axes −

 Horizontal (x) Value Axis

 Vertical (y) Value Axis

Bubble Chart
A Bubble chart is like a Scatter chart with an additional third column to specify the
size of the bubbles it shows to represent the data points in the data series.

Adarsha College Of Management And Science,Kolar Page 6

 INFORMATION SYSTEM AND COMPUTERS
2023

A Bubble chart has the following sub-types −

 Bubble
 Bubble with 3-D effect

Stock Chart
As the name implies, Stock charts can show fluctuations in stock prices.

Stock chart can also be used to show fluctuations in other data, such as daily
rainfall or annual temperatures.

To create a Stock chart, arrange the data in columns or rows in a specific order on
the worksheet.

For example, to create a simple high-low-close Stock chart, arrange your data with
High, Low, and Close entered as Column headings, in that order.

A Stock chart has the following sub-types −

 High-Low-Close
 Open-High-Low-Close

Surface Chart
A Surface chart is useful when you want to find the optimum combinations
between two sets of data. As in a topographic map, colors and patterns indicate
areas that are in the same range of values.

To create a Surface chart −

 Ensure that both the categories and the data series are numeric values.
Adarsha College Of Management And Science,Kolar Page 7
 INFORMATION SYSTEM AND COMPUTERS
2023

 Arrange the data in columns or rows on the worksheet.

Radar Chart
Radar charts compare the aggregate values of several data series.

To create a Radar chart, arrange the data in columns or rows on the worksheet.

A Radar chart has the following sub-types −

 Radar
 Radar with Markers
 Filled Radar

Combo Chart
Combo charts combine two or more chart types to make the data easy to
understand, especially when the data is widely varied. It is shown with a secondary
axis and is even easier to read. To create a Combo chart, arrange the data in
columns and rows on the worksheet.

What-If Analysis with Goal Seek

Goal Seek is a What-If Analysis tool that helps you to find the input value that
results in a target value that you want. Goal Seek requires a formula that uses the
input value to give result in the target value.

Adarsha College Of Management And Science,Kolar Page 8

 INFORMATION SYSTEM AND COMPUTERS
2023

Steps to create what-if-analysis –Goal seek.

Step 1: Select the cell with the output you want to change

Step 2: Navigate to the “Data” tab

Step 3: Select “What-if Analysis”

Step 4: Click “Goal Seek”

Step 5: Type the number you want to hit into the “To value” field

What Is Excel Data Validation?

Data validation in Excel is a feature that allows you to control the type of
data entered into your worksheet. 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.

Here are just a few examples of what Excel's data validation can do:

 Allow only numeric or text values in a cell.

 Allow only numbers within a specified range.
 Allow data entries of a specific length.
 Restrict dates and times outside a given range.
 Restrict entries to a selection from a drop-down list.
 Validate an entry based on another cell.
 Show an input message when the user selects a cell.
 Show a warning message when incorrect data has been entered.
 Find incorrect entries in validated cells.

Adarsha College Of Management And Science,Kolar Page 9

 INFORMATION SYSTEM AND COMPUTERS
2023

What is the SUBTOTAL Function in Excel?

The SUBTOTAL Function in Excel allows users to create groups and then perform
various other Excel functions such as SUM, COUNT, AVERAGE, PRODUCT,
MAX, etc. Thus, the SUBTOTAL function in Excel helps in analyzing the data
provided.

Formula

SUBTOTAL = (method, range1, [range2 …range_n])

What is the ABSOLUTE Function in Excel (ABS)?

The ABS Function in Excel returns the absolute value of a number. The function
converts negative numbers to positive numbers while positive numbers
remain unaffected.

Formula

ABSOLUTE Value = ABS(number)

Statistical Functions In Excel:

MIN
MIN function returns the lowest value from a list of values
Syntax
= MIN(number1, [number2], …)
MAX
The Excel MAX Function is listed under Microsoft Excel’s Statistical Functions category. It
returns
the largest value from a list of values
Syntax
= MAX(number1, [number2], …)

Adarsha College Of Management And Science,Kolar Page 10

 INFORMATION SYSTEM AND COMPUTERS
2023

COUNT Function
It returns a count of the numbers from the values you specify.

Syntax

= COUNT(value1, [value2],……. …)

COUNTA Function
The Excel COUNTA Function is listed under Microsoft Excel’s Statistical Functions category. It

returns a count of the values you specify.

Syntax

= COUNTA(value1, [value2], …)

COUNTIF
The Excel COUNTIF Function is listed under Microsoft Excel’s Statistical Functions category.
It returns a count of the numbers which meet the condition you specify.

Syntax

= COUNTIF(range, criteria)

Stdev(): The function will estimate the standard deviation based on a sample.
Formula

=STDEV(number1,[number2],…)

Mean():The mean is calculated by adding up a group of numbers and then dividing the sum by
the count of those numbers.

For example, to calculate the mean of numbers {1, 2, 2, 3, 4, 6}, you add them up, and then
divide the sum by 6, which yields 3: (1+2+2+3+4+6)/6=3.

Adarsha College Of Management And Science,Kolar Page 11

 INFORMATION SYSTEM AND COMPUTERS
2023

Mode():MODE returns the most frequently occurring, or repetitive, value in an array or range
of data.

Syntax
MODE(number1,[number2],...)

Median():Returns the median of the given numbers. The median is the number in the middle
of a set of numbers.

Syntax
MEDIAN(number1, [number2], ...)

Variance():Variance is a measurement of the spread between numbers in a data set. The

variance measures how far each number in the set is from the mean.

VAR.P(number1,[number2],...)

Correlation():

The CORREL function returns the correlation coefficient of two cell ranges. Use the
correlation coefficient to determine the relationship between two properties. CORREL(array1,
array2)

PERCENTILE ():The PERCENTILE is categorized under Excel Statistical

functions. PERCENTILE will return the k-th percentile of the values in a particular
range.

Formula

=PERCENTILE(array,k)

QUARTILE():
Returns the quartile of a data set. Quartiles often are used in sales and survey data
to divide populations into groups

Adarsha College Of Management And Science,Kolar Page 12

 INFORMATION SYSTEM AND COMPUTERS
2023

Syntax

QUARTILE(array,quart)

RANK():

Returns the rank of a number in a list of numbers. The rank of a number is its size
relative to other values in a list. (If you were to sort the list, the rank of the number
would be its position.)

RANK(number,ref,[order]

Financial Functions:
The finance function in business refers to the functions intended to acquire and manage
financial resources to generate profit.The following are the financial funcitons

PV ( ):

PV one of the financial functions, calculates the present value of a loan or an

investment, based on a constant interest rate. You can use PV with either periodic,
constant payments

Syntax

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

NPV( ) :The NPV() is an Excel Financial function that will calculate the Net
Present Value (NPV) for a series of cash flows and a given discount rate.
NPV(rate,value1,[value2],…)

NPER():
Returns the number of periods for an investment based on periodic, constant payments
and a constant interest rate.

Adarsha College Of Management And Science,Kolar Page 13

 INFORMATION SYSTEM AND COMPUTERS
2023

Syntax

NPER(rate,pmt,pv,[fv],[type])

 Rate : The interest rate per period.

 Pmt : The payment made each period; it cannot change over the life of the annuity.
Typically, pmt contains principal and interest but no other fees or taxes.

 Pv : The present value, or the lump-sum amount that a series of future payments is worth
right now.

 Fv : The future value, or a cash balance you want to attain after the last payment is made. If
fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).

 Type : The number 0 or 1 and indicates when payments are due.

PMT():

one of the financial functions, calculates the payment for a loan based on
constant payments and a constant interest rate.

Syntax

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

The PMT function syntax has the following arguments:

 Rate . The interest rate for the loan.

 Nper The total number of payments for the loan.
 Pv . The present value, or the total amount that a series of future payments
is worth now; also known as the principal.
Adarsha College Of Management And Science,Kolar Page 14
 INFORMATION SYSTEM AND COMPUTERS
2023

 Fv Optional. The future value, or a cash balance you want to attain after
the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is,
the future value of a loan is 0.
 Type Optional. The number 0 (zero) or 1 and indicates when payments are
due.

RATE():

Returns the interest rate per period of an annuity.

RATE(nper, pmt, pv, [fv], [type], [guess])

IRR ():

IRR will return the Internal Rate of Return for a given cash flow, that is, the initial
investment value and a series of net income values.

SLN ():

The Excel SLN function returns the depreciation of an asset for one period, calculated with a
straight-linemethod. The calculated depreciation is based on initial asset cost, salvage value, and
the number of periods over which the asset is depreciated.

SLN(cost, salvage, life)

SYD():
Returns the sum-of-years' digits depreciation of an asset for a specified period.

SYD(cost, salvage, life, per)

 Cost Required. The initial cost of the asset.

 Salvage Required. The value at the end of the depreciation (sometimes called the salvage
value of the asset).

Adarsha College Of Management And Science,Kolar Page 15

 INFORMATION SYSTEM AND COMPUTERS
2023
 Life Required. The number of periods over which the asset is depreciated (sometimes called
the useful life of the asset).

 Per Required. The period and must use the same units as life.

IPMT():

It returns the interest amount of a loan payment in a given period, assuming the interest
rate and the total amount of a payment are constant in all periods.

IPMT(rate, per, nper, pv, [fv], [type])

DB():

Returns the depreciation of an asset for a specified period using the fixed-declining
balance method.

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

The DB function syntax has the following arguments:

 Cost Required. The initial cost of the asset.

 Salvage Required. The value at the end of the depreciation (sometimes called the
salvage value of the asset).
 Life Required. The number of periods over which the asset is being depreciated
(sometimes called the useful life of the asset).
 Period Required. The period for which you want to calculate the depreciation. Period
must use the same units as life.
 Month Optional. The number of months in the first year. If month is omitted, it is
assumed to be 12.

Adarsha College Of Management And Science,Kolar Page 16

 INFORMATION SYSTEM AND COMPUTERS
2023

Logical functions:

Logical functions are used in spreadsheets to test whether a situation is true or false.
Depending on the result of that test, you can then elect to do one thing or another.

The IF Function:
The IF function is the key logical function used for decision making. It takes the
format:
=IF(condition, true, false)
For example, you could use the following formula:
=IF(B2 > 400, “High”, “Low”) where,
B2 > 400 is the condition being tested
(this could be translated as “Is the value in cell B2 greater than 400?”)
“High” is the text to display if B2 is greater than 400 (the result
of the test is yes or TRUE)
“Low” is the text to display if B2 is less than or equal to 400
(the result of the test is no or FALSE)

The AND Function

The AND function is used to compare more than one condition. It returns TRUE
only if all of the conditions are met, and takes the format:
=AND(condition1, condition2,...)
For example, you could use the following formula:
=AND(B2 > 400, C2 < 300) where,
B2 > 400 is the first condition being tested
C2 < 300 is the second condition being tested

Adarsha College Of Management And Science,Kolar Page 17

 INFORMATION SYSTEM AND COMPUTERS
2023

This will only return the result TRUE if the value in cell B2 is greater than 400 and
the value in cell C2 is less than 300. In all other situations, the result will be
FALSE

The OR Function
The OR function is also used to compare more than one condition. It returns TRUE
if any of the conditions are met, and takes the format:
=OR(condition1, condition2,...)
For example, you could use the following formula:
=OR(B2 > 400, C2 < 300) where,
B2 > 400 is the first condition being tested
C2 < 300 is the second condition being tested
This will return the result TRUE if either the value in cell B2 is greater than 400 or
the value in cell C2 is less than 300. The result will be FALSE only if neither of
the conditions is met

The NOT Function

returning the logical opposite of the condition test. If the condition test
returns the value TRUE, the NOT function will return the value FALSE.

Multiple IF Conditions in Excel

The multiple IF conditions in Excel are IF statements contained within another IF

statement.They are used to test multiple conditions simultaneously and return

distinct values

Adarsha College Of Management And Science,Kolar Page 18

 INFORMATION SYSTEM AND COMPUTERS
2023
Syntax

IF (condition1, result1, IF (condition2, result2, IF (condition3, result3,………..)))

VLOOKUP Function
The VLOOKUP function is a premade function in Excel, which allows searches across columns.

It is typed =VLOOKUP and has the following parts:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

What is the HLOOKUP Function?

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.

Formula

=HLOOKUP(value to look up, table area, row number)

How to sort in Excel?

1. Select a single cell in the column you want to sort.

2. On the Data tab, in the Sort & Filter group, click. to perform an ascending
sort (from A to Z, or smallest number to largest).
3. Click. to perform a descending sort (from Z to A, or largest number to
smallest).

Create a custom conditional formatting rule

1. Select the range of cells, the table, or the whole sheet that you want to apply
conditional formatting to.
2. On the Home tab, click Conditional Formatting.
Adarsha College Of Management And Science,Kolar Page 19
 INFORMATION SYSTEM AND COMPUTERS
2023

3. Click New Rule.

4. Select a style, for example, 3-Color Scale, select the conditions that you
want, and then click OK.

The Page Layout Tab

The Page Layout tab allows you to change the look and feel of your printed
worksheet. Here you can enhance the style and presentation of everything in your
worksheet, control how the worksheet is printed, and arrange the objects within the
worksheet

What is Filter in Excel?

The filter in excel helps display relevant data by eliminating the irrelevant entries
temporarily from the view. The data is filtered as per the given criteria. The
purpose of filtering is to focus on the crucial areas of a dataset.

Data Analysis
This section illustrates the powerful features Excel has to offer to analyze data.

1 Sort: You can sort your Excel data on one column or multiple columns. You can
sort in ascending or descending order.

2 Filter: Filter your Excel data if you only want to display records that meet certain
criteria.

3 Conditional Formatting: Conditional formatting in Excel enables you to highlight

cells with a certain color, depending on the cell's value.

4 Charts: A simple Excel chart can say more than a sheet full of numbers. As you'll
see, creating charts is very easy.

5 Pivot Tables: Pivot tables are one of Excel's most powerful features. A pivot
table allows you to extract the significance from a large, detailed data set.

Adarsha College Of Management And Science,Kolar Page 20

 INFORMATION SYSTEM AND COMPUTERS
2023

6 Tables: Master Excel tables and analyze your data quickly and easily.

7 What-If Analysis: What-If Analysis in Excel allows you to try out different
values (scenarios) for formulas.

8 Solver: Excel includes a tool called solver that uses techniques from the
operations research to find optimal solutions for all kind of decision problems.

9 Analysis ToolPak: The Analysis ToolPak is an Excel add-in program that

provides data analysis tools for financial, statistical and engineering data analysis.

Descriptive Statistic :Descriptive Statistic quickly summarizes your data and

gives you a few data points that you can use to quickly understand the entire data
set.

To get the Descriptive Statistics in Excel, you need to have the Data Analysis
Toolpak enabled.

Enabling Data Analysis Toolpak

Below are the steps to enable the Data Analysis Toolpak in Excel:

1. Open any Excel document

2. Click the File tab
3. Click on Options. This will open the Excel Options dialog box

4. In the Excel Options dialog box, click on Add-ins in the left pane
5. From the Manage drop-down (which is at the bottom of the dialog box),
select ‘Excel Add-ins’

6. Click on the Go button

7. In the Add-ins dialog box that shows up, check the Analysis Toolpak option
8. Click OK

Adarsha College Of Management And Science,Kolar Page 21

 INFORMATION SYSTEM AND COMPUTERS
2023
Getting the Descriptive Analysis

1. Click the Data tab

2. In the Analysis group, click on Data Analysis
3. In the Data Analysis dialog box that opens, click on Descriptive Statistics
4. Click OK
5. In the Descriptive Statistics dialog box, specify the input range that has the
data. Note that I have only used Column B as the data source (as you can
only use numeric data as the input here)
6. If your data has headers, check the ‘Labels in first row’ option
7. Select the New Worksheet Ply option (this will give the result in a new
sheet)
8. Select the statistics options you want (you need to select atleast one, and can
select all four)
9. Click OK

A PivotTable

A PivotTable is a powerful tool to calculate, summarize, and analyze data that

lets you see comparisons, patterns, and trends in your data.

Create a PivotTable in Excel for Windows

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

2. Select Insert > PivotTable.
3. This will create a PivotTable based on an existing table or range. ...
4. Choose where you want the PivotTable report to be placed. ...
5. Click OK.

Adarsha College Of Management And Science,Kolar Page 22

 INFORMATION SYSTEM AND COMPUTERS
2023

Steps to Create a chart

1. Select the data for which you want to create a chart.

2. Click INSERT > Recommended Charts.
3. On the Recommended Charts tab, scroll through the list of charts that
Excel recommends for your data, and click any chart to see how your data
will look.

If you don’t see a chart you like, click All Charts to see all the available
chart types.

4. When you find the chart you like, click it > OK.
5. Use the Chart Elements, Chart Styles, and Chart Filters buttons, next to
the upper-right corner of the chart to add chart elements like axis titles or
data labels, customize the look of your chart, or change the data that is
shown in the chart.
6. To access additional design and formatting features, click anywhere in the
chart to add the CHART TOOLS to the ribbon, and then click the options
you want on the DESIGN and FORMAT tabs.

Adarsha College Of Management And Science,Kolar Page 23