Hi all. Today’s post I want to devote to ways to select cells in Microsoft Excel. If you already know how to highlight cells, skim through the text; perhaps you are not aware of some of the program’s features and you are not taking advantage of them.
And so, selecting groups of cells is necessary in order to perform some common action with them: or , etc. How can you select cells in Excel? Let's figure it out!
How to select all cells in an Excel worksheet
I will immediately answer the most asked question on the topic of the article. How to select all the cells of a sheet at once? I offer you two methods, whichever you like best, use the one:
Selecting a rectangular range of cells
Let's say you need to select a rectangular group of cells to format them for your data type. I can offer 5 ways of highlighting, and you choose which one is appropriate for you at the moment:
- Mouse selection. The most common way is to hold the left mouse button inside one of the cells (not on the frame) and stretch the selection in the desired direction along the rows and columns
- By pressing the key Shift . Place the cursor in one of the corner cells of the future selection. Hold Shift and select the area with the arrow keys. When you're done, release Shift.
- Press the key F8. The command is similar to the previous one, but the key does not need to be pressed. Place the cursor in one of the corner cells of the array, press F8 to enable selection mode. Use the arrow keys to expand the selection, or click the mouse in the opposite corner of the future selection. When the array is selected, press F8 again to exit this mode.
Another way is to place the cursor in the upper left cell of the range, hold Shift and click on the lower right cell (see figure)
- Enter in the "Name" field cell address or range of cells (separated by colon) and press Enter
- Run the command(on the keyboard – F5, or Ctrl+G). In the window that opens, in the “Link” field, enter the address of the cell or range of cells separated by a colon. Click OK
Selecting using the "Go" command Select rows and columns completely
If you need to select an entire column or row, here are a few options:
- Click the mouse on the row number or column name. If you need to select several adjacent lines, hold down the left mouse button on the line number and drag the selection over the lines you want to select. We do the same with columns
- Place the cursor in any cell of the row that you want to select and press combination Shift+Space. To select a column, use the combination Ctrl+Space
- To select non-adjacent rows and columns − pinchCtrl and click by column names and row numbers. Once completed, release Ctrl.
Selecting non-adjacent ranges
If you need to select several cells that are not adjacent, do it using one of the suggested methods:
Selecting on multiple sheets at once
If the worksheet of the same document contains the same tables with different data, we can perform operations on all tabs at the same time. This saves time and reduces the chance of error. For example, we need the headers of each table on several sheets. There is no need to do this on each tab separately - select all the sheets and do everything in one go.
To select the same area on multiple sheets, first select the sheets you want. Activate the first sheet from the list, hold Ctrl and click on the labels of all sheets to select it.
When all the necessary sheets are selected, you can perform operations. Please note that in the name line next to the file name the inscription “ [Group]" This means that Excel is ready to process a group of sheets.
Changing the name when selecting a group of sheets Next, on the active sheet, select the required ranges, make changes, fill out general data and formulas. Everything you do will be applied to all selected sheets. After completing batch processing, do not forget to deselect the sheets. To do this, right-click on any label of the grouped sheet, in context menu select Ungroup.
Conditional cell selection
Microsoft Excel can highlight a group of cells depending on their contents. Not all users are aware of this feature, although its use can be very useful.
Run the command Home – Editing – Find and Select – Go. In the window that appears, click Select.... The Select Group of Cells dialog box appears with the following selection options:
Selecting cells by their contents Selecting cells using the search box
To open the value search window, run Home – Editing – Find and Select – Find(or press the key combination Ctrl+F). Find All appears. A list of cells containing the selected data will appear at the bottom of the window. Select one or more cells (holding Ctrl) in the list so that Excel selects them. To select all found cells, select one of them and press the combination Ctrl+A.
To search you can use Special symbols:
- "?" - any one symbol
- “*” - any number of any characters
For example, to find all cells that begin with the letter “A”, enter “A*” in the search.
That's all about selecting cells in MS Excel, and I want to devote the next post to . As always, this article will contain many useful things that will definitely come in handy in your work. So read it, you won’t regret it!
By the way, I look forward to your questions regarding this article in the comments!
Need to highlight duplicate values in a column? Do I need to select the first 5 maximum cells? Do you need to make a thermal scale for clarity (the color changes depending on the increase/decrease in the cell value)? In Excel, highlighting cells by color can be done very quickly and easily. The special “Conditional Formatting” function is responsible for highlighting cells with color. Highly recommend! Read on for more details:
I described the main features at the beginning of the article, but in fact there are a lot of them. Read more about the most useful
To begin, on the task ribbon in the main menu, find the Styles section and click on the Conditional Formatting button.
When clicked, a menu will open with different options for this editing. As you can see, there are really a lot of opportunities here.
Now more about the most useful ones:
Excel highlighting cells by color according to conditions. Simple conditions
To do this, go to Cell selection rules. If, for example, you need to select all cells greater than 100, click the More button. In the window:
By default, the conditions are suggested to be highlighted in red, but you can set the desired cell formatting by clicking in the right window and selecting the required option.
Highlighting duplicate values, incl. across multiple columns
To select all duplicate values, select the corresponding Duplicate Values menu.
What should you do if you need to find repetitions in two or more columns, for example when your full name is in different columns? Make another column and combine the values with the formula =, i.e. in a separate cell you will have IvanovIvanIvanovich written. Using this column, you can easily identify duplicate values. It is important to understand that if the word order is different, Excel will consider such rows to be non-repeating (for example, IvanIvanovichIvanov).
Highlighting the first/last values in color. Again conditional formatting
To do this, go to the Rules for selecting the first and last cells section and select the desired item. In addition to the fact that you can highlight the first/last values (including by percentage), you can use the ability to highlight data above and below the average (I use it even more often). Very convenient for viewing results that differ from the norm or average!
Building a thermal diagram and histogram
A cool feature for data visualization is the thermal/temperature chart. The bottom line is that, depending on the value in a column or row, the cell is highlighted with a certain shade of color, the more, the redder, for example. Tables are perceived much better by eye, and decision making becomes easier. After all, one of the best analyzers is often our eye, and therefore our brain, and not a machine!
The histogram in the cell (blue in the figure below) is also extremely useful feature, to detect changes in values and compare them.
Highlight cells containing specific text
Very often you need to find cells that contain a certain set of characters, you can, of course, use the = function, but it’s easier and faster to apply conditional formatting, go through - Rules for selecting cells - Text contains
Very useful when working with text. An example when you have the full names of employees written in a column, but you need to select all the Ivanovs’ colleagues. Select the cells, go to the desired item and select the text containing the text Ivanov, then filter the table by color
Excel color highlighting. Filter by color
In addition to the above options, you can filter selected cells by color using a regular filter. To my surprise, very few people know about this - apparently echoes of the 2003 version - this feature was not there.
Checking formatting conditions
To check what conditional formatting you have set, go to Home - Conditional Formatting - Manage Rules. Here you can edit the already specified conditions, the range of application, and also select the priority of the specified formatting (whoever is higher is more important, you can change it using the arrow buttons).
Invalid conditional formatting range
Important! Conditional formatting, when used incorrectly, is often the cause of strong . Double formatting occurs, for example, if you copy cells with color highlighting many times. Then you will have many conditions with color. I myself saw more than 3 thousand conditions - the file slowed down horribly. Also, the file may slow down when a range is specified as in the picture above; it is better to specify A:A for the entire range.
Read more about Excel brakes and their causes. This article has helped more than one hundred people;)
I hope it was helpful, I don’t say goodbye!
Share our article on your social networks:Conditional formatting in Excel allows you to select not only a cell, but also the entire row at once.To select an entire row in a large table at once, you need to set our table formatting conditions in the table.What is conditional formatting, read the article “ Conditional Formatting in Excel."
We have a table with visitor data. We need to highlight visitors who have completed a course of study, treatment, work, etc. The table is like this.
We create a small auxiliary table in separate cells. For example, let's make a table in cells G2, G3 and G4. 
If we write in the “Status” column - “Completed” or “1st stage”, “2nd stage”, the entire line will be colored.
Instead of an auxiliary table, you can make a “drop-down list” in an auxiliary cell.
Now we set conditional formatting to the table cells.
Select the entire table with data and additionally at the bottom of the table another empty line. We will copy an empty table row if we need to add rows to the table. The formatting conditions will also be copied immediately.
So, we have selected the range table A2:E7.
On the “Home” tab, click the “Conditional Formatting” button and select the “Create Rules” function.
Click on the line “Compare table columns to determine formatted cells.”In the "Format" line we write the formula. =$E2=$G$2
note– the reference to cell E2 is mixed. 
Press the button with “0”. Here we select and set the fill color of the line and the font color of this line. We chose green color filling cells.Click “OK” in all three dialog boxes. All.
Now we write in the table in the “Status” column - “Completed” and our line turns green, which we set in the conditional formatting rules.
Attention! In the cells of the “Status” column, write words in the same way as they are written in the auxiliary table. For example, we have the word “Completed” written with a capital letter. If we write the word “completed” in a column cell with a small letter, then the conditional formatting will not work. Therefore, it is better to set a drop-down list in the “Status” column.How to install a drop-down list, see the article " Dropdown list in Excel." It turned out like this.
In order to correctly place accents in a document made in MS Excel, you can increase the size of the cells, choose a different font or size of letters, or you can paint over the squares that you definitely need to pay attention to.
Now we will figure out how to highlight cells with color in Excel, or change the color of those that are already filled in. How to make a cell change color according to a given condition, depending on the value entered in it, and how to work with the created rules.
Simple block filling
Coloring one or more blocks in Excel is not difficult. First, select them and on the Home tab, click on the arrow next to the paint bucket to expand the list. Select a suitable color from there, and if nothing suits, click "Other colors".
By the way, you can fill in an entire line this way, just first click on its number to highlight it. You can read a separate article about selecting cells in Excel.
If you are working with a table in which something has already been painted over, then you can change the color of the blocks or remove it altogether like this. Click on it and from the colors or select a new one, or click on the option “No”.
Depending on the entered data
Now let's look at how to make a cell change color based on a given condition. Conditional formatting is used for this, about which there is a separate article on the site.
Text
Let's take the following table as an example. Let's make it so that red corresponds to an apple, yellow to a pear, and orange to an orange.
We select the data we will work with, in my case these are the names of fruits. Then click "Conditional Formatting", which we will continue to use continuously. From the list, click on "Create Rule".
This window opens. Select the type at the top - "Format only cells that contain", we will also celebrate it further. Below we indicate the conditions: we have a text that contains certain words. In the last field, either click on the button and select a cell, or enter text.
The difference is that by placing a link to a cell (=$B$4), the condition will change depending on what is typed in it. For example, instead of an apple in B4 I indicate currants, the rule will change accordingly, and blocks with the same text will be filled in. And if you enter apple in the field, then this particular word will be searched, and it will not depend on anything.
Here, select a fill color and click OK. To view all options, click on the “Others” button.
The rule has been created and we save it by clicking the “OK” button.
As a result, all blocks that contained the specified text were painted red.
Rules are also created for other fruits; only a different filling option is selected.
Numerical
Now let's look at the numerical data. Let's place the numbers in column D on a certain background according to the condition that we set.
Select the column, create a rule, and indicate its type. Next we write - “Value” “greater than” “15”. You can either enter the last number manually, or specify the address of the cell from which the data will be taken. Decide on the fill, click “OK”.
Blocks containing numbers greater than the selected one are colored in.
Let's specify more rules for the selected cells - select "Rule Management".
Here, select everything as I described above, you just need to change the color and set the condition "less or equal".
When everything is ready, click "Apply" and "OK".
Everything works, the values are equal and below 15 are painted pale blue.
Here the difference between imported and sold goods would be clearly visible. Select two columns and click "Rules for highlighting cells" – "Duplicate values". Choose a suitable color. After this, if two cells next to each other are colored, it means that the fruits that were delivered were sold.
Let's use the last column as an example to show how to make a cell change color depending on the specified value. Select them and go to "Rule Management".
We create a new one for the current fragment; if necessary, in this drop-down list you can select either for the entire sheet or for other sheets.
Select the required items in the window that opens. I will fill all values that are greater than 90 with dark green. Since I indicated the address in the last field (=$F$15), then if the number 90 in the cell changes, for example, to 110, the rule will also change. Save the changes by clicking on the “OK” button.
I’ll create another rule, but in it I’ll highlight in light green everything that is less than or equal to 90. Don’t forget to save everything by clicking on the buttons at the bottom right.
This is what happened. From the last column, you can visually quickly determine the profit from the sale of which product was greater.
For example, if the price changes, the rules will also be revised. If the value becomes greater or less than the specified value, the cell color will automatically change.
I think you noticed that the rule is created not only for text and numbers, but also for dates, and depending on the filling and presence of errors in the cells. Specify the condition, choose a color and save.
To see what you added, select the range and in the window "Rule Management" will full list. Using the buttons at the top you can add, change or delete them.
Hello, dear readers. Have you ever worked with huge data in a table? You know, it will be much more convenient to work with them if you know how to select several Excel cells different colors under certain conditions. Would you like to know how it's done? In this tutorial we will make the cell color change depending on the Excel value, and also color all the cells using search.
The fill color changes with the value
As an example, we will practice making a cell change color in a given table under a certain condition. Yes, not one, but all with a value in the range from 60 to 90. To do this, we will use the “Conditional Formatting” function.
First, select the data range that we will format.
Next, on the “Home” tab, find the “Conditional Formatting” button and select “Create Rule” from the list. 
The “Create formatting rules” window has opened. In this window, select the type of rule: “Format only cells that contain.”
Next, go to the “Change the rule description” section, where you need to specify the conditions under which the filling will be performed. In this section you can set a variety of conditions under which it will change.
In our case, it is necessary to put the following: “cell values” and “between”. We also indicate a range that if the value is from 60 to 90, a fill will be applied. Look at the screenshot how I did it. 
Of course, when working with your table, you may need to fill in completely different conditions, which you will indicate, but now we are just training.
If you have filled it out, do not rush to click on the “OK” button. First you need to click on the “Format” button, as in the screenshot, and go to the fill settings.
Okay, as you can see, the “Format Cell” window has opened. Here you need to go to the “Fill” tab, where you select the one you need, and click on “OK” in this window and in the previous one. I chose a green fill. 
Look at your result. I think you succeeded. I definitely succeeded. Take a look at the screenshot: 
Let's color a cell a certain color if it is equal to something
Let's return to our table in its original form. And now we will change the color where the number 40 contains to red, and where the number 50 is contained to yellow. Of course, you can use the first method for this matter, but we want to know more Excel capabilities.
This time we will use the Find and Replace function.
Select the section of the table in which we will make changes. If this is the entire sheet, then there is no point in selecting it.
Now it's time to open the search window. On the “Home” tab, in the “Editing” section, click on the “Find and Select” button. 
You can also use hotkeys: CTRL + F
In the “Find” field we indicate what we are looking for. In this case, we write “40”, and then click the “Find all” button.
Now that the search results have been shown below, select one of them and press CTRL + A to select them all at once. And then click on “Close” to remove the “Find and Replace” window. 
When we have everything containing the number 40 selected, on the “Home” tab in the “Font” section, select the color of the cell. Ours is red. And, as you see on your screen and in my screenshot, they turned red. 
Now the same steps need to be followed to color those where the number 50 is indicated. I think now you understand how to do this.
Did you succeed? And look what happened to me. 
That's all. Thanks friends. Subscribe, comment, join the group, share on social networks and stay up to date with new articles. And also, do not forget to study other articles on this site.
