A spreadsheet application by Microsoft. Use this tag along with [vba] if your question involves programming Excel in VBA, and with [worksheet-function] if it involves an Excel formula or worksheet function.

learn more… | top users | synonyms (2)

3
votes
2answers
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
2answers
13k views

How can I print Excel spreadsheet files automatically?

My company has computer-generated reports that come out as Excel spreadsheets which are emailed to the Billing Department. They would like these reports to be automatically printed as they are ...
3
votes
1answer
7k views

possible to have two Y axes and two X axes on same chart

Is it possible to have a chart with left Y axis right Y axis top X axis bottom X axis all on same chart?
3
votes
2answers
5k views

Format restrictions for cell in Excel

I want a cell to only have a specified format, e.g. "01-02-03", meaning "double digit dash double digit dash double digit". I a user tries to enter something into the cell, an error should occurr ...
3
votes
3answers
14k views

How to sum numbers in a range that only have a particular cell color [Excel]?

I would like to put a formula in Excel that would sum all the numbers of a specific range that have a specific cell background color.
3
votes
2answers
83 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 ...
2
votes
2answers
1k views

How can I combine two lists into one long list?

I have two lists in coloumns 1 and 2. I need coloumn 3 to be of the format one dash two. For example: Column C will need to repeat all values of A and B. I would prefer a formula for this is ...
2
votes
2answers
5k views

How can I compare two columns in Excel to highlight words that don't match?

(I'm using Microsoft excel 2010) OK, lets say I have a list of phrases in both column A and column B (see screen shot below) What I would like to happen whether it be with a macro, VBA or formula ...
2
votes
1answer
16k views

Find Substring Within Column - Excel

Suppose I have two columns of data. One is a list of names and the other is a list of peoples' names mixed with other stuff. For instance, one list has "Bob" while the other might have "blank blank ...
2
votes
1answer
997 views

Can I provide RGB hex strings in an Excel worksheet for pie chart colors without using code?

Like user1813558 in his question "Change the pie chart colors" I would like to choose the colors that Excel uses for a pie chart (indeed for any chart) as I need them to be consistent with other data ...
1
vote
1answer
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 ...
1
vote
1answer
3k 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
3answers
245 views

How do I delete specific text in multiple cells at once?

I have a massive list of email addresses; but just to make things simple i am dealing with 3 - 10 email addresses that are typed up in 1 cell and i cant seem to MASS delete specific ones. Is there any ...
0
votes
1answer
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
1answer
45 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
2answers
3k 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 ...
0
votes
4answers
781 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
2answers
74 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 ...
27
votes
12answers
158k 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 ...
10
votes
1answer
49k views

Formula to add minutes to date+time in Excel

If I have a cell formatted as date+time in Excel, how can I add to this in terms of minutes? It would need to work whether I was adding 20 minutes, or 2,500 minutes. So, for example if I had this ...
10
votes
1answer
49k views

How do I stop the excel file being in use? [duplicate]

I'm opening an Excel file and I'm getting the File in use pop-up that says it's locked for editing by me. However, I've not got it open on this computer (I think I had it open and my machine blue-...
8
votes
6answers
27k 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 ...
7
votes
1answer
26k views

How to have Excel chart connect data points when no value?

I'm charting daily values using a line chart. On some dates, I don't have any data. I would like the chart to connect the data points for which I have data across any gap in data. So for example if ...
7
votes
2answers
27k 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
1answer
34k views

Excel 2011 Mac auto-recover file lost

Yesterday morning my colleagues Excel 2011 (on Mac OSX 10.8) crashed. He reopened Excel and was asked if he wanted to recover the previous document. Yes, he said and then got a document named ...
6
votes
3answers
10k 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
2answers
10k 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 ...
6
votes
4answers
123k 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 8000-...
4
votes
2answers
12k 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 ...
4
votes
7answers
4k views

Free way to perform a text search in PDF and Excel files

Is there a free way to perform a text search in PDF and XLS (Excel spreadsheet) files? I want to be able to find a specific word in these type of files. Is there a free tool for that?
3
votes
2answers
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
2answers
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 | $3700....
3
votes
1answer
3k views

Excel 2013 Fuzzy Lookup to find near-duplicate text

I have a list of captions with a large number of near-duplicates. For example: Birthday for Her For Her Birthday Birthday - For Her For Her / Birthday I was looking into Fuzzy Lookup as a way ...
3
votes
2answers
19k 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
2answers
48k views

How do I calculate the number of months between two dates in Excel? [duplicate]

I have two columns on Microsoft Excel: F is the column with the date service ended; E is the column with the date service began. How do I calculate and report the number of months served for each ...
3
votes
1answer
11k views

Strip out all HTML Tags from an excel file

I have an excel file that has cells with text that includes HTML tags. The data was exported from a database and the HTML tags were included. The HTML is stored as text, but I need it gone. Is ...
3
votes
1answer
904 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
2answers
7k 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
4answers
31k 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
3answers
1k 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
2answers
115 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
2answers
6k 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
1answer
4k 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 ...
3
votes
2answers
693 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
1answer
316 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
3answers
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
3answers
248 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
1answer
3k 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
2answers
7k 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
2answers
11k views

Export to CSV From Excel

I frequently use Excel to prepare data to load into a table. I create a CSV file from it and use that file for data loading. I generally find myself creating the CSV file multiple times. My current ...