Part I: Excel Fundamentals

Introduction to Microsoft Excel

Learning Objectives

• Understand what Microsoft Excel is and its evolution from 1985 to Microsoft 365
• Identify real-world uses of Excel across business, education, science, finance, HR, and healthcare
• Navigate the Excel interface: Ribbon, Quick Access Toolbar, Formula Bar, Name Box, and Status Bar
• Differentiate between a Workbook and a Worksheet
• Understand the structure of rows (1,048,576), columns (A to XFD = 16,384), and cells
• Navigate between worksheets: add, delete, rename, and reorder sheet tabs
• Recognize file formats: .xlsx, .xls, .csv, .xlsm, .xlsb, .pdf
• Perform basic file management: New, Open, Save, Save As, Print, Export
• Use essential keyboard shortcuts for file operations and navigation

What is Microsoft Excel?

Microsoft Excel is a spreadsheet application developed by Microsoft. It allows you to organize, calculate, analyze, and visualize data in a structured grid of rows and columns. Think of it as a giant, intelligent calculator combined with a database and a charting tool — all in one application.

At its core, Excel provides a grid of cells where you can type numbers, text, dates, and formulas. But what makes it powerful is that cells can talk to each other — a formula in one cell can reference data from hundreds of other cells, automatically recalculating whenever data changes.

A Brief History of Excel

Where is Excel Used? — Industry-Wise Applications

The Excel Interface — A Complete Tour

When you open Excel and create a new blank workbook, you'll see several important areas on your screen. Let's explore each one from top to bottom.

1. Title Bar

The very top bar of the window. It shows the file name (e.g., "Book1 - Excel") and the window controls (Minimize, Maximize, Close). When you save your file as "Student_Marks.xlsx", the title bar updates to show "Student_Marks - Excel".

2. Quick Access Toolbar (QAT)

Located above or below the Ribbon (you can customize its position). By default, it contains three buttons: Save, Undo, and Redo. You can add frequently used commands like Print Preview, New, and Open by clicking the small dropdown arrow at the right end.

3. The Ribbon — Command Central

The Ribbon is the wide band of commands organized into tabs. Each tab groups related commands. Here's what each tab contains:

4. Formula Bar

Located below the Ribbon. It shows the contents of the currently selected cell. If a cell contains a formula like =A1+B1, the cell might display "500" but the Formula Bar will show the actual formula. You can also type or edit cell contents directly in the Formula Bar.

5. Name Box

Located to the left of the Formula Bar. It displays the address of the selected cell (e.g., "A1", "C5", "Z100"). You can also type a cell address in the Name Box and press Enter to quickly jump to that cell — extremely useful in large spreadsheets.

6. Column Headers and Row Numbers

Columns are labeled with letters (A, B, C, ... Z, AA, AB, ... XFD) across the top. Rows are labeled with numbers (1, 2, 3, ... 1,048,576) along the left side. The intersection of a column and a row creates a cell.

7. Sheet Tabs

At the bottom of the worksheet area, you'll see tabs labeled "Sheet1", "Sheet2", etc. You can click on these to switch between different worksheets within the same workbook. Right-click a tab to rename, delete, move, copy, or change the tab color.

8. Status Bar

The very bottom bar. It shows useful information like the current mode (Ready, Enter, Edit), and when you select multiple cells with numbers, it automatically shows Average, Count, and Sum on the right side — no formula needed!

Workbook vs. Worksheet

This is one of the most common points of confusion for beginners. Let's clear it up with an analogy:

Rows, Columns & Cells

Rows

Rows run horizontally (left to right) and are identified by numbers. Excel has 1,048,576 rows (from row 1 to row 1,048,576). That's over 1 million rows — enough to store the entire population data of Goa or Mizoram!

Columns

Columns run vertically (top to bottom) and are identified by letters. Excel has 16,384 columns (from column A to column XFD). After Z comes AA, AB, ... AZ, then BA, BB, ... and so on up to XFD.

Cells

A cell is the intersection of a row and a column. It's the basic unit where you enter data. Each cell has a unique cell address (also called a cell reference) that identifies it.

Cell Address (A1 Notation)

The cell address is formed by combining the column letter and the row number. For example:

• A1 — Column A, Row 1 (the very first cell, top-left corner)
• B5 — Column B, Row 5
• D10 — Column D, Row 10
• AA100 — Column AA, Row 100
• XFD1048576 — The very last cell in Excel (bottom-right corner)

Understanding the Grid Structure

Selecting Cells, Rows, and Columns

Navigating Worksheets

Sheet Tabs

At the bottom of the Excel window, you'll see sheet tabs. By default, a new workbook has one sheet named "Sheet1". Here's how to work with sheets:

Adding a New Worksheet

1. Click the + (plus) button next to the last sheet tab, OR
2. Press Shift+F11, OR
3. Right-click any sheet tab → click Insert → select Worksheet → click OK

Renaming a Worksheet

1. Double-click on the sheet tab — the name becomes editable
2. Type the new name (e.g., "January_Sales") and press Enter
3. Alternative: Right-click the tab → click Rename

Deleting a Worksheet

1. Right-click on the sheet tab → click Delete
2. If the sheet contains data, Excel will warn you that the data will be permanently deleted

Moving and Copying Worksheets

