Excel Table 35 strokes must learn the secret

Excel Table 35 strokes must learn the secret

Here are 35 list of mandatory learning categories:

1. Set up category Drop-down list fill item

2, the establishment of "common documents" new menu

3, so that different types of data in different colors display

4, the production of "professional symbol" toolbar

5, using the "Face Manager" to save multiple printed pages

6, let the data on demand sort

7, completely hide the data

8, Jean Cheng, English input method of intelligent appearance

9, let "AutoCorrect" enter a unified text

10. Customizing functions in Excel

11, under the table head lining picture

12, with the hyphen "&" to merge text

13. Quick Print Student record

14, Excel to help you select functions

15. View data in multiple cells on different worksheets at the same time

16. Quickly draw a border for cell

17, control the length of specific cell input text

18. Fixed cells filled with multiple tables

19, change the case of text

20. Extract specific characters from a string

21. Converting cardinal words into ordinal numbers

22, with special symbols to complement the number of digits

23. Create a text histogram

24, calculate the total number of words in the cell

25. On the conversion of the euro

26. Make a super search engine for the form

27, the establishment of Excel worksheet outline

28, insert "Diagram"

29, familiar with Excel "from Text to Speech"

30. The magical function of "photography" in Excel

31. To implement the link and reference of common data between multiple tables

32, "Tame" the Clipboard window of Excel 2002

33, use the Formula Audit tool to view the source of data

34, clever use of Excel 2002 "Smart Mouse"

35, Excel 2002 "Watch" Window application

　　First, create category Drop-down list fill item

We often enter the name of the enterprise into the table, in order to maintain the consistency of the name, using the "Data validation" feature to build a category Drop-down list fill item.

1. In Sheet2, create a database of enterprise names by entering different columns into categories (such as "Industrial Enterprise", "Commercial enterprise", "individual enterprise", etc.), respectively.

2. Select Column A (the "Industrial Enterprise" name is the column), in the Name column, enter the "industrial Enterprise" character, press "enter" key to confirm.

Imitate the above operation, will B, C ... The columns are named "Commercial enterprise", "Individual Enterprise", respectively.

3. Switch to Sheet1, select the column (such as column C) where you want to enter the enterprise category, and execute the data → effectivity command to open the Data Validation dialog box. In the Settings tab, click the Drop-down button to the right of allow, select the sequence option, and in the Source box below, enter "industrial Enterprise", "Commercial enterprise", "Individual enterprise" ... Sequence (separated by commas between the elements), OK exit.

Select the column you want to enter the enterprise name for, such as column D, and then open the Data Validation dialog box, select the series option, and then enter the formula in the Source box: =indirect (C1), OK exit.

4. Select any cell in column C (such as C4), click the Right Drop-down button, and select the appropriate enterprise category to fill in the cell. Then select the cell in column D (for example, D4) and click the Drop-down button to fill in the cell with the desired enterprise name from the list of enterprise names for the category.

Tip: If you don't need to print the Enterprise category column when you print the report later, you can select the column, right-click, and hide the column by selecting the Hide option.

　　Second, the establishment of "common documents" new menu

Create a new "common document" menu on the menu bar and add the commonly used workbook documents to it for easy invocation at any time.

1. Right-click in the margin of the toolbar and select the Custom option to open the Customize dialog box (Figure 1). In the Commands tab, select the New menu item under Categories, and then drag new menu under Commands to the menu bar.

Figure 1

Press the Change Selection button to enter a name (such as "common documents") in the Name box of the pop-up menu.

2. Select one of the following categories (such as the Insert option) on the right, under command, select an item (such as the hyperlink option), drag it to a new menu (a common document), and name it (such as "payroll", etc.), and create the first workbook document list name.

Repeat the above action, add more than a few document list names.

3. Select a menu item in the Common document menu (such as "payroll", etc.), right-click the mouse, and, on the pop-up shortcut menu, select the Assign hyperlink → open option to open the Assign Hyperlink dialog box. Navigate to the appropriate workbook (such as salary. xls, and so on) by pressing the Drop-down button to the right of look in, and select the workbook document.

Repeat the action above to link the menu item to the workbook document that corresponds to it.

4. When you need to open a workbook document from the Common document menu, just expand the Common Documents menu and click the appropriate option.

Tip: Although we dragged the hyperlink option to the common document menu, it does not affect the function of the Insert Hyperlink button on the Hyperlink menu item and the Standard toolbar on the Insert menus.

　　Three, let different types of data in different colors display

In the payroll, if you want to be greater than or equal to 2000 of the total wages to "red" display, greater than or equal to 1500 yuan in the total wages to "blue" display, less than 1000 of the total wages to "brown" display, and other "black" display, we can set this.

1. Open the Payroll workbook, select the column for payroll, and execute the format → conditional formatting command to open the Conditional Formatting dialog box. Click the Drop-down button to 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 the Format button to open the Format Cells dialog box and set the font color to red.

2. Press the "Add" button, and follow the above operation 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 setup is complete (Figure 2), press "OK" button.

Figure 2

Look at the payroll, and the payroll figures are displayed in different colours according to your requirements.

　　Four, the production of "professional symbol" toolbar

When editing a professional form, you often need to enter some special professional symbols, in order to facilitate the input, we can make a belong to their own "professional symbol" toolbar.

1. Execute "tools → macros → record new macros" command, open the Record New Macro dialog box, enter the macro name  such as "Fuhao1" and save the macro in the Personal Macro Workbook, and then OK to start recording. Select the Relative Reference button on the Record Macro toolbar, and then enter the special symbol you want into a cell, and then click the Stop button on the Record Macro toolbar to complete the recording of the macro.

Follow the above operation, recording the other special symbols of the input "macro".

2. Open the Customize dialog box (see Figure 1), in the Toolbars tab, click the New button, pop the new Toolbar dialog box, enter a name-"professional symbol", and then, when you are sure, a toolbar appears in the workspace.

Switch to the Commands tab, select Macros under Categories, and then drag the custom button item under commands to the professional symbols bar (how many buttons to drag with the number of special symbols).

3. Select one of the "custom buttons" and name them after the 1th of the 2nd secret.

4. Right-click on a Named button, in a subsequent pop-up shortcut menu, select the "Specify Macros" option, open the "Specify Macros" dialog box, select the appropriate macros (such as FUHAO1, etc.), to determine the exit.

Repeat this action to link the button to the corresponding macro.

5. Close the Customize dialog box, and then use the Professional Symbols toolbar (Figure 3) to quickly enter professional symbols in a cell, just as you would with a normal toolbar.

Figure 3