- Types of Data You Enter into Excel
- Entering Different Types of Data into a Cell
- Controlling the Next Cell Selection
- Using Copy, Cut, Paste, Paste Special to Enter Data
- Using Text to Columns to Separate Data in a Single Column
- Inserting Symbols and Equations into a Cell
- Using Web Queries to Get Data onto a Sheet
- Using Series to Quickly Fill a Range
- Editing Data
- Working with Tables
- Fixing Numbers Stored as Text
- Spellchecking Your Sheet
- Finding Data on Your Sheet
- Using Data Validation to Limit Data Entry in a Cell
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Finding Data on Your Sheet
If you press Ctrl+F or go to Home, Editing, Find & Select and select Find from the drop-down, the Find and Replace dialog box opens. Through this dialog box, you can find data anywhere on the sheet or in the workbook. Click on the Replace tab and you can quickly replace the found data.
Click the Options button and the Find dialog box opens up, showing several options to aid in your search, as shown in Figure 3.20:
)
Figure 3.20. Click the Options button to open up the full search potential of the Find and Replace dialog box.
- Within—You can search just the active Sheet or the entire Workbook. You can also narrow Excel’s search by selecting the range before bringing up the dialog box.
- Search—To have the search go down all the rows of one column before going on to the next column, set this to By Rows. To have the search go across all columns in a row before going on to the next row, select By Columns.
- Look In—By default, Excel looks in Formulas, that is, the true value of the data in a cell. When you’ve applied formulas or formatting to a sheet, what you see in a cell might not be what is actually in the cell. Look at Figure 3.21. The value 22.81 is obviously at the top of the column, but a search in Excel does not find it because the value in the cell was calculated. To search for the value, change the drop-down to Values. You can also choose to search in Comments.
Figure 3.21. If Excel can’t find the number you know is on the sheet, check your settings. If set to Formulas, change to Values and vice versa.
)
CAUTION
The settings in the Find and Replace dialog box are stored throughout an Excel session. This means that if you change them in the morning for a search and then try a search again later in the afternoon without having closed Excel at all during the day, the settings changes you’ve made are still active, even if you’re searching a different workbook.
Performing a Wildcard Search
What if you don’t know the exact text you’re looking for? For example, you’re doing a search for Jon Smith but don’t know if Jon was entered correctly. To do a wildcard search, you can use an asterisk (*) to tell Excel there might or might not be additional characters between the n, like this: Jo*n Smith. In this case, Excel would return John Smith, Jon Smith, and Jonathan Smith.
If you have a list of part numbers and can remember all but one of the characters, you can use a question mark (?) to replace the unknown character. Use a ? for each unknown. So if you aren’t sure of the first and last characters, do this: ?482?. This tells Excel that there is definitely one character in each of those positions.
If you need to include a * or ? as part of your search—not as a wildcard, but as actually part of the search text, then precede the symbol with a tilde (~). Doing this tells Excel that the * or ? is not a wildcard character but an actual text character to use in the search.