How does Excel work?

Let different types of data display in different colors
In the payroll, if you want to let the total wages of more than or equal to 2000 yuan be displayed in "Red", the total wage of more than or equal to 1500 yuan is shown in "Blue", the total wages below 1000 yuan are shown in "Brown", others are shown in "Black", we can set this.
1. Open the Payroll worksheet, 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 set the other criteria (greater than or equal to 1500, the font is set to "blue" or less than 1000, and the font is set to "Brown").
3. When the settings are complete, press the OK button.
Look at the salary table, the payroll data is not according to your requirements in different colors show up.

Ii. establishing a classification drop-down list fill item
We often have to enter the name of the enterprise into the table, in order to maintain the consistency of the name, using the "Data validation" function to build a classification drop-down list padding items.
1. In Sheet2, the company name is set up in different columns, such as "Industrial Enterprise", "Commercial enterprise", "individual enterprise" and so on, to establish an enterprise name database.
2. Select Column A (the "Industrial Enterprise" name is the column), in the "Name" field, enter the "industrial Enterprise" character, press "enter" key to confirm.
Follow the above operation, will B, C ... The column is named "Commercial enterprise", "Individual enterprise" ...
3. Switch to Sheet1, select the column for which you want to enter enterprise category (for example, column C), execute the data → effectivity command, and 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", "Business enterprise", "Individual enterprise" ... Sequence (separated by commas between the elements) to determine exit.
Select the column for which you want to enter the enterprise name (such as column D), open the Data Validation dialog box, select the Sequence option, enter the formula in the Source box: =indirect (C1), and determine exit.
4. Select any cell in column C (such as C4), click the drop-down button on the right, and select the appropriate enterprise category to fill in the cell. Then select the cell's D-column cell (such as D4) and click the drop-down button to populate the cell with the desired enterprise name from the list of enterprise names for the appropriate category.
Tip: When printing a report later, if you don't want to print the Enterprise Category column, you can hide the column by selecting it, right-clicking it, and selecting the Hide option.
Iii. Creating a new menu of "common documents"
Create a new common document menu on the menu bar to add frequently used workbook documents to it for easy invocation at any time.
1. Right-click in the blank of the toolbar and select the Customize 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 in the Name box in the pop-up menu, such as common documents.
2. Select an item under category (for example, insert option), select an item under command on the right (such as Hyperlink option), drag it to a new menu (common document), name it (such as "payroll", etc.), and create the first workbook document list name.
Repeat the above action to add a few more document List names.
3. Select a menu item in the Common Documents menu (such as payroll), right-click, and on the shortcut menu that pops up, select the Assign hyperlink → open option to open the Assign Hyperlink dialog box. Navigate to the appropriate workbook (such as the Payroll. xls) folder and select the workbook document by pressing the drop-down button to the right of the look in area.
Repeat the above action to hyperlink the menu item and the workbook document that corresponds to it.
4. When you need to open a workbook document from the Common Documents menu later, simply expand the Common Documents menu and click the appropriate option.
Tip: Although we dragged the hyperlink option to the common Documents menu, it does not affect the functionality of the Hyperlink menu item in the Insert menus and the Insert Hyperlink button on the Standard toolbar.
Iv. making the Professional Symbols toolbar
When editing a professional form, it is often necessary to enter some special professional symbols, in order to facilitate the input, we can make a own "professional symbols" toolbar.
1. Execute the "tools → macros → record new macro" command, open the "Record New Macro" dialog box, enter a 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 finish recording the macro.
Follow the above operation and record the input "macro" of other special symbols.
2. Open the Customize dialog box, in the Toolbars tab, click the New button, pop Up the new Toolbar dialog box, enter the name-"professional symbol", and then a toolbar appears in the workspace.
Switch to the Commands tab, select Macros under Categories, 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" to name them in the 1th of the 2nd trick.
4. Right-click on a Named button, and in the shortcut menu that pops up, select the "Assign Macro" option, open the "Assign Macro" dialog box, select the appropriate macro (such as FUHAO1, etc.), and then confirm the exit.
Repeat this step to link the button to the corresponding macro.
5. Close the Customize dialog box and use the Professional Symbols toolbar to quickly enter professional symbols into a cell, just like you would with a normal toolbar.
V. Save multiple printed pages with the viewport manager
Some worksheets, often need to print the different areas, with the "visual manager" bar.
1. Open the worksheet you want to print, drag and drop the rows (or columns) that you don't need to print, select them and then right-click, and then, in the shortcut menu that appears, choose the Hide option to hide the rows (or columns) that you don't need to print.
2. Execute the view → viewport manager command, open the Viewport Manager dialog box, click the Add button, pop up the Add Viewport dialog box, enter a name (for example, on report), and click the OK button.
3. Display the hidden rows (or columns) and repeat the above actions, "add" the other print view.
4. In the future, when you need to print a form, open the Viewport manager, select the name of the table you want to print, click the Show button, the worksheet is instantly displayed in a pre-defined interface, simple setup, layout, press the Print button on the toolbar, and everything is OK.
Six, let the data on-demand sorting
If you want to sort employees by their department, what if the information is not in alphabetical order or in stroke order? You can sort by custom sequences.
1. Execute the "format → options" command, open the "Options" dialog box, enter the "Custom Series" tab, enter the sequence of department sequencing in the box under "input sequence" (e.g. "authorities, fleet, one workshop, two workshops, three workshops", etc.), click the "Add" and "OK" button to exit.
2. Select any cell in the Department column, perform the data → sort command, open the Sort dialog box, click the Options button, pop Up the Sort Options dialog box, press the drop-down button, select the sequence you just customized, press the OK button two times to return, and all the data is sorted as required.

