preparing data for analysis with microsoft excel week 4 coursera answers
Self-review: Creating an executive data summary
1. You had to freeze the screen so that the summary data in columns A to F and the headings in row 1 were always visible. On the Freeze drop-down options in the View ribbon, which choice did you make?
- Freeze First Column
- Freeze Top Row
- Freeze Panes
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 did you need to add dollar signs to?
- K2:K103 only
- K2:K103 and R2:R103
- R2:R103 only
3. In cell D6, you created a formula to work out the percentage difference between the 2022 quarter-one sales and the 2023 quarter-one sales. Which of the following describes the logic of how this calculation should be designed?
- (2023 total-2022 total)/2022 total
- (2023 total – 2022 total)/2023 total
- (2022 total – 2023 total)/2022 total
4. In cell C12 you used SUMIF to calculate the total sales for January 2023 and then copied that formula down to cells C13 and C14. When you completed the exercise, what result was generated in C14?
- $145,535
- $164,740
- $143,555
5. The headings you added in rows 4 and 10 had to be centered across the tables of information that they related to by using the Merge & Center choice on the Home ribbon. Before selecting the Merge & Center button you typed the heading and then highlighted the cell range that the heading had to be centered in. Where should you type the heading before selecting Merge & center?
- On the right edge of the cell range the heading is to be centered in.
- 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.
Course quiz: Preparing data for analysis with MS Excel
6. 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 bar only.
- In the worksheet and the Formula bar.
7. True or False: The maximum number of data rows that can be contained in an Excel workbook is one million.
- True
- False
8. 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
- Orientation
- Wrap text
9. When you type the number 1.5 in the A1 and apply the Percentage format, how will Excel display it?
- 15%
- 150%
- 1.5%
10. 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 the initials C and D.
- Double-click the initial letter C.
- Double-click the vertical line between initials B and C.
11. You want to update your worksheet’s name. What is the correct sequence of steps to rename the spreadsheet you are working on?
- Triple-click the worksheet Tab. Type the new name.
- Double-click the worksheet Tab. Type the new name.
- Single-click the worksheet Tab. Type the new name.
12. You are updating an inventory file with new stock items that Adventure Works have just received. You have previously set the width of column A to only show 5 characters. Cell B200 contains a heading. If you type the 6 character serial number 95869M in A200 how will it appear in the worksheet?
- #####
- 95869
- 95869M
13. 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 is too long.
- It contains numbers.
- It contains spaces.
14. You are working on a worksheet that contains information on stock levels. You need to identify all rows in the data 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.
15. 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 top row
- Freeze first column
- Freeze panes
17. Cell D7 in your worksheet contains the value 10. In another cell in the workbook you have the following formula:
=D7*100/2
What is the result of this calculation?
- 250
- 500
- 1000
18. What mathematical symbol is processed first in the following formula?
=((A3-H4)*G7)/2
- The division symbol.
- The subtraction symbol.
- The multiplication symbol.
19. The following formula appears in your worksheet.
=((M2-$M$6)+($G$10/H2))
Which references will remain constant when the formula is copied down to the next row? Select all that apply:
- G10
- M6
- M2
- H2
20. 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%
21. 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 the sales figure for the last month of $125,000. Cell F10 holds this month’s sales figure of $150,000. G10 is still set to General format and contains the formula:
=(F10-E10)/E10
What is the result?
- 20
- 20%
- 0.2
22. 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)
- 3
- 1
- 2
23. 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?
- 50
- 0
- 10
24. 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
25. 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)
- 600
- 100
- 300
26. You are editing a worksheet to put the text content into a more appropriate format.
In the worksheet, cell A2 contains the first name “Mary”. Cell B2 contains the last name “Anderson”. In C2 you have created the following formula:
=CONCAT(“Dr.”,A2,B2)
What would the result of this formula be?
- Dr.MaryAnderson
- Dr. Mary Anderson
- Mary Anderson
27. 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 between words in the cell entry.
- Spaces after the words in the cell entry.
- Spaces before the cell entry.
28. In one of the Adventure Works sheets, the full address for a supplier is incorrectly entered in the one cell. Cell B7 contains the entry:
1474MainAvenueFreemontCA
You would like C7 to contain just the city name from this entry. Which function should you use?
- LEFT
- MID
- RIGHT
29. 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
- MID
- RIGHT
30. 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()
- =TODAY(E4)
- =TODAY(DD/MM/YYYY)
31. True or False: You recently created a spreadsheet that your colleagues can use to track and log their work hours. You designed it so that they would enter the date worked in column A and then their shift hours in other columns to the right.
When you reopen the spreadsheet cell A20 contains a number 45106 rather than a date. You can fix this by changing the Number format back to Date.
- True
- False
32. You are based in the New York office of Adventure Works and you’re currently working on a spreadsheet that contains the timeline for the rollout of their new marketing campaign. All the dates in the spreadsheet are in the MM/DD/YY format.
You need to calculate the number of weekdays between the start date of 06/15/23 in A2 and the deadline date 07/20/23 in B2. The formula needs to exclude the weekends and Public Holiday in this date range.
The range M2 to M11 contains a list of the United States Public Holidays including the Public Holidays on 06/19/23 and 07/04/23.
When you add the following formula what result do you get?
=NETWORKDAYS(A2,B2,M2:M11)
- 26
- 23
- 24
33. 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 Cycles?
=IF(AND(A20<06/30/2023,B20>10000),10%,IF(B20>5000,5%,0%))
- 10%
- 5%
- 0%
34. 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 northern region achieved sales of $150,000. This figure is displayed in cell A22. 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 congratulation message on MS Teams.
- Send individual Emails.
- Schedule a meeting with the team.
35. You are reviewing a spreadsheet that records a large number of sales listed by city. You want to generate city-specific totals so you create the following formula using the SUMIF function:
=SUMIF(A2:A50,"Chicago”,C2:C50)
What does the third argument of this function represent?
- The criteria.
- The criteria range.
- The sum range.
36. You are typing sales data into a column in your Microsoft Excel worksheet. When you type a numeric entry and press enter, the following entry appears:
######
Why has Excel displayed this in the cell?
- The column is too narrow.
- The cell contains an extra text character.
- The cell is formatted as text.
37. In the inventory spreadsheet that you are updating, the supplier name needs to be entered in column A. You type a supplier name into cell A2 in your spreadsheet. You want to use one of Excel’s quick entry features to copy the same supplier name into all cells down to A100. What feature should you use?
- Autofit
- Autocomplete
- Autofill
38. 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 ribbon, which column will Excel delete?
- Column C
- Column D
- Column E
39. 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
40. One of the cells in your worksheet contains a number that you will need to refer to frequently. You decide to assign a name to the cell so that you can move quickly to it. Which of the following are allowed in names assigned to a cell or a cell range? Select all that apply.
- Underscore Characters
- Numbers
- Capitals
- Spaces
41. You open an Excel file that other colleagues have worked on and notice that there are gaps in the row numbers on the left of the screen and the numbers are in blue. What does this indicate?
- The content has been filtered.
- The content has been sorted.
- The content has been copied and pasted.
42. 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
- 25
- 40
43. 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
- *
- +
- /
44. 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
- 5
- 10
45. 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?
=COUNTA(E7:E9)
- 1
- 3
- 2
46. 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. There is a formula in D5 which is currently displaying the result 10. The formula reads:
=AVERAGE(C1:C5)
If you type 20 in C3 and press enter what result is now showing in cell D5?
- 15
- 10
- 12
47. You are using the Insert Function feature to add a SUM formula to your worksheet which will total the values in a block of cells. You are on the second step of the Insert Function which is a dialog called Function arguments. In the Number 1 line, what must you include? Select all that apply.
- The Function name.
- A colon.
- The first cell reference of the block.
- The last cell reference of the block
- A closing parenthesis.
- An opening parenthesis.
48. 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
49. 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)
50. 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
What is the result of the following formula:
=LEFT(E7,6)
- 245BSC
- System
- Cylesaz
51. 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)
52. 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
- DATE
- DATEDIF
- YEAR
53. 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)
- 23
- 35
- 26
54. 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
- Single quotation marks
- Double quotation marks
55. Which of the following statements are true about naming Excel files? Select all that apply.
- Filenames can be a mix of text and numbers.
- Filenames cannot have spaces.
- Filenames can contain any punctuation symbol.
- Filenames can have up to 255 characters.
56. You’ve been asked to update a customer details spreadsheet that has the customer title in column B and their last name in column C. You’ve been asked to add their first names so you need to add a new column to your spreadsheet between the existing columns B and C. Before you choose one of the Insert column options on the ribbon or shortcut menu, where should you position your cursor?
- In column B.
- In column D.
- In column C.
57. You’re adding new stock items to an Adventure Works inventory file. Column D in your worksheet is still set to a general format. You type entry 465.1 in D4. If you select the Increase decimal button in the Number section twice because you want the cell to show two decimal places, what will the cell show?
- 465.100
- 465.1000
- 465.10
58. 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
59. You have the following formula in your worksheet. What would the result be?
=30+10/2*3
- 45
- 32
- 60
60. Your worksheet contains the following formula:
=((100-20)+(20/2))/3
What would the result be?
- 83
- 30
- 103
61. 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
- 5
- 20
62. In your end of month sales report, you are creating a formula to compare last month’s sales figure with this month’s sales and display the percentage difference. Cell E10 contains the sales figure for the last month of $125,000. Cell F10 holds this month’s sales figure of $150,000. G10 is set to Percentage format and contains the formula:
=(F10-E10)/E10
What is the result?
- 20
- 0.2
- 20%
63. 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:
- COUNTA
- MAX
- AVERAGE
- SUM
- MIN
64. 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:19
- A1:19
- A1:A9
65. You are working on a spreadsheet that has a lot of typing errors in customer names entered in column B. Cell B2 contains the entry “Mary Anderson”. If you create the following formula in cell C2 what result will be displayed? =PROPER(B2)
- mary anderson
- MARY ANDERSON
- Mary Anderson
66. 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.
- =B2-A2+1
- =B2-A2
- =A2-B2+1
- =A2-B2
67. 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?
- NETWORKDAYS
- NETWORKDAYS.INTL
- DATEDIF
68. 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.
