preparing data for analysis with microsoft excel week 1 coursera answers

Self-review: Adding data to a worksheet

1. In the exercise Adding data to a worksheet, you added data to a worksheet named Sheet1. You first downloaded and opened the Excel file. You then verified and corrected the existing information within Sheet1. Next, you formatted the data, then added new information to the workbook. Finally, you managed the worksheet and renamed it Sample Figures. You also added a second worksheet called Exchange Rates.

Your final workbook should resemble the following screenshots:

Now it’s time to review your understanding of the tasks you completed by answering the questions that follow. Don’t forget that you can revisit the previous learning items to recap the process steps.

1. In Sheet1, Microsoft Excel automatically aligned a numerical entry to the left. In this case, such an action indicates an issue with the content. How can you correct an error like this?

  • Change the alignment.
  • Alter the Number format.
  • Edit the content.

2. In step 3, you formatted cells E23 to H25, which contained figures representing Japanese Yen amounts. What steps did you take to apply the Yen Currency format to this block of cells?

  • Select the Currency button in the Number section of the Home tab. Choose More Accounting Formats. With Custom highlighted as a category, select the Symbol drop-down on the right. Select Japanese from the list currency symbols that appear.
  • Select the Currency button in the Number section of the Home tab. Choose More Accounting Formats. With Currency highlighted as a category, select the Symbol drop-down on the right. Select Japanese from the list of currencies that appear.
  • Select the Currency button in the Number section of the Home tab. Choose More Accounting Formats. With Special highlighted as a category, select the Symbol drop-down on the right. Select Japanese from the list of currency symbols that appear.

3. As one of the final steps in the exercise, you hid the Contacts worksheet using the Hide & Unhide command. Where on the Home tab is this command located?

  • On the Format choice in the Editing group.
  • On the Format choice in the Styles group.
  • On the Format choice in the Cells group.

Knowledge check: Creating workbook content

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

  • The number format is incorrect.
  • The cell contains text and numbers.
  • The column is too narrow.

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

  • The Comma format
  • The Percentage format
  • The Currency format

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

  • Select column G and click Format on the Home tab. Choose Hide & Unhide from the drop-down menu and then unhide columns from the sub-menu.
  • Select the column to the right of the hidden column. Select Format on the Home tab. Choose Hide & Unhide from the drop-down menu and then unhide columns from the sub-menu.
  • Highlight columns F and H. Select Format on the Home tab. Choose Hide & Unhide from the drop-down menu and then unhide columns from the sub-menu.

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

  • Type the word Name in the cell underneath the heading.
  • Use the Center align command in the Alignment group on the Home tab.
  • Use the Wrap Text command in the Alignment group.

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

  • The Contextual Sort tab
  • The View tab
  • The Data tab

Self-review: Sorting data

In the exercise Sorting data, you sorted data in a worksheet. You first downloaded the file Adventure Works Inventory.xlsx and customized the relevant worksheet. You then performed a series of alpha-numeric sorts within the worksheet.

Your final worksheet should resemble the screenshot:

Now it’s time to review your understanding of the tasks you completed by answering the questions that follow. Don’t forget that you can revisit the previous learning items to recap the process steps.

9. In the exercise, you sorted the Products worksheet data by the Product Name information stored in column C. Which of the following steps did you perform first, so that data would be sorted when you selected the Sort Ascending choice?

  • Select Product Name from a drop-down list.
  • Select any cell in column C.
  • Select column C by selecting the column initial.

10. You were asked to sort the data first by Supplier and then by Units in Stock. Which choice did you select on the Data ribbon to perform this?

  • Sort
  • Sort A to Z
  • Sort Z to A

11. True or False: You used Sort to sort the data using two columns with a first-level sort by Supplier and a second-level sort data by Units in stock. This meant that when the sort was complete, the Supplier entries were no longer in alphabetical order.

  • True
  • False

Self-review: Filtering data

In the exercise Filtering data, you filtered data in a worksheet. You first downloaded and customized the file Adventure Works Inventory1.xlsx. You then filtered the data to find the records that you required.

