DATA ANALYSIS WITH SPREADSHEETS LAB

PRACTICAL FILE

Submitted for partial fulfillment for the award of the Degree of

BACHELOR OF COMMERCE (HONS)

{BCOM (H) 2022 – 2025}

Under the guidance of

Submitted by
Harsh Raj Sarraf
00918088822
DELHI TECHNICAL CAMPUS
(Affiliated to Guru Gobind Singh Indraprastha University)
ACKNOWLEGDEMENT

A lot of effort has gone into this training report. My thanks are due to
many people with whom I have been closely associated. I would like to
thank all those who have contributed in the completion of this project.
First of all, I would like to send my sincere thanks to Mr. Rakesh
Razdan Sir for his kind advices as well as the helpful hand which he
provided me in order to complete this project. I would also like to
thank my mentor for her teachings as well as training . I am also very
thankful to all the faculty members of Delhi Technical Campus who
guided me and provided their support whenever needed. I thank all
those who knowingly and unknowingly have helped me in the
fulfillment of the project. And last but not the least I would like to
thank my entire beloved family and friends for providing me monetary
as well as nonmonetary support, as and when required, without which
this project would not have been submitted.

Harsh Raj Sarraf

STUDENT UNDERTAKING

I, Harsh Raj Sarraf, a student of bachelors of commerce from Delhi

Technical Campus, hereby declare that I have completed The Data
Analysis as a part of the course requirement.

I further declare that the information presented in this project is true

and original to the best of my knowledge.

Harsh Raj Sarraf

CERTIFICATE

This is to certify that I Harsh Raj Sarraf of course B. Com (Honors),

(Batch 2022-25) a student of Delhi Technical Campus, Greater Noida,
has undertaken the project file on Data Analysis. To the best of my
knowledge, the survey, data collection, & analysis work for preparing
the project has been carried out in partial fulfillment of the
requirements for the award of B. COM (H), under my guidance and
supervision.
INDEX

TOPIC PA
GE
NO.
UNIT 1
(i) BASIC FUNCTIONS 2
▪ Count 4
▪ Count A 5
▪ Count Blank 6
▪ Sum 7
▪ Max 8
▪ Min 9
▪ Average 10
(ii) DATA MANUPILATION (Sorting & Filtering)
a) Data Filtering 11
● Number Filters (between, Top 10 , greater than, 12
less than)
● Filter by color 18
b) Sorting Data 20
● Sort A to Z 20
● Sort Largest to smallest 21
c) Transpose data 24
(iii) DATA MANUPILATION 25
(Conditional Formatting)
▪ Conditional Formatting- Highlight Cell rules (greater 25
than, less than,
▪ between, equal to, text that contains)
▪ Conditional Formatting - Duplicate values 32
▪ Conditional Formatting - Top/ Bottom rules 34
▪ Conditional Formatting - Data Bars 37
▪ Conditional Formatting - Color Scales 39
▪ Format Cells: – Number , Alignment , Font, Border, Fill 40
▪ Cell Styles 46
(iv) Functions using Absolute Reference 46
(v) Functions using Relative reference 47
UNIT 2
(i) IF FUNCTIONS 48
▪ Count If 48
▪ Sum IF 49
▪ Average If 50
▪ COUNTIFS 51
▪ SUMIFS 52
▪ AVERAGEIFS 53
▪ NESTED IF 54
(ii) HLOOKUP 57
(iii) VLOOKUP (across multiple sheets) 58

UNIT 3
(i) Pivot Table and its tools 60
(ii) Pivot Chart and its tools 62
(iii) Power Pivot 65
▪ Installing Power Pivot in Excel 65
▪ Loading data into Power Pivot 67
▪ Calculating data in Power Pivot 71
▪ Analysing data in Power Pivot 71
(iv) Power Query 74
▪ Loading data in Power Query 74
▪ Calculating data in Power Query 78
▪ Analysing data in Power Query 81
(v) Power map Add inns (Installation) 83
● Add maps using data 83
(vi) Mouse and Keyboard shortcuts 85

UNIT 4
● Line Chart 95
● Bar Graphs 96
● Pie Charts 96
● Scatter Plot 97
● Histogram 97
● Assignment – Performing excel functions 98
on spreadsheet
UNIT - 1
Count Function
Use the COUNT function to get the number of entries in a number field that is in a range or
array of numbers. For example, you can enter the following formula to count the numbers in
the range A1:A20: =COUNT (A1:A20). In this example, if five of the cells in the range
contain numbers, the result is 5.

Result
COUNT A

We use the COUNT function to get the number of entries in a number field that is in a range
or array of numbers. For example, you can enter the following formula to count the
numbers in the range A1:A20: =COUNT (A1:A20).
Result

Count Blank

The COUNTBLANK Function is categorized under Excel STATISTICAL functions.

COUNTBLANK will count the number of empty cells in a given range of cells. In financial
analysis, the function can be useful in highlighting or counting empty cells in a given range.
Result
SUM

The SUM function adds values. You can add individual values, cell references or ranges or a
mix of all three.

● SUM (A2:A10) Adds the values in cells A2:10.

● SUM (A2:A10, C2:C10) Adds the values in cells A2:10, as well as cells C2:C10.

Result
MAX

The MAX function is a premade function in Excel, which finds the highest number in a
range. The function ignores cells with text. It will only work for cells with numbers.
Result
MIN

The MIN function is used in Excel to find the smallest number in the selected range

Result
Average
MS Excel’s AVERAGE function is a statistical function. The average function calculates the
arithmetic mean of a series of data. That is, it adds all the values of the variable we are
analysing and divides them by the number of values added.
Result

ii)Data Manipulation (Sorting and Filtering):

(A) Filtering data

Result
(B) Number Filters

Greater than: -
Result

Less Than: -
Result
Between: -
Result

TOP 10
Result
B) Sorting data
(

1. Sort A to Z

Result
2. Sort Largest to Smallest:

Result
3. SORT by Color
4. Transpose data
Result

iii) Data Manipulation (Conditional Formatting)

(

1. Highlight Cell Rules

A)Greater than
Result
B) Less than

Result
c) Between
Result

Duplicate Values
Result
Text that contains
Result

2) Top Bottom Rules

a) Top 10
a) Top 10

Result
b)Bottom 10
Result
Data Bars

Result
Color Scales

Result
Format Cells

a) Numbers
Result

b Alignment
Result
C Font

Result
CELL STYLES

Result
Customized Cell Style

Result
Absolute Reference
In Excel, an absolute reference is a cell reference in which the column and
row coordinates stay constant while copying a formula from one cell to the
other. A dollar symbol ($) is used before the coordinates to correct them.
Shortcut Key- F4
Result

Relative Reference

Relative cell references are basic cell references that adjust and change when
copied or when using AutoFill. Example: =SUM (B5:B8), as shown below,
changes to =SUM (C5:C8) when copied across to the next cell.
Result
UNIT 2

i) IF Functions
Count IF

Result
SUM IF
Result

SUM OF SALARIES OF MANAGER

Result

Average IF
Result

Count IFS:
To find out the number of employees whose salaries has been paid and leaves
are greater than 5
SUM IFS
To find out the sum of the salary of employees who’s salary has been paid and
leaves are less than 8.
SUM OF SALARIES PAID TO EMPLOYEES WITH LEAVES LESS
THAN 8

Result
SUM OF SALARIES NOT PAID TO EMPLOYEES WITH LEAVES LESS
THAN 9

Result
SUM OF SALARIES PAID TO EMPLOYEES WITH LEAVES GREATER
THAN 5
Result

AVERAGE IFS
Result

NESTED IF
Result

Nested List
Result

VLOOKUP
HLOOKUP

Result
Unit -3
(i) Pivot Table and It’s tools: -
A PivotTable is an interactive way to quickly summarize large amounts of
data. You can use a PivotTable to analyse numerical data in detail, and
answer unanticipated questions about your data.

Result
Pivot Chart and it’s tools: --
Result
UNIT 4

Line chart
Bar graphs
Pie chart
Scatter plot

Histogram