Preparing Data for Analysis with Microsoft Excel Quiz Answers – Practice & Graded Quizzes

Welcome to the ultimate guide for Preparing Data for Analysis with Microsoft Excel quiz answers! Whether you’re brushing up on your skills with practice quizzes or completing graded quizzes, this guide has you covered. Covering all modules, this post will help you understand Excel’s powerful tools for preparing data efficiently and accurately for analysis.

Preparing Data for Analysis with Microsoft Excel Quiz Answers – Practice & Graded Quizzes for All Modules

Preparing Data for Analysis with Microsoft Excel Module 01 Quiz Answers

General Knowledge Quiz Answers

Q1. You are the manager of a graphic design team that has been tasked with creating a series of promotional materials for a new product launch. Given the tight deadline, you decide to use generative AI to expedite the design process.

Correct Answer Use a generative adversarial network (GAN) to create a variety of realistic images based on textual descriptions provided by the marketing team.

Explanation Generative adversarial networks (GANs) are highly effective for generating realistic images based on textual descriptions. By leveraging GANs, your team can efficiently produce creative visuals aligned with the marketing team’s vision, saving time and meeting the tight deadline for the product launch.

---

Q2. As the head of a logistics company facing frequent supply chain disruptions due to unpredictable demand fluctuations, you decide to incorporate generative AI to improve efficiency.

Correct Answer Use generative AI to forecast demand trends and simulate various supply chain scenarios for better planning.

Explanation Generative AI excels in analyzing large datasets and creating simulations. By forecasting demand trends and simulating supply chain scenarios, you can anticipate disruptions and devise strategies to optimize efficiency, ensuring smoother operations and resource management.

---

Q3. True or False: Generative adversarial networks (GANs) involve two neural networks, the generator and the discriminator, which work together in an adversarial process to create highly realistic outputs.

Correct Answer True

Explanation GANs consist of two neural networks: the generator creates outputs (e.g., images or data), while the discriminator evaluates them against real data. This adversarial interaction pushes the generator to produce outputs that are increasingly realistic, a hallmark of GAN functionality.

---

Q4. Generative AI models like GPT and BERT use a mechanism called __________ to understand and generate contextually relevant text.

Correct Answer Self-attention

Explanation Self-attention is a core mechanism in models like GPT and BERT. It allows the models to focus on relevant parts of the input sequence, ensuring that each word is understood in the appropriate context. This results in the generation of coherent and contextually accurate text.

---

Q5. As the HR manager of a growing tech company, you are implementing generative AI to streamline the recruitment process.

Correct Answer Implementing rigorous testing and auditing of the AI system to identify and correct biases in the resume screening process.

Explanation Ethical concerns in AI hiring processes often stem from biases in data or algorithms. Rigorous testing and auditing of the AI system ensure fairness and transparency by identifying and mitigating these biases, fostering a more equitable recruitment process.

Knowledge check: Creating workbook content Quiz Answers

Q1. You type some non-scientific numerical data directly into a Microsoft Excel worksheet. You then press Enter, and 1.9E+09 appears in the cell. Why has this text appeared in the cell?

Correct Answer The column is too narrow.

Explanation When a column is too narrow to display a large number, Excel automatically converts the number into scientific notation (e.g., 1.9E+09) to fit it into the cell. Expanding the column width will display the full number.

---

Q2. Which number format option is both a format and an action button in Excel?

Correct Answer The Percentage format

Explanation The Percentage format in Excel is both a formatting style and an action. When applied, it automatically converts a value (e.g., 0.25) into a percentage (25%) by multiplying the number by 100 and adding a percent symbol.

---

Q3. You are editing a worksheet in which column G is hidden and is not visible between columns F and H. Which set of steps can you take to unhide column G and check its contents?

Correct Answer Highlight columns F and H. Select Format on the Home tab. Choose Hide & Unhide from the drop-down menu and then unhide columns from the sub-menu.

Explanation To unhide a hidden column, you need to highlight the columns adjacent to it (F and H in this case). Then, under the Format option on the Home tab, select Hide & Unhide and choose the Unhide Columns option to reveal column G.

---

Q4. You type the heading “Customer Name” into a cell. Part of the heading is hidden because of the column width and the fact that there is other content in the cell directly to the right. You would like the full heading to be visible, with the word “Name” appearing under the word “Customer.” How can you do this?

Correct Answer Use the Wrap Text command in the Alignment group.

Explanation The Wrap Text command in Excel ensures that text within a cell is displayed on multiple lines, adjusting the cell’s height automatically. Using this feature will make “Customer” appear on one line and “Name” appear on the next, keeping the content visible within the same cell.