• Move: Click and drag the sheet tab to a new position
• Copy: Hold Ctrl while dragging the sheet tab — you'll see a small "+" icon indicating a copy
• Alternative: Right-click → Move or Copy → check "Create a copy" checkbox

Changing Tab Color

Right-click on the sheet tab → Tab Color → choose a color. This is incredibly useful when you have many sheets — color-code them by department, month, or category.

File Formats & File Management

Excel File Formats

File Management Operations

Solved Examples

Example 1: Identifying Cell Addresses

Question: What is the cell address of the cell in column D, row 7?

Answer: D7 — The column letter comes first (D), followed by the row number (7).

Example 2: Calculating Total Cells

Question: How many cells are in the range A1:C5?

Answer: The range spans 3 columns (A, B, C) and 5 rows (1 to 5). Total cells = 3 × 5 = 15 cells.

Example 3: Column After Z

Question: What is the column letter after column Z?

Answer: AA. After Z, columns continue as AA, AB, AC, ... AZ, BA, BB, ... up to XFD (the 16,384th column).

Example 4: Workbook vs Worksheet Identification

Question: A teacher creates a file called "Class10_Results.xlsx" with three tabs: "Maths", "Science", "English". Identify the workbook and worksheets.

Answer: Workbook: Class10_Results.xlsx (the entire file). Worksheets: "Maths", "Science", "English" (the three tabs inside).

Example 5: Choosing the Right File Format

Question: A CA needs to send GST data to a Tally software. Which format should they use?

Answer: .csv (Comma Separated Values). Tally and most accounting software can import CSV files. The .xlsx format contains formatting data that Tally cannot read.

Example 6: Last Cell Address

Question: What is the address of the very last cell in an Excel worksheet?

Answer: XFD1048576 — Column XFD (16,384th column) and Row 1,048,576.

Example 7: Counting Rows and Columns in a Range

Question: A Flipkart analyst selects the range B3:F20. How many rows and columns does this selection contain?

Answer: Columns: B to F = 5 columns. Rows: 3 to 20 = 18 rows. Total cells = 5 × 18 = 90 cells.

Example 8: Renaming a Sheet

Question: You have a sheet named "Sheet1" and you want to rename it to "Jan_Sales". Describe two methods.

Answer: Method 1: Double-click the "Sheet1" tab, type "Jan_Sales", press Enter. Method 2: Right-click "Sheet1" tab → click Rename → type "Jan_Sales" → press Enter.

Example 9: File Size Consideration

Question: An HR manager at Infosys has a file with 500,000 employee records. The .xlsx file is very slow to open. What format should they try?

Answer: .xlsb (Excel Binary Workbook). Binary format is significantly faster for large files because it uses a more compact storage structure. File sizes are typically 50-75% smaller than .xlsx.

Example 10: Old Excel Limitation

Question: A government office still uses Excel 2003. They receive a file with 100,000 rows. Can they open it?

Answer: If the file is in .xlsx format, Excel 2003 cannot open it natively (it needs the compatibility pack). Even with the compatibility pack, Excel 2003 can only display 65,536 rows. The remaining 34,464 rows would be truncated. They should upgrade to at least Excel 2007.

Example 11: Active Cell

Question: When you open a new blank workbook, which cell is selected by default?

Answer: Cell A1 — the top-left cell of the worksheet.

Example 12: Navigation with Name Box

Question: You need to quickly jump to cell Z500 in a large spreadsheet. What's the fastest way?

Answer: Click on the Name Box (which shows the current cell address), type Z500, and press Enter. The cursor will instantly jump to cell Z500.

Example 13: Status Bar Calculation

Question: You select cells A1:A5 containing values 10, 20, 30, 40, 50. What will the Status Bar show?

Answer: The Status Bar will display: Average: 30, Count: 5, Sum: 150 (these are the defaults shown in the bottom-right corner).

Example 14: Saving for the First Time

Question: You press Ctrl+S on a brand new workbook that has never been saved. What happens?

Answer: Excel opens the Save As dialog box, asking you to choose a file name, location, and format. After the first save, subsequent Ctrl+S presses will save directly without showing any dialog.

Example 15: Macro File Format

Question: A Zomato developer creates an Excel file with VBA macros for automated report generation. They save it as .xlsx. What problem will occur?

Answer: All VBA macros will be silently removed from the file! Excel will show a warning, but if the user ignores it and saves as .xlsx, the macros are lost. They must save as .xlsm (Macro-Enabled Workbook) to preserve the macros.

Practice Exercises

Exercise 1

Open Excel and identify all seven Ribbon tabs. Write down the name of each tab and list three commands you can find in each tab.

Exercise 2

Create a new workbook with three worksheets. Rename them to: "Personal_Info", "Academic_Marks", and "Hobbies". Change the tab color of "Personal_Info" to green, "Academic_Marks" to blue, and "Hobbies" to orange.

Exercise 3

Navigate to the following cells and type the given values: A1 = "Name", B1 = "Age", C1 = "City", A2 = "Rahul", B2 = 22, C2 = "Mumbai". Observe the Name Box as you click on different cells.

Exercise 4

Calculate the total number of cells in the range D5:J15. Show your work: Number of columns × Number of rows.

