1  Data Analysis with MS Excel

Excel is a powerful tool for data analysis, and it is widely used in the industry. This course will help you to get familiar with Excel by using several types of graphs, tables, and functions. You will learn how to navigate, analyze, clean, sort, and represent data (graphically or numerically). You will also learn how to make pivot tables based on data and apply them in graphs.

1.1 Excel Installation

Microsoft Office 365 is available for UT  students; you can access (via Cloud) and/or install:

  • Word
  • Excel
  • PowerPoint
  • Teams
  • Outlook
  • OneDrive

Learn more about obtaining MS Office 365 (Excel) at:  https://www.utwente.nl/en/lisa/faq/office365-faq/. 

1.2 Excel materials

See all study materials (manual, exercises, previous exams) on Introduction to Data Analysis with Microsoft Excel (Canvas/University of Twente).

1.2.1 Learning goals

In this course, you will learn how to use Excel to:

  • Import and export data
  • Manipulate data
  • Analyze data
  • Visualize data

The content list below provides a comprehensive overview of the skills you will acquire during the course.

1.2.1.1 Import/Export data

1.2.1.2 Manipulate data

1.2.1.3 Analyze data

1.2.1.4 Visualize data*

Relevant skills when you are using generated figures and tables on reports:

1.2.2 Where to find data?

If you want to hone your data analysis skills, you can find publicly available datasets at Dataset Search (google.com).

TIP: Search for keywords within the IEM domain (e.g., “inventory management”) and check how people normally represent related data.

1.3 Excel study guide

The Excel study order (from novice to expert) is as follows:

  1. Go through the manual and complete its exercises.
  2. Complete the two exercises:
  3. Complete at least one practice exam (suggested order 3, 2, 1, from most recent to oldest).
  4. Explore the learning goals content list (for example, Insert data from picture and Analyze data automatically).

Save and submit all the required files on Canvas to get acquainted with the submission system that will be used during the exam.

1.4 Multiple-Choice Questions

Some questions below refer to the data saved in an Excel file. The data is presented in two sheets:

  • consultation_history: Patient consultation history (see Table 1.1).
  • patient_data: Name and gender of patients (see Table 1.2).
Table 1.1: Patient consultation history (Sheet consultation_history).
A B C D E F G H I J K
1 Patient ID Week Day OR Gender Age Expected duration Order of surgery Surgery type Registered duration Surgery types
2 70 0 1 1 male 83 62.44 13 WP174 160.61 AF241
3 1215 5 3 2 male 48 58.0 10 ID810 158.21 AX939
4 1439 6 4 0 male 61 58.0 10 ID810 151.68 BP285
5 502 2 1 2 male 43 58.0 11 ID810 142.66 CP913
6 1120 5 1 1 male 65 62.44 13 WP174 142.47 GV023
Table 1.2: Patient names and genders (Sheet patient_data).
A B
1 Patient name Gender
2 Leslie Fields male
3 Arnold Ross male
4 Craig Vega male
5 Doyle Banks Junior male
Excel Formulas: Comma vs. Semicolon

In some countries, the comma (,) is used as a decimal separator, and the semicolon (;) is used as a list separator. If you are using Excel in a country where the comma is used as a decimal separator, you may need to use the semicolon as a list separator in Excel formulas.

In Excel, you can check your list separator by going to File > Options > Advanced > Editing options > Use system separators.

Also, if you hover over the formula in Excel, you will see the correct separator for your system.

In the examples below, we use the colon as a list separator. Therefore, if copy the formulas to Excel, you may need to replace the colon with a semicolon.

1.4.1 Conditional Evaluation

In cell I2, there is a value “WP174” (Surgery type). If we apply the Excel formula =IF(I2="WP174", "Correct", "Incorrect") on Table 1.1, what will be the result?

  • WP174
  • Correct
  • Incorrect
  • Error

Answer: B) Correct

The formula will return “Correct” because the value in cell I2 is “WP174.”

1.4.2 Vertical Lookup

