ITI (Trade-COPA) Day- 19

ITI COPA (Computer Operator & Programming Assistant) – Basic Notes

Topic-Advanced Excel Concepts – Theory

1. Introduction to MS Excel (Revision)

Microsoft Excel is a powerful spreadsheet application used for data entry, calculation, analysis, reporting, and visualization. It is widely used in offices, industries, accounts departments, data analysis, and management work. Excel works in the form of rows and columns, which together form cells.

Basic Structure of Excel

  • Workbook – Excel file

  • Worksheet – Sheet inside workbook

  • Rows – Horizontal (1, 2, 3…)

  • Columns – Vertical (A, B, C…)

  • Cell – Intersection of row and column (e.g., A1)

Basic Excel Operations (Revision)

1. Rows and Columns

  • Rows are numbered (1, 2, 3…)

  • Columns are alphabetically named (A, B, C…)

  • Maximum rows: 10,48,576

  • Maximum columns: 16,384 (XFD)

2. Basic Formatting

Formatting makes data readable and presentable.

Formatting options include:

  • Font style and size

  • Bold, Italic, Underline

  • Cell color and font color

  • Alignment (Left, Center, Right)

  • Border formatting

  • Wrap text and merge cells

3. Insert Menu

The Insert Menu is used to insert:

  • Rows and columns

  • Tables

  • Charts and graphs

  • Pivot tables

  • Pictures and shapes

  • Headers and footers

4. Print Setup

Excel allows data to be printed professionally.

Print options include:

  • Page orientation (Portrait / Landscape)

  • Margins

  • Print area

  • Page breaks

  • Scaling

  • Header and footer

2. Lookup Introduction and Functions

What is Lookup?

Lookup functions are used to search data in a table and return related values from another column or row.

Common Lookup Functions

1. VLOOKUP (Vertical Lookup)

Searches data vertically in a table.

Syntax:

=VLOOKUP(lookup_value, table_array, col_index, range_lookup)

Example:
Find employee salary using employee ID.

2. HLOOKUP (Horizontal Lookup)

Searches data horizontally.

3. LOOKUP

Used for simple lookup operations.

4. XLOOKUP (Advanced)

Modern replacement of VLOOKUP and HLOOKUP.

Advantages of Lookup Functions

  • Saves time

  • Reduces errors

  • Automates searching

  • Useful in large datasets

3. Types of References and Cell Naming

Cell References

A cell reference identifies a cell or range in Excel.

Types of Cell References

1. Relative Reference

Changes when copied.

Example: =A1+B1

2. Absolute Reference

Does not change when copied.

Example: =$A$1+$B$1

3. Mixed Reference

One part fixed, one part changeable.

Examples:

  • $A1

  • A$1

Cell Naming (Named Ranges)

Cell naming allows assigning a name to a cell or range.

Advantages:

  • Easy formula writing

  • Better readability

  • Reduces errors

4. Excel Linkage

What is Excel Linkage?

Excel linkage means connecting data between worksheets or workbooks.

Types of Linkage

1. Sheet-to-Sheet Link

Data linked between sheets in same workbook.

Example:

=Sheet2!A1

2. Workbook-to-Workbook Link

Data linked from another Excel file.

Advantages

  • Automatic updates

  • Data consistency

  • Useful for reports

5. Custom Format and Excel Protection

Custom Format

Custom format allows users to display data in required format without changing actual values.

Examples:

  • Currency format

  • Date format

  • Phone number format

  • Text with numbers

Excel Protection

Excel provides security features to protect data.

Types of Protection

  • Protect sheet

  • Protect workbook

  • Password protection

  • Lock/unlock cells

Uses

  • Prevent unauthorized changes

  • Secure sensitive data

  • Maintain data integrity

6. Excel Tips and Tricks

Important Excel Tips

  • Use shortcuts (Ctrl+C, Ctrl+V, Ctrl+Z)

  • Use Freeze Panes

  • Use Filters for data analysis

  • Use Conditional Formatting

  • Use Pivot Tables

  • Use Data Validation

