4 Final project and assessment: Preparing data for analysis with Microsoft Excel
4.1 Self-Review: Creating an Executive Data Summary
Question 1
You need to freeze the screen so that the summary data in columns A to F and the headings in row 1 remain visible while scrolling.
In the Freeze Panes drop-down menu on the View tab, which option should you select?
⬜ Freeze First Column
✅ Freeze Panes
⬜ Freeze Top Row
Question 2
You created the following formula in cell B12 to calculate the total sales for January 2022:
=SUMIF(K2:K103,1,R2:R103)
Before copying this formula down to cells B13 and B14, which references need to have dollar signs ($) added to ensure the correct ranges remain unchanged?
✅ K2:K103 and R2:R103
⬜ K2:K103 only
⬜ R2:R103 only
Question 3
In cell D6, you created a formula to calculate the percentage difference between the 2022 quarter-one sales and the 2023 quarter-one sales.
Which of the following best describes the correct logic for this calculation?
✅ (2023 total − 2022 total) / 2022 total
⬜ (2023 total − 2022 total) / 2023 total
⬜ (2022 total − 2023 total) / 2022 total
Question 4
In cell C12, you used the SUMIF function to calculate the total sales for January 2023, then copied the formula down to cells C13 and C14.
After completing the exercise, what result appeared in cell C14?
⬜ $145,535
✅ $164,740
⬜ $143,555
Question 5
You added headings in rows 4 and 10 that needed to be centered across the related tables using the Merge & Center option on the Home tab.
Before clicking Merge & Center, you first typed the heading and then selected the cell range where it should be centered.
Where should you type the heading before selecting Merge & Center?
✅ On the left edge of the cell range the heading is to be centered in.
⬜ In the middle of the cell range the heading is to be centered in.
⬜ On the right edge of the cell range the heading is to be centered in.
4.2 Course Quiz: Preparing Data for Analysis with Microsoft Excel
Question 1
You are entering sales data into a column in your Microsoft Excel worksheet. After typing a numeric value and pressing Enter, the following appears in the cell:
######
What is the reason for this display?
- ⬜ The cell contains an extra text character.
- ⬜ The cell is formatted as text.
- ✅ The column is too narrow.
Question 2
A colleague has shown you how to create a chart from some inventory data that you have in a worksheet. They’ve indicated that you can customize it using colors and titles. Where would you find commands relating to the chart?
- ⬜ On the Review tab.
- ✅ On Contextual tabs.
- ⬜ On the Design tab.
Question 3
You are editing a spreadsheet that already has information in it. You type a long text heading into cell A1 and because cell B1 already contains an entry you can’t see all of the text you just typed. Which choice would you select so that some of the words will appear on a second line and the whole heading will be visible?
- ✅ Wrap text
- ⬜ Merge and center
- ⬜ Orientation
Question 4
You’re creating a spreadsheet that will be just for your own use and you type a text heading in cell A1. You decide to make it look different so that it stands out more. What formats can you apply by using choices in the Font section of the Home ribbon? Select all that apply.
- ⬜ Merge and center the text.
- ✅ Change the font color.
- ✅ Change the Font choice.
- ⬜ Wrap text.
- ✅ Increase the Font size.
Question 5
You open a spreadsheet that a colleague has shared with you and you notice that the column initial letter sequence is A, B, D. Why is the column initial letter C not visible?
- ✅ The column is hidden.
- ⬜ The column has been repositioned.
- ⬜ The column has been deleted.
Question 6
You want to update your worksheet’s name. What is the correct sequence of steps to rename the spreadsheet you are working on?
- ✅ Double-click the worksheet Tab. Type the new name.
- ⬜ Single-click the worksheet Tab. Type the new name.
- ⬜ Triple-click the worksheet Tab. Type the new name.
Question 7
True or False: You can use the Undo feature to reverse deleting a worksheet as long as you haven’t saved the workbook.
- ⬜ True
- ✅ False
Question 8
You are compiling a sales report in an Excel worksheet. Cell C4 contains an important sales total. A colleague has explained to you that names can be assigned to cells to make them easier to locate and you decide to try this out. When you type the name June 2023 Sales Total in the Name box, Excel displays a warning message which says:
“You must enter a valid reference you want to go to, or type a valid name for this selection.”
Why has Excel rejected this name?
- ⬜ It contains numbers.
- ✅ It contains spaces.
- ⬜ It is too long.
Question 9
You are working on a worksheet that contains information on stock levels. You need to identify all rows in the data that have a figure of 1000 in the Stock Level column. Which of the following options can you use to complete this task?
- ✅ Use Equals on the Number filter sub-menu.
- ⬜ Use Greater than on Number filter sub-menu.
- ⬜ Use Does not equal on the Number filter sub-menu.
Question 10
You’ve been asked to update a spreadsheet that already contains a lot of information, so you are adding entries beyond row 100 in the spreadsheet. Row one and column A in your worksheet both contain headings. You do not want to keep scrolling up or to the left to see the headings.
The cursor is on cell B2. Which of the following options must you choose to ensure that both sets of headings remain constantly visible onscreen?
- ✅ Freeze panes
- ⬜ Freeze top row
- ⬜ Freeze first column
Question 11
What symbol comes at the beginning of every formula?
- ✅ =
- ⬜ #
- ⬜ $
Question 12
You have the following formula in your worksheet. According to the Order of Precedence, which mathematical operator would Excel process first?
=30+10/2*3
- ⬜ +
- ✅ /
- ⬜ *
Question 13
Your worksheet contains the following formula:
=((100-20)+(20/2))/3
What would the result be?
- ✅ 30
- ⬜ 83
- ⬜ 103
Question 14
Cells J2, K2 and L2 in your worksheet all contain the number 5. In cell M2 there is a formula that reads:
=J2*K2/$L$2
On the next row, cells J3, K3 and L3 all contain the number 10. If you copy the formula from cell M2 to cell M3 what will the result be?
- ✅ 20
- ⬜ 10
- ⬜ 5
Question 15
Cell A2 of your spreadsheet contains the value 500. Cell B2 contains the value 150 and cell C2 contains the entry 10%. You have entered the following formula in cell D2 which still has the general format applied:
=(A2-B2)*C2
What would the result be?
- ⬜ 350%
- ✅ 35
- ⬜ 35%
Question 16
You are creating an end of month sales report. Cell D2 displays the value $150,000 which is the total sales achieved by your team in this month. In cell D3 you create the following formula to set a new sales target for the following month:
=D2*110%
What is the result?
- ✅ $165,000
- ⬜ $175,000
- ⬜ $185,000
Question 17
You are editing a results spreadsheet that people update in real time to show results. You want to create a formula that will show you how many cells have been updated and have content in them.
Cell E7 in your worksheet contains a value of 150. E8 contains the phrase “to be advised”. E9 is empty. What result would the following formula generate?
=COUNTBLANK(E7:E9)
- ⬜ 2
- ⬜ 3
- ✅ 1
Question 18
You need to add a SUM function formula to cell B2 in your worksheet to total the values in cell range A2 to A6. You decide to use the AutoSum Shortcut to create it. Where should you position the cursor before clicking on the AutoSum Shortcut button?
- ✅ On cell B2.
- ⬜ On cell A2.
- ⬜ On cell A6.
Question 19
You’ve been asked to create average calculations in a sales report.
In your worksheet, cell C1 and C2 contain the value 10. C3 is empty and there is a 0 in C4. C5 contains the value 20. What would the result of the following formula be?
=AVERAGE(C1:C5)
- ⬜ 13
- ⬜ 8
- ✅ 10
Question 20
You are creating a SUM formula in cell A20 of your worksheet. You position the cursor on cell A20 and click on the AutoSum Shortcut button which makes Excel generate a suggestion of what it should include in the total.
Cells A1 to A9 and A11 to A19 all contain numbers. Cell A10 is blank. What range of cells is the AutoSum feature likely to suggest that it includes as the arguments for the SUM function?
- ✅ A11:A19
- ⬜ A1:A9
- ⬜ A1:19
Question 21
True or False: Columns A, B and C of your worksheet contain numbers. The columns are called Day, Month and Year. The following formula will combine the entries into one numeric date.
=CONCAT(A2,B2,C2)
- ⬜ True
- ✅ False
Question 22
Column C in your worksheet contains stock codes. The entry in cell C2 reads 2563tt. If you created the following formula in D2 what result would Excel display?
=PROPER(C2)
- ⬜ 2563TT
- ⬜ 2563tt
- ✅ 2563Tt
Question 23
In one of the Adventure Works’ sheets, the full address for a supplier is incorrectly entered in one cell. Cell D6 contains the following text without any extra spaces:
1474MainAvenueFreemontCA
You are creating formulas to divide this content into separate columns. Which formula would extract only the city name?
- ⬜ =MID(D6,9,6)
- ⬜ =MID(D6,5,4)
- ✅ =MID(D6,15,8)
Question 24
Some information in an Adventure Works spreadsheet has been downloaded incorrectly from another source. In one column three pieces of information — a stock number, a supplier, and a product category — have been blended together. The entry in E7 reads:
245BSC Cylesaz Brakes System
You want the product category, Brakes System, to be in a separate column. Which function would be the best to use in a formula to achieve this?
- ✅ RIGHT
- ⬜ LEFT
- ⬜ MID
Question 25
You are creating a worksheet that your colleagues will use to log their work time. You need cell E4 to always show the current date. Which formula should you enter in E4 to have it always show the current calendar date?
- ⬜ =TODAY(E4)
- ⬜ =TODAY(DD/MM/YYYY)
- ✅ =TODAY()
Question 26
You are working on a spreadsheet that contains sales information for the last two years. Each sale is recorded on a separate row with the date of the sale recorded in column A. Which of these functions can you use to split the dates into component parts? Select all that apply:
- ✅ MONTH
- ⬜ DATEDIF
- ⬜ DATE
- ✅ YEAR
Question 27
You’re currently working on a spreadsheet that contains the timeline for the rollout of a new marketing campaign for Adventure Works. One of the cold-calling teams works a six-day week so they do not have a “standard” weekend. You need to calculate how many working days there will be for this team between the project start date and the deadline date. Which function should you use to do this?
- ⬜ DATEDIF
- ⬜ NETWORKDAYS
- ✅ NETWORKDAYS.INTL
Question 28
You are updating customer orders in a worksheet. The order dates are in column A in the format MM/DD/YYYY. The order values are in column B. Customers are given a discount based on a date and value criteria.
The order date for Contoso Bikes in A20 is 07/01/2023 and the order value in B20 is $10,500. Which discount rate would the following formula return for Contoso Bikes?
=IF(AND(A20<06/30/2023,B20>10000),10%,IF(B20>5000,5%,0%))
- ⬜ 10%
- ⬜ 0%
- ✅ 5%
Question 29
You are a sales manager for Adventure Works and you are currently reviewing the Quarter 1 results for the sales teams. The sales figures achieved by the teams are entered in column A.
The team for the western region achieved sales of $135,000. According to the following formula, what action are you going to take with this team?
=IFS(A22>150000,"Send congratulation message on MS Teams",A22=150000,"Send individual Emails",A22<150000,"Schedule meeting with the team",TRUE,0)
- ⬜ Send a congratulation message on MS Teams.
- ⬜ Send individual Emails.
- ✅ Schedule a meeting with the team.
Question 30
You are reviewing a spreadsheet that records a large number of sales listed by city. You intend to create a SUMIF formula that identifies all rows that have the city name Chicago in column A. Which characters do you need to add around the name Chicago when you are adding it as the criteria?
- ⬜ Parentheses
- ✅ Double quotation marks
- ⬜ Single quotation marks
Question 31
When you type an entry into a cell in an Excel worksheet, where does the entry appear?
- ⬜ In the worksheet grid only
- ⬜ In the Formula tab only
- ✅ In the worksheet and the Formula tab
Question 32
True or False: The maximum number of data rows that can be contained in an Excel workbook is one million.
- ✅ False
- ⬜ True
Question 33
You are updating a spreadsheet that displays Adventure Works’ international sales. The numbers in one column need to be identified as Japanese Yen (¥). When you click on the Currency format dropdown on the Home ribbon that currency symbol is not offered. What is the correct series of steps to apply this format?
- ⬜ Select the Number format dropdown. Choose Currency as a category and then the Yen symbol.
- ✅ Select the Number format dropdown. Choose More number formats. Choose Currency as a category and then the Yen (¥) symbol.
- ⬜ Select the Number format dropdown. Choose the Yen symbol from the list provided.
Question 34
When you type the number 1.5 in the A1 and apply the Percentage format, how will Excel display it?
- ⬜ 15%
- ⬜ 1.5%
- ✅ 150%
Question 35
Column C in the sales spreadsheet that you’re updating contains supplier names. As you’re adding a new supplier name you realize that you need to make column C in your worksheet wider as it is not displaying the supplier name correctly. Which of the following is a mouse shortcut to make the column as wide as it needs to be?
- ⬜ Double-click the vertical line between initials B and C.
- ✅ Double-click the vertical line between the initials C and D.
- ⬜ Double-click the initial letter C.
Question 36
You’ve been asked to update a customer details spreadsheet. You realize that the person who originally created the spreadsheet added a title of Other Information in D1 and used column D to record comments which are no longer relevant. The cursor is currently on cell D1. If you select Delete sheet columns from the Delete dropdown on the Home tab, which column will Excel delete?
- ⬜ Column E
- ✅ Column D
- ⬜ Column C
Question 37
True or False: You can use the Undo feature to reverse deleting a worksheet as long as you haven’t saved the workbook.
- ⬜ True
- ✅ False
Question 38
You are editing a sales report in an Excel worksheet. Other colleagues also work in the same file. Cell C4 contains an important sales total. You decide to assign a name to the cell and would first like to check what names are already in use in the workbook. Which of the following can you use to find and check the names in use? Select all that apply.
- ⬜ The Search box.
- ✅ The Name Box.
- ⬜ Custom views.
- ✅ The Name manager dialog.
Question 39
You are working on a worksheet that contains information on stock levels. You need to identify all rows in the data that have a figure of 1000 in the Stock Level column. Which of the following options can you use to complete this task?
- ⬜ Use Does not equal on the Number filter sub-menu.
- ⬜ Use Greater than on Number filter sub-menu.
- ✅ Use Equals on the Number filter sub-menu.
Question 40
Your spreadsheet contains a block of stock information. Column A contains the product name and column B contains the item color. There are 40 rows that have an entry of “Touring Bike” in column A. Of these, 10 rows have “black” listed as the color in column B. Fifteen other rows have other products where “black” is also the listed color column B. If you filter by the color “black” on column B and then the term “Touring Bike” on column A, how many records will Excel display?
- ✅ 10
- ⬜ 40
- ⬜ 25
Question 41
True or False: When Excel is processing a formula, it will always process the mathematical operators in the order in which it encounters them as it reads left to right.
- ✅ False
- ⬜ True
Question 42
You have the following formula in your worksheet. What would the result be?=30+10/2*3
- ⬜ 60
- ✅ 45
- ⬜ 32
Question 43
Which set of parentheses will Excel process first when it is calculating the formula result?=((A3-H4)+(F10+C4))*(G7/C8)
- ✅ (A3−H4)
- ⬜ (F10+C4)
- ⬜ (G7/C8)
Question 44
Cells J2, K2 and L2 in your worksheet all contain the number 5. In cell M2 there is a formula that reads: $J$2*$K$2/$L$2. On the next row, cells J3, K3 and L3 all contain the number 10. If you copy the formula from cell M2 to cell M3 what will the result be?
- ⬜ 10
- ✅ 20
- ⬜ 5
Question 45
You’ve been asked to update a monthly sales report and create some percentage calculations. Cell D2 of your worksheet contains the weekly sales total. Cell D7 contains the overall monthly sales. You want cell E2 to show what percentage the week one total is of the overall monthly total. E2 is still set to General format. What is the correct syntax for the formula?
- ⬜ =D7/D2
- ✅ **=D2/D7*100**
- ⬜ =D7/D2*100
- ⬜ =D2/D7
Question 46
You are creating an end of month sales report. Cell D2 displays the value $150,000 which is the total sales achieved by your team in this month. In cell D3 you create the following formula to set a new sales target for the following month. =D2*110% What is the result?
- ✅ $165,000
- ⬜ $185,000
- ⬜ $175,000
Question 47
You are editing a results spreadsheet that people update in real time to show results. You want to create a formula that will show you how many cells have been updated and have content in them. Cell E7 in your worksheet contains a value of 150. E8 contains the phrase “to be advised”. E9 is empty. What result would the following formula generate? =COUNT(E7:E9)
- ⬜ 3
- ✅ 1
- ⬜ 2
Question 48
Cell A2 in your worksheet contains the value 50. B2 contains the value 10 and C2 contains a zero. A formula in cell D2 reads: =MIN(A2:C2) What result is visible in D2?
- ⬜ 10
- ✅ 0
- ⬜ 50
Question 49
You’ve been asked to create average calculations in a sales report. In your worksheet, cell C1 and C2 contain the value 10. C3 is empty and there is a 0 in C4. C5 contains the value 20. What would the result of the following formula be? =AVERAGE(C1:C5)
- ⬜ 13
- ⬜ 8
- ✅ 10
Question 50
In your worksheet, cell E4 contains the value 100. Cell E5 and E6 are empty. Cell F4 has the value 200 and G4 the value 300. What result would the following formula generate? =SUM(E4:E6,F4,G4)
- ⬜ 100
- ⬜ 300
- ✅ 600
Question 51
Columns A, B and C of your worksheet contain numbers. The columns are called Day, Month and Year. You need Excel to recognize the result as a date. You need to create a formula in another column to combine them into one complete date. Which function should you use to achieve this?
- ⬜ DATEDIF
- ⬜ CONCAT
- ✅ DATE
Question 52
You are working on a worksheet that contains irregular formatting and errors. If you create the following formula what will be removed from the target cell B2? Select all that apply: =TRIM(B2)
- ✅ Spaces before the cell entry
- ✅ Spaces after the words in the cell entry
- ⬜ Spaces between words in the cell entry
Question 53
In one of the Adventure Works’ sheets, the full address for a supplier is incorrectly entered in one cell. Cell D6 contains the following entry in which there are no spaces. 1474MainAvenueFreemontCA You are creating formulas to divide this content into separate columns. If you add the following formula to a cell what would the result be? =MID(D6,15,8)
- ⬜ Avenue
- ✅ Freemont
- ⬜ Main
Question 54
Some information in an Adventure Works spreadsheet has been downloaded incorrectly from another source. In one column three pieces of information, a stock number, a supplier and a product category have been blended together. The entry in E7 reads: 245BSC Cylesaz Brakes System You want the product category, Brakes System, to be in a separate column. Which function would be the best to use in a formula to achieve this?
- ⬜ LEFT
- ✅ RIGHT
- ⬜ MID
Question 55
You are creating a worksheet that your colleagues will use to log their work time. You need cell E4 to always show the current date and time. Which formula should you enter in E4 to have it always show the current date and time?
- ✅ =NOW()
- ⬜ =NOW(mm/dd/yyyy,00:00)
- ⬜ =NOW(E4)
Question 56
You are working on a planning spreadsheet and creating some timeline calculations. Cell A2 of the spreadsheet contains the current date and cell B2 a deadline date. Which of the following formulas would correctly tell you how many calendar days there are between the two dates? The result must include the current date.
- ⬜ =A2-B2
- ✅ =B2-A2+1
- ⬜ =B2-A2
- ⬜ =A2-B2+1
Question 57
You’re currently working on a spreadsheet that contains the timeline for the rollout of your company’s new marketing campaign. Cell A2 contains the date 06/15/2023 and cell B2 the date 07/20/2023. (The dates are in the MM/DD/YYYY format.) You need to calculate the number of weekdays between the start date and the deadline date. When you add the following formula what result do you get? =NETWORKDAYS(A2,B2)
- ⬜ 35
- ⬜ 23
- ✅ 26
Question 58
You are working on a Quarter 1 report which will show the results achieved by each sales team in the three months of the quarter. If their results have exceeded a target amount in each of the three months, the team will be entitled to a bonus. You need to create a formula to work this out. Which combination of functions would be correct to do this?
- ⬜ An IF with a nested OR.
- ✅ An IF with a nested AND.
- ⬜ An IF with a nested IF.
Question 59
You are a sales manager for Adventure Works and you are currently reviewing the Quarter 1 results for the sales teams. The team for the western region did not meet their quarterly sales target set so you are about to have a meeting with them. The teams monthly sales totals are listed in cells C2, D2 and E2. You want to create a formula that would display the message “yes” if the team had met even one monthly target. Which of these formulas has the correct syntax to check for this?
- ⬜ =IF(AND(C2>50000,D2>50000,E2>50000),"Yes","No")
- ✅ =IF(OR(C2>50000,D2>50000,E2>50000),"Yes","No")
- ⬜ =IF(C2>50000,"Yes",IF(D2>50000,"Yes","no"))
Question 60
You are reviewing a spreadsheet that records a large number of sales listed by city. You intend to create a SUMIF formula that identifies all rows that have the city name Chicago in column A. Which characters do you need to add around the name Chicago when you are adding it as the criteria?
- ⬜ Parentheses
- ✅ Double quotation marks
- ⬜ Single quotation marks
Question 61
Which of the following statements are true about naming Excel files? Select all that apply.
- ⬜ Filenames can contain any punctuation symbol.
- ✅ Filenames can be a mix of text and numbers.
- ✅ Filenames can have up to 255 characters.
- ⬜ Filenames cannot have spaces.
Question 62
Which Tab on the Excel Ribbon area would you select to see commands to manage security settings or collaborate with colleagues?
- ⬜ Home
- ✅ Review
- ⬜ View
Question 63
You are editing a spreadsheet that already has information in it. You type a long text heading into cell A1 and because cell B1 already contains an entry you can’t see all of the text you just typed. Which choice would you select so that some of the words will appear on a second line and the whole heading will be visible?
- ⬜ Merge and center
- ✅ Wrap text
- ⬜ Orientation
Question 64
When you type the number 1.5 in the A1 and apply the Percentage format, how will Excel display it?
- ⬜ 1.5%
- ⬜ 15%
- ✅ 150%
Question 65
You open a spreadsheet that a colleague has shared with you and you notice that the column initial letter sequence is A, B, D. Why is the column initial letter C not visible?
- ✅ The column is hidden.
- ⬜ The column has been deleted.
- ⬜ The column has been repositioned.
Question 66
You want to remove the color that has been applied to the worksheet tabs. Which of the following series of steps would allow you to do this?
- ✅ Right-click on the worksheet Tab to open the shortcut menu. Select Tab color and choose No color.
- ⬜ Right-click on the worksheet tab. Select Tab color and choose Clear tab color.
- ⬜ Right-click on the worksheet Tab to open the shortcut menu. Select Tab color and choose Remove tab color.
Question 67
True or False: You can use the Undo feature to reverse deleting a worksheet as long as you haven’t saved the workbook.
- ⬜ True
- ✅ False
Question 68
You are compiling a sales report in an Excel worksheet. Cell C4 contains an important sales total. When you type the name June 2023 Sales Total in the Name box, Excel displays a warning message. Why has Excel rejected this name?
- ⬜ It contains numbers.
- ✅ It contains spaces.
- ⬜ It is too long.
Question 69
You are working on a worksheet that contains information on stock levels. You need to identify all rows in the data that have a figure of 1000 in the Stock Level column. Which of the following options can you use to complete this task?
- ⬜ Use Greater than on Number filter sub-menu.
- ✅ Use Equals on the Number filter sub-menu.
- ⬜ Use Does not equal on the Number filter sub-menu.
Question 70
You’ve been asked to update a spreadsheet that already contains a lot of information, so you are adding entries beyond row 100. Row one and column A contain headings. You do not want to keep scrolling up or left to see them. The cursor is on cell B2. Which of the following options must you choose?
- ⬜ Freeze first column
- ⬜ Freeze top row
- ✅ Freeze panes
Question 71
True or False: When Excel is processing a formula, it will always process the mathematical operators in the order in which it encounters them as it reads left to right.
- ⬜ True
- ✅ False
Question 72
You have the following formula in your worksheet. What would the result be?=30+10/2*3
- ⬜ 60
- ⬜ 32
- ✅ 45
Question 73
What mathematical symbol is processed first in the following formula?=((A3-H4)*G7)/2
- ⬜ The multiplication symbol.
- ✅ The subtraction symbol.
- ⬜ The division symbol.
Question 74
Cells J2, K2 and L2 in your worksheet all contain the number 5. In cell M2 there is a formula that reads:=J2*K2/$L$2
On the next row, cells J3, K3 and L3 all contain the number 10. If you copy the formula from cell M2 to cell M3 what will the result be?
- ⬜ 10
- ✅ 20
- ⬜ 5
Question 75
Cell A2 of your spreadsheet contains the value 500. Cell B2 contains the value 150 and cell C2 contains the entry 10%. You have entered the following formula in cell D2 which has the Percentage format applied.=(A2-B2)*C2
What would the result be?
- ✅ 350%
- ⬜ 35%
- ⬜ 3500%
Question 76
In your end of month sales report, you are creating a formula to compare last month’s sales figure with this month’s sales. You also want to display the percentage difference.
Cell E10 contains $125,000 and F10 contains $150,000.
Formula: =(F10-E10)/E10
What is the result?
- ✅ 0.2 (or 20%)
- ⬜ 20
- ⬜ 2
Question 77
You are editing a results spreadsheet that people update in real time to show results. You want to create a formula that will show you how many cells have been updated and have content in them.=COUNT(E7:E9)
If E7 = 150, E8 = “to be advised”, and E9 is empty, what result will Excel return?
- ⬜ 3
- ✅ 1
- ⬜ 2
Question 78
You are using the AutoSum shortcut to add a formula. When you click on the dropdown arrow, which of the following functions are available? Select all that apply:
- ✅ AVERAGE
- ✅ MIN
- ✅ SUM
- ✅ MAX
- ⬜ COUNTA
Question 79
You’ve been asked to create average calculations in a sales report.
Cells C1 and C2 contain 10, C3 is empty, C4 is 0, and C5 is 20.
Formula: =AVERAGE(C1:C5)
- ⬜ 13
- ✅ 10
- ⬜ 8
Question 80
You are using the Insert Function feature to add a SUM formula. In the Function Arguments dialog, what must you include in the Number 1 line? Select all that apply.
- ✅ The first cell reference of the block.
- ✅ A colon.
- ✅ The last cell reference of the block.
- ⬜ An opening parenthesis.
- ⬜ The Function name.
- ⬜ A closing parenthesis.
Question 81
True or False: Columns A, B and C contain numbers called Day, Month, and Year.
Formula: =CONCAT(A2,B2,C2)
This will create a valid date.
- ✅ False
- ⬜ True
Question 82
Column C contains stock codes. Cell C2 reads 2563tt.
Formula: =PROPER(C2)
What result will Excel display?
- ⬜ 2563tt
- ✅ 2563Tt
- ⬜ 2563TT
Question 83
Cell D6 contains:1474MainAvenueFreemontCA
Which formula will extract only the city name?
- ⬜ =MID(D6,9,6)
- ✅ =MID(D6,15,8)
- ⬜ =MID(D6,5,4)**
Question 84
Cell E7 contains:245BSC Cylesaz Brakes System
You want the product category “Brakes System” in a separate column. Which formula would achieve this?
- ⬜ =RIGHT(E7,11)
- ⬜ =RIGHT(E7,12)
- ✅ =RIGHT(E7,13)
Question 85
Column F contains dates, but in F10 the entry appears as:07\29\23
What mistake was made here?
- ✅ The wrong separator character was used.
- ⬜ The number format was set to General.
- ⬜ The year was only typed as two digits.
Question 86
True or False: You created a spreadsheet to log work hours. When reopened, cell A20 shows 45106 instead of a date. You can fix this by changing the Number format back to Date.
- ✅ True
- ⬜ False
Question 87
You are based in New York. The project start date is 06/15/23 (A2) and the deadline is 07/20/23 (B2).
Public holidays (e.g., 06/19/23, 07/04/23) are listed in M2:M11.
Formula: =NETWORKDAYS(A2,B2,M2:M11)
What is the result?
- ⬜ 26
- ✅ **24*
- ⬜ 23
Question 88
The order date in A20 is 07/01/2023, order value in B20 is $10,500.
Formula:=IF(AND(A20<06/30/2023,B20>10000),10%,IF(B20>5000,5%,0%))
What discount rate will apply?
- ✅ 5%
- ⬜ 0%
- ⬜ 10%
Question 89
You are reviewing Q1 results. Cell A22 = $150,000.
Formula:=IFS(A22>150000,"Send congratulation message on MS Teams",A22=150000,"Send individual Emails",A22<150000,"Schedule meeting with the team",TRUE,0)
What action will you take?
- ⬜ Schedule a meeting with the team.
- ⬜ Send congratulation message on MS Teams.
- ✅ Send individual Emails.
Question 90
You are reviewing sales by city and create the following formula:=SUMIF(A2:A50,"Chicago",C2:C50)
What does the first argument represent?
- ⬜ The sum range.
- ⬜ The criteria.
- ✅ The criteria range.