# Tag Info

### Formula to see if a series of numbers is increasing in value?

You can use array formulae to accomplish this: Assuming your data is in A1:J1, then enter the following formula into a cell =AND(B1:J1>A1:I1) and press Ctrl+Shift+Enter to create an array formula -...
• 1,556

### How to create a formula for every row in a column in Google SpreadSheet?

The suggested answers work well for small sheets but I had thousands of rows and using the mouse or the keyboard to select them was simply too time consuming. The ARRAYFORMULA method works but it's ...
• 366
Accepted

### How to find cells that reference the current cell in LibreOffice Calc spreadsheet?

LO Calc has built-in functions for that equivalent to Excel's audit functions. In Calc, it's called Detective, and is available from the Tools tab of the menu. Tracing precedents and dependents also ...
• 27.7k
Accepted

In Google Sheets: =INDEX(SPLIT(A1,","),2) Where the 2 is the index you want. In Excel: =TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(2-1)*999+1,999)) Or this array formula: =INDEX(TRIM(MID(...
• 23.5k

### Formula to see if a series of numbers is increasing in value?

Since you tagged google-spreadsheets, you can also use an ArrayFormula to simplify this in one formula: =AND(ARRAYFORMULA(B1:J1>A1:I1)) You can adjust the J1 and I1 accordingly if the number of ...
• 181

### Is it possible to copy & paste a pivot table in a spreadsheet?

Select the Pivot Table On the Analyze Ribbon - Click on Select-> Entire Pivot Table Right Click Copy in the pivot table area Go to the location where you want to place the copy of the pivot table and ...

### How to create a formula for every row in a column in Google SpreadSheet?

I found all of these solutions very frustrating and confusing also. I will warn you though, this will replace whatever is currently in the cells, but as it is a formula this should not be a problem. ...
• 413

### Highlighting every other row in LibreOffice (or OpenOffice) without conditional formatting?

From menu: Format → AutoFormat Styles.. . You could add your own. Another option is to use macros (as in ngulam's answer). Color2Rows is an extension that adds a button to the tool bar, for ...
• 668
Accepted

### Highlight the cell if greater than value in the next column

Here are the steps for Excel. Assuming the first value is in A1, select A1 to B3 click Conditional Formatting > New Rule click Use a formula to determine enter this formula into the formula box and ...
• 23k
Accepted

### Excel is changing my time input (32:00:00 becomes 8:00:00)

Excel isn't changing the cell's value. This is evidenced by the fact your sum operation is returning the correct result. Instead, Excel is formatting the cell in 24-hour time notation. In this ...

### Display real-time filtering data from another worksheet in Microsoft Excel

Try to use Power Query to get the result: Select Range- go to Data- select From Range/Table- Enter Power Query Editor: Filter Gender Column- Close and Load to New WorkSheet: If you update data in ...
• 2,973

4,8,2 Formulas for each number. =LEFT(A1, SEARCH(",",A1,1)-1) =MID(A1, SEARCH(",",A1) + 1, SEARCH(",",A1,SEARCH(",",A1)+1) - SEARCH(",",A1) - 1) =RIGHT(A1,LEN(A1) - SEARCH(",", A1, SEARCH(",", A1) + ...
• 28.1k
Accepted

### Multiline cells are displayed as single line cells

Once you have data in cells, run this short macro: Sub FixHardReturns() Dim CH As String, r As Range, v As Variant CH = Chr(10) For Each r In ActiveSheet.UsedRange v = r.Value ...
• 19.5k
Accepted

### how to pass a string value into COUNTIF?

The cell reference acts as a variable: =COUNTIF(A1:A10,C11)
• 780
Accepted

### Include an "at" symbol ("@") in a text cell

Go to Tools -> AutoCorrect Options -> Options Uncheck URL Recognition Press OK.
• 19.7k
Accepted

### How to combine multiple formulas in 1 cell

You can nest functions inside each other, I think this is the one you need: =IF(AND(C18>0, C6>J11), J11, IF(AND(C17 <K5, C6>J10), J10, C6))
• 21.7k
Accepted

### SUM depending on column value

You can do this with SUMIF(): =SUMIF(C:C,"Niels",B:B) You can also use the data in E directly in the formula so you do not need to retype the formula for each, in E2 put just Niels =SUMIF(C:C,E2,B:...
• 23.5k
Accepted

### How to compare two Excel spreadsheets?

It’s convenient that your spreadsheet uses 50 columns, because that means that columns #51, #52, …, are available.  Your problem is fairly easily solved with the use of a “helper column”, which we can ...

### How to fix Excel and prevent it from converting copy-pasted or imported strings to dates even when cells are formatted as text

Here are three ways that meet the requirements and a 4th way that's more situational. Yes, 2 of them you said not to work by OP. They work for me on Office 365 Pro Plus so they will work for many ...

### Using VLOOKUP for multiple lookup values, then summing the outputs

Sum a SUMIFS: =SUMPRODUCT(SUMIFS(Sheet1!B:B,Sheet1!A:A,A2:A5)) This will iterate the various parts of the meal in A2:A5 and sum the outputs of the SUMIFS.
• 23.5k

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

concat the regex string and the cell string within the formula as such: =COUNTIF(A1:A10,"*"&C11&"*") let me know if this helped
• 161
Accepted

### How to combine two rows in a spreadsheet into a single row?

In A3 enter: =IF(COLUMNS(\$A:A)<COUNTA(1:1)+1,INDEX(1:1,COLUMNS(\$A:A)),INDEX(2:2,COLUMNS(\$A:A)-COUNTA(1:1))) and copy across. This should work for different lengths of data in the first two rows. ...
• 19.5k
Accepted

### Excel: How to extract repeated values from randomly ordered list?

In T1 you can use the below formula, then drag down for the other names. =SUMIFS(Q:Q,O:O,S1,P:P,"HEIGHT")/SUMIFS(Q:Q,O:O,S1,P:P,"WEIGHT") Ahh this answer was to go with the ...
• 7,505
Accepted

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

In Excel 2019/365 you can do: =TEXTJOIN(,TRUE,IF(A1:A4="include",B1:B4,"")) In Google Sheets: =ArrayFormula(TEXTJOIN("",true,IF(A1:A4="include",B1:B4,"&...
Accepted

### How do I return a column value based on whether another column is within an array range - Libreoffice Calc

VLOOKUP will not work as the lookup column is right of the return column. Use INDEX/MATCH: =INDEX(A!A:A,MATCH(A1,A!B:B))
• 23.5k
Accepted

### How to sum a column based on existance of value in another column

The Syntax for SUMIF, according to the relevant microsoft support article: SUMIF(range, criteria, [sum_range]) For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in ...
• 3,306
Accepted

### How to get the type of a cell in Libreoffice Calc?

You should use the TYPE function which: TYPE Returns the type of value, where 1 = number, 2 = text, 4 = Boolean value, 8 = formula, 16 = error value, 64 = array. If an error occurs, the function ...
• 19.7k
Accepted

### Is there a spreadsheet version of .rtf?

It depends on your requirements. Comma separated values (.CSV) are just text files. Every spreadsheet program is able to open and import them. Microsoft's .XLS format is rather universal. Most ...
• 73.8k