Advanced Excel Tips

1. Editing skills
2. The merger of the cell contents
3. Conditions indicate
4. Custom format
5. Drawing Function Image
6. Custom functions
7 . Matrix
8. automatically switch the input method
9. batch delete empty lines
10. how to avoid the error message
11. macro use
12. the icon of the application of skills



1, editing skills

1 editing skills

(4) fill streak

If you want to add the beautiful in the workbook The horizontal stripes, can use the alignment of the filled function. First filled in a cell of
Figure 1


(5) multiple sheets to enter the same content in several worksheets

enter the same data in the same location, you can select a worksheet, and then hold down the Ctrl key, then click the window to the bottom left corner of the Sheet1, Sheet2 ...... to directly select the input the same content multiple sheets, then in any one of them to enter these same worksheet data, then the data will automatically appear in the table selected among other work. After input finished, press the Ctrl key on the keyboard, then use the left mouse button click on the selected multiple worksheets, work table to remove these links, or in a form input data will then appear in the election the other work table.

(6) cells do not populate the same data continuously

select a cell, hold down the Ctrl key, right click the other cells, it will select all those cells the. Enter data in the edit area, then hold down the Ctrl key while you tap Enter, in all the selected cells have emerged in the data.

(7) to display the formula in cell

data in the table if the majority of work is generated by a formula and want to quickly know the formula for each cell in the form of, for editing , you can do: Use the left mouse button click on At this time the division of labor in each cell that come. If you want to restore the display of calculated results to re-set
Figure 2


(8) Select the text using Ctrl + *

a work table, if there are many data tables, you can select the table by a cell, and then press Ctrl + * key to select the entire table. Ctrl + * the selected region: According to the selected cell to the surrounding radiation data involved are the largest regional cell. So that we can easily and accurately select the data table, and effective method to avoid dragging large range of cells selected screen Luangun phenomenon.
(9) quickly remove the contents of the cell

If you want to delete the contents of the cell content and its format and comments, can not simply be applied to the cell selected, then press the Delete key way to go. To completely remove the cells, using the following method: you want to remove the selected cell or range of cells; click the

2, the combined cell contents

needed, and sometimes want to B and C list the contents of the column to merge, if the number of rows less, can directly use the Paste

solution is: In the C line, insert a blank line (if the D column is not content to operate directly in the D column), in D1, enter , C, and the two columns. Select cell D1, the cell with the mouse to point the bottom right corner of the small box B series and C series combined. Then do not just put the first B and C column remove column, the first column of the results of D should copy it, and then Time you delete B, C, D columns.

The following is a With AutoCAD drawing, some people like the coordinate point is stored in EXCEL, called when drawing curves in these parameters. Stored data format is merged into C column, enter in C1: = A1 &All contents of the combined (see Figure 3-4).

merge the contents of different cells, there is a way is to use CONCATENATE function, the function of certain text string into a string, the specific operation for the . For example, assume that the investigation in a river ecosystem in the table, B2 contains ,
Figure 3 Figure 4


3, conditions indicate

