Excel 5 tricks must be learned

　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. In the Commands tab, select the New menu item under Categories, and then drag new menu under Commands to the menu bar.

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 perform the format → conditional formatting command

Perform the format → conditional formatting command

2. When you open the Conditional Formatting dialog box, select the greater than option, enter the value "2000" in the pop-up box, and then select the color you want to set to

Enter a value of "2000" in the pop-up box and select the color you want to set

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

4. When the settings are complete, press the "OK" button. This is the time to view the payroll, payroll data is not according to your requirements in different colors show.

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, 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 to quickly enter professional symbols in a cell, just as you would with a normal toolbar.

　Save multiple printed pages with the "Face Manager"

Some worksheets, often need to print a different area, with the "Face Manager" bar.

1. Open the worksheet that you want to print, drag the mouse over the line (or column) that doesn't need to be printed, select them, and then right-click the mouse, and in the shortcut menu that appears, select the Hide option to hide the rows (or columns) that don't need to be printed.

2. Execute the view → face manager command, open the Face Manager dialog box, click the Add button, pop the Add View dialog box, enter a name (such as "Report on"), and then click OK.

3. Display the hidden rows (or columns) and repeat the above operation, "add" the other print view.

4. When you need to print a form later, open the Face manager, select the name of the table you want to print, click the "Show" button, and the worksheet is immediately displayed in a pre-set interface, simply set, layout, press the "Print" button on the toolbar, and everything is OK.