Jim has a large worksheet containing over 12,000 rows of data. He often filters the data it to get just what he wants. He would like to change the contents of every filtered cell in a column, but if he selects the cells in the filtered column and tries to paste a value, all cells in that column, filtered or not, get replaced. Jim wonders if there is an easy way to change just what is in the cells visible after filtering.
There is a very easy way to handle this, and it has everything to do with how you select the cells whose contents you want to change. When Jim filters his data and selects the cells in the filtered column, not only the visible cells are selected. All of them are selected, visible or not, and so any edits affect them all.
The easy way is to filter the data, copy the cell you want to paste, and select the filtered column, as Jim is doing. Then, press Alt+; (that's the Alt key plus a semicolon). Excel reduces the selected cells to only those that are visible. In other words, the cells in the rows that have been filtered out are no longer selected. Now when Jim does a paste (Ctrl+V), only the selected, visible cells are affected.
The Alt+; shortcut is the same as pressing F5 (or Ctrl+G) to display the Go To tab of the Find and Replace dialog box, then clicking Special, clicking the Visible Cells Only radio button, and finally clicking OK. That's a lot of steps that are condensed into the Alt+; shortcut key, so it is quite handy.
It is interesting that the behavior noted by Jim is not the norm in all versions of Excel. In some versions, selecting cells in a filtered column does not, by default, select the invisible cells. If you work in an environment where you are working on multiple versions of Excel, then the safest approach is to remember the Alt+; shortcut to ensure that only visible cells are selected.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13989) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. Youll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
When you are working with large amounts of data in a worksheet, filtering that data can make the process much simpler. ...
Discover MoreThe advanced filtering capabilities of Excel allow you to easily perform comparisons and calculations while doing the ...
Discover MoreFiltering can be a powerful way to work with large amounts of data in a worksheet. If you use filtering quite a bit, you ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2026-07-26 17:23:08
MIke H
Since I have a memory like a sieve for keyboard shortcuts, I customised the Quick Access Toolbar to include "Select Visible Cells", so it is just a click away.
2026-07-25 16:01:35
J. Woolley
The Tip describes two ways to select the filtered cells (which my Excel 365 does natively) but only one way to replace their value: "...copy the cell [value] you want to paste. ...Now when Jim does a paste (Ctrl+V), only the selected, visible cells are affected."
After selecting the filtered cells, here's two more ways to replace their value:
1. You can replace the value of all the filtered cells by typing the new value into the active cell and pressing Ctrl+Enter. (The active cell is always one of the selected cells.)
2. Or you can use Find And Replace (Ctrl+H):
2a. In “Find what:” enter the value you want to change or enter an asterisk (*) wildcard (for one or more).
2b. In “Replace with:” enter the new value.
2c. Click “Replace” (one at a time) or "Replace All".
Only the filtered cells are changed.
Curiously, after selecting filtered (non-contiguous) cells this VBA expression
Selection.Areas.Count
returns 1 and this expression
Selection.Address
returns a contiguous range, but this expression
Selection.Value = "xyz"
changes only the filtered cells.
Assuming use of AutoFilter (Ctrl+Shift+L), here's how to select only the filtered cells below the header in column B of the active sheet:
Dim Target As Range
Set Target = ActiveSheet.AutoFilter.Range.Columns("B")
Set Target = Target.Offset(1, 0).Resize(Target.Rows.Count - 1)
Target.SpecialCells(xlCellTypeVisible).Select
An error will occur if there are none.
2026-07-25 12:01:26
Michael (Micky) Avidan
Hi,
Two more useable solutions:
1) Using VBA which I'll skip this time
2) If you are replacing values based on a rule or adding new text:
a) Insert a temporary blank column next to your data.
b) In the first visible filtered row, type the new value.
c) Press Ctrl + E (Flash Fill) or use a simple IF formula in the helper column (e.g., =IF(CONDITION, "New Value", Original_Cell)).
d) Copy the helper column and Paste as Values over your original column.
HAVE FUN !
2026-07-25 11:58:45
Michael (Micky) Avidan
Hi,
Two more useable solution:
1) Using VBA which I'll skip this time
2) If you are replacing values based on a rule or adding new text:
a) Insert a temporary blank column next to your data.
b) In the first visible filtered row, type the new value.
c) Press Ctrl + E (Flash Fill) or use a simple IF formula in the helper column (e.g., =IF(CONDITION, "New Value", Original_Cell)).
d) Copy the helper column and Paste as Values over your original column.
HAVE FUN !
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2026 Sharon Parq Associates, Inc.
Comments