Chapter 8: Advanced MS Excel (Functions & Pivot Tables)

 

 

MS EXCEL – ADVANCED

Introduction:

MS Excel is not just for basic calculations. It is widely used for data analysis, reporting, charts and automation.

Advanced Excel skills are highly valuable in jobs and business.

Workbook and Worksheet:

Workbook → Excel file

Worksheet → Individual sheets inside a workbook

Cell Referencing:

Relative Reference

Changes when copied (A1)

Absolute Reference

Does not change ($A$1)

Mixed Reference

Partially fixed ($A1, A$1)

Advanced Excel Functions:

IF Function

Used for logical conditions.

=IF(A1>=35,"Pass","Fail")

VLOOKUP Function

Searches data vertically.

=VLOOKUP(A2,A1:D10,3,FALSE)

COUNT, COUNTIF, SUMIF

Used for counting and conditional calculations.

Data Validation:

Controls user input.

Examples:

Dropdown lists

Number limits

Conditional Formatting:

Formats cells based on conditions.

Examples:

Highlight top scores

Highlight low values

Charts and Graphs:

·       Column Chart

·       Bar Chart

·       Pie Chart

·       Line Chart

Sorting and Filtering:

Sorting arranges data

Filtering shows selected data

Pivot Table:

Summarizes large datasets

Generates reports quickly

Advanced Excel Shortcuts:

Ctrl + Shift + L → Filter

Ctrl + T → Table

Alt + = → AutoSum

Exam Important Facts:

Excel is spreadsheet software

Rows are numbered

Columns are alphabetic

Formulas start with =

Conclusion:

Advanced Excel improves efficiency, accuracy and career opportunities. It is an essential skill in the modern digital world.