If you use the Excel formula =VLOOKUP(502, A2:K6, 9, FALSE) on Table 1.1, what will it return?

  • 2 (value in the “OR” column for Patient ID 502)
  • 43 (value in the “Age” column for Patient ID 502)
  • ID810 (value in the “Surgery type” column for Patient ID 502)
  • 1 (value in the “Week” column for Patient ID 502)
VLOOKUP Function

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_value: The value to search for in the first column of the table_array.
  • table_array: The range of cells that contains the data.
  • col_index_num: The column number in the table_array from which to retrieve the value (indexed from 1).
  • range_lookup: A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match.

Answer: C) ID810

The formula =VLOOKUP(502, A2:K6, 9, FALSE) will return “ID810” because it looks up the value 502 in the first column of the range A2:K6 and returns the value in the 9th column (Surgery type) when an exact match is found.

1.4.3 Average Calculation

If you enter the formula =AVERAGE(G2:G6) in Excel considering the data in Table 1.1, what will it calculate?

  • The average of the values in the “Age” column
  • The average of the values in the “Expected duration” column
  • The average of the values in the “Registered duration” column
  • The average of the values in the “Order of surgery” column

Answer: B) The average of the values in the “Expected duration” column.

The formula =AVERAGE(G2:G6) will calculate the average of the values in the “Expected duration” column (column G).

1.4.4 Conditional Counting

Considering the data in Table 1.1, what will the formula =COUNTIF(D2:D6, 1) return when used on this dataset?

  • The number of patients scheduled in OR 1
  • The number of patients who had surgery in the 1st week
  • The number of patients with a registered surgery duration of 1 hour
  • The number of patients with a surgery type ID starting with 1
COUNTIF Function

COUNTIF(range, criteria)

  • range: The range of cells that you want to apply the criteria to.
  • criteria: The condition that determines which cells to count.

Answer: A) The number of patients scheduled in OR 1.

The formula =COUNTIF(D2:D6, 1) will return the number of patients scheduled in OR 1 because it counts the occurrences of the value 1 in the “OR” column (column D).

1.4.5 Total Sum

If you use =SUM(G2:G6) in the dataset presented in Table 1.1, what are you summing?

  • The total number of surgeries
  • The total expected surgery duration for the selected patients
  • The total patient ages
  • The total number of patients scheduled in OR 1

Answer: B) The total expected surgery duration for the selected patients.

The formula =SUM(G2:G6) will sum the values in the “Expected duration” column (column G), giving the total expected surgery duration for the selected patients.

1.4.6 Multiple Criteria Sum

Considering the data in Table 1.1, what is the formula =SUMIFS(G2:G6, E2:E6, "male", F2:F6, ">50") calculating?

  • The total expected duration of surgeries for male patients above the age of 50
  • The sum of ages for male patients above 50
  • The total number of surgeries for male patients above 50
  • The total registered duration of surgeries for male patients above 50
SUMIFS Function

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

  • sum_range: The range to sum.
  • criteria_range1: The range to evaluate with criteria1.
  • criteria1: The criteria to apply to criteria_range1.
  • criteria_range2, criteria2, …: Additional ranges and their associated criteria.

Answer: A)

The formula =SUMIFS(G2:G6, E2:E6, "male", F2:F6, ">50") calculates the total expected duration of surgeries for

1.4.7 Multiple Criteria Counting

If you use the formula =COUNTIFS(D2:D6, 1, C2:C6, 1) in the dataset presented in Table 1.1, what is being counted?

  • The number of surgeries that took place in OR 1 during Week 1
  • The number of surgeries that took place for patients in OR 1 during Day 1
  • The number of male patients scheduled in OR 1 during Week 1
  • The number of surgeries that had an expected duration of 1 hour
COUNTIFS Function

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

  • criteria_range1: The range to evaluate with criteria1.
  • criteria1: The criteria to apply to criteria_range1.
  • criteria_range2, criteria2, …: Additional ranges and their associated criteria.

Answer: B)

The formula =COUNTIFS(D2:D6, 1, C2:C6, 1) counts the number of surgeries that took place for patients in OR 1 during Day 1.

