Formulas

Formulas Interview with follow-up questions

1. Can you explain how to create a basic formula in Excel?

Every formula in Excel starts with an equals sign (=). Without it, Excel treats the entry as plain text.

Basic structure:

The expression can include cell references (=A1+B1), constants (=100*1.2), operators (+, -, *, /, ^ for exponent, & for text join, and comparison operators), functions (=SUM(A1:A10)), or combinations of all of these.

Example:

To add values in A1 and B1 then multiply by a tax rate in C1:

=(A1+B1)*C1

Parentheses control order of operations — Excel evaluates multiplication before addition, so they are essential here.

Entering a formula:

  1. Click the destination cell.
  2. Type = then the expression.
  3. Press Enter to confirm and move down, or Ctrl+Enter to confirm and stay in the same cell.
  4. The formula bar shows the formula; the cell displays the calculated result.

Editing a formula: Press F2 to enter edit mode on the active cell, or click directly in the formula bar.

Common mistake: Omitting the = sign — Excel stores the text literally instead of evaluating it.

↑ Back to top

Follow-up 1

What are the different types of operators that can be used in Excel formulas?

There are several types of operators that can be used in Excel formulas:

  1. Arithmetic Operators: These include addition (+), subtraction (-), multiplication (*), division (/), and exponentiation (^).
  2. Comparison Operators: These include equal to (=), not equal to (<>), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=).
  3. Logical Operators: These include AND, OR, and NOT.
  4. Reference Operators: These include the range operator (:) and the intersection operator (,).

Follow-up 2

Can you provide an example of a formula using the division operator?

Sure! Here's an example of a formula using the division operator (/):

=A1/B1

This formula will divide the value in cell A1 by the value in cell B1 and display the result.

Follow-up 3

How would you handle errors that occur when executing a formula?

Excel provides several functions to handle errors that occur when executing a formula. Some of the commonly used error handling functions include:

  1. IFERROR: This function allows you to specify a value or expression to display if an error occurs in a formula.
  2. ISERROR: This function returns TRUE if the specified cell contains an error value.
  3. ISNA: This function returns TRUE if the specified cell contains the #N/A error value.
  4. ISERR: This function returns TRUE if the specified cell contains any error value except for #N/A.

You can use these functions in combination with conditional statements to handle errors in your formulas.

Follow-up 4

What is the order of operations in Excel formulas?

The order of operations in Excel formulas follows the acronym PEMDAS, which stands for:

  1. Parentheses: Operations inside parentheses are performed first.
  2. Exponents: Exponentiation is performed next.
  3. Multiplication and Division: These operations are performed from left to right.
  4. Addition and Subtraction: These operations are performed from left to right.

It's important to use parentheses to specify the order of operations when necessary, especially when you have complex formulas with multiple operators.

2. What is the difference between absolute and relative cell references in Excel formulas?

In Excel formulas, a cell reference identifies the cell whose value you want to use. References behave differently when a formula is copied or filled to other cells.

Relative reference (e.g., A1)

The reference adjusts relative to where the formula moves. If =A1+B1 is in cell C1 and you copy it down to C2, Excel automatically changes it to =A2+B2. This is the default and is ideal when you apply the same logic across many rows or columns.

Absolute reference (e.g., $A$1)

The $ sign locks the reference so it never changes regardless of where the formula is copied. $A$1 always points to A1. Use this for fixed values shared across many formulas — a tax rate, an exchange rate, a bonus percentage stored in one cell.

Mixed reference (e.g., $A1 or A$1)

Locks either the column ($A1) or the row (A$1) but not both. Useful when building grids — for example, a multiplication table where the formula must stay in one column but move freely across rows.

Toggling with F4:

While the cursor is inside a cell reference in the formula bar, press F4 to cycle through all four states:

A1 → $A$1 → A$1 → $A1 → A1

Practical example: To calculate commission for each salesperson in B2:B20 using a fixed rate in cell D1:

=B2*$D$1

Copy this formula down the column — B2 shifts to B3, B4, etc., but $D$1 always points to the rate cell.

↑ Back to top

Follow-up 1

Can you provide an example where absolute cell reference is necessary?

Sure! Let's say you have a spreadsheet where you want to calculate the total sales for each product. You have a column with the quantity sold and a column with the price per unit. To calculate the total sales for each product, you would multiply the quantity sold by the price per unit.

To do this, you can use a formula like =B2*C2, assuming that the quantity sold is in column B and the price per unit is in column C. However, if you want to copy this formula to calculate the total sales for other products, you need to use absolute cell references for the column with the price per unit. This is because you want the formula to always refer to the same column, regardless of where it is copied.

To use an absolute cell reference, you can modify the formula to =B2*$C$2. Now, when you copy this formula to other cells, the column reference ($C$2) will remain constant, ensuring that the correct price per unit is used for each product.

Follow-up 2

How would you copy a formula with relative cell references?

