How to select only visible cells in excel. Copy filtered data to Excel. Copy without damaging formatting
Pavlov Nikolay
In this article I would like to present to you the most effective techniques for working in Microsoft Excel, collected by me over the past 10 years of working on projects and conducting trainings on this wonderful program. There is no description of super complex technologies here, but there are techniques for every day - simple and effective, described without “water” - only “dry residue”. Most of these examples will take you no more than one or two minutes to master, but they will help you save much more.
Quickly jump to the desired sheet
Do you happen to work with Excel workbooks consisting of a large number of sheets? If there are more than a dozen of them, then each transition to the next required sheet becomes a small problem in itself. A simple and elegant solution to this problem is to click in the lower left corner of the window on the buttons for scrolling the sheet tabs not with the left, but with the right mouse button - a table of contents of the book will appear with full list all sheets and you can go to the desired sheet in one movement:
This is much faster than scrolling through sheet tabs using the same buttons in search of what you need.
Copy without damaging formatting
How many hundreds (thousands?) of times have I seen this picture, standing behind my students during trainings: the user enters a formula in the first cell and then “stretches” it across the entire column, violating the formatting of the rows below, since this method copies not only the formula, but also the cell format. Accordingly, you then have to manually correct the damage. A second to copy and then 30 to repair a design damaged by copying.
Starting with Excel 2002, there is a solution to this problem that is simple and elegant. Immediately after copying (dragging) the formula onto the entire column, you need to use a smart tag - a small icon that temporarily appears in the lower right corner of the range. Clicking on it will bring up a list possible options copying, where you can select Fill without formatting. In this case, the formulas are copied, but the formatting is not:
Copying only visible cells
If you have been working in Microsoft Excel for more than a week, you must have already encountered a similar problem: in some cases, when copying and pasting cells, more cells are inserted than were, at first glance, copied. This may occur if the copied range included hidden rows/columns, groupings, subtotals, or filtering. Let's take one of these cases as an example:
In this table, subtotals are calculated and rows are grouped by city - this is easy to understand by the plus-minus buttons to the left of the table and by the breaks in the numbering visible lines. If we select, copy and paste data from this table in the usual way, we will end up with 24 extra rows. We only want to copy and paste the results!
You can solve the problem by painstakingly selecting each row of the totals while holding down the CTRL key - as you would for selecting non-adjacent ranges. But what if there are not three or five such lines, but several hundreds or thousands? There is another, faster and more convenient way:
Select the range to copy (in our example it is A1:C29)
Press the F5 key on your keyboard and then the Select button in the window that opens.
A window will appear allowing the user to select not everything in a row, but only the necessary cells:
In this window, select the Visible cells only option and click OK.
The resulting selection can now be safely copied and pasted. As a result, we will get a copy of the visible cells and insert, instead of the unnecessary 29, only the 5 rows we need.
If you suspect that you will have to perform such an operation often, then it makes sense to add a button to the Microsoft Excel toolbar to quickly call such a function. This can be done through the Tools> Customize menu, then go to the Commands tab, in the Edit category, find the Select visible cells button and drag it to the toolbar with the mouse:
Converting Rows to Columns and Back Again
A simple operation, but if you don’t know how to do it correctly, you can spend half a day dragging individual cells manually:
It's actually simple. In that part of higher mathematics that describes matrices, there is the concept of transposition - an action that swaps rows and columns in a matrix with each other. In Microsoft Excel, this is implemented in three steps: Copy the table
Right-click on an empty cell and select Paste Special.
In the window that opens, check the Transpose flag and click OK:
Quickly add data to a chart
Let's imagine a simple situation: you have a report for last month with a visual diagram. The task is to add new numerical data to the chart for this month. The classic way to solve this is to open the data source window for the chart, where you add a new data series by entering its name and highlighting the range with the desired data. Moreover, this is often easier said than done - it all depends on the complexity of the diagram.
Another way - simple, fast and beautiful - is to select the cells with new data, copy them (CTRL+C) and paste (CTRL+V) directly into the chart. Excel 2003, unlike later versions, even supports the ability to drag a selected range of data cells and drop it directly into the chart using the mouse!
If you want to control all the nuances and subtleties, then you can use not a regular, but a special paste by selecting Edit> Paste Special from the menu. In this case, Microsoft Excel will display a dialog box that allows you to configure where and how exactly the new data will be added:
Similarly, you can easily create a chart using data from different tables from different sheets. Performing the same task the classic way will take much more time and effort.
Filling empty cells
After downloading reports from some programs to Excel format or when creating pivot tables, users often end up with tables with empty cells in some columns. These omissions do not allow you to apply familiar and convenient tools such as autofilter and sorting to tables. Naturally, there is a need to fill the voids with values from higher-level cells:
Of course, with a small amount of data, this can easily be done by simple copying - manually dragging each header cell in column A down onto the empty cells. What if the table has several hundred or thousand rows and several dozen cities?
There is a way to solve this problem quickly and beautifully using one formula:
Select all cells in a column with blank spaces (i.e. range A1:A12 in our case)
To keep only empty cells in the selection, press the F5 key and in the navigation window that opens, press the Select button. You will see a window that allows you to select which cells we want to select:
Set the switch to Blank and click OK. Now only empty cells should remain in the selection:
Without changing the selection, i.e. Without touching the mouse, enter the formula into the first selected cell (A2). Press the equal sign on your keyboard and then the up arrow. We get a formula that refers to the previous cell:
To enter the created formula into all selected empty cells at once, press not the ENTER key, but the combination CTRL + ENTER. The formula will fill all empty cells:
Now all that remains is to replace the formulas with values to record the results. Select the range A1:A12, copy it and paste their values into the cells using Paste Special.
Dropdown list in a cell
A technique that, without exaggeration, everyone who works in Excel should know. Its use can improve almost any table, regardless of its purpose. At all trainings, I try to show it to my students on the first day.
The idea is very simple - in all cases when you must enter data from any set, instead of manually entering a cell from the keyboard, select the desired value with the mouse from the drop-down list:
Selecting a product from the price list, the client’s name from the client database, the full name of the employee from the staffing table, etc. There are many options for using this function.
To create a dropdown list in a cell:
Select the cells in which you want to create a drop-down list.
If you have Excel 2003 or older, select Data>Validation from the menu. If you have Excel 2007/2010, then go to the Data tab and click the Data validation button.
In the window that opens, select the List option from the drop-down list.
In the Source field, you must specify the values that should be in the list. Here are the possible options:
Enter text options in this field separated by semicolons
If the range of cells with the original values is on the current sheet, you just need to select it with the mouse.
If it is located on another sheet of this workbook, then you will have to give it a name in advance (select cells, press CTRL+F3, enter the name of the range without spaces), and then write this name in the field
If some cells, rows, or columns in a worksheet aren't visible, you can copy all the cells (or just the visible cells). By default, Excel copies not only visible cells, but also hidden or filtered cells. If you only want to copy visible cells, follow the steps below. For example, you can copy only summary data from a structured worksheet.
Follow the steps below.
Note: When you copy, values are inserted sequentially into rows and columns. If the paste area contains hidden rows or columns, you may need to unhide them to see all the data you copied.
When you copy and paste visible cells in a data range that contains hidden cells or that has a filter applied, you may notice that the hidden cells are pasted along with the visible cells. Unfortunately, you can't change this setting when you copy and paste a range of cells in Excel for the web because Pasting only visible cells is not available.
However, if you format your data as a table and apply a filter, you can copy and paste only the visible cells.
If you don't want to format your data as a table and you have desktop Excel installed, you can open the workbook in Excel to copy and paste visible cells. To do this, click the button Open in Excel and follow the steps in Copying and pasting only visible cells.
additional information
You can always ask a question to an Excel Tech Community specialist, ask for help in the Answers community, and also suggest new feature or improvement on the website
Paste only into visible lines inExcel numbers, formulas, text can be done in several ways. When you need to insert numbers, formulas, text into not all rows of the table, you can use a filter. How to install a filter and how to filter in Excel, see the article “Filter in Excel”. But to insert data only into visible cells, you need your own methods, especially if there are many rows.
The first way is ordinary .
Let's take a table like this. The table will be the same for all examples.
Let's use a filter to remove all digits 2 from the table. In the remaining visible cells we put the number 600. In cell B2 we put the number 600, then copy it down the column (pull the lower right corner of cell B2). Values were copied only to visible cells. You can also insert formulas in the same way. We write the following formula in cell C2. =A2*10
It turned out like this. 
Let's cancel the filter. The result is a table like this.
The formula and numbers were inserted only into the filtered rows.
Second way.
We will also filter the data. In the first cell we write a number, formula, text, etc. Now, if there are thousands of rows, then select the cells like this: press the keys “Ctrl” + “Shift” + the down arrow button (or the up button, depending on where we want to select the cells - below or above the cell in which the number was written) .
Now, or press the key combination “Ctrl” + G, or the F5 key. The Transition dialog box will appear. Click the “Select...” button. And, in the new “Select a group of cells” dialog box, check the box next to the words “Only visible cells”.
Click "OK". Then insert as usual.
Another way to bring up the Select Group of Cells dialog box.On the “Home” tab, in the “Editing” section, click on the “Find and Select” button. In the list that appears, click on the “Select a group of cells” function.
To fill visible cells in selected ones Excel columns , press the key combination “Ctrl” + D. And all selected columns will be filled with data or a formula, as in the first cell. In our example, we wrote the number 800 in cell D2, column D.
Third way.
In a new column (in our example, column E), select the cells. Press the F5 key. The Transition dialog box will appear. Click the “Select...” button. And, in the new “Select a group of cells” dialog box, check the box next to the words “Visible cells only”. Click "OK". Now, without canceling the selection, in the first cell of the column (ours is E2), enter a formula, number, etc. Press the key combination “Ctrl” + “Enter”.
Lists and ranges (5)
Macros (VBA procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (4)
How to paste copied cells into visible/filtered cells only
In general, the meaning of the article, I think, is already clear from the title. I'll just expand it a little.
It's no secret that Excel allows you to select only visible rows (for example, if some of them are hidden or a filter is applied).
So, if you copy only visible cells in this way, they will be copied as expected. But when you try to paste something copied into a filtered range (or containing hidden rows), the result of the paste will not be exactly what you expected. Data will be inserted even into hidden rows.
Copy a single range of cells and paste only into visible ones
To insert data only into visible cells, you can use the following macro:
| Option Explicit Dim rCopyRange As Range "With this macro we copy the data Sub My_Copy() If Selection.Count > 1 Then Set rCopyRange = Selection.SpecialCells(xlVisible) Else : Set rCopyRange = ActiveCell End If End Sub "With this macro we insert data starting from the selected cell Sub My_Paste() If rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count > 1 Then MsgBox "The pasted range must not contain more than one region!",vbCritical, "Invalid range": Exit Sub Dim rCell As Range, li As Long , le As Long , lCount As Long , iCol As Integer , iCalculation As Integer Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 For iCol = 1 To rCopyRange .Columns.Count li = 0: lCount = 0: le = iCol - 1 For Each rCell In rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _ ActiveCell.Offset (li, le).EntireRow.Hidden = False Then rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop While lCount >= rCell.Row - rCopyRange.Cells(1 ).Row Next rCell Next iCol Application.ScreenUpdating = True : Application.Calculation = iCalculation End Sub |
Option Explicit Dim rCopyRange As Range "Use this macro to copy the data Sub My_Copy() If Selection.Count > 1 Then Set rCopyRange = Selection.SpecialCells(xlVisible) Else: Set rCopyRange = ActiveCell End If End Sub "Use this macro to paste the data starting from the selected cells Sub My_Paste() If rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count > 1 Then MsgBox "The pasted range must not contain more than one area!", vbCritical, "Invalid range": Exit Sub Dim rCell As Range, li As Long, le As Long, lCount As Long, iCol As Integer, iCalculation As Integer Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 For iCol = 1 To rCopyRange.Columns.Count li = 0: lCount = 0: le = iCol - 1 For Each rCell In rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False And _ ActiveCell.Offset(li, le).EntireRow.Hidden = False Then rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Loop While lCount >= rCell.Row - rCopyRange.Cells(1).Row Next rCell Next iCol Application.ScreenUpdating = True: Application.Calculation = iCalculation End Sub
To complete the picture, it is better to assign these macros to hot keys (in the codes below, this is done automatically when opening a book with the code). To do this, you just need to copy the codes below into the module This book (ThisWorkbook) :
Option Explicit "Cancel the assignment of hotkeys before closing the workbook Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey "^q": Application.OnKey "^w" End Sub "Assign hotkeys when opening the workbook Private Sub Workbook_Open() Application.OnKey "^q", "My_Copy": Application.OnKey "^w", "My_Paste" End Sub
Now you can copy the desired range by pressing keys Ctrl + q , and insert it into the filtered one - Ctrl + w .
Download example
(46.5 KiB, 9,622 downloads)
Copy only visible cells and paste only into visible ones
At the request of site visitors, I decided to improve this procedure. It is now possible to copy any ranges: with hidden rows, hidden columns, and paste copied cells also into any ranges: with hidden rows, hidden columns. It works exactly the same as the previous one: by pressing keys Ctrl
+
q
copy the desired range (with hidden/filtered rows and columns or not hidden), and paste with a keyboard shortcut Ctrl
+
w
. Insertion is also performed in hidden/filtered rows and columns or without hidden ones.
If the copied range contains formulas, then to avoid reference displacement, you can copy only the cell values - i.e. When inserting values, not formulas will be inserted, but the result of their calculation. Or if it is necessary to preserve the formats of the cells into which the insertion occurs, only the cell values will be copied and pasted. To do this, you need to replace the line in the code (in the file below):
| rCell.Copy rResCell.Offset(lr, lc) |
rCell.Copy rResCell.Offset(lr, lc)
to this:
| rResCell.Offset(lr, lc) = rCell.Value |
rResCell.Offset(lr, lc) = rCell.Value
Both of these lines are present in the file below; you just need to leave the one that is more suitable for your tasks.
Download example:
(54.5 KiB, 7,928 downloads)
Also see:
[]
("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))
Data selection in Excel was carried out using a filter or sorting. Now they need to be printed or moved to another location. Copy to Excel configured so that hidden cells are also copied.
Let's consider two ways, To
How to copy filtered rows in Excel.
First way.
There is a great function in Excel - Paste Special function in Excel.
So we have a table.
How to install a filter, see the article " Filter in Excel ".
We use a filter to remove all Ivanovs from the list. It turned out like this.
Select the table and click “Copy” in the context menu. Left-click cell A9 and select “values” in the context menu.
Click "OK". Voila. Not only the value of the visible rows was copied, but also the format of the cells.
There is one nuance- insert filtered data not into the rows where the filter is located. For example, in our example - not in lines 1-7, but below or on another sheet, etc. If we insert into the rows where the filter is located, then the filtered data will also be inserted into the rows hidden by the filter. In general, it will turn out to be a mess. 
Second way.
The table is the same. Select the table with filtered data. On the “Home” tab, click in the “Editing” section Find and Highlight functions in Excel. Then, click the “Go” button. In the dialog box that appears, click the “Select...” button. In the “Select a group of cells” window, check the box next to “only visible cells”. Click "OK". Now on the same selected table with the right mouse we call context menu. Click the “Copy” function. In a new location (in our example, this is cell A15), click “Insert”. All. It turned out like this.
How, without copying, immediately print filter data in excel, see the article "Bookmark Excel sheet"Page layout""
