【 Weak current College 】 Excel skills

Maybe you have already done in Excel before hundreds of financial statements, maybe you've been using Excel functions achieve thousands of complex operations, maybe you think it was all about, and even Excel. But our weekdays numerous duplicate handy Excel using method is only 1% of all skills. The topic from Excel in some little-known tricks, glimpse on Excel in different style.
First, enable different types of data displayed in a different color
In payroll, if you want to allow greater than or equal to the total wages of $ 2000 to display the "red", greater than or equal to the total wages of $ 1500 to "blue" displays, which is lower than the total wages of $ 1000 to display the "Brown", the other to the "black" show, we can set up.
1. open the "Payroll" workbook, select the "wage bill" column, execute "format → conditional formatting" command, open the "conditional format" dialog box. Click the second drop-down button on the right side of the box, select the "greater than or equal to" option in the back of the box, enter the value "2000". Click the "format" button, open the "format cells" dialog box, set the "fonts" in the "colors" is set to "red".
2. press the "add" button and follow the above operations set up other conditions (greater than or equal to 1500, the font is set to "blue"; less than 1000, the font is set to "Brown").
3. the Setup is finished, press the "OK" button.
See payroll, payroll data is not according to your requirements displayed in different colors.

Second, the establishment of category drop-down list to populate items
We often want to enter the name of the enterprise to the table, in order to maintain consistency, use the name "data validation" functionality built a category drop-down list to populate the items.
1. in Sheet2, the corporate name by category (e.g., "industrial enterprises" and "business enterprise" and "individual enterprise" and so on), enter a different column, the establishment of a corporate name for the database.
2. Select A column ("industrial enterprises" in the name column), in the "name" field, enter "industrial enterprises" characters, press the "enter" key to confirm.
Follow the above operations, B, C ... Columns are named "business enterprise" and "individual enterprise" ...
3. switch to Sheet1, select enter "enterprise class" column (such as C column), the implementation of the "validity" of data → command, open the "data validation" dialog box. In the "settings" tab, click the "allow" to the right of the drop-down button, select "serial" options, in the following "sources" box, enter "industrial enterprises", "business enterprise", "private enterprise" ... Sequence (each element separated by a comma) and OK to exit.
Then select the need to enter business name column (such as the D column), and then open the "data validation" dialog box, select the "sequence" option in the "from" box, enter the formula: = INDIRECT (C1), determine the exit.
4. Select any cell in column c (C4), click the right-hand drop-down button, select the appropriate "enterprise class" to fill the cell. Then select the cell that corresponds to the D column of cells (D4), click the drop-down button from the appropriate category of the Enterprise name list, select the desired company name into the cell.
Tip: when you print the report later, if you do not need to print "enterprise class" column, you can select the column, right-click, choose "hide" option, the column is hidden.
III. the establishment of a "General document" new menu
In the menu bar to create a new document of the "common" menu, you will commonly used workbook document added to which, conveniently be called at any time.
1. the blank space in the toolbar, right-click, choose "custom" option, open the "Customize" dialog box. In the "commands" tab, select the "category" of the "new menu" key, then the "command" following "new menu" onto the menu bar.
Press the "change selection"button in the pop-up menu in the "named" box, enter a name (e.g. "General documentation").
2. then in the "category" in the following optional item (such as "insert" option), in the right "command" and the following optional item (such as "hyperlink" option), drag it to the new menu (popular document), and follow the above actions are named on it (such as "Payroll", and so on), established the first workbook name the document list.
Repeat the above operation, add several documents list name.
3. Select the "General documentation" menu, a menu item (such as "Payroll", and so on), right-click the shortcut menu, select "assign hyperlink → open" option, open the "assign hyperlink" dialog box. By pressing the "look" to the right of the drop-down button, navigate to the appropriate workbook (.xls as "wages", etc.), and select the workbook in the document.
Repeat the above operation, the menu item and its corresponding workbook document hyperlink.
4. after the need to open the "frequently used documents" menu when a workbook, document, as long as you expand the "General documentation" menu, click the appropriate option.
Tip: Although we will "hyperlink" option onto the "General documentation" menu, but does not affect the "insert" menu "hyperlink" menu item and the "standard" toolbar "Insert Hyperlink" function of the button.
4. make the "symbol" toolbar
When editing a professional table, often need to enter some special professional symbols, in order to make it easier to enter, we can make a own "professional symbol" toolbar.
1. perform "tools → macros → recording new macros" command, open the "record a new macro" dialog box, type the macro name? "as" fuhao1? and save the macro in "aHuman macro workbook ", then" OK "to start recording. Select "recording" toolbar "relative reference" button, and then you will need to enter the special symbols into a cell, and then click the "record a macro," toolbar "stop" button to complete the macro recording.r>Modelled after the above operation, recording good other special symbols to enter the "macro".
2. open the "Customize" dialog box, in the "toolbar" tab, click the "new" button, a pop-up "new toolbar" dialog box, enter the name — the "symbol", OK, that is, a workspace in a ToolStrip.
Switch to the "commands" tab, select the "category" following "macro", the "command" following "custom buttons" item onto the "symbol" bar (the number of special symbols, drag-and-number of buttons).
3. Select one of the "custom buttons", modelled on the 2nd to the 1st point name them.
4. right-click a button named after, in the shortcut menu, select "assign macro" option, open the "assign macro" dialog box, select the appropriate macro (e.g. fuhao1, etc.), determine the exit.
Repeat this step to add a button link with the corresponding macro.
5. turn off the "custom" dialog box, you can use the common toolbar, use the "symbol" toolbar to quickly enter cell. professional symbol
5. use the "view manager" save multiple print page
A work table, it is often necessary to print these different areas, use the "view manager".
1. open the need to print the worksheet, use the mouse to not need to print the row (or column) marked on the tractor, select them and then right-click, then on the shortcut menu that appears, select "hide" option, you will not need to print the row (or column) is hidden.
2. do a "view → view manager" command, open the "view manager" dialog box, click the "add" button, a pop-up "add view" dialog box, enter a name (such as "report"), and then click "OK" button.
3. to hide the rows (or columns) is displayed, and repeat the steps above, "adding" good other print view.
4. the need to print a table later, open the "view manager", select the name of the table you want to print, click the "display" button on the worksheet to immediately set the prior good interface displayed, simple setup, layout, press the toolbar's "print" button, everythingis OK.
6. let the sorting data on demand
If you want to be employees by Department, the Department name is information not by phonetic order nor the press stroke order,? you can use a custom list to sort.
1. the implementation of the "format → options" command, open the "options" dialog box, enter "custom list" tab, in the "input sequences" in the box below to enter the sort sequence of the departments (such as "organs, fleet, a workshop, the second workshop, the workshop", etc.), click "add" and "OK" button to exit.
2. Select "sector" column in any of the cells, sort of "data →" command, open the "sort" dialog box, click the "options" button, a pop-up "sort options" dialog box, press one of the drop-down button, select just the custom sequence, press twice "OK" button to return all the data is sorted according to requirements.