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
2answers
20 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 ...
0
votes
0answers
45 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 ...
1
vote
1answer
36 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 ...
0
votes
1answer
35 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 ...
0
votes
1answer
24 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 ...
0
votes
0answers
28 views

Excel/VBA - Fill color of a cell in pivot table according to column value in source table

I want to make a pivot table to control clients' payments. This pivot table has as source a table in another sheet, like this (I made a simpler table just to use as example): Source table I want the ...
0
votes
1answer
65 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 ...
0
votes
0answers
73 views

How to convert dated time to fraction hour format

I have the following format of time data imported from a software (in the lefft section). I need them in hours (fraction) for representation (like the example in the right side). How am I supposed to ...
1
vote
2answers
69 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 ...
0
votes
0answers
73 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" ...
0
votes
1answer
23 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?
0
votes
1answer
54 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?
3
votes
2answers
67 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 ...
0
votes
0answers
10 views

I want to format numers as powers of 10 on a chart

I've got the following log chart: On the vertical axis, I want to display powers of 10, such as 10^0, 10^-1, 10^-2, instead of the default scientific notation that Excel uses.
0
votes
0answers
86 views

Why is 1st January 2020 formatted strangely in Excel Pivot Table?

I have a pivot table report containing data for 1st-7th January 2020 with date being used as row names. In the raw data, the dates in the data themselves are all formatted like 01-JAN-2020, 02-JAN-...
1
vote
0answers
13 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 ...
0
votes
2answers
42 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 ...
0
votes
0answers
35 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
1answer
951 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 ...
1
vote
2answers
41 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 ...
2
votes
0answers
60 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?
1
vote
2answers
212 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 ...
0
votes
0answers
36 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....
1
vote
3answers
69 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 ...
0
votes
1answer
736 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 ...
0
votes
0answers
3k 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 ...
2
votes
1answer
47 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 ...
2
votes
1answer
25 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 ...
2
votes
1answer
544 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:...
0
votes
1answer
224 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 ...
2
votes
1answer
47 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: [>=...
0
votes
0answers
40 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....
1
vote
2answers
386 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 ...
1
vote
2answers
1k 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?...
1
vote
1answer
46 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, ...
1
vote
1answer
62 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 ...
1
vote
2answers
5k 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 ...
1
vote
2answers
1k 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 ...
0
votes
1answer
170 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 ...
1
vote
1answer
2k 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 ...
0
votes
2answers
6k 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.
0
votes
1answer
482 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.
1
vote
3answers
59 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?
0
votes
0answers
168 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. ...
1
vote
1answer
72 views

How to format cells to display two places after the leading zero in Excel?

I'm new to excel and making a sheet that runs a nutritional analysis of a diet. Many of the foods analyzed contribute very little to the total value for a given nutrient, but the user needs to see ...
3
votes
2answers
4k views

LibreOffice Calc: conditional formatting based of column / cell value

Unfortunately I cannot grasp how the conditional formatting works. I have a sheet like that: I have defined 2 cell styles "Low" and "High" and I want cells in columns A-E to have one of these styles ...
0
votes
2answers
698 views

Excel 2007 Cut and paste formula or value without changing cell formatting - paste options greyed out

I just need to cut cell contents leaving the source cell format unchanged, and paste matching destination cell formatting... I think not an uncommon ask? I have searched but Google search filter ...
0
votes
2answers
117 views

How to identify the original cell location that contains the Max information that my formula gives me?

What I am trying to accomplish is to Identify the Max of B1:B60, B60:B120, B120:B180 and so on throughout the entire column. I would also like to identify the location of those cells, and (ideally) ...
1
vote
2answers
758 views

How to calculate song lengths in a spreadsheet?

Good afternoon. I'm using the LibreOffice Spreadsheet document to generate music logs for a broadcast assignment. I'm unable to use the "time" "876613:37:46" to format the cells to calculate the ...
7
votes
2answers
15k views

How to display multi lines in an Excel cell WITHOUT wrapping

I know about using Alt+Enter to add carriage returns to a cell, and they display fine at the top editing panel, but not within the cell itself. If I turn on wrapping, it shows as multi line, but each ...