---

Q5. You need to sort or reorder a large block of information in a worksheet. Which tab do you need to select to access the Sort feature?

Correct Answer The Data tab

Explanation The Sort feature is located on the Data tab in Excel. It provides options for organizing information alphabetically, numerically, or based on custom criteria, making it easy to manage large datasets effectively.

Knowledge check: Working with blocks of data in Excel Quiz Answers

Q1. You want to move quickly to cell S1215 in your worksheet. To achieve this, you type the cell reference into a particular area of the screen and then press Enter. What is the area of the screen called in which you type the cell reference?

Correct Answer The Name Box

Explanation The Name Box in Excel, located near the top-left corner of the worksheet, displays the current cell reference. You can also type a specific cell reference (e.g., S1215) into the Name Box and press Enter to quickly navigate to that cell.

---

Q2. You open an Excel worksheet that contains ten columns of data. The information is filtered, so some of the available data is not visible. How can you identify which heading the data is filtered by?

Correct Answer The Filter arrow contains a funnel symbol.

Explanation When a column is filtered in Excel, the Filter arrow in the column header changes to a funnel symbol, indicating that a filter has been applied to that column.

---

Q3. Column A in your worksheet contains component names. All twenty-two names have been input as text except for one entry called 11ratchet, which begins with a number. If you were to sort this column in descending order, where would the entry 11ratchet appear?

Correct Answer At the top of the column.

Explanation In Excel, sorting treats numbers as higher precedence than text when sorting in descending order. Since “11ratchet” starts with a number, it will appear at the top of the column above all text entries.

---

Q4. Row one in your worksheet contains headings and it is currently visible at the top of the screen. The cursor is on cell B3. Which of the Freeze Panes options must you choose to ensure that the headings remain visible onscreen?

Correct Answer Freeze Top Row

Explanation The Freeze Top Row option ensures that the first row remains visible as you scroll down the worksheet. This is particularly useful for keeping headings in view when working with large datasets.

---

Q5. True or False: You need to edit only those rows in a spreadsheet that have been shaded yellow. You can use the Filter by color filter option to hide rows that haven’t been formatted in this way.

Correct Answer True

Explanation Excel’s Filter by color feature allows you to filter rows based on cell color, font color, or conditional formatting. By applying this filter, you can display only the yellow-shaded rows and hide the others, making it easier to focus on the rows you need to edit.

Preparing Data for Analysis with Microsoft Excel Module 02 Quiz Answers

Knowledge check: Formulas in Excel Quiz Answers

Q1. You are creating a formula in cell C2 in your worksheet. The calculation only needs to appear in cell C2. You have typed the following formula:
A3*D15/45*F17
However, there is an error in the syntax of this formula. What part of the formula is missing?

Correct Answer An Equals Sign

Explanation Every formula in Excel must begin with an equals sign (=) to indicate that the cell contains a formula. Without it, Excel will treat the text as plain data rather than performing the calculation.

---

Q2. Cell D2 of your worksheet contains the weekly sales total. Cell D7 contains the overall monthly sales. You need cell E2 to show what percentage the week one total is of the overall monthly total. You’ve already applied the percentage format to E2. Which of the following options is the correct syntax for the formula?

Correct Answer =D2/D7

Explanation To calculate the percentage, divide the weekly total (D2) by the monthly total (D7). Since the percentage format is already applied to cell E2, Excel will automatically display the result as a percentage without requiring additional multiplication by 100.

---

Q3. True or False: When Excel processes the following formula, it will first multiply cell A1 by cell C3 before it processes the addition.
=(B15+D45)-(A1*C3)

Correct Answer True

Explanation Excel follows the standard order of operations (PEMDAS: Parentheses, Exponents, Multiplication/Division, Addition/Subtraction). Within the formula, Excel calculates the product of A1*C3 first before handling the addition and subtraction.

---

Q4. You have created the following formula in your worksheet:
=D4+$C$6-E4+F6
You use the Autofill feature to copy the formula down the column. Which of the cell references will update with new row numbers?

Correct Answer D4, E4, F6

Explanation Relative cell references (e.g., D4, E4, and F6) adjust automatically when the formula is copied to a new row. Absolute references (e.g., $C$6) remain fixed and do not change.

---

Q5. What mathematical sign does Excel process last when calculating the results of the following formula?
=D4+C3/N5*B3

Correct Answer The plus sign.

Explanation Excel processes operations in the order of operations. In this formula:

1. Division (C3/N5) is processed first.
2. Multiplication (/B3) follows.
3. Addition (+D4) is processed last. The plus sign is thus the final operation in this calculation.

Knowledge check: Getting started with functions Quiz Answers

Q1. Which of the following items are part of a function formula?

Correct Answer An equals sign, Parentheses, The function name

Explanation A function formula in Excel always begins with an equals sign (=) to signify it is a formula. It includes a function name (e.g., SUM, AVERAGE) followed by parentheses, which enclose the arguments or inputs to the function. Periods are not part of function syntax.

---

Q2. You need to know how many cells in a cell range contain entries. The cells with entries contain a mixture of text and numeric entries. Which function should you use in this situation?

Correct Answer COUNTA

Explanation The COUNTA function counts all non-empty cells in a range, including cells with text, numbers, or other data types. The COUNT function only counts numeric entries, while COUNTBLANK counts empty cells.

---

Q3. Three cells in your worksheet contain numbers. Cell D7 holds the number 10, D8 contains the number 20, and D9 contains the number 30. What is the result of the following formula when added to cell D10?
=MAX(D7:D8)

Correct Answer 20

Explanation The MAX function returns the largest number in the specified range. Since the formula references D7:D8, it only evaluates the numbers in cells D7 (10) and D8 (20), and the maximum value is 20.

---

Q4. Cells B1 to B3 in your worksheet contain numbers. Cell B4 has a piece of text. You add an AVERAGE function formula to cell B5, which works out the average of cells B1 to B4. How will Excel work out the average?

Correct Answer Total the numbers in B1 to B3 and divide by 3.

Explanation The AVERAGE function ignores text cells and only calculates the average of numeric values. In this case, it sums the numbers in B1 to B3 and divides by the count of numeric entries, which is 3.

---

Q5. Cell E4 in your worksheet contains the value 300, cell E5 the value 300, and cell E6 the value 0. What is the correct answer to the following formula when added to your worksheet?
=AVERAGE(E4:E6)

Correct Answer 200

Explanation The AVERAGE function sums the values in the range E4 to E6 (300 + 300 + 0 = 600) and divides by the number of values (3), resulting in 600 ÷ 3 = 200.

Preparing Data for Analysis with Microsoft Excel Module 03 Quiz Answers

Knowledge check: Using functions to clean or standardize text Quiz Answers

Q1. True or False: An Excel spreadsheet contains country names split over multiple columns. Cell A2 contains the word “United.” Cell B2 contains the word “States.” When executed, the following formula generates the result “United States”:
=CONCAT(A2," ",B2)

Correct Answer True

Explanation The CONCAT function combines text from multiple cells or strings into one. In this case, it concatenates the contents of A2 (“United”), a space (" "), and B2 (“States”), resulting in “United States.”

---

Q2. One of the employees at Adventure Works has made some typing errors in a spreadsheet. A text entry in a cell contains the following text:
aDVENTURE wORKS rESELLERS
Which function should you use in a formula to copy this entry so that it is in lowercase with a capital letter at the beginning of each word?

Correct Answer PROPER

Explanation The PROPER function capitalizes the first letter of each word and converts the rest of the letters to lowercase. Using PROPER("aDVENTURE wORKS rESELLERS") will result in “Adventure Works Resellers.”

---

Q3. In the Adventure Works Reseller spreadsheet, the reseller names are listed in column D. Cell D2 contains the following text, which is left-aligned in the cell with no redundant spaces:
EastBike Shop
What is the result of this formula?
=MID(D2,5,4)

Correct Answer Bike

Explanation The MID function extracts a substring from a text string starting at a specified position and for a specified number of characters. In this case, starting from the 5th character of D2 (“EastBike Shop”) and taking 4 characters gives “Bike.”

---

Q4. Cell A2 contains the following entry:
aceE6548.
What result would the following formula generate when applied to the above entry?
=UPPER(A2)

Correct Answer ACEE6548.

Explanation The UPPER function converts all letters in the text string to uppercase. Applying it to “aceE6548.” results in “ACEE6548.” The numbers and punctuation remain unchanged.

---

Q5. Cell E4 contains the city name “North Miami Beach.” What result would the following formula generate in your worksheet?
=RIGHT(E4,5)

Correct Answer Beach

Explanation The RIGHT function extracts the last N characters from a text string. Here, taking the last 5 characters of “North Miami Beach” results in “Beach.”

Knowledge check: Date and time functions Quiz Answers

Q1. You have created a formula in your spreadsheet using the TODAY function. What must you include after the word TODAY in your formula?

Correct Answer An opening and closing parenthesis only.

