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)
79
questions
4
votes
2
answers
15k
views
Image in a cell with a hyperlink
I need a cell that contains an image to also contain a hyperlink.
Following formula is the only one I found but doesn't work (error analyzing formula)
=HYPERLINK("URL", image("URL"))
Following ...
- 183
1
vote
1
answer
624
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 ...
- 782
0
votes
3
answers
1k
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 ...
- 25
0
votes
2
answers
23
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?
Nick
-2
votes
1
answer
293
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 ...
- 1
3
votes
3
answers
4k
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 ...
- 29
-1
votes
1
answer
1k
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 ...
- 12.6k
1
vote
1
answer
156
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/...
- 113
1
vote
1
answer
2k
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 ...
- 11
1
vote
2
answers
163
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 ...
- 11
0
votes
1
answer
429
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 ...
- 181
14
votes
2
answers
19k
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
1
answer
133
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
1
answer
2k
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
3
answers
7k
views
How can I open multiple URLs in Sheets using one cell?
I have been frequently using concatenate to combine cells and strings to render URLs in cells. The purpose of these URLs is to open specific pages to review the information on those pages.
I am ...
- 533
3
votes
1
answer
1k
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 ...
- 31
13
votes
1
answer
44k
views
Can I protect the formatting of a Google Sheets?
I’ve got a Google Sheets that has lots of formatting and formulas, in various columns, some columns are formula driven, and some columns that are user input driven. The spreadsheet is shared with ...
- 6,833
10
votes
6
answers
33k
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 ...
- 1,647
8
votes
2
answers
22k
views
How to convert all numbers regardless of decimal separator into valid number of current locale?
I have a column with text looking like this:
123,123
123.123
123,123
123.123
I would like another column to parse this into decimal numbers, disregarding the different decimal separators (dot . and ...
2
votes
1
answer
5k
views
google cell format: only 3 digits
Is there any way to do this:
I have cell and most of the numbers have 2 significant digits like 3.45. But once in a while I have a number like 143 big number, the rest are under 10. Currently for 143 ...
- 1,024
5
votes
2
answers
8k
views
Why doesn't "Custom number format" show up in Google Sheets (Google Spreadsheets)?
According to this Google help article, I should be able to do the following to apply custom number formatting in a Google Sheets document.
Highlight the data you want to format.
Click the Format menu ...
- 185
7
votes
1
answer
14k
views
Use specific currency symbol in Google Spreadsheets
'₴' is a currency sign used for the Ukrainian Hryvnia. It's in Unicode since 2004.
I want to use it in spreadsheets as a sign for money values, but Google Spreadsheets doesn't understand it as a ...
- 303
3
votes
1
answer
2k
views
Date format in spreadsheet
I want to create spreadsheets to record the money I spend each year.
Each file is named as Cost-2012, Cost-2013,...etc.
Inside each file, there are in fact 12 sheets, which are named as 01, 02, ..., ...
- 141
40
votes
6
answers
178k
views
Google Spreadsheets automatically removes leading zeroes
I have a column comprised exclusively of numbers. Whenever I add 050, the sheet automatically changes it to 50. I have tried prepending a space, but it makes no difference.
What can I do to avoid ...
- 533
21
votes
5
answers
49k
views
Having units in Google Spreadsheets?
I want to be able to input plain numbers like 200, or 3000 and have the cell display 200g, 3kg, etc...
But still be able to add the values in the cells. So if I add the two above I'd get 3.2kg on the ...
- 321
8
votes
2
answers
21k
views
A Way to Add Text Before Your Hyperlink in Google Spreadsheets? [duplicate]
Is there a way to add text before the hyperlink?
So for example:
This section needs reworking. You can find the section HERE.
HERE would be the text replacement for the actual link. The actual ...
- 89
2
votes
2
answers
4k
views
Hyperlinks in Google Sheets
In Google Sheets, it is possible to add a hyperlink to a cell as:
=hyperlink("www.google.com";"search the web")
This will show the text search the web which will hyperlink to Google.
Can this rule ...
- 211
26
votes
11
answers
132k
views
How to alter text orientation to vertical in Google Sheets?
Using MS Excel I can go to Format of the cell and choose the text orientation from horizontal to vertical fairly easily.
How do I alter text orientation to vertical in Google Sheets?
- 462
67
votes
6
answers
242k
views
Formatting a cell with word wrap without switching modes?
How can one format a cell to have word wrap in Google Sheets?
I've tried Shift+Enter, but that doesn't seem to work.
One thing I know works is to place the spreadsheet into List Mode and then if you ...
- 2,173