The function that allows you to detect all cells that contain special data like comments, formula, space, and data validation in your Excel list is the Go To Special function.
With this formula, you can select the cells with formulas at once and encrypt them with a formula; select the blank cells at once and highlight them; select the cells that contain numbers, text, or conditional formatting all together and execute the action you need.
This function is located in the Find&Select button in the Editing group in Home tab.
You can also press F5 (you might need to press the fn key as well) and choose Go To Special in the window that pops up.
Explore the skills of the Go To Special window and facilitate your work!
Let’s think about a scenario where we will fill the blanks in the Satış Bölgesi (Sales Region) column with the correct data like shown above.
First, we need to select the blank cells and then fill in the correct the quickest way possible!
How can we fill these cells easily without having to type into each of them one-by-one?
We will execute this action by using two features together.
Action Steps
- First, select the column that has the blank cells.
- The F5 shortcut (You might need to press the fn key) –> Go To Special
- Select Blanks in the window that pops open and click OK.
Then, we get the look below
You will see that the selected cells are gray and the cell on top is white. You can write into this cell directly. Which means that you don’t have to click to cell to be able to write. The reason why we focus on this is because the areas you want to work on are already selected, so no mouse click is needed. When you click a selected area with the mouse, it cancels the selection so you should be careful about it.
- We will write =G2 (the address of the cell right above it) through the keyboard directly. This way we get to say that we will use the data above to Excel. The most important point: Yes, we will write the data above but how is this formula work in the whole column?
We move onto the fifth and last step for this action. - For the =G2 action to work in all the selected areas of the column, we should press the CTRL and ENTER keys at the same time. After these steps, the Sales Region column will be filled completely.
Other Go To Special Articles
Take a look at this article to detect and encrypt the cells with formula by using the Go To Special window. And take a look at this article to learn which action to take to convert Blank Cells to a fixed text.
