pg

pg. 1
Excel and Data analysis
Seminar Report

in partial fulfilment of the requirements for the award of degree of
MASTER OF COMPUTER APPLICATIONS
by
SRISHAILA (Reg. No: 170970025)
Under the guidance of

Dr. Rohini Rao
Assistant Professor -senior Scale
Department of computer
Application.
M.I.T., Manipal – 576 104
Mrs. Linda Varghese
Assistant Professor
Department of computer
Application.
M.I.T., Manipal – 576104

pg. 2
ABSTRACT

The ability to analyse data is a powerful skill that helps you make
better decisions. Microsoft Excel is one of the top tools for data
analysis.

The use of Excel is widespread in the industry. It is a very powerful data
analysis tool and almost all big and small businesses use Excel in their
day to day functioning.

Excel provide commands and tools that make our data analysis tasks
easy. we can avoid many time consuming and complex calculations
using Excel.

Here we are using some excel features like Sorting, Filtering, What if
analysis, Spark lines, Pivot tables, Pivot charts, Conditional Formatting,
Color code for manipulating data.

pg. 3
Contents
Abstract …………………………………………………………………………………2
List of Tables ………………………………………………………………………….4
List of Figures …………………………………………………………………………4
Introduction
1.1 Objectives …………………………………………………………………………5
Literature Review ………………………………………………………5
Functionality of Excel Features
2.1 Sorting …………………………………………………………………………..….7
2.2 Filtering ……………………………………………………………………………..7
2.3 What-If Analysis …………………………………………………………….…..8
2.4 Sparklines …………………………………………………………………….……10
2.5 Pivot Tables ………………………………………………………………….……10
2.6 Pivot Charts ……………………………………………………………………….11
2.7 Color Coding ………………………………………………………………………12
2.8 Conditional Formatting ………………………………………………………12
2.8.1 Highlight Cells Rules
2.8.2 Top/bottom
2.8.3 Color Scales
2.8.4 Icon Sets
2.8.5 Clear Rules

pg. 4

Summary/Conclusion
3.1 Conclusion ………………………………………………………………………….14
References …………………………………………………………………..14

List of Tables
2.2 Sorting vs Filtering ………………………………………………….…………8
2.5 Excel Table vs Pivot Table ……………………………………………..….11

List of Figures
Figure 2.1 Sorting …………………………………………………………………….7
Figure 2.2 Filtering ……………………………………………………………………8
Figure 2.3 What-If Analysis ……………………………………………………….8
Figure 2.4 Sparklines ………………………………………………………………..10
Figure 2.5 Pivot Tables ……………………………………………………………..10
Figure 2.6 Pivot Charts ……………………………………………………………..11
Figure 2.8 Conditional Formatting …………………………………………….12

pg. 5

Introduction
1.1 Objectives
Excel provide commands and tools that make our data analysis tasks
easy. we can avoid many time consuming and complex calculations
using Excel.

Review of Literature
? What is Data Analysis (Definition)
It is a process of inspecting, cleaning, transforming, and
modelling data.
The main goal of data analysis is to highlight the useful
information, suggest the conclusions, and support the decision
making.
? History of Spreadsheet
A paper called ‘A Brief History of Spreadsheets’ by D. J. Power
(Editor, DSSResources.COM) in his paper he says that
Spreadsheets have been used by accountants for hundreds of
years. But Computerized or electronic spreadsheets are of much
more recent origin. And was invented by Dan Bricklin.

Muliplan was Microsoft’s first electronic spreadsheet program It
was introduced in 1982 as a competitor for VisiCalc. VisiCalc was
the first electronic spreadsheet program.

Excel and Data analysis

pg. 6

Muliplan was very popular on CP/M systems, but on MS-DOS
systems it lost fame to Lotus 1-2-3. This thing motivated
Microsoft to develop another spreadsheet product Excel.
The first version of Excel was released in 1985 for Mac. Later in
November 1987, the first Windows version was released.

? Microsoft Excel
Excel is one of the powerful tool which are used to organize,
calculate and format a data.
Excel provides many commands and we are using excel as tool
for data analysis .It makes data analysis task easy.

? Features
Excel comes up with plenty of features. Some basic and
important features are discussing in this topic such as, Sorting,
Filtering, What-If Analysis, Sparklines, Pivot Tables, Pivot Charts,
Color Coding and Conditional Formatting.

Excel and Data analysis

pg. 7

Functionality of Excel Features

2.1 Sorting
You can sort your Excel data on one column or multiple columns. You
can sort in ascending or descending order.
To sort in ascending order, on the Data tab, in the Sort ; Filter group,
click AZ
To sort in descending order, click ZA.