we know, the use of If the function can be achieved in accordance with the conditions of display. A common example is when the teachers in student achievement statistics, want to enter scores below 60, can appear as This effect, the use of IF functions can be easily achieved. Assumption results in the A2 cell, determine the results in cell A3. Then in cell A3 enter the formula: = if (A2 <60,

For example, if you type: = if (A2 <60,

If you enter = if (A2 <60, to four grades.

another example, the formula: = if (SUM (A1: A5> 0, SUM (A1: A5), 0) this style on the use of a nested function, meaning that, when A1 to A5 and large at 0, returns this value if less than 0, it returns 0. Another point to draw your attention: The above symbols are the half-angle, and the IF with no spaces between brackets.

4, the custom format

Excel by default a lot of useful data formats, and can basically meet the requirements of use, but for some special requirements, such as the emphasis on some important data or information display, set the display conditions so, we must use a custom format function to complete. Excel custom format using the following general model: a positive number format, negative format, zero format, text format, in this general model, containing three numbers and a text paragraph section: use of data greater than zero positive number format; less than zero data using a negative format; zero data using a zero format; enter the body cells to use text format. We can also use the test, add descriptive text and the use of custom format color to extend the application of universal model.

(1) use of color in a custom format to a paragraph set the color, simply add the paragraph in square brackets the color name or color number. Excel recognize the color is named: [black], [Red], [White], [Blue], [Green], [blue] and [magenta]. Excel is also identified by [color X] specified colors, where X is a number between 1-56, representing 56 kinds of colors (Figure 5).
Figure 5


(2) add description text in the input digital data to automatically after add text, using a custom format: add text position.

(3) create a conditional format of six logic symbols can be used to design a conditional format: ),<=( less than or equal), = (equal ),<>( not equal), bad if you think these symbols in mind, simply use the .

the custom number format, up to only 3 field, Excel provides a maximum number of segments in the first two conditions, including 2 tests, meet with a number of test conditions specified in the corresponding paragraph format, the other digital format for use in paragraph 3. If the test contains only one condition, they have different situations according to specific analysis.

custom format is equivalent to the general model the following formula: [>; 0] positive format; [<; 0] negative format; zero format; text format.

an example is given below: Select one, and then click the In the dialog box, select show Enter the text ; [blue] ,],[> As another example, assume that the ongoing settlement of accounts, you want to use the blue more than $ 50,000 account balance, negative values ​​shown in brackets in red, the rest of the value of the color displayed by default, you can create the following format: character values ​​can be used as a powerful auxiliary scale, for example, if the unit production of several products, as long as a few grams of each product of a compound, while the production of several thousand a day this product, then used in the preparation of the budget, you need to from g into kilograms, tons, then you can define the following format: custom format can be used to achieve the purpose of hiding the input data, such as the format Definition Format The appearance of only changing the data, the data does not change the value, that does not affect the calculation of data. flexible use of a good custom format function to the actual work will bring great convenience.

5, the draw function to do image

teaching friends will encounter problems drawn function curve now! If you want to quickly and accurately draw a function curve, a chart can make use of EXCEL functions, which enables you draw the curve of both standard and beautiful. You have to ask, is not hard to learn it? fact, this is not difficult, can be very simple, do not believe you told me to try.

to Draw y = | lg (6 + x ^ 3) | of the curve, for example, the method is as follows: in a blank worksheet, enter the function arguments: A column in the cell A1 enter that this is the independent variable, and then the A2 in the A column and box after successive small to large input from the values ​​of variables; the actual input, the application of arithmetic progression is usually input method, enter the first two values, determined from the number of variables between the number of steps and then select the two cells A2 and A3, so that the two into a rectangle with a black border, and then point to the lower right corner of the small rectangular black box ■ generally written in the form, y = | lg (6 + x ^ 3) |; in cell B2 enter the , and then select cell B2, so that the cursor point to the lower right corner of the rectangle B2 7


drawn curve: Click on the toolbar, the items, given the data area, then the curve in front of us (Figure 8).
Figure 8


Note: How to determine the initial value of the independent variables, the data points What is the step between, this is according to the specific characteristics to determine the function, which is the ability of the test users. If you want to quickly find the function of the extreme or see its development trend of the data points are given not necessarily have to be arithmetic, can be arbitrarily given.

from simple to complex logarithmic trigonometric functions, exponential functions, can draw the curve with EXCEL. If available, you can Using EXCEL to complete the determinant, the matrix of the various calculations, a simple integral operation, the use of iteration of a function value (such as x ^ 2 = x ^ 7 +4, iteration method can find x value), and so on, all things related to computing, looking for EXCEL to help, it will give you a satisfactory answer.

6, a custom function

While Excel has a large number of built-in function, but some calculations which may also run into situations without function is available. If a company uses a special mathematical formula products, the purchaser's discount, if there is a function to calculate how much more convenient? said the following about how to create such a self- defined function.

custom functions, also called user-defined functions, Excel is the most innovative and attractive features, one of the following Visual Basic module, we create a function. In the following example, we to give each person the amount by a factor, if it is working meals at work, and beat the tickets; if it is working overtime meal time, and beat half of the amount; if it is a rest day to dinner, hit Jiuzhe. First, open the In the right-hand column to create the following function rrr, the following code: Function rrr (tatol, rr) If rr = Then rrr = 0.9 * tatol End If End Function (Figure 9).
Figure 9


then close the editor, as long as we enter in the appropriate column rrr (F2, B2) , then after the kick off even out the amount (Figure 10).
Figure 10


7, Matrix

Excel's powerful computing capabilities, not only for Simple arithmetic can also be an array, matrix calculations.

(1) the definition of arrays and matrices

matrix is ​​not a number, but an array. In Excel, the array takes a unit field, the unit field with large brackets, such as {A1: C3}, in order and the general unit domain A1: C3 phase difference. settings, first select the unit field, press Shift + Ctrl + Enter keys, curly braces that is automatically generated, the array field able to confirm.

a cell is a variable, a unit of the domain can be regarded as a set of variables. In order to calculate the convenience, a set of variables to an array of the best name. For example A = {A1: C3 }, B = {E1: G3} and so on. array were set steps are: an array of field selected, click function - calculating a matrix of; MMULT - calculating the product of two matrices; SUMPRODUCT - the product of the calculation of all matrix elements corresponding to the sum.
Figure 11


(2) matrix array of basic computing and computing

matrix calculation is very different, such as the following example, A and B are defined array, as the two arrays are 3 × 3, the output is 3 × 3 unit cells. calculated results when the first output of the selected matrix domain, the range of cells for the 3 × 3, then enter the formula. If you enter added or subtracted, enter matrix addition, subtraction and addition and subtraction array is the same expression, but also A by B, the inverse matrix, so the formula is br>
8,

automatically switch the input method on a worksheet, the data is often both, there are text, so that input back and forth in the need to flip between English and Chinese input method much trouble. If you want to enter something very regular, such as this column is all words, all Chinese to explain the next column, you can use the following method for automatic switching. is:

(1) Select the column to type in English, click Mode , in the English, in the next column, you can directly enter the Chinese and English in order to achieve the automatic switching between input methods.

9, bulk delete blank lines

sometimes we need to delete Excel workbook blank lines, the general practice is to identify one by one blank line, and then delete it. If the number of rows in the table a lot of work, this is very inconvenient. We can use the deleted. approach: first in the table insert a new blank line, then press Ctrl + A keys to select the entire worksheet, right-click Then in the top of each column, are a drop-down list box, in a typical drop-down list box, select the column 13


in all cases the data have been selected, click the be deleted, and then click
If you want to delete only one empty cell in the column, while the other columns of data and blank cells are not affected, you can copy the first column, paste it to a blank worksheet will be empty by the above method delete all rows, then copy this column and paste it into the original location on the corresponding worksheet.

10, how to avoid the error message

enter the formula in Excel, sometimes not correctly to calculate results, and in the cell displays a error message, the production of these errors, some due to the formula itself, and some are not. Here's introduce some common error messages, and proposed ways to avoid mistakes. < br>
1) :####

mean error value: Enter the data into a cell or cell formula is too long the result is too large, so that the results displayed in the cell in less than . or the date and time format the cell to do subtraction, there has been negative.

solution: increase the width of the column so that the results can be fully displayed. If the date or time by subtracting the negative generated caused the cells to change the format to text format such as, the result is a negative amount of time.

2) error value: # DIV / 0!

meaning: trying to divide 0. This error is usually associated with the following situations: the divisor is 0, the divisor used in the formula or the empty cells contain a zero value cell reference cells.

solution: modify the cell reference, or used as a divisor input cell value is not zero.

3) error value: # VALUE!

