35 tips for using workbooks

35 tips for using workbooks (1)
Friday, 19. May 2006, 19:49:23

35 tips for using workbooks (1)

1. Create a category drop-down list fill item
We often need to enter the company name into the table, to maintain name consistency, use "Data Validity"
Feature creates a category drop-down list fill item.
1. In Sheet2, name an enterprise by category (for example, "Industrial Enterprise", "commercial enterprise", and "individual enterprise ")
) Enter different columns to create an enterprise name database.
2. Select column A (the column where the name of the "Industrial Enterprise" is located) and enter the character "Industrial Enterprise" in the "name" column.
, 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 columns (such as column C) that need to enter the "Enterprise category" and execute "data> validity ".
"Command to 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", "commercial enterprise", and "individual" in the "Source" box below.
Enterprises "...... To exit.
Select the columns (such as column D) that need to enter the enterprise name. Then, open the "Data Validity" dialog box and select the "sequence ".
"Option, enter the formula: = INDIRECT (C1) in the" Source "box, and confirm to exit.
4. Select any cell in Column C (such as C4), click the drop-down button on the right, and select the corresponding "Enterprise category" to fill in the ticket
In the cell. Select the D Column cell (such as D4) corresponding to the cell, and click the drop-down button
Enter the required enterprise name in the enterprise name list.
Tip: if you do not need to print the "Enterprise category" column when printing a report in the future, you can select this column and right-click the column.
Select "hide" 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, so that you can easily adjust
.
1. Right-click the blank area in the toolbar and select the "Custom" option to open the "Custom" dialog box. In the "command"
Label, select the "new menu" under "category", and then 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 another item under "category" (for example, "insert" option) and under "command" on the right (for example
"HYPERLINK" option), drag it to the new menu (common document), and name it like the above operation (such
"Payroll", etc.) 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 click
Select "Assign hyperlink> open" from the menu to open the "Assign HYPERLINK" dialog box. By pressing "search range"
Click the drop-down button on the Right To locate the corresponding work book (for example, " .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, you only need to expand the "frequently used documents" menu.
, Click the corresponding options.
Tip: although we drag the "HYPERLINK" option to the "Common Document" menu, the "insert" menu is not affected.
Click the "HYPERLINK" menu item in the ticket and the "insert HYPERLINK" button on the "frequently used" toolbar.

3. display different types of data in different colors
In the payroll, if you want to display the total wages greater than or equal to 2000 yuan in red
The total salary is displayed in "blue", the total salary below 1000 yuan is displayed in "brown", and the others are displayed in "Black,
We can set it like this.
1. Open the "payroll" workbook, select the column where "total wages" is located, and run the "format → condition format" command,
Open the "condition format" dialog box. Click the drop-down button on the right of the second box and select the "greater than or equal to" option.
Enter the value "2000" in the box ". Click "format" to open the "cell format" dialog box
Set "color" to "red ".
2. Press the "add" button and set other conditions (greater than or equal to 1500, and set the font to"
"Blue"; less than 1000; 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 toolbar of your own "professional symbols.
1. Run the "Tools → macros → recording new macros" command to open the "Recording new macros" dialog box and enter the macro name, such as "fuha ".
O1 and save the macro to "personal macro Workbook", and then "OK" to start recording. Select the "Recording macro" Toolbar
The "relative reference" button on, enter the required special symbols into a cell, and then click "Recording macro"
Click "stop" on the bar to 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. The "new tool" dialog box appears.
In the "bar" dialog box, enter the name "professional symbol". After confirming, a toolbar appears in the workspace.
Switch to the "command" tab, select "macro" under "category", and click "Custom button" under "command ".
Drag "item to the" professional symbols "Column (the buttons are dragged when there are many special symbols ).
3. select one of the "Custom buttons" and name them based on the 2nd points of the 1st tips.
4. Right-click a named button. In the shortcut menu that will pop up later, select the "specify macro" option to enable the "specify
Macro dialog box, select the corresponding macro (such as fuhao1), and confirm 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 as you would in the general toolbar.
To quickly enter the professional symbol in the cell.

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 and drop the rows (or columns) that do not need to be printed with the mouse, select them, and right
Click the mouse and select the "hide" option in the shortcut menu 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,
In the "add view" dialog box, 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"
Click the "show" button to display the worksheet immediately on the preset page. Simply set and typeset the worksheet and press the toolbar.
On the "print" button, everything is OK.

6. Sort data on demand
If you want to sort employees by their departments, the relevant information of these departments is not in the pinyin order.
And it is not in the order of strokes. What should I 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"
In the box below the input sequence, enter the sequence of the Department (for example, "agency, fleet, one workshop, two workshops, three workshops ").
), Click "add" and "OK" to exit.
2. Select any cell in the "department" column and run the "data> sort" command to open the "sort" dialog box.
Click the "options" button to bring up the "sorting options" dialog box. Press the drop-down button to select the order you just customized.
Column. After two "OK" buttons are returned, 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 (area) to hide the content, run the format → Cell command, and open the cell grid.
Type dialog box, select the "Custom" option under "category" of the "Number" label, and then under "type" on the right
Enter ";" in the box ).
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
, "OK.
After such settings, the content in the above cells is no longer displayed, that is, the Excel transparent function is used.
And cannot make it visible.
Tip: under the "Protect" label, do not clear the "unlock" number in the check box before "Lock". This prevents
Stop others from deleting your hidden data.
8. enable intelligent Chinese and English input methods
When editing a table, some cells must enter English, some cells must enter Chinese, and the input method should be switched over and over again
It is really inconvenient. Why not set it so that the input method can be adjusted intelligently?
Select the cell area where you want to enter Chinese characters, run the "Data → validity" command to enable the "Data Validity" pair
Switch to the "Input Method" tab, click the drop-down button on the right of "mode", and select the "open" option,
Click OK to exit.
When any cell in the cell area where Chinese characters are to be entered in the future election, the Chinese Input Method (input method list
1st Chinese Input Methods) 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, "pcw" in lower case),
Replace it with "enter" "in the box below, and then click" add "and" OK.
3. If you need to enter the above text, you only need to enter the "pcw" character. In this case, the "pcw" case is not considered.
And then confirm it.

