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

**4**

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

**14**

votes

**8**answers

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

**1**

vote

**4**answers

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

**6**answers

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

**18**

votes

**7**answers

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

**10**

votes

**4**answers

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

**10**

votes

**15**answers

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

**9**

votes

**3**answers

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

**2**answers

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

**1**

vote

**3**answers

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

**6**

votes

**4**answers

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

**3**answers

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

**2**

votes

**3**answers

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

**2**answers

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

135 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

**2**answers

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

**2**answers

575 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

**2**answers

555 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

**3**answers

797 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

**2**answers

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

**0**

votes

**2**answers

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

**-1**

votes

**1**answer

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

**14**

votes

**6**answers

59k views

### How do I get the day name into a cell in Excel?

Given a date, how do I get the day of the week (e.g. "Monday") into a cell in Excel?

**6**

votes

**4**answers

11k views

### Is there an Excel function to create a hash value?

I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real ...

**3**

votes

**1**answer

572 views

### Sample data to create Excel timeline

I am trying to create an Excel timeline like the one shown here. I am not understanding how the sample data would look and how to go about making this. Please post a screenshot or explain how to ...

**4**

votes

**1**answer

3k views

### Using INDIRECT and AND/IF for conditional formatting

I am having some issues with using INDIRECT to apply conditional formatting.
I have column G which carries a percentage. I want to conditionally format column H based on the percentage being greater ...

**4**

votes

**1**answer

267 views

### In which order does Excel process its formulae?

I've got a fairly large spreadsheet with major calculations going on, and it's starting to slow down every time a value that's part of a calculated field is modified. I'm in the process of optimizing ...

**4**

votes

**3**answers

12k views

### Only show a formula result if it meets given criteria

I like to keep my Excel spreadsheets fairly clean. So, often times I'll end up writing statements like this:
IF([formula x]=[value],"",[formula x])
What that essentially says is that if the ...

**4**

votes

**2**answers

3k views

### How to make two formulas reference the same cells in Excel?

I have one row that uses =MEAN(A1:A15), and another one that uses =SUM(A1:A15). But many times I have to alter this range to, let's say, A1:A13;A15 in both cells. I would like to alter the range in ...

**4**

votes

**4**answers

730 views

### Spreadsheet forumula questions to pick a random DVD

I have a list of my (279) DVD’s in an OpenOffice Spreadsheet (version 3.3.0) on my Windows 7 laptop and I want to create a formula to randomly pick a film to watch (for occasions when I can’t make up ...

**3**

votes

**1**answer

210 views

### How to make many column formulas ignore the same lines in Excel?

I have multiple columns and at the end of each one I have a SUM and MEAN lines.
Now I want to ignore some lines thus making =SUM(A1:A15) into =SUM(A1:A13;A15) if I want to ignore line 14. Doing this ...

**2**

votes

**4**answers

9k views

### Using two conditions with a SUMIF function in Excel 2003

For a given row on Sheet1, I use the SUMIF function to see if the value from the first cell of that row is on a list of historical values in Sheet2.
That will basically summarize all the values on my ...

**2**

votes

**3**answers

1k views

### Average of cells that match criteria and removing duplicates in Excel

I have an Excel spreadsheet where I want to remove the duplicate names and take an average for each person's score. I have a list of the following type:
Al 1
Bob 1
Bob 2
Bob 1
Joe 4
...

**2**

votes

**2**answers

1k views

### How do I copy a worksheet without creating local copies of workbook-scoped named formulas?

I have a workbook with some workbook-scoped named formulas. When I copy a worksheet that has some (different) worksheet-scoped names, the copy ends up also having worksheet-scoped names that are ...

**2**

votes

**2**answers

4k views

### What is an excel formula that will return me the last row number of a cell within a range that contains specific data?

I have a spreadsheet that will default with values of "No". Over time, these values will be changed to "Yes".
I need a formula (no VB, please, VB is useless to me) that will get me the row number of ...

**1**

vote

**2**answers

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

**1**

vote

**4**answers

3k views

### How to input only unique values in a column in Excel 2007

I'd like to know the easiest way to make sure that only unique strings are entered in a particular column in MS Excel 2007? I know I have to put a formula in the Data > Data Validation, but I don't ...

**0**

votes

**2**answers

857 views

### Remove duplicate rows in excel when not all the columns are the same

How can I automatically remove duplicate rows in Excel when data->filter doesn't work, because the columns aren't all the same?
I believe the picture explains what I need, but I can probably supply a ...

**6**

votes

**5**answers

951 views

### Excel Formula to Calculate Duration of Time Based on a String of Text Such As 9:00am — 12:30pm

I'm trying to develop an Excel formula that would calculate the actual time duration for a string of a time range of the following format: 1:00am – 3:00am
According to this article it seems it should ...

**4**

votes

**6**answers

4k views

### In Excel, how to fill down consecutive integers following a pattern 1, 1, 2, 2,

In Excel, I know how to use the "fill down" feature to populate a column of sequential numbers.
I would like to do this but following a pattern like:
1
1
2
2
3
3
Does anyone know how to do this? I ...

**4**

votes

**1**answer

1k views

### Excel 2007 - How can I write “use this cell” or IF BLANK “use this cell”?

I am trying to show the Days between NOW() and the dates (dd/mm/yy) in, either Column B or Column C - depending which one is NOT blank
A B C
29/03/10 01/04/10
29/03/10 ...

**3**

votes

**2**answers

687 views

### Split a string in a cell in Excel using numbers as the divider

Example cell value: President & Founder, Millennium Pain Center, 1998– present.
I'd want that parsed (split) into "President & Founder, Millennium Pain Center," and in another cell, " 1998– ...

**3**

votes

**2**answers

2k views

### Non identical random numbers

I have the following formula in Excel (not vba):
=RANDBETWEEN(1,10)
Then I auto fill down 10 rows. How can I re-write the formula so the numbers don't repeat.

**3**

votes

**1**answer

360 views

### Excel: How do I get a dynamic formula which can display a total of unique values in a range?

Example range:
A10=100,
A11=105,
A12=100,
A13=100,
A14=7,
A15=105.
The formula I need would display "3" only as there are only 3 unique values in this set. The spreadsheet will be updated ...

**3**

votes

**1**answer

916 views

### Using the INDIRECT() function with table formats [closed]

My purpose is to create data validation dropdown lists in Excel that reference whole columns in a table-formatted set of data. Ultimately, I hope to create a dynamically changing set of data by which ...

**3**

votes

**5**answers

12k views

### Using excel, how can I count the number of cells in a column containing the text “true” or “false”?

I have a spreadsheet that has a column of cells where each cell contains a single word. I would like to count the occurrences of some words. I can use the COUNTIF function for most words, but if the ...

**3**

votes

**5**answers

23k views

### Excel: copy cell formatting in equation

If I make an equation:
' on cell A2
=A1
Is there a way to make A2 have the exact same formatting as A1 and not just the value?
EDIT:
i.e. I want a FORMULA that copies both the value and the ...

**2**

votes

**1**answer

2k views

### Lookups targeting merged cells - only returning value for first row

I have a master worksheet which contains data that I wish to link to another 'summary' sheet using a lookup.
However, some of the cells whose data I wish to include in the summary sheet are merged ...

**2**

votes

**1**answer

3k views

### Excel 2010 formula to strip out part of cells

I have used basic IF ISNUMBER functions before to identify parts of cells and return the value in a different cell, such as:
...

**2**

votes

**1**answer

154 views

### How to retrieve non-contiguous cell collections from strings in Excel?

I tried to =SUM(INDIRECT("B4,B6")) but it doesn't work. How to do that?