Data Analytics with Excel BCS358A

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 provide multiple features to perform various operations like calculations,
pivot tables, graph tools, macro programming, etc. It is compatible with multiple OS like
Windows, mac OS, 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 the number representing a row.

MS Excel is a spreadsheet program were one can record data in the form of tablets. It is easy to
analyze date in an excel spreadsheet. The image given below represents how an Excel
Spreadsheet looks like:

Dept, of CSE, SIT, KLB Page 1

Data Analytics with Excel BCS358A

How to open MS Excel?

To open MS Excel on your computer, follow the steps given below.

 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 in 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 column meet, they form a
cell. Cells are used to record data. Each cell is uniquely identified using a cell address. Columns
are usually labeled with letters while rows are usually numbers.

A worksheet 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 compositions 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 spartlines, header and footer option, equation and symbols.

Dept, of CSE, SIT, KLB Page 2

Data Analytics with Excel BCS358A

 Page layout: themes, orientation and page setup options are available under the page
layout options.
 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.
 Date: 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 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 purpose because the data is easy to save, and information
can be added and removed without any discomfort and less hard work.

Given below area 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 calculation 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.

Dept, of CSE, SIT, KLB Page 3

Data Analytics with Excel BCS358A

 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, analyzing becomes easier. Thus information in a spreadsheet is more readable and
understandable.

Applications of MS Excel:
1. Data Entry and storage
2. Performing Calculations
3. Data Analysis and Interpretation
4. Reporting and visualizations
5. Accounting and Budgeting
6. Collection and Verification of Business Data
7. Calendars and Schedules
8. Administrative and Managerial Duties
9. Forecasting
10. Automating Repetitive Tasks

Dept, of CSE, SIT, KLB Page 4

Data Analytics with Excel BCS358A

1. Getting Started with Excel: Creation of spread sheets, Insertion of rows and columns,
Drag & Fill, use of Aggregate functions.

Creating a Spreadsheet:
1. Open Excel: Launch Microsoft Excel on your computer.
2. Blank Workbook: Upon opening Excel, you’ll see a blank workbook. This is where you can
create your spreadsheet.
3. Entering Data: Click on a cell and start typing to enter data.

Inserting Rows and Columns:

1. Inserting Rows: Right-click on the row number where you want to insert a new row. Choose
“Insert” from the context menu.

2. Inserting Columns: Right-click on the column letter where you want to insert a new column.
Choose “Insert” from the context menu.

Dept, of CSE, SIT, KLB Page 5

Data Analytics with Excel BCS358A

Drag & Fill:

1. AutoFill:

Enter a value in a cell. Hover over the bottom-right corner of the cell until you see a small square
(the fill handle). Click and drag to fill adjacent cells with a series or pattern.

Aggregate Functions:

1. SUM Function:

To add a range of cells, use the SUM function.

Example: =SUM(A1:A10) adds up the values in cells A1 through A10.

Dept, of CSE, SIT, KLB Page 6

Data Analytics with Excel BCS358A

2. AVERAGE Function:

To find the average of a range of cells, use the AVERAGE function.

Example: =AVERAGE(B1:B5) calculates the average of cells B1 through B5.

3. COUNT Function:

To count the number of cells with numerical values, use the COUNT function.

Example: =COUNT(C1:C8) counts the number of cells in C1 through C8 that contain numbers.

Dept, of CSE, SIT, KLB Page 7

Data Analytics with Excel BCS358A

4. MAX and MIN Functions:

To find the maximum or minimum value in a range, use the MAX and MIN functions.

Example: =MAX(D1:D6) returns the highest value in cells D1 through D6

Formatting:

1. Cell Formatting:

Highlight cells or ranges and use the formatting options in the toolbar to change font, color, and
other formatting.

Dept, of CSE, SIT, KLB Page 8

Data Analytics with Excel BCS358A

2. Column and Row Width:

Adjust the width or height by placing the cursor on the border between column or row headers,
click and drag.

Saving and Closing:

1. Save Your Work: Click on “File” and then “Save” to save your spreadsheet.

2. Closing Excel:

Click on the “X” button at the top-right corner of the Excel window.