10. Customize functions in Excel
Although Excel functions are rich, they cannot meet all our needs. We can customize a function.
Into some specific operations. Next, we will define a function to calculate the trapezoid area:
1. Execute "Tools → macros → visual
The 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 and select the expected background
Click "insert" to insert the image to the worksheet.
2. Press and hold the ctrl key, drag the mouse in the cell (area) that does not need to be lined with the image, and select
Some cells (regions ).
3. Press the drop-down button on the Right of fill color on the "format" toolbar. In the "palette" that appears later,
Select "white ".
After such settings, the left cell is lined with an image, and under the selected cell (area ),
No image is lined up (in fact, the image is "white ).
The image lined with cells does not support printing.

12. merge text with the hyphen "&"
If we want to merge the content of multiple columns into one column, we do not need to use a function. A small hyphen "&" is
It can be done (Here we assume that columns B, C, and D are merged into one 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 above formula to the cells in column D with the "fill handle", column B, column C, and column D.
Is merged into the cells corresponding to column E.
3. Select column E, perform the "copy" operation, select column F, and run the "Edit> select Paste" command to open
In the "select Paste" dialog box, select the "value" option and press the "OK" button. The content of column E (not
Formula) is copied 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, the formula will
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 like "How to print scores". Many people use recording macros or VBA to achieve this.
This 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
2. Name of the behavior discipline.
1. Switch to the sheet 2 worksheet, select cell A1, and enter the formula: = IF (MOD (ROW (), 3) = 0 ,″
", IF (0 MODROW (), 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, at the same time
Select cells A1 to G1 and copy the formula A2 to cells G185 with the fill handle.
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 the A1 to G2 cell area (score bar area of 1st students) and press
Drop-down button on the Right of border in the format toolbar. In the subsequent border list, select all borders.
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. Select cell A1 to cell G3 at the same time, click the "style brush" button on the "frequently used" toolbar, and then press and hold the mouse
Left click, drag from A4 to the G186 cell area, and add a border 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. "Search function" in Excel
You can narrow down the scope and select the appropriate function.
Run the "insert → function" command to open the "insert function" dialog box and enter
Input requirements (such as "count"), and then click the "go" button, the system immediately selects the functions related to the "count"
And displayed in the list box under "select function. Then, you can quickly confirm
Required functions.

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 and click "add monitoring"
Click to expand the "add monitoring point" dialog box, click the cell to be viewed, and then click "add ".
. Repeat the preceding operation to add other monitoring sites ".
In the future, no matter which worksheet you are in, you only need to open the "monitoring window" to view all the monitored vertex Cells
.

 

16. quickly draw borders for cells
In versions earlier than Excel 2002, it is difficult to add borders for the cell area.
2002.
Click the drop-down button on the Right of border on the "format" toolbar. In the drop-down list that appears later, select "Drawing
Border option, or run the "View> toolbar> border" command to expand the "border" toolbar.

Click the drop-down button on the far left of the toolbar, select a border style, and then in the cell area of the border to be added
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 the border to clear the incorrect border. ② If You Need To draw borders of different colors, you can press
Click the "line color" button on the right of the bar, 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 cell in the four-digit input, or enter a single
When you enter a number in the cell, Excel can automatically judge and analyze the data in real time and bring up a warning. How nice is that! Yes
Implementing this function is not difficult for Excel.
For example, we move the cursor to a cell for "year" registration. In order to unify the input and calculate conveniently,
We want "year" to be represented by a four-digit number. Therefore, you can click "validity" in the "data" menu"
. Select "text length" from the "allow" drop-down menu of "set" card "validity condition ". Then,
Select "equal" from the data drop-down menu and set "length" to "4 ". At the same time, we will go to the "error warning" card.
Set "error warning displayed when invalid data is entered" to "stop", and in "title" and "error message"
The entered text is invalid !" And "enter a four-digit year ." .
Apparently, if someone does not enter a four-digit number in the cell, a warning dialog will pop up in Excel.
Box to tell you the cause of the error, and you can continue to enter the correct "style" value. Magic? Actually
There are many special types of data formats available in the Excel "Data Validity" judgment, such as "text type"
"Ah," sequence size "and" time distance ". If you are interested, why not design your own detection method?
The standard makes your Excel show extraordinary.

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 Excel
The powerful processing capability of a single table is more suitable for coordination among multiple associated tables. To coordinate associations, of course
First, you need to synchronize the input. Therefore, in many cases, you must enter
The same content.
How can we edit tables in groups? First, click the label "Sheet1" of the first worksheet. However
Hold down the Shift key and click the label name "Sheet3" of the last table (if the table we want to associate is not the same
, You can press Ctrl to click ). At this point, we can see that the name on the title bar of Excel shows "Working Group"
Then we can edit the Working Group. When you need to input multiple table contents at a time
After writing something, 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, we need to unify the data at the same position in multiple tables.
What should I do if I change the format? First, we need to change the data format of the first table, and then click "edit"
Fill option, and then select "to same group worksheet" in its sub-menu ". In this case, "fill in as a group worker" appears in Excel.
In the "format" dialog box, click "OK ".
The data format has changed.
19. Change the case sensitivity of Text
In Excel, the most powerful support for table processing and data operations is not formulas or databases,
Function. Do not think that the functions in Excel are only for numbers. In fact, as long as they are written into the table
It edits special functions. 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 grid)", converts all text to uppercase;
"= LOWER (source data grid)", converts all text to lowercase;
"= PROPER (source data grid)", convert the text to "appropriate" case,
For example, the first letter of each word is capitalized. For example, we enter the lower-case "Excel" in the A1 cell of a table.
", And then enter" = upper (A1) "in the target cell. Press enter and the result will be" Excel ". Same
Example: If we enter "mr. Weiwei" in cell A3, then we enter "= proper (
A3) ", then the result we get will be" mr. Weiwei.
20. Extract specific characters from a string
In addition to direct input, extracting specific character input from the existing cell content is definitely a time-saving and easy-to-use method.
Methods, especially for some similar style information, such as employee list, nationality and other information.
If we want to extract titles from A4 cells quickly, we 'd better use
"= Right (source data lattice, number of extracted characters)" function, which indicates "extracting from the rightmost character of cell A4"
2 characters.
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, we want
When the word "Wuhan" is extracted 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.
35 tips for using workbooks (2)
Friday, 19. May 2006, 19:52:06

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 suffixed with "th" when becoming ordinal words. However, big fanwe end with "1", "2", and "3 ".
The numbers ending with "St", "nd", and "RD" respectively. In addition, "11", "12", and "13"
But they still end with "th. Therefore, implementation seems complicated. In fact, as long
We clarify our thinking and find the 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 this formula is a long string,
However, the 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 and end "St" with "1", and end with "2" with "N"
D. Use RD at the end of 3. ③ if the 1st and 2 principles are invalid, use th ". Therefore
And ordinal word conversion is so easy and fast.

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
Fill in vacancies in the amount, or add symbols such as "$" before the money data. In fact, there are also
A similar input method is the "rept" function. Its basic format is "= rept (" special symbol ", fill bit
Number )".
For example, if we want to fill the number ending in cell A2 in Figure 14 with "#" to 16 bits, we only need
Change to "= (A2 & rept (" # ", 16-len (A2 )".
When the side is filled with "#" to 16 bits, it should be changed to "= REPT (" # ", 16-LEN (A3) & A3". In addition, if
If we want to use "#" to fill in the values in A4 from both sides, we need to change it to "= REPT (" # ", 8-len (A4)/2 )&
A4 & REPT ("#") 8-LEN (A4)/2) "; if you are not professional enough, add the header of the number in the A5 Cell
For the "$" symbol, change it to "= (TEXT (A5," $ #, #0.00 "(& REPT (" # ", 16-LEN (TEX
T (A5, ″ ##,## 0.00 ")", will certainly meet your requirements.
Figure 14
23. Create a text Histogram
In addition to repeated input, another derivative application of the "REPT" function is that you can directly create
The histogram composed of the following. The principle is also very simple, that is, using the smart repetition of special symbols, according to
The calculation results show different lengths.
For example, we first create an annual income and expenditure balance table, and then use column E as the "budget" month in the histogram.
The "G column" is used as the "overbudget" display area in the histogram. Then, based on the existing result column "D" in the table"
Is expressed by the "N" character in the "Wingdings" font. 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, in cell G3
Write the formula "= IF (D3> 0, REPT (" n ", ROUND (D3 *, 0),", and drag the fill handle to G1.
4. We can see that a text 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. Yes
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
Except spaces. For example, if "how many
Words ?" Then we can use the following expression to help:
"= IF (LEN (A1) = 0, 0, LEN (TRIM (A1)-LEN (SUBSTITUTE (TRIM (A1 ),″,″,″
") + 1 )"
This formula first creates a new string using the "SUBSTITUTE" function and deletes it using the "TRIM" function.
The space between the characters is calculated, and the digit difference between the string and the original string is calculated to get the number of "spaces ",
Then, the number of spaces + 1 is used to obtain the number of characters in the cell.
25. Euro Conversion
This is a new tool in Excel 2002. If you are installing Excel
2002 is selected as the default method, so it is likely not found in the "tool" menu. However, we can select
Select "load macro" in the "Tools" menu, and then select the "Euro tool" option in the pop-up window. After "OK", Ex
Excel 2002 will be installed on your own.
After that, open the "tool" menu again and click "Euro Conversion", an independent one dedicated for Euro and Euro
The currency conversion window of the Alliance's Member States appears. Like other function windows in Excel, we can set them with the mouse
The "Source Region" and "Target Region" of the currency conversion, and then select different currencies before and after the conversion. Of course,
To make the display of the euro more professional, we can also click the "Euro" button on the Excel toolbar, so that after all the conversion
The currency values are all in the euro format.

26. Create a super search engine for a table
As we know, the biggest difference between an Excel table and a Word table is that Excel fills in all the content in the table.
(Including static text) are included in the scope of the database. We can use "function query" to import the target data
Precisely locates rows, just like search engines on webpages.
For example, in the table, the scores of multiple students are input from cells A1 to F7. Unit between A8 and A13
In this example, a "function query" area is created. Our assumption is that when we are on the right side of "Enter Student name ",
After you enter the name of any student in the C8 format, the cell below it will automatically display
The score of each student. 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 "VLOOKUP"
Function, click "OK ". In the pop-up "function parameters" window, we set "Lookup_value "(
The value searched in the first column of the data table) is "C8" (that is, the name of the person we entered in the C8 cell); "Table_ar
Ray (index data search range) is "A2: B6" (that is, search for "Chinese" scores of all students); "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 "). Set
Click "OK ".

