Little-known Excel skills

 

Little-known Excel skills

I don't know if you are patient .. Haha

 

Maybe you have already completed hundreds of financial statements in Excel. Maybe you have used the EXCEL function to perform thousands of complex operations. Maybe you think that excel is just the same, or even nothing new. However, we repeatedly tried to use Excel 1% of all the skills on a daily basis. This topic starts with some little-known skills in Excel to learn about the different styles of Excel.

1. display different types of data in different colors
In the payroll, if you want to display the total wages of more than or equal to 2000 yuan in red, the total wages of more than or equal to 1500 Yuan will be displayed in blue, the total salary of less than 1000 yuan is displayed as "brown", and the others are displayed as "black". We can set it like this.
1. Open the workbook "payroll", select the column where "total salary" is located, and run the "format → condition format" command to open the "condition format" dialog box. Click the drop-down button on the right of the second box, select the "greater than or equal to" option, and enter the value "2000" in the box that follows ". Click "format" to open the "cell format" dialog box and set "color" of "font" to "red ".
2. Click "add" and set other conditions (greater than or equal to 1500, the font is set to "blue", less than 1000, and the font is set to "brown ").
3. After the settings are complete, click OK.
Look at the payroll. Is the total salary data displayed in different colors as required.

2. Create a category drop-down list filling item
We often need to enter the company name into the table. To maintain name consistency, a category drop-down list fill item is created using the "Data Validity" function.
1. in sheet2, enter an enterprise name database in different columns by type (for example, "Industrial Enterprise", "commercial enterprise", and "individual enterprise.
2. Select column A ("Industrial Enterprise" name column), enter the "Industrial Enterprise" character in the "name" column, and press "enter" to confirm.
Follow the operation above to convert B, c ...... The column names are respectively "commercial enterprise" and "individual enterprise "......
3. Switch to sheet1, select the column (for example, column C) that requires entering the "Enterprise category", run the "Data → validity" command, and open the "Data Validity" dialog box. In the "Settings" tab, click the drop-down button on the right of "allow", select the "sequence" option, and enter "Industrial Enterprise" in the "Source" box below ", "commercial enterprise", "individual enterprise "...... To exit.
Select the column (such as column D) for which you want to enter the enterprise name. Then, open the "Data Validity" dialog box, select the "sequence" option, and enter the formula in the "Source" box: = indirect (C1). Confirm to exit.
4. Select any cell (such as C4) in Column C, click the drop-down button on the right, and select the corresponding "Enterprise category" to fill in the cell. Select the cell in column D (such as D4) corresponding to the cell, and click the drop-down button to enter the desired enterprise name from the list of enterprise names of the corresponding category.
Tip: if you do not need to print the "Enterprise category" column when printing a report in the future, you can select this column, right-click the column, and select the "hide" option to hide the column.

3. Create a new menu for common documents
Create a new "frequently-used documents" menu on the menu bar and add frequently-used workbook documents to it for convenient call at any time.
1. Right-click the blank area in the toolbar and select the "Custom" option to open the "Custom" dialog box. In the "command" tab, select the "new menu" under "category", and drag the "new menu" under "command" to the menu bar.
Click "Change selected content" and enter a name (for example, "Common Document") in the "name" box in the pop-up menu ").
2. select one item under "category" (for example, "insert" option) and one item under "command" on the right (for example, "HYPERLINK" option ), drag it to the new menu (frequently used documents) and name it (such as "payroll") Based on the above operations to create the name of the first workbook document list.
Repeat the preceding operation to add more document list names.
3. select a menu item (for example, "payroll") in the "frequently used documents" menu, right-click the item, and select "allocate hyperlink> open" in the shortcut menu, open the "allocate HYPERLINK" dialog box. Find the category owner's right-side drop-down button, locate the corresponding work book (such as payroll .xls) folder, and select the workbook document.
Repeat the preceding operation to hyperlink the menu item and its corresponding workbook document.
4. When you need to open a workbook document in the "frequently used documents" menu, expand the "frequently used documents" menu and click the corresponding options.
Tip: even though we drag the "HYPERLINK" option to the "frequently used documents" menu, however, this does not affect the "HYPERLINK" menu items in the "insert" menu and the "insert HYPERLINK" button on the "common" toolbar.

4. Create a "professional symbol" Toolbar
When editing a professional table, you often need to enter some special professional symbols. To facilitate the input, we can create a "professional symbol" Toolbar of our own.
1. Run the "Tools> macros> recording new macros" command to open the "Recording new macros" dialog box and enter the macro name? For example, "fuhao1 "? Save the macro to "personal macro Workbook" and "OK" to start recording. Select the "relative reference" button on the "Recording macro" toolbar, enter the required special symbols into a cell, and click "stop" on the "Recording macro" toolbar, complete macro recording.
Record the input "macro" of other special symbols one by one based on the above operations ".
2. open the "Custom" dialog box and click "new" in the "toolbar" tab. In the "new toolbar" dialog box that appears, enter the name-"professional symbol" and click "OK, a toolbar appears in the workspace.
Switch to the "command" tab and select "macro" under "category ", drag the "Custom button" item under "command" to the "professional symbols" Column (the number of special symbols is counted as many buttons ).
3. select one of the "Custom buttons" and name them based on the 2nd points of the 1st tips.
4. right-click a named macro. In the shortcut menu that will pop up later, select the option, open the dialog box, and select the corresponding macro (such as fuhao1 ), are you sure you want to exit.
Repeat this step to link the button with the corresponding macro.
5. Close the "Custom" dialog box. You can use the "professional symbols" toolbar to quickly enter the professional symbols in the cell, just like using the common toolbar.
　　

5. Use the "view Manager" to save multiple print pages
For some worksheets, you often need to print different areas. Use the "view Manager.
1. open the worksheet to be printed, drag the rows (or columns) that do not need to be printed with the mouse, select them, right-click them, and in the shortcut menu that appears later, select "hide" to hide the rows (or columns) that do not need to be printed.
2. run the "View> View manager" command to open the "view Manager" dialog box and click the "add" button. The "add view" dialog box is displayed, enter a name (such as "Report") and click "OK.
3. display the hidden rows (or columns) and repeat the preceding operations to "add" other printed faces.
4. when you need to print a table in the future, open the "view Manager", select the name of the table to be printed, and click the "show" button. The worksheet is immediately displayed on the preset page, in simple settings and layout, press the "print" button on the toolbar and everything will be OK.
　　

6. Sort data on demand
If you want to sort employees by their departments, what should you do? Custom sequence can be used for sorting.
1. run the "format → options" command to open the "options" dialog box and enter the "Custom sequence" label, in the box below the "input sequence", enter the sequence of departments (for example, "agency, fleet, one workshop, two workshops, three workshops ), click Add and OK to exit.
2. select any cell in the "department" column, execute the "data> sort" command, open the "sort" dialog box, and click the "options" button to bring up the "Sort options" dialog box, select the custom sequence from the drop-down list and press the "OK" button twice to return the result. All data is sorted as required.
Error! Unrecognized switch parameter.

7. completely hide data
The content in some cells of the worksheet is hidden instead of being viewed by the viewer.
1. select the cell (region) for which you want to hide the content, execute the "format → cell" command, and open the "cell format" dialog box, select the "Custom" option under "category" of the "Number" label, and enter ";" in the box below "type" on the right; "(semicolon in three English states ).
2. Switch to the "protection" tab, select the "hide" option, and press "OK" to exit.
3. Run the "Tools> protect worksheet" command to open the "protect worksheet" dialog box. After setting the password, click "OK" and return.
After such settings, the content in the above cells is no longer displayed, that is, the Excel transparency function cannot be used to make it visible.
Tip: under the "Protect" label, do not clear the "unlock" number in the check box before "Lock" to prevent others from deleting your hidden data.
　　

8. enable intelligent Chinese and English input methods
When editing a table, some cells must enter English and some cells must enter Chinese. It is inconvenient to switch the input method over and over again. Why not set it so that the input method can be adjusted intelligently?
Select the cell area where you want to enter Chinese characters, execute the "Data → validity" command, open the "Data Validity" dialog box, switch to the "Input Method" tab, and press the drop-down button on the right of "mode, select the "open" option and click "OK" to exit.
When any cell in the cell area that needs to enter Chinese characters is selected in the future, the Chinese Input Method (1st Chinese Input Methods in the input method list) is automatically enabled. When other cells are selected, the Chinese Input Method is automatically disabled.
　

9. Allow "automatic correction" to input Unified Text
Do you often worry about entering some fixed texts, such as the Computer newspaper? Let's look at it.
1. Run the "Tools> automatic correction" command to open the "Automatic correction" dialog box.
2. enter "pcw" in the box below "replace" (or other characters, and "pcw" is in lower case ), enter "Computer report" in the box below "Replace with", and then click "add" and "OK.
3. If you need to enter the above text in the future, you only need to enter the "pcw" character? What is the case sensitivity of "pcw ?, Then confirm it.
　　

10. Customize functions in Excel
Although Excel functions are rich, they cannot meet all our needs. We can customize a function to complete some specific operations. Next, we will define a function to calculate the trapezoid area:
1. Run the "Tools> macro> Visual Basic Editor" menu command (or press the "Alt + F11" shortcut) to open the Visual Basic editing window.
2. In the window, run the "Insert> module" menu command to insert a new module, Module 1.
3. Enter the following code in the "code window" on the right:
Function V (a, B, h) V = H * (A + B)/2end Function
4. Close the window and complete the UDF.
You can use udfs like built-in functions in the future.
Tip: udfs defined in the preceding method can only be used in the corresponding workbook.
　　

11. lined images under the header
The background added for the worksheet is lined under the whole worksheet. Can it be lined only under the header?
1. run the "format → worksheet → background" command to open the "worksheet background" dialog box. Select the image to be used as the background, and press the "insert" button to contrast the image to the whole worksheet.
2. Press and hold the ctrl key, drag the cell (area) that does not need to be lined with images, and select these cells (areas ).
3. Press the drop-down button on the Right of fill color on the "format" toolbar and select "white" in the "color palette" that appears later ". After such settings, the left cell is lined with an image, and the selected cell (area) is not lined with an image (in fact, the image is "white ).
Prompt? Images lined with cells do not support printing.

12. merge text with the hyphen "&"
If you want to merge the content of multiple columns into one column, you do not need to use a function, A small hyphen "&" can be used to get it done (Here we assume that columns B, C, and D are merged into a column ).
1. Insert two empty columns (column E and column F) behind column D, and enter the formula = B1 & C1 & D1 in the cell D1.
2. select cell D1 again and copy the formula to the cell below column D with the filling handle. The content of Column B, C, and column D is merged into the corresponding cell of column E.
3. select column E, execute the copy operation, select column F, execute the edit → select Paste command, open the select Paste dialog box, and select the value option, press the "OK" button to copy the content of column E (not a formula) to column F.
4. Delete columns B, C, D, and E to complete the merging.
Tip: After completing steps 1st and 2, the merge effect has been achieved. However, if columns B, C, and D are deleted, an error occurs. Therefore, step 3 is required to convert the formula to a constant "value ". Error! Unrecognized switch parameter.

Score
Some people often ask questions such as "how to print scores". Many people adopt recording macros or VBA methods, which is difficult for beginners. For this reason, I provide a simple method to implement functions.
It is assumed that the student score is saved in the A1 to g64 cell area of the sheet1 worksheet, where 1st behavior title and 2nd behavior subject name.
1. Switch to the sheet 2 worksheet, select cell A1, and enter the formula: = If (mod (row (), 3) = 0, "", if (0mod? Row (), 3 (= 1, sheet1! A blank, index (sheet1! $ A: $ g, INT (row () + 4)/3) + 1), column ()))).
2. select cell A1 again, and copy the above formula to cell B1 to cell G1 with the "fill handle". Then, select cell A1 to cell G1 at the same time, use the fill handle to copy the above formula to cells A2 to G185.
At this point, the score bar is basically formed. The following is a simple modification.
3. after adjusting the Row Height and column width, select cell A1 to cell G2 (score bar area of 1st students) and press the drop-down button on the right of "format" toolbar "border, in the subsequent border list, select the "all borders" option to add a border for the selected area (if you do not need a border, you can skip this step and the following operations ).
4. in addition, select cell A1 to cell G3, click the "style brush" button on the "frequently used" toolbar, and hold down the left mouse button to drag from cell A4 to cell g186, add Borders for all score entries.
Click "print" to print the score.
　　