Answer: Columns D to J = 7 columns. Rows 5 to 15 = 11 rows. Total = 7 × 11 = 77 cells.

Exercise 5

Save the workbook from Exercise 2 in three different formats: .xlsx, .csv, and .pdf. Observe what changes when you open the CSV version (hint: formatting is lost).

Exercise 6

Type the numbers 10, 20, 30, 40, 50 in cells A1 to A5. Select the range A1:A5 and check the Status Bar at the bottom. Note down the Sum, Average, and Count displayed.

Exercise 7

Use the Name Box to navigate directly to cell M100. Type "Hello" in that cell. Then navigate to cell AB500 using the Name Box. How is this faster than scrolling?

Exercise 8

Customize your Quick Access Toolbar by adding: Print Preview, New, and Sort A-Z buttons. Describe the steps you followed.

Exercise 9

What is the column letter of the 100th column in Excel? Navigate to it using the Name Box (hint: type the address of row 1 of that column).

Answer: The 100th column is CV. (A=1, Z=26, AA=27, AZ=52, BA=53, BZ=78, CA=79, CV=100)

Exercise 10

Create a workbook representing a small school's data. Add worksheets named "Class_8A", "Class_8B", "Class_9A", "Class_9B", "Class_10A", "Class_10B". Color-code all Class 8 tabs in green, Class 9 tabs in yellow, and Class 10 tabs in red. Save the file as "School_Data.xlsx".

MCQ Quiz — Chapter 1

Hover over each question to reveal the answer.

Interview Questions

Keyboard Shortcuts — Chapter 1

Mini Project — Personal Contact Directory

Chapter Summary

Data Entry and Formatting

Learning Objectives

• Enter different data types: text, numbers, currency, percentages, dates, and times
• Use Auto-Fill to quickly generate series (numbers, months, days, dates)
• Use Flash Fill (Ctrl+E) to intelligently extract and combine data
• Apply Font formatting: name, size, color, bold, italic, underline
• Control Alignment: horizontal, vertical, wrap text, merge & center
• Add Borders and Fill Colors to cells
• Apply Number Formats: General, Number, Currency (₹), Accounting, Percentage, Date, Time, Custom
• Use Conditional Formatting to highlight data patterns automatically
• Use Format Painter to copy formatting efficiently
• Master keyboard shortcuts for formatting operations

Entering Different Types of Data

Excel recognizes different types of data and handles them differently. Understanding this is crucial because Excel's behavior (alignment, calculations, sorting) depends on the data type.

1. Text (Labels)

Text entries include names, addresses, headings, and any non-numeric data. Text is automatically left-aligned in the cell.

2. Numbers

Numeric entries include integers, decimals, and scientific notation. Numbers are automatically right-aligned in the cell.

3. Currency Values

You can enter currency values by typing the rupee symbol (₹) or by applying currency formatting after entering the number.

4. Percentages

Type a number followed by the % sign, or type a decimal and apply percentage format.

5. Dates

Excel stores dates as serial numbers (January 1, 1900 = 1). This allows date arithmetic (e.g., calculating days between two dates). In India, we commonly use the DD/MM/YYYY format.

6. Times

Times are stored as decimal fractions of a day. 12:00 PM = 0.5 (half a day), 6:00 AM = 0.25 (quarter of a day).

Auto-Fill and Flash Fill

Auto-Fill (Drag Handle)

The Auto-Fill feature lets you quickly fill a series of values by dragging the fill handle — the small green square at the bottom-right corner of the selected cell.

Number Series

To create a number series (1, 2, 3, ...), type the first two values to establish the pattern, select both cells, then drag the fill handle down.

Month Series

Type any month name and drag — Excel knows all 12 months!

Day Series

Date Series

Flash Fill (Ctrl+E) — Excel's Magic Wand

Flash Fill was introduced in Excel 2013. It automatically detects patterns in your data and fills the remaining cells. It's incredibly useful for splitting, combining, or reformatting text data.

Example 1: Splitting Full Names

Steps: Type "Rahul" in B1 (the first name from A1). Click on B2 and press Ctrl+E. Excel instantly fills B2 with "Priya", B3 with "Amit", B4 with "Sneha" — it recognized the pattern!

Example 2: Extracting Email Domains

Type "gmail.com" in B1, click B2, press Ctrl+E. Excel fills "yahoo.in", "outlook.com" automatically.

Example 3: Reformatting Phone Numbers

Type "+91-98765-43210" in B1, click B2, press Ctrl+E. Excel reformats all phone numbers with the country code and dashes!

Cell Formatting

Formatting changes how data looks without changing the underlying data. A cell containing "50000" can be displayed as "₹50,000.00" or "50,000" or "5.00E+04" — the stored value remains 50000.

Font Formatting

Alignment

Borders

Borders add lines around cells to create a structured, professional look. Access them from Home → Borders dropdown.

Format Painter — Copy Formatting Instantly

The Format Painter copies formatting from one cell/range and applies it to another. It copies everything: font, color, borders, number format, alignment.

1. Select the cell with the formatting you want to copy
2. Click the Format Painter button (paintbrush icon, Home tab → Clipboard group)
3. Click on (or drag over) the cell(s) where you want to apply the formatting

Number Formats

Number formatting changes how numbers are displayed without changing the underlying value. Access via Home → Number group, or press Ctrl+1 for the Format Cells dialog.

