Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. If your question is about programming Excel in VBA then also tag it "VBA". If it is about an Excel formula or worksheet function, then also tag it "worksheet-function".

learn more… | top users | synonyms (2)

8
votes
2answers
13k views

Copying a multiline text into a single cell in Excel

I want to copy the following: line1 line 2 line 3 into a single cell in Excel. I know that I can use the combination Alt+Enter on Windows and Ctrl+Alt+Enter on Mac to create a line break in ...
8
votes
4answers
43k views

Using find() in Excel to remove text from string

I'm using find to remove some of the test string in lookup cell value For instance I look-up the following in cell A2 Application: sales_rep_support and use find =RIGHT(a2,FIND(" ",a2)+4) to change ...
8
votes
3answers
9k views

How to paste Excel data as fixed-width text?

If I copy data in Excel and paste it in a text file, I get tabs between cells. That's not good, I want spaces - and I want the columns to be aligned. How can I achieve this without a lot of effort ...
8
votes
1answer
7k views

Why don't arrows keys work in Microsoft Excel?

Watch this video. http://screencast.com/t/SRyUxusqQ In this video, you are seeing me press the down arrow three times, and the up arrow three times. It might have been reversed, I don't remember. ...
8
votes
1answer
61k views

How to concatenate a date in MS-excel and receive a date (not a number)

When I try to concatenate two cells in MS-excel in the formula I have the date as a integer number (eg: 04 april 2012 become 41006). How can I display the date also in the formula cells?
8
votes
3answers
8k views

Why does pasting into an unprotected cell in Excel protect it?

While researching the answer to this question, I stumbled across a rather odd behavior in Excel related to unprotected cells in protected worksheets. To see what I mean, create a new workbook, ...
8
votes
1answer
33k views

Conditional formatting of TRUE / FALSE values in an Excel 2010 range

I have a range containing formulas that evaluate to TRUE or FALSE. How would one apply conditional formatting to this range, so that TRUE cells are Green, and FALSE cells are RED?
8
votes
2answers
13k views

How to convert Excel file with multiple sheets to a set of CSV files?

I have an Excel file with 20 sheets on it. Is there a way to quickly convert the file into 20 CSV files - one for each sheet? I can do it manually of course, but I'll have to be processing these ...
8
votes
1answer
23k views

Compare two excel files

I need to compare two Excel Spreadsheets to see if values of each user in a specific column were changed. The new Spreadsheet may have additional users that were added making the row old users ...
8
votes
2answers
3k views

Excel - Prevent cell text from overflowing into next (empty) cell

