Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
micky_miha la Simpatie.ro
 | Femeie
23 ani
Bucuresti
cauta Barbat
23 - 43 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
For example, you want to see the difference between the cost of your utilities each month, compared to the previous month, and you want this in color. Of course, you can do this with Conditional formatting as well and in various ways, but I am going to show you a different mean, apart from Conditional formatting.
The following "formula" goes in "Format cells" and has nothing to do with "Conditional formatting".
To make the positive numbers show in a color, negative in another you must select the cells you want formatted (eg the ones with formulas - in my example column C has C2=B2-B1, C3=B3-B2, C4=B4-B3 and so on), right click on them and select "Format Cells" then as a Category select "Custom" and there insert in the field the following line:
[Blue]+#.##0,00;[Red]-#.##0,00;[White]_-* "0";_-@_-
So any difference (in my case) that results in a positive number will have blue text, negative numbers will be red, and zero difference will be white. You can change colors as you please and you can use such formatting for any formulas you want (not just a difference of 2 cells).
Available colors (you can replace Green, Red, Blue etc. with their corresponding colors below (color4, color3, color5):
_______________________________________
|
|
| pus acum 6 ani |
|
TonyTzu
Moderator
 Inregistrat: acum 12 ani
Postari: 252
|
|
With VBA
Sub Change_No_Format()
Application.ScreenUpdating = False
Selection.NumberFormat = "[Blue]+#,##0.00;[Red]-#,##0.00;[Color56]0"
Application.ScreenUpdating = True
End Sub |
|
|
| pus acum 6 ani |
|
TonyTzu
Moderator
Inregistrat: acum 12 ani
Postari: 252
|
|
Comment with custom cell formatting with condition (limitation): EXAMPLES
[>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;####0.00
or
[< =0.001]0.000;[<=50]0.0;0
or
I want a cell to be formatted as % if value 100. I tried using custom format:
[>100] $#,###.00;[100 and 10000, it displays the $ sign but the value shown is 100 fold too small. If I type in 1234567, I get $12,345.67.
or
[>=1000000]#,##0.00,,”m”;[>=10000]#,##0.00,”k”;#,##0
or
Is there a limit to the amount of conditions you can apply to custom formatted Cells?
ie.
[Red][=2]”▼”;[Green][=0]”▲”;” ”
Seems to crash if
[Red][=2]”▼”;[Green][=0]”▲”;[Blue][=3]”■”;” “
or
|
|
| pus acum 6 ani |
|
TonyTzu
Moderator
Inregistrat: acum 12 ani
Postari: 252
|
|
Shows values over 3 with red color and values under 3 with green color:
[Red][>3]+#.##0,0%;[Color10][<=3]-#.##0,0%;[White]_-* "0%";_-@_-
|
|
| pus acum 6 ani |
|
TonyTzu
Moderator
Inregistrat: acum 12 ani
Postari: 252
|
|
Another good site with colors in excel in cells, macros, conditional formatting:
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
More:
Modificat de TRaP (acum 6 ani)
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
Percentages?
Just add them to:
[Blue]+#.##0,00;[Red]-#.##0,00;[White]_-* "0";_-@_-
it becomes:
[Blue]+#.##0,00%;[Red]-#.##0,00%;[White]_-* "0%";_-@_-
|
|
| pus acum 5 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
How to make 2 line custom formats:
eg.
To do the same with labels in the case of charts, after you have set your custom labels, edit labels - text options - textbox - uncheck Resize shape to fit text.
Now click each label (yes, each one) and drag the box to put text in 2-3 rows.
Modificat de TRaP (acum 5 ani)
|
|
| pus acum 5 ani |
|