Currency vs Accounting Format

Both display the ₹ symbol, but they differ:

Custom Number Formats

For advanced control, use Custom formats (Ctrl+1 → Custom tab). Here are useful custom formats for Indian context:

Conditional Formatting

Conditional Formatting automatically changes the appearance of cells based on their values. It's like setting up "rules" that say: "If this condition is true, then format the cell this way."

1. Highlight Cell Rules

Navigate to: Home → Conditional Formatting → Highlight Cell Rules

Step-by-Step: Highlighting Pass/Fail Marks

Suppose you have CBSE marks in cells B2:B50 and you want to highlight marks below 33 in red (fail) and above 90 in green (distinction).

1. Select the range B2:B6
2. Go to Home → Conditional Formatting → Highlight Cell Rules → Less Than
3. Enter 33 → choose "Light Red Fill with Dark Red Text" → click OK
4. With B2:B6 still selected, go to Home → Conditional Formatting → Highlight Cell Rules → Greater Than
5. Enter 90 → choose "Green Fill with Dark Green Text" → click OK

Result: Amit (28) and Vikram (31) are highlighted in red; Rahul (92) is highlighted in green!

2. Top/Bottom Rules

3. Data Bars

Data Bars add horizontal bars inside cells, creating an inline bar chart. The bar length is proportional to the cell value. Perfect for quickly comparing values visually.

Navigate to: Home → Conditional Formatting → Data Bars → choose Gradient Fill or Solid Fill.

4. Color Scales

Color Scales apply a gradient of 2 or 3 colors based on value distribution. For example, a Green-Yellow-Red scale shows highest values in green and lowest in red — like a heatmap.

5. Icon Sets

Icon Sets add small icons (arrows, traffic lights, stars, flags) based on cell values. For example, use traffic light icons to show: Green = Good (>80%), Yellow = Average (40-80%), Red = Poor (<40%).

Solved Examples — Chapter 2

Example 1: Creating a Month Series

Task: Fill cells A1:A12 with all 12 month names starting from April (Indian financial year).

Solution: Type "April" in A1. Select A1 → drag the fill handle (small green square) down to A12. Excel automatically fills: April, May, June, July, August, September, October, November, December, January, February, March.

Example 2: Flash Fill — Extract First Names

Task: Column A has full names: "Rajesh Kumar", "Anita Desai", "Suresh Mehta". Extract first names in column B.

Solution: In B1, type "Rajesh". Click B2, press Ctrl+E. Excel fills "Anita" in B2 and "Suresh" in B3.

Example 3: Formatting a Salary Column

Task: Format the values 25000, 45000, 75000, 120000, 32000 as Indian Rupees with commas.

Solution: Select the cells → press Ctrl+1 → Number tab → Category: Currency → Symbol: ₹ English (India) → Decimal places: 0 → OK. Result: ₹25,000, ₹45,000, ₹75,000, ₹1,20,000, ₹32,000.

Example 4: Merge & Center a Title

Task: Create a centered title "FLIPKART SALES REPORT — Q4 2024" spanning columns A to F.

Solution: Type the title in A1. Select A1:F1. Home → Merge & Center. Set font to Calibri 16pt Bold, dark green font color.

Example 5: Conditional Formatting for GST Rates

Task: You have a column with GST rates (5%, 12%, 18%, 28%). Color-code them: 5% = light green, 12% = light blue, 18% = light yellow, 28% = light red.

Solution: Select the GST rate column. Apply 4 separate "Equal To" conditional formatting rules — one for each rate value with the corresponding fill color.

Example 6: Wrap Text for Long Addresses

Task: Cell A1 contains "Flat 204, Sunrise Apartments, Sector 15, Vashi, Navi Mumbai, Maharashtra - 400703". The text is getting cut off.

Solution: Select A1 → Home → Wrap Text (or Ctrl+1 → Alignment → check "Wrap text"). The cell will expand vertically to show the full address on multiple lines.

Example 7: Data Bars for Sales Comparison

Task: Five Zomato delivery partners have daily delivery counts: 25, 42, 18, 55, 33. Add data bars to visually compare performance.

Solution: Enter the values in B2:B6. Select B2:B6 → Home → Conditional Formatting → Data Bars → Gradient Fill (Green). The partner with 55 deliveries will have the longest bar.

Example 8: Number Series with Auto-Fill

Task: Create roll numbers from 101 to 150 in column A.

Solution: Type 101 in A1, 102 in A2. Select A1:A2. Drag the fill handle down to A50. Excel fills: 103, 104, 105, ... 150.

Example 9: Format Painter Usage

Task: Cell A1 has a perfect header format (Calibri 14pt, Bold, Dark Green, Center Aligned, Light Green fill). Apply the same formatting to cells D1, G1, and J1.

Solution: Select A1. Double-click Format Painter (to lock it). Click D1, then G1, then J1. Press Esc. All three cells now have the same formatting.

Example 10: Custom Number Format for Phone Numbers

Task: Format the number 9876543210 to display as +91-98765-43210.

Solution: This cannot be done purely with number formatting (because the grouping is non-standard). Use Flash Fill: In the next column, type the formatted version for the first phone number, then use Ctrl+E for the rest. Alternatively, use a formula: ="+91-"&LEFT(A1,5)&"-"&RIGHT(A1,5)

