Excel Data Interview Series: Formulas in Excel

Excel Love Story from Dribbble 💚

Hey there, everyone. Welcome to Day 3 of the Excel Data Analyst Interview Questions series. Following up on our previous discussion of Array Formulas in Excel, today, we’ll be talking on the (Regular) Formulas in Excel.

Excel has many built-in formulas that can be used to perform calculations on data. To create a formula in Excel, start by selecting a cell and typing the equal sign (=), followed by an operator and the cell or cells you want to use in the calculation. Press Enter to see the result of the calculation in the cell with the formula.

Some common formulas in Excel:
• SUM (this adds all the numbers in a range of cells)

This adds up the amounts spent on Meat Products

• SUMIF (in this, you can add an ‘IF’ statement for when it meets a criterion. E.g. SUMIF(K2:K2241, “>150”) calculates all the figures greater than 150 in the K column)

• SUMIFS (this needs multiple conditions. It adds the cells specified by a given set of conditions)
  • COUNT (this gives a count of the number of cells in a range)
  • COUNTIF (this counts the number of cells within a range that meet the given condition)
  • COUNTIFS (this counts the number of cells specified by a given set of conditions or criteria), e.g. from our Marketing Campaign dataset, let’s get a count of the number of customers who are single, have a Masters degree, and earn more than 50,000.

• MAX (this returns the highest value from a selected range of cells)
  • MIN (opposite of MAX, this returns the minimum value from a selected range of cells)
  • IF (this allows you to compare a value with a condition and return different results based on whether the condition is True or False)

• IFS (in this, you can have multiple conditions)
  • LEFT (this returns the specified number of characters from the start of a string)

• RIGHT (this returns the specified number of characters from the end of a string)
• MID (this extracts the characters from the middle of a string)
• TRIM (this removes all spaces from a string except for single spaces between words)
• CONCAT (‘Concatenate’; used to join several strings into one. Can be used to add first and last names together)

• SUBSTITUTE (this replaces existing text with a new text in a string)
  • REPLACE (used to replace part of a string with a new string)
  • UPPER (this converts all the letters in the text to uppercase)
  • LOWER (this converts all the letters in the text to lowercase)
  • PROPER (this converts the string to the proper case)

• NOW (this returns the current time and date)
• TODAY (this returns the current date)

That will be all for today on Formulas in Excel. If you enjoyed this, you know the drill already- claps and comments. Similar to SQL Functions, a lot of these formulas come in really handy during Data Cleaning. Be here tomorrow for our discussion on Pivot Tables. 🤗