COURSE 1 – PREPARING DATA FOR ANALYSIS WITH MICROSOFT EXCEL

Module 4: Final Project and Assessment: Preparing Data for Analysis with Microsoft Excel

MICROSOFT POWER BI DATA ANALYST PROFESSIONAL CERTIFICATE

Complete Coursera Study Guide

Enroll in Coursera Microsoft Power BI Data Analyst

Last updated:

Landscape Banner Thumbnail Promo 400H

TABLE OF CONTENT

INTRODUCTION – Final project and assessment: Preparing data for analysis with Microsoft Excel

In this module, you will undergo an assessment designed to evaluate your proficiency in the key skills and concepts that have been covered throughout the course. This comprehensive evaluation aims to gauge your understanding and ability to apply the knowledge gained, ensuring that you are well-prepared to utilize these skills effectively in practical scenarios. By testing your grasp of the course material, the assessment serves as a crucial step in reinforcing your learning and identifying any areas that may require further attention or improvement.

Learning Objectives

  • Synthesize the skills from this course by completing a graded assessment.
  • Reflect on this course’s content and on the learning path that lies ahead.

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 Top Row
  • Freeze First Column
  • Freeze Panes (CORRECT)

That’s correct! The Freeze Panes option uses the position of the cursor to determine which areas of the screen should remain static. Columns A to F were to the left of the cursor position. Row one was above the cursor position because the cursor was on cell G2. These elements would remain static when the screen scrolled. Columns A to F and row one above the cursor would stay static when the screen is scrolled because the cursor was on cell G2.

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?

  • R2:R103 only
  • K2:K103 only
  • K2:K103 and R2:R103 (CORRECT)

That’s correct! Both the criteria range and the sum range need to stay the same when the formula is copied down. It is the criteria itself that will be adjusted for the copies of the formula in cells B13 and B14.

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?

  • (2022 total – 2023 total)/2022 total
  • (2023 total – 2022 total)/2023 total
  • (2023 total-2022 total)/2022 total (CORRECT)

That’s correct! The subtraction step calculates the amount by which the sales total has changed from 2022 to 2023. Dividing this amount by the 2022 total expresses the result as a percentage increase

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?

  • $164,740 (CORRECT)
  • $143,555
  • $145,535

That’s correct! The criteria range, and the sum range in the formula should have stayed the same during the copy operation because you added dollar signs to the cell reference. If you had this result in C14 when you finished the exercise, you also correctly changed the criteria to month number 3.

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 left edge of the cell range the heading is to be centered in. (CORRECT)
  • On the right 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. 

That’s correct. The heading entry must be typed in the cell on the left edge of the cell range that it is to be centered in. By then highlighting to the right edge of the range, you identify the correct area that Excel must center the heading entry across.  

COURSE QUIZ: PREPARING DATA FOR ANALYSIS WITH MS EXCEL

1. 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. (CORRECT)

That’s correct! As you type an entry in a cell, it will appear in the cell itself and also on the Formula bar. 

2. Which Tab on the Excel Ribbon area would you select to see commands to manage security settings or collaborate with colleagues?

  • View
  • Home
  • Review (CORRECT)

Feedback: That’s correct! The Review tab contains commands which are relevant for collaboration and security. The choices here include commands for comments, sharing and use of passwords in files.

3. Which of the following statements are true about naming Excel files? Select all that apply.

  • Filenames can contain any punctuation symbol.
  • Filenames cannot have spaces.
  • Filenames can be a mix of text and numbers. (CORRECT)
  • Filenames can have up to 255 characters. (CORRECT)

That’s correct! Filenames can contain both text and numbers.

That’s correct. Filenames can have up to 255 characters, but it is recommended that they do not exceed 31 characters.

4. 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 (CORRECT)

That’s correct! The Wrap text command will position the words of the heading so that the part of the heading to the right will appear underneath the words at the left. To accommodate this, the row will become deeper.

5. When you type the number 1.5 in the A1 and apply the Percentage format, how will Excel display it?

  • 150% (CORRECT)
  • 15%
  • 1.5%

That’s correct! The Percentage format option applies a format that displays the cell content as a percentage but it also multiplies by 100. The Percentage format option can be selected with the Percentage format button or the choice is also on the Number format dropdown list. This format is usually used to format the results of specific calculations.

6. 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. (CORRECT)

That’s correct! When the mouse pointer is positioned over the vertical line between the initial letters C and D it changes shape to a vertical bar with a horizontal double arrow. When it is in this shape, it is possible to double-click the mouse button to resize the column. 

