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 excel-vba. If it is about an Excel formula or worksheet function, then also tag it worksheet-function.

learn more… | top users | synonyms (2)

109
votes
5answers
200k views

How do I get the distinct values in a column in Excel?

If I have a column with values, and I want to find out what distinct values are in there (not how many - but the actual distinct values), how can I do that? In SQL Server I would do something like ...
76
votes
12answers
247k views

How do I compare two Excel documents? [closed]

The compare function in Word 2007 is very handy -- is there a similar capability in Excel 2007? I can't seem to find it... The documents I'm trying to compare are essentially two versions of the same ...
46
votes
3answers
23k views

Is it possible to paste CSV formatted data into Excel 2007?

I want to copy and paste CSV formatted text into Excel 2007. Is there a way of doing this without saving it to a temporary file first? I'm sure in previous versions of Excel there was a way to ...
46
votes
4answers
31k views

How can I stop Excel from eating my delicious CSV files and excreting useless data?

I have a database which tracks sales of widgets by serial number. Users enter purchaser data and quantity, and scan each widget into a custom client program. They then finalize the order. This all ...
37
votes
7answers
37k views

Enable smooth scrolling in Excel for large cells

When I have rather big (height wise) cell in MS Excel and am trying to scroll via mouse or even via scrollbar (clicking on "<-" "->" pointers) - Excel just jumps below to the next cell, hiding even ...
36
votes
9answers
129k views

Convert a column into a comma separated list

I have the task of creating a simple Excel sheet that takes an unspecified number of rows in Column A like this: 1234 123461 123151 11321 And make them into a comma-separated list in another cell ...
36
votes
8answers
28k views

Open Excel files in new window

When I have Excel running and I double click an Excel file in Windows explorer, I'd like it to launch in a new window by default so that I can move documents to separate monitors. Is there any way to ...
35
votes
9answers
104k views

How can I “group by” and sum a column in excel?

I'm trying to figure out how to "group by" values in an Excel spreadsheet. Given the following example (this is an inventory sheet I use at my restaurant:) At the far right of the spreadsheet ...
34
votes
7answers
48k views

How to easily reorder rows in excel with drag and drop or move-up or move-down?

I have to do some manual reordering or rows in Excel and cut/paste is too hard to use. I'm looking for something that would enable me to use drag'n'drop or to add some buttons to move-up/down ...
33
votes
7answers
47k views

How to open semicolon delimited CSV-files in US-version of Excel

When I double-click on a .csv file, it is opened in Excel. The csv-files have columns delimited with semicolons (not commas, but also a valid format). Using a German Windows/Excel setup, the opened ...
31
votes
7answers
58k views

How do I join two worksheets in Excel as I would in SQL?

I have two worksheets in two different Excel files. They both contain a list of names and addresses. One is a master list that includes other fields, and the other is a list that only includes name ...
31
votes
9answers
33k views

Is it possible to “unpivot” or “reverse pivot” in Excel?

I have data that looks like this: Id | Loc1 | Loc2 | Loc3 | Loc4 ------------------------------ 1 | NY | CA | TX | IL 2 | WA | OR | NH | RI And I want to convert it to this: Id | ...
30
votes
3answers
165k views

Comparing two strings in Excel?

I need to compare two strings in excel and determine if they are equal, the strings are in adjacent cells. For example, given: apple apple water water pear carrot apple water dog dog ...
29
votes
6answers
164k views

How do you auto resize cells in Excel?

I have copied some data into Excel 2010. I have found that some of the cells need to be widened to allow the data to fit. How do I automatically adjust the height of the cells to fit the content?
29
votes
4answers
8k views

How do Microsoft Word and Excel run without any installation?

I was having a discussion on bookmarks in Word with a friend, and he suggested me to check out his implementation of a query in Word. Since I did not have Microsoft Word installed, I told him I don't ...
28
votes
2answers
5k views

Excel: how to undo in current file only?

Microsoft Excel's undo / redo feature behaves unlike any other program I know. The undo stack seems to be global across all open files, so that undoing sometimes switches to another file and undoes ...
27
votes
4answers
60k views

Howto expand all columns in Excel spreadsheet?

I have a spreadsheet with many columns. The columns are all a small standard width. Double-clicking the partition line between columns will expand the column width to encompass the longest string in ...
25
votes
6answers
62k views

How do you force excel to quote all columns of a CSV file?

Excel only places quotes around certain fields, how do I force excel to save a CSV file with quotes around every column?
24
votes
4answers
87k views

How to remove the print preview lines in Excel 2010?

Somehow or other I have managed to switch on the print margin lines in Excel 2010. I cannot seem to turn them off. I went to Page Layout->Page Setup->Print Area->Clear Print Area but the lines remain ...
24
votes
4answers
84k views

How do you customize Excel 2010 keyboard shortcuts?

How do you customize Excel 2010 keyboard shortcuts? This article from microsoft.com provides instructions for "Microsoft Office 2010" but seems only apply to Word, not Excel. ...
24
votes
13answers
169k views

How to make Excel's “Auto Fit Row Height” feature actually auto fit the row height?

