Question

Please show all steps and formulas. i'm using office 360.

L24 А B с D E F G H 1 K M N 0 P 1 2 Trend Trend Trend 3 4 Status 56 5 6 7 Area Europe S. America Far East Australia N. America 2005 Q4 $32 $26 $49 $22 $11 Q1 $33 $29 $62 $64 $67 Status 33 29 62 64 67 Q2 $81 $30 $70 $78 $77 2006 Gross Profits (in Millions) Status Trend Q3 Status 81 $72 72 30 $42 42 70 $43 43 78 $38 38 77 $35 35 Q4 $56 $69 $63 $45 $76 69 63 45 76 8 9 Required: 1. For the Status Columns, format the cells with an Icon Set with a Green Check Mark for values greater than or equal to 50, a Yellow Exclamation mark for values less than 50 but greater than or equal to 45, and a Red X for all others. 10 11 12 13 14 15 16 2. Populate the cells in the Trend Columns with a formula that returns the difference between the most recent quarter and the quarter preceding it. 3. For the Trend Columns, format the cells with an Icon Set that returns a Green Arrow oriented Up for values greater than or equal to 5, a Yellow Arrow oriented to the Right for values less than 5 and greater than or equal to 0, and a Red Arrow oriented Down for all others.

Answer 1

Formula and Icon set for Excel File:

Please give a thumbs up if you find this answer useful!

A. Setting up Icon set in "Status" Column:

Step 1 : Select data in status column where you need to apply conditional formatting.

Step 2: Go to Home Tab > Conditional Formatting > New Rule

= Menu Home Insert Page Layout Formulas Data Review View Tools Q Click to find com... . X Cut Calibri - 11 = General Σ FP - Α" Α A QE BIU TE Paste L Copy Format Painter IT Merge and Center Wrap Text - % 000 000 Conditional Format as Formatting Table AutoSum" AutoFilter Sort Format Rows an Columns E4 ® fx 33 A B C С D F H K L M N O P Q1 Trend Trend Status Trend $ 1 2 3 4 5 6 7 8 2005 04 32 $ 26 $ 49 $ 22 $ 11 $ Area Europe $ S. America $ Far East $ Australia $ N. America $ 33 29 62 64 67 Status 33 29 62 64 67 56 69 Q2 81 30 70 $ $ $ $ $ 2006 Gross Profits (in Millions) Status Trend Q3 Status 81 $ 72 72 30 $ 42 42 70 $ 43 43 78 $ 38 38 77 $ 35 35 Q4 56 69 63 63 $ $ $ $ 45 45 78 77 76 76 OH 1 -2 3

Step 3: Select Rule type as "Format all cells based on thier values" > Under Format Style, select Icon sets.

Formulas Data Review View Tools Click to find com.. Menu v DO PODE Home Insert Page Layout * Cut Calibri - 11 Α' Α' Paste" Copy Format BIURAS Painter = General T Merge and Wrap Center Text - % 0 Σ ? AutoSum AutoFilter Sort Format E Conditional Format as Formatting Table & Highlight Cells Rules E Top/Bottom Rules E4 @fx 33 A B С D E F G H N Data Bars O Q1 Trend Q2 Trend Status 56 33 56 EB Color Scales Icon Sets New Rule. EB Clear Rules E Manage Rules... 2005 Area Q4 Europe $ 32 $ S. America $ 26 $ Far East $ 49 $ Australia $ 22 $ N. America s 11 S Status 33 291 62 1 2. 3 4 5 6 7 8 9 10 11 29 69 2006 Gross Profits (in Million Status Trend Q3 S 81 $ 72 30 S 42 $ 43 78 $ 38 77 S 35 69 S $ $ $ S 81 30 70 78 77 62 64 70 63 45 64 671 38 35 $ $ 63 45 76 67 76

Step 4: Under Icon style, select Icon set with cross, exlaimation mark and green tick.

