Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
0answers
15 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
1answer
45 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
34 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
44 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
27 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
67 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 ...
2
votes
2answers
153 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
102 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
86 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) ...
0
votes
0answers
44 views

How to make custom currency in LibreOffice Calc?

I'm trying to create a custom currency (ETH) format for some value tracking in LibreOffice Calc. I followed this instructions on this link, but the numbers (e.g. 0.05) I enter in the formatted cells ...
1
vote
2answers
109 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 ...
3
votes
2answers
1k 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
2k 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
57 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
425 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 −...
15
votes
3answers
3k 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
792 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
74 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
85 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
3k 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
133 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
165 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 ...
1
vote
0answers
519 views

Show lines in merged cell

So here's the scoop. I am setting up a form for our office. The boss wants only the fillable cells to be editable. No problem; protect the title cells and sheet structure with only the editable cells ...
0
votes
0answers
2k views

How to change individual cell heights in a table using Word 2016?

I would like to change the height of individual cells at various places in a table in word. I would like to do this to keep use of space minimum and better aesthetics. Since I am using cell shading (...
0
votes
0answers
73 views

Excel 2010 Formatting Challenge

I am having problems with cells not automatically expanding with wrapped text in Excel 2010. My rows contain simple formulas pulling data from another worksheet and the rows are formatted with ...
0
votes
0answers
1k views

Merging cells in Excel not working like it used to

For the last couple of weeks my excel 'merge cells' has not been working like it used to. I used to be able to make multiple selections (e.g. ctrl+two adjacent cells on row 1, ctrl+four cells on row 3,...
3
votes
1answer
250 views

Excel 2016 changes my cell value

Recently, whenever I enter a number into Excel, it automatically changes the value of that number. For example: I enter the data 238: Excel automatically change it into 23.8: For clarification, ...
1
vote
2answers
185 views

Align numeric values on the decimal point

In Excel I want to align the numbers to the last unit and when there are no decimals, I don't want to display any zeros after the decimal point. I want to align the cells as follows: 1234 --> ...
0
votes
2answers
12k views

Copy and paste without unlocking the protection in excel

I protected certain cells in the excel worksheet without editing (Using review> Protected sheet) but when I copy the worksheet, locked cells turn into unlocked cells and password is removed. I want ...
-1
votes
1answer
22k views

inserting a decimal point to number in excel

I've currently got a list of numbers example. 123456 I need to add a three decimal point into the number example. 123.456 I've changed the settings in the options but I have to then type out over 250,...
0
votes
2answers
256 views

Excel SUMIFS Formula Sequential Date Criteria?

=SUMIFS($I$2:$I$2221,$M$2:$M$2221,"VISA",$B$2:$B$2221,"June 1") This is the formula I am using for a sales analysis worksheet, that takes the clutter from the POS report and breaks it down in a ...
0
votes
0answers
116 views

Two identical values not treated equally in Excel

Was trying to use a simple VLOOKUP on a table with some calculated values (using formulas). It couldn't do exact match, even though the lookup value is equal to the table value. But copying the needed ...
2
votes
1answer
669 views

LibreOffice Calc: how to pad number to fixed decimals when used within formula?

In a LibreOffice spreadsheet, I want to display numbers (actually, prices) with two decimals. When a cell contains just a number, I can format it to do automatic padding. But sometimes I have cases ...
1
vote
0answers
33 views

Replicate cell format as well as value in referenced cell

I want to reference another cell, and display not only the value of the referenced cell, but also it's format. For example: If cell A8 contains `=B8` and B8 displays as `$1,234.56` I want cell ...
1
vote
0answers
193 views

Show Excel Cell Only If Another Has Data

I am trying to format the below Excel sheet so that when a date is entered into cell J1, cells G24 - G37 populate with a date a specific to what I need. I currently have the cells formatted as follows:...
2
votes
3answers
4k views

Display thousand percent in MS-Excel

I have this number: 0.0065 If I format it as percentage, I obtain: 0.65% There is any way to get it with a 'thousand percent' symbol? 6.5 ‰
0
votes
1answer
2k views

How to have Excel recognize date in format MMM YYYY (e.g. Mar 2015)?

I have a system that has outputs reports to Excel but Excel does not recognize the format as a date--"Mar 2015" to be "Mar-15". This makes pivoting the data hard because the date column is sorted ...
0
votes
1answer
549 views

Formatting time s:mm.00 in Excel (Office 2010) and graphing

My son is a competitive swimmer. I would like to make a stacked bar chart using the various time standards for each stroke. The times are listed in m:ss.00 format. I have several problems I need help ...
0
votes
1answer
237 views

Excel positives values to red

i need custom text format to highlight positive values to red. I see that there is custom format for negative values in red, but i cant figure out what should i do to highlight positive values to red. ...
1
vote
1answer
150 views

Cell Formatting, 0.00 where blank value

I have some cells that are formatted as hours and minutes i.e. [h]:mm. If no value exists in a cell I want the cell to show 0:00 without having to manually type. It needs to be user friendly, so if ...
0
votes
1answer
145 views

h:mm:ss for workout data in MS Excel for Mac

I am trying to store my running data in an Excel spreadsheet, but Excel keeps formatting it as a date and time. For instance, I enter in 30:00 for 30 minutes and 0 seconds, and Excel converts it to 1/...
1
vote
1answer
279 views

StackExchange Number Format in Excel

I've grown to love the StackExchange number format for question views: after a particular question passes 1000 views, the view count is displayed as 1k. This format has become second-nature to me, ...
1
vote
1answer
1k views

Excel Edit Mode: Time without seconds

Is is possible to show only hours and minutes when editing a time cell? Something which bothers me for a long time: I have a sheet to hold my working times. Everytime I want to change a time shown ...
0
votes
1answer
49 views

Excel reformatted numbers to times

I have a spreadsheet stored on a company Sharepoint site. Only one person in my team will use it at any time, when they're finished it saves and is available to another user. Periodically when someone ...
0
votes
1answer
244 views

How does Excel handle the h:mm:ss format?

I have the number 0.00082788917980126 which once converted to h:mm:ss format gives me 0:01:12, and I need to understand how as I need to perform similar formatting outside Excel, hence my question: ...
0
votes
2answers
4k views

Lock formula but allow colouring of the cell?

Can anyone tell me how to lock a formula in a cell but allow the users to format the colour of the cell?
2
votes
2answers
2k views

Date format worldwide in Excel

On a Mac Mountain Lion, US locales, I'm working on a Spreadsheet with macros like TEXT(TODAY(), "yyyy-mm-dd")&" is the date" The problem is that the sheet is also used on computers set with ...
3
votes
1answer
12k views

Excel - Convert dates and currency as text to Excel dates and numbers respectively

I frequently copy and paste HTML tables into Excel to do quick aggregate operations (e.g. sums, averages, etc). In this example, PayPal, their HTML table contains text values like A | B ...
0
votes
3answers
674 views

Formatting Times (durations) in Excel

Does anyone have a good quick way of entering times, specifically durations, NOT time of day, into Excel? All of the times in this case will be short, minutes and seconds. Nothing as long as an hour,...
2
votes
3answers
4k views

How do I stop Excel from converting everything to date?

Every time I try to enter a fraction in Microsoft Excel 2010, it gets converted to a date. So for example: 5.6 becomes 2013.05.06, and 5,6 becomes 1900.01.05 14:24:00. I do not want to format the ...