Skip to content
```html

1. Conditional Formatting

Definition: Conditional Formatting is an Excel feature used to automatically apply formatting to cells when the data satisfies a specified condition or criterion.

The formatting may include a different font color, background color, borders, data bars, color scales, or icon sets.

Menu Path:
Home Tab → Styles Group → Conditional Formatting

Example

Suppose a student marks table contains marks from 0 to 100. We want to highlight students who scored more than 80.

Condition: Marks > 80
Action: Apply a suitable highlight color.

Another Example

In an employee salary table, Conditional Formatting can be used to highlight salaries greater than ₹50,000.

Remember: Conditional Formatting changes the appearance of data according to a condition. It does not change the actual value stored in the cell.

2. Sorting

Definition: Sorting means re-arranging the existing data in an Excel table according to a specified order.

Common sorting orders are:

  • Ascending: A to Z, Smallest to Largest
  • Descending: Z to A, Largest to Smallest

Example

Consider the following student data:

Student Course Marks
Ravi C 65
Anu Python 92
Raju Java 78

If the Marks column is sorted from largest to smallest, the order becomes:

Student Course Marks
Anu Python 92
Raju Java 78
Ravi C 65
Important: When sorting a table, make sure the complete data range is selected so that the information belonging to each record remains together.

3. Filtering

Definition: Filtering means displaying only the records that match specified criteria while temporarily hiding the remaining records.
Menu Path:
Home Tab → Editing Group

OR

Data Tab → Sort & Filter Group

Example

Suppose a student table contains students from C, Java and Python courses.

If we apply a filter to the Course column and select only Python, Excel displays only Python students. Other records are temporarily hidden.

Filter Criteria: Course = Python
Result: Only students enrolled in Python are displayed.

Filtering by Number

We can also filter numerical data using conditions such as:

  • Greater Than
  • Less Than
  • Equal To
  • Greater Than or Equal To
  • Less Than or Equal To
  • Between
Example: To display students who scored more than 60 marks, apply the condition Marks > 60.
Remember: Filtering does not normally delete the hidden records. It temporarily hides records that do not satisfy the filter.

4. RANDBETWEEN Function

Definition: RANDBETWEEN returns a random whole number between the specified lower and upper limits.

Syntax

=RANDBETWEEN(Low, High)

Example 1

=RANDBETWEEN(1,100)

This formula returns a random whole number from 1 to 100, including both 1 and 100.

Example 2

=RANDBETWEEN(10,50)

This formula returns a random whole number from 10 to 50.

Practical Example

Suppose we want to generate random marks for students. Enter the following formula in the Marks column:

=RANDBETWEEN(1,100)

Copy the formula down to generate random marks for multiple students.

Important: RANDBETWEEN is a volatile function. The generated values can change when Excel recalculates the worksheet.

5. Charts

Definition: A Chart is a graphical representation of data. Charts make it easier to understand, compare and analyse numerical information.
Menu Path:
Insert Tab → Charts Group

Example

Consider the following marks:

Student Marks
Ravi 65
Anu 92
Raju 78
Sita 85

Select the data and insert a Column Chart to visually compare the marks of the students.

Common Types of Charts

  • Column Chart
  • Bar Chart
  • Line Chart
  • Pie Chart
  • Area Chart
  • Scatter Chart

6. Insert Chart in a New Sheet

To create a chart in a new worksheet, select the required data and press:

F11

Excel creates a chart on a separate chart sheet.

Tip: F11 is useful when you want the chart to occupy a separate worksheet instead of appearing alongside the source data.

7. Contextual Tabs of a Chart

When a chart is selected, Excel displays additional Contextual Tabs on the Ribbon.

  1. Chart Design
  2. Chart Format

Chart Design

Chart Design contains commands related to the design and structure of the chart, such as changing the chart type, selecting chart layouts and styles, and managing chart elements.

Chart Format

Chart Format contains commands used to format selected chart elements such as shapes, text, fill, outline and effects.

8. Components of a Chart

A chart contains several important components:

  1. Chart Area
  2. Plot Area
  3. Category Axis
  4. Value Axis
  5. Gridlines
  6. Chart Title
  7. Axis Title
  8. Legend

1. Chart Area

The Chart Area is the complete area occupied by the chart, including the title, plot area, legend and other chart elements.

2. Plot Area

The Plot Area is the area where the actual graphical representation of the data is displayed.

3. Category Axis

The Category Axis generally displays categories such as student names, months, products or departments.

Example: In a chart showing monthly sales, January, February and March can appear on the Category Axis.

4. Value Axis

The Value Axis displays the numerical scale used to represent the values in the chart.

Example: A marks chart may have values such as 0, 20, 40, 60, 80 and 100 on the Value Axis.

5. Gridlines

Gridlines are reference lines displayed in the plot area. They help the reader estimate the values represented by the data points or columns.

6. Chart Title

The Chart Title describes the purpose or subject of the chart.

Example: "Student Performance – 2026"

7. Axis Title

Axis Titles describe what the Category Axis and Value Axis represent.

Example:
Category Axis: Students
Value Axis: Marks

8. Legend

A Legend identifies the data series represented in a chart.

Example: If a chart contains separate series for C, Java and Python, the Legend helps identify each series.

9. Practical Exercise – Student Marks

Create the following worksheet:

Student Name Course Marks
Ravi C
Anu Python
Raju Java
Sita C
Kiran Python
Priya Java

Perform the following operations:

  1. Use RANDBETWEEN to generate marks between 1 and 100.
  2. Apply Conditional Formatting to highlight marks greater than 80.
  3. Sort the students according to marks from highest to lowest.
  4. Filter the table to display only Python students.
  5. Remove the filter and display all students.
  6. Create a Column Chart using Student Name and Marks.
  7. Add a suitable Chart Title.
  8. Add Category Axis and Value Axis titles.
  9. Display suitable Gridlines and Legend.
  10. Press F11 and create a chart in a new chart sheet.

10. Practice Questions

1. What is Conditional Formatting? Explain with one practical example.
2. Write the menu path for Conditional Formatting.
3. What is Sorting? Explain Ascending and Descending order.
4. What is Filtering? How is it different from Sorting?
5. Write the menu path used to apply filtering in Excel.
6. Write the syntax of the RANDBETWEEN function.
7. What type of value is returned by RANDBETWEEN?
8. Write a formula to generate a random number between 1 and 100.
9. Write a formula to generate a random number between 50 and 100.
10. What is a Chart? Why are charts useful in Excel?
11. Write the menu path for inserting a chart.
12. Which function key can be used to create a chart in a new chart sheet?
13. Name the two contextual tabs displayed when a chart is selected.
14. What is the difference between Chart Area and Plot Area?
15. What is the purpose of the Category Axis?
16. What is the purpose of the Value Axis?
17. What is the purpose of Gridlines in a chart?
18. What is a Chart Title? Give one example.
19. What is an Axis Title?
20. What is the purpose of a Legend in a chart?

11. Practical Challenge

Create an Excel worksheet containing at least 15 student records with the following fields:

  • Student Name
  • Course
  • Gender
  • Marks

Then perform all of the following:

  1. Generate random marks using RANDBETWEEN.
  2. Apply Conditional Formatting to the Marks column.
  3. Sort the records from highest marks to lowest marks.
  4. Filter students according to Course.
  5. Filter students who scored more than 60 marks.
  6. Clear the filter and display all records.
  7. Create a suitable Column Chart.
  8. Add Chart Title and Axis Titles.
  9. Display appropriate Gridlines.
  10. Identify the Chart Area and Plot Area.
  11. Identify the Category Axis and Value Axis.
  12. Identify the Legend.
  13. Create another chart using F11 in a new chart sheet.
Learning Goal: After completing this exercise, you should be able to use Conditional Formatting, Sorting, Filtering, RANDBETWEEN and Charts to analyse and present Excel data effectively.
```