7. 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 (CORRECT)
  • Capitals
  • Numbers (CORRECT)
  • Spaces 

That’s correct! The underscore is one of the characters often used to create visual gaps between words in the name.

That’s correct! Excel will accept a mixture of upper- and lower-case letters in a name assigned to a cell or a cell range.

That’s correct! Excel will accept a mixture of letters and numbers in a name assigned to a cell or a cell range. The only restriction is that an alphanumeric name cannot be the same as a cell reference in the worksheet grid.

8. 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 “Mountain Bike” in column A. Of these, 10 rows have “red” listed as the color in column B. Fifteen other rows for different products also have “red” in column B. If you filter by the term “Mountain Bike” on column A and then filter by the color “red” on column B, how many records will Excel display?

  • 25
  • 10 (CORRECT)
  • 40

That’s correct! If there is already a filter in place, the second filter only looks at the results of the first filter. In this case, the first filter produced 40 results. The second filter only matched the color entry on those 40 rows.

9. True or False: Operators are symbols used to indicate actions such as addition, subtraction, division and multiplication.

  • True  (CORRECT)
  • False

That’s correct! Excel uses standard mathematical symbols such as the + for addition, the – for subtract, the / for divide and the * for multiplication. These are referred to as mathematical operators. 

10. You have the following formula in your worksheet. What would the result be?

=30+10/2*3

  • 45 (CORRECT)
  • 60
  • 32

That’s correct! The division and multiplication symbols are processed before the plus symbol as they have a higher priority in the Order of Precedence. They have equal status with each other so Excel processes the division first to get a subtotal of 5. It then processes the multiplication which gives a second subtotal of 15 and finally it adds on the 30.

11. What mathematical symbol is processed first in the following formula?    

=((A3-H4)*G7)/2

  • The multiplication symbol.
  • The subtraction symbol. (CORRECT)
  • The division symbol.

That’s correct! The parentheses in the formula would control the order of processing. Excel would not be working according to the rules of the order of precedence.

12. 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 (CORRECT)
  • M6 (CORRECT)
  • H2
  • M2

Feedback: That’s correct! The dollar signs before the column initial and row number make this reference absolute which means that it will not dynamically adjust when the formula is copied.

That’s correct! The dollar signs before the column initial and row number make this reference absolute which means that it will not dynamically adjust when the formula is copied.

13. 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)

  • 3
  • 1
  • 2 (CORRECT)

That’s correct! The COUNTA function includes cells that have either numbers or text in its result. E9 is not included because it is blank.

14. 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 A6.
  • On cell B2. (CORRECT)
  • On cell A2.

That’s correct! The starting point for any formula, including function formulas, is to begin with the cursor on the cell where the formula is to be entered and the result displayed.

15. 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?

  • 12 (CORRECT)
  • 15
  • 10

That’s correct! Because all five of the cells in the range now contain numeric values, Excel totals the numbers and divides by 5 to work out the average.

16. 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 last cell reference of the block (CORRECT)
  • The Function name.
  • The first cell reference of the block. (CORRECT)
  • A closing parenthesis.
  • A colon. (CORRECT)
  • An opening parenthesis.

That’s correct! The cell range references need to be included on this line. Excel will often suggest the correct references.

That’s correct! The cell range references need to be included on this line. Excel will often suggest the correct references.

That’s correct! Because the SUM function will total a block of cells the start and end cell references need to be separated by a colon to indicate that it is range.

17. 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 (CORRECT)

That’s correct. Excel ignores the numbers at the beginning of the entry as the PROPER function will only apply to text. When PROPER looks at the two text characters, the function automatically makes the first one into a capital.

18. 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,5,4)
  • =MID(D6,9,6)
  • =MID(D6,15,8) (CORRECT)

That’s correct! The second argument in the formula asks Excel to move to character 15 in the search string. The third argument asks it to display 8 characters from that point on.

19. 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(E4)
  • =NOW(mm/dd/yyyy,00:00)
  • =NOW() (CORRECT)

That’s correct! The NOW function does not require any arguments. However, because of the way that functions are designed, it still requires an opening and closing parenthesis after the function name.

20. 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:

  • DATEDIF
  • MONTH (CORRECT)
  • YEAR (CORRECT)
  • DATE

That’s correct! The MONTH function extracts the month element from a full date entry.

That’s correct! The YEAR function extracts the year element from a full date entry.

