Excel table 25 strokes must learn the secret

Let different types of data be displayed in different colors

In the payroll, if you want to make the total wages greater than or equal to 2000 dollars to show "red", greater than

Wages equal to 1500 yuan in the "blue" show, less than 1000 of the total wages to "brown"

Display, other "black" display, we can set this.

1. Open the Payroll workbook, select the column for payroll, and execute the format → condition

Format command, open the Conditional Formatting dialog box. Click the Drop-down button to the right of the second box to select

In the "greater than or equal to" option, enter the value "2000" in the following box. Click Format to

button, open the Format Cells dialog box, and set the font color to red.

2. Press "Add" button, and imitate the above operation set other conditions (greater than or equal to 1500, word

The body 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 want to enter the name of the enterprise into the table, in order to maintain the consistency of the name, using the "number

According to validity "feature has built a category Drop-down list fill item.

1. In Sheet2, the name of the enterprise is classified by category (e.g. "Industrial enterprise", "Commercial enterprise", "

Body Enterprise ", etc.), enter different columns, and establish a database of enterprise names.

2. Select Column A (the name of the "Industrial Enterprise"), and in the Name column, enter "industrial enterprises

Industry "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 columns (such as column C) that need to be entered in the Enterprise category, and execute the number

by → Effectivity Command, open the Data Validation dialog box. In the Settings tab, click the acceptable

Xu "to the right of the Drop-down button, select the" Sequence "option, in the following" Source "box, enter the" work

Industry Enterprise "," Commercial enterprise "," Individual enterprise "... Sequence (separated by commas between the elements),

OK exit.

Select the column (such as column D) where you want to enter the enterprise name, and then open the Data Validation dialog box.

When the sequence option is selected, enter the formula in the Source box: =indirect (C1), and make sure to exit.

4. Select any cell in column C (such as C4), click the Right Drop-down button, select the appropriate "Enterprise class

Don't "fill in the cell." Then select the cell in column D (such as D4) that corresponds to it, and click drop down to

button, you can fill in the desired enterprise name from the list of enterprise names in the appropriate 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 the mouse, and then 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 your favorite workbook documents to it.

Easy to call 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 under command, the new

menu, drag to the menu bar.

Press the Change Selection button to enter a name in the naming box in the pop-up menu (such as "often

With document ").

2. Select one of the items below "categories" (such as Insert option), and under command on the right

Select an item (such as the hyperlink option), drag it to a new menu (commonly used document), and imitate the above

Make a name for it (such as "payroll", and so on), and create the first workbook document List names.

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

From 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 pay. xls, by pressing the Drop-down button to the right of the look in range.

, and so on) folder, 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, expand the often

With the document menu, click the appropriate option.

Tip: Although we dragged the hyperlink option to the common document menu, it doesn't shadow

On the Insert menu, click Hyperlink menu item and insert Hyperlink on the Standard toolbar

Button's function.

Four, the production of "professional symbol" toolbar

When editing a professional form, you often need to enter some special professional symbols for easy input.

We can make a "professional symbol" toolbar that belongs to you.

1. Execute "tools → macros → record new macros" 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 the

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, and in the Toolbars tab, click the New button to play

Out of the New Toolbar dialog box, enter a name-"professional symbol", and after that, the workspace

A tool bar appears in.

Switch to the Commands tab, select Macros under Categories, and then under commands

The custom button item is dragged onto the professional symbols bar (how many buttons are dragged by the number of special symbols).

3. Select one of the "custom button", modeled after the 2nd secret secret of the 1th to their lives

Name.

4. Right-click on a Named button, and then, on the shortcut menu that pops up, select the "Specify Macros" option,

Open the Specify Macros dialog box, select the appropriate macro (such as FUHAO1, and so on), and make sure to exit.

Repeat this action to link the button to the corresponding macro.

5. Close the Customize dialog box, which you can use as a normal toolbar, using the professional

Symbol toolbar to quickly enter professional symbols into a cell.

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 needs to be printed, drag the mouse over the line (or column) that does not need to be printed, and select

In the shortcut menu that appears next to the right mouse click, select the "Hide" option and will not need to hit

The printed rows (or columns) are hidden.

2. Execute the view → face manager command, open the Face Manager dialog box, and click Add

Plus button, pops up the Add Face dialog box, enter a name (such as "Report on"), and then click

OK button.

3. Display the hidden rows (or columns) and repeat the above operation, "add" good other print

Visual surface.

4. When you need to print some form later, open the View manager and select the form you want to print

Name, click the "Show" button, the worksheet is immediately displayed in the pre-set interface, simply set

Place, typesetting, press the toolbar on the "Print" button, everything is OK.

Let data be sorted on demand

If you want to sort the employees by the department they are in, the information about these department names is not

Is the phonetic order, also not in the order of strokes, how to do? You can sort by a custom sequence.

1. Execute the format → Options command, open the Options dialog box, and go to the custom sequence

tab, in the box below "input sequence", enter a sequence of departmental orders (e.g. "organ, motorcade,

Workshop, two workshop, three workshop "etc.", click the "Add" and "OK" button to exit.

2. Select any cell in the Department column, perform the data → sort command, and open the row

Order dialog box, click the Options button, pop-up the Sort Options dialog box, and press the Drop-down

button, select the custom sequence, press the "OK" button two times to return, all the data on the required

Rows are sorted.

Seven, completely hide the data

The contents of the cells in the worksheet section do not want the viewer to look up, so you have to hide it.

1. Select the cells (ranges) that you want to hide, and then execute the format → cells command to open

Cell Format dialog box, select the Custom option under categories in the Number tab,

then enter ";;;" In the box below the right "type". (Semicolon in three English states).

2. Then switch to the "Protection" tab, select the "Hide" option, press "OK" button

Exit.

3. Execute the tools → protect → protect sheet command, open the Protect Sheet dialog box,

When the password is set, "OK" returns.

After this setting, the contents of these cells are no longer displayed, that is, using Excel

The transparent function also cannot let it be visible.

Tip: Under the Protection tab, do not clear the "∨" number in the check box before locking.

This will prevent others from deleting your hidden data.

Eight, Jean Cheng, English input method of intelligent appearance

When you edit a table, you have to type in English in some cells, in some cells, in Chinese,

Complex switching input method is really inconvenient, why not set up, let the input method intelligently adjust it?

Select the range of cells you want to enter in Chinese, perform the data → effectivity command, and open the number

According to the Effectivity dialog box, switch to the "Input mode" tab and press "mode" to the right of the dropdown

button, "OK" to exit after selecting the "Open" option.

When you are elected to enter any cell in the range of cells that you want in Chinese, the Chinese input method

(The 1th Chinese input method in the IME list) opens automatically, when the other cells are selected, the Chinese is lost

The entry method is automatically closed.

Let "AutoCorrect" enter a unified text

Do you often worry about typing some fixed text, such as the "Computer newspaper"? then look down.

it.

1. Perform the tools → AutoCorrect command to open the AutoCorrect dialog box.

2. In the box below "Replace" enter "PCW" (can also be other characters, "PCW" with a small

Write, type "computer newspaper" in the box below "Replace with", and then click "Add" and "confirm

Set button.

3. In the future, if you need to enter the above text, just enter the "PCW" character? At this time can not consider

The "PCW" case? And then make sure it's done.

Ten, custom functions in Excel

Excel functions, although rich, do not meet all of our needs. We can customize a

function to perform some specific operations. Below, we will come from the definition of a letter to compute the trapezoidal area

Number:

1. Execute "tools → macros →visual Basic Editor" menu commands (or press "ALT+F11" quick

Czech key), open the VisualBasic edit window.

2. In the window, execute the Insert → module menu command and insert a new module-Module 1.

3. In the Right code window, enter the following code:

Function V (a,b,h) v = h* (a+b)/2end function

4. Close the window and complete the custom function.

You can use custom functions in the future just as you would with built-in functions.

Tip: Functions that are customized with the above method are usually available only in the appropriate workbook.

Xi. picture below the head of the table

The background that is added to the worksheet, which is lined under the entire worksheet, can only be lined under the header

It?

1. Perform the format → sheet → background command, open the Sheet Background dialog box, and select

When you need a picture as a background, press the Insert button to put the picture underneath the entire worksheet.

2. While holding down the CTRL key, drag the mouse over the cell (area) that does not need the lining picture.

Select the cells (ranges) as well.

3. Press the Drop-down button on the right side of the fill color on the Formatting toolbar, followed by the "Tune

Palette, select White. After this setting, the left cell is lined up below the figure

, and the above selected cell (area) is not lined with pictures (in fact, the picture is "white"

Covered up).

Hint? The picture below the cell is not supported for printing.

12, with the hyphen "&" to merge text

If we want to combine the contents of multiple columns into one column, we don't need to take advantage of a function, a small

The character "&" will be able to fix it (this assumes that columns B, C, and D are merged into one column).

1. Insert two empty columns after column D (E, F), 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 formula above to the cell below column D

The contents of columns B, C, and D are merged into the corresponding cells in column E.

3. Select the E column, perform the copy operation, and then select column F to perform edit → selective sticky

Paste command, open the Paste Selective dialog box, select the value option, and press the

Set button, the contents of the E column (not the formula) are copied to column F.

4. Delete the columns B, C, D and E and complete the work of merging.

Tip: Complete steps 1th and 2, the merge effect has been implemented, but if you delete B, C,

Column D, the formula will appear incorrectly. Therefore, the 3rd step is required to convert the formula to the invariant "value".

Health Score Bar

Often friends ask "how to print the score bar" Such a problem, many people take the record macro or VBA

method to achieve, which for beginners is a certain degree of difficulty. In view of this, I am here to give

A simple method to realize using function.

This assumes that student scores are saved in the A1 to G64 range of the SHEET1 worksheet, where

1th Act title, 2nd behavioral subject name.

1. Switch to the SHEET2 worksheet, select the A1 cell, and enter the formula: =if (MOD (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 formula to the B1 to G1 cell;

Then, select the A1 to G1 cell range, and use the fill handle to copy the formula to A2 to

G185 in cells.

At this point, the results of the basic molding, the following simple modification.

3. After adjusting the row height and column width, select the range of A1 to G2 (the 1th student's score

Area), press the Drop-down button to the right of the border of the Formatting toolbar, and then the list of borders that appear

, select the all Borders option to add a border to the selected area (if you do not need a border, you can not

Do this and the following actions).

4. Select A1 to G3 cell, click Format Brush on the Standard toolbar

button, then hold down the left mouse button and drag from the A4 to the G186 cell area, adding for all the score bars

Border.

Press the "Print" button to print the score bar.

14, Excel to help you select functions

When using a function to process data, it is often not known what function is more appropriate. Excel's Search

Functions can help you narrow down the range and pick out the right function.

To perform the Insert → function command, open the Insert Function dialog box, under Search functions

Side of the box to enter the requirements (such as "count"), and then click the "Go" button, the system will immediately and "meter

Number "related functions are selected and displayed in the list box under Select function." And then combine it.

Look at the relevant help file to quickly determine the function you want.

See data in multiple cells on different worksheets at the same time

Sometimes when we edit a worksheet (SHEET1), we need to look at other worksheets (Sheet2,

Sheet3 ...) The contents of a cell, you can use the "Watch Window" feature of Excel to implement it.

Execute the view → toolbars → Watch window command, open the Watch window, and click the

Add Watch button, expand the Add Watch dialog box, and select the cells you want to view with the mouse

Grid, click the Add button. Repeat the previous operation and add other "watch points".

In the future, whenever you open the Watch window on any worksheet, you can view all of the

Data and related information within the viewpoint cell.

16. Quickly draw a border for cell

In previous versions of Excel 2002, adding borders to a range of cells was cumbersome,

Excel 2002 is a new extension of this functionality.

Click the Drop-down button to the right of the border on the Formatting toolbar, and then eject the Drop-down list

, select the Draw Border option, or perform the view → toolbar → border command, expand Edge

Box toolbar.

Click the left-most Drop-down button on the toolbar, select a border style, and then add a border when you want

Cell range, you can quickly draw a border over the range of cells.

Tip: ① If you draw the wrong border, it doesn't matter, select the Erase Border button on the toolbar,

Then drag on the wrong border to clear out the wrong border. ② If you need to draw a

The same color of the border, you can press the toolbar on the right side of the line Color button, the subsequent pop-up of the palette

Select the desired color in the board, and then draw a border. ③ This feature can also be used in a cell to draw the

Angle Slash.

17, control the length of specific cell input text

Can you imagine that when you enter a four-digit cell, you fill in a two-digit number, or in the

When you enter a number in a cell of text, Excel can automatically judge, instantly analyze, and

Pop-up warning, that would be a good ah! To achieve this, it is not difficult for Excel.

For example, we position the cursor in a cell that registers "year", in order to enter the uniform and

The calculation is convenient, we want the "year" to be expressed in a four-digit number. So, we can click

The effectivity option for the Data menu. "Allow" in "set" card "validity condition"

Select text Length from the Drop-down menu. Then select equals in the Data Drop-down menu, and

"Length" is "4". In the meantime, we go to the "error warning" card to "enter invalid data

Error warning "Set to stop" and fill in the title and error messages columns separately

"Enter text illegal!" and "Please enter a four-digit year." Words

Obviously, when someone enters a cell that is not a four-digit number, Excel plays

Show a warning dialog box that tells you the cause of the error and until you enter the correct "style" value

can continue to input. Is it amazing? In fact, in the "Data validity" judgment of Excel, there are many special

Different types of data format optional, such as "text type" Ah, "sequence size" Ah, "time near"

Ah, if you are interested, why not take the liberty to design a test of your own, so that your Excel show

A different kind of luster.

18. Fixed cells filled with multiple tables

We know that every time you open Excel, the software always opens more than one worksheet by default. From this we can see

Excel has the ability to handle a powerful sheet of tables, and it's better suited to work in multiple interrelated tables

Transfer work. To reconcile an association, of course, you need to synchronize input first. So, in many cases, it will

You need to enter the same content in the same cell in more than one table at the same time.

So how do you make group edits to tables? First, we click the label name of the first worksheet

Sheet1, 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 hold down the CTRL key to select it. At this point, we see Excel

The name on the title bar appears with the word "workgroup," and we can work on the team's editors

Done. To write something in a cell that needs to be typed in more than one table at a time, we find that "work

As a group, all tables in the same position are displayed accordingly.

However, just synchronizing input is far from enough. For example, we need to have the same bits in multiple tables

Data uniform change format What should I do? 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 of worksheets in its submenu.

At this point, Excel pops up the "fill in a group of Worksheets" dialog box, where we select "format" a

Item, the data format for that location is changed for all tables in the same group after the point "OK."

19, change the case of text

In Excel, the most powerful support for table processing and data operations is not a formula, nor

database, but a function. Do not think that the function in Excel is only for numbers, in fact, as long as it is written into

The contents of the table, Excel has a special function to edit it. For example, change the case of text.

In Excel 2002, there are at least three functions available for text capitalization conversions. They were

Yes: "=upper (source)" To convert all text to uppercase; =lower (source data lattice) ",

Convert all text to lowercase; =proper (source data lattice) ", convert text to" appropriate "large

lowercase, such as making the first letter of each word uppercase, and so on. For example, we're in the A1 cell of a table

Enter lowercase Excel, and then enter "=upper (A1)" In the target cell, and get

The result will be "EXCEL". Similarly, if we enter "Mr.weiwei" in Cell A3,

Then we enter "=proper (A3)" In the target cell, and the result will be

"Mr.weiwei".

20. Extract specific characters from a string

In addition to direct input, extracting specific character input from existing cell contents is definitely a

Save time and easy way, especially for some of the same style of information, such as employee list,

Origin and other information.

If we want to quickly extract the title from cell A4, it's best to use the "=right" (Source data

Lattice, the number of characters extracted) "function, which represents" extracts 2 from the rightmost character of cell A4

Character into this location. Of course, if you want to extract the name, you want to use the "=left" (Source number

According to the lattice, extract the number of characters) "function up. There is also a situation where we do not start from the left and right ends, but

Extracts several characters directly from the data center. For example, we want to extract the "Wuhan" from the cell A5

Word, just type "=mid" in the target cell (a5,4,2) is all you need. It means: in A5

The two characters after the 4th character is extracted in the cell, that is, the 4th and the first 52 words.

21, converting cardinal words into ordinal numbers is a comparison between the cardinal words in English and the ordinal number words.

Complex problem. Because it does not have a very fixed pattern: most numbers become ordinal words

Is the use of the "th" suffix, but generally the "1", "2", "3" is the end of the number is

End of "St", "nd" and "rd". Moreover, the 3 digits "11", "12", "13"

It's different, but they still end with "th". As a result, the implementation seems complicated. In fact

As long as we clear our mind and find the function, we just need to write a formula that can be easily converted. Don't believe,

See: "=a2&if (OR (a2,2) ={11,12,13}), ″th

″,if (OR (A2) ={1,2,3,},choose (Right (A2), ″st″,″nd″,″

Rd″), ″th″) ". Although the formula is a long string, the meaning is clear: ① If the number is

"11", "12", "13" at the end, then add the "th" suffix; ② if the 1th principle is invalid,

Then check the last number, use "St" at the end of "1", and use "ND" at the end of "2" to

"3" ends with "rd"; ③ if the 1th and 2 principles are not valid, then use "th". So

The conversion of cardinal words and ordinal numbers is realized so easily and quickly.

22, with special symbols to complement the number of digits

People who have dealings with finance know that there is a conventional "safe fill" in the book Filling

method, which is to make up the vacancy in the amount, or to precede the sum of money with a character such as "$"

Resolution In fact, there are similar input methods in Excel, that is the "REPT" function. It's basic

The format is "=rept" ("Special symbols", padding digits).

For example, we're going to fill 16 digits with the "#" number at the end of the number in the A2 cell, just

The formula must be changed to "= (A2&rept (″#″,16-len (A2))"; If we're going to A3 the unit

The number in the lattice is padded to 16 digits from the left with a "#" number, which is changed to "=rept" (″#

″,16-len (A3))) &a3 "; In addition, if we want to use the" # "number to fill A4 values from both sides

, it needs to be replaced by "=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

is: "= (text (a5,″$#,# #0.00″ &rept (″#″,16-len text (a5,″$#,# #0.00

″)) ", must be able to meet your requirements.

23. Create a text histogram

In addition to repeating the input, another derivative of the "REPT" function is that it can be used directly on the worksheet

A histogram consisting of plain text is created in. Its principle is also very simple, is the use of special symbols of the intelligent

Repeat, the results of the calculation in the specified cell show a different length of comparison effect.

For example, we first make an annual balance sheet, and then "E column" as a histogram of the pre

In the display area of the "month", the "G column" is used as the "super budget" display area in the histogram. Then the root

According to the table already has the result "D column" The numerical value, uses "the Wingdings" the font "N" the character to appear.

The specific steps are as follows:

Write the formula in cell E3 "=if (d30,rept (″n″,round (d3*100,0)), ″″)",

Also drag the fill handle to G14. We see a plain text histogram that doesn't use Excel chart features

Has shown the immediate, convenient and intuitive, simple and clear.

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 number of words. To solve this problem, in addition to using the virtual computation of the "substitute" function,

You also use the TRIM function to remove spaces. For example, now A1 cells are entered with the "how many

Words? " Words, then we can use the following expressions to help:

"=if (A1) =0,0,len (Trim (A1))-len (Substitute (Trim (A1), ″,″,″

″)) +1) "

The meaning of the formula is to create a new string with the "substitute" function and use the "TRIM"

function to delete the spaces between the characters, and then compute the digit difference between the string and the original string, thereby

The number of "spaces", and finally the number of spaces +1, we can get the number of characters in the cell.

25. On the conversion of the euro

This is the new tool in Excel 2002. If you select the default when you install Excel 2002

, you probably won't find it in the Tools menu. However, we can first select "Work

With the "Add-Ins" menu, and then check the Euro tool option in the pop-up window, OK

Then Excel 2002 installs itself.

Once we're done, we'll open the Tools menu again, click Euro Conversion, a separate, dedicated

The window for euro and EU currency conversion has emerged. Another function window with Excel is a

, we can set the source area and target area of currency conversion by mouse, and then select

Choose the different currency before and after the conversion. What is shown here is "100 euros" converted into EU Member States

A comparison list of his currency. Of course, in order to make the euro show more professional, we can also click Excel

The Euro button on the toolbar so that all converted currency values are in the euro style.