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 Ratings 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 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.

Go to the MPG PivotTable worksheet. This sheet includes a PivotTable created from the data on the MPG Data worksheet.

Refresh the PivotTable data.

Apply the Pivot Style Medium 1 Quick Style to the PivotTable (the first style in the Medium section).

Display a slicer for the SmartWay field and show only data where the SmartWay value is Elite.

To pay for the new car, you've decided to start a bakery business out of your home kitchen. You have limited capital and capacity, so you need to make wise decisions about which products to bake each day. Go to the Bakery Business worksheet. Cell B1 uses a SUMPRODUCT formula to calculate profit. Carefully review the assumptions and formulas in this worksheet before entering the following Solver parameters:

Find the maximum possible profit (cell B1) by changing the values in cells C7:C10.

The working hours required (B3) must be less than or equal to the number of working hours in the day (D3).

The total cost of ingredients (B4) must be less than or equal to the available capital (D4).

For each item in the range C7:C10, the number of items baked cannot exceed the maximum capacity available per day as defined in cells F7:F10. This restriction requires four separate constraints in the Solver Parameters dialog.

The items in the variable cell range C7:C10 must be whole numbers.

There should be a total of seven constraints.

Run Solver and accept the Solver solution.

Insert Draw Page Layout V E Get Data from Refresh All Data Picture fx Model 8 Home A1 123 + ; X A Madel 4 BMW is BEV Average 7 Average 10 Average 13 FIAT Average 15 Averapo 19 Average 22 Average 25 Electric Average 28 Hybrid Average 31 Hybrid SC Average SE 36 KIA Niro Average 37 KM No FE Average 40 Average p 43 Averag 46 Average 40 Bagne Average 53 Averag SS NISSAN Leaf Average 57 Average 59 TESLA Model S 6okWh Average 6 Average 63 TESLA Model 575kWh Average 65 Average 67 Aug 69 Bo Average 71 Average 73 Average 25 Average 77 Average 79 Average 80 Grand Average 81 82 Criteria 83 Model 84 85 86 Filtered Data 87 88 89 fx Displ Displ Cyl Queries & Connections Properties Edit Links E C Formulas Data Review View [03 Stocks Currencies H D Trans Trans Drive Drive Fuel F Fual Gasoline Cart Region Stad Cart Region Strad > Tell me I Vah Class Vuhdass J 22% 10 10 10 10 10 10 4 3 10 5 $ 3 9 $ 6 5 10 110 10 10 10 10 10 10 10 10 10 10 10 10 Y Sort Filter City MPG B 10 9.0833333 229 337 sall 123 118 55 72 150 38 53 52 46 120 He 224 101 98 202 City MPG 97 92 92 93 9 go 8 39 136 (04:56) Claar Heapply Advanced L Hwy MPG M C=b MPG 106 331 115 103 96 54 122 43 45 46 49 45 45 92 2013 102 107 105 105 10 200 94 95 94 92 90 835 Hwy MPG >35 20 Flash Fill E Data Validation E Text to Remove Duplicates Consolidate Columns N 0 P R 12$ 119 333 107 55 55 135 40 23 40 50 43 105 B 333 Cmb MPG * * * * * * * * * * * * *4 Comb CDa Comb CO S H+ What If Analysis T Group of ungroup v Subtotal U V += 3 Share 2, Solver W Comments Analysis Tools Y Insert Draw Page Layout Data from Picture Refresh All D4 X fx 200 A 1 What is my profit? $ 2 Available capital S 3 Working hours required 4 Total cost of ingredients 5 6 Price (retail) 7 Birthday Cake $ 60.00 8 Cupcakes (dozen) $ 25.00 9 Cookies, Shortbread (dozen) S 18.00 10 Cookies, Lemon Bars (dozen) S 20.00 21 11 12 Total labor hours must be less than or equal to working hours per day 13 Total cost must be less than or equal to capital 14 Number to make of each item cannot be more than the maximum per day possible Car Loan My Car Data Home Get Data 15 16 17 0 18 19 20 20 20 21 22 23 3 24 25 26 27 27 28 29 20 30 31 34 32 34 33 35 34 34 35 22 36 = 37 38 20 39 40 41 42 Ready Formulas [ Queries & Connections Properties Edit Links B 182.75 4.00 32,767 must be less than or equal to o must be less than or equal to Optimal number to make each day 1 S 4 $ OS 5 $ Scenario Summary Accessibility: Investigate Data Review View rez Currencies D Stocks Cost (ingredients) MPG Data > 12 200.00 14.00 8.00 6.00 6.25 Tell me ALA Sort Filter E Labor (hours) 14 Clear Reapply Advanced F Max per day MPG PivotTable 3 1 1 1 Bakery Business 1 4 6 6 + A Flash Fill Data Text to Remove Duplicates Con Columns G H I J K Insert Draw Page Layout V E Get Data from Refresh All Data Picture fx Model 8 Home A1 123 + ; X A Madel 4 BMW is BEV Average 7 Average 10 Average 13 FIAT Average 15 Averapo 19 Average 22 Average 25 Electric Average 28 Hybrid Average 31 Hybrid SC Average SE 36 KIA Niro Average 37 KM No FE Average 40 Average p 43 Averag 46 Average 40 Bagne Average 53 Averag SS NISSAN Leaf Average 57 Average 59 TESLA Model S 6okWh Average 6 Average 63 TESLA Model 575kWh Average 65 Average 67 Aug 69 Bo Average 71 Average 73 Average 25 Average 77 Average 79 Average 80 Grand Average 81 82 Criteria 83 Model 84 85 86 Filtered Data 87 88 89 fx Displ Displ Cyl Queries & Connections Properties Edit Links E C Formulas Data Review View [03 Stocks Currencies H D Trans Trans Drive Drive Fuel F Fual Gasoline Cart Region Stad Cart Region Strad > Tell me I Vah Class Vuhdass J 22% 10 10 10 10 10 10 4 3 10 5 $ 3 9 $ 6 5 10 110 10 10 10 10 10 10 10 10 10 10 10 10 Y Sort Filter City MPG B 10 9.0833333 229 337 sall 123 118 55 72 150 38 53 52 46 120 He 224 101 98 202 City MPG 97 92 92 93 9 go 8 39 136 (04:56) Claar Heapply Advanced L Hwy MPG M C=b MPG 106 331 115 103 96 54 122 43 45 46 49 45 45 92 2013 102 107 105 105 10 200 94 95 94 92 90 835 Hwy MPG >35 20 Flash Fill E Data Validation E Text to Remove Duplicates Consolidate Columns N 0 P R 12$ 119 333 107 55 55 135 40 23 40 50 43 105 B 333 Cmb MPG * * * * * * * * * * * * *4 Comb CDa Comb CO S H+ What If Analysis T Group of ungroup v Subtotal U V += 3 Share 2, Solver W Comments Analysis Tools Y Insert Draw Page Layout Data from Picture Refresh All D4 X fx 200 A 1 What is my profit? $ 2 Available capital S 3 Working hours required 4 Total cost of ingredients 5 6 Price (retail) 7 Birthday Cake $ 60.00 8 Cupcakes (dozen) $ 25.00 9 Cookies, Shortbread (dozen) S 18.00 10 Cookies, Lemon Bars (dozen) S 20.00 21 11 12 Total labor hours must be less than or equal to working hours per day 13 Total cost must be less than or equal to capital 14 Number to make of each item cannot be more than the maximum per day possible Car Loan My Car Data Home Get Data 15 16 17 0 18 19 20 20 20 21 22 23 3 24 25 26 27 27 28 29 20 30 31 34 32 34 33 35 34 34 35 22 36 = 37 38 20 39 40 41 42 Ready Formulas [ Queries & Connections Properties Edit Links B 182.75 4.00 32,767 must be less than or equal to o must be less than or equal to Optimal number to make each day 1 S 4 $ OS 5 $ Scenario Summary Accessibility: Investigate Data Review View rez Currencies D Stocks Cost (ingredients) MPG Data > 12 200.00 14.00 8.00 6.00 6.25 Tell me ALA Sort Filter E Labor (hours) 14 Clear Reapply Advanced F Max per day MPG PivotTable 3 1 1 1 Bakery Business 1 4 6 6 + A Flash Fill Data Text to Remove Duplicates Con Columns G H I J K