Have you ever needed to delete all instances of a word or phrase in Microsoft Excel without replacing that word/phrase with something else?
Recently, one of our past students used our Ask-an-Instructor Forum to ask us how to go about doing this in Excel 2010. This client maintains a work schedule for a number of staff in her office. The basic schedule covers the entire span of a year (January to December), with changes being made here and there throughout. One of the staff members moved on into a different role and so our student no longer needed to schedule this particular person. She was looking for a way to easily remove this staff member using Find and Replace.
While it is not exactly made very obvious, in the end the solution is very simple:
Excel Vocabulary: “Data” entered into Excel can be text data (a “text string” such as a word or a phrase) or number data (a number).
Data is stored in a “cell” (the rectangular box that is the intersection point of a column and a row).
When filtering data, the term “value” is used to describe the condition that data must meet in order to either remain in the data table or be filtered out.
1. Click on the Home Tab.
2. Locate the “Find and Select” button in the Editing group (far right).
Click on the button to activate the drop-down menu. Select “Find” (binoculars icon):
4. If the arrows on the Options button are pointing to the left (<<), skip ahead to step 5. If the arrows on the Options button are pointing to the right (>>), click the Options button.
5. Click Find All. Depending on how many instances of the data value there are in your spreadsheet, you may not be able to see them all immediately. To solve this, look at the bottom right corner of the dialogue box. Move your mouse over the corner until it turns into a two-headed arrow on a diagonal that looks like this:
Click and drag to expand the box until you can see all instances (cells) found:
6. Select all of the cells listed. You can do this by selecting one of the cells and then pressing Ctrl + A. Alternatively, you can click the first cell in the list, press Shift, and then click the last cell in the list:
7. Close the Find and Replace dialogue box. You should now have every cell populated with the data value you want to delete selected:
8. Press Delete. Voila! You have now successfully deleted all instances of the data value you wanted to remove.
One thing to keep in mind is that although all the steps listed above are for Microsoft Excel 2010, they also apply to Microsoft Excel 2007.
Don’t forget! If you have taken any classes with us in the past, take advantage of your unlimited access to our Ask-An-Instructor Forum. We’re all here to help! We will do our best to address your question within 2-3 business days.