Willow Tips

Tip #2000-2: Displaying color-coded up/down arrows in Excel

Getting those handy color-coded arrows is easy... It's only a three step process.

1. Get the correct field, so a U or D will appear in the cell; for example,
   
   

   =REUTER|IDN!´IBM,TICK:UP/DOWN´

   
   
2. Here's the trick..... convert the U to a positive number (+1) and the D to a negative number (-1), so you can use Excel's own built-in formatting. Don't forget the TRIM function!
   
   

   =IF(TRIM(REUTER|IDN!´IBM,TICK:UP/DOWN´)="U",1,-1)

   
   
3. Format the cell with custom formatting
   -- Go to Format Cells.... in Excel
   -- On the Font tab, select one of the IDN fonts available, such as IDNFONTV
   -- On the Number tab, create a custom format so that positive numbers are a green up-arrow and negative numbers are a red down-arrow. The format you need is:
   
   

   [GREEN]Þ;[RED]þ

Just highlight this format, copy it and paste it into the code box on the Number tab. Want more details on what's happening behind the scenes?

If you've formatted the cell properly, it will now display a green up-arrow for upticks and a red down-arrow for downticks. Download a sample spreadsheet