Questions tagged [google-sheets-cell-format]

Use for questions about Google Sheets cell format like formatting numbers, dates or using the equal, plus, minus symbols as part of texts (strings)

Filter by
Sorted by
Tagged with
1
vote
0answers
10 views

How do you “Paint Format” more than once in Google Sheets?

In Google Sheets you can select a formatted row, click the "Paint Format" Tool, then click another row to apply the same format. You can drag down to do multiple rows if they are together (say rows 50-...
1
vote
1answer
17 views

How do I keep borders when dragging down to fill cells?

I have a large scheduling spreadsheet of dates and names, split up by week, with borders for clarity. I want to be able to autofill the dates by dragging down the cell. Sometimes I also want to fill ...
0
votes
1answer
22 views

Google sheets spreadsheet will not format date correctly

I have a Google spreadsheet with a date column formatted to dd mmm yyyy. Many of the dates are formatted this way, but some cells will not format this way, and no matter what I select under "Format", "...
0
votes
1answer
27 views

Partial cell formatting with CONCATENATE in Google Sheets

Is there a way to format partially using CONCATENATE in Google Sheets? For example, =CONCATENATE("*", Sheet1!A1, Sheet1!B1), and I want to make the * bolded or blue in color or something else. I ...
0
votes
2answers
20 views

How do I Specify the Scientific Notation Power in Google Sheets?

In Google Sheets when I enter 10E-12 in a cell formatted for scientific notation, it automatically turns the number into 1E-11 Though technically correct, this is really annoying when comparing ...
1
vote
1answer
18 views

Formating a number with specific spacing

I'm attempting to pull a particular option value from Yahoo! into Google Sheets. The base URL is: https://finance.yahoo.com/quote/GOOG190418C00800000 When breaking the formula up, I have to ...
-1
votes
1answer
13 views

How can I search an argument in GSheets to return TRUE if the argument references another cell?

Hello! In some cells in the True Price Column, I applied a discount by referencing the cell Conv!C2. For example, the argument in D13 is =MROUND((D8 * 2) * Conv!C2, 5) where (D8 * 2) is me calculating ...
0
votes
0answers
47 views

Validation of cell values

I have a problem (like everyone here, obviosly). In the sheet in the column E from row 2 (row 1 is the heading) I have to choose some text: Text1, Text2... Depending on the choice of value (it should ...
0
votes
1answer
53 views

Google Sheets Custom Number Format - Unchanging #<1000

I am well aware of the excellent Custom Number Formula: [<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B" Which abbreviates #,000 numbers to #K, #,000,000 to #M etc... However, I am looking for ...
-3
votes
1answer
43 views

Why is alignment off in cells?

When the cell alignment is set to center it's actually more to the right. I've tried several reformating options including reset. The cells have no formulas, just text.
0
votes
3answers
108 views

Duration to seconds calculation

There are 3 columns in my google sheet with heading Start time (col C), End Time (col D) and time taken (col E). Column E is arrived by finding the difference between col D and col C. Now when I try ...
0
votes
2answers
16 views

When I edit a table in Google Sheet, it auto-changes my data

When I put '4.8.8' into a cell in Google Sheets, it automatically converts it to '4.8.2008'. How do I avoid it?
1
vote
1answer
73 views

Date fields that refuse to be parsed/formatted

I have some very mysterious date fields in a huge (600 000 records) CSV file which absolutely refuses to behave. They come up aligned left, and conditional formatting shows that they cannot be seen ...
0
votes
1answer
84 views

Show insignificant zero

I want, when I write a Number in a Cell, that the zeros are still visible. In a Google sheet. e.g. when I write 1.050, or 1.0 or 1.2000 there should stay exactly that. Not 1.05; 1; 1.2 or something ...
2
votes
2answers
1k views

Automatic currency conversion in Google Sheets

I have a personal accounting sheet that is mainly accounting in Euros, but for every manual Euro (EUR) entry I do, I want to have that entry converted to USD in the previous column next to it ...
0
votes
1answer
805 views

How to format a negative number red within a mixed formula result

We use the following formula: ="Stu/W: " & TEXT((A$2/B2),"[Red]#,###,##0.00") &"€" and wish to have the resulting negative currency numbers colored red and positive blue. Unfortunately ...
1
vote
1answer
44 views

Google Spreadsheet: Conditional format used together with a range doesn't seem to format cells correctly

I'm trying to create a small calendar, which is built like this: A1: = DATE(2018, 1, 1); B4: = A1 C4: = A1 + 1 D4: = A1 + 2 … This works fine and the columns B2 … D4 looks ok: Mon, 01/01 | Tue, 02/...
1
vote
1answer
637 views

How to calculate time worked in Google Sheets after 12am?

I usually work from 3pm to 1am. I can't seem to find out how to make google sheets find the correct duration between those times. I know the correct duration is 10 hours, but no matter what I do ...
1
vote
2answers
38 views

How do I add dates for the beginning and end of the week in one cell

I know very little about Google Sheets and all I want to do is create a cell that will display the beginning and end of the week, like this: 7/22-7/28. I'd like to be able to drag this across ...
0
votes
1answer
159 views

How to format Brazilian phone numbers in Google Sheets?

I have two possible phone numbers: One with 9 digits (mobile) and 8 digits (landline), and I need to insert - after 4 or 5 digits so they appear as 0000-0000 when 8 digits, and 00000-0000 when they ...
0
votes
1answer
3k views

How to remove insignificant decimal point in Google Spreadsheets?

I'm trying to format a number in Google Spreadsheets. I want it to show the thousands comma, then a period ., followed by mostly 3 digits, only when necessary. For example: 1,234.56789 => 1,234....
0
votes
1answer
35 views

How to enumerate dates into a Google Sheets' column?

I need to enumerate a Google Sheets' column with consecutive business dates (Sunday-Thursday) starting and ending with specific days. Can this be done automatically or I have to this manually?
1
vote
1answer
596 views

Convert multiple currencies and total the results in one cell

I have 4 currency columns (GBP, Euro, DKK, SKK). I then want to convert them to GBP in the 'Total Column. Is there a formula to look at all 4 currency columns and then convert to GBP in the specific ...
3
votes
1answer
407 views

IMPORTHTML data is sporadically changing to incorrect format

I'm hoping you can help me with my problem using importhtml with Google Sheets. It is a problem similar to Trying to use Google Sheets importHTML() to import a table. It forces content to a date ...
5
votes
4answers
9k views

How to change the text line height in one cell?

I could not find an option in the recent version of Google Sheets to change the text line height (the spacing between lines in a single cell). You realize the problem even more when exporting the ...
33
votes
5answers
142k views

Leading zeros in Google Spreadsheet automatically removed

I have a column in which all inputs are numbers. But these numbers are actually not real numbers. Not real in the sense, 050 in this case is very different from 50 The problem is that whenever I add ...