19 August, 2021

TIPS for CLICKS: Working with Functions and Formulas

Excel FUNCTIONS = Predefined formulas used for specific values in a specific order. They operate usually on data entered in range of cells and increase the Excel users productivity. Total number of the functions available in Excel is close to 700.

Some of the most important of them (most frequently used) are: SUM, IF, MIN, MAX, AND, OR, AVERAGE, COUNT, DAYS, VLOOKUP, DATE, ... etc. Examples of functions working with text: UPPER, LOWER, PROPER,... etc.

Excel FORMULAS = Expressions operating on values in a range of cells and operators. Can utilize several Excel functions and work with both numeric and textual data. ARRAY formulas are a special type of formulas, and can be very complex.

Here is the list and description of some of the most useful shortcuts used in conjunction with Functions and Formulas. I hope you'll find them helpful.

ALT+= > Inserts Auto Sum below the selected range of cells and displays small Quick Analysis icon. Clicking on the icon displays dialog box with data analysis tools (like charts, color-coding, formulas, tables, sparklines).

CTRL+` > Alternates between displaying cell values and formulas (if there are any) in the worksheet.

CTRL+' > Copies a formula from the cell above the active cell.

CTRL+K > Displays the Insert Hyperlink dialog box for you to enter a new hyperlink, or Edit Hyperlink for editing an existing hyperlink.

SHIFT+F3 >  Displays the Insert Function dialog box and subsequently all the Help on the selected function. Exceptionally practical and beneficial.

CTRL+A > After you type "=" and a valid function name in your formula, displays the Function Arguments (kind of formula palette) dialog box. If you do not actually enter anything, but your worksheet contains some data, the shortcut selects the current region, otherwise selects the entire worksheet. Pressing the shortcut a second time (in the case of already selected current region) it selects also the entire worksheet.

CTRL+SHIFT+A > After you type = (equal sign) and a valid function name in your formula, it inserts the argument names and parenthesis for the function.

ALT+T+U+F > Displays the Evaluate Formula dialog box for a selected cell with a formula.

CTRL+SHIFT+ENTER > This shortcut must be used to enter a formula as an array formula. This specific requirement differentiates an array formula from a regular Excel formula. To enter array formula, first you must select the output range, enter the formula in the top-left cell of the output range, and then pressing CTRL+SHIFT+ENTER to confirm it.

ARRAY formulas can also be considered as great shortcuts. They can be used to perform multiple calculations on items in an array (which is a range of cell values). Array formulas can return a single result or multiple result. The simple example below illustrates how array formulas can be used to output values in cells B2:B7 and in cell C3.

Here, just one array formula - using the LEN function - returns the length of each text string in each of the cells in A2:A7 range. The SUM function in combination with LEN function is used in another array formula to calculate the total length of the combined text string (shown in cell C8). For better clarity, the following image shows the above range of cells with applied formulas displayed.

Array formula shown above in the Formula Bar was entered in cell B2, while the range B2:B7 was selected. Another array formula was entered in cell C3. Formulas in C4 and C8 are regular formulas.


No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.