meaning: the text-input mathematical formula item. If incorrect parameters or operator, or when performing an automatic correction function can not correct the formula for the formula, will produce an error message # VALUE!.

solution: At this time the formula or function should be required to confirm the operation character or parameters correctly, and that a formula references a cell contains a valid value. For example, cell C4 has a numeric or logical values, cell D4 contains the text, then the formula = C4 + D4, the system can not convert text the correct data type, which returns the error value # VALUE!.

4) error value: # REF!

meaning: delete the formula referenced by the range of cells.
< br> The solution: restore the referenced cell range, or re-set the reference range.

5) error value: # N / A

meaning: no information can be used to run calculations. in the establishment of model, the user can enter in a cell # N / A, to show that is waiting for data. containing any reference to # N / A value of the cell will return # N / A.

Solution: waiting for the data cell is filled with data.

6) error value: # NAME?

meaning: the formula used in Excel can not recognize the text, such as may lose the wrong name, or enter a name deleted, if not the literal string enclosed in double quotes, will produce the error value

solution: If you are using a non-existent Name this type of error generated, should be sure to use the name does exist; if the name, function name, spelling mistakes should be corrected; the text string enclosed in double quotes; sure all areas used in the formula are used in reference to a colon ( :). For example: SUM (C1: C10). Note that the formula in the text enclosed in double quotes.

7) error value: # NUM! meaning: to provide an invalid argument to the worksheet functions or the result of the formula is too large or too small to be expressed in the worksheet.

solution: Make sure the function parameter type used is correct. If the result is too big or too small for the formula, it is necessary to modify the formula so that it results in -1 × 10307 and between 1 × 10307.

8) error value: # NULL! meaning: in the formula to insert a space between the two areas to represent the intersection But these two, there is no public cell. such as the input: grid. the style can be changed to the specified action. macro is similar to a computer program, but it is being fully operational in EXCEL, we can use a macro to complete the tedious, often repetitive work. macro to complete the action faster than the users themselves do much faster. For example, we can create a macro for each row of the worksheet, enter a date and in each cell within the center-aligned date and format of this border-line application. We can also create a macro, in the macro to help. In fact, you may rest assured bold to use, if only with a , the system will put the operation and run it again.

