Top Excel Functions Every Engineer Should Master

Posted on by Engr. Hamid Ali

Excel is an indispensable tool for engineers, offering powerful functions to simplify complex calculations, analyze data, and optimize workflows. In this Post, we’ll explore the top Excel functions every engineer should master to enhance productivity and tackle technical challenges with ease.

COUNTIFS

The COUNTIFS function in Excel is used to count the number of rows that meet multiple criteria. It is an extension of the COUNTIF function, which only allows for a single condition. COUNTIFS can handle multiple ranges and criteria, making it a powerful tool for filtering and counting data.

Syntax:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

Where

  • Criteria_range1: The first range of cells to evaluate.
  • Criteria1: The condition that must be met in criteria_range1.
  • Criteria_range2, criteria2: (Optional) Additional ranges and their corresponding criteria. You can include up to 127 range/criteria pairs.

Key Points about COUNTIFS function in Excel:

  • All criteria must be met: COUNTIFS counts only the rows where all the specified conditions are true.
  • Wildcards: You can use wildcards like * (matches any sequence of characters) and ? (matches a single character) in text criteria.
  • Logical operators: You can use logical operators like >, <, >=, <=, <>, and = for numeric or date criteria.
  • Case-insensitive: Text criteria are case-insensitive (e.g., “apple” and “Apple” are treated the same).

Examples

The formula =COUNTIFS(A:A,”*DSH*”,B:B,”>65″) is used in Excel to count the number of rows that meet multiple criteria. Here’s a breakdown of what it does:

  • A:A, “*DSH*”: This part checks column A for cells that contain the text “DSH” anywhere within the cell. The asterisks (*) are wildcards that match any sequence of characters, so *DSH* will match any cell that includes “DSH” as part of its text.
  • B:B, “>65”: This part checks column B for cells where the value is greater than 65.
  • COUNTIFS: This function counts the number of rows where both conditions are met simultaneously. In other words, it counts the number of rows where:
    • Column A contains “DSH” (anywhere in the text).
    • Column B has a value greater than 65.

You may also like

INDEX-MATCH

The INDEX-MATCH combination is a powerful and flexible alternative to VLOOKUP or HLOOKUP in Excel. It allows you to look up a value in a table based on a specific condition and return a corresponding value from another column or row. Here’s a detailed explanation of how it works:

How INDEX-MATCH Works

1. INDEX Function

The INDEX function returns the value of a cell in a specific row and column within a given range.

  • Syntax: =INDEX(array, row_num, [column_num])
    • array: The range of cells from which to retrieve the value.
    • row_num: The row number in the array from which to return the value.
    • [column_num]: (Optional) The column number in the array from which to return the value. If omitted, the entire row is returned.

 MATCH Function

The MATCH function searches for a specified value in a range and returns the relative position of that value.

  • Syntax:excelCopy=MATCH(lookup_value, lookup_array, [match_type])
    • lookup_value: The value you want to find.
    • lookup_array: The range of cells to search for the lookup_value.
    • [match_type]: Specifies how the match is performed:
      • 0: Exact match.
      • 1: Less than (requires the lookup array to be sorted in ascending order).
      • -1: Greater than (requires the lookup array to be sorted in descending order).

Combining INDEX and MATCH

When combined, INDEX-MATCH allows you to look up a value in one column and return a corresponding value from another column.

General Formula:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
  • return_range: The range of cells from which to return the value.
  • lookup_value: The value you want to find.
  • lookup_range: The range of cells to search for the lookup_value.
  • 0: Ensures an exact match.

Contrl+Enter

If you have applied a filter on C1 and want to change the values in C2 for all visible rows that have the same item, you can use the following steps to efficiently update multiple rows at once:

Steps to Change Multiple Rows in C2 for Filtered Items

  1. Apply the Filter:
    • Ensure the filter is applied on C1 so only the relevant rows are visible.
  2. Select the Visible Cells in C2:
    • Click on the first visible cell in C2 (below the header).
    • Scroll down to the last visible cell in C2.
    • Hold down the Shift key and click on the last visible cell in C2 to select all visible cells in the column.
  3. Enter the New Value:
    • Type the new value you want to apply to all selected cells.
    • Instead of pressing Enter, press Ctrl + Enter. This will fill the new value into all the selected visible cells.

Add a Hyperlink Using Ctrl + K

Pressing Ctrl + K in Excel opens the Insert Hyperlink window, allowing you to add or edit a hyperlink in a selected cell. Here’s how to use it effectively:

How to Add a Hyperlink Using Ctrl + K

  1. Select the cell where you want to add the hyperlink.
  2. Press Ctrl + K (or right-click and select Hyperlink).
  3. In the Insert Hyperlink window, choose one of the following:
    • Existing File or Web Page – To link to a website or file.
    • Place in This Document – To link to another sheet or specific cell.
    • Create New Document – To create and link to a new file.
    • Email Address – To open an email client when clicked.
  4. Click OK to apply.

How to Edit or Remove a Hyperlink

  • Edit: Select the hyperlinked cell and press Ctrl + K again to modify it.
  • Remove: Right-click the cell and choose Remove Hyperlink.

If your issue is that clicking in the cell allows editing instead of following the link, see the solutions in my previous message. Let me know if you need further clarification! 🚀