Module 4: Final Project and Assessment: Preparing Data for Analysis with Microsoft Excel
INTRODUCTION – Final project and assessment: Preparing data for analysis with Microsoft Excel
As a trainee, you will be taking a diagnostic assessment in this module so that you can demonstrate mastery-the overall core competences dealt with during your course of study. The assessment evaluates how well you have applied what you have learned as it relates to usefulness in a real-life setting. Such an inspection of your knowledge, hence, in such cases strengthens your learning and puts into focus any area that can further be reviewed or practiced.
Evaluation for this module is a test after picking up all the essential skills incorporated in the whole discussion. This evaluation judges if you can transfer that knowledge, which you got from the course. It will check if you are prepared to perform with the skills within real-life scenarios. Understanding this, therefore, tests how well you can explain the material after that and can, therefore, focus on improving or practicing any weak areas in your learning.
Learning Objectives:
- A profound reflection on both course content and self in learning 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 will allow the user to determine which parts of the screen shall whence remain the same while the location of the cursor decides regarding it. Since the cursor is placed on cell G2 here, all columns left to the cursor (i.e., Columns A to F) and the row above it (i.e., Row 1) shall remain frozen. They will be visible during the scrolling in the worksheet and keep in view the vital headers or data points.
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! One needs to make sure that both the range of criteria and the sum range do not change when one drags the formula downwards. This is done by enabling the subsequent components. Thus, the criteria are then adjusted for variation when pasting the formulas in cells B13 and B14 since the reference is generally with reference to the row where the formula is being pasted.
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 change or increase in sales in 2023 from 2022 is the value which is achieved by subtracting the two values that are to be calculated out of their sum. To find out the change or increase from the total sales in 2022, one should subtract the yearly values and divide by the total value in 2022. Then multiply this quotient by 100.
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! Theoretically, the range of criteria and summed ranges did not change with the copying operation since in the cell references absolute as a result of the dollar signs. If by the end of completing the exercise, the results read in C14 are accurate, then it implies that the criteria have been completely updated in relation to month number 3 as this should have been.
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. First, to align a heading across a few cells, the heading should be typed on the cell at the left edge of the cells you want to align, and then by marking off (highlighting) the cells that run over to the right edge, you define the range that Excel will take into consideration while centering this heading.
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! In essence, what results is the display of the content in a cell or in the Formula Bar. Would this allow you to see or make whatever changes in that content?
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 is correct! The Review tab allows you to work together on cooperating, and for continued protection. This tab comprises tools which allows you to insert or manage comments, sharing files, implementing passwords for file security.
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)
Correct, file names consist of both letter and number.
Also remember file name can be up to 255 characters long, while it is recommended not to have one with more than 31 characters-that would make it difficult to read and compatible; it may be deleted.setAlignment with the W3C standards.
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! Text wrap is the provision used by MSEXCEL when text is very extensive and does not fit within a particular column width. Text will automatically be caught wherever the word limit within the same cell width is exhausted. For, if the word limit is finished and the sentence is still exceeding before the word limit separation, the rest of the missing letters will move down and start from a new available space beneath. This in turn increases the row height by the extent of the deflection.
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! A percent template is applied and multiplies cells by 100 on rendering, thus giving them the appearance of a percentage. Sure, you can apply this percentage using the percentage template button or by selecting a transaction profile from the number format dropdown on the toolbar; indeed, percentages might be used on results from calculated percentages like growth rates or absolute changes in values.
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! As the pointer is occupying itself over the area of the vertical line separating the heading of the columns (e.g. between C and D), the shape changes into a vertical bar with a horizontal double arrow. You can double click here to let the mouse automatically adjust the column width to fit the longest value in 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 it! The gap is getting pretty common on names while the word reads.
That’s as well correct! In that, Excel even allows the combination of uppercase and lowercase-colored letters used to name cells or ranges, and it has flexible naming conventions.
Besides, Excel will also recognize the mixture of alphanumeric characters with exceptions that a mixed alphanumeric name should not be a valid cell reference such as A1, B2, and so on.
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
Yes, yes! When the user has already set with a filter and a second filter is already applied in place, it will consider only the results of the first filter. In this case, for example, the first filter will return 40 results; therefore, the second filter will only consider the criterion (in this case: the entry color) within those 40 rows that will pass the first filter.
9. True or False: Operators are symbols used to indicate actions such as addition, subtraction, division and multiplication.
- True (CORRECT)
- False
That’s correct! The information collected will be essential in helping students achieve their academic goals.
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! In the order of precedence, the operations of division and multiplication have priority over the operation of addition. Since the priority of division and multiplication is the same in the hierarchy, it will process first the operation of division followed by the multiplication of first-phase subtotal 5, then perform the addition of non-overlapping multiplicliplication that would have been 3*5+30rotation of 540.
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
A good and proper philosophy is richly rewarding and ultimately provides one with a mature understanding of life.
It is true. An apparent absolute absolute reference of the dollar signs before the column letter and row number prevents it from getting adjusted when the formula is duplicated.
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! Cells that contain either numbers or text can be counted by the COUNTA function. E9 is excluded in the resultant count since 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 first action you’d need to do, for any formula, even ones focused on functions, is position the cursor in the cell in which you need a formula and where the result will be recalled with every entry.
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.
Yes, the reference range of cells to sum is the cell reference. Excel can suggest the right one generally.
The reference range of cells to sum is the cell reference,. You should make sure the begin and end cell references are separated by a colon if the SUM function adds the referenced cells in a cell 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. PROPER only capitalizes letters so any numbers at the beginning of the entry will be ignored by Excel. When given two text characters, PROPER looks for that and will automatically capitalize the first one.
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 doesn’t take in any arguments, but like all functions, it still requires parenthesis immediately following 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! In a worksheet, excel gives the column initials an order consistent with their sequence and alphabetical sequence. If one of the letters retrieved from that sequence is missing, 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)
Certain! One can erase a worksheet by either right-clicking on the worksheet tab and selecting “Delete” from the menu or choosing “Delete Sheet” from the Delete dropdown on the Home ribbon. Once you delete a worksheet, however, it is irrevocable; you cannot use Undo to reverse the action. If the sheet contains content, Excel displays a warning message and asks you to confirm before you delete the corresponding worksheet.
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. A blank space cannot intervene between words when assigning names to cells or ranges in Excel-so the underscore is the normal character to add as a visual separator.
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! Files excluded by filters are not visible and have their row positions record gaps in the row number column. Essentially, the blue row numbers are used to outline which rows are displaying as a result of a filter application.
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! When the mouse cursor is on cell B2, “Freeze Panes” freezes all of Row 1 and Column A.
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)
Without a doubt that the SUM function would be an optimal selection for autoSum’s shortcut button.
Definitely! MIN is one of the many functions available in the autoSum dropdown menu.
Definitely! Also: AVERAGE as the function available is listed under the dropdown menu of the Autosum calculation.
That’s it! Another function: MAX, available under the dropdown list of Autosum calculation features.
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 title of the person is included by making it the first argument in the formula. By placing the title in quotes, Excel recognizes that it is text, and subsequently adds it to the entries of the other cells. Because there are no spaces in the formula between words, entries tend to 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 indicates a request to have Excel pull the last 13 characters from the right edge of the cell content. The two words and the space between the words count toward this number.
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! When using the TODAY function, no arguments are necessary. Yet, that being said, the structure of functions calls for the opening and closing parenthesis to be placed after the function’s 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 total sales figure of the Western Team was not met on the first logical test and, nor on the second, so both logical tests had failed and moved on to the third test. Given that the amount met the third criteria, the outcome was “Value If True” second 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 cell range C2:C50 specifies the third parameter out of the three, the cells that Excel needs to consider for deriving the sum. It will sum up those rows with matching conditions in the column A too.
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! There are additional Tabs in Excel that can be seen on the far right of the Ribbon area. These tabs are used to expand context when various elements, like charts, get added to the worksheet. These are the additional Tabs mentioned as Contextual Tabs.
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! As the ID is alphanumeric, Excel considers it to be text. With content in B200, Excel would not use it as a temporary overflow display. Where a column is too narrow to show entry in full, Excel only views part of 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 the way it is! The Microsoft solution Name Manager is available on the Formula ribbon and it brings about the full list of named elements deployed in the workbook in this format.
Of course! Microsoft application Name Manager offers a long list of particularly deployed cells in the worksheet in a drop-down list located to the left of the formula bar.
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! Exact number here will be matched with the number that contains the word “Matches”, and the matching rows will be presented here.
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! Usually, the order of precedence states that the division and multiplication symbols have a higher priority than the plus symbol; and therefore, they will be prioritized first for processing. If the division is processed first by Excel, followed by the multiplication, then the addition will come last since division and multiplication have equal order priority.
50. Your worksheet contains the following formula:
=((100-20)+(20/2))/3
What would the result be?
- 83
- 30 (CORRECT)
- 103
That’s correct! This speaks clearly and loudly; commas are inserted between examples of independent clauses; it is helpful to know the basics of an independent clause because it has a subject and verb and can stand alone as a complete thought; for this reason, the meaning that you are trying to write down must be very clear because otherwise the reader will not know what you mean or want to do with the following points that are not always about your statement.
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! If you include a percentage symbol in cell C2, then Excel will interpret whatever number is there to be a percentage of the total. But even with that written in and even though its format is that of a sum in General, the result still comes up as a decimal.
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! A cell reference from one of the adjacent cells is used to compute the value of this particular cell. When cells in range B1 to B4 have numbers in them, the division will be over 4 instead of over 3 even if one of the entries is 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! Function PROPER capitalizes the first letter in the text. Thus, it can just be transferred without modification to C2 since the entry in B2 fits in that format already.
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 tool is seen when a mouse pointer is brought into the lower right corner of the selected cell; then, you can click, hold, and drag to autofill just like entering data. To autofill the data in the available cells automatically, we can do it quickly to the left by double-clicking.
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! You will usually find yourself in a position where you need to choose a color for your tabs and when you right-click the tab, you will see an option called “Tab Color.” This option brings up a palette for selecting colors, with the reference to “No Color” as a distinct option that has flash in itself; if you choose this option, the color from the tab bar would be excised.
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! The value when entered into cell D4 would have displayed as 465.1 in Excel since in the General format, it will display positive decimal numbers. Every time the “Increase Decimal” button is pushed, Excel will go on to add another zero and display 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! After you divide the monthly total by weekly sales, multiply it by 100 to get the percentage figure. Nevertheless, this wouldn’t make the resulting value show up in a percentage format unless your cell is formatted into such a manner.
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! Whenever you select any one of the “Insert Column” choices from Excel, they always ensure that the new column is always to the right of the position where you had placed the cursor. That means when you decide to insert a column between appropriate Column B and Column C, place the cursor in Column C first and later click on “Insert Column.”
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
Absolutely! A digitless item can not only symbolize a number. The numerical attribute is the least among the available options supplied by the numbers in the indicated cells.
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 field category “Brakes for Road Vehicles” stays at the right edge of the content cell. RIGHT function should generally start from the right end and move to the left to determine the particular character you want to extract in this context. That is the best function to apply in such a situation.
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
The self-assessment in this module is significantly crucial for testing the maturity and appropriateness of the learning regarding the core skills and concepts provided in the whole course. A very profound examination, in addition to examining the understanding and its application in practice, where appropriate, will really strengthen learning to the extent that one may utilize the skills in real-world scenarios. Eventually, one could find out his models that exemplify in performance, showcasing sections that need further training and eventually developing capability and efficiency in the domain.