12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

Home » CSE-ISE » BCS358A

Data Analytics with Excel BCS358A

 2024-01-08  vtucode

Q1. 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.
https://vtucode.in/data-analytics-with-excel-bcs358a 1/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

Drag & Fill:

https://vtucode.in/data-analytics-with-excel-bcs358a 2/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

1.

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.

https://vtucode.in/data-analytics-with-excel-bcs358a 3/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

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:

https://vtucode.in/data-analytics-with-excel-bcs358a 4/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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

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.

https://vtucode.in/data-analytics-with-excel-bcs358a 5/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

2. Closing Excel:

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

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

1.

https://vtucode.in/data-analytics-with-excel-bcs358a 6/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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

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.

https://vtucode.in/data-analytics-with-excel-bcs358a 7/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

4. Flash Fill:

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

https://vtucode.in/data-analytics-with-excel-bcs358a 8/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

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.

https://vtucode.in/data-analytics-with-excel-bcs358a 9/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

4. AutoFilter:

Select your data range and click on the “Filter” button.

Use the filter dropdowns in each column header to filter data.

Additional Tips:

1. Remove Duplicates:

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

2. Data Tables:

https://vtucode.in/data-analytics-with-excel-bcs358a 10/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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

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.

https://vtucode.in/data-analytics-with-excel-bcs358a 11/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

Q3. 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
specific conditions.

3. Input Messages and Error Alerts:

https://vtucode.in/data-analytics-with-excel-bcs358a 12/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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

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

https://vtucode.in/data-analytics-with-excel-bcs358a 13/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

3. Filtering and Grouping:

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

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.

https://vtucode.in/data-analytics-with-excel-bcs358a 14/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

Additional Tips:

1. Refreshing Data:

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

https://vtucode.in/data-analytics-with-excel-bcs358a 15/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

2. Drilling Down:

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

3. Calculations in Pivot Tables:

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

https://vtucode.in/data-analytics-with-excel-bcs358a 16/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

4. Pivot Table Timeline:

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

Q4. 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:

https://vtucode.in/data-analytics-with-excel-bcs358a 17/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

Apply color scales to visualize data distribution.

Use icon sets to represent data trends or rankings.

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.

https://vtucode.in/data-analytics-with-excel-bcs358a 18/46
12/5/24, 7:20 PM vtucode » 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.

https://vtucode.in/data-analytics-with-excel-bcs358a 19/46
12/5/24, 7:20 PM vtucode » 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.

Charts & Graphs:

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

https://vtucode.in/data-analytics-with-excel-bcs358a 20/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

3. Combination Charts:

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

https://vtucode.in/data-analytics-with-excel-bcs358a 21/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

4. Sparklines:

Use sparklines to create small, in-cell charts that provide a visual

representation of trends.

Additional Tips:

1. Dynamic Charts:

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

https://vtucode.in/data-analytics-with-excel-bcs358a 22/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

2. Chart Animations:

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

time.

3. Error Bars:

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

https://vtucode.in/data-analytics-with-excel-bcs358a 23/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

4. Chart Titles and Labels:

Ensure your charts have descriptive titles and labels for clarity.

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

https://vtucode.in/data-analytics-with-excel-bcs358a 24/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

2. LOWER Function:

Converts text to lowercase.

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

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.

https://vtucode.in/data-analytics-with-excel-bcs358a 25/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

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.

https://vtucode.in/data-analytics-with-excel-bcs358a 26/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

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

https://vtucode.in/data-analytics-with-excel-bcs358a 27/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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

3. MID, LEFT, RIGHT Functions:

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

https://vtucode.in/data-analytics-with-excel-bcs358a 28/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

Q6. 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.
https://vtucode.in/data-analytics-with-excel-bcs358a 29/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

Example (Excel): =TIMEVALUE("12:30 PM")

Usage: Convert text representations of times into a format suitable for
calculations.

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.

https://vtucode.in/data-analytics-with-excel-bcs358a 30/46
12/5/24, 7:20 PM vtucode » 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.

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

https://vtucode.in/data-analytics-with-excel-bcs358a 31/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

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.
https://vtucode.in/data-analytics-with-excel-bcs358a 32/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

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.

https://vtucode.in/data-analytics-with-excel-bcs358a 33/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

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.

https://vtucode.in/data-analytics-with-excel-bcs358a 34/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

9. Dynamic Formatting with Pivot Tables:

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

https://vtucode.in/data-analytics-with-excel-bcs358a 35/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

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.

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

Working with multiple sheets within a workbook is a fundamental aspect of

spreadsheet software like Microsoft Excel or Google Sheets. Here are some key
benefits and functionalities of working with multiple sheets:

https://vtucode.in/data-analytics-with-excel-bcs358a 36/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

1. Organizing Data: You can segment your data into different sheets based on
categories, time periods, or any other relevant criteria. This helps keep your workbook
tidy and makes it easier to locate specific information.

2. Managing Data: With multiple sheets, you can manage large volumes of data more
efficiently. You can use features like sorting, filtering, and grouping within each sheet
to organize and manipulate your data as needed.

3. Performing Complex Calculations: By spreading your calculations across multiple

sheets, you can break down complex problems into smaller, more manageable parts.
This can make it easier to understand and debug your formulas.

https://vtucode.in/data-analytics-with-excel-bcs358a 37/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

4. Cross-Sheet References: You can reference data from one sheet to another,
allowing you to create relationships between different sets of data within your
workbook. This is particularly useful for building summary reports or performing
analysis across multiple datasets.

5. Data Analysis and Visualization: You can use different sheets to store raw data,
intermediate calculations, and final results. This allows you to analyze your data step
by step and create comprehensive reports with charts, graphs, and pivot tables.

https://vtucode.in/data-analytics-with-excel-bcs358a 38/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

6. Collaboration: When working with teams, you can assign different sheets to
different team members or departments. This enables parallel work on different
aspects of a project while keeping all the data within the same workbook.

7. Data Protection: You can protect certain sheets within your workbook by setting
permissions or passwords. This helps prevent unauthorized access or accidental
modification of sensitive data.

https://vtucode.in/data-analytics-with-excel-bcs358a 39/46
12/5/24, 7:20 PM vtucode » Data Analytics with Excel BCS358A

Overall, leveraging multiple sheets within a workbook enhances the flexibility,

efficiency, and organization of your data management and analysis processes.

 Project Management with Git Artificial Intelligence and Machine Learning 

BCS358C 21CS54

10 thoughts on “Data Analytics with Excel BCS358A”

raj says:
2024-01-15 at 3:17 pm

make the above content in pdf form

Reply

vtucode says:
2024-01-17 at 7:26 am

Ok sure…

Reply

https://vtucode.in/data-analytics-with-excel-bcs358a 40/46