Return to the table and click cell C9. The command behavior "= VLOOKUP (C8
, A2: B6, 2, FALSE )". Copy the command line and enter "= V" in cells C10, C11, C12, and C13 respectively.
LOOKUP (C8, A2: C6, 3, FALSE) ";" = VLOOKUP (C8, A2: D6, 4, FALSE) ";" = VLOOK
UP (C8, A2: E6, 5, FALSE) ";" = VLOOKUP (C8, A2: F6, 6, FALSE) "(the parameter meaning is the same
In C9 ).
Next, let's test the effectiveness of the "VLOOKUP" function. Try entering a student name in the "C8" cell,
For example, "Zhao Yao", when we press enter, we will find that the entry of the student is automatically displayed in each cell in the lower section.
Score.
27. Create an Excel worksheet outline
Like the Outline View of Word, Excel is mainly used to process large worksheets, and it is difficult
Entries are displayed on the same screen. If there are many names in a table, but the data types are comparable
Then we can select the data area with the mouse, and then 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. Finally, as shown in 19, the current
Isn't the 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 14 categories of more than 50 "chart" styles to Excel, but for the complex data relationships, the General
The chart representation method is still boring and unimaginative. Therefore, in the latest Excel version
2002 added the "Graph" function. Although there are only six la s in the "Graph" Window of the "insert" menu,
However, it shows that the structure between the data has achieved the effect of "Four or two hundred pounds. For example, to display the level of data, you can
To select the "organizational structure chart", and to express the circulation process of funds, you can select the "cycle Chart ".
For data overlapping, you can select "Wayne diagram ". You can see how beautiful the Wayne chart shown in Figure 20 is. And you still
You can right-click the icon to bring up the "icon" toolbar. You can even add more image styles as you like.
Several rings.

