excel basics for data analysis coursera answers week 4

Data Analysis Basics, Filtering and Sorting Data

Practice Quiz

1. Which of the following are valid sorting orders you can choose when adding sorting levels to your data? Select all that apply.

  • Z to A

  • Smallest to Largest

  • Youngest to Oldest 
  • Oldest to Newest

2. After filtering a column and getting the results, in which two ways can you return to showing all the data in a column? Select two answers.

  • refresh the worksheet
  • refresh the column 
  • clear the filter 

  • turn filtering off

3. What do custom filters provide that AutoFilters don't?

  • associate a formula with the filter control 
  • filter a column to see only above or below a certain value 
  • display more data 
  • set multiple filters at the same time 

4. According to the video 'Useful Functions for Data Analysis', what is one of the most common functions a Data Analyst might use?

  • Countif
  • Int
  • Randbetween 
  • Average

5. What does VLOOKUP stand for?

  • Vertical Lookup 
  • Volume Lookup 
  • Variable Lookup 
  • Virtual Lookup

Graded Quiz

6. If you have multiple filters set, how can you clear all of them at once?

  • You must clear each filter separately
  • Data tab > Sort & Filter group > Clear
  • Refresh the column
  • Turn sorting off

7. After enabling Filtering, where can you see and access the filter controls?

  • Only selected cells have filter controls
  • You must manually select data for filtering
  • Each column header now has a filter control
  • Each row has filter controls

8. The IF function applies to one or two conditions, but what if you need to apply multiple conditions?

  • Use the nesting capabilities of the IF function
  • Use the IF-Then function
  • Use the IFor function
  • Use separate IF functions

9. When you use nested functions, what is required for each of the functions?

  • Each function must be alphabetically ordered
  • A set of parentheses for each function
  • Semi-colon between each function
  • =IF to begin each function

10. The difference between HLOOKUP, VLOOKUP, and XLOOKUP is how they look for data. How does each look for data?

  • HLOOKUP = by column

    VLOOKUP = by row

    XLOOKUP = in all directions

  • HLOOKUP = by row

    VLOOKUP = in all directions

    XLOOKUP = by column

  • HLOOKUP = in all directions

    VLOOKUP = by column

    XLOOKUP = by row

  • HLOOKUP = by row

    VLOOKUP = by column

    XLOOKUP = all directions

Using Pivot Tables

Practice Quiz

11. Before creating a pivot table, how should you format your data?

  • Sort & filter 
  • Conditional formatting 
  • Cell size 
  • As a table

12. How can you add more filters to the pivot table?

  • use a formula
  • drag a field to the Filters area of the PivotTable Fields pane
  • use a function
  • first add filters to the original table data

13. What must you do first before adding another slicer to a pivot table?

  • select a cell in the pivot table
  • add additional fields to the pivot table
  • remove all existing slicers from the pivot table
  • remove all current filters from the pivot table

14. What do timelines provide in pivot tables?

  • previews views of different filters
  • provides details of all actions performed during a given period
  • sets the pivot table to an earlier view
  • lets you filter specifically on date-related data

Graded Quiz

15. According to the video checklist, what should you remove before making a Pivot Table?

  • Value fields
  • Date fields
  • Blank rows, columns, and cells
  • Row labels

16. What is automatically added after formatting data as a table?

  • Column headers
  • Alternate light/dark rows
  • Data bars
  • Filter drop-downs at top of columns

17. After creating a pivot table and selecting it, what pane appears to the right of the pivot table?

  • The PivotTable Metadata pane
  • The PivotTable Fields pane
  • The PivotTable Properties pane
  • Additional pivot table examples

18. What do Timelines provide in pivot tables?

  • Lets you filter specifically on date-related data
  • Previews views of different filters
  • Provides details of all actions performed during a given period
  • Sets the pivot table to an earlier view

19. What is one way to remove a slicer or timeline?

  • Right-click it and select cut
  • Create a new one
  • Press CTRL+Z
  • Remove all filters from pivot table

Leave a Reply