DATA SCIENCE USING SPREADSHEET SOFTWARE

Syllabus

[BCA-Science]

OE-151-CA: Data Science using Spreadsheet Software

Assignment 1: To Explore Interface and Basic Features of Excel. Make a Start with Excel from Simple to Complex Spreadsheet. Creating Templates in Excel.

Assignment 2: Using Autocomplete and Formatting Features. Data Entry in Excel with Different Data Types and Formatting. Formatting Cells with Number Formats, Font Formats, Alignment, Borders, etc.

Assignment 3: Printing Workbooks - Setting Up Print Area, Print Titles –Repeat Rows /Columns, Designing the Structure of a Template, Customizing Headers & Footers.

Assignment 4: Filtering and Sorting - Filtering on Text, Numbers & Colours, Sorting Options, Sorting and Filtering Lists.

Assignment 5: Calculations in MS-Excel Using Basic Functions (Sum, Average, Max, Min, Count, etc). Use of Text Functions (Upper, Lower, Proper, Left, Mid, Right , Trim, Len, Exact, Concatenate, Find, Substitute). Use of Arithmetic Functions (SumIf, SumIfs CountIf, CountIfs ,AverageIf, AverageIfs).

Assignment 6: What-If Analysis - Goal Seek, Data Tables, Solver Tool, Scenario Analysis.

Assignment 7: Data Validation- Number, Date & Time Validation, Dynamic Dropdown List Creation Using Data Validation – Dependency List, Custom Validations Based on a Formula for a Cell, Text and List Validation.

Assignment 8: Generating Different Types of Charts. Using SLICERS, Filter Data with Slicers, Various Charts i.e. Bar Charts / Pie Charts / Line Charts, Manage Primary and Secondary Axis.

Assignment 9: Use of Conditional Functions. Applying IF Functions. Conditional Formatting in MS-Excel. Use of OFFSET Function.

Assignment 10: Recording Macros and Buttons. Protecting Excel- Excel Security (File Level Protection Workbook, Worksheet Protection).

Assignment 11: Excel Dashboard, Planning a Dashboard, Adding Dynamic Contents to Dashboard, Adding Tables and Charts to Dashboard.

Assignment 12: Use of Lookup Functions. (Vlookup / HLookup), Creating Smooth User Interface Using Lookup, Reverse Lookup Using Choose Function.

Assignment 13: Creating Simple Pivot Tables, Classic Pivot Table, Basic and Advanced Value Field Setting, Calculated Field & Calculated Items, Grouping Based on Numbers and Dates.

Assignment 14: Arrays Functions - What are the Array Formulas, Use of the Array Formulas? Array with if, Len, and Mid Functions Formulas, Basic Examples of Arrays (Advanced Use of Formulas with Array, Array with Lookup Functions).

[BCA-Commerce]

OE-151-CA: Data Science using Spreadsheet Software

Assignment 1: To Explore Interface and Basic Features of Excel. Make a Start with Excel from Simple to Complex Spreadsheet. Creating Templates in Excel.

Assignment 2: Using Autocomplete and Formatting Features. Data Entry in Excel with Different Data Types and Formatting. Formatting Cells with Number Formats, Font Formats, Alignment, Borders, etc.

Assignment 3: Printing Workbooks - Setting Up Print Area, Print Titles –Repeat Rows /Columns, Designing the Structure of a Template, Customizing Headers & Footers.

Assignment 4: Filtering and Sorting - Filtering on Text, Numbers & Colours, Sorting Options, Sorting and Filtering Lists.

Assignment 5: Calculations in MS-Excel Using Basic Functions (Sum, Average, Max, Min, Count, etc). Use of Text Functions (Upper, Lower, Proper, Left, Mid, Right , Trim, Len, Exact, Concatenate, Find, Substitute). Use of Arithmetic Functions (SumIf, SumIfs CountIf, CountIfs ,AverageIf, AverageIfs).

Assignment 6: What-If Analysis - Goal Seek, Data Tables, Solver Tool, Scenario Analysis.

