35 tips for little-known Excel skills

1. Create a category drop-down list fill 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.

2. 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.

3. 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.

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.

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)/2
End 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 ".

13. Quickly print student scores
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 $2, 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.
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 ", in the "title" and "error information" columns, fill in the "input text non-methods!" 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 do not start from the left and right sides, but extract several characters directly from the data center. 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 is ending with "11", "12", "13", the suffix "th" is added; ② 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 digits, we need to change it to "= REPT (" # ", 16-LEN (A3) & amp; A3". In addition, if we want to use "#" to fill the values in A4 from both sides, you need to change it to "= REPT (" # ", 8-LEN (A4)/2) & A4 & amp; REPT (" # ") 8-LEN (A4)/2 )"; if you are not professional enough, add the "$" symbol to the top of the number in cell A5, then 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:
Write the formula "= IF (D3 <0, REPT (" n ",-ROUND (D3 *, 0)," "in cells E3 )),″″)", select it and drag "fill handle" so that all rows in column E can correspond to the results in column D one by one. Then, write the formula "= IF (D3> 0, 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.
26. Create a super search engine for a table
We know that the biggest difference between an Excel table and a Word table is that Excel includes all the content (including static text) in the table into the database. We can use "function query" to precisely locate the target data, just like a search engine on a webpage.
For example, in the table shown in, the scores of multiple students are input from cells A1 to F7. In cells A8 to A13, we create a "function query" area. Our assumption is that when we enter the name of any student in the cell right of "Enter Student name", that is, in the C8 format, the cell below the student's score is displayed automatically. The specific implementation method is as follows:
Move the cursor to the C9 cell and click the insert function option. In the pop-up window of 18, select the "VLOOKUP" function and click "OK ". In the "function parameters" window that appears, set "Lookup_value" (the value to be searched in the first column of the data table) is "C8" (that is, the name of the person we enter in the C8 cell); "Table_array" (index the range of the data search) is "A2: B6" (search for all students' "Chinese" scores); "Col_vindex_num" (the serial number of the value to be searched in the table) is "2" (that is, the value is in the 2nd Column); "Range_lookup" (whether exact match is required) is "FALSE" (indicating no. If yes, it is "true "). After the configuration is complete, press "OK ".
Return to the table and click cell C9. The command behavior "= VLOOKUP (C8, A2: B6, 2, FALSE)" is displayed in the "fx" area )". Copy the command line and input "= VLOOKUP (C8, A2: C6, 3, FALSE)" in cells C10, C11, C12, and C13 )"; "= VLOOKUP (C8, A2: D6, 4, FALSE)"; "= VLOOKUP (C8, A2: E6, 5, FALSE)"; "= VLOOKUP (C8, a2: F6, 6, FALSE) "(the parameter meaning is the same as that in C9 ).
Next, let's test the effectiveness of the "VLOOKUP" function. Try to enter a student name in the "C8" cell, such as "Zhao Yao". Press enter and we will find that the student's admission score is automatically displayed in each cell of the subject below.

27. Create an Excel worksheet outline
Like the Outline View of Word, Excel is mainly used to display key entries on the same screen when processing a particularly large worksheet. If there are many names on a table, but the data types are comparable, then we can select the data area with the mouse, click the "Category summary" option in the "data" menu. In the "select summary items" Area of the pop-up menu, select the category of the data to be summarized. As shown in 19, isn't the current table much smaller? If you want to view details, click the "+" button on the left side of the table.

28. Insert the "icon"
Although there are more than 50 "Charts" in 14 categories to Excel, the conventional chart representation method still seems boring and unimaginative for complicated data relationships. Therefore, the "Graph" function is added to the latest Excel 2002 version. Although there are only six partition styles in the "Graph" Window of the "insert" menu, it shows that the structure between the data has achieved the effect. For example, you can select an organization chart to display the hierarchy of data, and you can select a cycle chart to express the circulation process of funds. Of course, to illustrate the overlapping nature of various data, you can select "Wayne diagram ". You can see how beautiful the Wayne chart shown in Figure 20 is. You can also right-click the icon to bring up the "icon" toolbar. You can add more rings to the graph Style Library as you like.

29. Familiar with "From Text to Speech" in Excel"
This is an eye-catching new feature in Excel 2002. It allows Office software not only to write, calculate, but also to be able to read and speak. Despite the fact that many software programs are already devoted to text machine reading, Microsoft's Office is incomparable to other similar software, regardless of its voice softness and tone.
By default, you may not be able to find it in the Excel menu. After we confirm that the "From Text to Speech" option is installed, you can see the "Voice" project in the "tool" menu in Excel. 21. Open a table, select the content, and click the "read cell" button on the "From Text to Speech" toolbar, at this time, a magnetic sound begins to read aloud. It is worth mentioning that when you encounter punctuation marks, the reading of Excel will automatically pause for a while and then read again. This is completely different from other software, I think this process is more natural.
To adjust the speed of voice reading in Excel 2002, double-click the "Voice" icon in "Control Panel, then adjust it on the "Text to Speech" card.

Thirty, the wonderful use of the "photography" function in Excel
This feature is quite different from the "Orthodox" features such as computing, statistics, and sorting, because it serves as a "screen capture "! In addition, it is not just like the "PrintScreen" button, but allows you to select through the mouse, "where to capture ".
It is not easy to find this function. We click the "Custom" option in the "Tools" menu of Excel. Click "tool" in "category" of the "command" card, find the "photography" button in the command bar, and drag it to any location on the toolbar. If you want to take a photo of a part of the table, you only need to select them (figure 23) and then press the "photograph" button, in this case, the selected area is "photographed. Move the mouse to the place where you want to display the "photo" (of course, it can be another form), and then press the "photography" button again, in this way, the "photo" that was just taken will be immediately pasted. Of course, the "image" toolbar appears along with "photo. Obviously, Excel treats it as an image. we can rotate and scale it as needed.
However, please note that this is not a normal photo! You can try to change the table data that was previously taken by us. Refresh the "photo" and the data in the "photo" is also updated!

. Link and reference public data between multiple tables
Maybe we may wonder, why does the software always open a file group composed of three blank tables every time we open an Excel file? If you are a professional accountant, Statistician, or an expert on this table, you will surely understand that, due to the wide variety of Computing Projects and the complexity of data information, A single table cannot solve all the problems. Multi-table data links and multi-file data interaction are a perfect solution.
For example, we first create "Sheet1" as the "one-class student catalog table", "Sheet2" as the "two-class student catalog table", and then go to "Sheet3 ", we want to compare the scores of "One Class" and "two classes" and calculate the "average grade score. In this case, we can position the cursor to the target and select the relevant function. Then, in the function dialog box popped up in Excel, click the "" button on the right of the Data list and click the cells to be referenced in other tables. You see, as shown in 24, the "× class student orders table will appear in the function window! ×× Cell. After that, regardless of the changes we make in the source cell, the target position of "Sheet3" will be recalculated in a timely manner.

2002. "tame" the clipboard window of Excel
Excel 2002 greatly improved the weak clipboard function in earlier versions. It can accommodate up to 24 items (while Excel 2000 can accommodate only 12 items ). In addition, this clipboard can be copied back and forth between any Office applications, so it is more convenient for users who often need to copy and paste. But every time you use the "copy" or "cut" command twice in a row, the clipboard will pop up and compete with you for a limited space to display documents, which is annoying. Fortunately, the method of "tame" the clipboard is very simple.
I suggest that if you do not want the clipboard to pop out unexpectedly, you only need to click "options" at the bottom of the clipboard menu to clear the check box on the "automatically display Office Clipboard. If you do not want the clipboard icon to appear on the system taskbar or pop up at any time, you only need to clear the options on the "show the Office clipboard icon on the taskbar" and "show status near the taskbar during replication" check boxes. Keep only the hooks in front of the "collect without displaying the Office Clipboard" check box.
Of course, you can restore this function at any time after you cancel the auto display function of the clipboard. For example, you can customize a set of shortcut keys to call up the clipboard at any time. Select the "Custom" option of "tool" from the menu, click the "keyboard" button in the lower part of the pop-up dialog box, select "edit" in the "category" List of the pop-up dialog box, and then, in the "command" list in the upper-right corner of the dialog box, select "EditOfficeClipboard", place the cursor in the "Press new shortcut" dialog box, and press Alt + Shift + C (or other combinations you like ), "Close" dialog box. Now, we only need to press Alt + Shift + C, and the Excel 2002 clipboard will immediately appear in front of you.
Tip: this secret is also applicable to Word 2002 and PowerPint 2002.

. Use the formula review tool to view the data source
Excel 2002 has a secret weapon-the "Formula Review" tool, which can clearly display the data sources and computing results in any cell.
Click the "Formula Review" option in the "Tools" menu and click "show Formula Review toolbar ". We still use the example in "Create a super search engine for a table" to select cells C12 with the mouse. As shown in Figure 25, the result in the cell is obtained by the function "= VLOOKUP (C8, A2: C6, 3, FALSE. Therefore, there are three data sources: C8, A2, and C6. Therefore, when we click the "trace reference cell" button on the "Formula Review" toolbar, Excel immediately specifies the three cells with arrows and Blue Points (figure 25 ). Of course, if a data in our table is invalid or has incorrect syntax, you can also click the "Release Invalid Data" button on the "Formula Review" toolbar to let Excel automatically check for missing data.

2002 clever use of Excel's "smart mouse"
We know that the scroll wheel is now almost the computer's "standard", but when we scroll the scroll wheel up and down pages, have you ever wondered whether we can use it to complete some other functions?
Click the "options" command in the "Tools" menu of Excel 2002, select the "zoom with smart mouse" check box in the "General" tab, and click "OK, let's take a look at the functions of the mouse wheel:
In the default state of "smart mouse", scroll up or down the scroll wheel, and the tables in the workspace are scaled up or down by 15%. Only when we press Ctrl, when you scroll the scroll wheel, the worksheet will go up and down as usual. In addition, if we use the Excel "hierarchical display", when we press Shift and scroll the mouse wheel, we can control the display or hiding of entries at all levels. Of course, there are more special functions that you need to explore in practice.

35. Application of "monitoring" window in Excel 2002
If you create a large workbook with data linked to other workbooks, the monitoring window in Excel can be of great help. With this function, you can easily see how worksheets, cells, and formula functions affect the current data.
In the Tools menu, click the Formula Review Sub-menu, and then click the show monitoring window button. Right-click the target cell and select "add monitoring point" from the shortcut menu ". The cells monitored by Excel and Their Formulas appear in the monitoring window list.
In the future, as long as we double-click the entry in the monitoring window, the monitored cells will not be invited.
Tip: when cells that point to other workbooks are monitored, they can be displayed only when all referenced workbooks are opened.