macro given below applications, as long as the then edit the macro, we should have some knowledge of VBA.

* set a table for each job requires a fixed form of the header;

* set the cell into a species has its own style in the form;

* are fixed for each print page setup;

* frequently or repeatedly enter some fixed content, such as the line up of companies address format , personnel lists, etc.;

* create a formatted table;

* insert a thin sheet or other.

be pointed out that, EXCEL and WORD in the macro Some of the difference between the macro, for the recording operation, it will remember the coordinates of the cell (ie, all references are absolute), so when it comes to the operation of position, we should pay particular attention to. If the relative phase with reference, to learn from Offset methods, such as the following statement: ActiveCell.Offset (1,0). range (up, you will find it to have more content and more flexible application methods.

12, the chart application skills

Excel offers 14 standard chart types, each of which with a variety of combinations and transformations. Among the many chart types, select a chart that better? according to the different data and different application requirements, you can choose different types of charts. The choice of the chart form with the data related to Second, before considering the effectiveness and sense of aesthetics. Here are some common rules.

area chart: shows the magnitude of change over time. When there are changes in several parts, and the sum of those parts of you interested in, they are particularly useful. area chart that you see changes in the various parts separately, but also see the overall change.

bar: composed by a series of horizontal bars. makes the timeline a certain point, two or more items is comparable relative size. For example: it can compare each quarter, the three products in the sales of any kind. bar in each worksheet is a separate data points or number. because it is the row and column column is exactly transfer over, so are sometimes used interchangeably.

Column: composed by a series of vertical bars, usually used to compare period of time two or more projects in the relative size. For example: quarterly or annual sales of different products compare, in several projects in the allocation of funds from different departments, so each year the number of various types of information. bar is the application of more wide chart types, a lot of people are using the chart from its beginning.

Line: is used to show trends over time. For example: the data over time is an increasing trend, and the other a time in a downward trend, we can line chart, to predict the future. For example: speed - time curve, thrust - fuel consumption curve, lift coefficient - Mach number curve, the pressure - temperature curve, the fatigue strength - rotation curve transfusion power penalty - distance curve, using a line chart can be expressed more generally in engineering applications, if there are several situations in which a data, a line chart where there are several different lines, such as five athletes In the course of the rate of change of meters, there are five line, compared to each other, can also add a trend line to predict the speed.

stock figure: the three data series is a line chart, was used to show a given time period of the subject shares of high, low and close. by the highest, lowest drawing lines between data points forming vertical lines, while the axis of the closing price on behalf of a small scale. stock chart used for financial , commerce and industry, used to describe commodity prices, currency exchange rates and the temperature, pressure measurement, of course, describe the stock price is the most adept.

pie chart: number of data used for comparison The formation of a percentage of the value of the sum of the most useful. representative of the whole sum of bread, each with a wedge or slice the number of representatives. such as: sales of different products that the percentage of total sales, the units of the total funding requirements the proportion of books collected, how many of each type and so on. expressions pie chart although only one data column, but because the expression was clear and easy to use and easy to learn, so in practice more than used. If to several series of data, can be circular diagram.

radar: How to display data or other data changes at the center. coordinate values ​​for each category of radiation from a central point. from the same sequence of data connected with lines. You can use radar to map the sequence of several interrelated, it is easy to make a visual contrast. For example: You have three parts with five identical machines, the radar can draw on each one machine for each component wear.

XY scatter: display the number of pairs and they represent the relationship between the trend. For each number on a number is drawn in the X axis, while the other was drawn in the Y axis. had two points in the vertical axis, intersecting at a mark on the chart. When a large number of such numbers being drawn, the emergence of a graph. scatterplot important role is to function can be used to draw the curve, from simple trigonometric, exponential functions, logarithmic functions to more complex hybrid function, you can use to quickly and accurately draw the curves, so in teaching, often used in scientific computing to.

There are other types of charts, such as Cylinder, Cone, Pyramid, just a variation of bar and column charts, with no outstanding features, but with a relatively small here stop here. here is to note: This is only general application of the chart, sometimes a set of data can be used to express a variety of charts, then we should be selected according to specific circumstances. on some chart, if drawn into the cylinder of a data sequence, and the other drawn into a line chart or area chart, the chart will look better.

L in EXCE in combination allows the creation of such charts, but if you want EXCEL is not allowed to create a combination of chart types, such as a two-dimensional map can not be the same mix with a three-dimensional chart, this combination is not possible, the system will display an error message. how to build mixed-chart it? click Chart Wizard Click the