**1**

vote

**2**answers

13k views

### List values based on criteria

I have a table on a worksheet called "DealSetup" that looks like this simplified version:
A B
1 John Doe 1
2 Jane Doe 1
3 Mark Doe 2
4 Doug Doe 1
5 Mary Doe 2
I ...

**1**

vote

**1**answer

2k views

### Excel: I need a formula that will count months between two dates. If it's 1-15 it should be a half month. Any ideas?

So I am trying to find a formula for excel. Bascically I have two dates and I need to know the number of months between them. But to add complexity, I need a rule in there that if the earlier date is ...

**1**

vote

**1**answer

5k views

### customize excel 2010 highlight track changes

Is there any way to customize how Excel highlights cells on Track Changes? My customer requested us to "highlight anything you've changed by changing text color to red," and I'd like to implement ...

**1**

vote

**1**answer

2k views

### COUNTIF Duplicate Detection

I have created a formula (excel 2003) to output a "1" if duplicates are found in a column.
=IF(COUNTIF(A$2:A$10750,A2)>1,"1","")
For some reason this formula does not mark the last duplicate in the ...

**1**

vote

**4**answers

2k views

### Looking up a value, depending on which set of dates another date falls between

and apologies if this is a duplicate - if you could point me in the direction of any existing answers, that would be great.
I have a set of date ranges in Excel, each of which has some kind of ...

**0**

votes

**3**answers

417 views

### Excel Formula average without blank cells

I have a row of values and want to find the average of the first 3, not counting blank cells. Example:
A B C D E F G
12 blank 8 blank 7 9 4
The average of the first 3 ...

**0**

votes

**2**answers

63 views

### how do I sum a column with times that are related to another colums

I have a sheet with names, that appear up to 20-30 times in the column. Every name has in another column a time related, meaning workhours. The times are in the format hh:mm:ss (e.g. 01:15:00). I want ...

**0**

votes

**2**answers

9k views

### How to automatically update values referenced from one sheet to another in excel

Here is my problem. I have 2 to 3 worksheets, and I need to make a master worksheet. The 2-3 worksheets contain the sales made in the day and the customer name. The problem is that new customers come ...

**0**

votes

**2**answers

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

**0**

votes

**2**answers

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

**0**

votes

**2**answers

4k views

### How to make a dropdown list such that… (see details)

I want to plot the stock prices of certain companies VS the S&P500. I have all the price data downloaded in my excel sheet already.
I want to create a line graph in Excel. One line is fixed and ...

**0**

votes

**4**answers

658 views

### Sharing Excel sheet over network?

Is it possible to have a server PC running a Microsoft Excel sheet to which several Thin Clients, with Barcode Scanners, will be connected via TCP/IP? The scanners should dump their data in the Excel ...

**0**

votes

**2**answers

2k views

### How to convert column data into rows, grouped by identifier

I have data as shown below in two columns and I need to arrange them horizontally in a row for each individual.
Baker 17
Baker 37
Baker ...

**27**

votes

**12**answers

131k views

### Excel displays ### for long text - what's wrong?

We've got an excel sheet with a pretty long text in one cell. Instead of line breaks (as set in the cell format), we get a long line of ####s. We can go back and forth by deleting and adding chars at ...

**21**

votes

**3**answers

79k views

### Turn off scientific notation in Excel

I would like to prevent Excel behavior that any large number I enter is stored as a number, then abbreviated to scientific notation.
Is it possible to tell Excel 2007 to stop doing this?

**9**

votes

**4**answers

32k views

### How to set the default page layout from RTL to LTR in Excel 2010?

Whenever I open an Excel spreadsheet, it is displayed in a right-to-left layout by default (i.e the right most column is A and I need to scroll to the left to reach further columns).
I can change ...

**7**

votes

**2**answers

25k views

### How to disable the annoying compatibility warnings in Excel?

Every time I edit a CVS or 2003-2007 Excel file it gives a warning that it will be saved in compatibility mode. I have this habit of saving every other minute via Ctrl+S and the window keeps popping ...

**7**

votes

**6**answers

23k views

### How can I make Excel documents open in different windows?

