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
0answers
32 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
8 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
29 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
20 views

Excel Not UnHiding Cells

I was wondering if someone knew why, when using the illustrated feature below, why Excel will not unhide the cells? Some points to consider. My table has 6 columns and over 38k rows - in addition to ...
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
0answers
229 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
36 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 ...
0
votes
0answers
27 views

Excel 2016 - Format Painter applies only to first row

One colleague has a problem with Excel 2016 format painter. She selects some cells in one row, applies a fill color, then select the entire row and clicks format painter. Then selects a range of rows ...
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
1answer
29 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
15 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
64 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
128 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 filled out and I don't ...
0
votes
0answers
802 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
41 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 ...
0
votes
0answers
20 views

How can I get, in R, features of Excel cells?

I know, and use, several R libraries to read Excel sheets. But all of them seems to get only the content of the cells and I wanted to get also some feature informations (like text color, background ...
0
votes
0answers
11 views

MS Word: New Table Rows Copying Formatting from Other Rows

When I add a new row to an existing table, the formatting for each cell of that new row is being copied from the row above. For example: I start with a 2x2 table, the top row has special formatting ...
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
172 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
71 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
46 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
30 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
205 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 ...
0
votes
1answer
280 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
42 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
46 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
2k 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
345 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
114 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
821 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
2k 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
250 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
51 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
34 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
70 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
3k 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
461 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
111 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
399 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 ...
6
votes
2answers
8k 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 ...
7
votes
1answer
6k views

How to eliminate auto single quote added to formula

In the cell of a worksheet, I am trying to reference another cell on a different worksheet in the same workbook. Excel keeps putting a single quote in front of the equal sign in the formula square. I ...
0
votes
0answers
64 views

Text in table cells is not correctly aligned horizontally

I copied a table from one Microsoft word file to another, but it has copied an issue appeared in aligning the cells' text. I chose center alignment, but the text is aligned in the middle but with some ...
4
votes
1answer
1k views

Custom negative sign symbol in Excel?

Is it possible to make numbers in Excel cells to be displayed just as in General format, except for only one thing: another character (or several characters) in place of common minus sign? I.e. make −...
19
votes
4answers
11k views

What Colours Are Available To Use With a Custom Number Format EXCEL

NOTE. Not to be confused with Conditional Formatting When using the Custom Numbers in Excel I often use RED font for a negative number. See image below. What other Colours can be used in this way ? ...
0
votes
3answers
2k views

Excel inconsistently changing the format of DATE cells

Column 1 = What I entered in cell. Column 2 = What formula bar shows when cell is selected. Column 3 = What cell shows when cell is not selected. 1 | 2 | 3 -----------------------...
0
votes
0answers
81 views

Undoing Text Formatted as Dates

I imported a TXT file into Excel. Some of the entries, in the format 11-2, were treated as dates by Excel. I did not catch this until it was too late, but Excel also overwrote my original data with ...
2
votes
1answer
128 views

How do I conditionally format a cell based on different cell results?

I am making a spreadsheet that reads barcodes to scan an item in or out. I have used VB to do this every time I scan a barcode so it adds a date and time horizontally across one row each time the ...
2
votes
2answers
6k views

How to adjust row heights depending of cell content?

I have a table of one column and more than 200 rows. I want to adjust the heights of these rows depending on their contents. For example when a cell has less than 10 characters then the row has to be ...
-1
votes
2answers
479 views

General and Text Format covert data to exponential

[ I'm using Excel 2016 I got the following 2 numbers in excel: 27837837879 and 2.79602E+14 (I can see the full number in the function panel as per attachment 279602001944127, also if I expand the ...
0
votes
2answers
266 views

Creating the difference of two numbers without a sign in Excel

I am trying to create a sheet that if I input two numbers in two separate cells, I can have a third cell that has the difference of the two numbers. The catch is I don't want a negative sign in it at ...