Find & Replace

You can quickly locate and modify data across your worksheet.
● Find:
o G o to Home tab Find & Select Find
o Or press Ctrl + F
o Enter the value you want to search for and click Find All or Find Next
● Replace:
o G o to Home tab Find & Select Replace
o Or press Ctrl + H
o Enter the value to find and the replacement value, then click Replace All or
Replace
G o To
Jump to a specific cell or range instantly.
● G o to Home tab Find & Select G o To
● Or press Ctrl + G or F5
● Type the cell reference (e.g., A1, B5) and press Enter
G o To Special
Select cells based on specific criteria like formulas, blanks, constants, etc.
● G o to Home tab Find & Select G o To Special
● Or press Ctrl + G , then click Special
● Options include:
o Formulas: Select cells with formulas (you can filter by type: numbers, text,
logicals, errors)
o C onstants: Select cells with hardcoded values
o Blanks: Select all blank cells in a selected range
o Visible cells only: U seful when working with filtered data
o C onditional formats, Data validation, Objects, and more
G oTo
W hat is a cell reference?
● A cell reference refers to the name or address of a specific cell or range of
cells within the spreadsheet. A cell reference is commonly used as a
variable in Excel formulas. W hile representing the cell reference in Excel,
we need to specify the column name followed by the row number of the
respective cell.
● The following image displays the cell reference of the selected cell in an Excel
sheet:
●

A Simple Reference
The basic use of a cell reference can be displayed by simply mentioning the
referred cell with the equal sign. For example, if we enter "=A1" without quotes in
another cell within the sheet, the value of A1 will be displayed in the
corresponding cell. This means that the value of the selected cell, where the cell
reference is entered, is exactly equal to that of cell A1.
Reference to a Cell Range
W e can also use the reference of multiple cells at once by referring to their cell
range. For example, if we use the notation "=A2:C6" without the quotes, we refer
to the entire cell range from A2 to C6. However, a range alone is not valuable
data in Excel. W hen we use this cell reference in an Excel cell, Excel gives the
#VALU E! error, which means that the formula is missing. Therefore, a reference
to a cell range (A2:C6) has meaning only when used within a function or formula
(as discussed next).

Cell Reference in a Function

Excel can perform a tremendous job when we use a cell range in a function. For
example, if we supply the range A2:C6 in SU M function, Excel adds up all values
of the cell range from A2 to C6 gives the calculated value as a result.

How many types of cell references are there in Excel?

U nderstanding different types of cell references mainly help us to work with
Excel formulas easily, thereby preventing unexpected formula errors. This is
most helpful when copy- pasting Excel formulas. There are three primary types
of cell references in Excel based on different use cases, such as:
o Relative Cell Reference
o Absolute Cell Reference
o Mixed Cell Reference
Relative Cell Reference
A relative cell reference is the default approach in Excel. W henever we enter any
cell reference or a range within the formula in Excel, the reference used is
relative. The corresponding cell references are used normally with the relative
references, which typically represent the combination of column name and row
number. The cell reference does not contain any dollar ($) sign in relative
reference.
W hen we copy formulas from one relative cell to others, the cell references are
automatically adjusted by Excel based on respective rows and columns. The
relative cell references are commonly used to perform the same operation on
multiple relative cells by changing the corresponding cell's column and row
addresses in the formula.
How to use relative cell references in Excel?
Suppose that we have the following Excel sheet with two numbers in columns A
and B, and we want to add both the values in column C.

W e need to perform the following steps to use a relative reference and sum up
values from the same rows of columns A and B.
o First, we must select the destination cell to enter the formula to add values. In
our case, we select cell C 2 and enter the formula "=A2 + B2" without
quotes. After that, we press the Enter key to get the calculated value in cell
C 2.
o Next, we must copy- paste cell C2 to other relative cells from C3 to C8.
Alternately, we can click and hold the fill handle from the bottom- right
corner of cell C2. W e must drag the fill handle to the last cell we want to
copy the respective formula. In our case, we drag the fill handle to cell C8.