Example 11: Highlighting Duplicate Roll Numbers

Task: A class register has roll numbers in A2:A50. Quickly find if any duplicates exist.

Solution: Select A2:A50 → Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values → choose "Light Red Fill" → OK. Any duplicate roll numbers will instantly turn red.

Example 12: Creating a Decreasing Number Series

Task: Create a countdown: 100, 95, 90, 85, ... down to 5.

Solution: Type 100 in A1, 95 in A2. Select A1:A2 → drag the fill handle down. Excel recognizes the pattern (decrement by 5) and fills: 90, 85, 80, 75, ... 5.

Example 13: Applying Borders to a Table

Task: Create a professional-looking table for a Reliance Jio customer data sheet with all borders and a thick border around the entire table.

Solution: Select the entire data range → Home → Borders → All Borders. Then, with the range still selected → Borders → Thick Box Border. Result: every cell has thin inner borders, and the entire table has a thick outer border.

Example 14: Color Scale for Temperature Data

Task: Daily temperatures for Delhi (May): 38, 40, 42, 44, 41, 39, 45, 43. Apply a color scale showing cooler temps in blue and hotter in red.

Solution: Enter temperatures in B2:B9. Select the range → Home → Conditional Formatting → Color Scales → "Red - Yellow - Blue Color Scale" (but select the reverse: Blue to Red). Now 45°C appears darkest red and 38°C appears blue.

Example 15: Date Entry and Formatting

Task: Enter Indian Independence Day (15/08/1947) and format it as "15 August 1947".

Solution: Type 15/08/1947 in A1. Press Ctrl+1 → Number → Date → select the format that shows "14 March 2012" style → OK. The cell now displays "15 August 1947".

Example 16: Percentage Entry

Task: Enter the following GST rates correctly: 5%, 12%, 18%, 28%.

Solution: Type 5%, 12%, 18%, 28% directly. Excel stores them as 0.05, 0.12, 0.18, 0.28 internally, which is correct for calculations.

Example 17: Flash Fill — Combining Data

Task: Column A has first names (Rahul, Priya), Column B has cities (Mumbai, Delhi). Create Column C with "Rahul - Mumbai", "Priya - Delhi".

Solution: In C1, type "Rahul - Mumbai". Click C2, press Ctrl+E. Flash Fill creates "Priya - Delhi" automatically.

Example 18: Icon Sets for KPIs

Task: A Flipkart team has performance scores: 95, 72, 45, 88, 30. Apply traffic light icons.

Solution: Select the range → Conditional Formatting → Icon Sets → 3 Traffic Lights. Excel automatically assigns: Green ≥ 67%, Yellow ≥ 33%, Red < 33%.

Example 19: Auto-Fill with Days

Task: Create a weekly schedule header with day names from Monday to Sunday.

Solution: Type "Monday" in B1. Drag the fill handle right to H1. Excel fills: Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.

Example 20: Custom Format for Indian Accounting

Task: Display negative numbers in red with parentheses for a profit/loss column: 50000 should show as ₹50,000 and -25000 should show as (₹25,000) in red.

Solution: Ctrl+1 → Custom → type: "₹"#,##0;[Red]("₹"#,##0) → OK.

Practice Exercises — Chapter 2

Exercise 1

Create a monthly sales table for a chai stall. Headers: Month, Cups Sold, Rate per Cup (₹), Total Revenue. Use Auto-Fill for months (April to March). Enter sample data for cups sold (200-500 range). Format revenue as Indian Currency (₹).

Exercise 2

Enter the following data in Column A: "Aarav Patel - Mumbai", "Diya Shah - Pune", "Kabir Singh - Delhi", "Ananya Gupta - Kolkata". Use Flash Fill to extract names into Column B and cities into Column C.

Exercise 3

Create a class attendance register. Row 1: Student Name headers. Row 2 onward: 10 student names. Columns B-F: Weekday names (Monday-Friday) using Auto-Fill. Enter "P" (Present) or "A" (Absent) for each student. Apply Conditional Formatting: "P" = Green fill, "A" = Red fill.

Exercise 4

Enter these CBSE marks and apply conditional formatting: 92, 45, 78, 31, 85, 28, 67, 93, 55, 40. Rules: Below 33 = Red (Fail), 33-59 = Yellow (Pass), 60-79 = Light Blue (First Division), 80-100 = Green (Distinction).

Exercise 5

Create a custom number format that displays: positive numbers in green with ₹ symbol, negative numbers in red with parentheses, and zero as a dash (-). Test with values: 50000, -25000, 0, 75000, -10000.

Exercise 6

Format the following dates in three different formats: 26/01/1950 (Republic Day). Show as: (a) 26-Jan-1950, (b) January 26, 1950, (c) 26/01/50.

Exercise 7

Use Auto-Fill to create these series: (a) 2, 4, 6, 8, ... 100 (b) 1000, 900, 800, ... 0 (c) Q1 FY24, Q2 FY24, Q3 FY24, Q4 FY24 (d) Week 1, Week 2, ... Week 52.

Exercise 8

Create a Zomato Restaurant Rating table with columns: Restaurant Name, Rating (1-5), Number of Reviews. Apply Data Bars to the Rating column and Icon Sets (stars) to visually show ratings.

