Questions about using in-cell functions/formulas in spreadsheet software.

**32**

votes

**8**answers

550k views

### How to compare two columns and find differences in excel

I have two columns in Excel that I want to compare and find the differences between them.
Suppose:
Col A has 50 numbers i.e 0511234567
Col B has 100 numbers in the same format

**2**

votes

**3**answers

7k views

### How do I transpose data in columns to rows?

How do I transpose data in columns to rows?
If the data is:
Joe
some city
some state
Bob
no city
no state
and I use Paste Special » Transpose, I get
Joe some city some state Bob ...

**8**

votes

**5**answers

99k views

### How to combine values from multiple rows into a single row in Excel?

I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this:
...

**13**

votes

**3**answers

45k views

### Excel function that evaluates a string as if it were a formula?

Suppose I have a text string like "11+5" or even "=11+5" stored in a cell. Is there a function in Excel that will allow me to actually evaluate that string as if it were a formula?
This would be ...

**5**

votes

**4**answers

32k views

### How to add/subtract months from given date in Excel

Here is my scenario:
I have to populate list with dates and some metadata of upcoming events for the following year in my Excel application. Some of the events are occurring on the same day each ...

**0**

votes

**2**answers

13k views

### Excel - how to either have direct data input or cell value from formula

I would like to either populate a cell value from direct key input or have the cell value derived from a formula.
Example: either directly input a number into cell A4 or have the value for A4 = A2 + ...

**30**

votes

**7**answers

144k views

### How to count number of distinct values in a range?

I've got a large table that is already organized using filters etc. I'd like to add a summary underneath certain columns that contain the number of distinct values in that column.
There's no ...

**6**

votes

**1**answer

2k views

### Conditional formatting to highlight the whole row

I would like to highlight the whole row 5 if J5="T".
I used to do it with the old Excel, the 2010 conditional formatting ignores it. So my rule is like this and it does not work. I used the formula $...

**12**

votes

**4**answers

41k views

### Can you do Regular Expressions in Excel without VBScript?

I'm looking for an Excel function that I can put in a cell to do a regex search of the contents of another cell. Is there anything available to do this? I'd prefer not to have to add VB Script to ...

**11**

votes

**4**answers

8k views

### Is it possible to get the sheetname?

How can I get the sheet name in a cell? (I have more than one sheet, I want to get the current on each sheet)

**5**

votes

**2**answers

15k views

### Excel-Count number of visible cells containing certain string

I have a sheet in which I first apply a filter to a certain column to show "Unique records only". Now I have a column which contains, for e.g., values like:
Applepie
Applesauce
Crabapple
Banana
Mango
...

**2**

votes

**1**answer

544 views

### Excel VBA code Private Sub Worksheet Change Power function not working

I'm making a worksheet where the wind speed defines the potential energy of the location. This value is the basis of a few sheets in the same Excel file. I'm trying to write a code that where I10 is ...

**2**

votes

**5**answers

19k views

### Generate a comma-separated list of cell contents, excluding blanks

To concatenate a certain four cells, I'd use:
=CONCATENATE(A2,",",C2",",D2,",",F2)
This would make it so that...
A2 = "Matthew"
C2 = "Mark"
D2 = "Luke"
F2 = "John"
would result in Matthew,Mark,...

**4**

votes

**4**answers

197k views

### How to Merge Data From Multiple Excel Files into a Single Excel File or Access Database?

I have a few dozen excel files which are all of the same format (i.e. 4 worksheets per Excel file). I need to combine all the files into 1 master file which must have just 2 of the 4 worksheets. The ...

**1**

vote

**2**answers

57k views

### Looking-up the values of one worksheet in another worksheet

Basically, I could do this manually, but as the data is huge, it would take a lot of time. I am sure there must be a formulaic way of doing it.
I have two worksheets in Excel.
Worksheet 1.
Column-A ...

**0**

votes

**6**answers

25k views

### Convert Excel Text string to Time

I'm trying to Export from an OLD System that stored Time in / Time out as a text string in 24 hour time.
For example 3pm, is entered as 1500 (no colon)
Is there a simple way I can convert these ...

**0**

votes

**2**answers

2k views

### VLOOKUP with multiple criteria