Seven, the data completely hidden.
The contents of the worksheet part of the cell do not want the browser to look up, but it is hidden.
1. Select the cell (area) where you want to hide the content, execute the format → cells command, open the Format Cells dialog box, select the Custom option under category in the Number tab, and then enter ";;;" (semicolon in three English states) in the box below the "type" on the right.
2. Switch to the "Protection" tab, select the "Hide" option, and press the "OK" button to exit.
3. Execute the "tools → protection → protect Sheet" command, open the "Protect Sheet" dialog box, set the password, "OK" to return.
After this setting, the contents of the above cells are no longer displayed, that is, the use of Excel's transparency function is not allowed to appear.
Tip: Under the Protection tab, do not clear the ∨ in the check box in front of the lock, which prevents others from deleting data that you have hidden.
Eight, Jean Cheng, English input method to appear intelligently
When editing the table, some cells to enter in English, some cells to enter Chinese, repeatedly switching input method is not convenient, why not set up a bit, let the input method intelligently adjust it?
Select the range of cells that you want to enter in Chinese, execute the data → validation command, open the Data Validation dialog box, switch to the Input Mode tab, press the drop-down button to the right of mode, select the Open option, and then OK to exit.
The Chinese Input method (the 1th Chinese input method in the IME list) is automatically opened when any cell in the range of cells in Chinese is selected and the Chinese input method is automatically closed when other cells are selected.
Nine, let "AutoCorrect" enter a unified text
Do you often worry about inputting certain text, such as the computer report? then look down.
1. Execute the tools → AutoCorrect command to open the AutoCorrect dialog box.
2. In the box under Replace, enter "PCW" (or other characters, "PCW" in lowercase), enter "computer newspaper" in the box under "Replace with", and then click the "Add" and "OK" buttons.
3. In the future, if you need to enter the above text, just enter the "PCW" character? At this point, you can not consider the "PCW" case?, and then confirm it.
X. Customizing functions in Excel
Excel functions are rich, but do not meet all of our needs. We can customize a function to accomplish some specific operations. Below, we will come to define a function to calculate the trapezoid area:
1. Execute the tools → macro →visual basic Editor menu command (or press the "ALT+F11" shortcut) to open the VisualBasic editing window.
2. In the window, execute the "Insert → module" menu command and 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)/2end function
4. Close the window and the custom function is complete.
You can use custom functions in the same way you would use built-in functions.
Tip: Functions that are customized using the methods described above are usually only available in the appropriate workbook.
Xi. Picture below the table
The background that is added to the worksheet is lined up underneath the entire sheet, can it only be lined under the table header?
1. Execute the "format → sheet → background" command, open the "Sheet Background" dialog box, select the picture you want as the background, and press the "Insert" button to place the picture underneath the entire sheet.
2. While holding down the CTRL key, drag with the mouse over cells (areas) that do not need to be lined up, and select the cells (regions).
3. Press the drop-down button to the right of fill color on the Formatting toolbar, and then in the palette that appears, select White. After this setting, the left cell is lined with a picture, and there is no picture underneath the selected cell (area) (in fact, the picture is obscured by "white").
Hint? The picture below the cell is not supported for printing.
12. Combine text with the hyphen "&"
If we want to combine the contents of multiple columns into a single column, without the use of a function, a small hyphen "&" can be done (this assumes that the B, C, and D columns are merged into one column).
1. Insert two empty columns (E, f columns) after column D, and then enter the formula in cell D1: =b1&c1&d1.
2. Select the D1 cell again, and use the fill handle to copy the above formula into the cell under column D, and the contents of columns B, C, and D are merged into the corresponding cells in the E column.
3. Select the E column, perform the copy operation, and then select the F column, perform the edit → paste selective command, open the Paste Selective dialog box, select the value option in it, press the OK button, and the contents of the E column (not the formula) are copied into column F.
4. Delete the columns B, C, D and E to complete the merger.
Tip: Complete steps 1th and 2, the merge effect has been implemented, but if you delete columns B, C, and D, the formula will be wrong. Therefore, the 3rd step must be done to convert the formula to a constant "value".

