Ch 1: Excel Formulas, Referencing & Data Functions - Microsoft Excel (Formulas & Data Analysis)

Course Overview | Microsoft Excel (Formulas & Data Analysis)

Chapter 1

Excel Formulas, Referencing & Data Functions

Master rows, columns, Name Box, Formula Bar, Absolute Referencing ($), SUM, IF, COUNTIF, and VLOOKUP.

Microsoft Excel: Spreadsheets & Formulas

MS Excel is an electronic spreadsheet application used for storing, organizing, calculating, and analyzing data in tabular rows and columns. File extension: .xlsx.

Grid Dimensions:

Rows: 1,048,576 rows (numbered 1 to 1048576).
Columns: 16,384 columns (labeled A to Z, AA to ZZ, up to XFD).
Cell: Intersection of a column and row (e.g. A1, B10).
Active Cell: The currently selected cell highlighted with a bold border. Its address appears in the Name Box, and content in the Formula Bar.

Three Types of Cell Referencing:

Relative Reference (e.g., =A1+B1): Changes automatically when copied to other cells based on relative position.
Absolute Reference (e.g., =$A$1+$B$1): Fixed row and column locked using the $ sign. Press F4 to toggle.
Mixed Reference (e.g., =$A1 or =A$1): Locks only the row or only the column.

Hands-on Laboratory Procedures & Shortcuts

Follow the steps below to practice directly on your computer.

50+ Essential Formula Demonstrations:

Formula	Syntax	Example	Output
SUM	`=SUM(range)`	`=SUM(A1:A5)`	Calculates total of numbers
AVERAGE	`=AVERAGE(range)`	`=AVERAGE(B1:B10)`	Calculates arithmetic mean
COUNT	`=COUNT(range)`	`=COUNT(A1:A10)`	Counts numeric cells only
COUNTA	`=COUNTA(range)`	`=COUNTA(A1:A10)`	Counts non-empty cells (numbers + text)
COUNTIF	`=COUNTIF(range, criteria)`	`=COUNTIF(C1:C20, ">=50")`	Counts cells meeting condition
IF Condition	`=IF(test, true_val, false_val)`	`=IF(D2>=33, "Pass", "Fail")`	Returns conditional result
VLOOKUP	`=VLOOKUP(lookup, table, col_idx, [exact])`	`=VLOOKUP(101, A2:D50, 2, FALSE)`	Searches vertically in 1st col, returns col value
CONCATENATE	`=TEXTJOIN(" ", TRUE, A1, B1)`	Joins first name and last name	Combined string

Watch the video class above while following along with the practical exercises.

Key Summary & Revision Bullet Points

Quick cheat-sheet to review before online examinations.

• Every formula in Excel MUST begin with an equal sign (=).
• Press F4 while editing a cell reference to cycle between relative, absolute, and mixed references.
• Press Alt + = for AutoSum of selected numbers.
• #DIV/0! error occurs when dividing by zero.
• #N/A error indicates a lookup formula cannot find the match value.

Frequently Asked Viva & Exam Questions

Review these standard questions frequently asked in diploma exams and job interviews.

Q1: What is the primary difference between COUNT and COUNTA?
Ans: COUNT counts only cells containing numeric values. COUNTA counts all cells that are not empty, including text, dates, numbers, and boolean values.

Q2: How does VLOOKUP work and what does the 4th argument FALSE signify?
Ans: VLOOKUP searches for a lookup value down the leftmost column of a table array and retrieves data from a specified column on the same row. Setting the 4th parameter to FALSE (or 0) demands an Exact Match.

Interactive Chapter Quiz

Test your mastery immediately! Click an option to see instant answer feedback.

Q1 Every formula and function in Microsoft Excel must start with which symbol?

A. #

B. @

C. =

D. $

Q2 Which symbol is used to create an Absolute Cell Reference in Excel?

A. &

B. $

C. %

D. *

Q3 What is the keyboard shortcut for AutoSum in Excel?

A. Ctrl + S

B. Alt + =

C. Shift + S

D. Alt + Enter

Q4 How many total rows are available in a single modern Excel worksheet?

A. 65,536

B. 100,000

C. 1,048,576

D. 16,384

Q5 Which function counts only cells that contain numerical values?

A. COUNTA

B. COUNT

C. COUNTIF

D. SUMIF

Take Course Final Exam

Course Syllabus