877-629-6972
There are so many ways to work with the features in Excel. To be exact, there are 467 functions. We don’t know about you, but there is no way we could remember every single one by memory. We don’t have enough room to talk about the large amount of functions available in Excel so we will be covering a few of our favorites.
Functions can be used to add different functionalities to your workbook, such as the date and time, locate information, and detect blank cells. We also think it is important to know how to work with formulas and calculations in this platform. You will also see tips on the developer tab in Excel that stores options to create macros and controls.
Knowing how to work with formulas and functions in Excel, as well as, other popular functions, will give you the confidence to check that “I am efficient in Excel” box when applying for jobs (and genuinely meaning it)! If you don’t already have this application installed, you can download it on your Mac or PC.
   

Let’s get started covering the creation and use of formulas and functions in Microsoft 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.

The Developer tab stores options to create macros and controls. You can record macros to perform commands with a single button click and assign actions to controls. Below there are tips that cover developer tools in Microsoft Excel.

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.

 

Formulas and functions in Excel are used to calculate values in a cell, allowing you to use your workbook as a calculator. You can add, multiply, divide, and subtract values with formulas. These Pro Tips below will teach you how to calculate cell values with formulas. This may be a feature you are familiar with, but because of the many steps, it is easily forgotten.

Calculate the Sum

  1. Select the cell where the formula will be entered.
  2. Click the Home tab.
  3. In the Editing group, click Sum.
  4. Press Enter.

Construct a Formula

  1. There are hundreds of formulas that promote productivity in Excel. Formulas allow you to easily calculate a cell value.
  2. To insert the formula, select the appropriate cell.
  3. Enter an equal sign.
  4. Enter the equation of the formula using functions, numbers, cell references, and mathematical operators.
  5. Press Enter.

Calculate the Median of a Group of Numbers

  1. The MEDIAN function displays the middle number in the chosen cell range.
  2. Select the cell where the formula will be entered.
  3. Enter =MEDIAN, open parenthesis, the appropriate cell range, and close parenthesis.
  4. Press Enter.

Multiply Numbers in a Cell

  1. Select the cell where the formula will be entered.
  2. Enter an equal sign, followed by the appropriate numbers or cells separated by asterisks.
  3. Press Enter.

 

Just like using formulas to calculate information, data tools are another way you can analyze data in Microsoft Excel. For example, the AutoSum tool can be used to calculate the sum of data. You can also remove duplicate values, helping you locate and remove any values that may be present in your workbook. These last few tips will show you how to use data tools in Microsoft Excel.

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.

 

Hopefully you do not feel too overwhelmed by all of this information. Formulas and the functions that create those formulas are essential to know when working in Excel. Bookmark this page and come back frequently to practice these tips mentioned above. Make using formulas and functions in Excel easy by setting a goal to master everything you’ve learned in this article. You can do it, and we can help!

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!