Become a Data Analyst by Mastering Several Excel Formulas

In this digital era, data analysis skills are becoming more important in various fields of work. One of the most commonly used tools for data analysis is Microsoft Excel. By mastering some Excel formulas, you can improve your data analysis skills and become a reliable analyst.

For example, you’ve got a lot of raw and confusing data, and if you work in an office, you definitely need to get tasks done quickly. So, you should be able to use some of these key Excel formulas to finish the job:

CONCATENATE

Concatenate lets you combine the values of several cells into one. It’s one of the most powerful formulas for doing data analysis, and it’s usually used to merge text, dates, and numbers.

Use this:

• =CONCATENATE(text1, [text2], ...)
• Example: =CONCATENATE(A1, " ", B1) (combining the content of cell A1, a space, and the content of cell B1)
• The operator & can also be used as an alternative: =A1 & " " & B1

VLOOKUP

This formula lets you find data organized in a vertical column. For example, if you have a list of months in a year with profits made each month, =VLOOKUP can show you the data for your chosen month.

Use this:

• =VLOOKUP(value_to_find, table_array, column_number, [range_lookup])
• Example: =VLOOKUP(D2, A2:C10, 2, FALSE) (looks for the value in D2 in the first column of A2:C10, then returns the value from the second column)

LEN

Use this in data analysis to show the number of characters in a particular cell. It’s often used when creating text with character limits, or when trying to identify differences between product numbers.

Use this:

• =LEN(text)
• Example: =LEN(A1) (counts the number of characters in cell A1)

SUMIFS

The formula adds up the values in cells which meet a selected number. In the above example, the formula is adding up the numbers in cells that are higher than the number 100.

Use this:

• =SUMIFS(range, range_criterion1, criterion1, [range_criterion2, criterion2], …)
• Example: =SUMIFS(C2:C10, A2:A10, "Jakarta", B2:B10, ">100") (summing up the values in C2:C10 if A2:A10 is “Jakarta” and B2:B10 is greater than 100)

DAYS/NETWORKDAYS

This one is quite clear. =DAY determines the number of days between two calendar dates, and is usually used to determine product life cycles or contract periods. DAYS counting the difference in calendar days and NETWORKDAYS counts workdays, excluding weekends and optional holidays.

Use this:

1. Days
   • This formula returns the number of days between two dates.
   • How to use: =DAYS(end_date, start_date)
   • Example: =DAYS("2024/12/31","2024/01/01")
2. Networkdays
   • This formula returns the number of working days between two dates.
   • How to Use: =NETWORKDAYS(start_date, end_date, [holidays])
   • Example: =NETWORKDAYS("2024/01/01","2024/12/31")

SUBSTITUTE

This is a handy trick that’s perfect for updating cells in bulk. It’s one of those that does exactly what it says and is super useful for updating URLs, removing accidental spaces, or spelling mistakes.

Use this:

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• Example: =SUBSTITUTE(A1, "old", "new") (replaces “old” with “new” in cell A1)

MINIFS/MAXIFS

This handy formula identifies the lowest and highest values. But it does more than that; it also sorts the values based on certain criteria. For example, you can use it to rank the oldest and youngest ages from a sample of men and women, showing the values by gender. MINIFS returns the minimum value in a range that meets multiple criteria and MAXIFS returns the maximum value in a range that meets multiple criteria.

Use this:

1. MINIFS: =MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )
2. MAXIFS: =MAXIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

TRIM

This formula lets you remove unwanted spaces or characters from the text. So that analysts can work with the data, you often need to clean the data before it’s used. =TRIM is one of many useful formulas that cleans the data before analysis.

Use this:

• =TRIM(text)
• =TRIM(A1) (removes extra spaces in cell A1)

COUNTIFS

This formula counts how many times a value appears based on one criterion. In this example, for instance, we want to count how many times “Jakarta” appears in a cell, and that number is returned in the COUNTIFS cell.

Use this:

• =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
• Example: =COUNTIFS(A2:A10, "Jakarta", B2:B10, ">100") (counts the number of cells in A2:A10 that are “Jakarta” and B2:B10 greater than 100)

LEFT/RIGHT

The formulas are simple ways to extract data from a static cell. =LEFT returns a certain number of characters from the left side of the text string., while =RIGHT returns a certain number of characters from the right side of the text string.

Use this:

1. LEFT
   • Usage: =LEFT(text, [number_of_characters])
   • Example: =LEFT(A1, 3) (takes 3 characters from the left of cell A1)
2. RIGHT
   • Usage: =RIGHT(text, [number_of_characters])
     Example: =RIGHT(A1, 4) (takes 4 characters from the right of cell A1)

Additional tips

• Master Excel keyboard shortcuts to boost efficiency.
• Use Conditional Formatting features for better data visualization.

By mastering these Excel formulas, you’re one step closer to becoming a reliable data analyst. Keep practicing and explore other Excel features to boost your skills.