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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
The filtering tools provided in Excel make it easy to filter a data list so that only certain rows are displayed. What if ...
Discover MoreWhen working with filtered data, you may want to specially format a column that has a filter applied to it. Here are a ...
Discover MoreThe advanced filtering capabilities of Excel allow you to easily perform comparisons and calculations while doing the ...
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