Dept, of CSE, SIT, KLB Page 9

Data Analytics with Excel BCS358A

Viva Questions:

1. What is Excel, and why is it important for data analysis?

2. What is a cell in Excel?

3. What are the common data formats in Excel?

4. Describe the use of the AVERAGE function.

5. What is a formula in Excel?

Dept, of CSE, SIT, KLB Page 10

Data Analytics with Excel BCS358A

2. Working with Data: Importing data, Data Entry & Manipulation, Sorting & Filtering.

Importing Data:

1. Importing External Data:

Go to the “Data” tab on the Excel ribbon. Use options like “Get Data” or “From Text” to import
data from external sources such as text files, CSV, databases, or online sources.

2. Copy-Paste:

Copy data from an external source (e.g., a website, another spreadsheet, or a text file). Paste it
into Excel using “Ctrl + V.”

Dept, of CSE, SIT, KLB Page 11

Data Analytics with Excel BCS358A

Data Entry & Manipulation:

1. Entering Data:

Click on a cell and type your data. Use the Tab key to move to the next cell or Enter key to move
to the cell below.

2. Data Validation:

Use the “Data Validation” feature to control what data can be entered in a cell.

3. Text to Columns:

If data is separated by delimiters, use the “Text to Columns” feature to split it into separate
columns.

Dept, of CSE, SIT, KLB Page 12

Data Analytics with Excel BCS358A

4. Flash Fill:

Excel’s Flash Fill feature can automatically ll in values based on patterns you establish.

Sorting & Filtering:

1. Sorting Data:

Highlight the range of cells you want to sort. Go to the “Data” tab and use the “Sort” button.
Choose the column by which you want to sort the data.

2. Filtering Data:

Highlight the range of cells you want to filter. Go to the “Data” tab and click on “Filter.” Use the
dropdown arrows in the column headers to filter data based on specific criteria.

Dept, of CSE, SIT, KLB Page 13

Data Analytics with Excel BCS358A

3. Advanced Filter:

For more complex filtering, you can use the “Advanced Filter” option. Go to the “Data” tab,
click on “Advanced,” and set your criteria.

4. AutoFilter:

Select your data range and click on the “Filter” button. Use the filter dropdowns in each column
header to filter data.

Dept, of CSE, SIT, KLB Page 14

Data Analytics with Excel BCS358A

Additional Tips:

1. Remove Duplicates:

Use the “Remove Duplicates” feature in the “Data” tab to eliminate duplicate values in a range.

2. Data Tables:

If you have a large dataset, consider converting it into an Excel Table (Ctrl + T). Tables provide
dynamic sorting and filtering options.

Dept, of CSE, SIT, KLB Page 15

Data Analytics with Excel BCS358A

3. Transpose:

Use the “Transpose” feature to switch rows and columns.

4. Conditional Formatting:

Apply conditional formatting to highlight specific cells or ranges based on certain criteria.

Dept, of CSE, SIT, KLB Page 16

Data Analytics with Excel BCS358A

Viva Questions

1. What is conditional formatting?

2. How do you use conditional formatting in Excel?

3. What is sorting and filtering data?

Dept, of CSE, SIT, KLB Page 17

Data Analytics with Excel BCS358A

3. Working with Data: Data Validation, Pivot Tables & Pivot Charts.

Data Validation:

Data validation is the process of ensuring that the data entered into a cell meets specific criteria.

1. Setting Data Validation:

Select the cell or range of cells where you want to apply data validation. Go to the “Data” tab
and click on “Data Validation.” Choose the criteria (e.g., whole number, date, list) and set the
validation rules.

2. Custom Validation:

Create custom validation rules using formulas to restrict data entry based on specic conditions.

Dept, of CSE, SIT, KLB Page 18

Data Analytics with Excel BCS358A

3. Input Messages and Error Alerts:

Provide helpful input messages and error alerts to guide users when entering data.

Pivot Tables:

Pivot tables are powerful tools for summarizing and analyzing large amounts of data.

1. Creating a Pivot Table:

Select the range of cells that contain your data. Go to the “Insert” tab and click on “PivotTable.”
Choose where to place the pivot table (new worksheet or existing worksheet).