21. 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(OR(C2>50000,D2>50000,E2>50000),”Yes”,”No”) (CORRECT)
  • =IF(C2>50000,”Yes”,IF(D2>50000,”Yes”,”no”))
  • =IF(AND(C2>50000,D2>50000,E2>50000),”Yes”,”No”)

That’s correct! The OR function will return a value of TRUE if even one criteria is met and this would cause the IF function to return the Value if True message. 

22. 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? 

  • Double quotation marks (CORRECT)
  • Parentheses
  • Single quotation marks

Feedback: That’s correct! Any piece of text included in function formula arguments needs to be enclosed within double quotation marks so that Excel recognizes that it is text.

23. 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 cell contains an extra text character.
  • The cell is formatted as text.
  • The column is too narrow. (CORRECT)

That’s correct. 

24. True or False: The maximum number of data rows that can be contained in an Excel workbook is one million. 

  • True 
  • False (CORRECT)

That’s correct. 

25. 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. (CORRECT)
  • The column has been deleted.
  • The column has been repositioned. 

That’s correct! Excel will always maintain the correct alphabetical sequence for the column initials in a worksheet. If a letter does not appear in the sequence, then it means that that column has been hidden.

26. 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 (CORRECT)

That’s correct! You can delete a worksheet by right-clicking the worksheet Tab and choosing Delete from the shortcut menu or by selecting the Delete sheet choice from the Delete dropdown on the Home ribbon. Whichever method you chose, you cannot use the Undo feature to reverse this deletion. If the worksheet has content, Excel will provide a message warning you of this before it lets you proceed.

27. 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. (CORRECT)
  • It is too long.

That’s correct. You cannot include spaces between words when assigning a name to a cell or a cell range. An underscore is often used between words to provide visual gaps between the words.

28. 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 copied and pasted.
  • The content has been filtered. (CORRECT)
  • The content has been sorted.

That’s correct! The gaps in the row numbers indicate that some rows, which did not match the filter criteria, have been hidden by the filter operation. The row numbers will also be blue if a filter is in place.

29. 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 (CORRECT)
  • Freeze first column
  • Freeze top row

That’s correct! The Freeze panes choice will use the position of the cursor as a reference and freeze everything above it and to the left of it. The cursor is positioned on B2 the row above, row one, will be frozen and the column to the left, column A, will also be frozen.

30. 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?

  • 500 (CORRECT)
  • 1000
  • 250

That’s correct. In this case, because the mathematical symbols have the same status in the order of precedence, Excel will simply work left to right. It will take the D7 value of 10 and multiply it by 100 and then divide the subtotal by 2.

31. Which set of parentheses will Excel process first when it is calculating the formula result?

=((A3-H4)+(F10+C4))*(G7/C8)

  • (G7/C8)
  •  (A3-H4) (CORRECT)
  •  (F10+C4)

That’s correct. 

32. 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?

  • 3500% (CORRECT)
  • 35% 
  • 350%

That’s correct. 

33. 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?

  • $175,000
  • $165,000 (CORRECT)
  • $185,000

That’s correct. Multiplying by 110% increases the value in D2 by 10%. You could also multiply by 1.10 to achieve the same result.

34. 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:

  • SUM (CORRECT)
  • COUNTA
  • MIN (CORRECT)
  • AVERAGE (CORRECT)
  • MAX (CORRECT)

That’s correct. The SUM function is the default choice on this shortcut button.

That’s correct. MIN is one of the functions you can access on the AutoSum drop down menu.

That’s correct. AVERAGE is one of the functions you can access on the AutoSum dropdown menu.

That’s correct. MAX is one of functions you can access on the AutoSum dropdown menu.

35. 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 (CORRECT)

That’s correct. To calculate the average, Excel will total all the numbers in the cell range and then divide by the number of cells that contain numbers. In this case, four of the cells contain numbers.

36. 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. Mary Anderson
  • Mary Anderson
  • Dr.MaryAnderson (CORRECT)

That’s correct! The person’s title has been added by including it as the first argument in the formula. By surrounding the title with double quotes Excel recognizes it as text to add to the entries from the other cells. However, the formula does not ask for spaces between words so the entries are all run together.

37. 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)

  • Freemont (CORRECT)
  • Avenue
  • Main

That’s correct! The second argument in the formula asks Excel to move to character 15 in the search string. The third argument asks it to display 8 characters from that point on.

