Excel Practical Skills Daquan

Excel is our common Office software, the author in the use of the process summed up a number of experience, now written for your reference.





1, do not use the arrow keys can also move the selected cells up and down





due to work needs, I enter a large number of text in Excel cells, you know, in Excel, if you want to select the cell to the right of the selected cells, you can press the arrow keys to move, in order to save time, so that the operation of higher speed, you can press the TAB key, This allows you to quickly select the cells to the right of the selected cells, because when typing two hands are working in the primary key area, if you want to press the direction key, you have to vacate the right hand, which will undoubtedly reduce efficiency;





So what if you want to select the cell to the left of the selected cell? The author has found that the "shift+tab" combination can be pressed at the same time, so that the selected cell will move left.





, we know that. Fast move the selected cells to the left and right, the next cell is the "Enter" button, the same reason, if you want to select the cell is to press the key combination "Shift+enter", How, So you can enter a lot of text in Excel faster.





2, select the cell that contains the hyperlink



Enter an e-mail address in a cell in Excel, the cell is automatically converted to a hyperlink by default, and clicking it opens a hyperlink, so how can you select the cell without opening the hyperlink? The method is simple, use the mouse to hold the cell for a few seconds, you can select it.





3, continuous display of formulas within cells





Enter a formula anywhere in the cell in Excel, such as =1+1, press ENTER, when this cell will no longer show the formula, and display the calculation results 2, but sometimes we do not want to see the results immediately, but hope that this cell can continue to show the formula, how to solve this problem? Method is simple, after you enter a formula in a cell, click "Tools" in the menu bar--"Formula auditing"--"formula audit mode" so that you can keep the formula intact, and if you want to show the formula calculation results, you can click "Formula Auditing Mode" again.





4, the rectangular diagonal length is calculated quickly





the length and width of the known rectangle, it is now required to calculate its diagonal lengths, and Excel can quickly calculate the answer. How to operate it?





with a rectangular length of x, with a width of y and a diagonal length of z, you can see from this formula that Z is required for two steps. First, calculate the square value of the 蚘 of the Chili Street Island of X, and add them together; second, the value of the first step, the resulting figure is Z. Obviously, we want to use two functions in Excel, one is to compute the square of a value, that is, the number of the power operation, and the second is to calculate the square root of the number. Here's how to use these two mathematical functions.





Returns the power of a given number: power




The
power function can power a number, and its syntax is: Power (number,power)





where number is the base and can be any real number.





Power is the exponent, and the base is the exponent.





For example, we require 5 of the two times, that is, square, you can enter in the cell: "=power (5,2)", can come to the answer 25.





Returns the positive square root of a given number: SQRT



its syntax is: SQRT (number)





Where number is the count of the square root to be computed.





For example, we asked for a square root of 16, you can enter in the cell: "=sqrt (16)", can come to answer 4.





know the use of these two functions, we can easily draw the value of Z.





first the square of X and Y, then add them together, then add the value of the root, you can come to the answer.





If you still think it's slow, there's a better way. A function is described below: sumsq





Returns the sum of the squares of the parameters: Sumsq





its syntax is: SUMSQ (Number1,number2, ...). Number1,number2, such as the number of 30, with this function can be used to find their square sum, so that simply enter the value of x and Y, you can find their square sum, and then use the SQRT function one calculation, the last Z value can be obtained.


the best way to do this is to enter a function within a single cell, and you can find the final z value.





describes the function that returns the sum of the squares of the arguments: sumsq, suppose the x,y values are 3 and 4, enter SUMSQ (3,4) in one cell, and then press ENTER, the number 25 is displayed in the cell, which is the sum of the squares of the x and Y values. We're going to figure out the square root of the number 25, you need another function: SQRT, so that the function sumsq into a function SQRT parameter, which in Excel is called a nested function, where a function becomes the parameter of another function, to enter the values of x and y in a cell, and to find the Z value, After the author test, is completely feasible, because the use of nested functions here. If x and Y values are 3 and 4 respectively, enter =SQRT (SUMSQ (3,4) in the cell, and press ENTER, you can get a Z-value--5 right away. When this nested function is entered, the parentheses outside the arguments 3 and 4 of the SUMSQ function change to green, while the color of the brackets outside the parameters of the SQRT function is not changed, indicating that the software has read the meaning of this nested function.





Interested readers can try, in any cell in Excel, enter "=sqrt (SUMSQ (X,y))", note that here you can replace x,y with any two positive numbers, and then click the Enter key, you will get Z value.





In addition, Excel can support multilevel nested functions.


 

5, let the value complete or partially replace the formula

Enter a formula in one cell, such as =1+1, and then press ENTER, the value of this cell is automatically calculated to be 2, can you remove this formula and permanently convert to a numeric value? The

takes the 1+1 formula as an example, let me explain how to permanently replace a formula with a value: first select the cell that contains the formula, click Copy, and then click Paste, then click on the arrow on the right, then click "Only Value", so that The formula in the cell is permanently converted to a numeric value.

What if you have already performed the steps above and want to change the value back to the formula? The method is simple, click on the "Undo" button on the "Standard" toolbar.

Sometimes we don't want to convert the entire formula to a numeric value, but we just want to convert part of the formula to a numeric value.

First select the cell that contains the formula, and then in the edit bar, select the part that you want to convert to a numeric value, press F9, and then press ENTER, which is converted to a value instead of a formula.

6, removing the recently used workbooks list

There are several recently edited workbooks under the File menu in Excel, sometimes we don't want it to show up, how do we hide these workbooks?

Click Options on the Tools menu, click the General tab in the pop-up dialog box, and then remove the check mark in front of the list of recently used files.

If you want to change the number of recently used files that appear under the File menu, you can select this check box, and then enter the number of items to follow.

7, showing cells that contain formulas

In an Excel worksheet, there is a large amount of data, some of which are directly entered, and some are calculated with formulas. Now you want to display the cells of the data that you calculated with the formula.

Method: Select all of the selected cells, then click "Edit"-"position", then pop the "position" dialog box, click "Locate conditions", select "Formula", then click "OK", so that the cell containing the formula will be displayed.

8, simple Way to move replication toolbar buttons

Depending on your job needs, we need to put some of our most commonly used toolbar buttons in a more focused place, that is, move the toolbarbutton, this is very easy to implement, to move the toolbar button, you can press the "ALT" button to haul the toolbar buttons, move it to another location.

And if you want to copy one of the buttons on a toolbar to another place on the toolbar, you can drag the button to another place while you press the "Atl+ctrl" key combination.

Note that when you move and copy a toolbar button, you must first press the "ALT" and "Alt+ctrl" key combinations, and then drag with the mouse.

What if you don't want it after you copy the toolbar button? The method is simple, you need to press the "Alt+ctrl" key combination again, drag the unwanted button to the outside of the toolbar.

9, quick Insert function with Shortcut

in Excel, if you want to insert a function, you can use the shortcut key "Shift+f3" and press this key combination to eject the Insert Function dialog box.