Office 2007, Windows Server 2008 x64.
How can I make Excel so that when I double-click a document, it opens in a new Excel instance, so that I can easily view them side-by-side as separate windows ...

**6**

votes

**4**answers

18k views

### How to insert empty rows in Excel 2013 when I have copied rows in my clipboard?

This seems like something which should be painfully simple to do but I can't figure it out. So with an empty clipboard I can right click on a row header and say insert row, no problem, it inserts a ...

**6**

votes

**2**answers

7k views

### How to change the delimiter when you copy from Excel and paste to notepad

I would like to copy cells from Excel, and paste it to notepad with comma as delimiter, in stead of tab(which it is). Is there any way to do this without doing a replace in Notepad or saving the ...

**6**

votes

**5**answers

38k views

### How do you specify a specific worksheet to open for a MS Excel workbook using a hyperlink?

I have a MS Excel workbook with several worksheets.
Users are directed to this spreadsheet using a hyperlink in an e-mail.
Is there any way of expressing the hyperlink for getting the ...

**5**

votes

**5**answers

24k views

### Cell colors change when copy&paste in Excel 2010

We have a 3rd party tool that creates an Excel file (xls, 97/2003 format) and formats some cells in different colours. When I copy a range of cells into a newly created file (xlsx, 2007/2010 format) ...

**5**

votes

**2**answers

6k views

### How to keep part of a spreadsheet always visible during navigation

I want to apologize in advance for asking something that's clearly available on the web. Unfortunately, I don't know the appropriate terminology though.
I'm looking to keep certain rows/columns ...

**5**

votes

**4**answers

105k views

### How to break the Y axis in an Excel sheet

I want to break the Y-axis in an Excel sheet. How can I do that?
For example, suppose I have the data in a range from 0-7000 and then range from 22000-28000. I want a break in the Y axis from ...

**4**

votes

**3**answers

16k views

### Ctrl + Shift + Arrow stopped working

When I work with a big amount of data, I introduce formulas in Excel by using the shortcut: Ctrl+Shift+arrow. That shortcut allows me to select a whole set of data to the left, right, up or down. ...

**4**

votes

**2**answers

9k views

### Saving a tab separated file in Excel without quotes

I have a tab separated value file that I want to edit in Excel. When Excel saves the file it modifies any column with quotes. It puts quotes around the whole column, and then double any quotes within ...

**3**

votes

**2**answers

355 views

### In Excel (or Numbers) what formula can calculate the amount of days in a certain month across 2 dates

If a guest arrived on 24th of August and left on 4th of September what is the formula(s) to calculate how many nights are spent in August, and how many nights are spent in September?

**3**

votes

**2**answers

81 views

### How to do up a excel hair chart?

I'm trying to do up a chart (on excel), the chart that I have in mind is somewhat similar to the attached picture.
Data:
X-axis: dates (weekly data)
Y-axis: interest rates
Doing this, I can ...

**3**

votes

**3**answers

404 views

### How to correctly sort calendar months “mmm” with AutoFilter?

I need to use filter to sort calendar months in mmm format.
Data > Sort > Custom will do it but I cannot make filter work. Months are in a column eg SEP, APR, JAN, APR, etc.
Filter just does ...

**3**

votes

**1**answer

42k views

### How can I hide 0-value data labels in an Excel Chart?

I would like to hide data labels on a chart that have 0 as a value, in an efficient way. I know this can be done manually by clicking on every single label but this is tedious when dealing with big ...

**3**

votes

**2**answers

1k views

### Excel - Sum values if their row meets more than one criteria

I have a spreadsheet of my bank account transaction history in this format:
| A | B | C | D
1 | Date | Amount | Balance | Category
2 | 2013-03-10 | $ -10.59 | ...

**3**

votes

**4**answers

24k views

### Excel ran out of resources while attempting to calculate one or more formulas

