Super Skills Office serial ─ Excel cell techniques (below)

 Cell in Office 2010 Excel in absolute importance, therefore, as an Excel user, master cell techniques, we will bring great convenience Excel operations.

    Use drawing tool to draw a slash
    If you want to draw in a cell more than a slash, it is necessary to use "drawing" tool, the method is: Open the Excel's "drawing" tool, click one of the "straight line" button until the cursor changes to a small cross drag the cursor after can draw the required number of slashes. As long as the painting is correct, slash can be automated with the cell elongation or shortening. As for other forms of slash cell line, still according to the method described above to add. Of course, the diagonal of the cell data input will take some work, the usual approach is to let the data in the cell line (press Alt + Enter), then add a space to place data into the appropriate location.

    Magical "Format Painter" quick format a cell
    Double-click the "Format Painter" button, you can make the "Format Painter" repeated use. Press the Esc key (or re-click "Format Painter" button or continue typing) to cancel the function.

    Choose a continuous cell
    In the choice of the cell (row, column) at the same time hold down the Shift key to select contiguous cells (row, column). Select the cell in (row, column) at the same time hold down the Ctrl key to select non-contiguous cells (row, column).

    Clever search function
    Users are not familiar with Excel functions to find a function for a particular job is not easy, the following tips can help you a great favor: if the data to complete the sorting task, you can click on the toolbar "Insert Function" button to open the dialog box, and then in which the "search function" box enter "sort", then click "Go" button. Dialog box will list the "RANK" and other functions for sorting, select one of these functions, you can see it in the dialog box function description.

    Quick Reference formula
    If the formula is a combination of several Excel functions, but use the function wizard to modify the parameters of a function. You can click a function name or the parameters within parentheses, and then click Edit bar "Insert Function" button to open the "Function Arguments" dialog box to modify. Needs to be noted that, Excel XP, or you click the function name parameter can be automatically marked formula refers to cells, and a yellow prompt box function.

    Formula in the formula and display to switch between
    Formula is displayed after the implementation of the results, press Ctrl + `key (top left corner of the keyboard), can display the formula in the formula and display the results of the formula switch between editing and easy to calculate the formula results view.

    Fast access to reference parameter formula
    Is an important component of the formula cell references, used to determine the calculation. Drag way to refer to the right of the cell is not accurate and fast. To the SUM function, for example, if you use the "Function Arguments" dialog box that allows the cursor in the Number box, and then use the mouse to select the cells involved in the calculation. If these cells are not contiguous, hold down the Ctrl key and drag it should, cited a range of cells and separator will automatically appear in the formula.

    Find a powerful weapon to the wrong formula
    For complex formulas with nested functions, once the error is often difficult to track.Excel XP specially added a "Evaluate Formula" feature, simply select the cell where the formula, click in the toolbar of the "Evaluate Formula" button (if you can not open the "Customize" dialog box " Commands "tab, select" Category "list in the" Tools ", the right of the" Evaluate Formula "button to a toolbar). You can click the dialog box in the "evaluate" button, and follow the formula step by step execution of the order of the underlined expression observed result of the operation, in order to find the source of the error. This feature when debugging a bit like the "step" is the wrong formula to find a powerful weapon.

    Paste "simple operation
    Multiple cells of data with a number if you need to add, subtract, multiply, divide, and do not want to add new data cell, calculated using the formula will be very troublesome. If you use the "Paste" is a multiplier effect: if A2: A6.B6: B12 and other data areas, hoping the data is multiplied by 16. Can be in a blank cell, enter 16, select it and click the toolbar "Copy" button, then press the Ctrl key will be multiplied by all the selected cell 16.According to the method described above to open the "Paste Special" dialog box, select the "operator" under the "take", click "OK" button, the menu element into a grid of data are the result multiplied by 16. If you want the data for the cell to perform other operations, only in the dialog box "operator" under some kind of operation can be selected.

    Efficient calculation of the average, sum, the most value
    Some data in Excel to calculate the average, are generally carried out by function, but this efficiency is very slow, in fact, just select the relevant data on the status bar has been shown and, try right-click the status bar will pop up a menu, the menu includes a lot of computing projects, such as selection means that the status bar shows the average of the selected cell.

    Easily monitor the results of formulas and computing
    If more than one cell or set of formulas in the worksheet, in order to see the formulas and calculations, it is necessary to repeat scroll window or switch the worksheet. Using the following technique to avoid this trouble: Select the cell containing the formula (with multiple cells, hold down the Ctrl key), right-click the toolbar, select the "prison window mouth." Click the "prison window the mouth" in the "Add Watch" button, the selected cell in the formula and its results will be displayed in the monitor window to the mouth, very easy to observe.

    Quickly copy a formula or data
    Double-click the active cell of the auto fill handle, the active cell in the formula or data is automatically copied down, copy the data down the line range from active cell of the row before a decision that the active cell in the formula down data is copied to the bottom before a behavior where only the data.

    Quickly select the cell containing the formula
    Sometimes, the need for the worksheet that contains the formula unit Giga to protect, or other cells fill with different colors to alert other users to pay attention, can not enter data here, the following method can be used to quickly select a cell: the implementation of"Edit" → "positioning", press the "Go To" button in the pop-up "orientation conditions" dialog box, select the "formula" item, press the "OK" button.

    Clever use of rounding function example - rounded
    Previously used office 2007 download Excel to do statistical reports electricity, we often find that the final statistical data and actual data has errors, ranging from a few dollars to more than tens of dollars. The analysis found that the calculation of three-dimensional reference a problem: Under normal circumstances, the amount of data to be accurate to the sub, it is the element as a decimal integer units to keep two back, but because of the relationship between price and the formula, often have to calculate the small digital back three, four or even more, even if only in the cell two decimal places, the surface of this cell data calculated results are consistent with the actual calculation, but this data is automatically rounded cell result, the system still holds all the data cells (do not believe you click the "Add a few small digital" button), and finally figured out the data and the actual margin of error calculated from the data.
    Since there is no function in Excel rounding function, but there are rounding function INT.After the study found, can use the INT function to round. As follows: First, select the required cell rounding, and enter "= INT (formula × 100 +0.5) / 100", is done.

    To form an array formula, the formula
    After you finish entering the formula, press Ctrl + Shift + Enter key combination, then the formula to form an array formula.

    Macro lets you perform better, faster
    If you are a person of statistics and analysis, you will certainly this is an excellent Excel spreadsheet software, put it down, if you need to compile the program to report the final results are displayed, you can display data in Excel VBA form control. Even if you do not be programmed, you can also prepare a small program as follows: Open Excel in the "Tools" → "Macros" → "Record New Macro" and then be done manually, after the end of the generated code is a good macro program. If you spent a long time to complete a very complex macros, your results will be excited, but you look at the screen flashing very uncomfortable to your eyes, is there a trace of regret, then I will provide you with a solution, only two commands, no, a: Application.ScreenUpdating.
    Specific applications are as follows:
    1 Sub Macro ()
    Application.ScreenUpdating = False
    ... Your macro
    Application.ScreenUpdating = True
    End Sub
    ScreenUpdating property called Chinese screen updates, application Application object, you can control whether to update the screen, if screen updating is turned compared to True, the screen will display the corresponding track of all changes and close the screen update is not displayed, the default value is True . By comparison, close the screen update will not see the macro execution at the same time, the macro will be significantly accelerated the pace of implementation can be improved several times, with this method, why not? .
    Remember, when the macro end of the run, set the ScreenUpdating property set back to True. If you do not know what time bin program, please use the MsgBox command prompt in the final.

    Filled with duplicate data
    Fill in the need to repeat the first cell in the input text or numbers, and use the mouse to select it, and so lower right corner of the cell appear black four-box fill handle, hold down the mouse drag the fill handle to fill in the duplication of data began .

    Filled with the same data
    If the contents of the cell that contains the numbers and text, hold down the Ctrl key while dragging need to fill in order to ensure the data is the same.

    Fill the contents of multiple cells
    If you want to repeat more than one cell type fill the contents of multiple cells can be selected after this hold down the Ctrl key to begin dragging.

    Sequence of filling
    If the sequence of incremental data is 1, the sequence number to fill the first cell in the first value in the input sequence, and then hold down the Ctrl key does not release the mouse and then select the first cell, when the lower right corner of the cell appear black four-box fill handle, drag the fill handle down with the mouse, the sequence from the first cell in each unit Giga 1.

    If the data is not an incremental
    If the incremental data is not 1, the sequence of values ​​to fill the first cell in the input sequence of the first value in the second cell type in the value of the second sequence, then these two values ​​is selected, drag the fill handle, the sequence starting from the second cell padding, each unit Giga's first cell and second cell value of the difference.

    Enter the geometric series
    If you want to enter a "2,6,18,54,162 ..." the series, it is only with the mouse to drag the fill handle can not be completed filled. We can in the first cell, type 2, then the mouse to select the cell needs to be filled (note that it is selected, not drag the fill handle), then select "Edit" → "filling" → "sequence", Sequence dialog box appears; dialog box, set in the sequence "sequence generated in the" To "line" or "column", "type" to "geometric sequence", "step value" to "3", then in the "termination value "fill in the termination of the sequence value. Determined in a continuous "line" or "Column", enter a geometric sequence.
    Tips
    Must be in the "termination value" fill in the value of the termination sequence, or Excel can not make the filling.