In previous post we learnt about Templates in MS Excel. In this post, we shall learn how to find, replace, select cells based on data, conditions, formulas etc.
To learn those, we will take an example of below data.
To find data in MS Excel, we can use following approach
Click on 'Find & Select' icon from the 'Editing' group on the Ribbon and select 'Find...'.
This opens up Find and Replace dialog box as shown below. Alternatively, we can also press CTRL+F to open up Find and Replace dialog box.
Enter the data you want to find in your current sheet and click 'Find Next' button. If the data is found, it selects that particular cell otherwise displays a message of not found.
: By default Excel is case insensitive that means that it doesn't check whether the text is written in upper case or lower case.
In case, we need more control over how Excel finds the data we are looking for then click on 'Options >>' button and it shows like this.
Here, we can select
Sometimes, we want to find a specific text into a specific formatted cells only; like only in header or footer etc.
To do this, click on 'Format...' dropdown button and select 'Choose Format From Cell...'.
Notice the mouse cursor that changes like below.
Now click on the cell whose format you want to choose to find the data into.
See that the 'Preview' button changes to the chosen cell format. Now write the data to find into the Text box and click Find Next button.
The data to find will be searched only to those cells that satisfies the chosen format.
To clear the find format on the Find and Replace dialog box, click on the Format... dropdown again and then select last option (Clear Find Format).
To find all occurence of data in Excel, we can use 'Find All' button.
All the matched cells references are listed at the bottom of the dialog box. Clicking on each occurence, selects that particular cells.
To hide the list of occurences, simply hold the bottom border of the dialog box with mouse and drag up. To show again, hold the bottom border of the dialog box again and drag down.
To find case sensitive data use 'Match case' checkbox. Checking this check box will find only those data that is exactly written in the 'Find what' textbox.
To match all the content of the cell (not partial content of the cell), check the 'Match entire cell contents' checkbox and then click on 'Find Next' button.
There are three ways to open Replace dialog box.
You may have noticed that almost all options of the Find are available here and they work exactly the same way.
The only addition in the Replace dialog box is 'Replace with:' textbox, Replace All and Replace buttons.
Let's try to replace 'FALSE' to '0' using Replace dialog box. Fill the data as it is written in below dialog box.
Now we have two options to replace data
To close this dialog box, simply press ESC key on the keyboard or press Close button.
'Go to' is used to go to a specific cell in the spreadsheet.
To open the Go to dialog box, choose 'Go To...' of the 'Find & Select' dropdown from Editing group on the Ribbon.
This opens up Go To dialog box that looks like below.
Write the Reference of the cell in the text box and click OK. You will be sent to that particular cell.
If the reference contains the range of cells, all those cells will be selected.
Clicking on Special button opens up 'Go To Special' dialog box.
Go To Special dialog box also helps us in finding a particular cells with specifics as shown in the picture below.
Try them yourself and learn. If any question, feel free to ask.
Okay, we have a way to find them. Select Formulas from the Find & Select dropdown on the Ribbon as shown below (Do not worry about Find & Select icon here, when you decrease the size of the Excel widow, the big icon & description goes and small icon appears to accomodate all the commands on the Ribbon).
When you click on 'Formulas', you will see all the cells having formulas gets selected. Notice the picture below (PPF field formula in this case is '=E4*0.15').
Same applies to other commands (Comments, Conditional Formatting etc.) of the Find & Select dropdowns.Views: 331 | Post Order: 9