Exercise 9

Enter 10 Indian phone numbers (10 digits). Use Flash Fill to format them as: (a) +91-XXXXX-XXXXX (b) (0XX) XXXX-XXXX format.

Exercise 10

Create a beautifully formatted header for "TATA MOTORS — QUARTERLY SALES REPORT". Use Merge & Center across 8 columns, font size 18, dark green background, white text, thick bottom border.

Exercise 11

Enter salaries for 10 employees: 25000, 32000, 45000, 28000, 67000, 52000, 38000, 95000, 42000, 31000. Apply a Color Scale (Red-Yellow-Green) to quickly identify the salary distribution.

Exercise 12

Use the Format Painter to copy formatting from one professionally formatted cell to 5 different non-adjacent cells. Practice both single-click (one-time) and double-click (multi-use) modes.

MCQ Quiz — Chapter 2

Interview Questions — Chapter 2

Keyboard Shortcuts — Chapter 2

Mini Project — Student Report Card

Chapter Summary

Basic Calculations in Excel

Learning Objectives

• Understand that every Excel formula begins with the equals sign (=)
• Perform arithmetic operations: addition, subtraction, multiplication, division, and exponentiation
• Calculate percentages, discounts, and GST (5%, 12%, 18%, 28%)
• Use AutoSum (Alt+=) for SUM, AVERAGE, COUNT, MIN, MAX
• Differentiate between Relative (A1), Absolute ($A$1), and Mixed ($A1, A$1) cell references
• Understand and apply the order of operations (BODMAS/PEMDAS) in Excel
• Identify and troubleshoot common Excel errors: #DIV/0!, #VALUE!, #REF!, #NAME?, #NULL!, #N/A, #NUM!
• Build practical calculations for Indian contexts: GST, salary, marks percentage, EMI

Understanding Formulas

A formula is an instruction that tells Excel to perform a calculation. Every formula in Excel has three key characteristics:

1. Starts with an equals sign (=) — This tells Excel "I'm not typing data; I want you to calculate something"
2. Contains cell references and/or values — These are the inputs for the calculation
3. Uses operators or functions — These define what calculation to perform

Formula vs. Value

How to Enter a Formula

1. Click on the cell where you want the result
2. Type = (equals sign)
3. Type the formula (e.g., =A1+B1) or click on cells to build the reference
4. Press Enter to confirm (or Tab to confirm and move right)
5. Press Esc to cancel without entering the formula

Arithmetic Operations

Real-Life Calculation: Shopkeeper's Daily Revenue

Worked Examples:

Worked Examples:

Worked Examples:

Worked Examples:

Worked Examples:

Percentage Calculations & GST

Basic Percentage Formula

Worked Examples:

Example 1: CBSE Marks Percentage

Example 2: Discount Calculation on Flipkart

GST Calculations

The Goods and Services Tax (GST) in India has four slabs: 5%, 12%, 18%, and 28%. Let's learn to calculate GST, which is essential for every business in India.

Example 3: GST Calculation — Restaurant Bill

GST Slabs in India — Reference Table

AutoSum Functions

AutoSum is Excel's quickest way to apply common calculations. Press Alt+= to insert SUM automatically, or click the AutoSum dropdown (Home → Editing group) to choose other functions.

Worked Examples:

Worked Examples:

Worked Examples:

Worked Examples:

Worked Examples:

Cell References — The Heart of Excel

Cell references are what make Excel truly powerful. Instead of typing fixed numbers, you reference cells — and when the data in those cells changes, all formulas automatically update. There are three types of cell references:

1. Relative Reference (A1)

A relative reference changes when the formula is copied to another cell. It's like saying "the cell 2 rows up and 1 column left" — the reference is relative to the formula's position.

How Relative References Change When Copied

When you copy the formula =A1*B1 from C1 to C2, Excel automatically adjusts it to =A2*B2. Copy to C3, and it becomes =A3*B3. This is the default behavior and works perfectly for repeating calculations down a column.

2. Absolute Reference ($A$1)

An absolute reference stays fixed — it does NOT change when copied. Add a $ before both the column letter and row number. Use this when a formula always needs to reference the same cell (like a tax rate, exchange rate, or commission percentage).

When You NEED Absolute References

In column C, every formula references $E$1 (the GST rate). When copied from C2 to C3 to C4, the $E$1 part stays fixed while the B2 part adjusts to B3, B4, etc.

3. Mixed Reference ($A1 and A$1)

A mixed reference locks either the column or the row, but not both:

• $A1 — Column A is locked (always column A), but the row changes when copied vertically
• A$1 — Row 1 is locked (always row 1), but the column changes when copied horizontally

The Multiplication Table — Best Example of Mixed References

Let's build a multiplication table (1-10) using mixed references. This single formula, when copied to all cells, generates the entire table:

How it works:

• $A2 — The $ before A locks the column (always look at column A for the row multiplier). The row number (2, 3, 4...) adjusts when copied down.
• B$1 — The $ before 1 locks the row (always look at row 1 for the column multiplier). The column letter (B, C, D...) adjusts when copied right.

You only need to type the formula once in B2, then copy it across and down to fill the entire 10×10 table!

Reference Types Summary

Order of Operations — BODMAS/PEMDAS in Excel

