# Questions tagged [spreadsheet]

Questions about spreadsheet software in general and interoperability between spreadsheet programs.

607
questions

**1**

vote

**1**answer

10 views

### Searching a value of partial text from another sheet

My scenario is, I have 2 worksheet:
Sheet1 contains the name of the restaurants, and Sheet2 contains the name of restaurants to avoid.
My issue is, I use a formula in conditional formatting that only ...

**0**

votes

**0**answers

7 views

### Is there a way to return certain rows on Google Sheets that I have tagged?

I have a large list of books that I would like to tag and be able to filter out the row/book I am searching for without having to scroll through the entire sheet.
For example:
Book A humor, non-...

**2**

votes

**1**answer

46 views

### CONCATENATE cells in a column IF condition in other colum is met

Using Google sheets (or Excel if Google sheets can't do it). I'm trying to concatenate any cell in B column, if its corresponding A cell is "include". The columns are very long and therefore ...

**0**

votes

**1**answer

21 views

### Insert big text into multiply rows by keyword into Excel or google spreadsheets

If I have multiline text like
Line:
x1 = 10, y1 = 100
x2 = 30, y2 = 40
Line:
x1 = 100, y1 = 120
x2 = 130, y2 = 140
And so on. Is it possible to insert it at once at Excel or google spreadsheets so ...

**0**

votes

**0**answers

14 views

### Google SpreadSheets query: how to join “grouped by” cells in a list? [closed]

Suppose I have this table:
| Name | Amount | Category |
| name_1 | 10 | A |
| name_2 | 2 | B |
| name_3 | 3 | B |
| name_4 | 6 | C |
I know how to ...

**0**

votes

**1**answer

7k views

### Making a cell mandatory based on previous cell entry

I am looking to achieve the following:
Column H has 5 potential inputs from a data validation list:
Pass, High, Medium, Low & N/A
If Column H is either High, Medium or Low, I need Column I to ...

**0**

votes

**1**answer

18 views

### Excel - sort one spreadsheet by exact order of second spreadsheet based on one column with same data

I trying to find a way how to sort one spreadsheet into the same order like second spreadsheet - based on their one same column for example.
Here's an example what i need, i will make more sense like ...

**3**

votes

**1**answer

3k views

### Numbers conditional formatting based on a different cell in the same row

I have a Numbers spreadsheet, with "quantity required" and "quantity in stock" columns, then each part is on a row. I'm trying to use conditional formatting to turn a cell yellow when # required = # ...

**0**

votes

**0**answers

15 views

### Excel on notebook

I want to change all numbers in column I to be - (negative)
if the corresponding words in column B are SELL
Example
Col B row 1 “sell” then col I row 1 -123
Col B row 2 “buy” the col I no change
Col B ...

**0**

votes

**2**answers

3k views

### How to add prefix or suffix to range of cells in Excel?

I have cell A in excel full of images name without the extension I want to add ".jpg" as a suffix to these ranges of cells however I need to skip non text cells by not adding anything. Any help to ...

**1**

vote

**1**answer

15 views

### Combine one set of time series data with another when some dates are missing in one data set

I have a LibreOffice Calc spreadsheet which contains two sets of time series data e.g.
|Date-A |A | | | |Date-B |B |
|2020-01-01|101| | | |2020-01-01|201|
|2020-01-02|102| | | |2020-01-02|202|
...

**1**

vote

**2**answers

44 views

### Excel: How can I find all the occurrences of the matching columns

Consider I have the following table (unsorted):
Name
ID
Account
John Doe
1
1234
John Doe
1
6524
Jane Doe
2
6346
Jane Doe
2
1241
Jane Doe
2
3634
And another one like this:
Name
Account 1
...

**0**

votes

**1**answer

45 views

### i want to return value( eg;- sum) only if the source is not blank,other wise return blank !!not zero!! in excel, wps

see this screenshot for reference
see this screenshot..
I used SUM formula in the botton cells to get sum of all values from each row , the bottom cells display TOTAL
and i will assign the TOTAL ...

**0**

votes

**1**answer

27 views

### In LibreOffice Calc, why would sort buttons and menu items suddenly become disabled?

I was creating a spreadsheet in LibreOffice Calc. Several times, I used the "Sort Ascending" and "Sort Descending" toolbar buttons. All was well, and everything was working as ...

**0**

votes

**1**answer

308 views

### Hyperlink to certain text in excel page?

I'm trying to create hyperlinks to certain points in a large data sheet. Each are separated by a block of text identifying what is there. I know I could create the hyperlink to certain cells in the ...

**1**

vote

**3**answers

11k views

### Merge multiple columns into one

In Excel, I'd like to merge/combine the data of multiple, nonconsecutive columns into one (excluding the headers, preferably on another sheet).
Example: I have a sheet containing rows of names which ...

**2**

votes

**6**answers

3k views

### Good tools to visualize spreadsheets?

I have a few CSV and XLS files that I would like to analyse or "visualize" online (like in charts etc).
I tried Google Fusion Tables, but I want to know if there are any alternatives.
Also suggest ...

**0**

votes

**2**answers

71 views

### Ascending order of Excel rows

I have the following rows in Excel:
How can I put them in an ascending order (i.e. notice how the row starting with 12 comes before that starting with 118).
Thanks.

**3**

votes

**3**answers

54k views

### Put a checkbox in a cell in Excel v15 for Mac

I am trying to make a checklist in Excel.
Basically I am trying to make a cell:
Into a checkbox that can be checked or unchecked, and saved with the spreadsheet:
It's super-duper easy to do it in ...

**5**

votes

**1**answer

32k views

### Excel 2007 - changes on one sheet affecting other (without cell references)

Some strange behaviour I've encountered twice in Excel is that changes made to one sheet would be duplicated on another sheet in the same workbook. The first time, I typed values into a few cells on ...

**3**

votes

**4**answers

279 views

### keyboard equivalent of dragging the little black square to generate sequence

What is the keyboard way of generating a sequence?
The pic below shows libre office calc. In order to generate the sequence of numbers 1 to 10. I simply enter few numbers of the sequence (in ...

**1**

vote

**3**answers

7k views

### How to filter a spreadsheet on multiple filter values

I've got a spreadsheet of addresses with the following columns: First Name, Last Name, Street Address, City, State, and zip code. I'd like to be able to filter or otherwise extract a subset of these ...

**0**

votes

**1**answer

25 views

### How to copy rows of specific columns based on a criteria (and then continuously update)?

I have 2 sheets, let's call them sheet 1 and sheet 2.
In sheet 1, I have some data where essentially at its core its a Scoring tally, something like this:
Name
Age
Gender
Score
Jack
12
Male
155
...

**0**

votes

**1**answer

34 views

### How to get LibreOffice Calc to recognize dates in YYYY/MM/DD format?

I've been given a large tab-separated-values (TSV) text file that contains dates in the format YYYY/MM/DD.
I tried copy and pasting the dates into LibreOffice Calc, but it didn't recognize the strings ...

**0**

votes

**2**answers

4k views

### Referencing a single row to multiple rows in Excel

I have a worksheet with a list of sales, one sale per row. In order to import this data into my accounting software, I need to create a separate worksheet that formats the data differently. Each row ...

**1**

vote

**1**answer

35 views

### How to calculate 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**

vote

**7**answers

136k views

### =if() function is not working like it should be

I am trying to use an IF function in a formula in MS Excel 2013, but every time the IF function ignores the condition I wrote and goes to the true value. I checked my IF statement over and over, and I ...

**1**

vote

**2**answers

33 views

### Need to simplify date entry in Excel spreadsheet

Every month I need to do a large spreadsheet for billing purposes.
Among other columns I have one start date and another for end date, formatted as dd-mmm.
To enter this date in the spreadsheet for ...

**3**

votes

**4**answers

3k views

### How do you find the maximum value in a column, skipping to every nth value?

Using LibreOffice's Calc program (nearly the same functionality as Microsoft Excel) I have a set of numbers in column T, which represent numbers for the week (with labels in S). Each week's worth of ...

**8**

votes

**4**answers

110k views

### Too much text for an Excel cell — how to make the cell scrollable

What do I do when I have more text in an Excel cell than I want to show in the allocated space?
Something making it "scrollable" would be preferred.

**1**

vote

**0**answers

8 views

### Searching indexed spreadsheets from Windows 10 explorer “search”

I have a client that used to use Windows 7, and was able to search directly from the Windows explorer (start menu) to find spreadsheets by searching a number that was on column B within. Now that they'...

**0**

votes

**3**answers

128 views

### Excel MAX IF Function To Ingore Zero Date (1/0/00)

=MAX(IF('[YTD -2021.xlsx]OB'!$H$2:$H$23967=$C5,IF('[YTD -2021.xlsx]OB'!$J$2:$J$23967=$B$1,'[YTD -2021.xlsx]OB'!$A$2:$A$23967)))
I want to ignore Zero date and result should give me 0 or blank instead ...

**2**

votes

**1**answer

3k views

### Excel - compare three cells, if same then something, if not then something

=IF(F9=G9=B9; H9*C9;"Problem")
This is the formula I'm using, but it always results in "Problem", even when the three cells have same text in them.
I want to compare the cells, and if they are the ...

**0**

votes

**1**answer

48 views

### Based on cell formula, display another cell value

I want Excel to look into the formula of a cell and then display a different cell based on that. confused yet?
Column A on sheet 1 has a size and column B on sheet 1 has a tolerance.
Could be same ...

**0**

votes

**2**answers

5k views

### How can I pull specific rows in Excel into a new spreadsheet

I have several spreadsheets containing data I need to analyse. I have a randomized list of numbers that dictate which rows in each spreadsheet I need to code for my data sample.
Is there a formula I ...

**0**

votes

**2**answers

6k views

### How to add multiple digital signatures to an Excel spreadsheet?

I have an Excel spreadsheet that lives on a shared folder, that everybody can access. On that spreadsheet, I have a worksheet with a list of tasks that have to be completed, but not necessarily by the ...

**1**

vote

**1**answer

41 views

### Sum and lookup over range in spreadsheet

I have a google spreadsheet that looks like this
Items
Item
Value
A
10
B
12
C
15
D
20
Calc
Total
Items
A
A
B
A
A
C
C
C
In Total I want to have the a formula that sums the value of the ...

**0**

votes

**3**answers

66 views

### Is there a way to define a range with infinite row or column number in a spreadsheet program?

When defining a named range in a spreadsheet program, is there a way to define a range that covers for example A1:A[*infinity*], so I will be sure that the range will always include all rows, whether ...

**0**

votes

**3**answers

51 views

### Distinguish between A* and A in excel

I’m creating a spreadsheet to be used in schools that use various countifs, subtotals etc to look up data. A big problem I have is that I need to distinguish and count separately students on an A* (A ...

**2**

votes

**2**answers

3k views

### How to fix a corrupted LibreOffice spreadsheet

I have a substantial multi-sheet LibreOffice worksheet that seems to be throwing a lot of errors in LibreOffice, including crashes.
Any idea of how to get it to work as expected?
I'm thinking ...

**3**

votes

**3**answers

13k views

### Decimating rows in spreadsheet

Is there such an option to preserve only every 10th row in my spreadsheet? It can be Excel or Calc. Quality standards want some noise to be added.

**7**

votes

**1**answer

6k views

### Apple numbers: How can I create a filtered column (B) with only unique values from column (A)?

I'm using Apple Numbers.
I have a table with data like:
A | B
---------+---------
Spain |
U.K |
Spain |
Germany |
U.K |
Italy |
And I would like to set table B to include ...

**0**

votes

**2**answers

6k views

### Simple pattern with Excel COUNTIF to match a substring?

How do I fix this formula: =COUNTIF(A1:A10,"*jim*") to match anything containing the sub-string of "jim" but from a cell, as: =COUNTIF(A1:A10,C11) where cell C11 contains the text "jim"?
Perhaps ...

**0**

votes

**1**answer

42 views

### How to group multiple date|data columns to one single date column on the left?

I've got a large set of data that looks like:
and I've been trying to format it so that instead of having a column of dates for each item, there's only one column of dates on the left. The problem is ...

**1**

vote

**1**answer

7k views

### Copy reference instead of value/formula in Excel?

I have some cells from one table I which I want to reproduce in another table, initially in the same order (but later, I'll mess this order up by moving cells around). For the sake of discussion ...

**0**

votes

**0**answers

29 views

### How to isolate list of values in Excel spreadsheet

I have a randomly generated list of 5,000 filenames. I'd like to isolate this list from a seperate excel spreadsheet of 60,000 rows, in order to extract the respective values from other columns.
How ...

**0**

votes

**0**answers

14 views

### How to return the sheet name of a given cell of a query result

I have scrubbed the internet for answer to this and and either I am asking the question the wrong way or it isn't possible.
Here is a example template of what I am wondering about:
https://docs.google....

**0**

votes

**1**answer

3k views

### How to get plain text, colon separated file into spreadsheet with data separated into columns

I received a digest of registrations for events. The fields are separated by colons and in plain text. Can I copy and paste this info into libreoffice spead and get it displayed in columns?

**0**

votes

**1**answer

15 views

### Count number of rows with a specific MONTH in the date of a specific cell and a boolean condition in another cell

Let's say I have a number of dates and whether they occupy full or half a day...
DATE | WHAT | HALF?
...
2021-4-1 | Something Something | false
2021-4-2 | Something Something | ...

**3**

votes

**1**answer

52 views

### Filtering over merged downwards cells in MS Excel-2016

Note: This question has been asked back in 2010 on this site here. And yet, I would like to post it one more time, showing what I did and what didn't work. Besides, Excel has changed quite a bit since ...