DATA ANALYSIS AND VISUALIZATION

ANSWER KEY
FIRST SEMESTER (CUFYUGP) DEGREE MODEL EXAMINATION
Multi Disciplinary Course – CSC1FM103: DATA ANALYSIS AND VISUALIZATION THROUGH SPREADSHEETS

Time: 1.5 Hours

Maximum Marks: 50

SECTION A

(Each question carries 2 marks – Ceiling 16 marks)

1. List any five features of Excel.

1. Formulas and Functions – Excel allows performing complex calculations using built-in formulas.
2. Charts and Graphs – Data can be represented visually using column, bar, pie, and line charts.
3. Data Sorting and Filtering – Helps to arrange data in ascending/descending order and view specific
data.
4. Pivot Tables – Summarize large datasets quickly for analysis.
5. Conditional Formatting – Highlights cells automatically based on given conditions.

2. Differentiate between a Worksheet and a Workbook.

Basis Worksheet Workbook

Definition A single spreadsheet page in Excel. A complete Excel file containing one or more
worksheets.
Example Sheet1, Sheet2, etc. File named [Link].
Purpose Used to enter, analyze, and visualize data. Used to store and organize multiple
related worksheets.

3. How can you insert and delete a row or column in Excel?

To Insert:
1. Select the row/column header where you want to insert.
2. Right-click → Choose Insert.
3. Or use Home → Cells → Insert → Insert Sheet Rows/Columns.
To Delete:
1. Select the row/column header.
2. Right-click → Choose Delete.
3. Or use Home → Cells → Delete → Delete Sheet Rows/Columns.
4. What is the use of the SUMIF function in Excel?

Purpose: Adds values that meet a specific condition.

Syntax: =SUMIF(range, criteria, [sum_range])
Example: =SUMIF(A1:A10, ">50", B1:B10) → adds values in B1:B10 where corresponding A1:A10 > 50.

5. Explain the terms Range and Autofill.

Range: A group of continuous cells in Excel. Example: A1:A10 or B2:D5.

Autofill: Automatically fills series or patterns such as numbers, dates, or formulas.
Example: Type “Jan” in a cell → drag the fill handle → automatically fills “Feb”, “Mar”, etc.

6. Write a formula to calculate the average of cells A1 to A10.

=AVERAGE(A1:A10)
→ Returns the mean value of numbers from cell A1 to A10.

7. What is the purpose of the Formula Bar in Excel?

Displays the content or formula of the selected cell.

Allows users to view, enter, or edit data or formulas directly.

8. What is the use of the HLOOKUP function?

Searches for a value in the top row of a table and returns a value from a specified row.
Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Example: =HLOOKUP("Math", A1:E5, 3, FALSE) → Finds “Math” in row 1 and returns value from the 3rd
row of that column.

9. What is Absolute Referencing? Give one example.

Definition: A reference that remains fixed even when copied to another cell.
Example: $A$1 → both column and row are fixed; copying the formula will not change the reference.

10. What is the use of Dashboards in Excel?

Dashboards are interactive visual summaries of data using charts, slicers, and tables.
They help monitor key performance indicators (KPIs) and trends in one view.

SECTION B
(Each question carries 6 marks – Ceiling 24 marks)
11. Explain the steps to import a text file into Excel.

1. Open Excel → Go to Data Tab.

2. Click Get Data → From Text/CSV.
3. Select the text file to import.
4. Choose the delimiter type (Comma, Tab, Space, etc.) in the import wizard.
5. Preview the data and make necessary formatting changes.
6. Click Load to import the file into a worksheet.
7. The text data will now be displayed in proper columns and rows.

12. Write an IF formula to display “PASS” if marks are greater than 30, otherwise “FAIL”, and
explain it.

Formula:
=IF(A1>30,"PASS","FAIL")
Explanation:
The IF function tests a condition.
If A1 > 30, Excel displays PASS.
If A1 ≤ 30, Excel displays FAIL.
Syntax: =IF(logical_test, value_if_true, value_if_false)

