How do you replace multiple cells?

This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than you think, no VBA programming or formulas are needed.

The picture above demonstrates a simple example, the formula in cell B3 gets values below 5 from sheet 2 cell range B3:B12.

Array formula in cell B3

=SMALL(IF(Sheet2!$B$3:$B$12<5, Sheet2!$B$3:$B$12, ""), ROWS($A$1:A1))

I will now show you how to replace Sheet2 with Sheet3 in formulas, in all cells in Sheet1. Simply press CTRL and H to open the Find and Replace dialog box.

Press with left mouse button on the "Options" button to see all settings.

Here you have the option to

• Search the entire workbook or just the active worksheet. I want to search the active worksheet so I change nothing.
• Match entire cell contents. Deselect the check box, I want to match specific strings in formulas.

Press with left mouse button on in field "Find what:" and type Sheet2. Now press with left mouse button on in field "Replace with:" and type Sheet3, then press with left mouse button on "Replace All" button.

This will find all instances of Sheet2 in all cells and replace them with Sheet3.

Press with left mouse button on the "OK" button and then the "Close" button.

The array formula in cell B3 (Sheet1) changes to:

=SMALL(IF(Sheet3!$B$3:$B$12<5, Sheet3!$B$3:$B$12, ""), ROWS($A$1:A1))

Recommended article

Recommended articles

Search all workbooks in a folder
Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]

Replace part of formula in a specific cell range

Simply select the cell range, press CTRL + H to open the Find and Replace dialog box.

The "Find and Replace" action will now be applied to cell range B3:B4.

Replace n:th instance

Array formula in cell B3 (Sheet1)

=SMALL(IF(Sheet2!$B$3:$B$12<5, Sheet2!$B$3:$B$12, ""), ROWS($A$1:A1))

To replace only the first instance of a specific search string in the formula simply include more characters so it makes the search string unique.

Example, you want to replace Sheet2 with Sheet3 but only the first instance found in the formula.

Press CTRL + H to open the Find and Replace dialog box.

Don't forget to add the included characters in the "Replace with: " field as well.

String IF(Sheet2 is found in only one location in each cell, this will replace only the first instance of Sheet2.

=SMALL(IF(Sheet3!$B$3:$B$12<5, Sheet2!$B$3:$B$12, ""), ROWS($A$1:A1))

Excel basics category

How to use absolute and relative references
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]

How to quickly select a non contiguous range
A non-contiguous list is a list with occasional blank cells and that makes it harder to select the entire cell […]

Remove print preview lines (Page Breaks)
Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other […]

How to find errors in a worksheet
Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]

How to quickly select a cell range
Selecting cell ranges in Excel can sometimes be a real pain scrolling forever it seems. There is a quick and easy […]

How to select and delete blank cells
This article demonstrates how to select all blank cells in a given cell range and how to delete them. It […]

How to find blank cells
In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]

How to quickly select blank cells
In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of […]

How to add a macro to your Excel Quick Access Toolbar
The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your […]

How to select cells with data
The picture above shows data in column B, some cells contain nothing, they are blank. I will now go through […]

Convert column number to column letter
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]

Improve worksheet readability
Making your sheets easy to read is a fundamental approach of creating useful worksheets. Your message must be crystal clear, […]

Rotate text to save space
If your cell text is taking to much space Excel allows you to rotate text in any angle. Here are […]

Create a numbered list ignoring blank cells
The formula in column B returns a running count based on values in column C. Formula in cell B3: =IF(C3<>"",COUNTA($C$3:C3),"") […]

How to format numbers as text
A number that is formatted as text will be left-aligned instead of right-aligned, this makes it easier for you to […]

Text string manipulation category

Identify all characters in a cell value
Table of Contents Identify all characters in a cell value Identify all characters in a cell value - Excel 365 […]

Text string manipultion category

How to remove unwanted characters in a cell
Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]

How to remove numbers from a cell value
This article demonstrates an array formula that extracts all characters except numbers from a cell, cell C3 contains the formula […]

Functions in this article

SMALLIFROWS

Excel formula categories

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

How do I replace multiple cells in Excel?

How do I replace multiple cells in sheets?

How do you find and replace multiples?

How do I replace cells with other cells?