14. Select functions in Excel
When using functions to process data, you often do not know which functions are suitable. The "search function" function in Excel helps you narrow down the scope and select the appropriate function.
Run the "insert → function" command, open the "insert function" dialog box, and enter the requirements (such as "count") in the box under "search function "), click the "go" button. The system selects the functions related to the "count" and displays them in the list box under "select function. You can quickly determine the required functions by checking the help files.
　　

15. View data in multiple cells in different worksheets at the same time
Sometimes, when editing a worksheet (sheet1), you need to view other worksheets (sheet2, sheet3 ......) The content of a cell can be achieved through the "monitoring window" function of Excel.
Run the "View> toolbar> Monitoring window" command to open the monitoring window. Click "add monitoring" to expand the "add monitoring point" dialog box, select the cell to be viewed with the mouse, and then click "add. Repeat the preceding operation to add other monitoring sites ".
In the future, no matter which worksheet you are in, you can open the monitoring window to view the data and related information in the cells of all monitored points.
　　

16. quickly draw borders for cells
In versions earlier than Excel 2002, it is difficult to add borders to the cell area. Excel 2002 has completely expanded this function.
Click the drop-down button on the Right of border on the "format" toolbar. In the drop-down list that appears, select the "Drawing border" option, or run the "View> toolbar> border" command, expand the border toolbar.
Click the drop-down button on the far left of the toolbar, select a border style, and drag it in the cell area where you want to add a border to quickly draw a border for the corresponding cell area.
Tip: ① if the border is incorrect, it doesn't matter. Select the "erase border" button on the toolbar and drag it on the incorrect border to clear the incorrect border. ② If You Need To draw borders of different colors, you can first press the "line color" button on the right of the toolbar, select the desired color in the pop-up palette, and then draw a border. ③ This function can also draw diagonal lines in cells.

