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

**3**

votes

**4**answers

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

**5**

votes

**1**answer

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

**26**

votes

**7**answers

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

**23**

votes

**8**answers

324k 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

**6**

votes

**4**answers

55k 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:
...

**0**

votes

**2**answers

5k 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 + ...

**5**

votes

**2**answers

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

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

**0**

votes

**2**answers

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

**0**

votes

**6**answers

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

**13**

votes

**16**answers

96k 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?

**10**

votes

**4**answers

29k 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

3k 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)

**0**

votes

**1**answer

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

**3**

votes

**2**answers

2k views

### Excel conditional formatting based on a formula

I would like to use Excel's conditinal formatting of a particular column based on values in two other columns.
I have values in columns A, B, C and D.
column A has text values (any value user ...

**1**

vote

**3**answers

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

**0**

votes

**2**answers

92 views

### Searching and Inserting Data Based on Date Range

In another post, a formula was given that was meant to help search and insert an entry if the date associated with the entry was less than the date on another sheet. The formula was as follows:
=IF(
...

**0**

votes

**2**answers

116 views

### Using Index Match Conditionally to Indicate Whether

I feel pretty comfortable with INDEX MATCH, but I came across a problem I'd like to use it on where I'm not quite sure it works. I have two sheets: the first smaller sheet has a small subset of ...

**0**

votes

**3**answers

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

**9**

votes

**3**answers

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

**3**

votes

**3**answers

21k views

### Subtracting cells only if they have a value in Excel

I want to subtract two cells in excel but only if both cells have a value. More specifiaclly in my case the formula in cell E2 is currently =D2-C2 but I only want this to be calculated when D2 has a ...

**0**

votes

**3**answers

260 views

### how do I average the lowest 3 of the final 6 numbers in a row of 22? Some cells may be blank

How do I automatically average the lowest 3 of the last 6 numbers in a row of 22 that adds one number per week? Obviously I need at least 7 numbers to begin using this formula. Also, some cells in ...

**23**

votes

**4**answers

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

**0**

votes

**2**answers

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

**2**

votes

**1**answer

247 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

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

**2**

votes

**1**answer

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

**2**

votes

**2**answers

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

**1**

vote

**1**answer

53 views

### Excel formula to show FORMULA

Ok, guys. I am 100% sure that a few days ago I saw and tested a real built-in Excel formula for showing a formula as text in any cell. Now, when I need it, I cannot find it!!! No VBA, no tricks. Just ...

**1**

vote

**3**answers

1k views

### Excel function working like SQL group by + count(distinct *)?

Suppose I have an Excel sheet with below data
CODE (COL A) | VALUE (COL B)
==============================
A01 | 10
A01 | 20
A01 | 30
A01 | 10
B01 | ...

**1**

vote

**3**answers

69k 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

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

**1**

vote

**4**answers

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

**1**

vote

**2**answers

33k views

### If an Excel row contains particular words, return that row, highlight that row, or give Y/N for that row

I have a sheet containing thousands of cells in a column. These cell contains names of different securities.
Some names of these securities contains a particular string such as "C/O".
How can I use ...

**1**

vote

**1**answer

1k views

### Excel array formula for max with multiple/ variable criteria

G'day there,
I have a sheet that contains 365 rows, representing dates from a referred Start Date (for the following year). There are about 30 columns each of which has an agency name.
The cells in ...

**1**

vote

**2**answers

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

**0**

votes

**1**answer

35 views

### Select text to display based on matching a symbol in another row

How do I write this excel cell formula?
In Row 1 is a list of words and they each correspond to an "!" or an "X" in Row 2. Only one word is given an "!".
[A] [B] [C] [D] [E] ...

**0**

votes

**1**answer

155 views

### How to fill down formula so that range references are offset by 5 rows rather than by 1 row [duplicate]

How can I get Excel to increment ranges arguments by more than one row when I fill down a formula?
For example, if I want Excel to keep averaging the next five numbers in a column, how do I set the ...

**0**

votes

**2**answers

56 views

### How to assign rows in multiples of a number (Excel/Google Docs)

For example, I want the first entry in Column B (B1) to show A5, then B2 shows A10, then B3 shows A15.
How would I go about doing this?

**0**

votes

**2**answers

202 views

### Calculate date and time differences in Excel

I am trying to use Excel 2013 to put together a workbook for timesheet tracking. As an example, time worked yesterday needs to be entered by 10:00 AM on the next business day. Weekends become ...

**0**

votes

**1**answer

195 views

### Excel count all rows that contain certain values for certain fields

I have a sheet with a couple hundred rows. Each row has an 'owner' and a pass/fail field. I am making a second sheet that has statistics for the users. So for each user it will count the number of ...

**0**

votes

**1**answer

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

**0**

votes

**3**answers

3k 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

**2**answers

774 views

### Excel: Check if column A has any values from Column B, and delete that value from Column A

So I have two columns in Excel with these values suppose:
Column A | Column B
---------|---------
12 | 626
241 | 129
35 | 412
412 | 88
532 | 663
626 |
723 |
88 ...

**0**

votes

**2**answers

4k views

### Excel Sum that excludes duplicates

I have data in the following format:
Id Value
------------
1 20
2 40
3 20
3 20
4 50
I want to sum the value column while excluding duplicates from the id column. In this ...

**0**

votes

**2**answers

754 views

### How to make an Excel formula which totals several adjacent rows based on cell values

I have an Excel sheet with three columns: date, person and percentage. I would like to put in a data validation that flags cells if the total for a given data/person combination do not equal 100%. Is ...

**-1**

votes

**1**answer

265 views

### How to get data from separate worksheets displayed in a single separate worksheet

I am working on a spreadsheet in Microsoft excel 2010. In the spreadsheet I have 3 worksheets. These are known as:
1st
2nd
Cases
Now in the 1st and 2nd Worksheets, there is a table at the bottom ...

**-4**

votes

**2**answers

86 views

### How do I get Excel to look up customer names from another sheet?

I have two sheets in Excel i.e. sheet1 and sheet2.
In sheet1's column A there are account numbers of the customer and in column B there are names of the customers.
Now I want that in column A of ...

**30**

votes

**10**answers

68k views

### Formula for updated currency exchange rates in Excel or other spreadsheets?

What kind of spreadsheets support a formula like the following:
=exchangeCurrency("USD"; "EUR"; 3000)
The result should be 3000 USD exchanged into Euros. Possibly a date could be provided for a ...

**9**

votes

**1**answer

44k views

### Create a pie chart from distinct values in one column by grouping data in Excel

I have an Excel document containing information from a survey. I want to create a pie chart over location (countries).
How can I make Excel group all the distinct values together and then display ...