cooltshirts

Excel Cheats

Perhaps you have had hundreds of Excel to complete the financial statements, perhaps you have been using Excel functions to achieve thousands of complex calculations, you probably think the same about Excel, or even nothing new . However, we repeated countless times daily in handy to use only one per cent of Excel all skills. The topic from the Excel skills of some of the little-known start to enjoy the look of Excel Different customs.
one, so that different types of data using differen

Perhaps you have had hundreds of Excel to complete the financial statements, perhaps you have been using Excel functions to achieve thousands of complex calculations, you probably think the same about Excel, or even nothing new . However, we repeated countless times daily in handy to use only one per cent of Excel all skills. The topic from the Excel skills of some of the little-known start to enjoy the look of Excel Different customs.
one, so that different types of data using different colors
in the payroll, if you want more than equal to the total wage of 2,000 yuan to \is equal to 1,500 yuan, the total wage to \
1. Open the \Click the second box to the right of the drop-down button, select the \Click the \
2. press \
3. set is complete, press the \
see it payroll, payroll data is not in accordance with your request in a different color is displayed.

Second, a category drop-down list filled with items
we often want to enter the business name to form, in order to maintain the name of consistency, use of \
1. in Sheet2 in the Company Name by category (such as \
2. Select A column (\
follow the above operation, the B, C … … column named \If C out), the implementation of \In the \Enterprise \
then need to enter the business name of the selected column (eg column D), and then open the \C1), OK to exit.
4. Select any cell in column C (eg, C4), click the right side of the drop-down button, select the \Then select the cell corresponding column D cell (such as D4), click the drop-down button to the appropriate category business name from the list select the Company Name enter the cell.
Tip: print the report at a later time, if not print the \
Third, establish a \
1. Right-click on blank space in the toolbar, select \In the \
Click \
2. and then in the \(common document), and modeled it for the operation of the above named (such as \
repeat the above operation, add a few more than the name of the document list.
3. Select the \distribution Hyperlink \By pressing the \
repeat the above operation, the menu item and its corresponding workbook and hyperlink documents together.
4. After the need to open the \
Tip: Although we will \\
4, making \
1. implementation of the \\Select the \.
follow the above operation, 11 special symbols to record the input of other good \
2. open the \appear in the workspace of a toolbar.
switch to the \How many buttons).
3. select one of the \
4. Right-click the button after a name in the following pop-up shortcut menu, select \exit.
Repeat this step, the button to link with the corresponding macro.
5. close the \
five, with \
1. Open the need to print a worksheet, use the mouse without the need to print the row (or column) marked drag, select them and then right click in the resulting shortcut menu, select \, will not need to print the row (or column) hidden.
2. implementation of the \\
3. the hidden rows (or columns) are displayed, and repeat the operation, \
4. After the need to print a form, open the \, layout click, click on the toolbar’s \
6, so that data on demand sort
staff if you want to sort according to your department, the name of both of these sectors in accordance with the relevant information is not phonetic order, nor the order by stroke, how do? can use self- defined sort sequence.
1. implementation of the \team, a workshop, two workshops, three workshops, \
2. select the \One of the drop-down button, select the just-defined sequence, by two \

