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.
119
questions
0
votes
1
answer
32
views
Comma style button to format numbers with 1,000 separators in Excel not working as required
I am working with several large Excel spreadsheets with values frequently into the 10s of millions, so quickly comparing numbers by eye can be tricky. To help, I often format cells with 1,000 ...
- 103
0
votes
0
answers
92
views
Excel - paste *only* number formatting
Is it possible to copy/paste only number formatting in Excel.
There are various paste special options.
In particular Formulas/Number Formats and Values/Number Formats. But is there a way to paste only ...
- 263
0
votes
1
answer
41
views
librecalc showing wrong time numbers
ok so i have this
43.26
55.45
1:16.82
1:44.43
2:18.66
2:44.57
3:18.00
3:46.63
4:17.53
4:45.09
but when i input them into libre calc the first 2 are wrong. i get this as a result when formating to ...
0
votes
2
answers
240
views
Format number in Excel to align decimals but also remove decimal point optionally based on size of number
I'd like to create a publishable data table in Excel. Numbers may range from 0.010 to 1,000. I need to have similar significant figures and aligned decimal points. This is what I am looking for as ...
0
votes
2
answers
52
views
In Excel, Format a Number with Intermediate Spacing AND Optional Decimal
I have a series of numbers in Excel that I would like formatted in a certain way and I need some help.
Background:
I work in construction, and am using the Construction Specification Institute's (CSI) ...
- 1
0
votes
1
answer
59
views
Excel: Format number with minimum decimal points but leave bigger decimal points
I want a number format where I can specify minimum decimal points. So if a value has less that minimum decimal points then Excel will put the necessary decimal points. But if the value has more ...
- 271
2
votes
1
answer
6k
views
How can I format cells in Excel to render the HTML they contain?
Given an Excel spreadsheet with cells that contain HTML (in other words, content with markup), how can I get Excel to render that HTML rather than simply showing the combined content/markup? In other ...
- 251
0
votes
0
answers
493
views
Libre office, automatically color cell based on value
I’m trying to make a planning grid with various tasks and I would like a task to be colorized to easily spot it anywhere else. Each different task should get a different colour.
I can’t find a way to ...
- 101
0
votes
2
answers
67
views
How do i convert a date, stored as text with time, as "DD/MM/YYYY"
Please can you help, I have a report, with a fixed output of "Dec 15 2022 17:00:00" as the date for each line of date; however, I need to add a column to change this data to "DD/MM/YYYY&...
1
vote
2
answers
70
views
How to write custom time format in Microsoft Excel?
I'm trying to make a format which can convert values like 48.37 to 48h 37m.
- 121
1
vote
1
answer
32
views
How can I create a table with values in all columns that change relative to one another?
I want to create a table in Excel where all the resulting values in the other columns change relative to the raw value I modify. Think of it like a multi-way conversion table, where you get to change ...
- 111
0
votes
2
answers
60
views
Stop a formula when another is blank
In column A I have employees put a date in and I have formatted as date. In column H I want it to auto calculate 2 yr anniversary of the date they put into Column A which I have done using the cell +...
-1
votes
1
answer
32
views
Highlight rows when all values are for Order Number
I have a spreadsheet with two columns. One column is Order Number and the other one shows its status. As shown in the screenshot below:
Sample Data Sheet:
I want to highlight all the rows when the ...
0
votes
1
answer
520
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 ...
0
votes
1
answer
71
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, ...
- 23
4
votes
1
answer
274
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 ...
- 143
6
votes
4
answers
5k
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 ...
- 1,541
0
votes
2
answers
244
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 ...
- 1
1
vote
4
answers
1k
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${...
0
votes
2
answers
959
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
...
- 133
1
vote
1
answer
214
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 ...
- 11
1
vote
2
answers
787
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 ...
- 287
1
vote
1
answer
68
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 ...
- 11
0
votes
2
answers
455
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 ...
- 1
0
votes
1
answer
148
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 ...
- 654
1
vote
1
answer
43
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 ...
- 155
0
votes
1
answer
55
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 ...
- 485
0
votes
1
answer
29
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 ...
- 1
0
votes
1
answer
3k
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 ...
- 139
1
vote
2
answers
762
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 ...
- 113
0
votes
0
answers
445
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" ...
- 11
0
votes
1
answer
31
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?
- 1
1
vote
1
answer
996
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?
- 2,018
3
votes
2
answers
304
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 ...
- 271
1
vote
0
answers
32
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 ...
- 11
0
votes
2
answers
451
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 ...
- 101
0
votes
0
answers
40
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 ...
0
votes
1
answer
17k
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 ...
- 119
1
vote
2
answers
89
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 ...
- 1,118
2
votes
0
answers
66
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?
- 21
1
vote
3
answers
912
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 ...
- 2,572
0
votes
0
answers
477
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....
- 2,572
1
vote
2
answers
154
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 ...
- 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 ...
- 115
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 ...
- 101
2
votes
1
answer
158
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 ...
- 746
2
votes
1
answer
57
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 ...
- 251
2
votes
1
answer
1k
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:...
- 121
0
votes
1
answer
558
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 ...
- 111
2
votes
1
answer
69
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:
[>=...
- 33