2 CI Home Page Layout Formulas Data Review View Tools Q Click to find com... Menu BOP Home Insert X Cut Calibri - 11 Α' Α' Paste Copy Format BIUDA Painter General 000 - % Merge and Wrap Center Text 000 000 Conditional Format as Formatting* Table E4 @fx 33 c С А B D Sta New Formatting Rule Select a Rule Type: Format all cells based on their values Format only cells that contain Format only top or bottom ranked values Format only values that are above or below average Format only unique or duplicate values Use a formula to determine which cells to format Edit the Rule Description: 1 2 3 4 5 6 7 8 9 Q 2005 Area Q4 Europe $ 32 $ S. Americas 26 $ Far East $ 49 $ Australia $ 22 $ N. Americas 11 $ Q1 33 29 62 64 67 $ $ $ $ $ Format all cells based on their values: Format Style 2-Color Scale 2-Color Scale 3-Color Scale Type Data Bar Icon Sets Value: Color: 10 Maximum Highest Value (Highest Values 11 12 Preview: 13 14 OK Cancel 15 16 17 18

Menu GB250 Home Insert Page Layout Formulas Data Review View Tools Q Click to find com... General * Cut 24 PasteCopy Format Painter Calibri - 11 Α' Α' BI USA Σ Merge and Wrap Center Text - - % 100 000 AutoSum" Conditional Format as Formatting Table E4 - @fx 33 S New Formatting Rule х 4 A B С D M 1 2 d 3 4 5 6 7 8 8 Sta Select a Rule Type: Format all cells based on their values Format only cells that contain Format only top or bottom ranked values Sta Format only values that are above or below average Format only unique or duplicate values Use a formula to determine which cells to format Edit the Rule Description: 2005 Area Q4 Europe $ 32 $ S. America s 26 $ Far East $ 49 $ Australia $ 22 $ N. America s 11 $ Q1 33 29 62 64 67 $ $ $ $ $ Q4 56 69 63 45 Format all cells based on their values: Format Style: Icon Sets Reverse Icon Order 76 9 Icon Style: Show Icon only 10 11 Display each ico! Icon Value 12 67 Type Percent Percent 13 O 33 14 15 16 17 OK Cancel 18

Step 5 : Under Type, Select Number.

III Menu DOP@@ 2 <I Home Home Insert Page Layout Formulas Data Review View Tools Q Click to find com... Calibri 11 General * Cut Paste Copy Format Painter - Α Α Ae Σ BIU % 000.00 Merge and Wrap Center Conditional Format as Formatting Table AutoSu Text E4 @fx 33 A B С D M 1 2 3 New Formatting Rule Select a Rule Type: Format all cells based on their values Format only cells that contain Format only top or bottom ranked values Sta Format only values that are above or below average Format only unique or duplicate values Use a formula to determine which cells to format Edit the Rule Description: d 2005 Area Q4 Europe $ 32 $ S. America $ 26 $ Far East $ 49 $ Australia $ 22 $ N. America $ 11 $ 5 6 7 8 Qi 33 29 62 64 $ $ $ $ $ Q4 56 69 63 45 76 67 9 Icon 10 11 12 13 14 15 16 17 18 Format all cells based on their values: Format Style: Icon Sets Reverse Icon Order Icon Style: Show Icon only Display each icon according to these rules: Value When value is when < 0 and x when <0 Type Number Number Percent Formula Percentile OK Cancel 19

Step 6: Put values as per condition in value feild shown as in snap bellow:

Step: 7 Press OK.

= Menu Home Insert Page Layout Formulas Data Review View Tools Q Click to find com... Cut Calibri 11 T - At A A А. LO General - % % 000 Σ Paste U Copy Format Painter BI UH- Merge and Center Wrap Text .00 .00 +0 Conditional Format as Formatting Table Autosur S Edit Formatting Rule E4 ®. fx 33 B С D E M 2 1 Select a Rule Type: Format all cells based on their values ► Format only cells that contain ► Format only top or bottom ranked values Format only values that are above or below average Format only unique or duplicate values Use a formula to determine which cells to format 2. 2005 Tr Edit the Rule Description: Q4 32 $ 26 $ 49 $ 22 $ 11 $ Q1 Status 33 X 33 29 x 29 62 62 64 64 67 l 67 3 Area 4 Europe $ 5 S. America $ 6 Far East $ 7 Australia $ 8 N. America $ 9 LO 1 2 3 Q4 Sta 565 696 636 45 4 767 Format all cells based on their values: Format Style: Icon Sets Reverse Icon Order IT Icon Style: X! - Show Icon only Display each icon according to these rules: Icon Value Type ✓ When value is 50 Number ! >= when < 50 and 45 Number x y when < 45 OK Cancel 4 -5 6 7 -8

For rest of status colums you can repeat same step OR Simply copy formatting from this column and apply to rest of Status columns.

B. Formulas for Trend Column:

Here is the simple formula with same reference as shown in original sheet. These formula can be used straight away as referencing is same as given in question.

Home Insert Page Layout Formulas Data Review View Tools Q Click to find com... = Menu v BOPGO Cut - 11 Α' Α Paste" Copy Format BIU. BIU...A Painter 29 Calibri == 9 Merge and Wrap Center Text General - % Σ 8 000 000 000 E Conditional Formatas Formatting Table AutoSum" AutoFilter Sort 04 @fx =+M4-14 С DE A B F G H J K L M N P Р 1 Q1 Status Status 2 3 4 5 6 2005 Area Q4 Europe 32 S. America 26 Far East 49 Australia 22 N. America 11 33 29 62 64 67 Status X 33 x 29 62 64 67 Trend Q2 =+D4-C4 81 =+D5-C530 =+D6-C670 =+D7-C7 78 =+D8-C8 77 2006 Gross Profits (in Millions) Status Trend Q3 81 =+G4-04 72 72 x 30 =+G5-05 42 x 42 70 =+G6-D6 43 X 43 78 =+G7-D7 38 x 38 77 =+G8-D8 35 X 35 Trend Q4 =+J4-64 56 =+J5-G5 69 =+J6-G6 63 =+17-G745 =+J8-68 76 56 69 63 45 76 Trend =+M4-14 =+M5-J5 =+M6-J6 =+M7-J7 =+M8-18 7 8 9 10 11 12

C. Setting up Icon set for trend column:

Steps for setting up Icon set for this column are same as given in answer Part A, except for step 4 and 6.

Please follow following steps:

Step: 1 , 2 and 3 : Similar as mentioned in Part A.

Step: 4 Select Icon style as set of Arrow of directions in red, yello and green, as given in this snap.

= Menu DO POR Home Home Insert Page Layout Formulas Data Review View Tools Q Click to find com.. Calibri 11 Α' Α' = = General * Cut Paste Copy Format Painter BIU % 000 3 Merge and Wrap Center Text Conditional Formata Formatting Table F4 @fx =+04-04 х B С D K 3 New Formatting Rule Select a Rule Type: Format all cells based on their values Format only cells that contain Format only top or bottom ranked values Format only values that are above or below average Format only unique or duplicate values Use a formula to determine which cells to format Edit the Rule Description: Q1 St 2 A 1 2 3 4 5 6 7 Stato 2005 Area Q4 Europe $ 32 $ S. Americas 26 $ Far East $ 49 $ Australia $ 22 $ N. Americas 11 $ 33 X 29 X 62 72 42 43 38 35 64 67 00 9 10 Format all cells based on their values: Format Style: Icon Sets Reverse Icon Order Icon Style: Show Icon only Display each icon according to these rules: Icon Value When value is 0 when < 0 and 33 11 12 13 14 15 16 17 18 Type Number Number Percent Formula Percentile when <33 OK Cancel

Step 5 : Same as given in Part A.

Step 6: Put values as per condition as given in snap.

= Menu ✓ Home Insert Page Layout Formulas Data Review View Tools Q Click to find com... = = X Cut Calibri - 11 At BI UE A EL General L Σ 8 Paste - % 000 U Copy Format Painter 4.O.00 .00 → Wrap Text Merge and Center AutoSumAutoFilter Sor Conditional Format as Formatting Table х Edit Formatting Rule F4 ® fx =+D4-C4 B С D E M N 0 1 Select a Rule Type: Format all cells based on their values Format only cells that contain Format only top or bottom ranked values Format only values that are above or below average Format only unique or duplicate values Use a formula to determine which cells to format 2 2005 3 Area Tr > Edit the Rule Description: Q4 32 $ 26 $ 49 $ 22 $ 11 $ 4 Europe $ 5 S. America $ 6 Far East $ 7 Australia $ 8 N. America $ 9 Q1 Status 33 X 33 29 X 29 62 62 64 64 67 67 Q4 Status 5656 69 69 63 63 45 45 76 76 Trend -16 27 20 7 Format all cells based on their values: Format Style: Icon Sets Reverse Icon Order 41 Icon Style: Show Icon only 10 Display each icon according to these rules: Icon Value Type N When value is 5 Number 11 12 13 when < 5 and 0 Number 14 when < 0 OK Cancel 15 16 17 18

Step 7: Press OK.

For rest of trend colums you can repeat same step OR Simply copy formatting from this column and apply to rest of trend columns.