1.4.8 Total Surgery Duration

Considering the data in Table 1.1, you want to calculate the total expected surgery duration for male patients over 50 years old. Which of the following formulas would you use?

  • =SUMIFS(F2:F6, E2:E6, "male", G2:G6, ">50")
  • =SUMIFS(G2:G6, E2:E6, "male", F2:F6, ">50")
  • =COUNTIFS(E2:E6, "male", F2:F6, ">50")
  • =SUMIF(F2:F6, ">50", G2:G6)

Answer: B) =SUMIFS(G2:G6, E2:E6, "male", F2:F6, ">50")

Breakdown:

  • G2:G6: The range of values to sum (Expected duration).
  • "male": The criteria
  • E2:E6: The range to evaluate with the criteria
  • F2:F6: Additional criteria for age.
  • ">50": The age condition.

1.4.9 Surgery Count

To count the number of surgeries performed in OR 1 for patients older than 60 in Table 1.1, which formula would give the correct result?

  • =COUNTIFS(D2:D6, 1, F2:F6, ">60")
  • =SUMIFS(D2:D6, D2:D6, 1, F2:F6, ">60")
  • =COUNTIFS(E2:E6, "male", F2:F6, ">60")
  • =COUNTIF(F2:F6, ">60")

Answer: A) =COUNTIFS(D2:D6, 1, F2:F6, ">60")

Breakdown:

  • D2:D6: The range to evaluate with the criteria (OR).
  • 1: The criteria for OR 1.
  • F2:F6: The range to evaluate with the criteria (Age).
  • ">60": The age condition.

1.4.10 Surgery Duration

Considering Table 1.1, ou need to find the total registered surgery duration for surgeries in Week 2. Which formula would be correct?

  • =SUMIFS(J2:J6, B2:B6, 2)
  • =SUMIFS(G2:G6, B2:B6, "Week 2")
  • =COUNTIFS(B2:B6, 2, H2:H6)
  • =SUMIF(B2:B6, 2, H2:H6)

Answer: A) =SUMIFS(J2:J6, B2:B6, 2)

Breakdown:

  • H2:H6: The range of values to sum (Registered duration).
  • B2:B6: The range to evaluate with the criteria (Week).
  • 2: The criteria for Week 2.

1.4.11 Relative Reference Copying

You are copying the formula =A2+B2 from cell C2 to cell C3. What will the formula in cell C3 be?

  • =A3+B3
  • =$A$2+$B$2
  • =A2+B2
  • =$A2+$B2

Answer: A) =A3+B3

When you copy the formula =A2+B2 from cell C2 to C3, the formula will update to =A3+B3 because the relative cell references will shift down by one row.

1.4.12 Absolute Reference Copying

Which of the following formulas will keep the reference to column A fixed, but allow the row number to change as the formula is copied down?

  • =$A2
  • =A$2
  • =$A$2
  • =A2

Answer: A) =$A2

By using the formula =$A2, you fix the reference to column A while allowing the row number to change as the formula is copied down.

This is an example of an absolute column reference and a relative row reference.

1.4.13 Mixed Reference Copying 1

You want to use the formula =B2*$C$1 in column B and drag it down (using the fill handle ✚), so the multiplier $C$1 stays fixed, but B2 updates as you drag. What will be the formula in cell B3 after dragging down?

  • =B3*C1
  • =B2*$C$1
  • =B3*$C$1
  • =B3*$C1

Answer: C) =B3*$C$1

When you drag the formula =B2*$C$1 down to cell B3, the formula will update to =B3*$C$1. This maintains the fixed reference to cell C1 while updating the row reference in column B.

1.4.14 Mixed Reference Copying 2

You copy the formula =$A2+B$1 from cell C2 to D3. What will the new formula in D3 be?

  • =$A3+C$1
  • =$A2+B$1
  • =$A3+B$1
  • =A2+B$1

Answer: A) =$A3+C$1

When you copy the formula =$A2+B$1 from cell C2 to D3, the formula will update to =$A3+C$1. The row reference for column A will change, but the column reference for row 1 will remain fixed.

