Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 78
Default Conditional Formatting (Icon sets - 3 Arrows)

I have the following data:

A1 = 5
A2 = 10
A3 = 4
A4 = 4

I would like to use the icon sets (3 arrows) conditional formatting to show
the changes of the value from previous cell.

For example. if A2 A1, A2 will have a up arrow.
If A3 < A2; A3 will have a down arrow.
If A4 = A3; A4 will have a level arrow.
And these go on down in the column.

I set up the condition in the conditional formatting windows. BUT, is there
a faster way to do the conditional for all the rows in the column?

I tried to copy and paste the format down the rows, however it always use
the same A1 value for comparison. I don't want to do that since I want to
compare the previous row value, not the initial value.

Thanks for your help.!!
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Conditional Formatting (Icon sets - 3 Arrows)

Yes, there is a faster way to apply the conditional formatting to all the rows in the column. You can use a formula-based conditional formatting rule that references the cell above the current cell.

Here are the steps to apply the conditional formatting rule:
  1. Select the range of cells that you want to apply the conditional formatting to (in this case, A1:A4).
  2. Go to the Home tab in the ribbon and click on Conditional Formatting New Rule.
  3. In the New Formatting Rule dialog box, select "Use a formula to determine which cells to format".
  4. In the "Format values where this formula is true" field, enter the following formula:

    Code:
    =A2A1
  5. Click on the Format button and select the icon set with 3 arrows that you want to use.
  6. Click OK to close the Format Cells dialog box.
  7. Click OK to close the New Formatting Rule dialog box.

Now the up arrow icon will be applied to any cell in the range where the value is greater than the value in the cell above it.

To apply the down arrow and level arrow icons, you can create additional conditional formatting rules using the following formulas:

For the down arrow:

Code:
=A3<A2
For the level arrow:

Code:
=A4=A3
Make sure to adjust the cell references in the formulas to match the cells in your worksheet.

Once you have created all three conditional formatting rules, they will be applied to the entire range that you selected in step 1. You can test this by entering new values in cells A5, A6, etc. and verifying that the correct arrow icons are applied automatically.


__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Conditional Formatting (Icon sets - 3 Arrows)

Edit your conditional formatting for the first cell, and ensure that
you do not have any absolute cell references ($ symbols) in there.
Then you should be able to apply the CF to the other rows by means of
the Format Painter.

Hope this helps.

Pete

On Jun 16, 11:09*am, Jackie wrote:
I have the following data:

A1 = 5
A2 = 10
A3 = 4
A4 = 4

I would like to use the icon sets (3 arrows) conditional formatting to show
the changes of the value from previous cell.

For example. if A2 A1, A2 will have a up arrow.
If A3 < A2; A3 will have a down arrow.
If A4 = A3; A4 will have a level arrow.
And these go on down in the column.

I set up the condition in the conditional formatting windows. BUT, is there
a faster way to do the conditional for all the rows in the column?

I tried to copy and paste the format down the rows, however it always use
the same A1 value for comparison. I don't want to do that since I want to
compare the previous row value, not the initial value.

Thanks for your help.!!


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 78
Default Conditional Formatting (Icon sets - 3 Arrows)

Thanks for your advice, but I can't seem to make it works.

Whenever I try to set the value in the "edit formatting rule" window.
It will automatically add $ sign to the value... like this. "=$A$1"
if I don't use $ sign, there will be no arrows icon show up in the excel
sheet.

For the type. I choose "Number". It this correct??



"Pete_UK" wrote:

Edit your conditional formatting for the first cell, and ensure that
you do not have any absolute cell references ($ symbols) in there.
Then you should be able to apply the CF to the other rows by means of
the Format Painter.

Hope this helps.

Pete

On Jun 16, 11:09 am, Jackie wrote:
I have the following data:

A1 = 5
A2 = 10
A3 = 4
A4 = 4

I would like to use the icon sets (3 arrows) conditional formatting to show
the changes of the value from previous cell.

For example. if A2 A1, A2 will have a up arrow.
If A3 < A2; A3 will have a down arrow.
If A4 = A3; A4 will have a level arrow.
And these go on down in the column.

I set up the condition in the conditional formatting windows. BUT, is there
a faster way to do the conditional for all the rows in the column?

I tried to copy and paste the format down the rows, however it always use
the same A1 value for comparison. I don't want to do that since I want to
compare the previous row value, not the initial value.

Thanks for your help.!!



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting with Icon sets CC Excel Discussion (Misc queries) 4 April 21st 10 03:34 PM
Conditional Formatting with the icon set Tubthumper Excel Discussion (Misc queries) 1 November 5th 07 11:12 AM
conditional formatting for multiple sets of conditions steve Excel Discussion (Misc queries) 9 August 15th 07 07:00 PM
Basis of Icon Set (conditional formatting) Excel 2007 badeth Excel Discussion (Misc queries) 0 July 12th 07 03:20 AM
Additional Icon Sets Kevin S Excel Discussion (Misc queries) 0 July 3rd 07 03:18 PM


All times are GMT +1. The time now is 10:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"