Questions tagged [cell-format]

Cell-level setting in a spreadsheet that specifies its data type and describes the default way it will be displayed. Use only if the cell format is central to the question. Use [text-formatting], instead, for questions pertaining to creating or preserving the format of text or data. For questions pertaining to displaying cells differently under specific conditions, use [conditional-formatting] instead.

Filter by
Sorted by
Tagged with
0 votes
1 answer
29 views

Highlight entire rows when values in a single column are unique (conditional formatting)

I have a spreadsheet with several columns of data and a couple thousand rows. Each row contains a product and each column contains details about that product. Example: Row # Category ID Item Item ...
user avatar
0 votes
1 answer
24 views

How to output the results of 2 different cells into a single cell in Excel?

I am trying to get D5 and D6 into cell C6 so that I looks like this ($15,000.00 - $25,000.00) The formula I used to do this is : =DOLLAR(D5)&" - "&DOLLAR(D6) My first question is, ...
user avatar
  • 23
3 votes
1 answer
47 views

cell to overflow text but still consider linebreaks inside text

I have a cell that contains multiline text. Some lines are pretty long. I would like the text to overflow the cell boarder but at the same time respect the line breaks from the text and I need the ...
user avatar
  • 133
0 votes
3 answers
466 views

Prevent Excel to automatically "Wrap text" in a cell

Let's say we have a spreadsheet with all rows having the same default height. In a cell, let's write Hello ALT+ENTER World. I indeed sometimes need to enter multi-line text in a cell, to write some ...
user avatar
  • 1,696
0 votes
2 answers
39 views

How to assign a number on a text cell in excel

I need help. So I need to assign a number on a txt cell from excel. For example: the cell has the word Blouse. The word Blouse needs to have a value of 20 then I need to multiply this by $5. How can I ...
user avatar
  • 1
0 votes
3 answers
104 views

Paste copied text as "TEXT" format in Excel without apostrophe