I have a workbook with 2 sheets: spg and data
From the data sheet I wish to do a vlookup in the spg sheet, based on the value of multiple columns in spg.
I can do a =VLOOKUP(H5,spg!$D:$I,6,0) but ...

**16**

votes

**16**answers

153k views

### How to get the current column name in Excel?

What is the function to get the current line number and the current column name for a cell in Excel?

**4**

votes

**4**answers

77k views

### Extracting Data Between two Characters in Excel

I have the following format in Excel and I am trying to extract the data between the characters:
First Last <flastname@xxxx.com>
I need to extract the email address between < and > into ...

**4**

votes

**2**answers

6k views

### Excel TEXT function does not work properly

Let say I have the following values in the cells
A1 : `=Now()` which displays 17.03.2014 13:45
A2 : 12.03.2014
Both are in date format and I have the function in another cells like this:
B1 : `=...

**3**

votes

**1**answer

74k views

### Can Excel use formula-input data for cell references?

I have a cell in Excel, to which I want to pull data from another cell that is defined as a value in a third cell.
For example:
A1=5
A2=10
A3=insert syntax here, using A4 as source for cell ...

**1**

vote

**1**answer

76 views

### Drag a formula, so that it increases by 17 each time

I have the following formulas on the third sheet of my workbook. They are simply cell references to the first sheet; on my 'output' page, I want to display every 17th cell of the 'input' sheet. ...

**1**

vote

**2**answers

28k views

### How to identify cells in Column A that contain any of the values in Column B

First off, I'm not that advanced of an Excel user. I'm certain Excel can do what I'm about to ask, but I've yet to find a way in any of my attempts or searches.
My question is very similar to Find if ...

**1**

vote

**2**answers

1k views

### Look up a value in a list and return ALL multiple corresponding values [closed]

The basic answer to my question is provided here: How to look up a value in a list and return multiple corresponding values. However, once the formula is entered, I need to drag down, or copy/paste ...

**1**

vote

**3**answers

5k views

### Excel: how to count number of distinct values in a range with condition?

I read and tested quite a lot, but I still not able to come up with a solution for my problem.
The close solution I found is the second one proposed in article below:
Excel: how to count number of ...

**0**

votes

**3**answers

3k views

### Highlighting duplicate column-pairs in Excel

Example, this is the data in Excel:
1 2
2 2
3 4
3 4
3 3
2 2
2 2
1 2
The rows which have consecutive duplicate column-pair values (in bold font) should be highlighted:
1 2
2 2
3 4
3 4
3 3
2 2
2 2
1 ...

**0**

votes

**1**answer

3k views

### Can't get the formula to autofill correctly

In Excel I entered two formulas manually:
B3 = C3 * C2
B4 = C4 * C2
I have a lot of rows so I wanna copy the formula down.
The problem is, I select them both and then pull down.
What happens is ...

**41**

votes

**4**answers

113k views

### Is it possible to sum an entire column without setting explicit cell boundaries in Excel?

I'd like to sum all the values in a column starting from a certain minimum value, but I don't want to give an upper bound because any value in the table will be appropriate for summing (minus the ...

**7**

votes

**1**answer

36k views

### Excel, how to find column number of min value?

I have a table in Excel.
Can anyone help me find the column number of the cell where the min value of the row is?
Example: 200 is the min value in row1 and has the column number 1.
Is there a ...

**0**

votes

**2**answers

80k views

### How do you write an Excel formula that will paste a specific value in a different cell?

So basically I want to write a formula that will check a condition, and if that condition is met, then I want to paste a specific line of text in a different cell. I should note that I do not want the ...

**11**

votes

**5**answers

72k views

### How to get time difference as minutes in Excel?

I have time values in two cells in an Excel sheet and I need the difference of
the times in minutes.
The cells in the Excel are like below:
A B C
1 6:38 8:23 1:45:00
A is ...

**4**

votes

**2**answers

5k views

### Counting occurences of a character in an excel spreadsheet

Feel silly asking this question, but I need some help!
I want to count how many * appear in a range of 5 cells.
The cells will range from 0-7 * each.
I was thinking of using the =COUNTIF(A2:A:6,"*...

**3**

votes

**2**answers

92 views

### Combine multiple rows to one (many-many relationship)

Could anyone help me write an Excel formula to do the following? I am trying to merge product codes for a customer from a flat file extract. Each customer can have multiple products, and vice ...

**3**

votes

**2**answers

36k views

### Conditionally format cells based on match in another sheet

I have an Excel spreadsheet with 2 worksheets. The first is just a header row and a single column of item names. The second is a list of item groups, with a header row and a title in the left most ...

**3**

votes

**3**answers

5k views

### Multiplying values in Excel 2010 (Same cell)

Another excel question. I'm using Excel 2010.
If for example I have a value in cell A1, lets say 2.59
What I want to do is multiply that cell by 1.333 but in the same cell
I don't want to have to ...

**1**

vote

**1**answer

4k views

### Lookup list dependant on the contents of another cell

hope someone can help to crack this one?
I've got a table on my data entry tab (sheet 1) where each row includes two data validation drop down lists (source is on sheet2). I want the options in the ...

**0**

votes

**1**answer

3k views

### How to Mirror cells in excel 2013 [closed]

I want to make an excel file for sales management of my company. the excel I have created has 4 sheets and every sheet is handled by different persons.
I want to dedicate the first column of these ...

**0**

votes

**1**answer

46 views

### Excel: Changing and updating a future value based on past occurrence and lookup in a table

I have a big dataset, dataset is in such a way that each time a customer is called a row will be created. So the customer Number is the unique ID here. Based on their current and past response type, ...

**0**

votes

**2**answers

81 views

### Referencing text from one cell into an equation in Excel

I have text in a cell (A1) that is also the name of a worksheet within my workbook. How do I go about referencing a specific cell on that sheet? I want it to look like the formula below without having ...

**3**

votes

**1**answer

1k views

### Excel Large Function with Duplicates

I'm trying to apply a LARGE function to get the 5 largest values from a column ignoring duplicates. I did some searching around and found some proposed solutions but they don't seem to work for me. In ...

**3**

votes

**2**answers

7k views

### How can I change a cell's background color based on the cell's current value?

I have a column with function values of True or False:
=IF(ISNA(A1), false, true)
from row 1 to 5000+ rows. I would like to mark all the False as red background color or text as red. Is there any ...

**3**

votes

**2**answers

8k views

### Sum contents in column from starting cell on down without setting an explicit last cell index

I have an Excel spreadsheet and would like to sum the numbers in a column starting at one cell (say, B5) and continuing down to the last number in that column.
I know I could just enter a large ...

**2**

votes

**1**answer

4k views

### How to copy a column with conditional format by “value” - Libreoffice 4.1

I have a column that contains the data and a set of rules for the conditional format of the cells.
i.e. the conditional format changes the background colour of some cells if they are negative.
I ...

**2**

votes

**2**answers

337 views

### Can I use the Excel Match function to match on more than one type of value

In another question I posed recently I was shown how to use the Excel Match function to query my data in order to produce a summary type table:
Creating a formula that queries data in Column B based ...

**2**

votes

**1**answer

341 views

### how to determine which values in a given set of cells will add up to a numerical range

In other words, I'm looking for a sum range that consists of combinations of cells from an array. Given a set of numbers:
1 720 56 17 59 120 153 203 39 1 690 583 582 561 256 310 232 95 108 16
26 ...

**2**

votes

**1**answer

21k views

### How to hide formula results until conditions are met

I am completely new to making spreadsheets with Excel and even more dumb when making formulas. I have come to surprise myself how much I have learned, but now I am stumped and could really use some ...

**2**

votes

**1**answer

3k views

### Using Excel Linest function with blank cells

I need LINEST function to be applied with blank cells to get the slope parameter as well as the associated standard error. I can get the slope parameter with a normal function that may resolve the ...

**1**

vote

**3**answers

5k views

### How can I count unique comma-separated values in Excel 2010

I need to build a formula to walk a column of comma-separated values and count the number of unique values seen. I am using Excel 2010. I have a screenshot of some example data...
The output of ...

**1**

vote

**1**answer

8k views

### Indirect function for worksheet names in Excel?

I am wondering if there is an equivalent to the indirect function for worksheets or workbooks instead of cells. What I mean by that is I am updating a rather large and unwieldy workbook for payroll ...

**1**

vote

**2**answers

1k views

### Excel conditional formatting of row

Can someone please tell me how to highlight multiple cells in a row based on some values in any one of a given number of cells?
For example, if in Row 2 any of the cells from D2 to L2 have value "A" ...