Assignment 7: Data Validation- Number, Date & Time Validation, Dynamic Dropdown List Creation Using Data Validation – Dependency List, Custom Validations Based on a Formula for a Cell, Text and List Validation.

Assignment 8: Generating Different Types of Charts. Using SLICERS, Filter Data with Slicers, Various Charts i.e. Bar Charts / Pie Charts / Line Charts, Manage Primary and Secondary Axis.

Assignment 9: Use of Conditional Functions. Applying IF Functions. Conditional Formatting in MS-Excel. Use of OFFSET Function.

Assignment 10: Recording Macros and Buttons. Protecting Excel- Excel Security (File Level Protection Workbook, Worksheet Protection).

Assignment 11: Excel Dashboard, Planning a Dashboard, Adding Dynamic Contents to Dashboard, Adding Tables and Charts to Dashboard.

Assignment 12: Use of Lookup Functions. (Vlookup / HLookup), Creating Smooth User Interface Using Lookup, Reverse Lookup Using Choose Function.

Assignment 13: Creating Simple Pivot Tables, Classic Pivot Table, Basic and Advanced Value Field Setting, Calculated Field & Calculated Items, Grouping Based on Numbers and Dates.

Assignment 14: Arrays Functions - What are the Array Formulas, Use of the Array Formulas? Array with if, Len, and Mid Functions Formulas, Basic Examples of Arrays (Advanced Use of Formulas with Array, Array with Lookup Functions).

[B.Sc.-CA]

OE-151-CA: Data Science using Spreadsheet Software

Assignment 1: To Explore Interface and Basic Features of Excel. Make a Start with Excel from Simple to Complex Spreadsheet. Creating Templates in Excel.

Assignment 2: Using Autocomplete and Formatting Features. Data Entry in Excel with Different Data Types and Formatting. Formatting Cells with Number Formats, Font Formats, Alignment, Borders, etc.

Assignment 3: Printing Workbooks - Setting Up Print Area, Print Titles –Repeat Rows /Columns, Designing the Structure of a Template, Customizing Headers & Footers.

Assignment 4: Filtering and Sorting - Filtering on Text, Numbers & Colours, Sorting Options, Sorting and Filtering Lists.

Assignment 5: Calculations in MS-Excel Using Basic Functions (Sum, Average, Max, Min, Count, etc). Use of Text Functions (Upper, Lower, Proper, Left, Mid, Right , Trim, Len, Exact, Concatenate, Find, Substitute). Use of Arithmetic Functions (SumIf, SumIfs CountIf, CountIfs ,AverageIf, AverageIfs).

Assignment 6: What-If Analysis - Goal Seek, Data Tables, Solver Tool, Scenario Analysis.

Assignment 7: Data Validation- Number, Date & Time Validation, Dynamic Dropdown List Creation Using Data Validation – Dependency List, Custom Validations Based on a Formula for a Cell, Text and List Validation.

Assignment 8: Generating Different Types of Charts. Using SLICERS, Filter Data with Slicers, Various Charts i.e. Bar Charts / Pie Charts / Line Charts, Manage Primary and Secondary Axis.

Assignment 9: Use of Conditional Functions. Applying IF Functions. Conditional Formatting in MS-Excel. Use of OFFSET Function.

Assignment 10: Recording Macros and Buttons. Protecting Excel- Excel Security (File Level Protection Workbook, Worksheet Protection).

Assignment 11: Excel Dashboard, Planning a Dashboard, Adding Dynamic Contents to Dashboard, Adding Tables and Charts to Dashboard.

Assignment 12: Use of Lookup Functions. (Vlookup / HLookup), Creating Smooth User Interface Using Lookup, Reverse Lookup Using Choose Function.

Assignment 13: Creating Simple Pivot Tables, Classic Pivot Table, Basic and Advanced Value Field Setting, Calculated Field & Calculated Items, Grouping Based on Numbers and Dates.

Assignment 14: Arrays Functions - What are the Array Formulas, Use of the Array Formulas? Array with if, Len, and Mid Functions Formulas, Basic Examples of Arrays (Advanced Use of Formulas with Array, Array with Lookup Functions).