7, the data sheet completely hidden part
cell contents do not want visitors to access, had it hidden.
1. selected to hide the contents of the cell (area), implementation of the \\
2. and then switch to the \
3. implementation of the \
after this set after the contents of the cell is no longer displayed, is the use of Excel’s features can not be allowed to borrow this transparent.
Tip: In the \
8, so in English and Chinese input method appeared
intelligent editing table, some cells to enter the English, and some cells to enter the Chinese repeatedly to switch input method is inconvenient, why not Set a bit and let it automatically adjust input?
selected range of cells to enter the Chinese, the implementation of \tab, click \
later elected to the range of cells that need to input Chinese characters in any one cell, the Chinese input method (IME list, the first a Chinese input method) automatically open, elected in the other cell, the Chinese input method automatically Close.
9, so that \
1. implementation of the \
2. In the \then click \
3. If you need to enter the text later, just enter \
10, a custom function in Excel,
Excel function, though rich, but it does not meet all our needs. We can customize a function to accomplish some specific operations. Here, we define a calculation from the trapezoid area function:
1. Implementation of the \
2. In the window, the implementation of the \
3. in the right \defined function to complete.
can later use the same function as using the built-in custom functions.
Tip: Use the above method of self-defined function is usually only used in the corresponding workbook.
11, table header contrast images
following worksheet to add to the background, is the lining in the whole work sheet below, can not only lined the first in the table below it?
1. implementation of the \Worksheet → Background \
2. While holding down the Ctrl key while using the mouse in the picture without lining cells (area) in drag, and select those cells (area).
3. click \After this set after leaving the cell lining on the picture below, but the selected cell (area) below no contrast image of the (in fact, is the picture being \
tips? lining in the cell does not support the following picture is printed.
12, with a hyphen \get it done (here assumed that the B, C, D out into a column).
1. in the D column, insert two empty columns (E, F series), and then enter the formula in cell D1: = B1 & C1 & D1.
2. D1 cells again selected, use the \
3. Select the E series, the implementation of \, press the \
4. to B, C, D, E series removed, the completion of the merger work.
Tip: Complete step 1,2-operation, combined effect has been achieved, but at this time if you delete B, C, D series, the formula error. Therefore, to carry out step 3, the formula is converted to the same \

friend asked, \For this consideration, I would like to use function gives a simple method to achieve.
here assumes that student achievement save in the Sheet1 worksheet range of cells A1 to G64, which act title 1, 2 acts subject name.
1. Switch to Sheet2 worksheet, select cell A1, enter the formula: = IF (MOD (ROW (), 3) = 0, \, sheet1! Aū, INDEX (sheet1! $ A: $ G, INT (((ROW () +4) / 3) +1), COLUMN ())))。
2. again select cell A1 using the \> Thus, the results basic shape, the following simple modification look.
3. adjust the row height and column width, the same selected range of cells A1 to G2 (1 results section area students), according to \\The following).
4. also selected range of cells A1 to G3, click on the \Add articles results for all of the border.
click \What function is more appropriate. Excel’s \box input requirements (such as \related help files, you can quickly determine the required function.
15, while view different worksheet within multiple cells of data
Sometimes we edit a sheet (Sheet1) is required when View other work in the table (Sheet2, Sheet3 … …) the contents of a cell, you can use Excel’s \\.
later, no matter in which the work table, simply open the \> In previous versions of Excel 2002, add border operations for the range of cells is too much trouble, Excel 2002 This feature has a new expansion.
Click the \pull button, pop-up in the subsequent drop-down list, choose \drop-down button, select a kind of border styles, and then in the range of cells you need to add border in drag, you can quickly draw the corresponding range of cells on the border.
Tip: ① If you draw the wrong frame, does not matter on the toolbar to select \Color \

17, control of specific cell input text length
you can imagine when you enter the four digits of the cell was filled with a digit, or enter text in the cell Enter the number you have the time, Excel will automatically determine, instant analysis and pop-up warning, it nice to ah! To achieve this function, Excel is, it is not difficult.
example, we position the cursor to a register \Therefore, we can click on \In the \Then in the \We then went to \and \
Obviously, when if someone in the cell when the input is not a four-digit, Excel will display a warning dialog box pops up, telling you wrong reasons, and until you enter the correct \can continue to input. Magic, right? In fact, in Excel’s \Why not its own, and their design a test is to make your show unique luster Excel does.
18, into a group of more than fill the form of fixed cells
we know that each time you open Excel, the software is always the default to open multiple sheets. In addition to this we can see that Excel has a strong capacity leaflet form, more suitable for multiple interrelated tables coordination. Related to coordination, of course, first need to synchronization input. Therefore, in many cases, will need more than one form simultaneously in the same cell type the same content.
form then how to edit it in groups? First, we click the first worksheet tab name \form is not associated with, you can hold down the Ctrl key to click). At this point, we see the name of Excel’s title bar to a \Need more than one input table cell content easily write something, we find that \
However, the only synchronization input is not enough. For example, we need multiple forms of data in the same position to change the format of the uniform how to do? First of all, we have to change the first table of data format, and then click the \The sub-menu, select \At this time, Excel will pop up, \
19, change the text of the case
in Excel, the form processing and data computing support to provide the most powerful formula is not, nor is the database, but the function. Do not think that Excel, the function only for the figure, as long as the content is written into the table, Excel has a special function to edit it. Such as changing the text of the case.
in Excel 2002, provide at least three relevant text case conversion functions. They are: \into \For example, on a table cell A1 enter lower case \Similarly, if we enter in the A3 cell \
20, extraction of a specific character string
addition to the direct importation from an existing cell to extract the contents of a specific character input, is absolutely a way to save time and save trouble, especially on some styles particularly similar information, such as staff lists, country of origin and other information.
if we want to quickly extract a cell from A4 appellation, it is best to use \characters \Of course, if you want to extract the name, then, will have to use the \In another case, we do not start from the left and right ends, but directly extracted from the data center a few characters. For example we want to extract the cells from A5 \Means: in the A5 cell extract after the first four characters of the two characters, that is, 4 and 5 of the word.

21, the base sequence numerals to words into English words into ordinal base is a more complex problem. Because it does not have a very fixed pattern: Most of the figures into numerals are used in sequence \st \Moreover, the \Therefore, it seems very complicated to achieve them. In fact, as long as we sort out our thinking, identify the function, just write a formula that can be easily converted. Do not believe it, see: \, 2,3,), CHOOSE (RIGHT (A2), \is \\
22, padded with special symbols and financial dealings with the median
people know, when filling in the book there is a conventional \, or the amount of data in the front with \digits) \16-LEN (A2))) \(A3))) & A3 \) & A4 & REPT (\\requirements.
23, create a histogram
addition to repeatedly enter the text in addition, \The same principle is simply the intelligent use of Te Shu Zhong Fu symbols, in accordance with the Zhiding unit cell the calculations Biaoxian the different lengths of Bijiaoxiaoguo.
Biruwomen Shouxianzhizuo an annual balance sheet, and then the \\font \also drag the fill handle to the G14. We see no use of Excel chart features a plain text histogram has demonstrated immediate and convenient, simple and clear.
24, calculate the total cell number of words
Sometimes, we may have a cell number of interesting characters Di, Xu Yao calculate the total number of words in the cell. To solve this problem, in addition to use of Dao \function to remove spaces. such as cell A1 now enter a \LEN (TRIM (A1))-LEN (SUBSTITUTE (TRIM (A1 ),\use \The number of characters.
25, on the euro conversion
This is a new tool in Excel 2002. If you choose to install the Excel 2002 is the default, then it may not be in the \find it. However, we can first select the \> completed we again open the \Currency conversion of \The show is more professional, we also can click on the Excel toolbar, the \

(0) Comments Permalink