1.4.15 Relative and Absolute Reference

You want to create a formula that multiplies the values in column A by a constant in cell B1. If you want to copy the formula down without changing the reference to B1, which formula should you use in cell C2?

  • =A2*B1
  • =$A2*B1
  • =A2*$B$1
  • =A2*$B1

Answer: C) =A2*$B$1

By using the formula =A2*$B$1 in cell C2, you ensure that the reference to cell B1 remains fixed when you copy the formula down. This is an example of a relative row reference and an absolute column reference.

1.4.16 Substring Extraction 1

You want to extract the first 6 characters from the patient name in cell A2 (“Leslie Fields”) from Table 1.2. Which formula would you use?

  • =LEFT(A2, 6)
  • =RIGHT(A2, 6)
  • =MID(A2, 6, 6)
  • =TEXT(A2, 6)

Answer: A) =LEFT(A2, 6)

Explanation: The LEFT function extracts the first 6 characters from the string in cell A2, which returns “Leslie”.

1.4.17 Substring Extraction 2

In cell A3 from Table 1.2, the patient name is “Arnold Ross”. You want to extract the last 4 characters of the name. Which formula would you use?

  • =LEFT(A3, 4)
  • =RIGHT(A3, 4)
  • =MID(A3, 4, 4)
  • =TEXT(A3, 4)

Answer: B) =RIGHT(A3, 4)

Explanation: The RIGHT function extracts the last 4 characters from the string in cell A3, which returns “Ross”.

1.4.18 Concatenating Values

You want to concatenate the first name and last name of the patient in A2 and add “is male” in Table 1.2. Which formula will correctly combine these values?

  • =CONCAT(A2, " is ", "male")
  • =A2 & " is " & B2
  • =CONCAT(A2, " ", "is", " ", B2)
  • All of the above

Answer: D) All of the above

Explanation: All three formulas will write “Leslie Fields is male”.

  • =CONCAT(A2, " is ", "male") will concatenate the patient name with " is " and "male".
  • =A2 & " is " & B2 will concatenate the patient name with " is " and B2, which is "male".
  • =CONCAT(A2, " ", "is", " ", B2) will concatenate the patient name with spaces and the word “is” and the B2 value., which is "male".

1.4.19 Finding a Space

You want to find the position of the space in the patient name in cell A2 (“Leslie Fields”) in Table 1.2. Which formula(s) will return the correct position?

  • =SEARCH(" ", A2)
  • =FIND(" ", A2)
  • =LEFT(A2, 1)
  • =MID(A2, 1, 1)
FIND and SEARCH Functions
  • FIND(find_text, within_text, [start_num]): Returns the starting position of a substring within a text string.
  • SEARCH(find_text, within_text, [start_num]): Returns the starting position of a substring within a text string (case-insensitive).
  • LEFT(text, [num_chars]): Returns the leftmost characters from a text string.
  • MID(text, start_num, num_chars): Returns a specific number of characters from a text string, starting at a specific position.

Answer: A) =SEARCH(" ", A2) and B) =FIND(" ", A2)

Explanation:

  • The FIND function returns the position of the space character in “Leslie Fields”, which is at position 7.
  • The SEARCH function also returns the position of the space character in “Leslie Fields”, which is at position 7.

1.4.20 Substring Extraction Using Spaces

You want to extract the middle name “Banks” from the patient name in cell A5 from Table 1.2, where the name is “Doyle Banks Junior”. Which of the following formulas would achieve this? (Note: Multiple answers may be correct.)

  • =MID(A5, 7, 5)
  • =MID(A5, FIND(" ", A5) + 1, FIND(" ", A5, FIND(" ", A5) + 1) - FIND(" ", A5) - 1)
  • =MID(A5, 7, 13)
  • All of the above