38. 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 formula would achieve this?

  • =RIGHT(E7,13) (CORRECT)
  • =RIGHT(E7,11)
  • =RIGHT(E7,12)

That’s correct! The second argument asks Excel to extract the 13 characters from the right edge of the cell content. This number is comprised of the two words and the space between them 

39. 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(DD/MM/YYYY)
  • =TODAY(E4)
  • =TODAY() (CORRECT)

That’s correct! The TODAY function does not require any arguments. However, because of the way that functions are designed, it still requires an opening and closing parenthesis after the function name.

40. 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 individual Emails.
  • Send a congratulation message on MS Teams.
  • Schedule a meeting with the team. (CORRECT)

That’s correct! The western team’s sales total did not meet the first criteria or the second criteria so the first two logical tests failed and Excel moved on to the third logical test. The amount did meet that criteria so the formula would display the second Value If True result.

41. 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 sum range. (CORRECT)
  • The criteria range.
  • The criteria.

That’s correct! The third argument, the cell references C2:C50, indicates the cell range that Excel should look at to calculate the total. It will include in the total the figures on any rows where the criteria was also present in column A.

42. 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 Design tab.
  • On the Review tab.
  • On Contextual tabs. (CORRECT)

That’s correct. Excel has a range of extra Tabs that can appear on the far right of the Ribbon area. They appear when specific elements such as charts are added to the worksheet. These extra, context-sensitive Tabs are called Contextual Tabs.

43. You’re adding sales figures to a column in Excel. Some of the numbers do not have any decimal place figures. You would like the figures in the column to show two decimal places, even if they are zeros, and also to have a character between the thousands and the hundreds. Which of the following number formats should you apply?

  • Currency 
  • Number
  • Comma (CORRECT)

That’s correct.

44. 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 D (CORRECT)
  • Column E
  • Column C

That’s correct! When you select the Delete sheet columns choice from the Delete dropdown on the Home ribbon, Excel will check the position of the cursor and immediately delete the column that the cursor is in. It does not ask you to confirm the deletion.

45. 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? 

  • 95869M
  • #####
  • 95869 (CORRECT)

That’s correct! Because the serial number is alpha-numeric, Excel considers it to be text. There is content in B200, so Excel can’t use it as a temporary overflow display. Excel will display part of text entry if the column is too narrow to display it.

46. 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 Name manager dialog. (CORRECT)
  • The Name Box. (CORRECT)
  • The Search box.
  • Custom views.

That’s correct! The Name manager dialog is on the Formula ribbon. It displays the full list of named elements in the workbook.

That’s correct! The Name box which is to the left of the formula bar will display all visible names in the workbook in a dropdown list.

47. 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 Does not equal on the Number filter sub-menu.
  • Use Equals on the Number filter sub-menu. (CORRECT)
  • Use Greater than on Number filter sub-menu.

That’s correct! The Equals choice will match the number you specify exactly and display only the rows that contain that number.

48. 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 (CORRECT)
  • True

That’s correct.

49. 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

  • *
  • +
  •  / (CORRECT)

That’s correct! The division and multiplication symbols are processed before the plus symbol as they have a higher priority in the Order of Precedence. They have equal status with each other so Excel processes the division first and then the multiplication and finally the addition.

50. Your worksheet contains the following formula:   

=((100-20)+(20/2))/3

What would the result be?

  • 83
  • 30 (CORRECT)
  • 103

That’s correct! There are two actions enclosed in parentheses, the addition and the division. These will be done before anything else because both of these are enclosed in an outer set of parentheses and will be processed left to right. 

51. 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 (CORRECT)

That’s correct! The L2 cell reference is absolute so it would not adjust when the formula is copied down. Excel will multiply the value in J3 by the value in K3 giving a subtotal of 100. It will then divide the subtotal by the value in L2 which is 5.

52. 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?

  • 35%
  • 350%
  • 35 (CORRECT)

That’s correct! The percentage symbol in C2 means that Excel will use that value to calculate the percentage of the subtotal. However, the General format is still applied to cell D2 so the answer will be a decimal number.

53. 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
  • 1 (CORRECT)
  • 3

That’s correct! The COUNTBLANK function only includes cells that are completely empty in its count.

54. You’ve been asked to create average calculations in a sales report. 

Cells B1 to B3 in your worksheet contain numbers. Cell B4 contains a 0. B5 is blank. If you add the following formula to your worksheet, how will Excel work out the Average?

