Excel2016 How to use the F5 positioning function

quickly clear blank rows

Take the following table, there are many blank columns with no data in the table, which is very bad for us to deal with the data, we need to clean them out quickly, how to do?

We first ask F5 to come forward, press it, the screen will appear positioning dialog box. Then click the "Locate Condition" button.

In the select list, we select "Null value" and click "OK" button.

Next, we see that all the spaces in the table are selected. Right-click on the selection and select Delete to select Entire column in the pop-up selection dialog box and ok so that all blank columns are eliminated. Of course, if you want to clean out the empty line, then do a select "whole line" operation is OK.

I know what the formula is for people.

We often use tables that are made by others, and some of the cells in those tables may use some kind of formula or function. So, if you want to learn the format of the formula, how can we quickly find out which cells in the big table use the formula? Let's ask F5 to help solve it again.

Open a table that might contain a formula, press F5, then click on "Locate conditions", select "Formula" in the Positioning dialog box, and the following 4 subkeys are selected to display formulas for numbers, text, logical values, error judgments, and so on, and click OK.

After that, you can see that all the cells in the table that use the formula are shown in a dark background. If you want to see the formula in which cell, click the cell, the formula appears in the Top Public column. This trick is particularly useful for the "Amoy" formula from a large table.