Your final worksheet should resemble the screenshot:

Now it’s time to review your understanding of the tasks you completed by answering the questions that follow. Don’t forget that you can revisit the previous learning items to recap the process steps.

12. You have five filters in place on the data in your worksheet. What is the quickest way to remove all filters and restore the full display of data?

  • Use the Clear option on the Data ribbon.
  • Use the Undo button.
  • Use the Clear filter from choice in each filter drop-down.

13. You are working with a large block of data. You applied a filter but there are still a lot of rows visible. How can you determine how many results you have found?

  • Check the bottom-right of the Excel Screen.
  • Check the bottom-left of the Excel screen.
  • Check the middle of the status bar.

14. In the exercise, you selected the Product Name filter to filter and find all the entries relating to mountain bikes. What were your next steps?

  • You selected Text filter and then begins on the sub-menu.
  • You selected Text filter and then equals on the sub-menu.
  • You selected Text filter and then contains on the sub-menu.

Knowledge check: Working with blocks of data in Excel

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

  • The Name Box
  • The Title Bar
  • The Formula Bar

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

  • The Filter arrow drop-down symbol is in bold.
  • The Filter arrow contains an arrow symbol.
  • The Filter arrow contains a funnel symbol.

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

  • At the top of the column.
  • At the bottom of the column.
  • In the middle of the column.

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

  • Freeze Panes
  • Freeze Top Row
  • Freeze First Column

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

  • True
  • False

Module quiz: Excel fundamentals

20. A colleague in Adventure Works has asked you to quality-check information in a workbook. How would you know if the data in the workbook has been filtered? Select all that apply.

  • There are Filter arrows beside the headings on the first row of the screen.
  • There are gaps in the row numbering on the left of the screen.
  • Some Filter arrows have funnel symbols and are in a different color.
  • There are funnel symbols on some of the Filter arrows.

 

21. You want to identify all rows that contain a specific word, but the word might appear anywhere in a cell entry. Which of the following options can you use to complete this task?

  • Use Begins with on the Text Filter sub-menu.
  • Use Ends with on the Text Filter sub-menu.
  • Use Contains on the Text Filter sub-menu.

22. You are applying a multi-level Sort to a block of data. Row one of the data contains headings. How do you ensure that these titles are not sorted with the rest of the data?

  • Tick the box that says, My data has headers.
  • Select the whole data range.
  • Click the first row of the data range.

23. You are having difficulty locating numbers in a large worksheet. You decide to name the cells that contain these figures so that you can locate them quickly. Which of the following options are valid names you could use? Select all that apply.

  • MonthlyProfit2

  • Monthly Profit
  • Monthly_Profit

24. You use the New Window command to view two copies of your worksheet. Which ribbon do you select to access the New Window command?

  • The Review ribbon.
  • The View ribbon.
  • The Home ribbon.

25. What appears on the Formula bar when you enter a calculation in a worksheet cell?

  • The calculation.
  • The result.
  • The calculation and the result.

26. In larger worksheets, it can be useful to hide columns that are not required. This allows you to scroll through the content more quickly. What is the key visual indicator onscreen that there are columns hidden in a worksheet?

  • There is a darker vertical line between two of the column initials.
  • There is a gap in the initial letters at the top of the screen.
  • Some column initials are shaded in a different background color.

27. You type a supplier name into cell C2 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 C100. What shape should the mouse pointer be?

  • An arrow.
  • A white cross.
  • A narrow black cross.

28. You need to present a worksheet to your colleague. However, columns D and E contain confidential data that they cannot be privy to. What option on the Home ribbon can you use to hide these columns during the presentation?

  • Format drop-down.
  • Insert drop-down.
  • Delete drop-down.

29. Cell A2 in your worksheet contains the number 50. You want this number to be recognized as a percentage. If you apply the Percentage format to this cell what number would now be showing in A2?

  • 5000%
  • 500%
  • 50%

 

30. True or False: When you select the Filter button on the Data ribbon, Excel automatically filters the data based on choices that you have already made in the drop-down menus next to each heading.

  • True
  • False

