Power of Find All with Ctrl + A

Most of the Microsoft Excel users are aware of the short cut key Ctrl + A. What it does is selecting the entire contents of the table if the cursor is in any of the table in the work sheet, or the entire sheet if the cursor is outside the table data of Excel. But one of the good use of this shortcut is in the ‘Find and Replace’ dialog box.

To make it clear, we will consider the below example.

findAllDelRows1

In the above data you can see the text ‘Invoice Number:’ in column A for some of the rows and it is repeated intermittently.  Suppose we want to remove all the rows which has the text ‘Invoice Number :’ in it, writing a formula will be a tough task and takes lots of time. This is where you can combine the power of Ctrl + A with the Find All feature of excel.

Select the entire column A, click Ctrl + F to bring the search dialog box, type ‘Invoice Number :’ in the Find What : and click the Find All Button.

findAllDelRows2

Now you can see all the search results in the bottom of the Search window, keeping the cursor in this result window (cursor will be there already, so don’t move from there), press Ctrl + A. This selects all the cells which are returned in the search. Now press escape button once to go back to the sheet. Right click on any of the highlighted cell and choose Delete … >> Entire Row. Same technique can be used for even deleting all the cell content once or highlighting / formatting all the search result etc.  This is very useful while auditing or cleaning a large data containing thousands of rows.

findAllDelRows3


Leave a Reply

Your email address will not be published. Required fields are marked *