Practical Excel skills sharing: search, positioning functions, entry skills

In the previous article "Practical Excel Skills Sharing: Four Methods for Summing across Worksheets", we learned four methods for summing across worksheets. Today we are going to talk about 3 commonly used skills in excel tables: search, positioning functions, and entry skills. Come and collect them now!

#I believe everyone uses the shortcut keys in Excel. Some of them are also very familiar, such as Ctrl C is to copy, Ctrl V is to paste, etc. Proficiently mastering some shortcut keys not only demonstrates your Excel skills, but also greatly improves work efficiency. Today we will learn some more powerful functions of shortcut keys. Spend five minutes and save a whole day.

1. Ctrl F

We are more accustomed to pressing Ctrl F to open the search window, enter the search characters, and then find the content you want. If you only know these, what you are using is only at the entry level. There are also many advanced Excel skills that can greatly improve work efficiency. The following examples can basically be solved by other methods. But the author personally thinks that using Ctrl F is more convenient.

1. Filter multiple columns by interval

As shown below, we want to filter units with performance >80 and fill them with green background.

##1.1 Select the data area A1:B13, press and hold "Ctrl F", a search pop-up window will pop up;

1.2 In the search content, enter "* ", * represents any character, click to search all;

1.3 Click the title "value" to sort, now it is sorted from small to large

1.4 Select the first row greater than 80, hold down shift and select the last row greater than 80

1.5 Click the close button to see Cells greater than 80 in the table have been selected.

Click Start-Background Fill to fill it with a green background

2. Change the cell's Background color

The replacement function of Ctrl F can not only replace text, but also find the background color of the cell and replace it. For example, we now want to replace the green background in the table obtained above with a red background.

2.1 Select the table, hold down "Ctrl F", a search pop-up window will pop up, select the "Replace" option, and click "Options";

2.2 In the new pop-up window, click the "Format" button behind the search content and select "Background Color". A straw will appear. Move it to the green cell and absorb the green background color. ;

2.3 Click the "Format" button behind Replace and select "Set Format";

##2.4 In In the formatting pop-up window, click "Pattern", select red, then click OK, click "Replace All";

3. Delete the content in brackets

As shown below, we now need to remove the brackets and the content within the brackets in the performance. This can also be done with Ctrl F.

3.1 Select the area to be replaced, hold down "Ctrl F", a search pop-up window will pop up, click "Replace"

3.2 Fill in "(*)" in "Find content", fill in nothing in "Replace with", click "Find All" to see if all the content you want to replace, then click "Replace All" That’s it;

2. Ctrl G

Ctrl G is mainly the Excel positioning function. How to use Excel positioning conditions? Press and hold "Ctrl G" and the following pop-up window will pop up (the author uses wps, which may be different from excel). All the tips today are related to the pop-up window below.

1. Check the text numbers

If you use the SUM formula to sum up the results and the manual results are different, it is most likely because the cell format of some numbers in the table is text, so the SUM formula is used when calculating Automatically ignored. At this time, use Ctrl G to make these text numbers invisible.

Select the positioning area-"Ctrl G"-check "Constant"-check "Text", click positioning, and you can quickly view text-type numbers.

2. Only copy visible rows

In the table, if there are hidden rows, they will also be copied when copying . But we don't need the contents of these hidden rows. Use Ctrl+G to copy only the visible lines.

Select the positioning area - "Ctrl G" - check "Visible cells" - click "Position", then pressing "Ctrl C" will only copy the visible rows.

3. Delete all pictures and buttons in the worksheet

Forms downloaded from the Internet often have some small pictures And small buttons, we can use Ctrl G to delete them with one click.

"Ctrl G"-check "Object"-click "Locate". At this time, you can select all buttons and pictures and press delete to delete them.

3. Some tips on Sogou input method

I encountered some before at work I have input problems, such as how to type unknown words, etc., so I checked some tips on the Internet, and now I will share them with you, hoping to help everyone.

1. How to type special symbols such as ✔ and ·

Type "dot" directly, and the fifth one is the special symbol ·. Of course, you can also type right, wrong, ellipses, RMB, etc.;

2. Type unknown words

When you encounter When you find a character you don't recognize, use U mode to type that character. For example, "㙓", "u" followed by "tututu" can be typed;

3. Number case switching

For accountants, switching between upper and lower case numbers is a frequently used function. Sogou's V mode can quickly implement this function. "v" followed by "123456789" will directly appear in capital letters;

4. Quickly enter time

Enter "rq" ( The first letter of the date), dates in various formats appear

Enter "sj" (the first letter of the time), the time appears directly

Enter "xq" (the first letter of the week), and the day of the week will appear directly

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: search, positioning functions, entry skills. For more information, please follow other related articles on the PHP Chinese website!