Many friends will encounter this situation at work. Even though they are already very proficient in operating Excel, their work is still very slow. This is because there are many little tricks that you don’t know. The following article will introduce to you some commonly used skills in excel tables that can improve work efficiency. They are simple and easy to use. Hurry and collect them!
#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 desired content. 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, hold down "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 the unknown characters
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. Switch the case of numbers
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 be capitalized directly;
4. Quickly enter the 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: how to search, locate functions, and enter. For more information, please follow other related articles on the PHP Chinese website!