Answers:

    1. =MID(A5, 7, 5)
    • This formula directly extracts the middle name “Banks” starting from the 7th character and with a length of 5 characters.
    1. =MID(A5, FIND(" ", A5) + 1, FIND(" ", A5, FIND(" ", A5) + 1) - FIND(" ", A5) - 1)
    • This formula uses the FIND function to locate the spaces and extract “Banks” based on the positions of the spaces.
      • The first FIND locates the first space, which is at position 6. Therefore, FIND(" ", A5) + 1 gives the position of the character after the first space (B), which is 7.
      • The second FIND locates the second space.
        • FIND(" ", A5) + 1 finds the position after the first space witch is FIND’s start_num parameter. This is equal to 6.
        • FIND(" ", A5, FIND(" ", A5) + 1) = FIND(" ", A5, 6) finds the position of the second space, starting from the position after the first space. Tis is equal to 12.
        • FIND(" ", A5) is the position of the first space, which is 6.
        • The difference between the positions of the second and first spaces minus 1 gives the length of the middle name. So, 12 - 6 - 1 = 5.

1.4.21 Length of First Name

You want to calculate the total number of characters in the patient’s first name in cell A2 (“Leslie Fields”) from Table 1.2. Which formulas will give the correct result? (Note: Multiple answers may be correct.)

  • =LEN(LEFT(A2, FIND(" ", A2) - 1))
  • =LEN(LEFT(A2, FIND(" ", A2)))
  • =LEN(MID(A2, 1, FIND(" ", A2) - 1))
  • =LEN(A2) - LEN(A2) + FIND(" ", A2)

Answers:

    1. =LEN(LEFT(A2, FIND(" ", A2) - 1))
    • This formula correctly uses the LEFT function to extract the first name from the text in cell A2 by finding the position of the first space using FIND(" ", A2). The -1 ensures the space itself is excluded, and LEN calculates the length of the extracted first name.
    1. =LEN(LEFT(A2, FIND(" ", A2)))
    • This formula is incorrect because it includes the space after the first name in the result. The FIND function locates the space, but the LEFT function does not subtract the space, so the length includes the space as well.
    1. =LEN(MID(A2, 1, FIND(" ", A2) - 1))
    • This formula works correctly. It uses MID to extract the first name starting from the 1st character (1 as the start position) and up to the first space (FIND(" ", A2) - 1), excluding the space. LEN then calculates the length of the extracted first name.
    1. =LEN(A2) - LEN(A2) + FIND(" ", A2)
    • This formula is incorrect because it simplifies to just FIND(" ", A2), which returns the position of the first space (which is 7 in this case for “Leslie Fields”). This does not represent the length of the first name; it only gives the position of the space.

1.4.22 Adding Mr. or Miss based on Gender

In sheet patient_data, you have patient names in column A and their genders in column B. You want to add “Mr.” or “Miss” before each patient’s name in column A, depending on their gender (column B). What formula should you use in cell C2?

  • =IF(B2="male", "Mr. " & A2, "Miss " & A2)
  • =IF(B2="female", "Mr. " & A2, "Miss " & A2)
  • =IF(B2="male", "Miss " & A2, "Mr. " & A2)
  • =IF(B2="female", "Miss " & A2, "Mr. " & A2)

Answer: A) =IF(B2="male", "Mr. " & A2, "Miss " & A2)

Explanation: The IF function checks if the value in B2 is “male”. If it is, it adds “Mr.” before the patient name in A2. Otherwise, it adds “Miss”.

1.4.23 Classifying Grades

You have a list of student grades in column A, where A2 contains the value 85. You want to classify grades into letter grades as follows:

  • A: 90 and above
  • B: 80 to 89
  • C: 70 to 79
  • D: Below 70

Which formula would you use in cell B2 to assign the correct letter grade based on the numeric grade in A2?

  • =IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "D")))
  • =IF(A2>=90, "A", IF(A2>=80, "C", IF(A2>=70, "B", "D")))
  • =IF(A2<=90, "A", IF(A2<=80, "B", IF(A2<=70, "C", "D")))
  • =IF(A2=90, "A", IF(A2=80, "B", IF(A2=70, "C", "D")))

Answer: A) =IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "D")))

