wholesale high quality diamond jewelry 1. Let different types of data be displayed in different colors In the salary table, if you want to make the total salary greater than 2,000 yuan displayed in "red", the total wages greater than 1500 yuan are displayed in "blue", low low, low low, low low, low low, low low, low low, low low, low low, low low, low low, low low, low, low, low, low, low, low. The total salary of 1,000 yuan is displayed in "brown". Others are displayed in "black", we can set it like this. 1. Open the "Salary Table" workbook, select the "total salary", execute the "format → condition format" command, and open the "conditional format" dialog box. Click the drop -down button on the right side of the second box, select the "greater than or equal to" option, and enter the value "2000" in the back box. Click the "Format" button to open the "unit format" dialog box and set the "color" of "font" to "red". 2. Press the "Add" button and imitate the operation above to set other conditions (greater than equal to 1500, the font is set to "blue"; less than 1000, the font is set to "brown"). 3. After setting, press the "OK" button. In look at the salary table, is the data of the total salary displayed in different colors according to your requirements.
. Establishing a classification drop -down list Fill in We often enter the name of the enterprise into the table. In order to maintain the consistency of the name, we use the "data effectiveness" function to build a classification. Fill in the drop -down list. 1. In SHEET2, enter different columns in different columns according to the category (such as "industrial enterprises", "commercial enterprises", "individual enterprises", etc., and establish a corporate name database. 2. Select column A (the name of the "Industrial Enterprise"). In the "Name" column, after entering the "Industrial Enterprise" character, press the "Enter" key to confirm. If imitation the above operations, namely B, C ... columns are named "commercial enterprises", "individual enterprises" ... 3. Switch to sheet1, select the column of "enterprise category" in the selection (Such as column C), execute the "data → effective" command, and open the "data validity" dialog box. In the "Settings" label, click the drop -down button on the right side of "Allow", select the "Sequence" option, and enter "Industrial Enterprise", "Commercial Enterprise", "individual enterprises" ... … The sequence (separate in English comma between each element) to determine the exit. In again, select a column (such as column D) that needs to be entered, and then open the "data validity" dialog box. After selecting the "Sequence" option, enter the formula in the "Source" box: = Indirect (C1) (C1) , Determine withdrawal. . Select Cycles C4 (such as C4), click the down pull button on the right, and select the corresponding "Enterprise Category" to fill in the cell. Then select the C -column cell (such as D4) corresponding to the cell, and click the drop -down button to fill in the required enterprise name from the corresponding class name list. Tips: When printing the report in the future, if you do not need to print the "Enterprise Category" column, you can select the column, right -click the mouse, select the "Hidden" option, and hide the column. . Establish a new menu of "Common Documents" The new "Common Documents" menu on the menu bar, add the commonly used workbook document to it, so that it is convenient to call at any time. 1. Right -click the mouse in the blank space of the toolbar, select the "Custom" option, and open the "Custom" dialog box. In the "command" label, select the "new menu" item under the "category", and then drag the "new menu" below the "command" to the menu bar. Plip the "Selected content" button and enter a name (such as "Common Documents") in the "Naming" box of the pop -up menu. 2. Select one item (such as the "insert" option) under the "category"), and select one item (such as the "hyperlink" option) under the "command" on the right, drag it to the new menu ( In common documents), and imitate the above operations, name it (such as "salary table", etc.), and establish the first workbook document list name. repeat the above operation, add a few more document list names. 3. Select a menu item (such as "salary table", etc.) in the "Common Document" menu, right -click the mouse, and in the pop -up fast menu, select the "All link of the hyperlink → open" option, open the "allocation of allocation" Hyperlink "dialog box. By pressing the drop -down button on the right side of "Finding range", position the corresponding workbook (such as "salary .xls", etc., and select the workbook document. Repeat the above operation to lin the menu item and the workbook document hyperlinks corresponding to it. . When you need to open a workbook document in the "Common Document" menu in the future, just open the "Common Documents" menu and click the corresponding options. Tip: Although we dragged the "hyperlink" option to the "Common Document" menu, it does not affect the "hyperlink" menu item in the "insert" menu "The function of the button. . Making "Professional Symbol" toolbar In editing professional forms, some special professional symbols are often required. In order to facilitate the input, we can make a "professional symbol" toolbar that belongs to its own. 1. Execute the "Tool → Macro → Recording New Macro" command, open the "Recording New Macro" dialog box, enter the macro name? For example, "Fuhao1"? Determine "Start recording. Select the "relative reference" button on the "Recording Macro" toolbar, then enter the required special symbols into a cell, and then click the "Stop" button on the "Record Macro" toolbar to complete the macro recording. If in the operation above, record the input "macro" of other special symbols one by one. 2. Open the "Custom" dialog box. In the "Toolbar" label, click the "New" button to pop up the "New Toolbar" dialog box, enter the name- "Professional Symbol", after confirmation,, after confirmation, That is, a tool bar appears in the work area. The switch to the "command" label, select the "macro" below "category", drag the "custom button" item below "command" to the "professional symbol" column (how many special symbols are dragged by dragging it if it is dragged on How many buttons). 3. Select one of the "Custom Buttons", and name them in the first point of the second secret. 4. Right -click the naming button. In the shortcut menu that pops up, select the "Specify Macro" option, open the "Specific Macro" dialog box, select the corresponding macro (such as Fuhao1, etc.), determine the exit exit Essence Repeat this step operation to link the button to the corresponding macro. 5. Turn off the "Custom" dialog box. In the future, you can use the "Professional Symbol" toolbar like a normal toolbar to quickly enter the professional symbols to the cell. 5. Save multiple printing pages with "Vision Manager" The worksheets often need to print different areas, and use the "noodle manager". 1. Open the worksheet that needs to be printed. Use the mouse to drag on the line (or column) that does not need to be printed. Select them to right -click the mouse. In the subsequent shortcut menu, select the "Hidden" option. Hide the rows (or columns) that do not need to be printed. 2. Execute the "View → Vision Manager" command, open the "Noodle Manager" dialog box, click the "Add" button, pop up the "Add View Noodle" dialog box, and enter a name (such as "above" above After the report "), click the" OK "button. 3. Show the hidden line (or column), and repeat the above operations, "add" other print vision. . When you need to print a certain form in the future, open the "Facial Manager", select the name of the form that needs to be printed, and click the "Display" button. Set up, typesetting, press the "Print" button on the toolbar, everything is OK. 6. Let data sort on demand If you want to sort the employees according to the department where their departments are located, the relevant information of these departments is not in the order of pinyin or the order of strokes. What should I do? Sort with custom sequences. 1. Execute the "Format → Options" command, open the "Options" dialog box, enter the "custom sequence" tag, and enter the sequence of the sequence in the box under the "Enter the Sequence" (such as the "agency, the agency, the agency, the agency, the agency, the agency, the agency, the agency, the agency, the authority, The team, the first workshop, the second workshop, the third workshop ", etc.), click the" Add "and" OK "buttons to withdraw. 2. Select any cell in the "department" column, execute the "data → sorting" command, open the "Sort" dialog box, click the "Options" button, pop up the "Sorting Options" dialog box, press in it The drop -down button, select the customized sequence just now, press the "OK" button to return twice, and sort all data as required.
Seven. Thoroughly hide the data The content in some cells in the worksheet does not want to make a viewer, so I have to hide it. 1 1. Select the cell (area) that requires hidden content, execute the "format → cell" command, open the "cell format" dialog box, and select "customized" below the "classification" of the "digital" label Options, then enter the box under the "type" on the right ";;;;" (segment number in three English states). 2. Switch to the "Protection" label, select the "Hidden" option, and exit by pressing the "OK" button. 3. Execute the "Tools → Protection → Protection Workheet" command, open the "Protection Workheet" dialog box, set the password, and return to "OK". In this settings, the content of the above cells is no longer displayed, that is, using Excel's transparent function cannot make it shape. Tip: Under the "protection" label, please do not clear the "∨" number in the check box in front of the "Lock", so as to prevent others from deleting the data you hidden. 8. Let the Chinese and English input methods intelligently appear When editing forms, some cells need to enter English, and some cells need to enter Chinese. , Why not set it up and let the input method be intelligently adjusted? In selection of the cell area that needs to be entered in Chinese, execute the "data → effective" command, open the "data validity" dialog box, switch to the "input method Under the "mode" label, press the drop -down button on the right side of "Mode", select the "Open" option, and exit "OK". Is when elected in the cell that needs to enter Chinese, when a cell in the cell that needs to be entered at any cell, the Chinese input method (the first Chinese input method in the input method list) is automatically opened. Close automatically. Nine. Let "Automatic Correction" enter the unified text Do you often worry about entering some fixed texts, such as "Computer News"? Then look down. 1. Execute the "Tools → Automatic Correction" command to open the "Automatic Correction" dialog box. 2. Enter "PCW" in the box below "replacement" (can also be other characters, "PCW" uses a lowercase), enter the "Computer News" in the box below "Replacement to" Then click the "Add" and "OK" buttons. 3. If you need to enter the above text in the future, just enter the "PCW" character? At this time, you can consider the "PCW" applied applied, and then confirm that it will be done. X. Custom function in Excel although the excel function is rich, it does not meet all our needs. We can customize a function to complete some specific operations. Below, we will define a function that calculates a trapezoidal area: 1. Execute the "Tool → Macro → Visual Basic Editor" menu command (or press the "ALT F11" shortcut key) to open the Visual Basic editing window. 2. In the window, execute the "Insert → Module" menu command, 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)/2nd function 4. Close The window, the custom function is completed. It after using a built -in function, you can use a custom function like a built -in function. The prompt: The customized function of the above method can usually only be used in the corresponding workbook. 11. The picture under the head of the head The background added to the worksheet, is lined with the entire worksheet. Can you just set it under the head? 1. Execution of execution The "Format → Workheet → Background" command, open the "Workheet Background" dialog box, select the picture that needs to be used as the background, press the "Insert" button to set the picture under the entire worksheet. . While holding down the CTRL key, use the mouse to drag in the cell (area) that does not need to lined the picture, and select these cells (regions) at the same time. 3. Press the drop -down button on the right side of the "Filling Color" on the "Format" toolbar. In the subsequent "palette", select "White". After such settings, a picture is lined under the element of the unit, and there is no picture under the selected cell (area) above (in fact, the picture is covered by "white"). This prompts? The picture lined under the cell does not support printing. . Twelve, use the character "
wholesale high quality diamond jewelry 1. Let different types of data be displayed in different colors
In the salary table, if you want to make the total salary greater than 2,000 yuan displayed in "red", the total wages greater than 1500 yuan are displayed in "blue", low low, low low, low low, low low, low low, low low, low low, low low, low low, low low, low low, low, low, low, low, low, low. The total salary of 1,000 yuan is displayed in "brown". Others are displayed in "black", we can set it like this.
1. Open the "Salary Table" workbook, select the "total salary", execute the "format → condition format" command, and open the "conditional format" dialog box. Click the drop -down button on the right side of the second box, select the "greater than or equal to" option, and enter the value "2000" in the back box. Click the "Format" button to open the "unit format" dialog box and set the "color" of "font" to "red".
2. Press the "Add" button and imitate the operation above to set other conditions (greater than equal to 1500, the font is set to "blue"; less than 1000, the font is set to "brown").
3. After setting, press the "OK" button.
In look at the salary table, is the data of the total salary displayed in different colors according to your requirements.
. Establishing a classification drop -down list Fill in
We often enter the name of the enterprise into the table. In order to maintain the consistency of the name, we use the "data effectiveness" function to build a classification. Fill in the drop -down list.
1. In SHEET2, enter different columns in different columns according to the category (such as "industrial enterprises", "commercial enterprises", "individual enterprises", etc., and establish a corporate name database.
2. Select column A (the name of the "Industrial Enterprise"). In the "Name" column, after entering the "Industrial Enterprise" character, press the "Enter" key to confirm.
If imitation the above operations, namely B, C ... columns are named "commercial enterprises", "individual enterprises" ...
3. Switch to sheet1, select the column of "enterprise category" in the selection (Such as column C), execute the "data → effective" command, and open the "data validity" dialog box. In the "Settings" label, click the drop -down button on the right side of "Allow", select the "Sequence" option, and enter "Industrial Enterprise", "Commercial Enterprise", "individual enterprises" ... … The sequence (separate in English comma between each element) to determine the exit.
In again, select a column (such as column D) that needs to be entered, and then open the "data validity" dialog box. After selecting the "Sequence" option, enter the formula in the "Source" box: = Indirect (C1) (C1) , Determine withdrawal.
. Select Cycles C4 (such as C4), click the down pull button on the right, and select the corresponding "Enterprise Category" to fill in the cell. Then select the C -column cell (such as D4) corresponding to the cell, and click the drop -down button to fill in the required enterprise name from the corresponding class name list.
Tips: When printing the report in the future, if you do not need to print the "Enterprise Category" column, you can select the column, right -click the mouse, select the "Hidden" option, and hide the column.
. Establish a new menu of "Common Documents"
The new "Common Documents" menu on the menu bar, add the commonly used workbook document to it, so that it is convenient to call at any time.
1. Right -click the mouse in the blank space of the toolbar, select the "Custom" option, and open the "Custom" dialog box. In the "command" label, select the "new menu" item under the "category", and then drag the "new menu" below the "command" to the menu bar.
Plip the "Selected content" button and enter a name (such as "Common Documents") in the "Naming" box of the pop -up menu.
2. Select one item (such as the "insert" option) under the "category"), and select one item (such as the "hyperlink" option) under the "command" on the right, drag it to the new menu ( In common documents), and imitate the above operations, name it (such as "salary table", etc.), and establish the first workbook document list name.
repeat the above operation, add a few more document list names.
3. Select a menu item (such as "salary table", etc.) in the "Common Document" menu, right -click the mouse, and in the pop -up fast menu, select the "All link of the hyperlink → open" option, open the "allocation of allocation" Hyperlink "dialog box. By pressing the drop -down button on the right side of "Finding range", position the corresponding workbook (such as "salary .xls", etc., and select the workbook document.
Repeat the above operation to lin the menu item and the workbook document hyperlinks corresponding to it.
. When you need to open a workbook document in the "Common Document" menu in the future, just open the "Common Documents" menu and click the corresponding options.
Tip: Although we dragged the "hyperlink" option to the "Common Document" menu, it does not affect the "hyperlink" menu item in the "insert" menu "The function of the button.
. Making "Professional Symbol" toolbar
In editing professional forms, some special professional symbols are often required. In order to facilitate the input, we can make a "professional symbol" toolbar that belongs to its own.
1. Execute the "Tool → Macro → Recording New Macro" command, open the "Recording New Macro" dialog box, enter the macro name? For example, "Fuhao1"? Determine "Start recording. Select the "relative reference" button on the "Recording Macro" toolbar, then enter the required special symbols into a cell, and then click the "Stop" button on the "Record Macro" toolbar to complete the macro recording.
If in the operation above, record the input "macro" of other special symbols one by one.
2. Open the "Custom" dialog box. In the "Toolbar" label, click the "New" button to pop up the "New Toolbar" dialog box, enter the name- "Professional Symbol", after confirmation,, after confirmation, That is, a tool bar appears in the work area.
The switch to the "command" label, select the "macro" below "category", drag the "custom button" item below "command" to the "professional symbol" column (how many special symbols are dragged by dragging it if it is dragged on How many buttons).
3. Select one of the "Custom Buttons", and name them in the first point of the second secret.
4. Right -click the naming button. In the shortcut menu that pops up, select the "Specify Macro" option, open the "Specific Macro" dialog box, select the corresponding macro (such as Fuhao1, etc.), determine the exit exit Essence
Repeat this step operation to link the button to the corresponding macro.
5. Turn off the "Custom" dialog box. In the future, you can use the "Professional Symbol" toolbar like a normal toolbar to quickly enter the professional symbols to the cell.
5. Save multiple printing pages with "Vision Manager"
The worksheets often need to print different areas, and use the "noodle manager".
1. Open the worksheet that needs to be printed. Use the mouse to drag on the line (or column) that does not need to be printed. Select them to right -click the mouse. In the subsequent shortcut menu, select the "Hidden" option. Hide the rows (or columns) that do not need to be printed.
2. Execute the "View → Vision Manager" command, open the "Noodle Manager" dialog box, click the "Add" button, pop up the "Add View Noodle" dialog box, and enter a name (such as "above" above After the report "), click the" OK "button.
3. Show the hidden line (or column), and repeat the above operations, "add" other print vision.
. When you need to print a certain form in the future, open the "Facial Manager", select the name of the form that needs to be printed, and click the "Display" button. Set up, typesetting, press the "Print" button on the toolbar, everything is OK.
6. Let data sort on demand
If you want to sort the employees according to the department where their departments are located, the relevant information of these departments is not in the order of pinyin or the order of strokes. What should I do? Sort with custom sequences.
1. Execute the "Format → Options" command, open the "Options" dialog box, enter the "custom sequence" tag, and enter the sequence of the sequence in the box under the "Enter the Sequence" (such as the "agency, the agency, the agency, the agency, the agency, the agency, the agency, the agency, the agency, the authority, The team, the first workshop, the second workshop, the third workshop ", etc.), click the" Add "and" OK "buttons to withdraw.
2. Select any cell in the "department" column, execute the "data → sorting" command, open the "Sort" dialog box, click the "Options" button, pop up the "Sorting Options" dialog box, press in it The drop -down button, select the customized sequence just now, press the "OK" button to return twice, and sort all data as required.
Seven. Thoroughly hide the data
The content in some cells in the worksheet does not want to make a viewer, so I have to hide it.
1 1. Select the cell (area) that requires hidden content, execute the "format → cell" command, open the "cell format" dialog box, and select "customized" below the "classification" of the "digital" label Options, then enter the box under the "type" on the right ";;;;" (segment number in three English states).
2. Switch to the "Protection" label, select the "Hidden" option, and exit by pressing the "OK" button.
3. Execute the "Tools → Protection → Protection Workheet" command, open the "Protection Workheet" dialog box, set the password, and return to "OK".
In this settings, the content of the above cells is no longer displayed, that is, using Excel's transparent function cannot make it shape.
Tip: Under the "protection" label, please do not clear the "∨" number in the check box in front of the "Lock", so as to prevent others from deleting the data you hidden.
8. Let the Chinese and English input methods intelligently appear
When editing forms, some cells need to enter English, and some cells need to enter Chinese. , Why not set it up and let the input method be intelligently adjusted?
In selection of the cell area that needs to be entered in Chinese, execute the "data → effective" command, open the "data validity" dialog box, switch to the "input method Under the "mode" label, press the drop -down button on the right side of "Mode", select the "Open" option, and exit "OK".
Is when elected in the cell that needs to enter Chinese, when a cell in the cell that needs to be entered at any cell, the Chinese input method (the first Chinese input method in the input method list) is automatically opened. Close automatically.
Nine. Let "Automatic Correction" enter the unified text
Do you often worry about entering some fixed texts, such as "Computer News"? Then look down.
1. Execute the "Tools → Automatic Correction" command to open the "Automatic Correction" dialog box.
2. Enter "PCW" in the box below "replacement" (can also be other characters, "PCW" uses a lowercase), enter the "Computer News" in the box below "Replacement to" Then click the "Add" and "OK" buttons.
3. If you need to enter the above text in the future, just enter the "PCW" character? At this time, you can consider the "PCW" applied applied, and then confirm that it will be done.
X. Custom function in Excel
although the excel function is rich, it does not meet all our needs. We can customize a function to complete some specific operations. Below, we will define a function that calculates a trapezoidal area:
1. Execute the "Tool → Macro → Visual Basic Editor" menu command (or press the "ALT F11" shortcut key) to open the Visual Basic editing window.
2. In the window, execute the "Insert → Module" menu command, 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)/2nd function
4. Close The window, the custom function is completed.
It after using a built -in function, you can use a custom function like a built -in function.
The prompt: The customized function of the above method can usually only be used in the corresponding workbook.
11. The picture under the head of the head
The background added to the worksheet, is lined with the entire worksheet. Can you just set it under the head?
1. Execution of execution The "Format → Workheet → Background" command, open the "Workheet Background" dialog box, select the picture that needs to be used as the background, press the "Insert" button to set the picture under the entire worksheet.
. While holding down the CTRL key, use the mouse to drag in the cell (area) that does not need to lined the picture, and select these cells (regions) at the same time.
3. Press the drop -down button on the right side of the "Filling Color" on the "Format" toolbar. In the subsequent "palette", select "White". After such settings, a picture is lined under the element of the unit, and there is no picture under the selected cell (area) above (in fact, the picture is covered by "white").
This prompts? The picture lined under the cell does not support printing.
. Twelve, use the character "