When you have text in an Excel cell that is too long to be shown in the visible area of that cell, and the next cell on the right is empty, Excel lets the text be displayed in that next cell (and the ...
8
votes
7answers
40k views

Excel destroying special character when saved as CSV

I am working on a project that uses a CSV file supplied by the client to populate a database. The client is to create the spreadsheet, then save it as a CSV to be uploaded, which is then parsed into a ...
8
votes
7answers
38k views

Merging two cells in multiple rows

I have an excel spreadsheet with two cells in each row that I'd like to merge. Highlighting all the cells of interest and just merging from the cell formatter seems to just merge all the cells ...
8
votes
2answers
43k views

Excel: How to display superscript text as data series on chart?

I have a line chart in Excel 2007 that is created from data in the worksheet. It has several series whose titles are displayed in the chart on the right side. The names of the series are of the form ...
8
votes
9answers
44k views

Why is Excel 2010/2013 taking 10 seconds open any file?

I have a fast Windows 7 PC with two SSDs and 16GB of RAM, so I'm used to programs loading very fast. But recently, for no reason I can figure out, Excel has started taking way too long to open Excel ...
8
votes
4answers
73k views

How to open ods files in Excel?

What is the best way to open ods files in Excel 2003? Is there some plug-in available? Should I use some convertor to xls? Or would it be best if I just used LibreOffice (or OpenOffice)? The file I ...
8
votes
2answers
16k views

How can I determine which version of Excel was used to create a .xls file?

Does anyone know of a way to determine which version of Excel was used to create a file? We have some .xls files and need to determine the version. They open fine in Excel 2007 and higher, but a ...
8
votes
4answers
36k views

Excel 2010 PowerPivot: How do I show items with no data?

In standard Excel pivot tables, there is an option for fields that allow you to force display of all items even if there are no results for your current selection. Here's the option: However, using ...
8
votes
3answers
75k views

How to I auto-refresh an Excel auto-filter when data is changed?

How to I auto-refresh an Excel auto-filter when data is changed? Use case: I change the value of one cell to a value that was filtered. I want to see the current row disappearing without having to do ...
8
votes
4answers
13k views

Set Excel to search within entire workbook by default

In Microsoft Excel, when I search using CTRL+F, by default it only searches within the current worksheet. How can I set it to search within the entire workbook by default? I have Office 2010. Is ...
8
votes
2answers
10k views

Unix command to get number of lines in a CSV file

I have to get the count of lines from incoming CSV files. I have used the following command to get the count. wc -l filename.csv Consider files coming with 1 record I am getting some files with * ...
8
votes
3answers
3k views

Microsoft Excel Mac, keyboard shortcut to edit in cell

In Microsoft Excel for Mac 2011 (14.0.0) how can I modify the enter/return key to edit in cell instead of moving the selection down? I know that in Preferences > Edit you can uncheck the box that says ...
8
votes
3answers
14k views

Saving filtered results from an Excel workbook to a CSV file

I have an Excel workbook with a lot of rows in it. I filter it using the values of one column and then I want to save the filtered results to a CSV file. When I do "Save as..." I get all the rows. I ...
8
votes
1answer
2k views

How does the NOW() function get evaluated cross time zones in Excel Online?

How does EXCEL ONLINE evaluate the NOW() function when a spreadsheet is accessed from different time zones? For example, if user PAUL is in London, UK and accesses a sheet at 5PM GMT and user SUE is ...
8
votes
1answer
1k views

Where can I find guidelines for how to use Excel's “Data and Model” cell styles (specifically “Linked Cell”)?

(I originally asked for "well-known" guidelines, but got no answers. In light of that I think it's fine is anyone just wants to answer with their own guidelines or a link to some they like.) I have ...
7
votes
4answers
943 views

Can I sort the text after a certain character?

I have a large list I'm trying to sort but I'd like to sort the list from the value after a certain character. eg: 241&name=A 123&name=C 645&name=B So I'd like to sort the list ...
7
votes
4answers
910 views

Excel, remove leading 0's from IP address

I have a list of IP addresses in the following form: 010.125.015.013 010.125.153.012 010.125.012.135 I'm unable to execute ping with that form as windows thinks it's dotted octal form. ...
7
votes
5answers
36k views

Is there a function in Excel to find the maximum absolute value of a range?

I'm looking for a function in Excel that looks something like = MAX(ABS(A1:A10)) except ABS() doesn't take a range of numbers. The best that I can come up with is: = ...
7
votes
4answers
40k views

Go to row in Excel 2007

How do I go to a certain row in Excel 2007? Amazingly, I can't find the answer to this simple question on Google. Ctrl+G doesn't do it. When I enter a row number under "Reference", it tells me the ...
7
votes
11answers
20k views

Can you convert an address to a zip code in a spreadsheet?

Given a column of street addresses with city and state but no zip in a spreadsheet, I'd like to put a formula in a second column that yields the ZIP code. Do you know a way to do this? I'm dealing ...
7
votes
3answers
4k views

Excel elapsed time format

In Excel, given a number representing some amount of time (e.g. 25.5 hours), is there a way to format it so that it shows as 1d 1h 30m? Bounty question : Is there a way to add months to this format? ...
7
votes
4answers
29k views

Specifying delimiter for Excel 2007 export

I'm trying to save an Excel 2007 file as a pipe-delimited text file but in the Save As list, I see only tab-delimited and csv options. How do I go about specifying the delimiter, which as I recall ...
7
votes
2answers
8k views

In Excel, can I provide a “default value” in case my formula returns #NA?

For example, Say the value of a cell is: IF(ISNA(VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,FALSE)), 0, VLOOKUP(A3,somesheet!G:J,3,FALSE) * ...
7
votes
1answer
883 views

Pull a user-defined season from a date?

So I want to pull a season from a date. I have a seasons table and it goes a little like this. Jan = Low (low/mid/high = season) feb = low mar = low apr = mid may = mid jun = high jul = high aug = ...
7
votes
1answer
39k 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 ...
7
votes
3answers
24k views

Setting up a column of “enum”-fields in Excel

I'd like to setup a column in excel with several fixed possible values (like 'in-progress', 'done', 'canceled'). I'd like to set it up so the only possible values in these column are from the fixed ...
7
votes
3answers
5k views

How do I prevent Excel from locking files by default?

When I double-click on a CSV file on a network share, the file is opened in Microsoft Excel (which is what I want). However, Excel assumes that I'm going to modify the file, and that everyone else is ...
7
votes
4answers
44k views

Dynamically reference a Named Table Column via cell content in Excel

How do I reference an Excel Table column dynamically in Excel 2007? ie: i wanna reference a named column of a named table and what table it is will vary with the value of a cell. I have a Table in ...
7
votes
5answers
25k views

How to batch convert .csv to .xls/xlsx

I am looking for a way to quickly convert a directory of .csv files into the .xls or .xlsx format (would be nice if I could do either/or). Is there an easy way to do this or do I need to install a ...
7
votes
7answers
20k views

How do you keep Microsoft Excel running at full speed even when it does not have the window focus?

I've noticed that when Excel 2007 (and probably earlier versions) has the window focus, Excel runs at full speed which means that files open fast and long-running VBA macros execute quickly. If the ...
7
votes
2answers
346 views

Numbering of rows in a filter

In a range with filter, I want to have a column of row numbers which will change according to the filter, so that rows always count consecutively from 1. For example, the following is a range with ...
7
votes
6answers
20k 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 ...
7
votes
5answers
12k views

Excel CSV import treating quoted strings of numbers as numeric values, not strings

I've got a web application that is exporting its data to a CSV file. Here's one example row of the CSV file in question: 28,"65154",02/21/2013 00:00,"false","0316295","8316012,8315844","MALE" ...
7
votes
5answers
75k views

Excel: Delete row if cell in certain column is blank?

I am a complete newbie to Excel so please excuse me if this is something easy to do. I have looked through a lot of options but couldn't find what I needed. Basically, I want to delete all of the ...
7
votes
1answer
76k views

Change cell color based on other cell value

I have encountered numerous questions related to changing a cell color based on its value, which can be achieve easily with Excel "Conditional Formatting". However I'm interested in changing a cell ...
7
votes
7answers
12k views

Can Excel show a formula and its result simultaneously?

I know that it's possible in Excel to toggle between displaying values and displaying formulas. I'm required to turn in assignments for a statistics class as a printed Excel sheet showing both the ...
7
votes
2answers
10k views

How to conditionally create a hyperlink based on value of a cell

ColA Col B ColC Project A A.pdf A.pdf Project B No Link I have a list of project records. One column (e.g., B) ...
7
votes
5answers
22k views

Why does this Excel file keep asking to save?

A client sent me some Excel .xlsx documents, and every time I open one of them and then close it, Excel asks if I would like to save changes, even if I didn't make any. This doesn't happen with any ...
7
votes
3answers
17k views

How to Label / Tag Items in a Spreadsheet

I'm a novice at spreadsheets, so I hope you'll forgive me if I'm asking a super obvious question. Is it possible to use tags in Excel/Google Spreadsheet? I'm creating a spreadsheet to log all the ...
7
votes
5answers
18k views

Conditionally format a cell if it contains formula

Is it possible to conditionally format a cell if the cell contains formula (to alert myself and other users when updating the cell)?
7
votes
1answer
7k views

Shortcut to accept suggested formula in Excel

I'm working with a lot of formulas in Excel 2007 and I would like to know what shortcut key you need to press to accept the suggested formula. When I type in =DEG the first suggested formula is ...