lab-manaul-final-data-analytics-with-excel-bcs358a

lOMoARcPSD|35013525
Lab manaul final - Data Analytics with Excel: BCS358A
3rd sem cse (Visvesvaraya Technological University)
Studocu is not sponsored or endorsed by any college or university

Downloaded by Anshu Man (anshuman26112005@gmail.com)
lOMoARcPSD|35013525
Data Analytics with Excel: BCS358A Academic Year: 2023-24
THE OXFORD COLLEGE OF ENGINEERING

Department of Computer Science & Engineering
LAB MANUAL
Semester-III
BCS358A- Data Analytics with Excel

Academic Year: 2023-2024
Prepared By,
Prof.Manjula L
Prof.Thendral
Dept. of CSE,TOCE,Bengaluru.

lOMoARcPSD|35013525
THE OXFORD COLLEGE OF ENGINEERING

Bommanahalli, Hosur Road, Bangalore – 560068
(Approved by AICTE, New Delhi, accredited by NBA, NAAC Grade A, New Delhi & Affiliated to VTU, Belagavi)
(Affiliated To Visvesvaraya Technological University, Belagavi)
DEPARTMENT VISION:
To produce technocrats with creative technical knowledge and intellectual skills to sustain and excel
in the highly demanding world with confidence.
DEPARTMENT MISSION
 M1 - To produce the Best Computer Science Professionals with intellectual skills.
 M2 - To provide a vibrant Ambience that promotes Creativity, technology Component and
Innovations for the new Era.
 M3 - To pursue Professional Excel with Ethical and Moral Values to sustain in the highly
demanding world.

lOMoARcPSD|35013525
Data Analytics with Excel Semester 3

Course Code BCS358A CIE Marks 50
Teaching Hours/Week (L:T:P: S) 0:0:2:0 SEE Marks 50
Credits 01 Exam Hours 100

Examination type (SEE) Practical
Course objectives:
● To Apply analysis techniques to datasets in Excel
● Learn how to use Pivot Tables and Pivot Charts to streamline your workflow in Excel
● Understand and Identify the principles of data analysis
● Become adept at using Excel functions and techniques for analysis
● Build presentation ready dashboards in Excel

Sl.NO Experiments
1 Getting Started with Excel: Creation of spread sheets, Insertion of rows and columns, Drag
& Fill, use of Aggregate functions.
2
Working with Data : Importing data, Data Entry & Manipulation, Sorting & Filtering.
3
Working with Data: Data Validation, Pivot Tables & Pivot Charts.
4 Data Analysis Process: Conditional Formatting, What-If Analysis, Data Tables, Charts &
Graphs.
5
Cleaning Data with Text Functions: use of UPPER and LOWER, TRIM function, Concatenate.
6 Cleaning Data Containing Date and Time Values: use of DATEVALUE function, DATEADD and
DATEDIF, TIMEVALUE functions.
7 Conditional Formatting: formatting, parsing, and highlighting data in spreadsheets during

data analysis.
8 Working with Multiple Sheets: work with multiple sheets within a workbook is crucial for
organizing and managing data, perform complex calculations and create comprehensive
reports.
9 Create worksheet with following fields: Empno, Ename, Basic Pay(BP), Travelling
Allowance(TA), Dearness Allowance(DA), House Rent Allowance(HRA), Income Tax(IT),
Provident Fund(PF), Net Pay(NP). Use appropriate formulas to calculate the above scenario.
Analyse the data using appropriate chart and report the data.
10 Create worksheet on Inventory Management: Sheet should contain Product code, Product
name, Product type, MRP, Cost after % of discount, Date of purchase. Use appropriate
formulas to calculate the above scenario. Analyse the data using appropriate chart and report
the data.