Excel follows the standard mathematical order of operations, known as BODMAS (Brackets, Orders, Division, Multiplication, Addition, Subtraction) or PEMDAS (Parentheses, Exponents, Multiplication, Division, Addition, Subtraction).

Why This Matters — A Real Example

Calculate the total bill with 18% GST on a base amount of ₹500:

Excel Error Values

When something goes wrong with a formula, Excel displays an error code instead of a result. Understanding these errors helps you troubleshoot quickly.

Solved Examples — Chapter 3

Example 1: Simple Addition

Task: Add the values in A1 (250), A2 (375), and A3 (425).

Formula: =A1+A2+A3 or =SUM(A1:A3)

Result: 1050

Example 2: Net Salary Calculation

Task: Calculate net salary. Gross = ₹60,000 (B1), PF = ₹7,200 (C1), TDS = ₹5,000 (D1), Professional Tax = ₹200 (E1).

Formula: =B1-C1-D1-E1 → =60000-7200-5000-200

Result: ₹47,600

Example 3: Revenue Calculation

Task: A Reliance Fresh store sells 250 kg of rice at ₹65/kg. Calculate total revenue.

Formula: =250*65 or =A1*B1 (where A1=250, B1=65)

Result: ₹16,250

Example 4: Percentage of Marks

Task: A student scored 423 out of 500. Calculate percentage.

Formula: =423/500*100

Result: 84.6%

Example 5: GST at 18%

Task: Calculate GST and total for a ₹25,000 laptop accessory at 18% GST.

Formulas: GST Amount: =25000*18% → 4500. Total: =25000+4500 → 29500. Or: =25000*1.18 → 29500.

Example 6: Compound Interest

Task: Calculate the maturity amount for an SBI FD of ₹1,00,000 at 7.1% for 5 years (compounded annually).

Formula: =100000*(1+7.1/100)^5 → =100000*(1.071)^5

Result: ₹1,41,060.15 (approximately)

Example 7: Average Monthly Expense

Task: Monthly expenses: ₹15000, ₹22000, ₹18000, ₹25000, ₹19000, ₹21000. Find the average.

Formula: =AVERAGE(A1:A6)

Result: ₹20,000

Example 8: Finding Highest and Lowest Marks

Task: Marks of 10 students: 78, 92, 45, 88, 31, 67, 95, 55, 82, 73. Find highest and lowest.

Formulas: Highest: =MAX(A1:A10) → 95. Lowest: =MIN(A1:A10) → 31.

Example 9: Discount Calculation

Task: An Amazon product has MRP ₹4,999 with a 35% discount. Calculate discount amount and selling price.

Formulas: Discount: =4999*35% → ₹1,749.65. Selling Price: =4999-4999*35% → ₹3,249.35. Or: =4999*(1-35%) → ₹3,249.35.

Example 10: Absolute Reference — Commission Calculation

Task: Commission rate is 8% (in cell E1). Calculate commission for 5 salespeople with sales in B2:B6.

Formula (in C2, copy down): =B2*$E$1. The $E$1 stays fixed as you copy down to C3, C4, C5, C6.

Example 11: Using BODMAS

Task: Calculate the total cost including GST for 5 items at ₹200 each with 18% GST.

Formula: =5*200*(1+18/100) → =5*200*1.18 → ₹1,180

Example 12: Count Students Who Appeared

Task: In a column of marks, some cells are blank (student absent). Count how many students actually appeared.

Formula: =COUNT(B2:B50) — This counts only cells with numbers, skipping blanks.

Example 13: EMI Calculation (Simplified)

Task: Approximate monthly EMI for a ₹5,00,000 car loan at 9% annual interest for 5 years.

Formula: =500000*9%/12/(1-(1+9%/12)^(-5*12))

Result: ₹10,380.72 (approximately)

Example 14: Avoiding #DIV/0! Error

Task: Calculate profit percentage. Profit = A1, Cost = B1. If cost is 0, show "N/A".

Formula: =IF(B1=0,"N/A",A1/B1*100)

Example 15: Mixed Reference — Price Matrix

Task: Create a price matrix where Row 1 has quantities (1, 5, 10, 25, 50) and Column A has unit prices (₹10, ₹25, ₹50, ₹100). Each cell shows total price.

Formula (in B2): =$A2*B$1 — Copy across and down. $A2 locks the price column; B$1 locks the quantity row.

Example 16: Total with Multiple GST Rates

Task: Item 1: ₹500 @ 5% GST. Item 2: ₹1000 @ 12% GST. Item 3: ₹2000 @ 18% GST. Calculate total bill.

Formula: =500*1.05 + 1000*1.12 + 2000*1.18 → 525 + 1120 + 2360 = ₹4,005

Example 17: F4 Key Usage

Task: While typing =A1*B1, make B1 absolute.

Steps: Click on B1 in the formula, press F4 once → $B$1. Press F4 again → B$1. Again → $B1. Again → B1 (back to relative).

Example 18: Difference Between COUNT and COUNTA

Task: A1:A5 contains: 10, "Present", 30, "", 50. What do COUNT and COUNTA return?

Answer: =COUNT(A1:A5) → 3 (counts only numbers: 10, 30, 50). =COUNTA(A1:A5) → 4 (counts all non-empty cells: 10, "Present", 30, 50).