Explanation: The formula uses nested IF functions to classify the grade in A2. - If A2 is 90 or above, it assigns an “A”. - If A2 is between 80 and 89, it assigns a “B”. - If A2 is between 70 and 79, it assigns a “C”. - If A2 is below 70, it assigns a “D”.

1.4.24 User Input with Case Insensitivity and Extra Spaces

You have a list of patient names in sheet patient_data presented in Table 1.2, and the user will input a patient name in a specific cell in Sheet3 (see Table 1.3). You want to perform a case-insensitive search based on the user’s input and return the exact match from patient_data, regardless of whether the input is in upper case, lower case, mixed case, or has extra spaces.

Table 1.3: Sheet3 sheet
A B
1 User Input Result
2

The user will type a patient name (e.g., “leslie fields”, “Leslie Fields”, or “LESLIE FIELDS”) in cell A2 on Sheet3. The formula in cell B2 should return the correct, exact case-sensitive match from patient_data column A, while ignoring any extra spaces and case differences.

For example: If the user types ” leslie fields ” or “LESLIE FIELDS” into A2, the result in B2 should return “Leslie Fields” from patient_data.

Which formula would you use in cell B2 on Sheet3 to achieve this?

  • =XLOOKUP(A2, patient_data!A2:A5, patient_data!A2:A5, "Not Found", 0)
  • =XLOOKUP(LOWER(A2), LOWER(patient_data!A2:A5), patient_data!A2:A5, "Not Found", 0)
  • =XLOOKUP(TRIM(LOWER(A2)), TRIM(LOWER(patient_data!A2:A5)), patient_data!A2:A5, "Not Found", 0)
  • =XLOOKUP(TRIM(A2), patient_data!A2:A5, LOWER(patient_data!A2:A5), "Not Found", 0)
Functions Used
  • XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode]): Searches a range or an array, and returns an item corresponding to the first match found.
    • lookup_value: The value to search for.
    • lookup_array: The range to search for the value.
    • return_array: The range to return the value from.
    • if_not_found: The value to return if no match is found.
    • match_mode: The type of match to perform (0 for exact match).
  • TRIM(text): Removes extra spaces from text.
    • text: The text from which to remove spaces.
  • LOWER(text): Converts text to lowercase.
    • text: The text to convert to lowercase.
  • UPPER(text): Converts text to uppercase.
    • text: The text to convert to uppercase.

Answer: C) =XLOOKUP(TRIM(LOWER(A2)), TRIM(LOWER(patient_data!A2:A5)), patient_data!A2:A5, "Not Found", 0)

Explanation:

  • The TRIM function is applied to both the user’s input (A2) and the patient names in patient_data to remove any extra spaces.
  • The LOWER function is applied to both to make the search case-insensitive. This ensures that no matter how the user enters the name (with extra spaces or different cases), the function will find the correct match in patient_data and return the exact case-sensitive match.

1.4.25 Splitting Full Name into First Name and Surname

You have a list of patient names in patient_data sheet presented in Table 1.2, column A, where each name consists of a first name and a surname separated by a space. You want to extract the first name into one column and the surname(s) into another.

For example: If A2 contains “Leslie Fields”, the formulas should return:

  • First Name in C2: Leslie
  • Surname(s) in D2: Fields

Which combination of formulas should you use in C2 and D2 to extract the first name and surname from A2?

  • =LEFT(A2, FIND(" ", A2) - 1) for First Name and =RIGHT(A2, LEN(A2) - FIND(" ", A2)) for Surname
  • =RIGHT(A2, FIND(" ", A2) - 1) for First Name and =LEFT(A2, LEN(A2) - FIND(" ", A2)) for Surname
  • =LEFT(A2, LEN(A2) - FIND(" ", A2)) for First Name and =RIGHT(A2, FIND(" ", A2) - 1) for Surname
  • =MID(A2, 1, FIND(" ", A2)) for First Name and =LEFT(A2, FIND(" ", A2)) for Surname
