Note: The other languages of the website are Google-translated. Back to English

Quickly select specific cells, entire rows or entire columns based on criteria in Excel

Normally if you need to select cells, entire rows or entire columns based on specific criteria in Excel, such as you want to select all cells, which are ending with "Km", you have to search cells one by one and select them manually. Kutools for Excel's Select Specific Cells utility can help quickly select cells, entire rows or entire columns based on one or two criteria.

Select cells, entire rows or entire columns based on one criterion
Select cells, entire rows or entire columns based on two criteria


Click Kutools > Select > Select Specific Cells. See screenshots:

arrow-big

Select Cells, Entire Rows Or Entire Columns Based On one Criterion

Suppose you have a school report as shown in the screenshot below, to find Nicol and his scores for all subjects, please do as follows.

1. Select the entire school report, and then click Kutools > Select > Select Specific Cells to enable this feature.

2. In the Select Specific Cells dialog box, please configure as follows.

2.1) The selected range is listed in the Select cells in this range box. You can change the range if you need;
2.2) In the Selection type section, there are three options (in this case, I choose the Entire rows option and check the Select entire row or column in the selection checkbox).
Cells: select this option, only cells in the selected range that match the criteria will be selected;
Entire rows: select this option, the entire row of cells that match the criteria will be selected;
Entire column: select this option, the entire column of cells that match the criteria will be selected.
Tips: If you want to select the entire row or column only in the selected range, check the Select entire row or column in the selection checkbox.
2.3) In the Specific type section, select Equals in the first drop-down list and enter a text “Nicol” into the text box (or you can click the dropper icon to extract the text from a cell). Make sure that the second-drop-down list shows the None option.
2.4) Click the OK button or Apply button.

3. Then a Kutools for Excel dialog box pops up to tell you how may cells were found and selected, click the OK button to close it.

Then Nicol and his scores for all subjects are selected in the school report as shown in the screenshot below.


Select Cells, Entire Rows Or Entire Columns Based On two Criteria

Suppose you have a product order list as shown in the screenshot below, to search and select the cell that starts with the text “KTW” and ends with the number “04” in the Order_ID column, this Select Specific Cells utility can also help to quickly handle it.

1. Select the Order_ID column range, and then apply this utility by clicking Kutools > Select > Select Specific Cells.

2. In the Select Specific Cells dialog box, please configure as follows.

2.1) The selected range is listed in the Select cells in this range box. You can change the range if you need;
2.2) In the Selection type section, there are three options (in this case, I choose the Cells option).
Cells: select this option, only cells in the selected range that match the criteria will be selected;
Entire rows: select this option, the entire row of cells that match the criteria will be selected;
Entire column: select this option, the entire column of cells that match the criteria will be selected.
Tips: If you want to select the entire row or column only in the selected range, check the Select entire row or column in the selection checkbox.
2.3) In the Specific type section, specify the criteria as follows.
In this example, we are going to select the cell that starts with the text “KTW” and ends with the number “04”.
In the first drop-down list, select the Begins with option, and then enter the text KTW into the text box or click the dropper icon to extract the value from a specific cell;
Select the And logic radio button;
In the second drop-down list, select the Ends with option, and then enter the number 04 into the text box or click the dropper icon to extract the value from a specific cell;
2.4) Click the OK button or Apply button

3. Then a prompt box pops out to tell you how many cells (rows or columns based on your section type) were found and selected, click the OK button.

You can see cells that match the specified criteria are selected.

Things to know for selecting date format cells based on date criteria:

To ensure a correct result, you must use the dropper icon to extract the date from a cell;
The date will be extracted and displayed as a serial number in the text box;
If you manually enter a date into the text box, no cell will be found.

Demo: Select cells, entire rows or entire columns based on criteria

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!


Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments (2)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Everything is nice, bar the group of functions related to selection of specific cells. This group of functions is virtually non-working and nearly always returns the "No cells qualify" message, whatever the criterion. I wonder why this is so.
Darius Kaunelis
This comment was minimized by the moderator on the site
Thanks ajay, but i will re put my question I have values from cell A1:A100 which is actually the hours reqired to fabricate certain items. In another cell C1 i have a fixed value say 200 whic is total hours the x number of people will work in day. Now in B2 i will make a formula =sum($A$1:A2) and i will copy this formula upto B100. Now i will check the results in the column B. The cell from from where the value is greater than the value in C1, i want the formula to change from that cell. For example if the value in B5 is greater than the value in C1 i want to change the formula as =sum($B$5:B6). In short cell B1:B5 have fulfilled the required hours in a day. So i want to calculate the required hours for the next day. So my question is how can i change my range of sum when the criteria is achieved..
Ajaz
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0  Characters
Suggested Locations