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
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
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
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
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
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....
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
3
answers
21k
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-...
- 253
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 ...
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
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
7
votes
3
answers
22k
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 ...
- 181
6
votes
4
answers
4k
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 ...
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
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
4
votes
4
answers
6k
views
Avoid decimal point when integer
Is there a way to use a custom number format in Google Sheets so that decimal point will be visible only when decimals are present?
For instance
9
8,2
10
Is there a newer solution about achieving ...
- 579
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
3
answers
873
views
File import into sheets is dropping necessary leading spaces
I have a configuration .txt file that I'm trying to upload into a new tab in an existing Google spreadsheet. I choose File>Import and select Insert new sheet(s), Detect automatically (there is no ...
- 364
3
votes
3
answers
1k
views
Entering fractions verbatim in Google Sheets
I'm trying to put some marks in a spreadsheet and have a lot of unsimplified fractions or fractions like 19.5/20. Is there a way to enter fractions and have the cells be usable in other formulas, ...
- 131
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
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
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
2
votes
1
answer
73
views
How to format ten thousands (만/万)
I know you can format thousands and millions by using a comma in the custom number format. However, I would like to format by ten thousand (man/wan in Asian counting system). For example 10,000,000 =&...
- 127
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
2
votes
1
answer
972
views
Utilize SI units in Google Sheets
Seven years ago this was asked, are there any new developments with regard to formatting with SI units?
Normally, I will adjust the format to be all milli or micro simply by division, however my ...
- 203
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
2
votes
1
answer
7k
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 ...
- 121
2
votes
1
answer
127
views
Using INDIRECT and a cell reference to compare values on sheets
Simple story - driving me cray cray.
I have a single Google Workbook with sheets within with separate names (Sheet1, Sheet2, etc).
I have a separate sheet called Compare that I want to make formulas ...
- 23
2
votes
1
answer
108
views
Pull data as a string from a hyperlinked cell in Google Sheets
I have utility that I've been working on (modified from an existing sheet) for a Tabletop game I run for my group.
Currently, it generates random items from a list. The name of each of the items has ...
- 45
2
votes
3
answers
5k
views
Google Sheets - Hide Specific Cells (NOT Rows or Columns) based on Checkbox within a Sheet (Is this even possible?)
First off, let me say that I am as newbie as possible when it comes to Scripts and coding in general (up until last Friday, my experience was one class in C++ and some HTML back in high school...) so ...
- 21
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
1
vote
2
answers
60
views
Custom format that works for both 8 and 9 digits phone numbers
I want to format a cell to properly display phone numbers, but the number of accepted digits can be either 8 or 9.
I am currently using the following format:"387" ## ### ###
This works ...
- 11
1
vote
1
answer
7k
views
Rich text formatting in a Google Sheets cell
How is it possible to enable "rich text formatting" inside a single Google Sheets cell?
I'd like to have a cell containing some text with both bold and non-bold content, words in italic, with some ...
- 546
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
1
vote
1
answer
1k
views
How to deactivate auto-formatting?
I was trying to type things in Google Sheets, but I can't find a way for the app not to automatically change some of the things I type.
For example I can enter (-3000) in a cell and nothing happens, ...
- 11
1
vote
1
answer
67
views
Editing cell value changes its formatting
Enter the value 18/04/2020 08:10:00 in a sell.
Set the formatting of the sell to Hour(1):Minute(01)
Actual value 18/04/2020 08:10:00
Display value: 8:10
Edit the value of the sell to become 18/04/...
- 111
1
vote
1
answer
221
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", "...
1
vote
1
answer
595
views
Unsimplified Fractions in Google Sheets
How can I get Google Sheets to display fractions in the form I enter them, rather than in a simplified form? I'm trying to evaluate how a student did on a test, so I have one cell that reports the ...
1
vote
1
answer
59
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 ...
- 137
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
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 ...
1
vote
1
answer
4k
views
Split / subdivide Google Sheets cell into multiple columns and / or rows
In Google Docs, you can insert a table, right-click a cell, select "Split cell", specify into how many columns (and / or rows) you want to subdivide the cell, and click "Split" to ...
- 11
1
vote
2
answers
102
views
Alternate colors with masked rows
In Google Sheets, it seems that masking an odd number of rows in a range somehow defeats the alternate color formatting (adjacent rows will occur with identical colors). Is there a workaround using, e....
- 41
1
vote
0
answers
26
views
How can I add a thick borderline after a certain text in Google Sheets?
I'm trying to modify my Working Hours Spreadsheet; What I want to do is add a thick borderline after each Sunday, so I can better identify each individual Week of the Month.
Here's the Masterfile
...
1
vote
1
answer
2k
views
How can I paste HYPERLINK formula in Google Sheets cell, to make it clickable link with defined text?
I got a script that takes some data and prepares it to paste into Google Sheets cells, some of which are supposed to be links with custom text. But when I paste hyperlink formula the script created, ...
- 111
1
vote
0
answers
2k
views
Google Sheets text wrap no longer expands cell
All of a sudden I seem to be having trouble in my Google Sheets auto extending height of cell with text wrap, for certain cells.
Mac OS: 10.13.6 (17G65)
Chrome: Version 73.0.3683.103 (Official Build)...
- 263
1
vote
1
answer
6k
views
Line spacing in Google Sheets
Is there a way to increase the line spacing in Google Sheets, not the cell padding? I'm referring to the space between the lines in a paragraph that's inside a cell. If not, is there an addon that can ...
- 11
0
votes
2
answers
317
views
Google Sheets text rotated up, how to clip text end instead of beginning?
I have a very large table where the columns and rows are named, and interior cells are "1" or empty (essentially a sparse matrix). I have rotated the column header text direction up so that the ...
- 121
0
votes
1
answer
327
views
Trying to change Gantt chart in Google Sheets to highlight a cell based on date range and progress percentage
I have been working on a dynamic Gantt chart template in Google Sheets for awhile now. Here's how it currently looks:
As you can see, the task bar currently highlights gray dependent on the start and ...
- 21