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
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....
CommunityBot
- 1
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 ...
0
votes
1
answer
297
views
Google sheet - highlihghting cell if in the same row other cell not empty
I'm trying to make a worksheet where I log shift hours.
I made columns with dates and to each date a row of hours
I want the date text to be green if in the same row a cell is not empty..
The code I ...
CommunityBot
- 1
0
votes
1
answer
47
views
IF/AND functions from multiple cells
Would anyone know how to fix this problem? I want cell A676 to categorize based on info from 2 cells C676 and D676.
Basically IF the word "Paypal" is in cell D, AND there is a specific ...
CommunityBot
- 1
0
votes
1
answer
28
views
How to highlight duplicate values when clicking on a specific cell
I'm new to spreadsheets, but trying to put a functional one together for my team.
I have a list of member names, and several lists of tasks they are assigned on different days of the week. The goal is ...
CommunityBot
- 1
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 ...
- 1
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, ...
CommunityBot
- 1
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 ...
- 3,403
0
votes
1
answer
574
views
How To Reformat Phone Numbers Within One Column? [duplicate]
I have a google sheet in which people's phone numbers are stored. I want them to be displayed in format NNN-NNN-NNNN, and to do this I like to store them in format NNNNNNNNNN set to display that way (...
- 44.6k
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 ...
- 44.6k
-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 ...
CommunityBot
- 1
-1
votes
1
answer
362
views
Create a RAG system based on dates, upcoming and expired within Google Sheets
I want to create a RAG system in google sheets based on dates in a cell. Column A will show a date when someone is trained on a topic and column B shows the due refresher date (3 years later). I want ...
CommunityBot
- 1
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....
- 21
0
votes
2
answers
256
views
How can I change the format to a popup calendar in Google Sheets?
I set the format in the cell to DD/MM/YY but when I click on that cell, I get a popup calendar with the week starting with Monday. But I want the calendar to start on Sunday. My Google Calendar is set ...
- 1
-1
votes
1
answer
135
views
How can I get the same format of cells in my script?
I've made a script, but I need the numbers off 3 rows to be on percentage and I also want to use only 2 decimals and format the date (one of my table colums) as "yyyy-MM-dd".
this is my ...
- 13.3k
10
votes
3
answers
22k
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-...
- 101
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 =&...
- 967
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, ..., ...
- 44.6k
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 ...
CommunityBot
- 1
-1
votes
2
answers
16
views
Have a cell always populate a certain word but also hide the same column
I would like to hide column C called Type because the input field should always be the same. I know how to hide column C, but I also need column C to always fill with the word Retail so that it ...
CommunityBot
- 1
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 ...
- 967
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 ...
- 1
0
votes
0
answers
217
views
Importrange changes the format only for a couple cells
I am using importrange and for some cells google sheets changes the format of only a couple cells. I have attached a screenshot. The original worksheet has the correct dates M/DD/YYYY. So I am ...
- 44.6k
0
votes
0
answers
83
views
SI units in Google Sheets
In Google Sheets, I work with data spanning a few orders of magnitude, like mm (millimeter), µm (micrometer), and nm (nanometer). I wrote this formula for a custom number format using conditional ...
- 44.6k
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 ...
- 9,915
0
votes
0
answers
66
views
How to recolor all cells based on the original color of the cell in Google Sheets?
I'm looking to recolor cells in a Sheets document I have, simply based on the original color that the cell already has. This is regardless of the contents of the cell and doesn't follow a traditional ...
- 1
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 ...
- 1
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 ...
- 101
0
votes
1
answer
865
views
I want date with yyyy/mm/dd format on Google Sheets
I want to convert mm/dd/yyyy to yyyy/mm/dd with function on Google Sheets.
I applied TEXT function like the picture but it's not working.
How can I convert this?
- 9,915
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 ...
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 ...
- 101
0
votes
1
answer
160
views
Google sheet currency (changable)
I was usually working on Excel sheets, in which I had option to choose currency (file was used as a template). It was based on Conditional formatting and simple formulas so it worked like charm.
So it ...
- 13.3k
-1
votes
1
answer
17
views
How could I copy information from multiple cells that share a common feature into one cell?
I have a list of codes that are each assigned to a specific bigger case code and I would like to put a list all of the smaller codes that share one bigger case code in one cell. for example
D2-D6 are ...
- 11
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 ...
- 5,530
0
votes
1
answer
30
views
How to jump to a specific customer name in a Google Sheet
I am trying to create a Google Sheet that lists all payments made for tuition by family. I would like to use only one tab (not create a tab for each family) as I have about 120 families to keep track ...
- 9,915
0
votes
1
answer
1k
views
How to format numbers with a different thousand separator in Google Sheets?
On "Format->Number->More formats->Custom number format" there are several presets, and you can create your own. Great.
So #,##0.0 yields results such as 1,234.6, ok interesting.
But ...
- 44.6k
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 ...
- 131
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 ...
- 13.3k
0
votes
0
answers
960
views
How to increase precision up to 18 decimals after decimal point in Google Sheets?
I want to have a number like this in my cell, 18 decimals after decimal point:
10.643958132611619777
But Google Sheets rounds it down to 13 decimals
10.6439581326116
How can I increase precision ...
- 351
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 ...
- 9,915
0
votes
2
answers
448
views
Simplify fractional result of custom number format
In Google Sheets I would like to express any decimal number to the nearest 32nd of an inch. If I type 1.537 I want the result to be 1 17/32, but if I type 1.5 I need it to be expressed as 1 1/2 not 1 ...
- 9,915
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 ...
- 44.6k
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 ...
CommunityBot
- 1
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", "...
CommunityBot
- 1
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 ...
- 106
0
votes
1
answer
45
views
Multiple IF functions on a single cell
How do I add multiple IF functions on 1 cel, without blocking it on the first line.
As you can see, column D contains a bunch of text. In column A, I want to categories them.
It works up until the ...
- 8,959
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 ...
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 ...
- 44.6k
0
votes
1
answer
5k
views
Convert hyperlinks to buttons [duplicate]
In Google Sheets, I have a column of active hyperlinks. When you hover over one of them, a tool tip appears that contains an "open link" icon that you can click on to open the URL in a new tab.
In ...
- 44.6k
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 ...
- 44.6k