Questions tagged [microsoft-excel]
Questions with this tag should ONLY be about how Microsoft Excel interacts with a specific web application. General questions about Microsoft Excel are off-topic here but can be asked at Super User.
86
questions
0
votes
0
answers
11
views
Data validation with 2 conditions [migrated]
I am looking for a conditional formatting formula that validates two conditions from another sheet.
I have Sheet1 and Sheet2: Sheet2 has daily records of workers such as time in and off, Sheet2 ...
2
votes
1
answer
238
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,206
2
votes
1
answer
62
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
1
vote
2
answers
70
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
...
- 13
0
votes
1
answer
378
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 ...
1
vote
1
answer
101
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
20
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
85
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
269
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
2
votes
2
answers
2k
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/...
- 25
0
votes
1
answer
66
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 ...
0
votes
1
answer
78
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
43
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
12
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
15
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
48
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 ...
- 25
1
vote
0
answers
46
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
23
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
179
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
0
votes
1
answer
942
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&...
- 231
0
votes
1
answer
136
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
37
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
32
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
645
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 ...
- 139
0
votes
2
answers
693
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?
- 139
-1
votes
1
answer
3k
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
52
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 ...
0
votes
1
answer
89
views
Unique numeric ID
I have a spreadsheet that gets its data from a google form. I use a formula I found on a forum to generate a unique ID and assign it to every entry avoiding repetition.
=arrayformula(if(len(B2:B);&...
- 9
1
vote
1
answer
644
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
769
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
41
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
294
views
Google Sheets Conditional Formatting rows based on color adjacent
I am using Google Sheets to make a homework spreadsheet and have used conditional formatting to arrange assignments by the due date and also each class has a color. When I type in a class it ...
Sarah
1
vote
1
answer
102
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 ...
- 706
0
votes
1
answer
14
views
Whats wrong with this formula in Google sheets? Im trying to get info from another sheet, but I need the cell to be dynamic
Heres the formula:
='Sector info'!(CONCAT("C", B2)) there's 2 parts to it, the part before the ! which references another sheet, and the second part, that lets me change the row im looking ...
0
votes
1
answer
537
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 ...
- 87
1
vote
2
answers
310
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 ...
- 87
0
votes
1
answer
20
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
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 ...
0
votes
1
answer
290
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
-1
votes
1
answer
22
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
42
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
0
votes
1
answer
18
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 ...
- 103
0
votes
1
answer
28
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
42
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
0
votes
1
answer
94
views
Data validation with specific condition
I have special conditions for column in Google Sheets which are:
should only be filled with numbers
no special characters allowed
no alphabets allowed
no space allowed
should be 16 digits in total
I ...
- 1
-1
votes
1
answer
840
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
164
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
379
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