7. Pivot Table and Pivot Chart

Pivot Table

A Pivot Table is a powerful tool used to summarize, analyze, and report large data quickly.

Features of Pivot Table

  • Data summarization

  • Grouping

  • Filtering

  • Sorting

  • Calculations

Steps to Create Pivot Table

  1. Select data

  2. Insert → Pivot Table

  3. Choose rows, columns, values

  4. Analyze data

Pivot Chart

A Pivot Chart is a graphical representation of pivot table data.

Advantages:

  • Visual analysis

  • Easy comparison

  • Interactive charts

8. Conditional Formatting

What is Conditional Formatting?

Conditional formatting changes the appearance of cells based on conditions.

Examples

  • Highlight highest values

  • Show color scales

  • Data bars

  • Icon sets

Advantages

  • Easy data analysis

  • Identifies trends

  • Highlights errors

9. Advanced Graphs

What are Advanced Graphs?

Advanced graphs represent complex data visually.

Types of Advanced Charts

  • Combination charts

  • 3D charts

  • Waterfall charts

  • Histogram

  • Funnel chart

Uses

  • Professional reporting

  • Business analysis

  • Decision making

10. Power Queries

What is Power Query?

Power Query is an advanced Excel tool used for data import, cleaning, and transformation.

Features of Power Query

  • Import data from multiple sources

  • Clean and transform data

  • Remove duplicates

  • Merge and append tables

  • Automate data preparation

Advantages

  • Saves time

  • Reduces manual work

  • Handles large datasets

  • Improves accuracy

MCQ Questions (50 – CBT Exam)

1. MS Excel is a:

A) Word processor
B) Spreadsheet software
C) Database software
D) Presentation software
Ans: B

2. A cell is formed by intersection of:

A) Row and sheet
B) Column and sheet
C) Row and column
D) Workbook and sheet
Ans: C

3. Rows in Excel are identified by:

A) Letters
B) Numbers
C) Symbols
D) Colors
Ans: B

4. Columns in Excel are identified by:

A) Numbers
B) Letters
C) Symbols
D) Icons
Ans: B

5. Insert menu is used to insert:

A) Data only
B) Formatting only
C) Objects and data
D) Password
Ans: C

6. VLOOKUP is used to:

A) Format cells
B) Search data vertically
C) Sort data
D) Filter data
Ans: B

7. HLOOKUP searches data:

A) Vertically
B) Diagonally
C) Horizontally
D) Randomly
Ans: C

8. Which is an advanced lookup function?

A) SUM
B) XLOOKUP
C) COUNT
D) IF
Ans: B

9. Relative reference changes when:

A) Deleted
B) Copied
C) Protected
D) Locked
Ans: B

10. Absolute reference symbol is:

A) #
B) $
C) &
D) @
Ans: B

11. Example of absolute reference:

A) A1
B) A$1
C) $A$1
D) $A1
Ans: C

12. Cell naming is used to:

A) Delete cells
B) Rename sheets
C) Assign name to cell
D) Protect workbook
Ans: C

13. Excel linkage connects:

A) Computers
B) Cells and sheets
C) Printers
D) Charts only
Ans: B

14. Sheet-to-sheet linking uses:

A) =Sheet1+A1
B) =Sheet2!A1
C) =A1@Sheet
D) =LinkA1
Ans: B

15. Custom format changes:

A) Actual value
B) Display of value
C) Formula
D) Sheet
Ans: B

16. Excel protection is used to:

A) Increase speed
B) Secure data
C) Format cells
D) Create charts
Ans: B

17. Which feature prevents editing?

A) Conditional formatting
B) Sheet protection
C) Sorting
D) Filtering
Ans: B

18. Pivot table is used for:

A) Data entry
B) Data summarization
C) Printing
D) Formatting
Ans: B

19. Pivot table is created from:

A) Chart
B) Data range
C) Formula
D) Cell only
Ans: B

20. Pivot chart represents:

A) Raw data
B) Pivot table data
C) Images
D) Text
Ans: B

21. Conditional formatting applies formatting based on:

A) Color
B) Condition
C) Font
D) Size
Ans: B

22. Data bars show:

A) Text length
B) Value comparison
C) Formatting
D) Borders
Ans: B

23. Which is an advanced chart?

A) Line chart
B) Pie chart
C) Histogram
D) Column chart
Ans: C

24. Advanced graphs are used for:

A) Decoration
B) Analysis
C) Printing
D) Security
Ans: B

25. Power Query is used for:

A) Formatting
B) Data cleaning
C) Printing
D) Protection
Ans: B

26. Power Query can import data from:

A) Excel only
B) Multiple sources
C) Printer
D) Scanner
Ans: B

27. Freeze Panes is used to:

A) Lock workbook
B) Fix rows/columns
C) Delete data
D) Hide sheet
Ans: B

28. Filter option is used to:

A) Delete rows
B) Sort data
C) Display selected data
D) Print data
Ans: C

29. Combination chart combines:

A) Two workbooks
B) Two formulas
C) Two chart types
D) Two sheets
Ans: C

30. Workbook protection protects:

A) Single cell
B) Sheet only
C) Entire file
D) Chart
Ans: C

31. Data validation is used to:

A) Enter correct data
B) Format data
C) Delete data
D) Print data
Ans: A

32. Which function returns average?

A) SUM
B) COUNT
C) AVERAGE
D) MAX
Ans: C

33. Excel tips improve:

A) Hardware
B) Productivity
C) Storage
D) Virus protection
Ans: B

34. Custom number format is applied using:

A) Insert tab
B) Home tab
C) Format cells
D) Review tab
Ans: C

35. Pivot tables are interactive because they allow:

A) Editing data
B) Drag and drop
C) Deleting files
D) Coding
Ans: B

36. Which chart shows trend over time?

A) Pie
B) Line
C) Bar
D) Area
Ans: B

37. Power Query saves time by:

A) Manual entry
B) Automation
C) Formatting
D) Printing
Ans: B

38. Conditional formatting helps to:

A) Hide data
B) Highlight important data
C) Encrypt data
D) Delete data
Ans: B

39. Advanced Excel is useful for:

A) Only students
B) Office work
C) Gaming
D) Hardware repair
Ans: B

40. Pivot table calculations include:

A) Sum
B) Count
C) Average
D) All of the above
Ans: D

41. Power Query removes duplicates using:

A) Delete
B) Remove duplicates option
C) Filter
D) Sort
Ans: B

42. Excel charts are used for:

A) Programming
B) Data visualization
C) Database creation
D) Security
Ans: B

43. Absolute reference is useful for:

A) Copying formulas
B) Fixing values
C) Formatting
D) Printing
Ans: B

44. Mixed reference example is:

A) A1
B) $A$1
C) A$1
D) 1A
Ans: C

45. Which Excel feature summarizes large data?

A) Conditional formatting
B) Pivot table
C) Data validation
D) Custom format
Ans: B

46. Power Query supports:

A) Small data only
B) Large datasets
C) Text only
D) Images
Ans: B

47. Print setup is used to:

A) Save file
B) Format page for printing
C) Insert chart
D) Protect data
Ans: B

48. Custom format does NOT change:

A) Value
B) Display
C) Appearance
D) Layout
Ans: A

49. Pivot charts update when:

A) File closes
B) Pivot table changes
C) Data deletes
D) Workbook protected
Ans: B

50. Advanced Excel skills are important for:

A) ITI COPA trade
B) Office automation
C) Data analysis jobs
D) All of the above
Ans: D


Posted by at

No comments:

Post a Comment

Give your valuable feedback

Subscribe to: Post Comments (Atom)

ITI (Trade - COPA) Day-23

ITI COPA (Computer Operator & Programming Assistant) – Basic Notes TOPIC- PROGRAMMING LANGUAGE – PYTHON 1. Introduction to Programming...