For every generation of Excel I can remember (including 2010, which I'm using now), Excel's "Auto Size Row" features sometimes fails to actually auto size a row when the cell contains wrapped text. ...
23
votes
14answers
57k views

Format a number with optional decimal places in Excel

I have numbers in cells in Excel. I want the numbers formatted so that if they have decimal places they show to a maximum of two, and if they have no decimal places it doesn't show any. For example. ...
23
votes
8answers
27k views

How to copy multi-line text from Excel without quotes?

When you create a multi-line string in an excel cell (by using Alt-Enter), if you copy that cell to a text editor, excel will automatically add double quotes (") around the full string, ie: Cell1 | ...
22
votes
7answers
100k 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 ...
20
votes
2answers
1k views

Why are Excel weekdays wrong for 1900?

This question is based on the observations of AdamV in his answer on How do I get the day name into a cell in Excel? When A1 has the value 2009-08-01, then: =WEEKDAY(A1) will obtain 7 =TEXT(7, ...
18
votes
9answers
74k 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 ...
17
votes
9answers
48k 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 ...
17
votes
7answers
72k 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?
17
votes
7answers
31k views

Easiest way to open CSV with commas in Excel

CSV files are automatically associated with Excel but when I open them, all the rows are basically in the first column, like this: It's probably because when Excel thinks "comma-separated values", ...
17
votes
5answers
1k views

How to simultaneously rename a file in MS Office and delete old version?

In Microsoft Office, when wanting to save a file to a different file name without keeping a copy of the previous file name, doing so requires two steps: First, File -> Save As... and select the new ...
17
votes
7answers
33k views

How can I save a graph to a PNG or GIF file in Microsoft Excel?

How can I save a graph to a PNG or GIF file in Microsoft Excel? I know I can save as an HTML file and use the image created there, but I suspect there is a way that doesn't create other clutter of ...
17
votes
2answers
38k 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 ...
17
votes
9answers
41k views

How can I set Excel to always import all columns of CSV files as Text?

Though I try to avoid it, I occasionally have to open a CSV file in Excel. When I do, it formats columns containing numbers, which makes them useless for my purposes. As far as I can tell, the only ...
17
votes
4answers
79k views

How to set character encoding when opening Excel

Is it possible to set the default encoding for Excel (any version, e.g. 2010) when opening files like csv files (like you can in Open Office Calc)? I When I try to open a csv file encoded in Japanese ...
17
votes
3answers
69k views

How do you change default delimiter in the Text Import in Excel?

I want to import a CSV file to Excel. The CSV file has comma separated data (go figure), but the delimiter in the Text Import Wizard is set to "Tab" by default. How can I change the default to "Comma" ...
16
votes
3answers
57k views

Reverse order of rows in Excel

Is there any simple way to reverse the order of some rows in Excel? I'd rather hoped that there might be a suitable option in Paste Special, as there is for Transpose, but apparently not.
16
votes
9answers
34k views

Shortcut in Word or Excel for Special Paste?

If I want to paste without any formatting, I have to go to the "Paste" button on the ribbon and click "Paste Special" and then "unformatted text". Is there a shortcut like Ctrl+V that'll ...
16
votes
7answers
237k 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
16
votes
2answers
1k views

What does ALT+F+C+N do, and how to undo it?

I was trying to figure out how to copy/paste a forumla without changing the references, and I found on a forum board post that someone said "Copy the cell, then do ALT+F+C+N". I tried this without ...
16
votes
15answers
40k views

How to make cells perfect squares in Excel?

In Microsoft Excel, I want to make the height of first three rows and first four columns (12 cells in the top left corner) such that these cells are squares. How can this be done? Surprisingly, ...
16
votes
5answers
18k views

How can I change the focus in Excel to the formula bar via the keyboard?

I like to avoid using my mouse as much as possible, but on occasions when I need to type directly into the formula bar I am currently forced to 'rattle the rodent'. Does anyone know a way to move ...
15
votes
5answers
97k views

How to write multi lines in one Excel cell?

I want to write multi-lines in one MS Excel cell. But whenever I press the Enter key, the cell editing ends and the cursor moves to next cell. How can I avoid this?
15
votes
5answers
66k views

Excel: Find Items in one column that are not in another column

I have two columns in excel, and I want to find (preferably highlight) the items that are in column B, but not in column A. What's the quickest way to do this?
14
votes
6answers
9k views

Can you edit the contents of an Excel cell without having to double click it?

I have a list of about 10,000 name/addresses that I have to manually cleanse in Excel (fun!). As you can imagine, having to double click to enter almost every cell to edit it is a drag. And it's ...
14
votes
6answers
79k views

In Excel, how do I check if a cell is in a list of values (a range of cells)

I've got a range (A3:A10) that contains names, and I'd like to check if the contents of another cell (D1) matches one of the names in my list. I've named the range A3:A10 'some_names', and I'd like ...
14
votes
3answers
31k views

How to move columns with drag and drop in Excel 2011?

How to move columns with drag and drop in Excel 2011?
14
votes
4answers
11k views

Freeze row and columns in Excel (and the same time)

How can I lock both column and rows and the same time using the 'freeze panes' in Excel ? I can manage to freeze either columns or rows but not both. I can do a similar thing with "split panes", ...
13
votes
3answers
50k views
13
votes
3answers
68k views

Excel: Replace occurences of one character with another in a String?

I want an excel formula that will change Haverford, PA to haverford,+pa. (Text is to lowercase, and spaces are replaced with plus characters.) The following will get the first space, but not all of ...
13
votes
2answers
5k views

Excel Conditional Formatting Escaping a Question Mark

I would like to use a conditional formatting rule in an excel file that would color any box with a question mark in it red. It seems that Excel is using a question mark as a wild card and will turn ...