COUNT FUNCTIONS IN EXCEL
Excel provides different COUNT functions depending on what type of data
needs to be counted. Using the correct function ensures accurate results during
analysis.
1. COUNT
Purpose: Counts only numeric values. Text, blanks, and symbols are ignored.
Syntax:
=COUNT(range)
Example:
Sales
(₹)
1200
1500
Apple
1800
(blank)
2000
Formula:
=COUNT(A1:A6)
Result: 4
Explanation: Only numeric values (1200, 1500, 1800, 2000) are counted. Text
and blank cells are ignored.
Used when: You want to know how many numbers are present in a dataset.
2. COUNTA
Purpose: Counts all non-empty cells, including numbers, text, dates, and
symbols.
Syntax:
=COUNTA(range)
Using the same data
Formula:
=COUNTA(A1:A6)
Result: 5
Explanation: All filled cells are counted except the blank cell.
Used when: You want to check how many entries are filled in total.
3. COUNTBLANK
Purpose: Counts only empty cells in a given range.
Syntax:
=COUNTBLANK(range)
Formula:
=COUNTBLANK(A1:A6)
Result: 1
Explanation: Only one cell is completely blank.
Used when: Identifying missing or incomplete data.
4. COUNTIF (Conditional Counting)
Purpose: Counts cells that satisfy a specific condition.
Syntax:
=COUNTIF(range, criteria)
Example 1: Sales greater than ₹1500
Sales
(₹)
1200
1500
1800
2000
1400
Formula:
=COUNTIF(A1:A5,">1500")
Result: 2
Counts only 1800 and 2000.
Example 2: Counting a specific text
Produ
ct
Pen
Pencil
Pen
Eraser
Pen
Formula:
=COUNTIF(B1:B5,"Pen")
Result: 3
Quick Summary
COUNT → counts numbers only
COUNTA → counts all non-empty cells
COUNTBLANK → counts empty cells
COUNTIF → counts values based on a condition
CONDITIONAL FORMATTING IN EXCEL
Conditional Formatting is used to automatically format cells based on
conditions. It helps in quick analysis by visually highlighting important values,
trends, and exceptions.
Where to find Conditional Formatting?
Home tab → Conditional Formatting
Inside this menu, the main options are clearly grouped.
1. Highlight Cells Rules
Path:
Home → Conditional Formatting → Highlight Cells Rules
Options available:
Greater Than, Less Than, Equal To, Between, Text that Contains, Duplicate
Values
Example Output:
If sales greater than ₹5,000 are highlighted, those cells appear with a colored
fill (e.g., light red background with dark text).
Used when: You want to immediately identify values meeting a condition.
2. Data Bars
Path:
Home → Conditional Formatting → Data Bars
Options available:
Gradient Fill, Solid Fill
Output:
Each cell displays a horizontal bar.
Larger values → longer bars
Smaller values → shorter bars
The actual number remains visible along with the bar.
Used when: Comparing values visually without using charts.
3. Color Scales
Path:
Home → Conditional Formatting → Color Scales
Options available:
Two-color scale, Three-color scale
Output:
Cells change colour based on value range.
Lowest values → light/red shade
Medium values → yellow shade
Highest values → green/dark shade
Used when: Showing performance levels or data distribution.
4. Icon Sets
Path:
Home → Conditional Formatting → Icon Sets
Options available:
Directional (arrows), Shapes, Indicators, Ratings
Output Example:
Green arrow ↑ → high values
Yellow arrow → medium values
Red arrow ↓ → low values
Icons appear inside the cells along with the values.
Used when: Quick performance comparison or ranking.
Managing Rules
Home → Conditional Formatting → Manage Rules
Used to edit, delete, or reorder existing rules.
Important Note
Conditional Formatting only changes the appearance of data. The actual values
in the cells remain unchanged.
SINGLE DATASET FOR ALL 4 CONDITIONAL FORMATTING OPTIONS
Regi Sales
Product
on (₹)
Pen North 2800
Pencil South 4200
Marker East 5100
Noteboo
West 6700
k
Eraser North 3500
Scale South 5900
Folder East 7200
Regi Sales
Product
on (₹)
Diary West 4600
Stapler North 8200
Highligh
South 3000
ter
How to Apply All 4 on the Same Dataset
Highlight Cells Rules:
Apply on Sales (₹) → Greater Than → 5000
(Highlights 5100, 5900, 6700, 7200, 8200)
Data Bars:
Apply on Sales (₹) → Shows relative magnitude
Color Scales:
Apply on Sales (₹) → Red (low) to Green (high)
Icon Sets:
Apply on Sales (₹) → Arrows show performance level
PIVOT TABLES AND PIVOT CHARTS – EXCEL NOTES
Pivot Tables and Pivot Charts are tools used to summarise, analyse, and present
large datasets quickly without changing the original data.
PIVOT TABLES
Where can you find Pivot Table in Excel?
Insert tab → PivotTable
How Pivot Table Works
Excel groups data based on selected fields
Performs automatic calculations like Sum, Count, and Average
Allows rearranging data by dragging fields without rewriting formulas
Original data remains unchanged
Basic Requirements
Data must be in tabular form
Each column must have a heading
No blank rows or columns
Example Dataset
Regio Produ Sales
n ct (₹)
North Pen 1200
North Pencil 800
South Pen 1500
South Pencil 900
East Pen 1100
West Pen 1600
Creating a Pivot Table (Steps)
1. Select the entire dataset
2. Go to Insert tab
3. Click PivotTable
4. Choose From Table/Range
5. Select New Worksheet
6. Click OK
Pivot Table Layout Areas
Rows: Categories displayed vertically
Columns: Categories displayed horizontally
Values: Performs calculations (Sum, Count, Average)
Filters: Filters the entire Pivot Table
Example: Total Sales by Region
Field Placement:
Rows → Region
Values → Sum of Sales
Final Output (Explanation):
The Pivot Table shows total sales for each region by adding all sales values
belonging to that region.
Example:
North → 2000
South → 2400
East → 1100
West → 1600
Uses of Pivot Tables
Region-wise or product-wise analysis
Summarising large datasets
Quick comparison and reporting
PIVOT CHARTS
Where can you find Pivot Chart in Excel?
Insert tab → PivotChart
OR
Click inside Pivot Table → PivotTable Analyze → PivotChart
How Pivot Chart Works
Pivot Chart is linked directly to a Pivot Table
Any change in Pivot Table updates the chart automatically
Allows filtering and comparison visually
Creating a Pivot Chart (Steps)
1. Click anywhere inside the Pivot Table
2. Go to Insert tab
3. Click PivotChart
4. Choose chart type (Column / Bar / Pie / Line)
5. Click OK
Example: Pivot Chart Showing Sales by Region
Pivot Table Setup:
Rows → Region
Values → Sum of Sales
Final Output (Explanation):
A column chart appears showing total sales for each region.
Each column represents a region
Height of the column shows total sales
Higher column indicates higher sales
Advantages of Pivot Charts
Dynamic and interactive
Automatically updates with data changes
Easy to understand and present
Difference Between Chart and Pivot Chart
Normal chart uses fixed data
Pivot chart changes when Pivot Table fields are modified