Dept, of CSE, SIT, KLB Page 19

Data Analytics with Excel BCS358A

2. Building Pivot Table:

Drag and drop fields into the Rows and Columns areas to arrange data. Drag numeric fields into
the Values area to perform calculations (e.g., sum, average).

3. Filtering and Grouping:

Use the filter and grouping options within the pivot table to focus on specific data.

Dept, of CSE, SIT, KLB Page 20

Data Analytics with Excel BCS358A

Pivot Charts:

Pivot charts are visual representations of data created from a pivot table.

1. Creating a Pivot Chart:

After creating a pivot table, select any cell in the pivot table. Go to the “Insert” tab and click on
“PivotChart.” Choose the chart type you want.

2. Customizing Pivot Charts:

Modify the chart layout, styles, and colors. Use the “Filter” and “Slicer” options to interactively
control the data displayed in the chart.

Dept, of CSE, SIT, KLB Page 21

Data Analytics with Excel BCS358A

Additional Tips:

1. Refreshing Data:

If your data changes, refresh the pivot table to update the results.

2. Drilling Down:

Double-clicking on a cell in a pivot table can allow you to drill down into the underlying data.

Dept, of CSE, SIT, KLB Page 22

Data Analytics with Excel BCS358A

3. Calculations in Pivot Tables:

Add calculated fields or items to perform custom calculations within the pivot table.

4. Pivot Table Timeline:

If your data includes dates, use a timeline in the pivot table to filter data based on date ranges.

Dept, of CSE, SIT, KLB Page 23

Data Analytics with Excel BCS358A

Viva Questions

1. How do you create a simple bar chart in Excel?

2. What is a pivot table?

3. What is a slicer in Excel?

4. How do you use conditional formatting in Excel?

5. What is a Pivot Table used for?

6. What Is Data Validation in Excel?

Dept, of CSE, SIT, KLB Page 24

Data Analytics with Excel BCS358A

4. Data Analysis Process: Conditional Formatting, What-If Analysis, Data Tables, Charts
& Graphs.

Conditional Formatting:

1. Highlighting Cells:

Use conditional formatting to highlight cells based on certain criteria (e.g., values greater than or
less than a specific number).

2. Color Scales and Icon Sets:

Apply color scales to visualize data distribution. Use icon sets to represent data trends or
rankings.

Dept, of CSE, SIT, KLB Page 25

Data Analytics with Excel BCS358A

3. Data Bars:

Represent data values using data bars within cells.

What-If Analysis:

What-If Analysis allows you to explore different scenarios by changing input values and
observing the impact on calculated results.

1. Scenario Manager:

Define different scenarios with specific input values. Use the Scenario Manager to switch
between scenarios and view the results.

Dept, of CSE, SIT, KLB Page 26

Data Analytics with Excel BCS358A

2. Goal Seek:

Set a specific goal for a calculated value. Use Goal Seek to determine the required input value to
achieve the goal.

3. Solver:

Solver is an Excel add-in that allows you to optimize solutions by changing variable values
within certain constraints.

Dept, of CSE, SIT, KLB Page 27

Data Analytics with Excel BCS358A

Data Tables:

Data Tables help you analyze the impact of changing one or two variables on a formula or set of
formulas.

1. One-Variable Data Table:

Analyze how changing one input variable affects the results of a formula. Set up a data table with
different values for the input variable.

2. Two-Variable Data Table:

Extend the analysis to two input variables by creating a two-variable data table.

Dept, of CSE, SIT, KLB Page 28

Data Analytics with Excel BCS358A

Charts & Graphs:

Charts and graphs are powerful tools for visualizing data patterns and trends.

1. Creating Charts: Select the data you want to visualize. Go to the “Insert” tab and choose the
desired chart type (e.g., bar chart, line chart, pie chart).

2. Formatting Charts:

. Customize chart elements, colors, and styles to enhance readability.

Dept, of CSE, SIT, KLB Page 29

Data Analytics with Excel BCS358A

3. Combination Charts:

Combine different chart types within the same chart to represent multiple data series.

4. Spark lines:

Use spark lines to create small, in-cell charts that provide a visual representation of trends.

Dept, of CSE, SIT, KLB Page 30

