33
votes
Accepted
What Colours Are Available To Use With a Custom Number Format EXCEL
In addition to the 8 named colors you can also use [ColorX], where X is a number between 1 and 56. The named colors are identical to [Color1] to [Color8].
A quick way to see all the colors provided ...
- 688
29
votes
Accepted
How to format a Microsoft Excel 2007 cell in hexadecimal?
As mentioned earlier, the formula =DEC2HEX(A1) converts to hex, and DEC2HEX(A1,8) converts to hex with 0 prefix to denote 32 bits. While adding the leading 0's makes the numbers more readable, ...
- 306
19
votes
What Colours Are Available To Use With a Custom Number Format EXCEL
According to this, There are only 8 colors, the list is:
[Black]
[Green]
[White]
[Blue]
[Magenta]
[Yellow]
[Cyan]
[Red]
It applies to Excel 2016, Excel 2013, Excel 2010 and Excel 2007
- 17k
12
votes
How to eliminate auto single quote added to formula
The single quote prefix means Excel is treating the cell as text. Most likely, the cell is formatted as Text rather than General. Select the cell, bring up the formatting on the cell, go to the ...
- 344
8
votes
Why does Excel treat long numeric strings as scientific notation even after changing cell format to text
I'm aware of the age of the question, but this is the one I've landed after google search and which did not answered my question (why formatting as text doesn't work and why number is cut down to 15 ...
- 1,670
8
votes
Accepted
How to dynamically show the current time (and not date) in Excel?
After you entered the formual =now() to the cell, select the cell and press Ctrl + Shift + 2
You're welcome!
- 196
7
votes
How to display multi lines in an Excel cell WITHOUT wrapping
However, if I turn wrapping off, it stupidly becomes a single line of
text. I want the best of both worlds, for it to display as multiline
AND for the text to bleed over into the next column.
...
- 21.2k
7
votes
Accepted
How to Format Excel Cells for Engineering Notation?
In the cell formatting options create a new custom type for ##0.0E+0.
- 5,746
5
votes
Align numeric values on the decimal point
I can’t figure out how to do this in one simple format.
Here is a two-step solution:
Give the cells a custom format of #,##0.0000.
Alternatively, just format them as Numbers with 4 decimal places
...
- 21.2k
5
votes
LibreOffice Calc: how to pad number to fixed decimals when used within formula?
Solved!
I couldn't find this it at first, but I've got it: FIXED(C1,2)
- 982
4
votes
Accepted
Custom negative sign symbol in Excel?
Use this Custom format:
General;"!"General;0
It will change the negative to !
- 22.1k
4
votes
LibreOffice Calc: conditional formatting based of column / cell value
For LibreOffice 5.1 as well as 6.0:
Select the range of cells to be conditionally formatted (e.g. A3:F50);
Select Menu Format -> Conditional Formatting -> Manage...:
Click Add:
Under "Condition 1",...
- 10.1k
4
votes
How do I format a number in an "=CELL" type of statement?
You're better off just copying/referencing the cell value (e.g. =A1) and changing the formatting of the cell to 'Accounting':
- 4,079
3
votes
Accepted
Excel 2016 changes my cell value
Sounds like you may have automatic decimal place insertion on?
Not sure about 2016, but in 2010, it was File > Options , then the Advanced section, and then uncheck "Automatically insert a decimal ...
- 6,273
3
votes
How to adjust the thickness of SOME lines in a Word table?
It could be done quickly with the border painter tool
Click inside the table to make the table tools group appear, then on the ribbon select Design tab
In the Borders group select whatever line style ...
- 25.2k
3
votes
Accepted
LibreOffice Calc: conditional formatting based of column / cell value
I am using LibreOffice Calc 5.1.6.2.
Just like my highly respected preceding poster described:
Select the range of cells to be conditionally formatted (in my case A1:F44);
Select Menu Format -> ...
- 280
3
votes
Accepted
Display vulgar fractions with a colon for aspect ratios
There is no way to have a cell containing a number, displayed as you mean to do.
You would have to settle for having text in that cell, referring to another cell holding the corresponding number.
The ...
2
votes
How to dynamically show the current time (and not date) in Excel?
Try this code:
=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
Thanks.
- 29
2
votes
How to dynamically show the current time (and not date) in Excel?
You could try the following:
TIME(VALUE(LEFT(TEXT(NOW(), "HH:MM:SS"), 2)), VALUE(LEFT(RIGHT(TEXT(NOW(), "HH:MM:SS"), 5), 2)), VALUE(RIGHT(TEXT(NOW(), "SS:MM:SS"), 2)))
This will effectively break ...
- 21
2
votes
Display thousand percent in MS-Excel
See this post at http://chandoo.org/wp/2012/01/31/custom-number-formats-multiply-divide-by-any-power-of-10/
Using a combination of the multiply by 1,000 formatting, and ALT+0137 character to add the ...
- 1,650
2
votes
Accepted
Excel positives values to red
This will put positive values in red and negative in default color.
[Red]#,##0;-#,##0
- 17k
2
votes
Align numeric values on the decimal point
Select the cells that contain the numbers (or the entire column).
On the Home tab Click Format and choose Format Cells.
In the Format Cells dialog switch to the Number tab.
In the Category list on the ...
- 3,071
2
votes
Creating the difference of two numbers without a sign in Excel
If you wrap the subtraction into the ABS() function, any negative signs will be removed.
=ABS(A1-B1)
- 22.3k
2
votes
Accepted
Excel date format with abbreviated month without the dot
You can use either of the Custom Date Format:
[$-"es-ES"]d-mmm-yyyy
[$-"es-US"]d-mmm-yyyy
You get 4-Oct-2018.
- 8,872
2
votes
Accepted
Can I create a multi-color cell without a gradient in Excel?
So after playing around a little bit, I figured out how to do it. My mistake was that I only added two color stops, when I should've added 4 color stops, in order to remove the color gradient. The ...
- 41
2
votes
Accepted
Merging cells accross multiple rows in Excel
You could first merge & center one column by hand (in your example M4:M7), and then use "format painter" on the other columns (N4:O7). This is probably the best way to do it.
- 36
2
votes
Accepted
How can I make an Excel cell with a DATE format accept dashes?
Had a bit of a fight with Excel but it turns out the steps to solving this craziness with the dates was for me to a bit more responsible before I started entering dates.
Solution
I wanted to have a ...
- 141
2
votes
Accepted
Filtering empty cells
Alright just to let you know, merged cells are a nightmare. A proper dataset wouldn't have merged cells. The minute you merge cells together, it will delete the values from the second, third or ...
- 2,162
2
votes
Accepted
Percentage increase in excel for cell in hour format
This method solves the issue:
How it works:
Applied Time format on cell L37 and M39:M41 is mm:ss.
Formula in Cell M39, fill down till required.
IF(ISBLANK(K39),"",(L$37*K39)+L$37)
Adjust cell ...
- 8,872
2
votes
Excel cell custom format
Create a custom number format
On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list Format Number box.
In the Format Cells dialog box, under ...
- 101
Only top scored, non community-wiki answers of a minimum length are eligible