Example:
If A1 = 45 → Output = PASS
If A1 = 25 → Output = FAIL

13. What is Data Validation? How can we avoid duplicate data in Excel?

Data Validation:
A tool used to control the type or value of data entered into a cell.
Helps ensure data accuracy and consistency.
Steps to avoid duplicates:

1. Select the range (e.g., A1:A20).

2. Go to Data → Data Validation → Custom.
3. In formula box, type: =COUNTIF($A$1:$A$20,A1)=1
4. Click OK.
→ This will restrict the entry of duplicate values within the selected range.

14. Explain the use of VLOOKUP with an example to find employee designation using
Employee ID.

Definition:
VLOOKUP stands for Vertical Lookup.
It searches for a value in the first column of a table and returns a value in the same row from another
column.

Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Example:
If employee data is in range A2:C10, where
Column A = Employee ID
Column B = Name
Column C = Designation

Formula:
=VLOOKUP(E2, A2:C10, 3, FALSE)

→ Finds the Employee ID entered in E2 and returns the corresponding Designation from column 3.

15. What is a Pivot Table? Explain the steps to create a Pivot Table with an example.

Definition:
A Pivot Table summarizes and analyzes large datasets by organizing data into meaningful categories.

Steps:

1. Select the data range (including headers).

2. Go to Insert → PivotTable.
3. Choose New Worksheet or Existing Worksheet.
4. Drag and drop fields:
Rows Area: Categories (e.g., Product Name).
Values Area: Numeric values (e.g., Total Sales).
5. Choose a summary function (SUM, COUNT, AVERAGE).
6. The table will display summarized data (e.g., total sales per product).

Example:
Summarizing total sales per region using a Pivot Table.

SECTION C
(Answer any one question – 10 Marks)

Q16. Based on the following data, perform the operations:

Name Science Physics Mathematics

Arun 70 69 85
Athira 62 64 29
Sanitha 90 49 88
Monisha 88 86 68

a) Find the MIN and MAX for the first exam (Science).

Minimum: =MIN(B2:B5) → 62

Maximum: =MAX(B2:B5) → 90

b) Sort the names A to Z.

Select all data → Go to Data → Sort → Sort by Name → A to Z

→ The table will be alphabetically arranged by Name.

c) Find the average and sum of all exams.

Sum of Science: =SUM(B2:B5) → 310

Sum of Physics: =SUM(C2:C5) → 268
Sum of Mathematics: =SUM(D2:D5) → 270
Average of Science: =AVERAGE(B2:B5) → 77.5
Average of Physics: =AVERAGE(C2:C5) → 67
Average of Mathematics: =AVERAGE(D2:D5) → 67.5

d) Create a Clustered Column Chart and Hide Legend.

1. Select the data range (A1:D5).

2. Go to Insert → Charts → Clustered Column Chart.
3. Chart displays marks for each subject by student.
4. Click the Legend → Press Delete to hide it.

OR

Q17. Explain the following:

a) Form Controls in Excel

Tools that allow user interaction within a worksheet.

Examples: Buttons, Combo Boxes, Check Boxes, and Scroll Bars.
Used to create dashboards and automate tasks (with or without macros).
Example: A Combo Box to select a student’s name and automatically display marks.

b) Conditional Formatting Rules with Examples

Used to automatically format cells based on their values.

Types of Rules:

1. Highlight Cell Rules: e.g., highlight cells greater than 50 → Home → Conditional Formatting →
Highlight Cell Rules → Greater Than → 50.

2. Top/Bottom Rules: Highlight top 10% of marks.

3. Data Bars, Color Scales, Icon Sets: Represent values visually.

Example:
To highlight all marks below 40 in red:
Select marks → Conditional Formatting → Highlight Cells → Less Than → 40.