Functions Used
  • LEFT(text, num_chars): Returns the first characters in a text string.
    • text: The text string from which to extract characters.
    • num_chars: The number of characters to extract from the left of the text.
  • RIGHT(text, num_chars): Returns the last characters in a text string.
    • text: The text string from which to extract characters.
    • num_chars: The number of characters to extract from the right of the text.
  • FIND(find_text, within_text): Returns the starting position of one text string within another.
    • find_text: The text you want to find.
    • within_text: The text in which you want to search for find_text.
  • LEN(text): Returns the number of characters in a text string.
    • text: The text string for which you want to calculate the length.
  • MID(text, start_num, num_chars): Returns a specific number of characters from a text string, starting at the position you specify.
    • text: The text string containing the characters you want to extract.
    • start_num: The position of the first character you want to extract.
    • num_chars: The number of characters you want to extract.

Answer: A)

Explanation:

  • First Name (C2): =LEFT(A2, FIND(" ", A2) - 1)
    • This formula extracts the first name by taking the characters from the left up to the first space.
  • Surname (D2): =RIGHT(A2, LEN(A2) - FIND(" ", A2))
    • This formula extracts the surname by taking the characters from the right, starting just after the first space.

1.4.26 Using Constants in Formulas

You are calculating the total cost of products based on their price and the number of units sold. The prices of the products are listed in column A, the number of units sold is listed in column B, and you want to multiply the price by the units sold in column C. You also want to apply a fixed discount of 10% from cell D1 to all the totals. Your task is to write a formula that you can drag down to calculate the total cost for each product after the discount is applied.

Data of products and units sold
A B C D
1 Price Units Sold Total Cost Discount
2 10.00 5 10%
3 15.00 3
4 12.00 4
5 8.00 6

You need to calculate the total cost for each row using the formula:

\[ \text{Total Cost} = (\text{Price} \times \text{Units Sold}) \times (1 - \text{Discount}) \]

Which formula would you use in C2 that you can drag down to calculate the total cost for each product, applying the fixed discount from D1?

  • =A2 * B2 * (1 - D1)
  • =A2 * B2 * (1 - $D$1)
  • =A$2 * B$2 * (1 - D$1)
  • =$A2 * $B2 * (1 - D$1)

Answer: B) =A2 * B2 * (1 - $D$1)

Explanation:

  • The A2 * B2 multiplies the price by the number of units sold.
  • (1 - $D$1) applies the discount, where $D$1 is an absolute reference to ensure that the discount (10%) remains fixed for all rows as you drag the formula down.
  • Using the absolute reference $D$1 ensures the discount stays constant for all calculations, while A2 and B2 are relative references that change as the formula is dragged down for each row.

1.4.27 Using AutoFill with a Gap in the Data

You have the following data in column A, where there is a gap (empty cell) in row 5. You want to auto-fill a sequence of numbers starting from 1 in cell B2. You enter 1 in cell B2 and 2 in cell B3. You then select both cells and double-click the fill handle to auto-fill down the column.

Initial Data
A B
1 Data Sequence
2 Alice Johnson 1
3 Bob Smith 2
4 Charlie Rogers
5
6 Diana Stone

What values will be filled in cells B4 to B6 when you double-click the fill handle?

  1. 1, 1, 1 (cells B2 to B6 will be filled with 1)
  2. 3, 4, 5 (the sequence continues down to B6)
  3. 1, 2, 1, 2 (cells B2 to B6 alternate between 1 and 2)
  4. 3 (only B4 is filled and cells B5 to B6 remain blank)
AutoFill in Excel

AutoFill: A feature in Excel that allows you to quickly fill cells with a series of values or data based on the detected pattern.

When you point to the fill handle (a small square at the bottom-right corner of a cell), the cursor changes to a black cross. Double-clicking the fill handle will auto-fill the cells based on the pattern detected.

Solution: 4) 3 (only B4 is filled and cells B5 to B6 remain blank)

Explanation: Excel’s AutoFill feature fills cells based on the detected pattern. Since there’s data in A2:A4, the sequence will continue from B2 (1), B3 (2), to B4 (3). However, the fill stops at row 4 because row 5 in column A is blank, and Excel recognizes the end of the data.