To copy a formula with relative cell references, you can use the fill handle in Excel. Here's how:

  1. Select the cell containing the formula that you want to copy.
  2. Move your cursor to the bottom-right corner of the selected cell until it turns into a small black crosshair.
  3. Click and drag the fill handle across the range of cells where you want to copy the formula.

As you drag the fill handle, Excel will automatically adjust the cell references in the formula based on the relative position of each cell. For example, if the original formula references cell A1 and you copy it to cell B1, the formula will automatically adjust to reference cell B1.

Note that if you want to copy the formula without adjusting the cell references, you can use absolute cell references by adding dollar signs ($) before the column letter and row number in the formula.

Follow-up 3

What happens if you don't use the correct cell reference in a formula?

If you don't use the correct cell reference in a formula, the formula may not produce the expected results or may result in an error.

Here are a few scenarios:

  • If you use a relative cell reference incorrectly, the formula may refer to the wrong cells when copied or moved. This can lead to incorrect calculations or referencing of unintended data.

  • If you use an absolute cell reference incorrectly, the formula may always refer to the same cell, even when it should be adjusted. This can result in incorrect calculations or referencing of outdated data.

  • If you use a cell reference that doesn't exist or is invalid, the formula will result in an error. Excel will display an error message, such as #REF! or #VALUE!, indicating that the formula contains an invalid reference.

To avoid these issues, it's important to double-check and verify the cell references used in your formulas.

3. How would you use a formula to calculate the sum of a range of cells in Excel?

Use the SUM function to add up a range of cells:

=SUM(range)

Examples:

  • =SUM(A1:A10) — adds all values in cells A1 through A10.
  • =SUM(A1:A10, C1:C10) — adds two separate ranges together.
  • =SUM(A1, B3, C7) — adds individual non-contiguous cells.
  • =SUM(A:A) — sums the entire column A (handy but slightly slower on very large sheets).

Keyboard shortcut: Select a cell directly below or to the right of a data range and press Alt + = — Excel inserts =SUM(...) with the range pre-filled automatically.

Conditional summing (common follow-up):

  • =SUMIF(range, criteria, sum_range) — sums where one condition is met. Example: =SUMIF(B2:B100,"North",C2:C100) sums column C where column B equals "North".
  • =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...) — sums where multiple conditions are all true.

Excel 365 — combining SUM with dynamic arrays:

SUM works naturally with spill ranges. For example, to sum a filtered subset without a helper column:

=SUM(FILTER(C2:C100, B2:B100="North"))

Interviewers often follow up by asking the difference between SUM and SUMIF — be ready to give a real-world example for each.

↑ Back to top

Follow-up 1

What would you do if the range of cells includes non-numeric data?

If the range of cells includes non-numeric data, the SUM function will ignore those cells and only sum the numeric values. For example, if you have a range of cells A1 to A5, and A3 contains the text 'N/A', the formula =SUM(A1:A5) will still calculate the sum of A1, A2, A4, and A5, excluding A3.

Follow-up 2

Can you explain how to use the SUM function in a formula?

Yes, the SUM function is used to add up a range of cells in Excel. To use the SUM function, you need to provide the range of cells you want to sum as an argument. For example, to sum the values in cells A1 to A5, you would use the formula =SUM(A1:A5). You can also use the SUM function with multiple ranges by separating them with commas. For example, =SUM(A1:A5, B1:B5) will sum the values in both ranges.

Follow-up 3

What other functions can be used in formulas to perform calculations on a range of cells?

There are many other functions that can be used in formulas to perform calculations on a range of cells in Excel. Some commonly used functions include:

  • AVERAGE: Calculates the average of a range of cells.
  • MAX: Returns the largest value in a range of cells.
  • MIN: Returns the smallest value in a range of cells.
  • COUNT: Counts the number of cells in a range that contain numbers.
  • COUNTA: Counts the number of cells in a range that are not empty.
  • SUMIF: Calculates the sum of a range of cells based on a specified condition.
  • PRODUCT: Calculates the product of a range of cells.

These are just a few examples, and there are many more functions available in Excel for performing calculations on ranges of cells.

4. Can you explain how to use a formula to conditionally calculate a value in Excel?

The IF function is the core tool for conditional calculations in Excel:

=IF(logical_test, value_if_true, value_if_false)
  • logical_test: any expression that evaluates to TRUE or FALSE — e.g., A1&gt;100, B2="Yes", C3&lt;&gt;0
  • value_if_true: what to return when the condition is TRUE
  • value_if_false: what to return when the condition is FALSE (can be left empty, which returns 0 or an empty string)

Example:

=IF(B2&gt;=60, "Pass", "Fail")

Returns "Pass" if B2 is 60 or above, "Fail" otherwise.

Nested IFs — multiple conditions:

=IF(B2&gt;=90, "A", IF(B2&gt;=80, "B", IF(B2&gt;=70, "C", "F")))

This works but becomes difficult to read beyond 2–3 levels. In Excel 365, prefer IFS:

=IFS(B2&gt;=90,"A", B2&gt;=80,"B", B2&gt;=70,"C", TRUE,"F")