29. Familiar with "From Text to Speech" in Excel"
This is Excel
One eye-catching new feature in 2002. It allows Office software not only to write, calculate, but also to be able to read and speak. Although
Currently, many software programs are dedicated to reading texts from machines. However, Microsoft Office does not care about the softness and tone of speech.
All the other similar software is incomparable.
By default, you may not be able to find it in the Excel menu.
After selecting "Text to Speech", you can see the "speech" project in the "Tools" menu of Excel. 21
Open a table, select the content, and click "read aloud" on the "From Text to Speech" toolbar.
Cell "button, then a magnetic sound begins to read aloud. It is worth mentioning that
When punctuation marks are used, the reading of Excel will automatically pause for a while and then read again. This is totally different from other software.
Same, I think this process is more natural.

Note that if you want to adjust the Excel
In 2002, double-click the "Voice" icon in the "control panel", and then click "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 of its
It turned out to be "screen capture "! It is not like the "printscreen" button, but allows
You can use the mouse to select "where to capture ".
It is not easy to find this function. We click the "Custom" option in the "Tools" menu of Excel. In
Click "tool" in "category" of the card, find the "photography" button in the command bar, and drag it
Any location. If you want to take a photo of a part of the table, you only need to select them first, and then press "photography"
", Then the selected area is" shot. Then move the cursor to the place where the "photo" is to be displayed (
Of course, it can also be another form), press the "photography" button again, so that the "photo" that was just taken will
Paste it immediately. Of course, the "image" toolbar appears along with "photo. Obviously, in Excel
It is treated as an image. we can rotate and scale it at will.

