• We've upgraded our forums. Please post any issues/requests in this thread.

excel cell formating

Joined
Jun 5, 2008
Messages
475 (0.14/day)
Likes
46
Location
Kansas City, Ks
System Name The "H3X" / beaut
Processor AMD X6 1090t /Intel Q6600
Motherboard GIGABYTE 990FX UD3 / eVGA 780i FTW
Cooling Corsair H70/ Stock Intel
Memory 8GB Mushkin Silverline Enhanced 1333 / 4 Gigs Corsair Dominator ddr2 1066
Video Card(s) XFX 5850 /eVGA 9800 GTX+ (dual SLI)
Storage OCZ Vertex III 120 GB + Hatichi 1TB / WD Blue 640GB
Display(s) 3x Acer S211H/ (buddies) Vizio 37" LCD HDTV
Case Corsair Obsidian 800D / Antec 900
Power Supply Corsair 850 Modular/ Corsair 750
Software Win 7 Ultimate /Windows Vista Ultimate
#1
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
 
Joined
Nov 15, 2005
Messages
880 (0.20/day)
Likes
394
Processor 2500K @ 4.5GHz 1.28V
Motherboard ASUS P8P67 Deluxe
Cooling Corsair A70
Memory 8GB (2x4GB) Corsair Vengeance 1600 9-9-9-24 1T
Video Card(s) eVGA GTX 470
Storage Crucial m4 128GB + Seagate RAID 1 (1TB x 2)
Display(s) Dell 22" 1680x1050 nothing special
Case Antec 300
Audio Device(s) Onboard
Power Supply PC Power & Cooling 750W
Software Windows 7 64bit Pro
#2
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.
 
Joined
Feb 6, 2007
Messages
2,576 (0.65/day)
Likes
510
Processor Mysterious Engineering Prototype
Motherboard Intel 865
Cooling Custom block made in workshop
Memory Corsair XMS 2GB
Video Card(s) FireGL X3-256
Display(s) 1600x1200 SyncMaster x 2 = 3200x1200
Software Windows 2003
#3
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.
 
Joined
Nov 15, 2005
Messages
880 (0.20/day)
Likes
394
Processor 2500K @ 4.5GHz 1.28V
Motherboard ASUS P8P67 Deluxe
Cooling Corsair A70
Memory 8GB (2x4GB) Corsair Vengeance 1600 9-9-9-24 1T
Video Card(s) eVGA GTX 470
Storage Crucial m4 128GB + Seagate RAID 1 (1TB x 2)
Display(s) Dell 22" 1680x1050 nothing special
Case Antec 300
Audio Device(s) Onboard
Power Supply PC Power & Cooling 750W
Software Windows 7 64bit Pro
#4
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.
 
Joined
Feb 6, 2007
Messages
2,576 (0.65/day)
Likes
510
Processor Mysterious Engineering Prototype
Motherboard Intel 865
Cooling Custom block made in workshop
Memory Corsair XMS 2GB
Video Card(s) FireGL X3-256
Display(s) 1600x1200 SyncMaster x 2 = 3200x1200
Software Windows 2003
#5
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.
 
Joined
Nov 15, 2005
Messages
880 (0.20/day)
Likes
394
Processor 2500K @ 4.5GHz 1.28V
Motherboard ASUS P8P67 Deluxe
Cooling Corsair A70
Memory 8GB (2x4GB) Corsair Vengeance 1600 9-9-9-24 1T
Video Card(s) eVGA GTX 470
Storage Crucial m4 128GB + Seagate RAID 1 (1TB x 2)
Display(s) Dell 22" 1680x1050 nothing special
Case Antec 300
Audio Device(s) Onboard
Power Supply PC Power & Cooling 750W
Software Windows 7 64bit Pro
#6
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.
 
Last edited:
Joined
Feb 6, 2007
Messages
2,576 (0.65/day)
Likes
510
Processor Mysterious Engineering Prototype
Motherboard Intel 865
Cooling Custom block made in workshop
Memory Corsair XMS 2GB
Video Card(s) FireGL X3-256
Display(s) 1600x1200 SyncMaster x 2 = 3200x1200
Software Windows 2003
#7
Healthy discussion :toast:
 
Joined
Jun 5, 2008
Messages
475 (0.14/day)
Likes
46
Location
Kansas City, Ks
System Name The "H3X" / beaut
Processor AMD X6 1090t /Intel Q6600
Motherboard GIGABYTE 990FX UD3 / eVGA 780i FTW
Cooling Corsair H70/ Stock Intel
Memory 8GB Mushkin Silverline Enhanced 1333 / 4 Gigs Corsair Dominator ddr2 1066
Video Card(s) XFX 5850 /eVGA 9800 GTX+ (dual SLI)
Storage OCZ Vertex III 120 GB + Hatichi 1TB / WD Blue 640GB
Display(s) 3x Acer S211H/ (buddies) Vizio 37" LCD HDTV
Case Corsair Obsidian 800D / Antec 900
Power Supply Corsair 850 Modular/ Corsair 750
Software Win 7 Ultimate /Windows Vista Ultimate
#8
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
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.