Excel table Basic Operations

　　Perhaps you have completed hundreds of financial statements in Excel, perhaps you have used Excel functions to achieve thousands of complex operations, you may think that Excel is too much, or even new. but the countless times we routinely use the handy method is just 1% of all Excel skills. This topic from excel in some of the little-known skills to get a glimpse of the different aspects of Excel style.

　　Let different types of data be displayed in different colors

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. When the settings are complete, press the "OK" button.

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

　　Second, set up 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.

　　Third, 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.

　　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.

　　Let data be sorted on demand

If you want to sort the employees by the department they are in, what do they do if the information is not in phonetic order or in the order of the Strokes? You can sort by a custom sequence.

1. Execute tools → options command, open the Options dialog box, in the Custom Sequence tab, in the box below "input sequence", enter the sequence of departmental sequencing (e.g. "agency, fleet, workshop, workshop, workshop," etc.), and click the Add and OK buttons to exit.

2. Select any cell in the Department column to perform the data → sort command. Opens the Sort dialog box, clicks the Options button, pops up the Sort Options dialog box, presses the Drop-down button, selects the custom sequence, and returns the two times OK button, and all the data is sorted as required.