However, please note that this is not a normal photo! You can try to change the original image we "shot"
Take a look at the following table data-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, every time we open an Excel file, the software always opens a text consisting of three blank tables.
What about the group? If you are a professional accountant, Statistician, or a table guru familiar with this, you will understand that
Project names and data details are complex. A single table cannot solve all the problems.
Data links and multi-file data interaction are the best way to win.
For example, we first create "sheet1" as "one-class student renewal table" and "sheet2" as "two-class student renewal table ".
Then he came to "Sheet3" and wanted to compare the scores of "One Class" and "two classes" and the "average grade score"
. In this case, we can position the cursor to the target and select the relevant function. Then, a letter pops up in Excel.
In the data dialog box, 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. This
No matter what 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, which can accommodate up to 24 items (Excel
2000 can only accommodate 12 ). In addition, the clipboard can be copied back and forth between any Office applications.
It is more convenient for users who often need to copy and paste files. But each time when you use two consecutive "copies" or"
When you cut the command, the clipboard will pop up and compete with you for a limited space for document display, which is annoying. Fortunately
The clipboard method is very simple.
I suggest you click"
Option to clear the hook on the "automatically display Office Clipboard" check box. If you do not want the clipboard icon to appear in
On the System taskbar or at any time, you only need to clear the "display the Office clipboard icon on the taskbar" and "copy"
Display status near the taskbar. Only keep "collect without displaying the Office Clipboard"
Select the hooks in front of the box.
Of course, you can restore this function at any time after you cancel the auto display function of the clipboard. For example, we can
Define a set of shortcut keys so that the clipboard can be quickly called up at any time. Select the "Custom" option of "tool" from the menu, click
Click the "keyboard" button in the lower part of the pop-up dialog box.
Select "edit" in the "category" list, and then select "editoffice" in the "command" list in the upper-right corner of the dialog box.
Clipboard ";
Place the cursor in the "Press new shortcut" dialog box, and press Alt + Shift + C (or another combination you like ),
"Close" dialog box. Now, we only need to press Alt + Shift + C, Excel
The 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 there is a secret weapon-the "Formula Review" tool, which can be used to store the data sources and computing results of any cell.
The location is clearly and clearly displayed.
Click the "Formula Review" option in the "Tools" menu and click "show Formula Review toolbar ". We
You can use the example in "Create a super search engine for a table" to select cells C12 with the mouse. From Figure 25, I
We can see that the results in this cell are obtained through the function "= Vlookup (C8, A2: C6, 3, false.
Therefore, there are three data sources: C8, A2, and C6. Therefore, when we click track reference on the "Formula Review" Toolbar
After the cell button is used, Excel immediately specifies the three cells with arrows and blue points. Of course, if we
If a data in the format is invalid or the syntax is incorrect, you can also click "Release Invalid Data" on the "Formula Review" toolbar"
Button to let Excel automatically help us check for missing items.

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 Excel
2002 "options" command in the "Tools" menu, and then select "zoom with smart mouse" on the "General" tab
Select the box 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 displayed in a 15% ratio.
Large or small, and the worksheet will go up or down as usual only when we press Ctrl and scroll the scroll wheel.
. In addition, if we use Excel's "hierarchical display", when we hold down Shift and scroll the mouse wheel,
You can also 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,
Excel
The "monitoring window" in can help you a lot. It allows you to easily view worksheets, cells, and formulas.
How does a function affect the current data.
In the Tools menu, click the Formula Review Sub-menu, and then click the show monitoring window button. Right-click me
And select "add monitoring point" in the shortcut menu ". In this case
The cells monitored by Excel and Their formulas are displayed.
In the future, as long as we double-click the entry in the monitoring window, the monitored cells will not be invited.
Tip: When a cell containing a cell pointing to another Workbook is monitored, only when all referenced workbooks are opened
Can be displayed in the "monitoring Window Interface" list. 97 does not seem feasible.