On the My Car Data worksheet, use Advanced Filter to copy a subset of data to another
Fantastic news! We've Found the answer you've been seeking!
Question:
On the My Car Data worksheet, use Advanced Filter to copy a subset of data to another location on this worksheet.
A. Copy cells a1:p1 and paste them below the data set in row 52.
B. in cell A51 type: Criteria
Then
- Apply the Accent 4 cell style to cell A51.
- Set up the criteria in the row(s) below row 52. Find cars where the value in the Fuel column is Gasoline and the value in the Hwy MPG column is greater than 35.
- In cell A55, type: Filtered Data
- Apply the Accent 4 cell style to cell A55.
- Autofit column A.
- Use Advanced Filter to find the rows that meet the criteria and copy the filtered data to another location beginning in cell A56.
- Sixteen rows should have been copied from the main data set to cells A57:P72.
- Format the filtered data set (cells A56:P72) as a table. Use Table Style Light 1 (the first table style in the gallery). Be careful not to include row 55 in the table data range.
- Insert a new column to the left of the Greenhouse Gas Score column. Hint: Be sure to add a column to the worksheet, not just the table.
- Copy the values from cells M57:M72 to N57:N72.
- Type this column label in cell N56: Cmb MPG Icon
- Apply a new custom conditional formatting rule to cells N57:N72 to show the 4 Rating icon set. Show the icon only.
- Continue working with the filtered data that has the conditional formatting applied. Sort the table first alphabetically by the values in the Model column and then by icon in the Cmb MPG Icon column so the Signal Meter With Four Filled Bars icon appears at the top.
- Go back to the main data set at the top of the My Car Data worksheet and add subtotals for each change in the model to calculate the average for the following: Air Pollution Score, City MPG, Hwy MPG, and CMB MPG.
- Collapse the data to show just the total rows.
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date: