Basic Excel
Excel → Accounting → Workbook → Worksheet
Excel is a spreadsheet software developed by Microsoft, used for storing, organizing, analyzing, and
visualizing data. It is part of the Microsoft Office Suite.
Key Features of Excel:
✔️ Works with data arranged in rows and columns (grid of cells)
✔️ Can perform mathematical calculations using formulas and functions
✔️ Supports charts and graphs for data visualization
✔️ Allows data analysis tools like PivotTables, conditional formatting, etc.
✔️ Can be used for automating tasks with Macros
Columns:
✔️ Vertical sections in the worksheet
✔️ Labeled with letters (A, B, C, ..., Z, AA, AB, etc.)
✔️ Example: Column A, Column B
---
Rows:
✔️ Horizontal sections in the worksheet
✔️ Labeled with numbers (1, 2, 3, 4, ...)
✔️ Example: Row 1, Row 2
---
Cell:
✔️ The intersection of a row and a column
✔️ Each cell has an address based on its column and row
✔️ Example:
A1 → Column A and Row 1
B2 → Column B and Row 2
---
Simple Diagram:
A B C
1 Cell A1 Cell B1
2 Cell A2 Cell B2
3 Cell A3 Cell B3
A, B, C = Column = 16384 vertical lines
1, 2, 3 = Row = 1048576 horizontal lines
---
To increase the size of column and row:
Column → Select the column name
Place the cursor between two column names
A|B
← Drag and increase/decrease
Row → Select the Row number
Place the cursor between two row numbers
1|2
← Drag and increase/decrease
2) To autofit columns and rows
Column → Place the cursor between two column names
A|B
Double click on the icon ↔
Row → Place the cursor between two row numbers
1|2
Double click on the icon ↔
---
To insert column/cell:
① Home → Insert → Insert cell → Insert sheet column
② Click on column name
Right click
Insert
③ Click on cell
Right click
Shift cells right
Shift cells down
Entire row
Entire column → ok
To insert Row
① Home → Insert → Insert cell → Insert sheet Rows
② Click on row number
Right click
Insert
---
To delete columns/cells
① Click on column name
Right click
Delete
② Home → Delete → Delete Cell → Delete sheet Columns
③ Click on cell
↓
Right click
Shift cells left
Shift cells up
Entire row
Entire column
Ok
---
To delete Row
① Home → Delete → Delete cells → Delete Sheet Rows
② Click on row number → Right click → Delete
---
Signs
① ➕ → Active cursor → (to select the cells)
② ➡ ➕ → Drag and Drop → to drag the same formula
③ ⬌ ⬍ ⬋ ⬉ → Move cursor → to move the selected text
④ ➕ → Fill handle
---
Bring Enter within the cell
(Computer classes) → Alt + Enter
---
To take a new sheet
① Click on “➕”
② Home → Insert → Insert Sheet
③ Shift + F11
To delete a sheet
① Click on sheet name
Right click
Delete
② Home → Delete → Delete Sheet
---
To Rename a sheet
① Click on sheet name
Right click
Rename
Enter
② Home → Format → Rename → Enter
---
To color a sheet
① Click on sheet name
Right click
Tab color
Choose color
② Home → Format → Tab color → Choose color
Formula
Notes:
① Always starts with "="
② No space between the characters
③ The text in the formula is to be written in capital or small, doesn't matter
---
Formula
1) Date formula
=today() ⏎
Yesterday → =today() - 1 ⏎
Tomorrow → =today() + 1 ⏎
More examples →
=today() - 20 ⏎
=today() + 20 ⏎
---
2) Date and Time
=Now() ⏎
---
3) EOMONTH formula (End date of month)
① =eomonth(start date, month number) ⏎
Start date = today's date
Month number = month
For example:
=eomonth(A1, 0) ⏎ (Cell check)
A1 = Click on start date i.e., today's date
0 = Present month
=eomonth(A1, 2) ⏎
A1 = Click on today's date
2 = Month number
---
=eomonth(A1, -2) ⏎
A1 = Click on today's date
-2 = Month number
---
4) Sum (total)
=SUM(range) ⏎ / Alt + = ⏎
Range = selecting the numbers
---
5) Average
=Average(range) ⏎
---
6) Maximum
=Max(range) ⏎
---
7) Minimum
=Min(range) ⏎
8) Addition
= Click on first cell + Click on second cell ⏎
---
9) Subtraction
= Click on first cell - Click on second cell ⏎
---
10) Multiplication
= Click on first cell * Click on second cell ⏎
---
11) Division
= Click on first cell / Click on second cell ⏎
---
Marksheet
Subjects
Name Roll No Total Average Max Min Pass/Fail Grade
English Bengali Math
=Nested
Random Random Random =Sum =Average =Max =Min =If If
Random Random
IF Formula
=IF(logical test, true value, false value) ⏎
Logical test = condition
True value = if the condition matches, what should be the answer
False value = if the condition doesn't match, what should be the answer
Examples
VALUE RESULT
1. Cat white
2. Dog 0
3. Cat white
4. Tiger 0
5. Cat white
6. Lion 0
7. Monkey 0
=IF(A1="Cat", "white", "0") ⏎
A1 = cell reference
Examples with Numbers
Value Result
99 No
100 Yes
101 Yes
299 Yes
85 No
67 No
499 Yes
79 No
45 No
400 Yes
500 Yes
---
IF Formula Examples
=IF(A1 > 99, "Yes", "No") ⏎
=IF(A1 < 100, "No", "Yes") ⏎
=IF(A1 >= 100, "Yes", "No") ⏎
=IF(A1 < 99, "No", "Yes")
---
Pass/Fail formula
=IF(Avg >= 40, "Pass", "Fail") ⏎
---
Nested If formula (Grade formula)
Grade Criteria
90+ = AA
80+ = A+
70+ = A
60+ = B+
50+ = B
40+ = C
Below 40 = Fail
Nested If formula
=IF(Avg > 90, "AA", IF(Avg >= 80, "A+", IF(Avg >= 70, "A", IF(Avg >= 60, "B+", IF(Avg >= 50, "B", IF(Avg >=
40, "C", "Fail")))))) ⏎
---
Conditional Formatting
Home
Select range
Conditional Formatting
Highlight Cell Rules
Data Bars
Color Scales
Icon Sets
Options
Greater than .....
Less than .....
Between .....
---
Equal to .......
Text that contains
Put value
Choose color → custom format → customize color → Ok
---
SALES REPORT
Khosla Electronics
Serial No Item Brand Price Per No. of Total Paid Due
Name Name Unit Unit
Formula Data Data RANDOM RANDOM Price RANDOM TOTAL-
Validation Validation per PAID
unit ×
No. of
unit
---
DATA VALIDATION
Method 1:
Select cells where you want data validation
↓
Data
↓
Data Validation
↓
Allow : List
↓
Source : Type the item names separated by commas
↓
Ok
Method 2 - Type the item names somewhere
Select cells where you want Data Validation
↓
Data
↓
Data Validation
↓
Allow : List
↓
Source : Click on the icon
↓
Select the item names you wrote before
↓
Enter
↓
Ok
---
Method 3 - Name Manager
Type the item names and select them
↓
Formulas
↓
Name Manager
↓
New
↓
Rename
↓
O
---
Select cells where you want Data Validation
↓
Data
↓
Data Validation
↓
Allow : List
↓
Source : "Name"
↓
Ok
---
Serial No Formula
1. Type 1 and 2
↓
Select both 1 and 2
↓
Drag and drop
1) Type
1
↓
Type
2
↓
Select
↓
Column
↓
Step value - 1 and Stop value – as per your need
↓
OK
---
2) = row () ⏎
3) = row () - 2 ⏎
4) = row () - 2 ⏎
5) = IF (A1 = 4, 4, row() - 2) ⏎
Return the above cell numbers.
---
Count Formula
1) = COUNT () → to count numbers except characters
2) = COUNTA () → to count characters or blank-filled cells
3) = COUNTIF () → to count only for one specific section
4) = COUNTIF (range, criteria) ⏎
---
Sumif Formula
Sumif → to count the total of something particular
= Sumif(range, criteria, sum total) ⏎
Range → select the whole table
Criteria → select the criteria or type the criteria within quotes
Sum total → select the range of total
Dashboard
Sheet 1 = Income
Sheet 2 = Expense
Sheet 3 = Study
Sheet 4 = Dashboard
---
1)
Item Name Price per Unit No. of Unit Total Amount (Sum of total) Total Paid Total Due
Total Collection = Sum of paid
Total Due = Sum of due
---
2) Sheet 2 = Expense
Salary =
Rent =
House holding Cost =
Electric Bill =
Water Bill =
Total Expense = Sum of the above
---
3) Sheet 3 = Study
Income = Automatic
Expense = Automatic
Profit = Formula
Profit = income - expense
Loss = if (income < expense, income - expense, 0.0)
---
4) Sheet 4 = Dashboard (PDF sent)
Travel Budget (PDF sent)
We will use sum by formula
---
Mail Merge
Database - Excel
Design Template - Word
Database Example:
Name | Roll | English | Bangla | Math | Total | Average
---
# No merge cell
# Headline must
# No blank cell
---
Design Template Example:
Name: ..............
Roll No.: ..........
Class: .............
Section: ...........
Subjects:
English | Full Marks | 100
Bangla | Full Marks | 100
Total:
Average:
---
Steps of Mail Merge
STEP 1:
Mailings → Select Recipients → Use an existing list
STEP 2:
Browse → Select Database → OK
STEP 3:
Mailings → Insert Merge Field → Select option
STEP 4:
Mailings → Finish and Merge → Edit individual document → All → OK
Mail Merge
13) Remove Duplicates
Select range
↓
Data
↓
Remove Duplicates
↓
OK
---
14) Freeze Panes
Click on the first row/first column which ever you want to freeze
↓
Freeze Panes
↓
Freeze Top Row/Top Column
---
To Unfreeze
Select the row/column
↓
View
↓
Freeze Panes
↓
Unfreeze Panes