After copying the formula to all corresponding cells from C3 to C8, the addition
operation is performed using values from cells A3, B3, and A4, B4, etc.
Absolute Cell Reference
In Excel, we don't always want Excel to automatically change the references,
especially when copied into other cell or a range that are not relative. In such
cases, the formula gives wrong results or the formula error. This is where the
absolute cell references are useful. U nlike relative cell references, absolute cell
references do not change when copied to other cells.
An absolute reference is the cell reference in which the corresponding reference
is locked, meaning that the row and column remain constant. This type of cell
reference contains a dollar ($) sign before the column name and row number,
making the corresponding reference fixed. W e can press the F4 function key to
fix the reference or lock it for the selected cell. $A$1, $B$1, and $C$1 are
examples of absolute cell references.
How to use absolute cell references in Excel?
Suppose that we have the following Excel sheet with some items (Column A)
with their initial prices (Column B). However, the prices have increased by 5%
(cell E2), and we need to calculate the new price for each item using the absolute
cell reference.

W e need to perform the following steps to use an absolute reference to calculate

increased prices (C olumn C ) for each item:
o First, we must select the destination cell and enter the formula to calculate the
new/increased price. In our case, we select cell C 2 and enter the formula
"=B2*$E$2+B2" without quotes. After that, we press the Enter key to get the
increased price in cell C 2.
According to the formula above, we multiply the item's initial price with the
increased percentage rate and add the resultant value to the old price for
the respective item. In this way, we can calculate the increased price of the
item.
o Since the increased rate percentage is fixed (5% ) for each item, we add the
dollar ($) signs with E2 cell to make it absolute, i.e., $E$2. Thus, $E$2 will
be unchanged after copying the formula into other cells.
o Next, we must copy- paste cell C2 to other relative cells from C3 to C8.
Alternately, we can click and hold the fill handle from the bottom- right
corner of cell C2. W e must drag the fill handle to the last cell we want to
copy the respective formula. In our case, we drag the fill handle to cell C8.

W hen copied the formula into other relative cells, the values from column B
change automatically. However, the absolute reference ($E$2) does not
change. For instance, if we select cell C5, we can see that cell B2 has
changed to B5, but $E$2 is constant.

It is important to note that we must use the dollar ($) sign in both row and column
letters to create an absolute cell reference.
Mixed Cell Reference
As the name suggests, the mixed cell reference combines the relative and
absolute reference. The dollar sign is used either before the column letter or the
row number in a reference. W ith the mixed reference, we can use the following
two cases in reference:
o The column remains fixed, while the row changes as the relative reference
when copying the respective formula.
o The row remains fixed, while the column changes as the relative reference
when copying the respective formula.
$A1 and B$1 the examples of the mixed cell reference where relative and
absolute references are combined.
Q uick Filtering
Q uick filtering is the easiest way to narrow down data.
● Steps:
1. Select your data range or table.
2. G o to the Data tab Click Filter (adds dropdowns to headers).
3. C lick the dropdown in any column to:
● Sort A– Z or Z– A
● Filter by specific values
● U se Text Filters, Number Filters, or Date Filters for custom conditions
● Example: Filter a sales column to show only values greater than ₹10, 000.
Filtering by Multiple Criteria
You can apply multiple conditions using either AutoFilter, Advanced Filter, or the
FILTER function.
AutoFilter (AND logic across columns)
● Apply filters to multiple columns using dropdowns.
● Each column’ s filter acts as an AND condition.
Advanced Filter (AND/OR logic within or across columns)
● G o to Data tab Sort & Filter Advanced
● Define a criteria range with headers and values.
● Supports:
o AND logic: Criteria in the same row
o OR logic: Criteria in different rows
● Step- by- step examples of Advanced Filter
● FILTER Function (Dynamic filtering with formulas)
● Syntax: =FILTER(range, (criteria1) * (criteria2), "No data")
● =FILTER(B5:D16, (C5:C16="A") * (D5:D16> 80), "No data")
Performing Calculations on Filtered Data
W hen data is filtered, Excel adjusts calculations automatically— but some
functions behave differently.
Functions that respect filters:
● SUBTOTAL: Automatically ignores hidden rows
o Example:
● Performing Calculations on Filtered Data
● W hen data is filtered, Excel adjusts calculations automatically— but some
functions behave differently.
● Functions that respect filters:
● SU BTOTAL: Automatically ignores hidden rows
● Example:
AGGREGATE : More flexible than SU BTOTAL
● Example:
● =AG G REG ATE(9, 5, C2:C100) // 5 = ignore hidden rows
● Functions that do NOT respect filters:
● SU M, AVERAG E, COU NT include all rows— even hidden ones
● To ensure accurate results, use SU BTOTAL or AG G REG ATE when working
with filtered data.
AutoFill
AutoFill extends a pattern or formula across adjacent cells.
How to U se:
● Type a value or formula in a cell.
● Hover over the bottom- right corner of the cell until you see a plus sign (+)
(called the Fill Handle).
● Drag down, up, or sideways to fill the cells.
Examples:
● Fill a series: 1, 2, 3… or Monday, Tuesday…
● C opy formulas: =A1+B1 dragged down applies to =A2+B2, =A3+B3, etc.
● U se C trl+D to fill down or Ctrl+R to fill right.
Learn more about AutoFill shortcuts and techniques.
Flash Fill
Flash Fill detects patterns in your data and fills in the rest automatically.
How to U se:
● Type the desired result in the first cell to establish a pattern.
● G o to Data tab Click Flash Fill
● Or press Ctrl + E
Examples:
● Split full names: From John Smith to John and Smith in separate columns.
● C ombine data: Merge First Name and Last Name into Full Name.
● Format data: Convert 1234567890 into (123) 456- 7890
Key Differences