Data Analytics with Excel BCS358A

Additional Tips:

1. Dynamic Charts:

Make your charts dynamic by using named ranges or tables for the data source.

2. Chart Animations:

Add animations to charts to enhance the presentation of data changes over time.

Dept, of CSE, SIT, KLB Page 31

Data Analytics with Excel BCS358A

3. Error Bars:

Include error bars in charts to show the margin of error or variability in data.

4. Chart Titles and Labels:

Ensure your charts have descriptive titles and labels for clarity.

Dept, of CSE, SIT, KLB Page 32

Data Analytics with Excel BCS358A

Viva Questions

1. What is What-If Analysis In Excel?

2. Types of What-if Analysis In Excel

3. What is a chart in Excel?

4. How do you create a simple bar chart in Excel?

Dept, of CSE, SIT, KLB Page 33

Data Analytics with Excel BCS358A

5. Cleaning Data with Text Functions: use of UPPER and LOWER, TRIM function,
Concatenate.

UPPER and LOWER Functions:

1. UPPER Function:

Converts text to uppercase.

Syntax: =UPPER(text)
Example: =UPPER(A1) converts the text in cell A1 to uppercase.

2. LOWER Function:

Converts text to lowercase. Syntax: =LOWER(text) Example: =LOWER(B1) converts the text in
cell B1 to lowercase.

Dept, of CSE, SIT, KLB Page 34

Data Analytics with Excel BCS358A

TRIM Function:

1. TRIM Function:

Removes extra spaces from text, except for single spaces between words. Syntax: =TRIM(text)
Example: =TRIM(C1) removes extra spaces from the text in cell C1.

CONCATENATE Function:

1. CONCATENATE Function: Combines multiple text strings into one. Syntax:

=CONCATENATE(text1, [text2], ...) Example: =CONCATENATE(A1, " ", B1) combines the
text in cells A1 and B1 with a space in between.

Dept, of CSE, SIT, KLB Page 35

Data Analytics with Excel BCS358A

Combining Text Functions:

1. Example – Creating Full Names:

Assuming you have first names in column A and last names in column B. In cell C1, you can use
=CONCATENATE(UPPER(A1), " ", UPPER(B1)) to create a full name in uppercase with a
space in between.

2. Example – Cleaning Data:

If you have text data in column D with extra spaces and mixed cases, you can clean it using
=TRIM(UPPER(D1)) in a new column.

Dept, of CSE, SIT, KLB Page 36

Data Analytics with Excel BCS358A

Additional Tips:

1. & Operator for Concatenation:

Instead of CONCATENATE, you can use the & operator. Example: =A1 & " " & B1 achieves
the same result as =CONCATENATE(A1, " ", B1).

2. TEXT Function:

The TEXT function allows you to format a value as text with a specified format. Example:
=TEXT(DateCell, "yyyy-mm-dd") formats a date as “yyyy-mm-dd”.

Dept, of CSE, SIT, KLB Page 37

Data Analytics with Excel BCS358A

3. MID, LEFT, RIGHT Functions:

Use MID, LEFT, and RIGHT functions to extract specific portions of text from a cell.

4. SEARCH and REPLACE Functions:

The SEARCH function helps find the position of a substring within a text. The REPLACE
function allows you to replace a specific part of the text.

Dept, of CSE, SIT, KLB Page 38

Data Analytics with Excel BCS358A

Viva Questions

1. What is the difference between CONCATENATE and CONCAT?

2. What is the use of the TRIM function in Excel?

3. Explain the use of the REPLACE function.

4. What is the purpose of the FIND and SEARCH functions?

Dept, of CSE, SIT, KLB Page 39

Data Analytics with Excel BCS358A

6. Cleaning Data Containing Date and Time Values: use of DATEVALUE function,
DATEADD and DATEDIF, TIMEVALUE functions.

1. DATEVALUE Function:

Purpose: Converts a date string to a serial number that represents the date. Example (Excel):
=DATEVALUE("2024-01-05") Usage: Convert text representations of dates into a format that
can be used for calculations.

2. TIMEVALUE Function:

