Excel Concepts & Practice Notes
1. Referencing in Excel
● Absolute Referencing ($A$1): Reference stays the same when you copy the
formula.
● Mixed Referencing ($A1 or A$1): Only row or column is fixed.
● Relative Referencing (A1): Reference changes when you copy/move formula.
Tip: Make a habit of using the correct referencing based on your calculation needs.
2. Conditional Formatting
● Practice: Learn how to use conditional formatting to highlight cells based on their
values (e.g., greater than, less than, specific text).
3. Custom Lists
● Practice: Understand how to create custom lists to autofill data (e.g., days,
months, custom labels).
4. Text to Columns
● Functionality: Use this feature (found under Data tab) to split text in one column
into multiple columns based on a delimiter (comma, space, etc.).
5. SUMIF and SUMIFS Formulas
● SUMIF: Adds up values that meet a single condition.
● SUMIFS: Adds up values that meet multiple conditions.
6. Difference Among SUM, SUMIF, SUMIFS
Formula Purpose
SUM Adds all numbers in range
SUMIF Adds numbers with one condition
SUMIFS Adds numbers with multiple conditions
7. AND Formula (and OR, NOT)
● AND: Returns TRUE if all conditions are met.
● OR: Returns TRUE if any condition is met.
● NOT: Reverses logic (TRUE becomes FALSE, vice versa).
Common Use Case: Used inside IF statements for multi-condition logic.
8. Why Use AND/OR/NOT in Formulas
● AND: Check multiple conditions (e.g., if student passes all subjects).
● OR: Check if at least one condition is true (e.g., if discount applies for product A
or B).
● NOT: Exclude a condition.
Additional Excel Functions and Tips
9. Counting Rows and Columns
● Count Rows: Use Ctrl + ↓ to jump to the last row. Use =ROWS(range) formula to
count.
● Count Columns: Use Ctrl + → to jump to last column. Use =COLUMNS(range)
formula to count.
10. Formula Tab
● Usage: Check and insert formulas (Insert Function), search for functions, audit
formulas.
11. Cell Definition
● Cell: Intersection of a row and a column (e.g., A1, C4).
12. Macros
● Explanation: A macro automates repetitive tasks using VBA (Visual Basic for
Applications).
13. XLSB Format
● XLSB: Binary Excel file; stores data and macros efficiently.
14. CSV Format
● CSV: Comma Separated Values, a text file storing tabular data. Used for data
transfer between applications.
15. VBA Function
● VBA: Programming language for automating tasks and customizing Excel with
macros.
16. Ribbon Shortcuts
● Shortcut: Ctrl + F1 toggles ribbon visibility.
● Paste Special: Ctrl + Alt + V opens Paste Special dialog for advanced pasting
options.
17. Function vs Formula
● Function: Predefined operation (e.g., SUM, COUNTIF).
● Formula: User-created combination of functions, operators, and references (e.g.,
=SUM(A1:A10)+B1).
18. PRODUCT Formula
● PRODUCT: Multiplies all numbers given as arguments.
○ Example: =PRODUCT(A1:A5) multiplies values in A1 to A5.
19. COUNTIF Formula
● COUNTIF: Counts cells that meet single condition.
○ Example: =COUNTIF(A1:A10, ">5") counts how many values are greater
than 5 in range.
