Kitz ADSL Broadband Information
adsl spacer  
Support this site
Home Broadband ISPs Tech Routers Wiki Forum
 
     
   Compare ISP   Rate your ISP
   Glossary   Glossary
 
Please login or register.

Login with username, password and session length
Advanced search  

News:

Author Topic: How to Automate coloured text in a Excel Spreadsheet  (Read 17294 times)

oldfogy

  • Helpful
  • Kitizen
  • *
  • Posts: 3568
  • If it ain't broke....... I'll soon fix it.
How to Automate coloured text in a Excel Spreadsheet
« on: September 26, 2008, 08:15:18 PM »

How to Automate coloured text in a Excel Spreadsheet

I had a Excel spreadsheet that I wanted the result of "K22" to show as either "Red or Blue" depending upon the result of another cell.

Basically:
K19 = a Fixed figure of £70.88
K15 = the total of other columns. (Expense's)
K22 = the sum of (K15 minus K19) (Income)

So, what I wanted was:
If K22, is less than K19, then the result to be shown in BLUE, or, the result to be shown in RED if more than K19
Red for "Overspent" and Blue for "Under-spent"

Conditional formating is what was required.
"Conditional Formating" can be found in the ""Format" menu of Excel from the menu bar.

On the format menu from the first drop-down box select "Formula Is"  then type =K22<K19
Then choose the "Format" button because you need to chose a font (Regular, Bold etc), and font colour so now chose "Blue"
Important don't forget to include the " = " (equals sign) at the start of the formula's (without the quotes)

Now click "add", again first choosing the "Formula Is" option, now put in the second condition =K22>k19
Followed again by the "Format" button, because you now need to chose a second font and font colour etc, so now chose "Red"



However, there are also a few other choices you can use from the drop-down menu's if you are not sure of what to type in the box, none of which are difficult once you know what you needed to use to begin with and how to get to "Conditional Formating" and not forgetting the basics.

However, I found by clicking the small coloured box on the right of the text typing box that I could then select the cell/s with the mouse instead of typing them. (Which for me worked out better and easier the first time)

Obviously in your worksheet you will be using different cell numbers, so you need to adjust this accordingly.

Just as a matter of interest:
The > sign means "More than"
The < sign means "Less than"

Original solution provided by:
Exo & DaveS from:
http://forum.kitz.co.uk/index.php?topic=1436.0
Logged

tonyappuk

  • Reg Member
  • ***
  • Posts: 589
Re: How to Automate coloured text in a Excel Spreadsheet
« Reply #1 on: September 27, 2008, 02:25:31 PM »

Could you not also format the cells which hold the result so that negative numbers show as red? (right click on cell, Format Cells, Number, Custom and take your pickof the offered formats) My own spreadsheet of income and expenses does this. I admit I don't see any results in blue but the rest of the spreadsheet is in normal black if positive. Or have I missed the point?!!
Tony
Logged

oldfogy

  • Helpful
  • Kitizen
  • *
  • Posts: 3568
  • If it ain't broke....... I'll soon fix it.
Re: How to Automate coloured text in a Excel Spreadsheet
« Reply #2 on: September 27, 2008, 08:59:18 PM »


Or have I missed the point?!!
Tony
Only slightly

But what you also say is correct.
However yours only works on the results of one column being totalled.

IE:
If your column total = £123.00 (profit) it would = Black
However, if the column total was a minus (loss) then yes this would = Red

Whereas the original example works on being able to determine the values from multiple independent columns.

Hope this helps to clarify things.
Logged

tonyappuk

  • Reg Member
  • ***
  • Posts: 589
Re: How to Automate coloured text in a Excel Spreadsheet
« Reply #3 on: September 27, 2008, 09:21:27 PM »

I think it's a question of horses for courses. My spreadsheet has totals, subtotals and averages of all my income and expenditure month by month on a yearly basis. All the cells with numbers in them are formatted as I suggested so any negative totals/balances are shown in red. This suits me and of course I'm used to it now, but your method could be used just as well. As in all things computer, there's more than one way to do it and the result is what matters.
Tony
Logged

oldfogy

  • Helpful
  • Kitizen
  • *
  • Posts: 3568
  • If it ain't broke....... I'll soon fix it.
Re: How to Automate coloured text in a Excel Spreadsheet
« Reply #4 on: September 27, 2008, 09:42:54 PM »

I know I mentioned "Profit and Loss" earlier, but this actually has nothing to do with what I needed mine to do.
My two main columns are both running totals of my bandwidth UL and DL totals on a hourly basis, therefore there are no minus figures to be taken into account in the totals.

I just wanted my total/s to change colour when it went over a pre-determined set figure for any given portion of the day, as it then made it easier to spot.
Logged

tonyappuk

  • Reg Member
  • ***
  • Posts: 589
Re: How to Automate coloured text in a Excel Spreadsheet
« Reply #5 on: September 27, 2008, 09:54:13 PM »

I understand.
Tony
Logged
 

anything