ITA-40120A-04-Practice1

The following steps are more detailed and therefore, in order to gain practice, use the high-level Instructions table in the sheet to perform the exercise rather than following these steps.

Follow these steps for the first task:

Open the ITA-40120A-04-Practice1 workbook.

Click the Skating sheet.

Select the cell range A1:L9, right-click, and click Copy.

Click the cell A11, right-click, point to Paste Special, click Paste Special. In the Paste Special dialog box, click Transpose, and then click OK.

Select the cell range A1:L10, right-click, click Delete, and in the dialog box, click Shift cells up, and then click OK.

Select the cell range I1:I12, and on the HOME tab, on the ribbon, click Conditional Formatting, point to Icon Sets, and then click More Rules.

In the New Format Rule dialog box, under Select a Rule Type, ensure Format all cells based on their values is selected.

In the Icon Style list, click 3 Traffic Lights (Unrimmed), and in the Type lists, select Number, and set the green light to appear when the value is greater than or equal to 1000, and the yellow light to appear when the value is greater than or equal to 0, and then click OK.

Select the cell range A1:I12, and on the ribbon, in the Editing group, click Sort & Filter, and then click Custom Sort.

In the Sort dialog box, in the Sort by drop-down list, click Profit, in the Sort On drop-down list, click Cell Icon, and in the Order drop-down list, click the red color, and in the last drop-down list, ensure it displays On Top, and then click OK.

Click the Ex1 sheet.

Select the cell range A2:A21, and on the ribbon, click Conditional Formatting, and then click New Rule.

In the New Formatting Rule dialog box, under Select a Rule Type, click Format only cells that contain.

In the Format only cells with drop-down lists, in the first list, ensure Cell Value is displayed, in the second list, click equal to, and in the third box, type ="Excel Expert 1"

Click Format and in the Format Cells dialog box, click the Fill tab, click the yellow color, click OK, and then click OK.

To edit the rule that you created, on the ribbon, click Conditional Formatting, and then click Manage Rules.

In the Conditional Formatting Rules Manager dialog box, click Edit Rule, under Edit the Rule Description, in the text box, change ="Excel Expert 1" to ="Word Expert 1", click OK, and then click OK.

Click the Ex2 sheet.

Select the cell range A2:H21, and on the ribbon, click Conditional Formatting, and then click New Rule.

In the New Formatting Rule dialog box, under Select a Rule Type, click Use a formula to determine which cells to format.

In the Format values where this formula is true box, type =$A2=$J$2

Click Format and in the Format Cells dialog box, click the Fill tab, click the yellow color, click OK, and then click OK.

Select a different value in cell J2, for example, Excel Expert 1, and see the differences the highlighted rows.

JUS UPS YAK ROC COS Women Delete Date Jan13 San132/132an/14 3ian/4 lan/ 4an/14 /an/4 5/an46an//14 I Instructions Arm bands Cargo pants Hoody Tanitops Flexficted Snapback Baseball jacket Anorak Parla Sneakers Sneakers Brand Tubetese Carhart Nike Must Rhyse Yums Yums Urban Classics Dance Yakura Rocawear DC British Knights Code CAR YUM Cel range ALD Product grout Accessories Pants outerwear outerwear Caps Packers lackers Shoes Shoes wome women women Men cells (Cell range A1L10) - (Shift cels up arnover 52080.00 $2..00 $ 2,99000 $ ,74000 $316.00 $ 3,680.00 $ 1920.00 $ 3732.00 5 162000 52767.00 $ 3,548.00 columns JHL Costs 52187.0 $ 92.00s 2,52100 $ ass.00 $ 1,364.00 $ 2,22.00 S 441.00 $ 1,346.00 $ 1,0100 $ 922.00 $ 1,867.00 conditional formatting 13 traffic lights without barder (Call range in:/12) Profits 5 (107.0os L70100's 369.00 |s ,355.00 $ (548.00 $ 95700 1,46900 s 2,386.00 $ 600.00 $ 1845.00 |s L,681.00 Display the green light if value is greater than or equal to 1000 Display the yellow light it value is greater than or equal to 0 Cell range A1112 on cell lcon Red color On Top futle PI Sara Fleafard Yunsure Paris Carpe favts Carhat CAR NIK rats werenato Guerrer ran 2:.0 $ aid of Lite 200 at ss.co. ses mo Gar14 Baseball batburban dess une Paren to Lot 163.co Eira Pain Aurora Poncets MoS .0 1 00 as a co lar/1A States D D es Men 27STLOS 2200 SL BLS to Maya Sarales Dita Eagl es evens 35010 100 SLOWL 60 . JUS UPS YAK ROC COS Women Delete Date Jan13 San132/132an/14 3ian/4 lan/ 4an/14 /an/4 5/an46an//14 I Instructions Arm bands Cargo pants Hoody Tanitops Flexficted Snapback Baseball jacket Anorak Parla Sneakers Sneakers Brand Tubetese Carhart Nike Must Rhyse Yums Yums Urban Classics Dance Yakura Rocawear DC British Knights Code CAR YUM Cel range ALD Product grout Accessories Pants outerwear outerwear Caps Packers lackers Shoes Shoes wome women women Men cells (Cell range A1L10) - (Shift cels up arnover 52080.00 $2..00 $ 2,99000 $ ,74000 $316.00 $ 3,680.00 $ 1920.00 $ 3732.00 5 162000 52767.00 $ 3,548.00 columns JHL Costs 52187.0 $ 92.00s 2,52100 $ ass.00 $ 1,364.00 $ 2,22.00 S 441.00 $ 1,346.00 $ 1,0100 $ 922.00 $ 1,867.00 conditional formatting 13 traffic lights without barder (Call range in:/12) Profits 5 (107.0os L70100's 369.00 |s ,355.00 $ (548.00 $ 95700 1,46900 s 2,386.00 $ 600.00 $ 1845.00 |s L,681.00 Display the green light if value is greater than or equal to 1000 Display the yellow light it value is greater than or equal to 0 Cell range A1112 on cell lcon Red color On Top futle PI Sara Fleafard Yunsure Paris Carpe favts Carhat CAR NIK rats werenato Guerrer ran 2:.0 $ aid of Lite 200 at ss.co. ses mo Gar14 Baseball batburban dess une Paren to Lot 163.co Eira Pain Aurora Poncets MoS .0 1 00 as a co lar/1A States D D es Men 27STLOS 2200 SL BLS to Maya Sarales Dita Eagl es evens 35010 100 SLOWL 60