To see the argument in a formula, press Ctrl-Shift-A For example, if you type = RATE, and press Ctrl-Shift-A, you can see all the arguments for this function (eg = RATE (nper, pmt, pv, fv, type, guess)).
If you want more details, type in: = RATE
And press Ctrl + A to display in the Function Wizard.
Reference functions
To reference the function in Excel application, you hold down the Shift-F3, Excel will make the dialog box contains all the function and application instructions specific syntax for each function when you move the cursor highlights want to refer to the function. This is quickly entering formulas without using the full syntax.
F9 for temporary use
If you create a formula containing the term should not be too soon to know the results, you can drag the cursor to select a portion of the formula in the formula bar, and press F9. Immediately, the result of a formula in the range of your recipe will appear on the screen. More importantly, are you not press Enter, a part of the formula will be lost, so make sure you have to press the ESC key. However, if you missed the press Enter, then try pressing Ctrl-Z to restore the changes.
Text box to link the data in cell
You can link a text box to data in a cell of nature by creating a text box and link the formula returns the cell to text box.
1. Click on the icon to create a text box on the Drawing toolbar. Click on the spreadsheet and drag the cursor to create a text box.
2. Cursor to the formula bar, enter the formula with the results to the cells to be linked to the text box. (Example: in cell A1 you have the data is 2. On the formula bar, type = A1). And press Enter.
3. Text or data you type in the box for the link (eg A1) will appear in the text box. In the above example, the text box will be valid in the second.
You can move the text box to a couple of different nature in the spreadsheet file if you want.
Link a picture to a cell You can copy a cell and paste them as a quick image of a computer. This is a neat way to easily see which cell in a few places in your spreadsheet. You can use this method to print the cells in a page. When these changes are linked cell formatting is also applied to the cells linked. To create a linked image, follow these steps:
1. Select the cell containing the original data.
2. Click Copy on the Edit menu.
3. Select the cells you want to paste the selected image appears.
4. While holding down the Shift key, click on the Edit menu Paste Picture Link. Will give quick results.
Using Advanced Filter
If you create a list Shift in Microsoft Excel and want to choose at which data and copy them to another worksheet, use the Advanced Filter command. To start using this command, click on the Filter menu dat, click on the Advanced Filter and follow the instructions.
Using Sum+If function to calculate the total data Advanced Filter Suppose you create a list of data in cell A1 to A10 and want to sum all the values greater than 50 and less than 200. To do this, use the formula as below:
= SUM (IF (A1: A10> = 50, IF (A1: A10 <= 200, A1: A10, 0), 0))
To make sure you enter as an array formula, press Ctrl-Shift-Enter. Then you will see () brackets in the formula. But not press Enter when you are entering the formula.
Using Sum + If function to count data
Do you have a list of data in cells A1: A10, and want to count all the values greater than 50 and less than 200. You use the following formula:
= SUM (IF (A1: A10> = 50, IF (A1: A10 <= 200, 1.0), 0))
Enter data quickly using the same cell
By double click on lower right corner of a box to bring up the cursor plus sign will create a sequence identical to the original cell data in the next cell of the column. For example, if you enter data in cells A1: A20, type a formula or text in cell B1. Double click your mouse on the bottom of B1. Microsoft Excel will fill the bottom of the column data from cell B1 to cell B20.
Sort a list of filtered
To sort a filtered list, choose Data \ Sort, and select the appropriate column from the Sort by box control. In particular, you should note is arranged in ascending order (Ascending) and descending (Descending) and click on OK.
Take the filter when the job was completed
If you use AutoFilter to filter records, you've finished with the record show that, but then you want to review all your records again. To get the log, you simply select All from the drop-down list of existing filters. If you want to disable AutoFilter, select the Data \ and delete the selected Filter AutoFilter.
Make just one page of data
Excel has a very "trying" to put so much data to fit a page, but you can reduce or increase for your spreadsheet data by changing the selected Adjust To% Normal Size. Or you can use Pages to select Fit To compress data to fully separate the page number. Simply, select File \ Page Setup and experiment with two choices that by changing the settings of them. Finally, click on the Print Preview button to see the results.
Displaying or using cell
To find out any box that you use, choose Auditing from the Tools menu and click Show Auditing Toolbar. Click on a cell with a formula, then select Trace Precedents (first button on the Auditing toolbar), and put the arrow to the box that you used to calculate the values of individual cells, as This green arrow will appear with links from the input cell to cell results. To remove the arrows, click Remove All Arrows.
Quickly find the cell containing the formula
To find out where the cell containing the formula quickly in a spreadsheet, select Go To from the Edit menu. In the dialog that appears, select Special \ Formulas, and click on OK. Meanwhile, each cell containing a formula will be selected.
Check Formulas for the Go To dialog box with the formula.
Additional Shift to Web-based spreadsheet
To supplement the data "live" from the Web-based spreadsheet to your existing spreadsheet: Open Web worksheet, type the URL into the File Open dialog box, then select and copy the cells you want. In your worksheet, choose Paste Special from the Edit menu and click Paste Link.
Using cell reference and text labels in formulas
To use the cell reference with label text in a formula, you just type a symbol (& Wink between reference and text. For example, to display the data as "25 departments", type (= A1 & "departments"), where A1 contains the number 25.
How to assign a macro to a button?
You want to run macros or functions with just one touch of a button click? By assigning a function or a macro to a custom button that you can implement very quickly without having to select the Tools \ Macro. To assign a macro to a button, select View \ Toolbars \ Customize. Click the Commands tab and select Macros in the Categories control. K ustom 𠄢 Commands Button in a toolbar. When you have completed additional macro button, click on the button with the symbol of a yellow smiley face and click Modify Selection button in the Customize dialog box. Select Assign Macro from the shortcut menu that appears. Finally, for homogeneous macro you want to add macro button, click OK, and then click Close.
Displayed formula safely
You must always keep in mind before moving on to a spreadsheet showing the formula by pressing Ctrl - ~. When you return after displaying spreadsheet formulas, you can find several different formats. For example, Excel might have changed the column width and a few of your other columns. If this happens, simply open the spreadsheet to return to the format lost. Another method is to convert the spreadsheet in a way on and off by clicking Tools \ Options and select Views Formulas check box. Click OK to finish.
Arrange the letters in lower case and uppercase Excel is not interested in the case of alphabetical characters to sort, unless you notify it. If you want to distinguish between capital letters and in often during the sort, select the data you want to sort, select Data \ Sort. Click on Options in the Sort dialog box and select the Case Sensitive option. Click OK twice. You must remember to select this option every time because Excel will not retain this setting.
Convert labels to numbers Text format is a type of valid data. For example, a phone number is not entirely certain. When this type of data storage, you can add data entered by the character ('). However, sometimes the value of entered number is entered as text and this confusion can occur. Fortunately, it can easily convert text numbers to numbers. You follow these steps: select the cells you want to convert data. Choose Data \ Text To Column. Click Next twice. Click the General format and click Finish. Excel will convert the text into digital data.
0 comments:
Post a Comment