How To Use Formulas And Functions In Excel

Create a Formula with Functions

1. Functions allow you to calculate a solution for your function arguments.
2. Select the cell where the formula will be entered.
3. On the Formula bar, click the Insert Function icon.
4. From the Select A Category drop-down, select the category of the function.
5. In the Select A Function list box, select the appropriate function.
6. Click OK.
7. Enter the arguments for the selected function, or click Collapse Dialog to select cells as arguments.
8. Select the appropriate cells.
9. Click OK.

Create a Formula with Nested Functions

1. Nested functions, are functions within a function, to calculate a value.
2. Select the cell where the formula will be entered.
3. On the Formula bar, click the Insert Function icon.
4. From the Category drop-down, select the category of the function.
5. In the Select A Function list box, select the appropriate nested function.
6. Click OK.
7. Enter the arguments in the fields.
8. To enter another function as an argument, enter the function in the argument field.
9. Click OK.

Use the SUBTOTAL Function

1. Note that this process is the same in all editions of Excel.
2. The SUBTOTAL function returns an aggregate result for supplied values.
3. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others, and can either include or exclude values in hidden rows.
4. For example, if you want to sum all values in a range of cells, including hidden cells, you can use the SUBTOTAL function with function number 9.
5. Select the cell where you want to add the function arguments.
6. Enter =SUBTOTAL, open parenthesis, 9, comma, the range of cells in the appropriate column, close parenthesis).
7. Press Enter.
8. If you want to sum all values in a range of cells, ignoring hidden cells, use the SUBTOTAL function with function number 109.

Use the ROW Function

1. The ROW function can be used to return the number of a reference.
2. Select the cell where to enter the function.
3. Enter =ROW([reference]).
4. Note that the reference value is optional, but that if the reference is omitted, the function will assume that the reference refers to the cell in which the ROW function appears.
5. Press Enter.

Use the MATCH Function

1. The MATCH function can be used to locate a specified term in a range of cells.
2. Select the cell where to enter the function.
3. Enter =MATCH(lookup_value, lookup_array, [match_type]).
4. Note that lookup_value and lookup_array are required, but match_type is optional.
5. Press Enter.

Use the INDIRECT Function

1. The Indirect function can be used to change the reference to a cell within a formula without modifying the formula itself.
2. Select the cell where to enter the function.
3. Enter =INDIRECT(ref_text, [a1]).
4. Note that the ref_text value is required, but a1 is optional.
5. Press Enter.

Show the Developer Tab in the Ribbon

1. On the File tab, click Options.
2. In the left navigation pane, click Customize Ribbon.
3. From the Customize The Ribbon drop-down, select Main Tabs.
4. In the Main Tabs list box, select the Developer checkbox.
5. Click OK.

Record a Macro

1. Click the Developer tab.
2. In the Code group, click Macro Security.
3. In the left navigation pane, select Macro Settings.
4. In the Macro Settings area, select the Enable All Macros radio button.
5. Click OK.
6. In the Code group, click Record Macro.
7. In the Macro Name field, enter a name for the macro.
8. In the Shortcut Key field, enter a lowercase or uppercase letter to be used as a CTRL combination key.
9. From the Store Macro drop-down, select the workbook where to save the macro.
10. In the Description field, enter a description for the macro.
11. Click OK.
12. Perform the actions to be recorded.
13. In the Code area, click Stop Recording.

Assign a Macro to an Object, Graphic, or Control

1. Right-click the object, graphic, or control to be modified.
2. On the shortcut menu, click Assign Macro.
3. In the Macro Name list box, select the macro to be assigned.
4. Click OK.

Create a VBA Procedure for the Open Event of a Workbook

1. Note: Close all workbooks except the workbook where the macro is to be added.
2. Select the Developer tab.
3. In the Code group, click Visual Basic.
4. In the Project pane, click This Workbook.
5. On the View menu, click Code.
6. From the Object drop-down, click Workbook.
7. Enter the appropriate code for VBA procedure.
8. From the File menu, click Close And Return To Microsoft Excel.

Add a Button

1. Select the File tab.
2. Click Options.
3. Click Customize Ribbon.
4. In the Main Tabs list box, select the Developer checkbox.
5. Click OK.
6. Select the Developer tab.
7. Locate the Controls group.
8. Select the Insert drop-down.
9. Under Form Controls, click Button.
10. Click and drag into the worksheet to create the button.
11. In the Macro list box, select the appropriate macro.
12. Click OK.

Remove the Duplicate Values

1. Select the range of cells to be checked.
2. Click the Data tab.
3. In the Data Tools group, click Remove Duplicates.
4. In the Columns group, select the checkboxes of the columns that contain duplicates.
5. Click OK.
6. Click OK.

Use the AutoSum

1. Select the appropriate values.
2. Click the Home tab.
3. In the Editing group, select the AutoSum drop-down.
4. Click Sum.

Create a Two-Variable Data Table

1. In a cell, enter the formula that refers to the two input cell.
2. Enter the list of input values below the formula.
3. Enter the second list in the same row.
4. Select the range of cells containing the formula and the row and column of values.
5. Click the Data tab.
6. In the Forecast group, select the What-If Analysis drop-down.
7. Click Data Table.
8. In the Row Input Cell field, enter the cell reference for the input values in the row.
9. In the Column Input Cell field, enter the cell reference for the input values in the column.
10. Click OK.

Separate Text across Cells Using the Delimited Option

1. Select the range of cells with text to be separated.
2. Click the Data tab.
3. In the Data Tools group, click Text To Columns.
4. In the Original Data Type group, select the Delimited radio button.
5. Click Next.
6. In the Delimiters group, select the checkbox that describes how the data will be separated.
7. Click Next.
8. Click Finish.

Do you need hands on help or more training? Call us today at (877) 637-7573 to get your membership started and start one-on-one help with our expert technicians. Don’t forget to check out our reviews here and visit our website for more helpful tips like this one!