Error! Unrecognized switch parameter.

17. Control the length of input text in a specific cell
You can imagine that when you fill in a two-digit number in the four-digit cell of the input text, or you enter a number in the cell of the input text, excel can be automatically judged, analyzed in real time, and a warning is displayed. How nice is that! To implement this function, it is not difficult for excel.
For example, we move the cursor to a cell for "year" registration. In order to unify the input and facilitate the calculation, we hope that "year" will be represented by a four-digit number. Therefore, you can click the "validity" option in the "data" menu. Select "text length" from the "allow" drop-down menu of "set" card "validity condition ". Select "equal" from the "data" drop-down menu and set "length" to "4 ". At the same time, we go to the "error warning" card and set "error warning displayed when invalid data is input" to "stop ", the entered text is invalid in the "title" and "error message" fields!" And "enter a four-digit year ." .
Obviously, if someone does not enter a four-digit number in the cell, the Excel worksheet will display a warning dialog box to show you the cause of the error, and you can continue to enter the correct "style" value. Magic? In fact, there are many special data formats available in Excel's "Data Validity" judgment, such as "text type", "sequence size", and "time distance, if you are interested, why not design your own testing standards to make your Excel stand out.
　　

18. Fill in fixed cells of multiple tables in groups
We know that each time an Excel worksheet is opened, multiple worksheets are always opened by default. From this we can see that in addition to the powerful processing capabilities of a single table, Excel is more suitable for coordinating work in multiple associated tables. To coordinate the association, you must first synchronize the input. Therefore, in many cases, you must enter the same content in the same cells of multiple tables at the same time.
How can we edit tables in groups? First, click the label name "sheet1" for the first worksheet, then press shift, and click the label name "sheet3" for the last table (if the table we want to associate is different, you can press Ctrl to click ). At this point, we can see that the name on the title bar of Excel shows the word "Working Group", and we can edit the Working Group. In the cell where you need to enter multiple table contents at a time, we found that the corresponding content is displayed in the same position of all tables in the "Working Group.
However, synchronous input alone is far from enough. For example, what should we do if we need to change the format of data in multiple tables at the same position? First, we need to change the data format of the first table, click the "fill" option in the "edit" menu, and then select "to same group worksheet" in its sub-menu ". In this case, the "fill in group worksheet" dialog box is displayed in Excel. Here we select "format" and click "OK, the data format of all tables in the same group at this position has changed.
　　

