Aung Shine
Microsoft Excel is a powerful software for analyzing and managing data. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel Functions that we can use to create formulas. In Excel, we often need to paste data into visible cells only.
Sometimes, we apply the Filter feature to our dataset. Applying Filter can hide some rows of data. Generally, when we paste copied data in a range, Excel pastes the values in the entire range including the hidden cells.
This can lead to some unwanted results which can be quite time consuming to fix. In this article, we will learn different ways to paste into visible cells only in Excel. The following video demonstrates an overview of one of the methods of this article.
Download Practice Workbook
You can download the practice workbook from the link below.
How to Paste into Visible Cells Only in Excel: 6 Easy Methods
In this section of the article, we will show you 6 easy ways to Paste into Visible Cells Only in Excel. For illustration, we have the Delivery Status of Ordered Products in XYZ Tech Store as our dataset. In the dataset, we have a table with 5 columns consisting of different information. We will filter this dataset and then try to paste values into only the visible cells.
So, let’s start this article, and explore the detailed steps of these methods.
1. Pasting into Visible Cells Only Through Go To Command in Excel
In our first method, we’ll use the Go To command in Excel. As we copy a cell value and try to paste it into the filtered column in the worksheet, it also gets pasted in the hidden cells. To avoid this incident, follow the steps below.
Step 01: Filter Dataset
- First, click on the drop-down icon beside the Delivery Status column >> select the “In Transit” option >> click OK.
Step 02: Paste Data into Visible Cells
As a result, only the products that have an “In Transit” Delivery Status, will show in the dataset. Now, we want to change these statuses to “Delivered”.
- Copy cell B23 >> select the cells of the column “Delivery Status” >> use the keyboard shortcut F5.
You can also use the keyboard shortcut CTRL + G to open the Go To dialogue box.
- The Go To dialogue box will open. In the Go To dialogue box, click on the Special option.
- Choose the Visible cells only option from the Go To Special dialogue box. Then click OK. This will select only the visible cells of the worksheet.
- Finally, use the keyboard shortcut CTRL + V to paste the copied data into the visible cells.
As a result, the “In Transit” status will be replaced by the “Delivered” status as shown in the following image.
- Following that, click on the drop-down icon beside the Delivery Status column >> select the Clear Filter From “Delivery Status” option.
Now, you can see that all the statuses that were “In Transit”, are now changed to “Delivered”. The other cells remained unchanged. This indicates that we have successfully pasted data only into the visible cells.
Read More: Excel VBA to Select First Visible Cell in Filtered Range
2. Use of Excel Fill Feature for Pasting into Visible Cells
Using the Fill feature of Excel is another efficient way to paste data only into the visible cells in Excel. The Fill feature of Excel allows us to identify a pattern or series across multiple cells automatically. This saves a lot of time and effort. Now, let’s follow the steps outlined below to paste data only into the visible cells using the Fill feature.
- Filter the dataset based on the Delivery Status (“Delivered”).
- After that, select the cells of the filtered Delivery Status column >> hold CTRL and select the destination cells. In this case, we have selected the cells of the Shipment column as our destination cells.
- Then, go to the Home tab >> choose the Fill option from the Editing group >> select the Left option as we are pasting data to the left of the Delivery Status column.
- Clear the Filters to see the entire dataset. You can see that, copied Delivery Status is pasted to the Shipment column. The rest of the cells remained blank.
Read More: [Fixed!] Paste Visible Cells Only Not Working (4 Possible Solutions)
3. Pasting into Visible Cells with Keyboard Shortcut
Now, we will use a keyboard shortcut to paste data only into visible cells in Excel. This keyboard shortcut can help us complete work faster and with less effort. Let’s achieve this by following the guidelines below.
- Filter the dataset by following the steps mentioned earlier.
- Select the cells of the Delivery Status column >> type in the desired Delivery Status (“Delivered“) >> press CTRL + ENTER.
- Finally, clear the filters in the Delivery Status column. You will see that all of the only the “In Transit” statuses are replaced by the “Delivered” status.
Read More: How to Copy and Paste Visible Cells Only in Excel (3 Easy Ways)
4. Using Quick Access Toolbar to Paste into Visible Cells Only
Using the Quick Access Toolbar is another smart way to paste data only into visible cells in Excel. In the Quick Access Toolbar, we can add a wide range of commands to make them more accessible and readily available. Now, let’s follow the steps mentioned in the following section to paste data only into the visible cells in Excel.
- First, click on the Customize Quick Access Toolbar option >> select the More Commands option from the drop-down.
As a result, the Excel Options dialogue box will open on your worksheet.
- Click on the drop-down icon >> select the All Commands option.
- Following that, scroll down and select the Select Visible Cells option >> click on Add >> click OK.
The Select Visible Cells option will be added to the Quick Access Toolbar.
- Copy the data that you want to paste. Here, we copied cell B23.
- Select cells of the Delivery Status column >> use the keyboard shortcut CTRL + V to paste the copied data.
- Finally, clear the filter to see the entire table. You will see that only the products that had an “In Transit” status, are replaced by “Delivered” status as shown in the following image.
5. Apply Simple Formula in Excel to Paste Only into Visible Cells
We can also apply a simple Excel formula to paste data only into the visible cells in Excel. This method is quite straightforward and easy to follow. Now, let’s follow the guidelines discussed below to do this.
- Use the following formula in cell D5 and press ENTER.
=[@[Delivery Status]]Here, [Delivery Status] is the Named Range of the Delivery Status column.
As we used the named range, Excel will automatically copy the formula for the rest of the cells in the Shipment column.
Note: If you don’t use a table, then your formula will be like “=E6”. You also need to drag the Fill Handle to copy down the formula.
- Lastly, clear the filters by following the steps mentioned earlier and you will see that only the visible cells are pasted in the Shipment column as shown in the image below.
Read More: Excel VBA: Select Visible Cells After Autofilter (4 Examples)
6. Pasting into Visible Cells with Excel VBA
In our final method, we will use the VBA Macro option of Excel to paste data only into the visible cells automatically. Now, let’s follow the instructions mentioned below.
Step 01: Launch VBA Editor
- First, go to the Developer tab >> choose the Visual Basic option.
Note: Developer tab is not available in Excel by default. You need to manually add the Developer option if it is not available to you.
As a result, the Microsoft Visual Basic for Applications window will open on your worksheet.
- Now, go to the Insert tab >> select the Module option.
Step 02: Write and Save VBA Code
- A blank Module will be created. Now, write the following VBA code in it >> click on the Save icon.
Sub PasteIntoVisible()
Dim rng As Range
Dim visinputRange As Range
Dim dest As Range
Dim inputRange As Range
Dim r As Range
Set rng = Application.Selection
rng.SpecialCells(xlCellTypeVisible).Select
Set visinputRange = Application.Selection
Set dest = Application.InputBox("Choose dest:", Type:=8)
For Each inputRange In visinputRange
inputRange.Copy
For Each r In dest
If r.EntireRow.RowHeight <> 0 Then
r.PasteSpecial
Set dest = r.Offset(1).Resize(dest.Rows.Count)
Exit For
End If
Next r
Next inputRange
End SubCode Breakdown
List of Variables Used
| Variable Name | Data Type |
|---|---|
| rng | Range |
| visinputRange | Range |
| dest | Range |
| inputRange | Range |
| r | Range |
- First, we created a sub procedure named PasteIntoVisible and declared the necessary variables for the code.
- Then, we used the Set statement to assign the selected cells to the rng variable. After that, we used the rng.SpecialCells(xlCellTypeVisible).Select method to select only the visible cells of the selected range.
- Now, we used an InputBox to take the destination range as input. Following that, we used the Set statement to assign the input obtained from InputBox to the variable named dest.
- Next, we used two nested ForNext loops to copy data from the range visinputRange and paste the copied data to the range named dest.
- Lastly, we ended the sub-procedure.
Step 03: Run VBA Code
- Following that, use the keyboard shortcut ALT + F11 to return back to the worksheet from the VBA Editor window.
- Then, apply a filter by following the steps discussed before >> select the cells that you want to copy >> go to the Developer tab >> select the Macros option.
- The Macros dialogue box will open on your worksheet. Now, choose the PasteIntoVisible option >> click on Run.
- After that, a prompt will open and insert the destination range in it >> click OK.
- Finally, clear the filters and you will see that copied data are pasted only into the visible cells corresponding to the filtered Delivery Status column.
Read More: How to Copy Visible Cells Only without Header Using VBA
How to Paste Formatting into Visible Cells Only in Excel
Pasting formatting into only visible cells is quite similar to pasting data. You can follow the steps mentioned below to do this.
- First, copy the cell from which you want to copy the formatting >> select the range where you want to paste the formatting >> use the keyboard shortcut “ALT + ;” to select only visible cells.
- Following that, go to the Home tab >> choose the Paste option >> select the Formatting (R) option from the drop-down.
- Lastly, clear the filters and you will see that the formatting is pasted to the visible cells only. The remaining cells are unchanged.
Frequently Asked Question
1. How to Paste Data Validation into Visible Cells Only in Excel
Pasting data validation is exactly the same as pasting formatting. Just you need to copy a cell that has data validation instead of copying formatting. The rest of the steps are the same.
2. Is there a way to make “paste into visible cells only” the default paste option in Excel?
No, there is no in-built way to make the “paste into visible cells only” the default pasting option in Excel. But you can always create a custom macro or add-in that will automatically paste data into visible cells only. You can also use keyboard shortcuts to do this.
Practice Section
In the practice workbook, we have provided a Practice Section. Please practice these examples by yourself in the Practice Section.
Conclusion
In conclusion, pasting values into visible cells only in Excel can be a powerful and effective way to summarize and analyze data. You can use any of the methods that are explained in this article according to your convenience. If you regularly work with large datasets, this technique will be a life saver for you. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website.
