37 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 ...
Nayrb's user avatar
  • 728
31 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, ...
OldEmbeddedGuy's user avatar
18 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
jcbermu's user avatar
  • 17.5k
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 ...
Ben Scott's user avatar
  • 344
11 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 ...
AcePL's user avatar
  • 1,806
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!
xwhitelight's user avatar
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. ...
Máté Juhász's user avatar
7 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 -> ...
Pawel Debski's user avatar
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.
Jim Fell's user avatar
  • 5,906
7 votes

Prevent Excel to automatically "Wrap text" in a cell

Set Fixed Row Height(s) In order to stop row heights from increasing to accommodate content containing ALTENTER line breaks you can set the row height(s) to a fixed value. Row Height: Fixed Value  ...
user1769377's user avatar
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 ...
Scott - Слава Україні's user avatar
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)
RocketNuts's user avatar
  • 1,082
4 votes
Accepted

Custom negative sign symbol in Excel?

Use this Custom format: General;"!"General;0 It will change the negative to !
Scott Craner's user avatar
  • 23.2k
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",...
tohuwawohu's user avatar
  • 10.7k
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':
Glorfindel's user avatar
  • 4,099
4 votes
Accepted

Excel: Format number with minimum decimal points but leave bigger decimal points

You would use 0 for the ones that must show and # for the optional 0.00#########
Scott Craner's user avatar
  • 23.2k
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 ...
ernie's user avatar
  • 6,313
3 votes

Why does Excel treat long numeric strings as scientific notation even after changing cell format to text

Excel is frustrating for CSV/bulk data work like this. Work with LibreOffice if possible, it doesn't do this, that is, if you open the same sheet (.xlsx) in Libreoffice, the large 'numbers' which are ...
Tim Richardson's user avatar
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 ...
phuclv's user avatar
  • 27.7k
3 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 ...
Victor O's user avatar
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 ...
sancho.s ReinstateMonicaCellio's user avatar
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.
Fahad's user avatar
  • 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 ...
user638978's user avatar
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 ...
Tanya's user avatar
  • 1,703
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 ...
Atzmon's user avatar
  • 3,191
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)
teylyn's user avatar
  • 22.7k
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.
Rajesh Sinha's user avatar
  • 9,178
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.
Jingzhi Wang's user avatar
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 ...
Nikos's user avatar
  • 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 ...
JvdV's user avatar
  • 2,237

Only top scored, non community-wiki answers of a minimum length are eligible