Example 19: SUM with Non-Contiguous Ranges

Task: Sum January (B2), March (B4), and May (B6) sales, skipping February and April.

Formula: =SUM(B2,B4,B6) or =B2+B4+B6

Example 20: Area and Perimeter of a Rectangle

Task: Length = 12m (A1), Width = 8m (B1). Calculate Area and Perimeter.

Formulas: Area: =A1*B1 → 96 sq m. Perimeter: =2*(A1+B1) → 40 m.

Practice Exercises — Chapter 3

Exercise 1: Basic Arithmetic

Enter these values: A1=150, B1=75, C1=200, D1=90, E1=185. Calculate: (a) Sum in F1 (b) Average in G1 (c) Maximum in H1 (d) Minimum in I1 (e) Count in J1.

Answers: Sum=700, Average=140, Max=200, Min=75, Count=5

Exercise 2: Percentage Marks

A Class 10 student scored: Maths 85, Science 72, English 91, Hindi 58, SST 79 (each out of 100). Calculate: (a) Total marks (b) Percentage (c) Is it above 75%?

Answers: Total=385, Percentage=77%, Yes

Exercise 3: GST Invoice

Create a GST invoice for a stationery shop: Notebook (₹50, 12% GST, Qty 10), Pen (₹20, 18% GST, Qty 25), Eraser (₹10, 5% GST, Qty 15), Calculator (₹500, 28% GST, Qty 2). Calculate: Base Amount, GST Amount, and Total for each item. Find Grand Total.

Exercise 4: Salary Calculation

Create a salary sheet for 5 employees with columns: Name, Basic Salary, DA (45% of Basic), HRA (30% of Basic), PF (12% of Basic), Gross Salary (Basic+DA+HRA), Net Salary (Gross-PF). Use cell references and the DA/HRA/PF percentages should be in fixed cells ($E$1, $F$1, $G$1).

Exercise 5: Absolute Reference Practice

A foreign exchange counter converts USD to INR. Exchange rate is ₹83.50 (store in cell E1). Create a conversion table for $10, $25, $50, $100, $200, $500, $1000 using absolute reference for the exchange rate.

Example: =A2*$E$1 where A2 has the dollar amount.

Exercise 6: Multiplication Table

Build a complete 12×12 multiplication table using mixed references. Type numbers 1-12 in row 1 (B1:M1) and column A (A2:A13). Write the formula =$A2*B$1 in B2 and copy it to fill the entire 12×12 grid.

Exercise 7: Error Identification

For each formula, predict the error (if any) and explain: (a) =100/0 (b) =5+"Hello" (c) =SUMM(A1:A5) (d) =SQRT(-16) (e) =A1+B1 (where A1=10, B1="Twenty")

Answers: (a) #DIV/0! (b) #VALUE! (c) #NAME? (d) #NUM! (e) #VALUE!

Exercise 8: BODMAS Challenge

Predict the result without using Excel, then verify: (a) =10+5*3 (b) =(10+5)*3 (c) =2^3+4*2 (d) =100/5/4 (e) =(100-20)/4+5*2

Answers: (a) 25 (b) 45 (c) 16 (d) 5 (e) 30

Exercise 9: Discount Calculator

Create a Flipkart-style discount calculator. Products: Headphones (MRP ₹2999, 40% off), Phone Case (MRP ₹599, 50% off), Charger (MRP ₹1499, 25% off), Screen Guard (MRP ₹399, 60% off). Calculate discount amount, selling price, and GST at 18% on selling price for each. Find grand total.

Exercise 10: LIC Premium Calculator

Monthly premium is ₹5,000. Calculate: (a) Annual premium (b) Total premium for 20 years (c) If bonus rate is 4% per year, total bonus after 20 years (use compound interest formula) (d) Maturity amount (Total premium + Bonus)

Exercise 11: Cricket Statistics

Enter data for 5 batsmen with columns: Name, Matches, Runs, Highest Score, Not Outs. Calculate: (a) Average = Runs / (Matches - Not Outs) (b) Use IF to avoid #DIV/0! when Matches = Not Outs (c) Find highest average using MAX (d) Find lowest runs using MIN

Exercise 12: Temperature Conversion

Convert temperatures of 5 Indian cities from Celsius to Fahrenheit. Formula: F = C × 9/5 + 32. Cities: Delhi (42°C), Mumbai (35°C), Chennai (38°C), Kolkata (36°C), Bangalore (28°C). Also find the Average temperature in both °C and °F.

Verify: Average of converted values should equal the conversion of the average. (This demonstrates that linear operations commute!)

MCQ Quiz — Chapter 3

Interview Questions — Chapter 3

Keyboard Shortcuts — Chapter 3

Mini Project — Monthly Household Expense Calculator

Chapter Summary

Coming Up Next: Part II — Intermediate Excel

You've built a solid foundation! In Part II, we'll unlock the real power of Excel with functions (IF, VLOOKUP, HLOOKUP, INDEX-MATCH, COUNTIF, SUMIF), data management (Sort, Filter, Data Validation, Remove Duplicates), and Charts & Visualization. These are the skills that turn you from a "basic user" into someone who can build professional dashboards and automated reports — the skills companies like Deloitte, EY, and KPMG look for in every new hire.