35 Little-Known Secret Tricks of Excel [1]

Thread Content

I. Creating categorized dropdown list options 　　We often need to enter company names into tables, and to ensure consistency in these names, we used the “Data Validation” function to create categorized dropdown list options. 　　1. In Sheet2, enter the company names into separate columns according to their category (such as “industrial enterprises”, “commercial enterprises”, “individual businesses”, etc.) to create a database of company names. 　　2. Select Column A (the column containing the names of industrial enterprises). In the “Name” field, enter the characters “Industrial Enterprises”, then press the “Enter” key to confirm. 　　Following the same steps, name columns B, C, and so on as “Commercial Enterprises”, “Individual Enterprises”, etc. 3. Switch to Sheet1, select the column in which the “Enterprise Type” information is to be entered (such as column C), and then execute the “Data→Data Validation” command to open the Data Validation dialog box. In the “Settings” tab, click the dropdown button to the right of “Allow”, select the “Sequence” option, enter “Industrial enterprises”, “Commercial enterprises”, “Individual enterprises”… in the “Source” field (separating each element with a comma), and then click OK to exit. 　　Select again the column in which the company name needs to be entered (such as Column D), then open the \"Data Validation\" dialog box. Choose the \"Sequence\" option and enter the formula =INDIRECT(C1) in the \"Source\" field, then click OK to exit. 　　4. Select any cell in Column C (such as C4), click the dropdown button on the right, and choose the appropriate \"Enterprise Category\" to enter in the cell. Then select the cell in column D corresponding to that cell (such as D4), click the drop-down button, and choose the desired company name from the list of company names in that category to enter into the cell. 　　Tip: When printing reports in the future, if you don’t need to print the “Enterprise Category” column, you can select that column, right-click on it, and choose the “Hide” option to hide it. 　　II. Create a new “Frequently Used Documents” menu Add the commonly used workbook documents to this new menu on the toolbar, so they can be accessed easily at any time. 　　1. Right-click on an empty area of the toolbar and select the “Customize” option to open the “Customize” dialog box. In the “Commands” tab, select the “New Menu” option under “Categories”, then drag the “New Menu” located under “Commands” to the menu bar. 　　Click the “Change Selected” button, and enter a name in the “Name” field of the pop-up menu (such as “Frequently Used Documents”). 　　2. Next, select any option under “Category” (such as the “Insert” option), then select any option under “Command” on the right side (such as the “Hyperlink” option). Drag it to the new menu (Common Documents), and name it in a similar manner to the previous one (such as “Payroll Sheet”), thus creating the name for the first workbook document list. 　　Repeat the above steps to add a few more document list names. 　　3. Select a menu item from the “Common Documents” menu (such as “Payroll Sheet”, etc.), right-click the mouse, and in the shortcut menu that appears, choose the option “Assign Hyperlink → Open” to bring up the “Assign Hyperlink” dialog box. By clicking the dropdown button to the right of \"Search Range\", navigate to the folder containing the corresponding workbook (such as \"Salary.xls\" etc.), and select that workbook document. 　　Repeat the above steps to create hyperlinks between menu items and the corresponding workbook documents. 　　4. In the future, when you need to open a specific workbook document from the “Frequently Used Documents” menu, simply expand that menu and click on the corresponding option. 　　Tip: Although we have dragged the “Hyperlink” option to the “Frequently Used Documents” menu, this does not affect the functionality of the “Hyperlink” menu item in the “Insert” menu or the “Insert Hyperlink” button on the “Quick Access” toolbar. III. Display different types of data in different colors In a salary sheet, if we want to display salaries that are 2000 yuan or more in “red”, those that are 1500 yuan or more in “blue”, those that are less than 1000 yuan in “brown”, and all other amounts in “black”, we can set it up in this way. 　　1. Open the “Payroll” workbook, select the column containing the “Total Salary”, and execute the “Format → Conditional Formatting” command to open the “Conditional Formatting” 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 box that appears. Click the “Format” button to open the “Cell Format” dialog box, and set the “Color” of the “Font” to “Red”. 　　2. Click the “Add” button and set the other conditions in the same way as above (greater than or equal to 1500, font set to “blue”)” ； Less than 1000: set the font to “brown”). 　　3. After setting it up, press the “OK” button. 　　Take a look at the pay sheet – are the total salary figures displayed in different colors as you requested? 　　IV. Creating a “Special Symbols” Toolbar When editing professional tables, it is often necessary to enter certain special symbols used in that field. To make entry easier, we can create our own “Special Symbols” toolbar. 　　1. Execute the command “Tools→Macros→Record New Macro” to open the “Record New Macro” dialog box. Enter a macro name, such as “fuhao1”, and save the macro in the “Personal Macros Workbook”, then click “OK” to start recording. Select the \"Relative Reference\" button on the \"Record Macro\" toolbar, then enter the desired special symbol in a cell, and finally click the \"Stop\" button on the \"Record Macro\" toolbar to complete the recording of the macro. 　　Following the same procedure, record the input “macros” for the other special symbols one by one. 　　2. Open the “Customize” dialog box. In the “Toolbars” tab, click the “New” button; this will bring up the “Create New Toolbar” dialog box. Enter the name “Professional Symbols”, and after clicking OK, a toolbar will appear in the workspace. 　　Switch to the “Commands” tab, select “Macros” under “Categories”, and drag the “Custom Buttons” item under “Commands” to the “Special Symbols” bar (drag as many buttons as there are special symbols). 　　3. Select one of the “custom buttons” and name them following point 1 from the second trick. 　　4. Right-click on a named button; in the shortcut menu that appears, select the “Assign Macro” option. This will open the “Assign Macro” dialog box, where you can select the desired macro (such as fuhao1, etc.), and then click OK to exit. 　　Repeat this step to link the button to the corresponding macro. 　　5. Close the “Custom” dialog box; from now on, you can use the “Special Symbols” toolbar just like a regular toolbar to quickly enter special symbols into cells. 　　V. Saving Multiple Print Pages Using the “View Manager” For some worksheets, it is often necessary to print different sections of them; use the “View Manager” for this purpose. 　　1. Open the worksheet that needs to be printed, use the mouse to select the rows (or columns) that should not be printed by dragging over them; once they are selected, right-click and choose the “Hide” option from the shortcut menu that appears, thereby hiding the rows (or columns) that do not need to be printed. 　　2. Execute the “View→View Manager” command to open the “View Manager” dialog box. Click the “Add” button, which will bring up the “Add View” dialog box. Enter a name (such as “Report Above”) and then click the “OK” button. 　　3. Show the hidden rows (or columns) and repeat the above steps to \"add\" additional print views. 　　4. When you need to print a certain form in the future, open the “View Manager”, select the name of the form you want to print, and click the “Display” button. The worksheet will then be displayed according to the preset layout. After making some simple adjustments and formatting, simply click the “Print” button on the toolbar, and that’s it. VI. Sorting data as needed 　　If you want to sort employees by the departments they belong to, and the names of these departments are not ordered alphabetically or based on the number of strokes, what should you do? You can use a custom sequence for sorting. 　　1. Execute the “Format→Options” command to open the “Options” dialog box. Go to the “Custom Sequence” tab, enter the sequence for department sorting in the box below “Enter Sequence” (such as “Office, Fleet, Workshop 1, Workshop 2, Workshop 3”, etc.), then click the “Add” and “OK” buttons to exit. 　　2. Select any cell in the “Department” column, then run the “Data→Sort” command to open the Sort dialog box. Click the “Options” button, which will bring up the Sort Options dialog box. Use the dropdown menu there to select the sequence you defined earlier, and click “OK” twice to return. All the data will then be sorted as requested. 　　VII. Completely hide the data If you don’t want viewers to see the content in certain cells of a worksheet, you have to hide it. 　　1. Select the cell (or range) whose content you want to hide, then go to \"Format→Cell\" to open the \"Cell Format\" dialog box. Under the \"Number\" tab, select \"Custom\" in the \"Category\" section, and then enter the desired value in the box under \"Type\" on the right side“ ； ； ； ”(Three semicolons in English format). 　　2. Switch to the “Protection” tab, select the “Hide” option, and click the “OK” button to exit. 　　3. Execute the “Tools→Protection→Protect Sheet” command to open the “Protect Sheet” dialog box; after setting the password, click “OK” to return. 　　After making such settings, the content in the aforementioned cells no longer appears; even using Excel’s transparency feature cannot make it visible. 　　Tip: Under the “Protection” tab, do not remove the “∨” symbol from the checkbox next to “Lock”, as this will prevent others from deleting the data you have hidden. 　　VIII. Enable intelligent switching between Chinese and English input methods When editing tables, some cells require input in English while others need input in Chinese; switching between input methods repeatedly is quite inconvenient. Why not set things up so that the input methods can adjust automatically? Select the range of cells where Chinese text needs to be entered, then go to “Data→Data Validation”. In the “Data Validation” dialog box, switch to the “Input Method” tab. Click the drop-down button next to “Mode”, select the “Open” option, and then click “OK” to exit. 　　From now on, whenever any cell within the cell range where Chinese input is required is selected, the Chinese input method (the first Chinese input method in the list of input methods) will be activated automatically, while it will be deactivated automatically when other cells are selected. 　　IX. Use “Auto-Correct” to enter consistent text Are you often troubled by having to type certain fixed texts, such as those from the Computer Newspaper? Then keep reading. 　　1. Execute the “Tools→AutoCorrect” command to open the “AutoCorrect” dialog box. 　　2. Enter “pcw” in the box below “Replace” (any other characters can be used as well; “pcw” should be in lowercase), enter “《Computer Newspaper》” in the box below “With”, then click the “Add” and “OK” buttons. 　　3. In the future, if you need to enter the above text, just type the “pcw” characters; the case of “pcw” does not matter at this point, and confirmation will complete the process. X. Customizing Functions in Excel Although Excel offers a wide range of functions, they may not meet all of our needs. We can define a function to perform some specific calculations. Next, we will customize a function to calculate the area of a trapezoid: 1. Execute the menu command “Tools→Macros→Visual Basic Editor” (or press the shortcut key “Alt+F11”) to open the Visual Basic editor window. 　　2. In the window, execute the “Insert → Module” menu command to 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)/2 End Function 　　4. Close the window; the custom function is now created. 　　In the future, custom functions can be used just like built-in functions. 　　Tip: Functions customized using the above method can usually only be used in the corresponding workbook. 　　11. Add a picture under the table header The background added to a worksheet covers the entire worksheet; is it possible to have it cover only the area under the table header? 1. Execute the “Format → Worksheet → Background” command to open the “Worksheet Background” dialog box. Select the image that is to be used as the background, then click the “Insert” button to place the image under the entire worksheet. 　　2. While holding down the Ctrl key, use the mouse to drag over the cells (areas) that do not require a background image, thereby selecting those cells (areas). 　　3. Click the dropdown button to the right of \"Fill Color\" on the \"Format\" toolbar; in the palette that appears, select \"White\". After making such settings, a picture is placed beneath the remaining cells, while no picture is placed beneath the selected cells (areas) (in fact, the picture is covered by “white”). 　　Tip: Images placed below cells cannot be printed. 　　XII. Combining text using the hyphen “&” If we want to combine the contents of multiple columns into one, there is no need to use functions; a simple hyphen “&” is sufficient to achieve this (here, it is assumed that columns B, C, and D are combined into one column). 　　1. Insert two empty columns (E and F) after column D, then enter the formula =B1+C1&D1 in cell D1. 　　2. Select cell D1 again, and use the fill handle to copy the aforementioned formula to the cells below column D; thus, the contents of columns B, C, and D will be combined into the corresponding cells in column E. 　　3. Select column E and perform the “Copy” operation; then select column F and use the “Edit→Paste Special” command. The “Paste Special” dialog box will appear, where you should select the “Numbers” option and click “OK”. The content of column E (not the formulas) will be copied to column F. 　　4. Delete columns B, C, D, and E to complete the merging process. 　　Tip: By completing steps 1 and 2, the merging effect is achieved; however, if columns B, C, and D are deleted at this point, the formulas will produce errors. Therefore, Step 3 must be carried out to convert the formula into an immutable “value”. 　　13. Quickly printing student grade reports Frequently, friends ask questions like “how to print grade reports.” Many people use macros or VBA to achieve this, but it can be somewhat difficult for beginners. For this reason, I present here a simple method implemented using functions. 　　It is assumed here that the students’ grades are stored in the cell range A1 to G64 of Sheet1, where the first row contains the titles and the second row contains the names of the subjects. 　　1. Switch to Sheet2, select cell A1, and enter the formula: =IF(MOD(ROW(),3)=0,„“,IF(0 MOD ROW(),3,“=1,sheet1!” A$2, INDEX(sheet1! $A: $G, INT((((ROW())+4)/3)+1), COLUMN())). 　　2. Select cell A1 again, and use the fill handle to copy the above formula to cells B1 through G1 ； Then, select the range of cells A1 to G1 at the same time, and use the Fill Handle to copy the aforementioned formula to cells A2 to G185. 　　At this point, the progress bar is basically shaped; now let’s make some minor adjustments to it. 　　3. After adjusting the row height and column width, select the range of cells from A1 to G2 (the area containing the grades for the first student). Click the drop-down button on the right side of the \"Borders\" option on the \"Format\" toolbar. From the list that appears, choose the \"All Borders\" option to add borders to the selected area (you can skip this step and the steps below if no borders are desired). 　　4. Select the range of cells from A1 to G3 at the same time, click the \"Format Painter\" button on the \"Common\" toolbar, then hold down the left mouse button and drag from A4 to the range of cells G186, in order to add borders to all the score bars. 　　Press the “Print” button to print out the grade bar. 　　14. Excel to Help You Choose Functions When working with functions to process data, it’s often difficult to decide which function is the most suitable to use. Excel’s “search function” feature can help you narrow down the options and select the appropriate function. 　　Execute the \"Insert → Function\" command to open the \"Insert Function\" dialog box. Enter the desired term in the box below \"Search for functions\" (for example, \"Count\"), then click the \"Go\" button. The system will immediately select the functions related to \"Count\" and display them in the list box below \"Select Functions\". By also consulting the relevant help documents, it is possible to quickly determine the required function. 15. Viewing data in multiple cells across different worksheets simultaneously Sometimes, when editing a certain worksheet (Sheet1), we need to view the content of a specific cell in other worksheets (Sheet2, Sheet3, etc.). The “Watch Window” feature in Excel can be used to achieve this. 　　Execute the command “View→Toolbar→Monitor Window” to open the Monitor Window; click the “Add Monitor” button within it, which will bring up the “Add Monitoring Point” dialog box. Select the cell you wish to monitor using the mouse, and then click the “Add” button. Repeat the previous steps to add additional \"monitoring points\". 　　From now on, no matter which worksheet it is, by opening the “Monitoring Window”, one can view the data and related information in all the monitored cell units.