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