lOMoARcPSD|35013525
11 Create worksheet on Sales analysis of Merchandise Store: data consisting of Order ID,
Customer ID, Gender, age, date of order, month, online platform, Category of product, size,
quantity, amount, shipping city and other details. Use of formula to segregate different
categories and perform a comparative study using pivot tables and different sort of charts.
12 Generation of report & presentation using Autofilter &macro.
Course outcomes (Course Skill Set):

At the end of the course the student will be able to:
● Use advanced functions and productivity tools to assist in developing worksheets.
● Manipulate data lists using Outline and PivotTables.
● Use Consolidation to summarise and report results from multiple worksheets.
● Apply Macros and Autofilter to solve the given real world scenario.

lOMoARcPSD|35013525
Assessment Details (both CIE and SEE)

The weightage of Continuous Internal Evaluation (CIE) is 50% and for Semester End Exam (SEE) is 50%. The
minimum passing mark for the CIE is 40% of the maximum marks (20 marks out of 50) and for the SEE
minimum passing mark is 35% of the maximum marks (18 out of 50 marks). A student shall be deemed to
have satisfied the academic requirements and earned the credits allotted to each subject/ course if the
student secures a minimum of 40% (40 marks out of 100) in the sum total of the CIE (Continuous Internal
Evaluation) and SEE (Semester End Examination) taken together.
Continuous Internal Evaluation (CIE):

CIE marks for the practical course are 50 Marks.
The split-up of CIE marks for record/ journal and test are in the ratio 60:40.
● Each experiment is to be evaluated for conduction with an observation sheet and record
write-up. Rubrics for the evaluation of the journal/write-up for hardware/software
experiments are designed by the faculty who is handling the laboratory session and are
made known to students at the beginning of the practical session.
● Record should contain all the specified experiments in the syllabus and each experiment
write-up will be evaluated for 10 marks.
● Total marks scored by the students are scaled down to 30 marks (60% of maximum
marks).
● Weightage to be given for neatness and submission of record/write-up on time.
● Department shall conduct a test of 100 marks after the completion of all the experiments
listed in the syllabus.
● In a test, test write-up, conduction of experiment, acceptable result, and procedural

knowledge will carry a weightage of 60% and the rest 40% for viva-voce.
● The suitable rubrics can be designed to evaluate each student’s performance and learning
ability.
● The marks scored shall be scaled down to 20 marks (40% of the maximum marks).
The Sum of scaled-down marks scored in the report write-up/journal and marks of a test is the
lOMoARcPSD|35013525
total CIE marks scored by the student.
Semester End Evaluation (SEE):
● SEE marks for the practical course are 50 Marks.
● SEE shall be conducted jointly by the two examiners of the same institute, examiners are appointed
by the Head of the Institute.
● The examination schedule and names of examiners are informed to the university before the
conduction of the examination. These practical examinations are to be conducted between the
schedule mentioned in the academic calendar of the University.

