Basic Excel Formulas Lecture

Introduction

• Learn basic Excel formulas
  • Sum
  • Subtraction
  • Multiplication
  • Division
  • Percentage
  • Average
  • Count
• Methods to use formulas
  1. Sum function with formula
  2. Sum function without formula
  3. Sum function with shortcut key

Method 1: Sum Function with Formula

• Given Table: Products, Quantity, Sales Amount
• Steps:
  1. Start formula in desired cell
  2. Type =SUM(... and select data manually or using keyboard (Shift + Control + Arrow keys)
  3. Press Enter to calculate total
  4. Use Autofill handle (bottom right corner of the cell) to copy formula to adjacent cells

Method 2: Sum Function without Formula

• Use Home tab -> AutoSum icon
• Select data range and AutoSum calculates total automatically

Method 3: Sum Function with Shortcut Key

• Select range including total cell
• Press Alt + = to apply AutoSum

Subtraction Formula

• Example: Net salary = Gross salary - Taxes
• Steps:
  1. Select cell
  2. Type =Gross - Taxes and Press Enter
  3. Copy formula to adjacent cells using Autofill

Multiplication Formula

• Given Table: Products, Units Sold, Rate, Total Sales Amount
• Steps:
  1. Select cell
  2. Type =Units_Sold * Rate and press Enter
  3. Copy formula to adjacent cells using Autofill

Division Formula

• Calculate Rate = Total Amount / Units Sold
• Steps:
  1. Select cell
  2. Type =Total_Amount / Units_Sold and press Enter
  3. Use Autofill for adjacent cells

Percentage Formula

• Given Data: Student, Total Marks
• Steps to calculate Percentage:
  1. Select cell
  2. Type =Marks_Obtained / Total_Marks and press Enter
  3. Copy formula using Autofill
  4. Go to Home tab -> Number tab -> Click on Percentage icon

Average Formula

• Formula: =AVERAGE(range)
• Example: Calculate average marks
• Steps:
  1. Select cell
  2. Type =AVERAGE and select range
  3. Press Enter
  4. Copy formula using Autofill
• Vertical Application: Can be applied vertically by selecting entire column

Count Function

• Count cells containing numbers in a range
• Example: Counting numbers in given range
  1. Select cell
  2. Type =COUNT(range)
  3. Press Enter
• Notes: Dates are also considered as numbers in Excel