We changed our privacy policy. Read more.

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
0
votes
0answers
13 views

How to create a google sheet file that inputs a phrase in a certain range when another phrase is entered in a different range

I'm trying to create a google document with a classified theme (And no, it's not actually classified) And I want it set to where if you put certain text in a table or cell, it gives you a link to the ...
0
votes
1answer
23 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, ...
0
votes
1answer
28 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 ...
0
votes
0answers
22 views

Adding negative formatting for custom number format

I have a custom number format that formats a number into an in-game currency, the currency is broken down into copper, silver and gold with the following conversion; 1 gold = 100 silver = 10000 copper ...
-1
votes
1answer
16 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 ...
0
votes
1answer
8 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 ...
0
votes
1answer
40 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 ...
0
votes
0answers
71 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 ...
0
votes
1answer
63 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 ...
1
vote
1answer
548 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 ...
0
votes
0answers
24 views

Use an embedded image as a link in a Google Spreadsheet in the published version

This is a very specific set of circumstances. I'm trying to use an image which I've embedded as a link. This works in the Edit interface, but when published, the image/cell does not act as a link. I'm ...
0
votes
0answers
13 views

In a sheet which has tab names as date, can I empirically refer previous sheet, so I can calculate report based on yesterdays

In a sheet which has tab names as date ., can I empirically refer previous sheet, so I can calculate report based on yesterdays? =IMPORTRANGE("Link",""&BM4&"!AU:BC&...
0
votes
1answer
33 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 ...
0
votes
1answer
40 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 ...
0
votes
1answer
37 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 ...
1
vote
2answers
1k 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
vote
2answers
174 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 ...
1
vote
1answer
35 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/...
0
votes
1answer
61 views

How can I have conditional formatting with lower priority than manual formatting

I know how to do the formatting that I want. The document should have conditional formatting. However When I added it. It covered the manual formatting. It there a way to say do this conditional ...
0
votes
2answers
107 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 ...
2
votes
1answer
78 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 ...
1
vote
0answers
22 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
1answer
802 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, ...
0
votes
1answer
3k 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 ...
2
votes
4answers
3k 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 ...
0
votes
2answers
4k views

How can I make a formula ignore a blank cell in Google sheets

I am creating a budget template and have a formula that multiplies cells. However, not every cell will have a number in it. How do I make it so the formula will ignore blank cells or make them = 1 ...
0
votes
1answer
291 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 ...
3
votes
3answers
870 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, ...
5
votes
2answers
8k 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-...
5
votes
4answers
2k 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 ...
1
vote
1answer
61 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
2answers
95 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 ...
1
vote
1answer
292 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 ...
2
votes
1answer
3k 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 ...
2
votes
3answers
12k 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
0answers
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)...
1
vote
1answer
48 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 ...
0
votes
1answer
4k 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 ...
-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 ...
3
votes
2answers
11k 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 ...
1
vote
1answer
431 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 ...
0
votes
3answers
507 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
18 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?
0
votes
1answer
205 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 ...
3
votes
3answers
3k 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 ...
-1
votes
1answer
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 ...
1
vote
1answer
71 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
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 ...
1
vote
2answers
64 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
349 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 ...