lOMoARcPSD|35013525
● All laboratory experiments are to be included for practical examination.
● (Rubrics) Breakup of marks and the instructions printed on the cover page of the answer
script to be strictly adhered to by the examiners. OR based on the course requirement
evaluation rubrics shall be decided jointly by examiners.
● Students can pick one question (experiment) from the questions lot prepared by the examiners
jointly.
● Evaluation of test write-up/ conduction procedure and result/viva will be conducted jointly by
examiners.
General rubrics suggested for SEE are mentioned here, writeup-20%, Conduction procedure and
result in -60%, Viva-voce 20% of maximum marks. SEE for practical shall be evaluated for 100
marks and scored marks shall be scaled down to 50 marks (however, based on course type, rubrics
shall be decided by the examiners)
Change of experiment is allowed only once and 15% of Marks allotted to the procedure part are to
be made zero.
The minimum duration of SEE is 02 hours
Suggested Learning Resources:
● Berk & Carey - Data Analysis with Microsoft® Excel: Updated for Offi ce 2007®, Third
Edition, © 2010 Brooks/Cole, Cengage Learning, ISBN-13: 978-0-495-39178-4
● Wayne L. Winston - Microsoft Excel 2019: Data Analysis And Business Modeling, PHI,
ISBN: 9789389347180
● Aryan Gupta - Data Analysis in Excel: The Best Guide.
(https://www.simplilearn.com/tutorials/excel-tutorial/data-analysis-excel)
Dept. of CSE

lOMoARcPSD|35013525
An Introduction to MS Excel
What is Microsoft Excel?

Microsoft Excel is a spreadsheet 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, macOS, Android and
iOS.
A Excel spreadsheet 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.
MS Excel is a spreadsheet program where one can record data in the form of tables. It is easy to analyze data
in an Excel spreadsheet. The image given below represents how an Excel spreadsheet looks like:
How to open MS Excel?

To open MS Excel on your computer, follow the steps given below:
Dept. of CSE

lOMoARcPSD|35013525
 Click on Start
 Then All Programs
 Next step is to click on MS Office
 Then finally, choose the MS-Excel option
Alternatively, you can also click on the Start button and type MS Excel in the search option available.
What is a cell?
A spreadsheet is in the form of a table comprising rows and columns. The rectangular box at the intersection
point between rows and columns forms a cell.
Understanding the worksheet (Rows and Columns, Sheets, Workbooks)
A worksheet is a collection of rows and columns. When a row and a column meet, they form a cell. Cells
are used to record data. Each cell is uniquely identified using a cell address. Columns are usually labelled
with letters while rows are usually numbers.
A workbook is a collection of worksheets. By default, a workbook has three cells in Excel. You can delete
or add more sheets to suit your requirements. By default, the sheets are named Sheet1, Sheet2 and so on and
so forth. You can rename the sheet names to more meaningful names i.e. Daily Expenses, Monthly Budget,
etc.
Features of MS Excel
Various editing and formatting can be done on an Excel spreadsheet. Discussed below are the various
features of MS Excel.
The image below shows the composition of features in MS Excel:
 Home
Comprises options like font size, font styles, font colour, background colour, alignment,
formatting options and styles, insertion and deletion of cells and editing options.
 Insert
Comprises options like table format and style, inserting images and figures, adding graphs,
charts and sparklines, header and footer option, equation and symbols.
 Page Layout
Themes, orientation and page setup options are available under the page layout option.
 Formulas
Since tables with a large amount of data can be created in MS excel, under this feature, you
can add formulas to your table and get quicker solutions .
Dept. of CSE

lOMoARcPSD|35013525
 Data
Adding external data (from the web), filtering options and data tools are available under this
category.
 Review
Proofreading can be done for an excel sheet (like spell check) in the review category and a
reader can add comments in this part .
 View
Different views in which we want the spreadsheet to be displayed can be edited here. Options
to zoom in and out and pane arrangement are available under this category.
Ms-Excel shortcuts:
1. Ctrl+N: To open a new workbook.
2. Ctrl+O: To open a saved workbook.
3. Ctrl+S: To save a workbook.
4. Ctrl+C: To copy the selected cells.
5. Ctrl+V: To paste the copied cells.
6. Ctrl+X: To cut the selected cells.
7. Ctrl+W: To close the workbook.
8. Delete: To remove all the contents from the cell.
9. Ctrl+P: To print the workbook.
10. Ctrl+Z: To undo.
Benefits of Using MS Excel

MS Excel is widely used for various purposes because the data is easy to save, and information can be added and
removed without any discomfort and less hard work.
Given below are a few important benefits of using MS Excel:
 Easy To Store Data: Since there is no limit to the amount of information that can be saved in a spreadsheet,
MS Excel is widely used to save data or to analyse data. Filtering information in Excel is easy and convenient.
 Easy To Recover Data: If the information is written on a piece of paper, finding it may take longer, however,
this is not the case with excel spreadsheets. Finding and recovering data is easy.
 Application of Mathematical Formulas: Doing calculations has become easier and less time-taking with the
formulas option in MS excel
 More Secure: These spreadsheets can be password secured in a laptop or personal computer and the
probability of losing them is way lesser in comparison to data written in registers or piece of paper.
 Data at One Place: Earlier, data was to be kept in different files and registers when the paperwork was done.
Now, this has become convenient as more than one worksheet can be added in a single MS Excel file.
 Neater and Clearer Visibility of Information: When the data is saved in the form of a table, analysing
becomes easier. Thus, information in a spreadsheet is more readable and understandable.
Applications of MS Excel:
 Data Entry and Storage
 Performing Calculations
 Data Analysis and Interpretation
 Reporting and Visualizations
 Accounting and Budgeting
Dept. of CSE

lOMoARcPSD|35013525
 Collection and Verification of Business Data

 Calendars and Schedules
 Administrative and Managerial Duties
 Forecasting
 Automating Repetitive Tasks
Experiment No: 1
Aim:
Getting Started with Excel: Creation of spread sheets, Insertion of rows and columns, Drag & Fill, use of
Aggregate functions.
1. Create a suitable examination database and find the sum of the marks(total) of each Student
,Average,pass or fail and grade secured by each student.
Rules
 Pass if marks in each subject >=35,

 Distinction if average>=70,
 First class if average>=60 but <70,
 Second class if average>=50 but<60,
 Third class if average>=35andbut<50,
 Fail if marks in any subject is<35.
Display average marks of the class, subject wise and pass percentage
Solution:
To find the grade of a student we need to do the following steps
 Step1: Typing Student database in Excel2007
Type the student database with the required fields starts fromA1 cell as follows
Dept. of CSE

lOMoARcPSD|35013525
 Step2: To find Total Marks of Student
To find the total marks of a student click on the cell“G2” and type the following formula
To find the total marks for the remaining students select “G2” cell and drag down to the
remaining students.
 Step 3: To find Average marks
To find the average marks of the student click on the cell“H2”and type the following formula
Dept. of CSE

lOMoARcPSD|35013525
To find the average marks for the remaining students select “H2” cell and drag down to the all
the students.
 Step4: To Check Pass or Fail
To check whether the student is Pass or Fail select the cell “I2” and type the following formula
To check the remaining students are Pass/Fail select the cell“I2”and drag down to all the students
 Step5: To find Grade
To find the grade of a student click on the cell “J2” and type the following formula
Dept. of CSE

lOMoARcPSD|35013525
To find the grade for the remaining students select “J2”cell and drag down to the all the students
Finally we get the following student database with total, average and grade
Output:
Dept. of CSE

lOMoARcPSD|35013525
Experiment No: 2
Aim:
Working with Data : Importing data, Data Entry & Manipulation, Sorting & Filtering.
Microsoft Excel is a powerful tool for managing and analyzing data. Whether you are dealing with a large
dataset or a simple list, Excel offers various features for importing data, entering information, manipulating
content, sorting, and filtering.
Importing Data:
Excel allows you to import data from various sources, making it a versatile tool for handling diverse datasets.
Step 1:Open Excel and Navigate to Data Import
Opening Excel:
Start by launching Microsoft Excel on your computer.
Access Data Import:
Click on the "File" tab in the ribbon at the top of Excel.
Dept. of CSE

lOMoARcPSD|35013525
Select "Open" from the menu to open a previously saved file.
Alternatively, choose "Get External Data" or "Import" based on your Excel version and select the source of
data (e.g., CSV, Database, Web).
Import Data:
If you choose "From Text/CSV," navigate to the location of your CSV file and click "Import."
Excel will guide you through the import process, allowing you to specify delimiters, data types, and other
settings.
Data Entry and Manipulation:
Excel provides a user-friendly interface for entering data and performing basic manipulations.
Step 2: Data Entry and Basic Manipulation
Entering Data:
Click on a cell and start typing your data. Press Enter to move to the cell below or use the arrow keys to
navigate.
Excel automatically adjusts the width of the cell to fit your data.
Data Manipulation:
Copying and Pasting:
Select a range of cells, right-click, and choose "Copy."
Move to the destination, right-click, and choose "Paste" to copy the data.
Formulae and Functions:
Enter formulas in cells to perform calculations. For example, =SUM(A2:A10) calculates the sum of cells A2
to A10.
Excel offers a wide range of functions (AVERAGE(), IF(), VLOOKUP(), etc.) for advanced calculations.
Data Validation:
Select a range, go to the "Data" tab, and click on "Data Validation."
Set criteria to restrict data entry, such as allowing only numbers between a specific range.
Sorting Data:
Dept. of CSE

lOMoARcPSD|35013525
Sorting data helps in organizing information in a meaningful way for better analysis.
Step 3: Sorting Data
Sorting Ascending/Descending:
Select the column you want to sort.
Click on the "Data" tab and choose "Sort A to Z" for ascending or "Sort Z to A" for descending order.
Alternatively, right-click the selected column and choose "Sort."
Complex Sorting:
For sorting based on multiple criteria (e.g., first sort by Category, then by Price within each Category), use
the "Sort" dialog box.
Specify the primary and secondary sort columns along with the sort order for each.
To Sort the Data:
Steps: 1. Select data on list to be sorted.
For example salary in the above figure.
2. Click the Data Menu and select the sort option. The sort dialog box appears.
Dept. of CSE

lOMoARcPSD|35013525
3. Select the ascending and descending option in the Sort by section
4. Click the OK button
Filtering Data:
Filtering data allows you to focus on specific subsets of information within your dataset.
Step 4: Filtering Data
Applying Filters:
Select your data range.
Click on the "Data" tab and select "Filter."
Dropdown arrows will appear next to each column header.
Click on these arrows to filter data based on specific criteria.
Filtering Criteria:
For text columns, you can filter by specific text values.
For numeric columns, you can filter by numbers greater than, less than, or within a specific range.
For date columns, filter options include dates within a specific period.
2. Prepare a salary statement for the following information and plot a graph
SL NO EMP NAME BASIC PAY
Dept. of CSE

lOMoARcPSD|35013525
1 RAMA 10000
2 MANJU 15000
3 SASH 11000
4 HANU 10000
5 SITA 9000
I. Using MS-Excel calculate the following
a. DA is 3% of basic
b. HRA is 5% of basic
c. Tax is 5% of Gross
II. Find maximum and minimum of Basic pay
III. Count the number of employees where net salary is more than 10,000
IV. Sort the data on the basis of employee name in ascending order
V. Plot the column graph by taking employee name on x-axis and net salary on y-axis
VI. Apply custom filter and display the empnames for whom the Basic pay>=10000
SOLUTION:
1. Type the text i.e. “EMPLOYEES SALARY STATEMENT” in the cell from “A1 to H1”
using the merge and center button
2. Type the SL NO, EMP NAME, BASIC PAY, DA, HRA, GROSS, TAX, NET SALARY
in A2, B2, C2, D2, E2, F2, G2, H2 respectively
3. Put the data for SL NO, EMP NAME, BASIC PAY
4. Calculation for
DA = C3*3%
HRA = C3*5%
GROSS = C3+D3+E3
Dept. of CSE

lOMoARcPSD|35013525
TAX = F3*5%
NET SLARY = F3-G3
5. Calculation for
MAXIMUM = max(C3:C8)
MINIMUM = min(C3:C8)
COUNT = countif(C3:C8,”&>=10000”)
6. For Sort
Select data range -> Click data menu ->Sort -> Sort by -> Emp Name -> Sort on ->
Values -> Order -> A to Z
7. For Graph
Select data range -> Click insert menu -> Chart -> Select column chart -> OK
8. For Filtering
Apply filter selecting Basicpay, then -> click Number filter -> Select greater than and equal to -> enter 10000
 Click OK
Dept. of CSE

lOMoARcPSD|35013525
Dept. of CSE

lOMoARcPSD|35013525
Dept. of CSE

lab-manaul-final-data-analytics-with-excel-bcs358a

Uploaded by

Copyright:

Available Formats

lab-manaul-final-data-analytics-with-excel-bcs358a

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

lab-manaul-final-data-analytics-with-excel-bcs358a

Uploaded by

Copyright:

Available Formats

lOMoARcPSD|35013525

Lab manaul final - Data Analytics with Excel: BCS358A

3rd sem cse (Visvesvaraya Technological University)

Studocu is not sponsored or endorsed by any college or university

Data Analytics with Excel: BCS358A Academic Year: 2023-24

THE OXFORD COLLEGE OF ENGINEERING

BCS358A- Data Analytics with Excel

Downloaded by Anshu Man (anshuman26112005@gmail.com)

Data Analytics with Excel: BCS358A Academic Year: 2023-24

THE OXFORD COLLEGE OF ENGINEERING

Downloaded by Anshu Man (anshuman26112005@gmail.com)

Data Analytics with Excel: BCS358A Academic Year: 2023-24

Data Analytics with Excel Semester 3

Credits 01 Exam Hours 100

● Understand and Identify the principles of data analysis

● Become adept at using Excel functions and techniques for analysis

● Build presentation ready dashboards in Excel

7 Conditional Formatting: formatting, parsing, and highlighting data in spreadsheets during

Downloaded by Anshu Man (anshuman26112005@gmail.com)

Data Analytics with Excel: BCS358A Academic Year: 2023-24

Course outcomes (Course Skill Set):

● Manipulate data lists using Outline and PivotTables.

● Use Consolidation to summarise and report results from multiple worksheets.

Downloaded by Anshu Man (anshuman26112005@gmail.com)

Data Analytics with Excel: BCS358A Academic Year: 2023-24

Assessment Details (both CIE and SEE)

Continuous Internal Evaluation (CIE):

● In a test, test write-up, conduction of experiment, acceptable result, and procedural

Data Analytics with Excel: BCS358A Academic Year: 2023-24

total CIE marks scored by the student.

Semester End Evaluation (SEE):

● SEE marks for the practical course are 50 Marks.

Downloaded by Anshu Man (anshuman26112005@gmail.com)

Data Analytics with Excel: BCS358A Academic Year: 2023-24

● All laboratory experiments are to be included for practical examination.

The minimum duration of SEE is 02 hours

Suggested Learning Resources:

Downloaded by Anshu Man (anshuman26112005@gmail.com)

Data Analytics with Excel: BCS358A Academic Year: 2023-24

What is Microsoft Excel?

How to open MS Excel?

Downloaded by Anshu Man (anshuman26112005@gmail.com)

Data Analytics with Excel: BCS358A Academic Year: 2023-24

Understanding the worksheet (Rows and Columns, Sheets, Workbooks)

Downloaded by Anshu Man (anshuman26112005@gmail.com)

Data Analytics with Excel: BCS358A Academic Year: 2023-24

Benefits of Using MS Excel

Downloaded by Anshu Man (anshuman26112005@gmail.com)

Data Analytics with Excel: BCS358A Academic Year: 2023-24

 Collection and Verification of Business Data

 Pass if marks in each subject >=35,

To find the grade of a student we need to do the following steps

 Step1: Typing Student database in Excel2007

Downloaded by Anshu Man (anshuman26112005@gmail.com)

Data Analytics with Excel: BCS358A Academic Year: 2023-24

 Step2: To find Total Marks of Student

 Step 3: To find Average marks

Downloaded by Anshu Man (anshuman26112005@gmail.com)

Data Analytics with Excel: BCS358A Academic Year: 2023-24

 Step4: To Check Pass or Fail