=AVERAGE(B1:B5)

  • Total the numbers in B1 to B5 and divide by 3.
  • Total the numbers in B1 to B5 and divide by 4. (CORRECT)
  • Total the number in B1 to B5 and divide by 5.

That’s correct! Even though the formula references B5, Excel will ignore it while it is calculating because it is empty. Cells B1 to B4 all contain numbers so Excel will divide by 4 despite the fact that one of the entries is a zero. 

55. 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?

  • CONCAT
  • DATEDIF
  • DATE (CORRECT)

That’s correct! The DATE function will combine numeric entries into one date. The result will automatically contain the correct syntax to be recognized as a date.

56. 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 (CORRECT)

That’s correct! The PROPER function will display the text with a single capital at the beginning of each word. In this case, the entry from B2 is already in this format so the function formula copies it unchanged to C2. 

57. 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 (CORRECT)
  • RIGHT

58. 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 (CORRECT)
  • False

That’s correct.

59. 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)

  • 23
  • 26
  • 24 (CORRECT)

That’s correct.

60. 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?

  • Autofill  (CORRECT)
  • Autocomplete
  • Autofit

That’s correct! The Autofill feature is a quick way to copy content or formulas down a column or across a row. When the mouse is resting on the bottom left corner of the cursor the mouse pointer shape changes to a black cross you can hold down the mouse button and drag it down to copy the original entry or, if there is data to the left, use the double click shortcut.

61. 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. (CORRECT)
  • 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.

That’s correct! When you select Tab color on the shortcut menu, you have a palette of colors to choose from. There is also a separate option that says No color. If you select this option, it will remove any color applied.

62 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.1000
  • 465.100 (CORRECT)
  • 465.10 

That’s correct! When you typed the entry in cell D4, Excel would have displayed as 465.1 as it always displays the positive decimal place numbers when the general format is selected. Each time you select the Increase Decimal button it will add another zero to indicate another decimal place.

63. 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 (CORRECT)
  • =D7/D2*100
  • =D2/D7

That’s correct! The weekly sales total needs to first be divided by the overall monthly total. The subtotal generated must be multiplied by 100 to show the percentage value. This formula will not display a result that contains a percentage symbol.

64. 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)

  • 2
  • 3
  • 1 (CORRECT)

That’s correct.

65. 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 (CORRECT)

That’s correct! The CONCAT function will generate a test result so this formula would combine the numbers into one text string. The result could not be used for date analysis.

66. 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 (CORRECT)
  • System
  • Cylesaz

That’s correct.

67. You are updating a spreadsheet that displays some of 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. (CORRECT)

That’s correct.

68. 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 C. (CORRECT)
  • In column D.

That’s correct! When you select one of the insert column choices, Excel will always add the new column to the left of the cursor position. To have the new column between the existing columns B and C, the cursor should be in column C before the Insert column choice is selected.

69. 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% (CORRECT)

That’s correct! The formula initially generates a decimal result of 0.2 but, because the Percentage format automatically multiplies by 100 and adds a percentage symbol character, the result will display as 20%.

70. 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?

  • 0 (CORRECT)
  • 10
  • 50 

That’s correct! A zero is a number. The MIN function in Excel is designed to display the lowest number in the cell range included as arguments.

71. 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?

  • A1:A9
  • A1:19
  • A11:19 (CORRECT)

That’s correct.

72. 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?

  • MID
  • LEFT
  • RIGHT (CORRECT)

That’s correct. The category name “Brake Systems” is at the right edge of the cell content. The RIGHT function starts at the right edge and counts to the left to identify the characters to extract. It would be the appropriate function to use here.

73. 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 first argument of this function represent?

  • The sum range.
  • The criteria range. (CORRECT)
  • The criteria.

That’s correct.

CONCLUSION – Final project and assessment: Preparing data for analysis with Microsoft Excel

In conclusion, the assessment in this module plays a pivotal role in evaluating your mastery of the key skills and concepts covered throughout the course. This comprehensive evaluation not only tests your understanding and practical application of the material but also reinforces your learning, ensuring you are well-prepared to utilize these skills effectively in real-world scenarios. It serves as an essential step in identifying areas of strength and pinpointing aspects that may require further development, ultimately contributing to your overall growth and proficiency in the subject matter.

Previous Module
Weekly Breakdown

Quiztudy Top Courses

Popular in Coursera

Liking our content? Then, don’t forget to ad us to your BOOKMARKS so you can find us easily!

Expanded Thumbnail Promo1

Mood Zone for Studying & Relaxing