Explanation The TODAY function in Excel does not take any arguments, but parentheses are still required for the function to execute. Using =TODAY() will return the current date.

---

Q2. You have created a formula in your spreadsheet using the NOW function. By default, what will the formula display in its result?

Correct Answer The date and time.

Explanation The NOW function returns the current date and time as a result. It combines the output of the TODAY function (date) with the current system time.

---

Q3. You are working on a spreadsheet that contains three columns called Day, Month, and Year. In another column, you would like to combine these entries so that it shows a complete date. Which function should you use to achieve this?

Correct Answer DATE

Explanation The DATE function creates a valid date by combining separate values for year, month, and day. For example, =DATE(Year, Month, Day) will generate a proper date format like “1/1/2023.” The CONCAT function is for text concatenation, and DATEDIF calculates the difference between two dates.

---

Q4. True or False: You can use the NETWORKDAYS.INTL function to calculate the number of working days between two dates while excluding national holidays and weekends because it has built-in knowledge of public holidays.

Correct Answer False

Explanation The NETWORKDAYS.INTL function calculates the number of working days between two dates, allowing you to customize which days of the week are considered weekends. However, you must explicitly provide a range of dates for holidays. The function does not have built-in knowledge of public holidays.

---

Q5. You have entered the following three dates in your spreadsheet in the month, day, and year format. Which of these dates has the largest serial number?

Correct Answer 09/10/2025

Explanation Excel stores dates as serial numbers, with each date representing a unique number that increases sequentially over time. The later the date, the larger the serial number. Among the given dates:

• 09/10/2025 is the latest, so it has the largest serial number.
• 04/15/2020 and 01/30/2023 have smaller serial numbers because they are earlier dates.

Knowledge check: Logical functions Quiz Answers

Q1. Cell A2 of your worksheet contains a value of 250. What is the result of the following formula when added to your worksheet?
=IF(A2>300,10%,IF(A2>200,5%,0%))

Correct Answer 5%

Explanation The formula uses nested IF functions. First, it checks if A2 is greater than 300, which is false. Then, it checks if A2 is greater than 200, which is true. Therefore, the result is 5%.

---

Q2. You create a formula using the IFS function to test for a series of alphabet characters. When typing the criteria to test for, what symbols should you add around each text character?

Correct Answer Double quotation marks.

Explanation In Excel formulas, text values must be enclosed in double quotation marks. For example, "Chicago" would be the correct way to reference text criteria in an IFS function.

---

Q3. In your worksheet, cell A2 contains a value of 100. Cell B2 contains a value of 200, and C2 contains a value of 400. What is the result of the following formula when added to your worksheet?
=IF(OR(A2>=200,B2>=200),"Result 1",IF(C2>300,"Result 2","Result 3"))

Correct Answer Result 1

Explanation The formula uses the OR function to check if either A2 is greater than or equal to 200, or B2 is greater than or equal to 200. Since B2 is 200, the OR condition is true, and the formula returns “Result 1.”

---

Q4. You create the following formula using the AVERAGEIF function:
=AVERAGEIF(A2:A50,"Chicago”,C2:C50)
What does the first argument of this function represent?

Correct Answer The criteria range.

Explanation In the AVERAGEIF function, the first argument is the range of cells to evaluate against the given criteria. In this case, A2:A50 is the range of cells where the function will look for the criteria (in this case, “Chicago”).

---

Q5. In your worksheet, cell A2 contains the value 100. Cell B2 contains a value of 200, and C2 contains a value of 400. What is the result of the following formula when added to your worksheet?
=IFS(A2>200,"Rate 1",B2>200,"Rate 2",C2>200,"Rate 3",TRUE,0)

Correct Answer Rate 2

Explanation The IFS function evaluates conditions in order. Since A2 is not greater than 200, it checks B2, which is 200 (and greater than 200), so it returns “Rate 2.”

Frequently Asked Questions (FAQ)

Conclusion

We hope this guide to Preparing Data for Analysis with Microsoft Excel quiz answers helps you succeed in mastering data preparation techniques in Excel. Bookmark this page for easy reference and share it with your fellow learners. Ready to sharpen your Excel skills and ace your quizzes? Let’s dive in!

Resource: Preparing Data for Analysis with Microsoft Excel

Get All Course Quiz Answers of Microsoft Power BI Data Analyst Professional Certificate >>

Preparing Data for Analysis with Microsoft Excel Quiz Answers

Harnessing the Power of Data with Power BI Quiz Answers

Extract, Transform and Load Data in Power BI Quiz Answers