Basic Method: Remove Duplicates Tool

Steps:
Select your data range (including headers).

G o to the Data tab.

Click Remove Duplicates in the Data Tools group.

In the dialog box:

Check the columns you want to evaluate for duplicates.

Click OK.
Excel will show how many duplicates were removed and how many unique
values remain.

Shortcut:
Press Alt + A + M to open the Remove Duplicates dialog instantly.

Advanced Options
1. Remove Duplicates from Specific Columns
U seful when you want to keep rows with unique combinations (e.g., Name +
C ountry).

In the dialog, check only the relevant columns.

2. U se Advanced Filter (Non- Destructive)

G o to Data Sort & Filter Advanced.

C hoose “ Copy to another location” .

C heck “ U nique records only” .

This extracts unique values without deleting anything.

3. U se the U NIQ U E Function (Excel 365+)

=U NIQ U E(A2:A100)
● Returns a list of unique values from the range.
4. Power Q uery
● Load your data into Power Q uery via Data G et & Transform From Table/
Range.
● U se Remove Duplicates in the Power Q uery editor.
● G reat for large datasets or automation
G et External Data - From web, from text and from other sources
From W eb
U se Excel’ s W eb Connector to import data from websites.

Steps:
G o to the Data tab G et Data From Other Sources From W eb

Paste the U RL of the webpage containing the data.

Excel opens the Navigator window showing detected tables.

Select the table you want Click Load or Transform Data to clean it up in Power
Q uery.

Learn more in Microsoft’ s official guide

From Text or CSV Files

Import structured data from .txt or .csv files.

Steps:
G o to Data tab G et Data From File From Text/CSV

Select the file and click Import

C hoose the delimiter (comma, tab, semicolon, etc.)

C lick Load or use Transform Data to clean it in Power Q uery

You can also use the Text Import W izard by enabling legacy import tools in Excel
Options.

From Other Sources

Excel supports importing from databases, other workbooks, and cloud services.
 C ommon Sources:
From W orkbook: Import data from another Excel file

From Folder: Combine multiple files in a folder

From Database: SQ L Server, Access, Oracle, etc.

From Online Services: SharePoint, Azure, etc.

Steps:
G o to Data tab G et Data Choose your source

Navigate to the file or server

Select the table or data range

Load or transform as needed