excel cell formating

[lilkiduno]

Okay So i have been working on an excel spreadsheet for a good part of my day ~3 hours. I believe I have everything pretty much as I want/need it. It's nothing fancy by any needs for keeping track of bills. But I am trying to fromat my percent of change to be red if positive and black if negitive.

I for some reason Am having a hard time and can't seem to figure it out.

Any help would be greatly appericated.
Thanks,
lilkiduno

 

[Beertintedgoggles]

What you're looking for is called conditional formatting. Highlight the cells you want, click on the Format header in the top toolbar (it'll be in a different place for Office 2010 but you're looking for the same thing). From there you can enter your condition values and set the formatting to do to the cell what you want when the condition is met. Additionally you can set multiple conditions for the same cells for example red when the value is below zero and black when the value is positive. You can have the whole cell change colors or just the text.

 

[Completely Bonkers]

You dont need conditional formatting (in your simple case)

1./ Highlight the cell you want to format.
2./ Format/Cells... and choose category "custom"
3./ In the Type: box enter: #,##0.00;[Red]-#,##0.00
4./ For percent format use 0.00%;[Red]-0.00%

Done.

 

[Beertintedgoggles]

You realize that's a more roundabout way of "conditionally formatting" the cells than using Conditional Format?

Of course if it works, knowing two ways to do it doesn't hurt.

 

[Completely Bonkers]

You realise that using the "conditional format" script on each cell will take up a lot more memory, and is a lot slower, than the simple cell format? It is also less robust to file conversion to other spreadsheet formats like OpenOffice etc.

One might not care if the condition is used just a handful of times. But in a spreadsheet with (potentially) tens of thousands of cells it can make a significant difference.

lilkiduno's spreadsheet appears simple and small. Both ways will work for him. However, I believe the most elegant solution is to follow the simplest and most efficient path. At all times. I'm against bloat and unnecessary complexity. It doesn't scale well.

 

[Beertintedgoggles]

Well I will conceed but only slightly. I started with a spreadsheet of 80,000 entries made by the rand function to be between -5 and 3 (picked for no good reason) then copied and pasted all as values and deleted all the function cells. Essentially left with 50 rows of 1600 entries of values between -5 and 3 and saved as the template. I tried both ways and did see the conditional format took up more memory... the resulting file was 1.44 MB on disk compared to 1.42 MB using the custom format. It doesn't appear to be much more memory intensive. Also, it's much easier for a regular user to understand what the heck is going on using conditional formatting.

Edit: Just tried it with a 92 x 3200 entry spreadsheet, only 5.18 MB compared to 5.17 MB. The slick part though, the custom formatting keeps the spreadsheet at exactly the same size as the original... didn't know that.

 

[Completely Bonkers]

Healthy discussion

 

[lilkiduno]

Thank ALL you guys, Sorry I havn't been on in a while. I started a new job and its hours are different and early mourning so my sleeping has been all jacked up.

You dont need conditional formatting (in your simple case)

1./ Highlight the cell you want to format.
2./ Format/Cells... and choose category "custom"
3./ In the Type: box enter: #,##0.00;[Red]-#,##0.00
4./ For percent format use 0.00%;[Red]-0.00%

Done.

I used your percentage format except I changed the formating around a bit. I instead used [red]0.00%;[black]-0.00%

If anyone would like I can post my excel spreadsheet so you can look at it and change it to fit your own personal needs. My family and I have been trying to cut back on our bills andn save a little money where we can. So thats why I put this together and wanted the percentage to stand out in red when it was grater than the month before.

Things we have done recently includes

Spoiler

we filled all our walls with the blow insulation, installed dual flush tolitets, new super effecient centeral air/heater <with heat pump>, LED light bulbs. and most importanly shut off EVERYTHING at the outlet when not in use. I will be switching to a 90% effecient PSU tomorrow when my newegg order comes in.