figure 2.1

2.2 Filtering
You can Filter your Excel data if you only want to display records that
meet certain criteria.
Click any single cell inside a data set.
Excel and Data analysis

pg. 8

figure 2.2
On the Data tab, in the Sort ; Filter group, click Filter.
Sorting vs Filtering:
Sorting Filtering
Same search results, different
order.
Filtering reduces the number of
results
Sorting takes a set of
data and sorts it by a chosen
metric or letter
Filtering removes data that’s
irrelevant to your analysis
2.3 What-If Analysis
What-If Analysis in Excel allows you to try out different values
(scenarios) for formulas.

figure 2.3

Excel and Data analysis

pg. 9

A Scenario is a set of values that Excel saves and can substitute
automatically in cells on a worksheet. You can create and save
different groups of values on a worksheet and then switch to any of
these new scenarios to view different results.
For example, suppose you have two budget scenarios: a worst case
and a best case. You can use the Scenario Manager to create both
scenarios on the same worksheet, and then switch between them. For
each scenario, you specify the cells that change and the values to use
for that scenario. When you switch between scenarios, the result cell
changes to reflect the different changing cell values.

If you know the result that you want from a formula, but you are not
sure what input value the formula requires to get that result, you can
use the Goal Seek feature. For example, suppose that you need to
borrow some money. You know how much money you want, how long
a period you want in which to pay off the loan, and how much you can
afford to pay each month. You can use Goal Seek to determine what
interest rate you must secure in order to meet your loan goal.
If you have a formula that uses one or two variables, or multiple
formulas that all use one common variable, you can use a Data Table
to see all the outcomes in one place. Using Data Tables makes it easy
to examine a range of possibilities at a glance. Because you focus on
only one or two variables, results are easy to read and share in tabular
form.
Excel and Data analysis

pg. 10

2.4 Sparklines
A Sparkline is basically a little chart displayed in a cell representing
your selected data set that allows you to quickly and easily spot trends
at a glance.

figure 2.4

2.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.

figure 2.5

Excel and Data analysis

pg. 11

Excel Table vs Pivot Table:
Excel Table Pivot Table
An Excel table is simply a set of
rows and columns in a worksheet
that contains related data and is
displayed in a specific table
format
A PivotTable is an interactive
table
Helpful for calculating values and
displaying totals and grand totals
Quickly summarizes large
amounts of numeric data, which
you can then analyze in detail.

2.6 Pivot Charts
A pivot chart is the visual representation of a pivot table in Excel. Pivot
charts and pivot tables are connected with each other.

figure 2.6

Excel and Data analysis

pg. 12

2.7 Color Coding
Color coding makes your sheet faster to read, harder to mess up, and
more professional.

2.8 Conditional Formatting
Conditional formatting in Excel enables you to highlight cells with a
certain color, depending on the cell’s value.

figure 2.8

Excel and Data analysis

pg. 13

2.8.1 Highlight Cells Rules
The Excel Conditional Formatting Highlight Cells Rules allow you to
apply formatting to highlight cells that satisfy one or more specific
conditions.
These conditions can relate to numeric values (e.g. greater than,
between), dates (e.g. a date occurring…) or text values (e.g. text
containing…). You can also use the Highlight Cells rules to highlight
cells containing duplicate values.
2.8.2 Top/bottom
The Excel Conditional Formatting Top/Bottom Rules allow you to
apply formatting to cells that satisfy a statistical condition in relation
to other cells in the range (e.g. above average, within top 10%, etc.).
These conditions will only be applied to cells containing numeric
values.
2.8.3 Color Scales
When using color scales in conditional formatting, Excel assigns one
color to the lowest value, and another to the highest value. Other
values are assigned a weighted blend of color. This makes it easy to
see general patterns in data, especially with a large data set.
2.8.4 Icon Sets
For conditional formatting, you can use icon sets to mark the values
which meet your conditions. The icon sets are more attractive to draw
your attention. This article will show you how to use icon sets to
highlight values in conditional formatting in Excel in details.

Excel and Data analysis

pg. 14

2.8.5 Clear Rules
To clear a conditional formatting rule we can use Clear Rules.

Summary/Conclusion
3.1 Conclusion
Using above Excel Features we can sort our excel data , filter our data
,can apply effect to a data and we can represent data as pivot tables
and pivot charts.

References
1.www.tutorials.com
2.www.edx.org.
3.www.excel-easy.com
4. www.exceltrick.com

Excel and Data analysis