I have a function in a web app that generates a list of values that can be pasted into Excel. This code is in javascript. Each line is generated with the following line of code: exportList += `\n${...
user avatar
0 votes
2 answers
200 views

How to *format* time duration as hh:mm:ss in LibreOffice/Excel?

I would like to copy the time duration (in seconds) to a cell, then have it displayed as (hh:)mm:ss or similar. For example: Input: 0.49 Display: 0.49 or 0.49s or 490ms (any will do) Input: 857.26 ...
user avatar
  • 33
1 vote
1 answer
60 views

Word tables. text skewed between cells in the row

The image below demonstrates the issue. (using Word from 'Microsoft office 365 Apps' product is up to date at the time of post) The gap is due to special characters and an equation input. It's most ...
user avatar
  • 11
0 votes
2 answers
149 views

Is it possible to format a cell in Excel with optional decimal point when not required?

If I use this cell format: ###,##0.##, and the number is an integer, for example, 12, cell appears like this: 12, which looks ugly. Is there a way integer numbers to be displayed as integers and ...
user avatar
  • 255
1 vote
1 answer
49 views

Is there a way to protect only some cells from re-formatting?

I have a .xlsm file that users input information to in unprotected cells. Users need to be able to add as many rows as necessary and to reformat the cells the added. However, I do not want them to ...
user avatar
  • 11
0 votes
2 answers
27 views

How to Fill One Cell With Date Based On Corresponding Cell

For a project, I'd like to look through a set of cells for a maximum in one cell, and auto-populate the next cell based on the corresponding date for the maximum. For example, column A is the date of ...
user avatar
0 votes
1 answer
73 views

Number format #.# - Excel bug?

I try to use the number format #.# to display 7 as 7, but it does display "7." (in Excel, or other MS environment) I want 7.1 to be 7.1, and 7 to be 7, and 7.49 to be 7.5, that is #.# (see ...
user avatar
  • 596
1 vote
1 answer
38 views

Can this cell numeric value be "displayed" in a different way in Excel?

Take a cell with this numeric value 210061. I would like to display this cell as: 21.0.6.1 Specifically: The first 2 digits are the major value. The next two digits are the minor value (but ...
user avatar
0 votes
1 answer
42 views

How to match cell value to cell format

I have cells that appear as 20-Feb, 20-Mar, 20-Apr, etc, when you look at the sheet. When you click on the cell you can see in formula bar 2/20/2021, 3/20/2021, 4/20/2021. I would like to copy and ...
user avatar
  • 387
0 votes
1 answer
28 views

Is thеrе any wаy tо propеrly displаy nеgativе timе spаns in Exсеl?

Is there any way to make Excel show a negative time span? If I subtract two time values (say, when subtracting the actual amount of time spent on something from the amount of time planned for it) and ...
user avatar
0 votes
1 answer
739 views

How to change the formatting of German letters in Excel?

I have a CSV file with German words in its entries. Here's an excerpt: As one can see, a number, a number of entries contain words with German words that contain characters that are depicted somewhat ...
user avatar
1 vote
2 answers
137 views

Excel: different number of digits in the same column Excel

I have these numbers pasted as text in one sheet: 0.000180 0.000590 0.000850 0.000240 0.000290 0.000160 0.00419 0.000180 0.00187 0.000160 0.000310 0.000120 I would like to convert them to numbers in ...
user avatar
0 votes
0 answers
144 views

Excel: Fake Blank Cell (cell has no content, nothing in the function box, but not recognized as a blank cell)

I have an Excel file with plenty of "blank" cells, these cells are not showing any content in the cell itself nor in the function box. And I'm trying to replace all these "blank" ...
user avatar
0 votes
1 answer
26 views

Combine merge content in Excel

Are there any formulas or tricks to change the cell formatting from the red table to the green table in Excel 2016?
user avatar
0 votes
1 answer
220 views

How can I avoid an Excel cell's being automatically formatted as currency upon entering financial functions such as PV, FV, PMT, NPV...?

A cell is automatically formatted as Currency when I fill it with a formula including a financial formula like PV, FV, PMT, NPV... How can I avoid this automatic formatting?
user avatar
3 votes
2 answers
137 views

Display vulgar fractions with a colon for aspect ratios

How can I display floating point numbers in cells as aspect ratios with a colon : or ratio symbol ∶ U+2236 instead of a slash / in spreadsheet applications? One can format numeric cell contents to ...
user avatar
  • 231
1 vote
0 answers
19 views

How do I turn off autoformat OpenCalc / OpenOffice Spreadsheets

Without context and background: Read the title, How do I turn off AutoCorrect in OpenCalc, specifically time, preferably turn off all AutoFormat. I am using a spreadsheet to track how fast my online ...
user avatar
0 votes
2 answers
248 views

Dynamic rule to change the color of a cell when a check box is true

The following code automatically links any CheckBox in the active sheet to one cell before the checkbox. In other words, I can add as many checkboxes as I want and when I run the code/macro, all ...
user avatar
  • 101
0 votes
0 answers
38 views

Duplicating certain rows while retaining variable values (except for 1 cell)

I am using Excel. I have a dataset which consists of a number of variables. Sadly this dataset was entered by multiple people who didn't keep to the same format. So I have a dataset containing data ...
user avatar
0 votes
1 answer
4k views

Excel custom number format: Is there a way to show the number in basis points (bps)?

I would like to show a cell in basis points, so that if the numeric value is 0.01, it renders as a string "1 bps" in the cell; 1.00 would show "100 bps" Is there a way to do this through custom ...
user avatar
  • 119
1 vote
2 answers
48 views

Format cell as days plus time duration

In Excel I have cells that contain durations, stored as date (integer part) plus time (fraction) as usual. Now I would like to format the values to see the number of elapsed days plus the remaining ...
user avatar
2 votes
0 answers
64 views

I am unable to enter 6E029 in a cell in Excel

I am unable to enter the text from 6E000 to 6E999 in Excel. Every time I enter this it is taking exponential form. How can I format this to actual text?
user avatar
1 vote
2 answers
360 views

Suspect behaviour getting cell width in Excel

From what I have gleaned, the way to get a cell's width in Excel is to enter the formula =CELL("width") into the cell in question. This works fine for one cell at a time, but when I copy the formula ...
user avatar
  • 2,422
0 votes
0 answers
78 views

Cells show number as text despite number format

I have a range of cells I apply a number format to using the EPPlus Excel library for .NET. I apply the format as follows: ws.Cells[$"D{startAt + 3}:E{endAt - 1}"].Style.Numberformat.Format = "#,##0....
user avatar
  • 2,422
1 vote
2 answers
87 views

Excel cell custom format

I cannot find the way, how to create custom cell format in Excel. For example, I have value 3.5555, which is displayed as 3.6. I would like to get displayed 35 555. If I insert number 5 ... I would ...
user avatar
  • 19
0 votes
1 answer
1k views

Excel apply complicated formula to existing cells value without copy/paste

I want to apply the formula =TEXT(A1,IF(A1-INT(A1)=0,"0","#.####")) to existing cells with numbers without doing copy and paste. The existing spreadsheet is already big and ...
user avatar
  • 15
0 votes
0 answers
4k views

Excel 2016: All cells dark by default

I have a very weird and annoying problem. Cells in my Excel 2016 are dark by default. Every new document is dark. Every opened document is dark (sometimes with some exceptions). I tried removing ...
user avatar
  • 1
2 votes
1 answer
70 views

Percentage increase in excel for cell in hour format

I need to set a formula to calculate a percentage increase/decrease from a cell with hour format. Now the original cell is like [mm:ss] and I need that the result to be also in tha same format. By ...
user avatar
  • 746
2 votes
1 answer
27 views

Filtering empty cells

Notice that B2 and B3 are merged and a filter is created on the B column When I'm going to apply the filter it shows Empty (Vazias in Portuguese) as one of the options as there are merged cells in ...
user avatar
  • 229
2 votes
1 answer
875 views

How do I format time to MM:SS in Libre Office Calc?

Libre Office Calc 6.0.7.3. I'd like to enter the time 5:39 in minutes and seconds in a cell. I highlight the cell and select Format > Cells from the menu. In the 'Format Cells' dialog, I enter MM:...
user avatar
0 votes
1 answer
384 views

Need to Stop Excel From Reformatting Numbers

I can't get Excel to leave my numbers alone. I've set my column to "Text" in the format settings, but nothing I do in the number or other formatting is having any effect. It's been going on for a ...
user avatar
  • 111
2 votes
1 answer
49 views

Multiple conditions in custom format

I would like to have multiple formatting conditions eg. 561 -> 561 1,100 -> 1.10K 2,220,000 -> 2.22M 45,200,000 -> 45.20B I came up with following solution but it wont work: [>=...
user avatar
  • 33
1 vote
0 answers
65 views

Hide irrelevant decimal fractional part of numbers in spreadsheets

Is there any way to format numbers in 123/Excel-compatible spreadsheet applications, Google Spreadsheets in particular, in a way that hides the fractional part and the preceding decimal separator (i.e....
user avatar
  • 231
1 vote
2 answers
576 views

Microsoft Word "DOT" not showing up after the table

The "dot" as highlighted below in the screenshot does not show up in my another Word document. It only appears in inside the table and not at the right edge of the table. See the difference of the ...
user avatar
1 vote
2 answers
2k views

How to Format Excel Cells for Engineering Notation?

In the engineering disciplines int's nice to be able to have values formatted in easily readable engineering notation, where the exponent is a multiple of three. How can this be accomplished in Excel?...
user avatar
  • 5,636
1 vote
1 answer
52 views

Put a list of cell data in a single cell based off of specific values

I know the question title isn't great, I can't think of a good way to phrase the question, making hard for me to find answers just searching around online. I don't have a ton of experience in excel, ...
user avatar
1 vote
1 answer
91 views

Excel: Different formatting on different values in same cell

I have the following formula in excel: =TEXTO(D39;"#.##0")&" "&"("&TEXTO(D40;"0%")&")" This formula returns an output with a size in the cell -- left number in this picture: I need ...
user avatar
  • 113
1 vote
2 answers
7k views

How can I make an Excel cell with a DATE format accept dashes?

I have an Excel sheet in which various date columns exist. Initially, all dates were in format "01/01/2000" but I wanted dashes, so I did a find-replace all expecting all the slashes (/) to be ...
user avatar
  • 121
1 vote
2 answers
2k views

Excel 2013 - Detect Cell Currency Format

I received an Excel file containing an extract of sales forecasts. There is no column explicitly defining currency unit. Currency unit is only shown by number formatting (GBP, EUR or CHF). Does anyone ...
user avatar
0 votes
1 answer
200 views

excel clear sets format as accounting

In a specific workbook when i use Clear > All or Clear > Formats Excel sets the number format to Accounting instead of General. This does not happen in other workbooks. It seems that Excel is ...
user avatar
  • 1
1 vote
2 answers
3k views

Merging cells accross multiple rows in Excel

Excel has a neat feature called "Merge Across" which as the name implies, merges multiple cells into compound cells that each span two or more columns. I would like to know if I can achieve a similar ...
user avatar
0 votes
2 answers
10k views

Can I create a multi-color cell without a gradient in Excel?

I want to apply two colors to one cell and separate them diagonally. I know that there is a function to add gradients in Excel, but I haven't found a way to set the strength of the color stop.
user avatar
0 votes
1 answer
748 views

Excel date format with abbreviated month without the dot

Hi, I am using this formula for custom date format in Excel: [$-es-ES]d-mmm-yyyy and this is what I get: 4-oct.-2018 almost fine but I dont need that dot there, how can I lose it? Thank you.
user avatar
  • 179
1 vote
3 answers
63 views

How do I format a number in an "=CELL" type of statement?

I have a spreadsheet where cell A1 is 1234.56789. In cell B1, I have ="$"&A1. This shows up in B1 as $1234.56789. How do I make it show up as $1,234.57 in B1?
user avatar
0 votes
0 answers
290 views

Excel automatically changes font in a cell after text input

I have an Excel sheet (in Excel 2013, xlsx format) with a table in the first 10 rows. The first column of this table (and it's 10 rows) has font set to Arial/Bold/10. All rows have some data in it. ...
user avatar
  • 296