# 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 -...

### 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 ...
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(...
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 ...

### 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 ...

### 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 ...
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 ...
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 ...

### 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?

Very similar to ceoliphant's answer but a little more straightforward, simply add one formula to C1: =ARRAYFORMULA(iferror(A:A*B:B*1.6))

### 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. ...

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

After you write your forumla, you can double click the bottom, right corner of the selected cell with the blue box, to copy the data down the column down as long as a neighboring cell has data. This ...

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) + ...
Accepted

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

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

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

Go to Tools -> AutoCorrect Options -> Options Uncheck URL Recognition Press OK.

### Format percentages in Microsoft Excel

Found this answer on a different site: Right Click, Format Cells, Custom. Manually type in "0.0\%" without the quotes. If you want more decimal places to show, just add in zeroes. "0.000\%" ...
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))
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 ...
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:...
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 ...

### Microsoft Excel: display realtime filtering data from another worksheet

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 ...

### 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.

### 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
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. ...
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 ...
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,"&...