IFS evaluates conditions left to right and returns the value paired with the first TRUE condition. The TRUE at the end acts as a catch-all default.

SWITCH for discrete values:

=SWITCH(A2, "N","North", "S","South", "E","East", "Other")

Cleaner than nested IF when matching a value against a fixed list of options.

Related conditional functions:

  • SUMIF / SUMIFS — conditional summing
  • COUNTIF / COUNTIFS — conditional counting
  • AVERAGEIF / AVERAGEIFS — conditional averaging

Excel 365 — FILTER for conditional arrays:

=FILTER(A2:C100, B2:B100="North")

Returns all rows where column B equals "North" — more powerful than IF alone when you need to extract a subset of data.

↑ Back to top

Follow-up 1

Can you provide an example of a formula using the IF function?

Sure! Here's an example of a formula using the IF function in Excel:

=IF(A1&gt;10, "Greater than 10", "Less than or equal to 10")

In this example, if the value in cell A1 is greater than 10, the formula will return the text "Greater than 10"; otherwise, it will return the text "Less than or equal to 10".

Follow-up 2

What is the syntax of the IF function in Excel?

The syntax of the IF function in Excel is as follows:

=IF(condition, value_if_true, value_if_false)

The condition is a logical expression that evaluates to either true or false. If the condition is true, the value_if_true is returned; otherwise, the value_if_false is returned.

Follow-up 3

How would you use nested IF functions in a formula?

Nested IF functions in Excel allow you to perform multiple levels of conditional calculations. You can nest one IF function inside another to create complex logical expressions. Here's an example of a formula using nested IF functions:

=IF(A1&gt;10, "Greater than 10", IF(A1&gt;5, "Greater than 5", "Less than or equal to 5"))

In this example, if the value in cell A1 is greater than 10, the formula will return the text "Greater than 10". If the value is not greater than 10 but is greater than 5, the formula will return the text "Greater than 5". Otherwise, it will return the text "Less than or equal to 5".

5. How would you use a formula to combine text from two or more cells in Excel?

In Excel 365 there are several ways to combine text from multiple cells. Choose based on how many cells you are joining and whether you need a delimiter.

1. Ampersand operator &amp; (best for simple joins)

=A1&amp;" "&amp;B1

Joins A1, a literal space, and B1. Clean and fast for two or three values. You can mix cell references and literal text: =A1&amp;", "&amp;B1&amp;" "&amp;C1.

2. TEXTJOIN (best for ranges or consistent delimiters)

=TEXTJOIN(", ", TRUE, A1:A10)
  • First argument: the delimiter placed between each value.
  • Second argument: TRUE to skip empty cells, FALSE to include them (leaving blank gaps).
  • Third argument onward: the cells or ranges to join.

This is the modern choice when joining many values — far cleaner than chaining &amp; operators.

3. CONCAT (replaces the old CONCATENATE)

=CONCAT(A1, " ", B1)

Accepts ranges (=CONCAT(A1:A5)) but uses no delimiter — all values are joined directly. Use TEXTJOIN when you need a separator.

4. CONCATENATE (legacy — avoid in new work)

=CONCATENATE(A1, " ", B1)

CONCATENATE does not accept ranges and is superseded by CONCAT and TEXTJOIN. It still works for backward compatibility, but avoid writing it in new formulas.

Interview tip: Interviewers often ask how you would join a list of names with commas. The answer is TEXTJOIN(", ", TRUE, A1:A20). Know why TEXTJOIN is preferred over CONCATENATE and be able to explain the ignore_empty argument.

↑ Back to top

Follow-up 1

What is the syntax of the CONCATENATE function in Excel?

The syntax of the CONCATENATE function in Excel is as follows:

=CONCATENATE(text1, text2, ...)

Where text1, text2, etc. are the text strings you want to combine. You can specify any number of text strings as arguments to the CONCATENATE function.

Follow-up 2

Can you provide an example of a formula using the CONCATENATE function?

Sure! Here's an example of a formula using the CONCATENATE function in Excel:

=CONCATENATE("Hello", " ", "World")

This formula will combine the text strings "Hello", " ", and "World" into one string, resulting in the output "Hello World".

Follow-up 3

What other functions can be used in formulas to manipulate text in Excel?

There are several other functions in Excel that can be used to manipulate text in formulas. Some commonly used text manipulation functions in Excel include:

  • LEFT: Returns a specified number of characters from the start of a text string.
  • RIGHT: Returns a specified number of characters from the end of a text string.
  • MID: Returns a specified number of characters from a text string, starting at a specified position.
  • LEN: Returns the number of characters in a text string.
  • FIND: Returns the position of a specific character or text string within another text string.

These functions can be combined with other functions and operators to perform various text manipulation tasks in Excel.

Live mock interview

Mock interview: Formulas

Intermediate ~5 min Your own free AI key

Your voice and your AI key never touch our servers; the key stays in this browser and is sent only to Google. Only your round scores are saved to track progress.