31. You want to sort information alphabetically by supplier and then by product in an inventory file. You access the Sort dialog and choose the supplier as the Sort by option. Which of the following series of steps do you need to carry out next to complete this sort?

  • Select Add and choose Product from the then by order column.
  • Select Add and choose product from the then by column drop-down.
  • Select Add and choose Supplier from the Then by column drop-down.

32. You want to create a new name for a cell, but you are not sure if a colleague has already assigned names to the cells. How would you check what names are already in use across the whole workbook?

  • Select each worksheet separately and then click on the drop-down arrow on the right of the Name box in the top left of the Excel work area.
  • Select the drop-down arrow on the right of the Name box in the top left of the Excel work area.
  • Select the Name Manager choice on the Home ribbon.

 

33. You are working with a block of information, and you realize that you need to add a new column to the left of the column that your cursor is in. Which of the following series of steps can you perform to correctly insert the column? Select all that apply.

  • Select the Insert Ribbon, select on Insert, and then select Insert Sheet Columns.
  • Click the right mouse button, select Insert, and then select Entire column.
  • Select the Home ribbon, select Insert, and then select Insert Sheet Columns.

34. You enter numbers into a column. Your entries are displaying a different number of decimal places from those you intended. Where can you locate the increase and decrease decimal buttons to control the number of decimal places displayed?

  • In the Alignment group on the Home ribbon.
  • In the Editing group on the Home ribbon.
  • In the Number group on the Home ribbon.

35. Your worksheet has the Filter feature turned on. The Filter arrows on two columns labeled Name and Town have Funnel symbols on them. How can you alter the display to show the data filtered by Town but not by Name?

  • Use the Clear option in the Filter group in the Data Ribbon.
  • Use the Clear Filter option on the Filter menu in the Name column.
  • Use the Clear Filter option on the Filter menu in the Town column.

36. You are working on an inventory spreadsheet that contains a list of suppliers and products. You need to know how many touring bikes are supplied by a company which is called "Cycles". However, several other suppliers in the spreadsheet also have the word cycles in their name. Which of the following options should you use to complete this task?

  • Use Begins with on the Text Filter sub-menu.
  • Use Equals on the Text Filter sub-menu.
  • Use Ends with on the Text Filter sub-menu.

37. You want to assign a specific name to a cell. You have used this same name previously for a different cell. Where can you view the sheet and cell reference that indicates where the name has been assigned?

  • Use the Name box on the top left of the worksheet.
  • Use the Search feature at the top of the Excel window and type the name.
  • Use the Name Manager in the Formulas ribbon

38. When Autosave is switched on, Microsoft Excel automatically assigns a generic name to a new workbook. This name is visible on the Title bar at the top of the Excel window. What can you change within this file name?

  • The file name and the save location.
  • Only the save location of the file.
  • Only the file name.

 

39. You need to change the position of sheets in a workbook. Which set of steps can you take to complete this task? Select all that apply.

  • Point at the sheet tab. Hold down the Ctrl key and then the mouse button. Drag the sheet to a new location.
  • Point at the sheet tab. Hold down the mouse button. Drag the sheet to a new location.
  • Point at the sheet tab. Click the right mouse button. Choose Move or Copy from the shortcut menu.

40. As you type a list of city names into a column in Excel, a floating dialog saying Convert to Geography appears. You accidentally select this feature and reveal a card icon. How can you reverse this choice?

  • Select Undo on the left-hand side of the Title Bar.
  • Select the floating dialog which should now read Remove Geography.
  • Right-click on the card and immediately choose Convert to Text from the options presented.

 

41. You manually adjust the width of column A and then type 958697100T into cell A2. However, the entry still displays across cell B2, which is currently empty. What is the visual effect on the entry in cell A2 when you enter data into cell B2?

  • The content will only be partially displayed and some of the entry will be hidden by the content in B2.
  • The content will be replaced by crosshatch symbols as the column is too narrow to display it fully.
  • The content will automatically “wrap around” so that some of the heading will be brought down to a new line.

Leave a Reply