conditional formatting in attached file not working in excel 2007

Posted on 2014-01-06
Last Modified: 2014-01-07
Hi Experts

I cannot get the trending arrows to show in excel 2007...the attached example is excel table (see tab 5 attachment) goes across in my workbook, as opposed to downwards as per tab 5.
Question by:route217
  • 2
  • 2
  • 2
LVL 22

Expert Comment

ID: 39759892
I have Excel 2007 and the formatting seems to be working. Are you not seeing the arrows? Could you provide your 2007 workbook?


Author Comment

ID: 39759915
Hi expert

yes not seeing the arrows and...cannot up load due to restriction. ..sorry.
LVL 12

Expert Comment

by:Harry Lee
ID: 39759916

If you don't see the arrows in Excel 2007, your problem is absolutely not conditional formatting, since the trending arrows are shown using In Formula Concatenate by using &.

All your conditional format do in Tab 5 is to give the text color Red, Blue, or Black.

I'm opening your file with Excel 2003, 2007, 2010, and 2013, and I don't have any problem seeing the Arrows.

Can you explain your problem a little more?
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.


Author Comment

ID: 39759943
Hi experts

I have tried to recreate the trending  arrows using new worksheet (lets ignore the one I uploaded) this is where I cannot see the trending arrows...when i open the attachment its fine. ...the problem is when I try to recreate in a new worksheet....

and apologies if the question was poorly worded.
LVL 22

Accepted Solution

Flyster earned 250 total points
ID: 39760067
See attached file. It uses the same formula as the one you provided, I just modified the cell reference:

=ROUND(A2,0) & " " &  IF(A1<A2,"¿", IF(A1=A2,"¿","¿"))

The first part, =ROUND(A2,0) , just rounds the number in the reference cell. The second part,
 IF(A1<A2,"¿", IF(A1=A2,"¿","¿")), is an IF Statement that selects which symbol to show. The symbols appear through the formula, not conditional formatting. The formatting comes with the font color being changed depending on the cell value change.
LVL 12

Assisted Solution

by:Harry Lee
Harry Lee earned 250 total points
ID: 39760575

As Flyster says, it's about the formula that you have issue with.

You have to first understand the formula.

Please be aware that EE is not supporting the Triangle shapes and Round Dot. I have turn them into text so that you don't see the ¿ like in Flyster's post.
The & inside the formula is to help you combining the results of multiple section of a formula, and convert the whole result into a Text String.

In your formula, =ROUND(B5,0) & " " &  IF(B4<B5,"Up Pointing Triangle", IF(B4=B5,"Round Dot","Down Pointing Triangle")), the sections are
" "
IF(B4<B5,"Up Pointing Triangle", IF(B4=B5,"Round Dot","Down Pointing Triangle"))

While B5 = 549, the first section =Round(B5,0) will return 549. The second section will return " ", A Space.
Then the third section will compare B4 and B5. If B4 is smaller then B5, it will return a Up Pointing Triangle. If B4 = B5, it will return a Round Dot. Finally if B4 is not smaller than B5, and B4 is not equal to B5 (Which means B4 is lager than B5, then it will return a Down Pointing Triangle.

In your sample, B4 is 566, and B5 is 549, means B4 is larger then B5. The 3rd section will return a Down Pointing Triangle.

Combining the result of the 3 sections it will give you "549" & " " & "Down Pointing Triangle".

Combining the use of conditional formatting. If B4 is larger than B5, it will turn the text into Red, It will give you a Red Color result of 549 Down Point Triangle.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question