Health Score Bar
Often friends ask "how to print the score bar" Such a problem, a lot of people take the record macro or VBA method to achieve, this for beginners have some difficulty. For this reason, I am here to give a simple way to implement the function.
This assumes that the student score is saved in the A1 to G64 cell area of the SHEET1 worksheet, where the 1th behavior title, the 2nd behavioral discipline name.
1. Switch to the SHEET2 worksheet, select A1 Cell, enter the formula: =if (ROW (), 3) =0,″″,if (0MOD? ROW (), 3 (=1,sheet1! Aū,index (sheet1! $A: $G, INT ((ROW () +4)/3) +1), COLUMN ())).
2. Select the A1 cell again and use the fill handle to copy the above formula to the B1 to G1 cell, and then select the A1 to G1 cell range, and use the fill handle to copy the above formula into the A2 to G185 cell.
At this point, the results of the basic molding, the following simple decoration.
3. After adjusting the row height and column widths, select the A1 to G2 range (1th Student's score area), press the drop-down button to the right of the Formatting toolbar border, and in the list of borders that appears, select the All Lines option to add a border to the selected area (if no border is required, This step and the following are not possible).
4. Select the A1 to G3 range, click the Format Painter button on the Standard toolbar, then hold down the left mouse button and drag from A4 to the G186 cell area to add a border to all the score bars.
Press the Print button to print the score bar.
14, Excel to help you choose the function
When working with data in a function, it is often not known what function is appropriate. The "search function" feature in Excel can help you narrow down the range and pick out the right functions.
Execute the Insert → function command, open the Insert Function dialog box, enter requirements (such as count) in the box under search for functions, and click the Go button, and the system immediately picks up the functions related to count and appears in the list box under Select function. You can quickly identify the functions you need by looking at the relevant help files together.
At the same time, view data in multiple cells on different worksheets
Sometimes when we edit a sheet (SHEET1), we need to see other worksheets (Sheet2, Sheet3 ...). The contents of a cell can be implemented using the Excel Watch Window feature.
Execute the view → toolbars → Watch window command, open the Watch window, click the Add Watch button in it, expand the Add Watch dialog box, select the cell you want to view with the mouse, and then click the Add button. Repeat the preceding actions to add additional "watch points".
Later, you can view the data and related information in all the monitored point cells, whenever you open the Watch window, regardless of the worksheet.
16. Quickly draw a border for a cell
In previous versions of Excel 2002, adding a border to a range of cells was a hassle, and Excel 2002 was a new extension of this feature.
Click the drop-down button to the right of the border on the Formatting toolbar, and in the drop-down list that pops up, select the drawing border option, or execute the view → toolbars → borders command to expand the Borders toolbar.
Click the drop-down button at the leftmost side of the toolbar, select a border style, and then drag in the range of cells where you want to add a border to quickly frame the corresponding range of cells.
Tip: ① If you draw the wrong border, check the "Erase Border" button on the toolbar and drag it over the wrong border to clear the wrong border. ② If you need to draw a different color border, you can first press the Line Color button on the right side of the toolbar, then select the desired color in the palette that pops up and then draw the border. ③ This function can also draw diagonal slashes in the cell.

           6>

17. Control the length of specific cell input text
Can you imagine when you fill a four-digit cell with a two-digit number, or you enter a number in the cell of the input text, Excel can automatically judge, instantly analyze, and pop up a warning, that would be great! It is not difficult for Excel to achieve this functionality.
For example, we position the cursor in a cell that registers "year", and we want the "year" to be represented by a four-digit number in order to facilitate the unification and calculation of the input. So, we can click the "effectivity" option on the "Data" menu. Select text length in the Allow drop-down menu of the set card validity condition. Then select equals in the Data drop-down menu, and length is 4. At the same time, we come to the "error warning" card, set "error warning when invalid data is entered" to "Stop", and fill in the "title" and "Error message" Fields "input text is illegal!" and "Please enter a four-digit year." Words
Obviously, when someone doesn't enter a four-digit number in the cell, Excel will pop up a warning dialog to show you the reason for the error and continue typing until you enter the correct "style" value. Is that amazing? In fact, in Excel "Data validity" judgment, there are many special types of data formats are optional, such as "text type" Ah, "sequence size" Ah, "time is near" ah, if you are interested, why not take the liberty to design a test standard, let your Excel show a different luster.
18. Fixed cells filled with more than one table
We know that every time you open Excel, the software always opens more than one worksheet by default. As a result, Excel has a powerful single-sheet processing capability, which is better suited to coordinate work across multiple interrelated tables. To coordinate the association, of course, you need to synchronize the input first. Therefore, in many cases, you will need to enter the same content in the same cell in multiple tables at the same time.
So how do you make group edits to a table? First we click the label name "Sheet1" of the first sheet, and then hold down the SHIFT key and click the label name "Sheet3" of the last table (if we want the associated table to be not together, you can press and hold the CTRL key to click). At this point, we see that the name "Workgroup" appears on the title bar of Excel, so that we can work on the editing of the workgroup. In a cell that needs to enter more than one table at a time, we find that the same location for all the tables in the workgroup shows the corresponding content.
However, just synchronizing the input is not enough. For example, we need to change the format of data in the same position in multiple tables. First, we have to change the data format of the first table, click the Fill option on the Edit menu, and then select "to same group sheet" in its submenu. At this point, Excel will pop up the "Fill Group Sheet" dialog, where we select the "format" item, after the point "OK", the data format for all tables in the same group is changed.
19. Change the case of text
In Excel, the most powerful support for table processing and data operations is not a formula, nor a database, but a function. Instead of thinking that the functions in Excel are only for numbers, Excel has special functions for editing them as long as they are written into the table. For example, change the case of text.
In Excel2002, there are at least three functions available for text case conversions. They are: "=upper (source data)", convert all text to uppercase, "=lower (source data)", convert all text to lowercase, "=proper (source data)", the text to "appropriate" case, such as the first letter of each word is uppercase, and so on. For example, we enter the lowercase "Excel" in the A1 cell of a table, and then enter "=upper (A1)" In the target cell, and the result will be "Excel" after the carriage return. Similarly, if we enter "Mr.weiwei" in cell A3, and then we enter "=proper (A3)" In the target cell, we will get the result "Mr.weiwei".
20. Extracting specific characters from a string
In addition to direct input, extracting specific character input from existing cell content is definitely a time-saving and easy way to do so, especially for information that is similar to some styles, such as employee lists, hometown, etc.
If we want to quickly extract the appellation from the A4 cell, it is best to use the "=right (source data, extracted characters)" function, which means "extract 2 characters from the rightmost character of the A4 cell" input to this location. Of course, if you want to extract your name, you'll use the "=left (number of characters extracted from the source)" function. In another case, we do not start with the left and right ends, but instead directly extract several characters from the middle of the data. For example, if we want to extract the "Wuhan" two words from A5 cell, we just need to enter "=mid (a5,4,2)" in the target cell. This means: Two characters after the 4th character are extracted in cell A5, that is, 4th and 52 words.

21. It is a complicated problem to convert cardinal words into ordinal words by converting the cardinal words into ordinal words. Because it does not have a very fixed pattern: most of the numbers are used as the "th" suffix, but the numbers that end with "1", "2", and "3" are terminated with "St", "nd" and "rd" respectively. Moreover, the 3 numbers "11", "12", and "13" are different, but they still end with the words "th". Therefore, the implementation seems to be very complex. In fact, as long as we clear the idea, to find a quasi-function, only need to write a formula, it can be easily converted. Do not believe, see: "=a2&if (or (a2,2) ={11,12,13}), ″th″,if (or (Value (A2)) ={1,2,3,},choose (right (A2), ″st″ , ″nd″,″rd″), ″th″) ". The formula, though a long string, but the meaning is very clear: ① If the number is "11", "12", "13", then add the "th" suffix; ② if the 1th principle is invalid, check the last number, use "St" at the end of "1", use "nd" at End of "2", End with "3" Rd ③ if the 1th and 2 principles are not valid, then the "th" is used. Therefore, the conversion of radix and ordinal words is implemented so easily and quickly.
22. The number of digits with special symbols
People who have dealt with finance know that there is a conventional "safe-filling" approach to book filling, which is to fill the void in the amount, or add a "$" symbol to the front of the payment data. In fact, there are similar input methods in Excel, that is, the "REPT" function. Its basic format is "=rept (" special symbol ", padding number)".
For example, if we want to fill 16 bits with "#" at the end of the number in cell A2, we only need to change the formula to "= (A2&rept (″#″,16-len (A2))"), and if we want to fill the number in the A3 cell from the left with a "#" number to 16 bits, change to " =rept (″#″,16-len (A3))) &a3 "; In addition, if we want to use the" # "number to fill the values in A4 from both sides, you need to change to" =rept (″#″,8-len (A4)/2) &a4&rept (″#″) 8- LEN (A4)/2) "; If you are not professional enough to add the" $ "symbol to the A5 cell number, change to:" = (text (a5,″$#,# #0.00″ (&rept (″#″,16-len 0.00″))) ", must be able to meet your requirements.
23. Create a text histogram
In addition to repeating the input, another derivative application of the "REPT" function is the ability to create a histogram of plain text directly on the worksheet. Its principle is also very simple, is the use of the special symbol of the intelligent repetition, according to the calculation results in the specified cell shows the comparison effect of different lengths.
For example, we first make an annual balance sheet, and then use "e column" as the display area of the "budget" month in the histogram, and "column G" as the "Super budget" display area in the histogram. The "N" character of the "Wingdings" font is then represented by the value of the "column D" in the table that already has the result. The steps are as follows:
Write the formula "=if (D30,rept (″n″,round (d3*100,0), ″″)" in cell E3, and drag the fill handle to G14. As we can see, a plain text histogram without the use of Excel chart features has been presented, easy and intuitive, simple and straightforward.
24, calculate the total number of words in the cell
Sometimes, we may be interested in the number of characters in a cell, and we need to calculate the total word count in the cell. To solve this problem, the "TRIM" function is used to remove spaces, in addition to the virtual computations of the "substitute" function. For example, now the A1 cell is entered with "How manywords?" Words, then we can use the following expressions to help:
"=if (LEN (A1) =0,0,len (Trim (A1))-len (Substitute (TRIM (A1), ″,″,″″)) +1)"
The meaning of the formula is to use the "substitute" function to create a new string, and the "TRIM" function to remove the space between the characters, and then calculate the string and the number of the original string difference, so that the number of "space", and finally the number of spaces +1, the number of characters in the cell.
25, about the conversion of the euro
This is the new tool in Excel 2002. If you choose the default mode when you install EXCEL2002, you probably won't be able to find it in the Tools menu. However, we can select Add-ins from the Tools menu, and then tick the "Euro tools" option in the pop-up window, and Excel 2002 will install itself after "OK".
Once we're done, we'll open the Tools menu again, click on "Euro Conversion", and a separate window for currency conversions for the euro and EU Member States appears. As with other function windows in Excel, we can set the "source area" and "target area" of the currency conversion with the mouse, then select different currencies before and after the conversion. The "100 euros" is shown to be converted into a comparison list of the other currencies of the EU Member States respectively. Of course, to make the euro display more professional, we can also click the "Euro" button on the Excel toolbar so that all converted currency values are in the euro style.

How does Excel work?