14
votes
Accepted
How do you "Paint Format" more than once in Google Sheets?
I figured out a way that is almost as good. Early on in Google sheets double clicking the paintbrush icon worked. Why Google removed that I don't know. But while you can't make the paintbrush icon "...
- 166
12
votes
Accepted
Can I protect the formatting of a Google Sheets?
The way to address the problem is to use a combination of custom function in google spreadsheet and google script. I will use examples to try to address solution for your problem
Formula protection :
...
- 521
9
votes
Accepted
How do I Specify the Scientific Notation Power in Google Sheets?
To specify power of exponent, such as to powers of 3, 6, 9, etc, specify the number of places in the format.
e.g.
##0.00#E+##
Note that there are 3 places on the left of the decimal and 3 places on ...
- 106
5
votes
How to alter text orientation to vertical in Google Sheets?
As of 22 February 2017, support for rotated text will be introduced:
Note
Gradual rollout (potentially longer than 3 days for feature visibility)
Reference
Blog post reference: Support for rotated ...
- 22.8k
5
votes
Accepted
How to remove insignificant decimal point in Google Spreadsheets?
closest you can get is to use 1+ column
go to 123 or Format
select Numbers
select More formats
select Custom number format...
type: #,###.###
and corect it with: =IF(RIGHT(A2;1)=".";SUBSTITUTE(A2;".";...
- 26.2k
4
votes
Image in a cell with a hyperlink
Your first formula worked for me. Perhaps there's something off about the way you've formatted one of the URL's?
- 141
4
votes
How to format ten thousands (만/万)
I have two solutions, and both aren't great. The first problem is that Google Sheet has no support for that counting system. It can only shorten a number by thousands, as fractions, or as scientific ...
- 967
3
votes
Accepted
Image in a cell with a hyperlink
=HYPERLINK("https://mail.google.com/mail/u/0/";
IMAGE("https://i.stack.imgur.com/XJ2AY.png"; 3))
demo spreadsheet
- 26.2k
3
votes
How do I add dates for the beginning and end of the week in one cell
A1:
=ARRAYFORMULA(JOIN("-",TEXT({"7/22","7/28"}+(COLUMN(A1)-1)*7,"M/DD")))
Drag to right.
Two dates are initialized and a column number*7 is added per each column.
TEXT to format dates " m/...
- 3,272
3
votes
Having units in Google Spreadsheets?
I found it.
Format > Number > More Formats > Custom Number Format
then enter: . "lbs"
If you want two decimal points then it would be: .00 "lbs"
Putting whatever you want in quotes allows you to ...
- 31
3
votes
How can I open multiple URLs in Sheets using one cell?
Google sheets now supports multiple hyperlinks in one cell. Select text fragment and press ctrl+K or use hyperlink icon after selecting text.
3
votes
How to change the text line height in one cell?
Google Sheets doesn't include a setting to customize the text line height.
One workaround is to set vertical align to Middle and change the row height to a size big enough.
NOTES:
When the OP says ...
- 44.6k
3
votes
How do you "Paint Format" more than once in Google Sheets?
In a (not-so-distant) past, it was possible to do what you want by double-clicking the Paint Format icon and then selecting the destination cells no matter if such cells were contiguous or not. ...
- 246
3
votes
Utilize SI units in Google Sheets
Custom number formatting supports conditionals, but only two of them plus a default format. So, to display millions and billions of dollars, you can use Format > Number > More Formats > ...
- 13.3k
3
votes
Google Sheets text rotated up, how to clip text end instead of beginning?
You have a long header cell that you have rotated to save space. However, when the height of the row is shortened, a "long" value is effectively right-aligned so that the end of the value is shown ...
- 5,530
3
votes
Accepted
File import into sheets is dropping necessary leading spaces
After waiting a while and coming back to the problem, I stumbled upon a solution that's better for someone like me who knows nothing about apps script (though I'll grant that Rubén's solution did ...
- 364
3
votes
Custom format that works for both 8 and 9 digits phone numbers
Try this custom format:
[<99999999999]### ## ### ###;[<999999999999]### ## ### ####;0
which in practice means: if the number consists of 11 digits, format it one way, if it consists of 12 ...
- 2,338
2
votes
Formatting a cell with word wrap without switching modes?
First Select the Cell you want to Wrap
From The toolbar, select "Format" -> "Text Wrapping" -> "Wrap"
- 121
2
votes
IMPORTHTML data is sporadically changing to incorrect format
Possibly try a different approach. Don't use query but instead, in A1 and copied across to B1:
=index(IMPORTHTML("http://www.thepowerof10.info/athletes/profile.aspx?athleteid=800922&viewby=date","...
- 17.4k
2
votes
Accepted
Google Sheets Custom Number Format - Unchanging #<1000
custom number format: [<1000]0"";[<1000000]0,"K"
- 26.2k
2
votes
How to remove insignificant decimal point in Google Spreadsheets?
I know this won't resolve anything for most people, but for me the answer was to format the number as text in Excel before I copied it into Google sheets. Again, probably not useful, but for those of ...
- 21
2
votes
How do I Specify the Scientific Notation Power in Google Sheets?
Select the cell(s)
Click on Format > Number > More formats > Custom number format...
On the box write something like #0.0E+00
Click Apply button
Note: The leading # is to set an optional digit
...
- 44.6k
2
votes
Partial cell formatting with CONCATENATE in Google Sheets
If you are after this then it's not possible:
You can do this only manually.
If you just want more visible star you can use one of these instead like:
=CONCATENATE("✱", Sheet1!A1, Sheet1!...
- 26.2k
2
votes
How do I keep borders when dragging down to fill cells?
For me the easiest way is:
Copy the whole range you are planning to drag onto including the draggable item (select+ Ctrl+C)
Drag the item and break the formatting
Select the range and paste format ...
- 121
2
votes
Entering fractions verbatim in Google Sheets
Put the numerator and denominator in different cells. Use an extra column to calculate the decimal representation of the fraction (eg d2 = b2/cd) to use in calculations, and convert the original ...
- 7,293
2
votes
Rich text formatting in a Google Sheets cell
Rich-Text formatting is not an OPT in/out / turn on/off feature. It's always turned on. To use it:
Put the cell in edit mode
Type the text
Select the text part to apply bold, italics, color
Apply the ...
- 44.6k
2
votes
Accepted
Google Sheets text rotated up, how to clip text end instead of beginning?
For now, I have worked around the issue by rotating the text down instead of up. Although this doesn't achieve the alignment aesthetic I wanted, it will suffice for now, and clipping end of value text ...
- 121
2
votes
Multiple IF functions on a single cell
Matter is with SEARCH() returning error when failing to find your criteria.
To avoid that wrap your SEARCH() in IFERROR() like this:
=IF(IFERROR(SEARCH("wheel", A41),),"car",
IF(...
- 791
2
votes
Trying to change Gantt chart in Google Sheets to highlight a cell based on date range and progress percentage
Update: I have found the answer. I split the above formula into two conditions as such:
=AND($C6=100%,I$4>=$E6,I$4<=$G6) then dark gray
=AND($C6<100%,I$4>=$E6,I$4<=$G6) then light gray
...
- 21
Only top scored, non community-wiki answers of a minimum length are eligible