Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • Gmail
    • Productivity
      • Time Management
      • Task Automation
    Inside The Web
    Home»Excel»How to Use Find and Delete in Excel

    How to Use Find and Delete in Excel

    AsmiBy AsmiFebruary 8, 2023 Excel 4 Mins Read

    Did you just spend a whole lot of time entering a piece of information, only to realize you no longer need the data? Unfortunately, we cannot bring you back the time you spent entering the data but, what we can do is help you save time in deleting them.

    Excel has three features – Find & Select, Sort & Filter, and the Find & Replace command that will help you in the process of locating and deleting values. You can use these tools to first select the data, and then use Ctrl + – to delete them. If you’re not a big fan of shortcuts, you can use the Delete option in the Home ribbon to delete the selected values.

    Find & Select

    The Find & Select tool comes in handy when you’re looking for more than just data on your spreadsheet. The utility helps you select almost everything including formulas, comments, conditional formatting, constant values, data validation, and objects.

    1. Open your worksheet.
    2. On the Home tab, select Find & Select.
      Find and Select Excel
    3. From the drop-down, select one of these:
    • Formulas: To select cells with formulas.
    • Comments: Selects cells with embedded comments.
    • Conditional Formatting: To select cells with conditional formatting rules.
    • Constant: Selects cells with static values.
    • Data Validation: To select cells with data validation.
    • Select Objects: Selects objects in your sheet.
      Find and Select values Excel

    On your keyboard, select Ctrl + – to delete the selected cells.

    Sort & Filter

    Sort and Filter is the perfect tool if you’ve entered your data under a table. Even if you haven’t inserted a table, you can still use the tool if your data is under a range.

    You will not have to insert Filter if your data is under the table. If your information is however in a range, select the first cell of the range and head to Home > Sort & Filter > Filter.

    1. Select the drop-down icon on the column header.
    2. Deselect the box next to Select All.
      Deselect all Filter tool Excel
    3. On the search bar, type the data you wish to delete and hit Enter.
      Search result filter tool Excel
    4. The tool will show only the data you wish to delete. Select the data.
    5. Use Ctrl + – to delete the values.
      Delete sheet row
    6. Click on the drop-down menu again and select Select all, and hit Enter.
      Select all sort and filter

    Find and Replace Command

    You can use the Find and Replace command to not only select the cells to delete them but, you can also swap the existing value with a new value.

    To better explain this utility, we have a sales sheet as an example. We’re looking to filter out all records with the “Complete” status.

    1. On your keyboard, hit Ctrl + H.
    2. Select the Options >> button.
      Find and replace Options Excel
    3. Next to Find what, we entered Complete. 
      Find and Replace Excel
    4. Leave the Replace with section empty. (If we were looking to swap “Completed” with another value, we’d enter our replacement value in this section and then click Replace All.)
    5. Click Find All.
    6. The tool will display all the cell items with the value in a pop-up below the window. Select the first item and scroll down to the last item.
      Find and select search results
    7. Hold down the Shift key and click on the last item.
    8. Close the Find and Replace Window.
      close find and replace window
    9. On your keyboard, use Ctrl + –. 
    10. Select Entire row > OK on the pop-up.
      Delete entire row

    Related Questions

    How to Find Duplicate Values

    You can use conditional formatting to locate duplicate cells in your Excel grid.

    1. Select your range from the grid.
    2. From the Home tab, select Conditional Formatting.
    3. Choose Highlight Cells Rule > Duplicate Values.
      Highlight duplicate values Excel
    4. On the pop-up, pick a format next to values with, then click OK.

    How to Delete Duplicate Values

    The Power Query is a powerful tool that could help you in filtering your data, including deleting empty rows and duplicate values. The simplest way to find and delete duplicate values is by using Power Query. However, Power Query is case-sensitive, meaning that it will not delete the same values in different cases (like Apple and apple).

    1. Select your range from your worksheet.
    2. Head to the Data tab and select From table in the Get & Transform section.
      Create new query from table Excel
    3. If you see the Create Table pop-up, click OK.
    4. Right-click on the column header and choose Remove Duplicates.
      Remove Duplicate Power Query'
    5. From the Home tab, click Close & Load.
      Close and Load Power Query
    Excel Basics
    Asmi

      Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).

      Add A Comment
      Table of Contents
      • Find & Select
      • Sort & Filter
      • Find and Replace Command
      • Related Questions
        • How to Find Duplicate Values
        • How to Delete Duplicate Values
      Recent Posts
      • How to Calculate Discount Percentage in Excel
      • How to Create a Progress Bar in Excel
      • What is VSTACK in Excel
      • How to Separate Dates in Excel
      • How to Lock a Cell in Excel Formula
      • Home
      • Privacy Policy
      © 2023 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.