All Questions
Tagged with excel or microsoft-excel
80 questions
0
votes
1
answer
43
views
Looking for help on formula while pulling data from a different GSheet using Index & Match?
Facing a #NA error while pulling data from a different google sheet, using Index & Match. Need Test1 (GSheet) data pulled in to Test2 (GSheet) on yellow highlighted cells.
Test1 - https://docs....
- 45
3
votes
1
answer
722
views
Does importrange not work on Google Sheets embedded XLSX files?
The ImportRange documentation mentions nothing about not supporting XLSX embedded files.
But when I try it, it claims it has no permission (whereas I'm an editor in the file I try to import).
Does it ...
- 1,270
2
votes
1
answer
101
views
How do i convert a Google Sheets formula into an Excel formula
A week ago I found help here in creating a formula in Google Sheets. Now the requirements have changed. The new requirement is to create the document as an Excel document. I can't take the formula ...
- 23
2
votes
2
answers
216
views
How to sort unique cells in order of appearance on google sheets?
So, I know there is a easy way to sort unique values in alphabetic order using
SORT(UNIQUE(),1,TRUE)
but i'm trying to order based on how many times a item has appeared.
exemple, if had a column like
...
- 23
0
votes
1
answer
795
views
Use MATCH & LARGE but with 2 identical highest values in Google Sheets
Thank you to doubleunary for quickly solving my first of two questions. I am now able to create a single cell that properly lists all of the top 5 largest value column titles separated by commas; even ...
2
votes
1
answer
137
views
Using Google Sheets to bulk collect the number of Google Search results count for given keywords
I need to bulk check the number of Google Search results count for given keywords by using Google Sheets or Microsoft excel
For example,
ocean is About 2,960,000,000 results
0
votes
1
answer
24
views
Formula to count the text in a rang of cells (with formulas)
I’m trying to have an Excel formula that counts the number of cells filled with text from a range of cells. The problem I have when using =counta is that the cells I’m trying to count all have their ...
- 1
1
vote
2
answers
135
views
Google Sheets: return multiple values of substring search
Lets say I had a sheet like this
COLA
COLB
COLC
ROW1
blah
blahbingo
blah
ROW2
blahmagic
blah
blah
ROW3
bingoblah
blah
magicblah
And then I had a sheet like:
R
C
Substrings
magic
bingo
What ...
user1754150
0
votes
1
answer
503
views
How to prevent users from editing an Excel file with Google Sheets when sharing a Google Drive link?
When I share Excel files that have features unsupported in Google Sheets (such as Pivot Tables), I would like to ask people to open the file in Excel (using the copy located in their Google Drive for ...
- 111
3
votes
2
answers
7k
views
How to repeat a cell a set number of times in Excel?
I have two columns, the first with a description of an item, the second with the quantity of the item. I'd like to turn this into a list of the described items. I need the solution to work on Windows/...
- 35
1
vote
1
answer
94
views
How do I convert this excel formula to work in Google Sheets?
In excel I have this:
=INDEX(PayoutID,MATCH(TRUE,COUNTIFS(INDIRECT("'"&PayoutID&"'!$D:$D"),$A3)>0,0)) in B3
It looks at cell A3, then using a named range PayoutID (a ...
- 11
0
votes
1
answer
107
views
How to use data validation based on 2D array or row and column of tickboxes in google sheets?
Here, I'm attaching the part of the sheet which contains the tickboxes.
Objective is to format the column A with green or red background colour.
Rules:
Data in column A should be green, if any of ...
1
vote
1
answer
46
views
How can I conditionally format any empty cell from column C to L if the respective cell in column B not empty
I need your help with conditional formatting in Gsheet or Excel (I will choose one of them depending on the solution).
This format needs to be: Any empty cell in columns C to L must be highlighted if ...
- 11
1
vote
0
answers
25
views
returning value 0 when use COUNTIF
I am stuck in a situation, I am trying to count the number of releases based on each month , but not sure why it keeps returning the value 0?
example :
=COUNTIFS(M2:M5,G19,B2:B5,H18)
M2:M5 : Month ...
- 11
1
vote
1
answer
23
views
Need help coming up with formula to count and concatenate specific things
Though I'm not the best at Excel/Google Sheets (this is Sheets specifically), I'm usually able to figure out how to do the things I need. However, this one is stumping me.
Here is a screenshot of my ...
- 11
1
vote
0
answers
69
views
NETWORKDAYS.INTL Method Changed
Google sheet's NETWORKDAYS.INTL Method supposes to calculate number of working days between two given dates, and consider weekends and holidays.
I use this method a lot in my functions, but today I ...
- 35
1
vote
0
answers
53
views
How do I perform a VLOOKUP to the left without explicitly naming the left column range in Google Sheets?
I have an array of data that I want to look up a specific phrase from. Once the phrase is found I would like to output the value or phrase of the cell to the direct left of the cell that’s found. I ...
- 21
0
votes
0
answers
24
views
Number of Cells which have a specific text, but where the row also has another specific text
There are some WoW terms in this post, will clarify if requested.
I'm making a spreadsheet in google sheets for my wow guild where we will be able to input our class, spec and if it is an alt or not.
...
-1
votes
1
answer
602
views
Formula to select the name associated with lowest value from data set & a random name from the lowest options if tied
In Google Sheets, I have 4 Names associated with 4 data points that update throughout the month. I'd like to auto pick the name associated with the lowest data point. If there are 2, 3 or all 4 that ...
- 1
1
vote
1
answer
2k
views
Automatically Color Series In Chart
I am trying to make a graph that is essentially a timeline of a few different activities, all of which can be repeated. The only way I've come up with to do this so far is to have each "instance&...
- 241
0
votes
1
answer
155
views
Check if any cells in one column fall between cells in different columns in Google Sheets
So I'm trying to figure out if any call for service happens during the time frame of another call.
The information is set up below like this:
| |[A]|[B]|[C]|
|:---|:---|:---|:---|
|1|"Call Time&...
0
votes
1
answer
39
views
How do I put the if function inside the lookup? - Daily Routine sheet
I have a sheet - daily routine which has two columns: activity and time as shown here
Activity
Time
Sleep 😴
6:00 am
Toilet 🚽
6:15 am
Get ready for gym 🤸♂️
6:30 am
Exercise 💪
7:50 am
... ...
- 101
0
votes
0
answers
34
views
Multivariable Matching in Google Sheets - scanning for multiple values simultaneously and posting shared data
I have two sheets from two different sources (Facebook Ads and Google Ads) and I want to match their data onto a third sheet. The data itself is different, but the identifiers are the same - campaign ...
vdog
0
votes
1
answer
972
views
Lookup between two dates in Google Sheets
I have one sheet with the following information:
In another sheet I have the following:
How can I fill in the cells in column B in my first sheet, based on the date? The date should be between the ...
- 149
0
votes
2
answers
2k
views
Select full column in Google Sheets by [@column]
In Microsoft Excel I know I can select a row by:
Inserting a table (table name "Table1") with header
Select column by = Table1[@ColumnName]
How can I do this in Google Sheets?
- 149
-1
votes
1
answer
5k
views
Replacing text of links in google sheets
I have a bunch of links in cells in my google sheets. I want to replace all the text of them all just to say "LINK" so that when I click on them it's still hyperlinked to the correct site.
...
- 1
-1
votes
1
answer
54
views
Google Sheets - Multiple countifs
Example scenario: (picture added)
I want to find out how many people had reason "No access to accommodate patient" at location A during February 2022. (more like filling up the table)
How ...
1
vote
1
answer
3k
views
A way to "lock" and "unlock" a Google Sheet's contents
How can I create a cell lock / unlock mechanic in Google Sheets similar to Excel?
NOTE: I know how to protect a google sheet and limit editing permissions to certain users. There are many similar and/...
- 173
-1
votes
1
answer
2k
views
How can I change the min and max value of vertical axis in chart?
I want the vertical axis to range between 75 and 125. The min should be 75 and the max should be 125.
0 to 125 range is automatically generated.
The highest data point is 107 and the lowest data point ...
0
votes
1
answer
62
views
Google Sheets Formula Not Working in Excel
I have this formula pasted below that works in Google Sheets, but not in Excel. I've looked up possible solutions, but just cannot find what may be the problem with it. The return I get is just the ...
- 11
1
vote
1
answer
160
views
How can I highlight the source cells in a formula (e.g. =SHEETA!$E$568 )
I have a sheet that makes extensive use of cells from another sheet with formulas, some of which have errors. The formulas look like:
=SHEETA!$E$568
Is there a way, or a script, that can parse the ...
- 726
0
votes
1
answer
724
views
How do I correct the automatic borders that are scripted into this Google Sheet?
I have this Google Sheet document that already has functionality built in which applies automatic borders. I don't know how it is doing this. I've tried looking in the Script Editor, but I don't see ...
- 192
1
vote
2
answers
783
views
Transferring an Excel document to G Sheets, how do I fix the slightly broken auto-borders implementation?
I'd like to transfer this Excel spreadsheet to Google Sheets, maintaining the automatic background formatting functionality, particularly in the case of the borders.
Here is the same spreadsheet ...
- 192
0
votes
1
answer
21
views
How do I get data from the 'Statement' sheet to automatically sort into the 'Expenses' sheet?
https://docs.google.com/spreadsheets/d/1xrS8_Lcuqt3rLJgEkV4SHR_XTyGCAn8t9dJyWIUtMJ0/edit?usp=drivesdk
So it will read columns B & C, Everyday > Groceries on the Statement sheet and that amount ...
- 65
-1
votes
1
answer
20
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
1
answer
479
views
Return a list if 2 criteria match in Google Sheets
In an effort to organize staff training: So far I've made the pivot table, but I'd like for the pivot table to extract who needs which training, as a list in one cell. I tried using =JOIN, but I have ...
- 1
-2
votes
1
answer
23
views
How could I convert a Google Sheets in to a excel file?
I am trying to convert the attached document into a excel file ..
It contains a scrapped websites that is constantly updating ...
I want the same feature such as the conditional formatting to occur..
...
1
vote
1
answer
48
views
Sheets or Excel - sum values in column a for each unique date and time in col b
I have two columns. One is a value, the other is a date and time:
A. B.
01/01/21 5.15 3
01/01/21 5.15 2
01/01/21 5.15 4
01/01/21 5.30 2
01/01/21 5.30 3
I want ...
- 23
1
vote
1
answer
24
views
Keeping referenced data organized after sorting
I run a Cub Scout Pack, and when scouts fundraise, a portion of the money they earn can go into an "account". Money can then be used to pay for scouting-related activities.
I'm trying to ...
- 113
0
votes
1
answer
44
views
Getting the latest date of something happening (with conditions)
So basically, I've got two sheets. One (sheet_1) with every single record in the system (over 11000 rows) and the other one (sheet_2) is querying information from that sheet. The relevant information ...
- 1
0
votes
0
answers
30
views
Max sum held over a month
I need help with a formula that needs to calculate the max sum held at one time for each month.
In this example it would be 54 for March (12+13+14+15, held on the 29/03/2021). Month is determined by &...
- 1
1
vote
1
answer
90
views
"Export to Excel Default" when pasting cells into Google sheet from Excel
Sometimes, as I copy data from cells in an Excel sheet (1 or more cells), when I paste the copy into a Google sheet, it will include a first row with the content "Export to Excel Default". ...
- 111
-1
votes
1
answer
1k
views
What's the best way to upload a million row spreadsheet as Google spreadsheet? [closed]
I have an Excel spreadsheet with over a million rows that I would like to have as a Google sheet.
I tried uploading the file as a CSV to my Google drive, which worked. Then I tried opening it as a ...
1
vote
1
answer
207
views
How tell Google Sheets to not change columns width?
I wrote a script that generates excel files. In those files, I add images inside cells. I set the width of the images cell by calculating the total images width.
Here is how it looks in MS Excel:
And ...
- 111
0
votes
1
answer
40
views
How to move “answers” rows into under column “questions”? [closed]
In Excel, how do I move answers from other column to same question under another column, can any one guide the exact formula to achieve this? See example below what needs to be achieved as shown in ...
1
vote
1
answer
606
views
Export Google bookmarks into an excel sheet [closed]
I have a bookmark folder where I have bookmarked various professor's profiles. There are some 40 bookmarked links. I wanted to know if there is a way I can export the bookmark in an excel sheet with ...
- 111
2
votes
1
answer
41
views
How do I write a formula while referencing another sheet in Google Sheets?
So I want to do a CountA on Column A and Column B in Sheet Two and divide Column A by Column B in Sheet One to get the percentage totals. Can I write all that in one line?
=SUM(Sheet!((COUNTA(A2:A516)/...
- 65
1
vote
0
answers
63
views
Changing the value of a single cell based on 2 different cells
By using the IF function I am controlling the value of a cell based on the values of a different cell.
For example: If the date in Column G is bigger than TODAY() then the value of column N is "...
0
votes
3
answers
123
views
In Google Sheets or Excel, how can I write a formula to SUM() a range based on the criteria that none of the respective cells are blank?
What I want to do:
+------+--------+---------+-----------+----------+--------+-------+------------+
| Week | Monday | Tuesday | Wednesday | Thursday | Friday | Total | Difference |
+------+--------+---...
- 101
-2
votes
1
answer
42
views
Turn an excel sheet to a webpage
I have an Excel sheet that we use to calculate sales commissions. We, sometimes, share the file with individuals that we prefer they don't see the formulas. One thought came to mind is to convert the ...