19. Change the case sensitivity of Text
In Excel, the most powerful support for table processing and data operations is not formulas, nor databases, but functions. Do not think that functions in Excel are only for numbers. In fact, as long as they are written into the table, Excel has special functions for editing it. For example, change the case sensitivity of text.
In Excel 2002, at least three functions are provided for text case conversion. They are: "= upper (source data lattice)", which converts all texts to uppercase; "= lower (source data lattice)", which converts all texts to lowercase; "= proper (source data grid)", converts the text to "appropriate" case, such as making the first letter of each word capitalized. For example, input "Excel" in lower case in cell A1 of a table, and then enter "= upper (A1)" in the target cell )", after you press enter, the result is "Excel ". Similarly, if we enter "mr. weiwei ", and then input" = proper (A3) "in the target cell, then the result we get will be" mr. weiwei.
　　

20. Extract specific characters from a string
Apart from direct input, extracting specific character input from existing cell content is definitely a time-saving and easy-to-use method, especially for some similar style information, such as the employee list and nationality.
If we want to quickly extract titles from A4 cells, we 'd better use the "= right (source data lattice, number of extracted characters)" function, it indicates that "two characters are extracted from the rightmost character of cell A4" is input to this position. Of course, if you want to extract a name, you need to use the "= left (source data lattice, number of extracted characters)" function. In another case, we extract several characters from the data instead of starting from both the left and right sides. For example, to extract the word "Wuhan" from cell A5, you only need to enter "= mid (A5,)" in the target cell. It means to extract two characters After 4th characters in cell A5, that is, 4th and 5th.