I received the error message
"Excel ran out of resources while attempting to calculate one or more formulas"
on ONE of my computers (Out of 2).
My worksheet contains: (Inside a single worksheet, not ...

**3**

votes

**3**answers

217 views

### Strange behavior of left arrow key in Excel 2010

When I press the left arrow to return to column A - it will not stop at column A but will continue to wrap around to the end (column XEP or something huge like that). How do I make it stop when it ...

**3**

votes

**3**answers

31k views

### How to change or remove all traces of original author name in Excel 2010 document

At our company we need to deploy one Excel Workbook in that way that nobody would see it is made by us by looking at the Office metadata.
What fields do we need to look for? I know, that the document ...

**3**

votes

**2**answers

5k views

### Is it possible to reference an excel cell in a comment?

I have an Excel balance sheet with a total at the bottom. On that cell I have a comment that says that the reason the total is X is because of what happens in J2. What happens if I insert another row ...

**3**

votes

**1**answer

293 views

### Paginate Rows into Columns in Excel

How would I 'paginate' every X row into a new column?
ORIGINAL LIST
Col A
-----
1
2
3
4
5
6
7
8
9
10
Is there a way to break after every third item? Turning the list into this:
PAGINATED LIST
Col ...

**3**

votes

**6**answers

40k views

### Missing Paste special options in Excel 2010

I have a weird problem with the Excel. Every time I copy and paste only the text is copying and the format is not copying as shown in the below figure. There are no paste special options available ...

**3**

votes

**9**answers

35k views

### Excel freezes when copying / cutting to paste elsewhere

When cutting/copying some cells to paste them into another sheet/page, sometimes Excel freezes/locks up and fades out. At the top toolbar it says in brackets "not responding".
Eventually, I must ...

**3**

votes

**1**answer

2k views

### Excel conditional formatting to highlight entire row if one date is not equal to another date in that row

I'm trying to use conditional formatting to highlight an entire row if the date in column D is greater than the date in column B. I can't seem to make it work. Is it a problem because it's a date ...

**3**

votes

**2**answers

17k views

### Excel cells - show text different to the actual value?

Is it possible in Excel to have a cell show text that is different from its value?
I would like to have a a cell that has the value 1,2,3 ... or 7 but it will show day of the week as text - but when ...

**3**

votes

**2**answers

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

**3**

votes

**1**answer

24k views

### How do you force the focus of an Excel Cell to remain the same?

I have set up an excel table where occasionally the data I have in several columns gets shifted down several rows to make room for new data. I also have a cell that references the top most cell in ...

**3**

votes

**2**answers

6k views

### How to utilize multiple monitors with Microsoft Project 2010 (or other Office 2010 products)?

My question does primarily concern Project 2010, though it would be applicable to other Office products such as Excel, which can tend to span several horizontal screens.
Seems like an obvious ...

**3**

votes

**6**answers

41k views

### Entering Time data in excel in mm:ss format (without the leading 0: for the hours)

I want to enter time data into excel sheet in mm:ss format.
The problem is - that although the cell format is mm:ss, if I enter the data like this:
12:33 the actual value stored is 12 hours and 33 ...

**3**

votes

**3**answers

3k views

### Excel trendline accuracy

This is a problem I have every once in a while, and it annoys me tremendously, beacuse I have always to recheck every trendline I get.
An example:
r L
(mm)
30,00 97,0
60,00 103,2
...

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

**1**answer

3k views

### Is there any way to suppress the Excel 2007 warning “in a different format than specified by the file extension”?

We have an application that exports data using the older Office XP/2003 XML Spreadsheet format. These files are saved with a .xls extension so that they can be launched directly, without having to ...

**2**

votes

**1**answer

88 views

### How to convert number to words?

Is there any formula / procedure to convert number to words. e.g 25371.34 to be converted as "Twenty Five Thousand Three Hundred Seventy One and paise Thirty Four Only".

**2**

votes

**1**answer

4k views

### how to generate all possible 3 digit combination of the numbers 0-6

I'm using Excel and am trying to generate a table (3 columns) that presents all possible combinations of the numbers 0,1,2,3,4,5, and 6. Digits are allowed to be repeated (example, 2-2-5). To do that ...

**2**

votes

**1**answer

6k views

### Excel - find local maxima with multicell peak

I want to find a local maxima in a huge dataset with Excel and the way I've been trying is to compare the previous and the next value to make sure they are smaller eg.:
...