Managing and Analyzing Data in Excel

You do not have the button necessary to perform a Filter by Selection. Which steps can you take to perform a Filter by Selection?

Add Auto Filter from Customize Quick Access Toolbar, select any Warehouse cell, and then select the new Auto Filter button.

How do functions such as COUNTIFS and AVERAGEIF differ from nonplural functions such as COUNTIF and AVERAGE IF?

COUNTIFS and AVERAGEIFS allow you to use multiple criteria.

Your Customer table contains names, sales regions, and other data. To the right, you have columns with calculations you performed for certain averages. Before you sort by region, what must you do?

Ensure there is at least one empty column to the right of your Customer data separating it from the calculations columns.

Because of the limitations of Excel standard filtering, what is the only filtering you can do?

Identify data predictively with Tomorrow, Next Week, Next Month, and Next Quarter.

The first row of your table contains column names in bold text. If you want to see the column names as you navigate through the table, how can you do this?

If you use your mouse wheel to scroll, the column names will change to column headings that will remain at the top as you navigate.

You are starting a mentoring program. You want to identify all employees with 10 or more years tenure, and employees with less than 1 year. Which connector in Custom AutoFilter will you use?

OR

Your sales data has some duplicate customer information. Which entries will you need in the Advanced Filter dialog box to get a list of data without these duplicates?

Select the box Copy to another location, verify the List range, enter a Copy to location, and check Unique records only.

Your spreadsheet shows all employees across all departments. You want to see the top 10 compensation amounts among your warehouse employees. How will you do this?

Sort the Compensation column ascending to descending, and you will see the top 10.

You use Advanced Filter for day of the week Friday. When you select OK, nothing appears. Why is this?

The advanced filter will not return anything until you remove the column heading Date in Row 1.

Using Excel's advanced filter capability, you want to filter employees who have at least 15 years tenure, and all employees making over $100,000. How will you set up the criteria range?

Use >15 in the first empty row under the column heading Years, and >100000 in an empty row under the column heading Salary.

Column H of your Table lists employees with health benefits. Employees who elected not to have health benefits have a blank cell. How can you quickly find the number of employees without health benefits?

Use the formula =COUNTBLANK(Table1[Benefits])

You want to buy cupcakes for employees on the anniversary of their hiring day. How can you most easily find this information in your spreadsheet?

You can filter by the employee records by hire month.

You see a funnel-shaped icon to the right of the column heading Status on your warehouse employee spreadsheet. What does this mean?

You have a filter in place in the Status column.

The Part# your company uses contains a code number as the second digit, identifying whether it has a warranty. You want a list of all part numbers with the 1 (warranty) code. Which filter do you use?

You use ?1*

You have a large spreadsheet with a number of bad cell values. Why would you use the aggregate function?

to tabulate the good data

You want to sort employees by department, full- or part-time, and then years of service. Which tool will let you most easily do this?

use the Sort button on the Data tab

You want to sort employees in Warehouse 1 by Full-Time, Hourly, and Contractors. You may also want to sort your other warehouses this way in the future. How should you perform this type of sort?

with the Custom Edit option in Options under the File menu

Before selecting A to Z on the Data tab, how do you set up Excel to sort data in random order?

Enter the formula =rand( in the cell to the right of your first row of data, in an empty column select Enter, and copy it down the column

Your spreadsheet has entries for Employee Name and Department, Hire Data, and Years of Service. What, if anything, will Excel do to help you filter your data?

Excel will recognize text, numeric, and date fields, and provide different filter options for each.

You used the formula =IF(AND (B2:K2=B3:K3)"dup","unique") to check for duplicates in a large spreadsheet, but only one was identified. You know there are many more. What happened?

You did not extend the formula all the way down to the last row.

You have a spreadsheet of employees sorted by employee last name in Column A. You want to sort A to Z by department, which is in Column D. What will you do first?

select any cell in column D