21. Convert base words into ordinal words　

Converting English base words into ordinal words is a complicated problem. Because it does not have a very fixed pattern: Most numbers are converted into ordinal words using the "th" suffix, however, the numbers ending with "1", "2", and "3" end with "St", "nd", and "RD" respectively. Moreover, the three numbers "11", "12", and "13" are different, but they still end with "th. Therefore, implementation seems complicated. In fact, as long as we clarify our thinking and find a function, we can easily convert it by writing a formula. Believe it or not, please refer to: "= a2 & if (or (value (right (A2, 2) = {11,12, 13})," th ″, if (or (value (right (A2) = {1, 2, 3,}, choose (right (A2), "St", "nd", "RD ″), "th ″))". Although the formula is a long string, its meaning is clear: ① if the number ends with "11", "12", and "13", add the suffix "th; ② If the 1st principle is invalid, check the last number, use "St" at the end of "1", use "nd" at the end of "2", and use "RD" at the end of "3". ③ if the principles 1st and 2 are invalid, then "th" is used ". Therefore, it is easy and quick to convert base and ordinal words.
　　

22. Fill the number of digits with special symbols
People who have dealt with finance know that there is a conventional "Security Filling Method" in book filling, that is, filling up the vacancies in the amount, you can also add symbols such as "$" before the payment data. In fact, there are similar input methods in Excel, that is, the "rept" function. The basic format is "= rept (" special symbol ", fill digit )".
For example, if we want to fill the number at the end of cell A2 with "#" to 16 bits, we only need to change the formula to "= (A2 & rept (″#″, 16-len (A2) ". If we want to fill the number in cell A3 with" # "on the left to 16 bits, we need to change it to" = rept (″#″, 16-len (A3) & A3 "; in addition, if we want to fill the value in A4 with" # ", we need to change it to" = rept (″#″, 8-len (A4)/2) & A4 & rept ("#") 8-len (A4)/2) "; if you are not professional enough, to add the "$" symbol to the header of the number in cell A5, change it to "= (text (A5," $ #, #0.00 "(& rept (" # ", 16-len (text (A5," $ #, #0.00 ")" will certainly meet your requirements.
　　

23. Create a text Histogram
In addition to repeated input, another derivative application of the "rept" function is to create a histogram composed of plain text in the worksheet. Its principle is also very simple, that is, the intelligent repetition of special symbols, according to the calculation results in the specified cell shows different lengths of comparison effect.
For example, we first create an annual income and expenditure balance table, and then use column E as the display area of the "budget" month in the histogram, use the "G column" as the "overbudget" display area in the histogram. Then, the "N" character in the "wingdings" font is displayed based on the value of the existing result "d column" in the table. The procedure is as follows:
In cells E3, write the formula "= If (d30, rept (" N ", round (D3 *, 0)," ")", and drag the fill handle to G14. We can see that a text-only histogram without the Excel chart function is displayed, convenient, intuitive, and simple.
　　

Twenty-four. calculate the total number of words in a cell
Sometimes, we may be interested in the number of characters in a cell and need to calculate the total number of words in the cell. To solve this problem, in addition to using the virtual computing of the "substitute" function, we also need to use the "trim" function to delete spaces. For example, how many words are input in cell A1 ?" Then we can use the following expression to help:
"= If (LEN (A1) = 0, 0, Len (TRIM (A1)-len (substitute (TRIM (A1)," ") + 1 )"
The meaning of this formula is to first create a new string using the "substitute" function, and use the "trim" function to delete spaces between the characters, and then calculate the digital difference between the string and the original string, the number of "spaces" is obtained, and then the number of spaces plus 1 is obtained to obtain the number of characters in the cell.
　　

25. Euro Conversion
This is a new tool in Excel 2002. If you select the default method when installing Excel 2002, you may not find it in the "Tools" menu. However, you can select "load macro" in the "tool" menu, and then select the "Euro tool" option in the pop-up window. After "OK", Excel 2002 will be installed on its own.
After that, open the "tool" menu again and click "Euro Conversion". An independent window dedicated to currency conversion between the euro and the EU Member States appears. Like other function windows in Excel, you can set the "Source Region" and "Target Region" of the currency conversion with the mouse, and then select different currencies before and after the conversion. As shown in the following table, the "100 Euro" value is converted into other currencies of the EU Member States. Of course, to make the display of the euro more professional, we can also click the "Euro" button on the Excel toolbar, so that all the converted currency values are in the format of the euro.