Purpose: Converts a time string to a serial number that represents the time. Example (Excel):
=TIMEVALUE("12:30 PM") Usage: Convert text representations of times into a format suitable
for calculations.

Dept, of CSE, SIT, KLB Page 40

Data Analytics with Excel BCS358A

3. DATEADD Function:

Purpose: Adds a specified time interval to a date. Example (SQL): DATEADD(day, 7, '2024-01-
05') Usage: Useful for adding or subtracting days, months, or years from a given date.

4. DATEDIF Function:

Purpose: Calculates the difference between two dates in years, months, or days. Example
(Excel): =DATEDIF(A1, B1, "d") Usage: Determine the duration between two dates, useful for
age calculation or tracking time intervals.

Dept, of CSE, SIT, KLB Page 41

Data Analytics with Excel BCS358A

Example Scenario (Using Excel Functions):

Let’s say you have a dataset with a column containing date and time values in text format. You
want to clean this data and perform some calculations.

Assuming your date and time values are in column A and the format is “yyyy-mm-dd
hh:mm:ss”:

1. Separate Date and Time: In column B, use the formula =DATEVALUE(A1) to extract the
date. In column C, use the formula =TIMEVALUE(A1) to extract the time.

2. Add Days to Date: In column D, use the formula =DATEADD(B1, 7) to add 7 days to the
date.

3. Calculate Time Difference: In column E, use the formula =DATEDIF(C1, C2, "h") to
calculate the time difference in hours between two time values.

Viva Question

1. Describe the purpose of the DATEDIF function.

2. What is a datevalue function?

3. . How would you filter data in Excel to show only rows with a certain value?

4. How do you calculate the total sales from a list of individual sales?

Dept, of CSE, SIT, KLB Page 42

Data Analytics with Excel BCS358A

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

during data analysis.

1. Highlighting Cells Based on Values:

Example (Excel): Select the range of cells you want to format. Go to the “Home” tab, click on
“Conditional Formatting,” and choose “Highlight Cells Rules.” Set rules such as “Greater Than,”
“Less Than,” or “Equal To” and define the criteria.

2. Color Scales for Gradient Highlighting:

Example (Google Sheets): Select the range. Click on “Format” in the menu, choose
“Conditional formatting.” Select “Color scale” and choose the appropriate color scale.

Dept, of CSE, SIT, KLB Page 43

Data Analytics with Excel BCS358A

3. Icon Sets for Visual Indicators:

Example (Excel): Apply icon sets to cells based on conditions (e.g., arrows indicating value
trends). Go to “Conditional Formatting,” choose “Icon Sets,” and select the set you want.

4. Data Bars and Color Gradients:

Example (Excel): Apply data bars to visualize the magnitude of values in a cell. Go to
“Conditional Formatting,” choose “Data Bars,” and pick the desired format.

Dept, of CSE, SIT, KLB Page 44

Data Analytics with Excel BCS358A

5. Text and Date Formatting:

Example (Google Sheets): Change text or date color based on conditions. Use “Custom formula
is” option in conditional formatting to apply rules.

6. Top/Bottom Rules:

Example (Excel): Highlight the top or bottom percentage/values in a range. Go to “Conditional

Formatting,” choose “Top/Bottom Rules,” and set the criteria.

Dept, of CSE, SIT, KLB Page 45

Data Analytics with Excel BCS358A

7. Formula-Based Formatting:

Example (Excel): Create custom rules using formulas. Use “Use a formula to determine which
cells to format” option in conditional formatting.

8. Data Validation and Input Formatting:

Example (Google Sheets): Set up data validation rules to control data input. Use formatting
options to visually guide users on acceptable data.

Dept, of CSE, SIT, KLB Page 46

Data Analytics with Excel BCS358A

9. Dynamic Formatting with Pivot Tables:

Example (Excel): Dynamically format cells based on changes in data using PivotTable
conditional formatting.

10. Conditional Formatting in Programming (VBA or Google Apps Script):

Example (Excel VBA): Use VBA to apply conditional formatting based on complex rules or
dynamic conditions.

Dept, of CSE, SIT, KLB Page 47

Data Analytics with Excel BCS358A

Viva Questions

1. How do you add numbers in Excel?

2. What is a formula in Excel?

3. What is a chart in Excel?

4. How do you create a simple bar chart in Excel?

Dept, of CSE, SIT, KLB Page 48

Data Analytics with Excel BCS358A

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.

1. Sheet Navigation:

Use sheet tabs at the bottom of the workbook to quickly navigate between sheets. Rename sheets
to provide clear and meaningful labels.

2. Sheet Grouping:

Group related sheets together. Right-click on a sheet tab, select “Group,” and then choose the
sheets you want to include in the group. Use grouping to collapse or expand multiple sheets at
once for better organization.

Dept, of CSE, SIT, KLB Page 49

Data Analytics with Excel BCS358A

3. Sheet Color Coding:

Assign different colors to sheet tabs to visually differentiate between types of sheets (e.g., data
sheets, summary sheets). Right-click on a sheet tab, select “Tab Color,” and choose a color.

4. Linking and Referencing:

Use formulas to link data between sheets. For example, use cell references like ='Sheet2'!A1 to
refer to a cell in another sheet.

Create summary sheets that consolidate data from multiple sheets.

Dept, of CSE, SIT, KLB Page 50

Data Analytics with Excel BCS358A

5. 3D Formulas:

Utilize 3D formulas to perform calculations across multiple sheets. For instance,

=SUM(Sheet1:Sheet3!A1) sums the values in cell A1 across sheets 1 to 3.

6. Sheet Protection:

Protect sheets with sensitive data by setting passwords or restricting editing permissions. Right-
click on a sheet tab, choose “Protect Sheet,” and set the desired options.

Dept, of CSE, SIT, KLB Page 51

Data Analytics with Excel BCS358A

7. Consistent Formatting:

Maintain consistent formatting across sheets to enhance the overall visual appeal of the
workbook. Consider using templates for a consistent look and feel.

8. Sheet Visibility:

Hide sheets that are not immediately relevant or are interim calculation sheets. Right-click on a
sheet tab, choose “Hide” or “Unhide” to manage sheet visibility.

Dept, of CSE, SIT, KLB Page 52

Data Analytics with Excel BCS358A

9. Table and PivotTable References:

Use structured tables for dynamic data ranges that automatically adjust as your data grows.
Reference tables and PivotTables across sheets to create dynamic reports.

10. Sheet Protection:

Protect sheets with sensitive data by setting passwords or restricting editing permissions. Right-
click on a sheet tab, choose “Protect Sheet,” and set the desired options.

Dept, of CSE, SIT, KLB Page 53

Data Analytics with Excel BCS358A

11. Data Validation Across Sheets:

Apply data validation rules consistently across multiple sheets to maintain data integrity. Use
named ranges for easier management of data validation rules.

12. Consolidation and Linking:

Use consolidation functions to combine data from multiple sheets into a summary sheet. Linking
between sheets can also help maintain data integrity and avoid redundancy.

Dept, of CSE, SIT, KLB Page 54

Data Analytics with Excel BCS358A

13. Hyperlinks:

Use hyperlinks to navigate between sheets or link to external resources. Right-click on a cell,
choose “Hyperlink,” and set the link destination.

14. Sheet Templates:

Create sheet templates with predefined formats, formulas, and settings for consistency in similar
types of sheets

Dept, of CSE, SIT, KLB Page 55

Data Analytics with Excel BCS358A

Viva Question:

1. How do you apply a filter to a column in Excel?

2. How do you save an Excel file as a PDF?

3. How do you insert a new row in Excel?

4. What is the TEXT function used for in Excel?

Dept, of CSE, SIT, KLB Page 56

Data Analytics with Excel BCS358A

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. Analyze the data using appropriate chart and report the data.

Given: DA=30% of BP, HRA=20% of BP, TA=17.5% of BP, IT=15% of BP, PF=12.5% of BP

Steps:-

1. Create an Excel Worksheet for an employee payroll system.

2. Enter the details of Employee as given and calculate the DA, TA, HRA, IT, PF as a
percentage on the basis of Basic Pay.

3. Calculate the Net Pay by using the formulae

Gross Pay= DA+TA+HRA+BP

Deductions=IT+PF

Net Pay=Gross Pay-Deductions

Dept, of CSE, SIT, KLB Page 57

Data Analytics with Excel BCS358A

Viva Questions:

1. What does the SUM function do?

2. What is the AVERAGE function used for?

3. How do you find the highest number in a list using Excel?

4. What does the COUNT function do?

Dept, of CSE, SIT, KLB Page 58

Data Analytics with Excel BCS358A

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.

Steps:-

1. Create an Excel Worksheet for an inventory management system.

2. Enter the details of Inventory as given Product Code, Product name, Product type,
MRP and calculate the cost after cost after % of discount.

3. Calculate the Net Pay by using the formulae:

Cost after % of discount= (1- discount %)* MRP

Dept, of CSE, SIT, KLB Page 59

Data Analytics with Excel BCS358A

Viva Question:

1. Explain the use of the CONCATENATE function with an example.

2. How do you add numbers in Excel?

3. How do you copy a formula across multiple cells?

Dept, of CSE, SIT, KLB Page 60

Data Analytics with Excel BCS358A

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.

Step 1: Create a table

Step 2: Use the below formula to segregate different Categories

“=SUMIFS([Amount],[Category of product],“Apparel”)”

Step 3: Create a Pivot table for Comparative Analysis

 Plotted the pivot table for Category of product and Amount field
 In the pivot table list drag the Category of product to the “Rows” area and Amount to the
“Values” area to see the total amount per Category.

Dept, of CSE, SIT, KLB Page 61

Data Analytics with Excel BCS358A

Step 4: Create a Different types of Charts

 Select a cell within your pivot table

 Go to insert tab and choose the type of a chart you want(eg: bar chart, pie chart etc)
 Customize the chart with titles, labels etc

1. Bar Chart

Dept, of CSE, SIT, KLB Page 62

Data Analytics with Excel BCS358A

2. Pie Chart

3. Column Chart

Dept, of CSE, SIT, KLB Page 63

Data Analytics with Excel BCS358A

Viva Question

1. How to create a pie chart in Excel?

2. How many types of pie charts are there in Excel?

3. What is the main purpose of a bar chart?

4. When would you use a column chart?

Dept, of CSE, SIT, KLB Page 64

Data Analytics with Excel BCS358A

12. Generation of report & presentation using Auto filter &macro.

Step 1: Prepare a table with Data

Step 2: Apply AutoFilter

 Select your data range.

 Go to the 'Data' tab in Excel.
 Click on 'Filter' or 'Sort & Filter' to enable Autofilter options.
 Use Autofilter to filter the data as needed, e.g., filter for a specific category.

Step 3: Record a Macro

 Go to the "View" tab in Excel.

Dept, of CSE, SIT, KLB Page 65

Data Analytics with Excel BCS358A

 Click on "Macros" and choose "Record Macro."

 In the "Record Macro" dialog box, provide a name for your macro, e.g., "Generate
Report."
 In the "Store macro in" dropdown, select "This Workbook" to save the macro
within your workbook.
 Click "OK" to start recording.

Step 4: Create a Report Manually

 While the macro is recording, manually copy the filtered data by selecting it and
pressingCtrl + C.
 Open a new sheet.
 Manually paste the copied data using Ctrl + V.
 Format the data, add titles, and structure it as you like to create a report.
 Manually create charts to visualize the data.

Step 5: Stop Recording a Macro

 Go to the "View" tab in Excel.

Dept, of CSE, SIT, KLB Page 66

Data Analytics with Excel BCS358A

 Click on "Macros" and choose "Stop Recording."

For Generating a presentation

Step 6: Create a presentation manually

 Open Microsoft PowerPoint

 Create a new presentation.
 Copy the content from your report (the filtered data, text, and charts).
 Create slides for your presentation and paste the content onto the slides.
 Format the slides, add titles, and organize the content in a presentation format manually.
 Save the presentation

Dept, of CSE, SIT, KLB Page 67

Data Analytics with Excel BCS358A

Viva Questions

1. What is the AutoFilter feature in Excel?

2. What are the three types of filters in Excel?

3. What is the shortcut for AutoFilter in Excel?

Dept, of CSE, SIT, KLB Page 68