Microsoft Excel is a powerful spreadsheet application used for data analysis, calculation, and reporting. To fully utilize Excel’s capabilities, it’s essential to understand and use a variety of functions and formulas. Below is a list of Excel functions and formulas that you should use regularly, along with detailed explanations of each.

1. Date and Time Excel Functions:

a. DATE(year, month, day):

• Description: Returns the serial number representing a specified date.
• Usage: DATE(2023, 8, 23) returns the date August 23, 2023.
• Note: Excel stores dates as serial numbers.

b. DAYS360(start_date, end_date):

• Description: Returns the number of days between two dates based on a 360-day year.
• Usage: DAYS360(A1, B1) calculates the number of days between dates in cells A1 and B1.
• Use: Commonly used in accounting calculations.

 

c. NOW():

• Description: Returns the current date and time.
• Usage: NOW() returns the current date and time.
• Note: The result is formatted as a date and time.

d. TODAY():

• Description: Returns the current date.
• Usage: TODAY() returns the current date.
• Note: The result is formatted as a date.

e. WORKDAY(start_date, days, holidays):

• Description: Returns a date a specified number of working days before or after a given date, excluding weekends and holidays.
• Usage: WORKDAY(A1, 5, B1:B10) calculates a date five working days after the date in A1, using holidays listed in cells B1 to B10.
• Use: Useful for calculating due dates or project timelines.

f. WORKDAY.INTL(start_date, finish_day, weekend, holidays):

• Description: Similar to WORKDAY but allows you to specify the weekend days.
• Usage: WORKDAY.INTL(A1, 5, “0000011”, B1:B10) calculates a date five working days after A1, considering weekends on Saturday and Sunday (as specified) and using specified holidays.

---

2. Logical Excel Functions:

a. AND(logical1, logical2, …):

• Description: Returns TRUE if all arguments are TRUE; otherwise, returns FALSE.
• Usage: AND(A1>5, B1<10) returns TRUE if both conditions are met.

b. IF(logical_test, value_if_true, value_if_false):

• Description: Returns one value if a condition is TRUE and another if it’s FALSE.
• Usage: IF(A1>10, “Yes”, “No”) returns “Yes” if the condition is met; otherwise, “No.”

---

3. Lookup and Reference Excel Functions:

a. HLOOKUP(lookup_value, table_array, row_index_num, range_lookup):

• Description: Searches for a value in the top row of a table and returns a value from a specified row.
• Usage: HLOOKUP(A1, B1:E10, 3, FALSE) searches for A1 in the top row of the table and returns the value from the third row.

b. VLOOKUP(lookup_value, table_array, col_index_num, range_lookup):

• Description: Searches for a value in the first column of a table and returns a value from a specified column.
• Usage: VLOOKUP(A1, B1:E10, 2, FALSE) searches for A1 in the first column of the table and returns the value from the second column.

 

---

4. Math and Trigonometry Excel Functions:

a. ROUND(number, num_digits):

• Description: Rounds a number to a specified number of digits.
• Usage: ROUND(A1, 2) rounds the value in A1 to two decimal places.

b. SUM(number1, number2, …):

• Description: Adds all numbers in a range of cells.
• Usage: SUM(A1:A10) adds the values in cells A1 through A10.

c. SUMIF(range, criteria, sum_range):

• Description: Adds cells specified by a given criteria.
• Usage: SUMIF(B1:B10, “>50”, C1:C10) sums values in cells C1:C10 where the corresponding values in B1:B10 are greater than 50.

---

5. Statistical Excel Formulas:

a. AVERAGE(number1, number2, …):

• Description: Returns the average (arithmetic mean) of the arguments.
• Usage: AVERAGE(A1:A10) calculates the average of values in cells A1 through A10.

b. COUNT(value1, value2, …):

• Description: Counts the number of cells that contain numbers or entries.
• Usage: COUNT(A1:A10) counts the number of non-empty cells in the range A1:A10.

c. COUNTIF(range, criteria):

• Description: Counts the number of cells within a range that meet specific criteria.
• Usage: COUNTIF(B1:B10, “<30”) counts cells in B1:B10 that are less than 30.

d. MAX(number1, number2, …):

• Description: Returns the largest value in a set of values.
• Usage: MAX(A1:A10) finds the maximum value in cells A1 through A10.

e. MIN(number1, number2, …):

• Description: Returns the smallest value in a set of values.
• Usage: MIN(A1:A10) finds the minimum value in cells A1 through A10.

These Excel functions and formulas are essential tools for data analysis, reporting, and decision-making in various professional and personal contexts. By mastering these functions, you can enhance your proficiency in Microsoft Excel and improve your ability to work with data effectively.

Do you need to learn more about Excel in Planning? I highly recommend this course: Data Management Course

---

Download the PDF version of this Article to share with your colleagues