**179**

votes

**5**answers

442k views

### How do I get the distinct/unique 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
...

**87**

votes

**5**answers

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

**87**

votes

**5**answers

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

**75**

votes

**13**answers

173k 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", ...

**75**

votes

**9**answers

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

**73**

votes

**9**answers

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

**66**

votes

**11**answers

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

**66**

votes

**7**answers

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

**63**

votes

**12**answers

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

**62**

votes

**8**answers

131k 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, id numbers, and associated data. One is a master list that includes general demographic fields, and the other is ...

**60**

votes

**2**answers

3k views

### Excel - can I set a cell to equal a certain value, no matter what is typed in it?

This is to play a joke on someone...not sure if it's possible to do what I want. We are circulating an Excel file among friends to select players for a golf tournament. I would like to set a cell ...

**58**

votes

**6**answers

225k views

### Cells not updating automatically

I have a formula in a cell which counts the values in 2 other cells. Normally, after entering in the values in these 2 other cells, the formula cell updates automatically when Enter is pressed or when ...

**56**

votes

**6**answers

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

**56**

votes

**2**answers

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

**55**

votes

**4**answers

129k views

### How to 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 ...

**51**

votes

**16**answers

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

**49**

votes

**7**answers

299k views

### How do you auto resize cells in Excel? [duplicate]

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?

**49**

votes

**11**answers

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

**47**

votes

**3**answers

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

**47**

votes

**9**answers

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

**45**

votes

**7**answers

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

**45**

votes

**2**answers

9k views

### What does “A1:A4^{1,2,3}” mean in an Excel formula?

What does the caret operand in Excel do to a range when it is followed by some other numbers in brackets? For example lets say we have the following table:
A B C
1 1.5 0 0
2 ...

**43**

votes

**7**answers

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

**43**

votes

**10**answers

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

**39**

votes

**11**answers

98k views

### Why does Excel treat long numeric strings as scientific notation even after changing cell format to text

I'm trying to process some data in Excel. The data includes numeric account numbers and other long numeric strings (like cell phone MEIDs). I am not doing math operations on these strings, so I want ...

**39**

votes

**11**answers

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

**37**

votes

**5**answers

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

**37**

votes

**4**answers

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

**36**

votes

**11**answers

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

**35**

votes

**3**answers

135k views

### What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx

Is there a limit? I couldn't find one posted.

**34**

votes

**10**answers

4k views

### How do I insert a new row in Excel via the keyboard?

Right clicking on a row and clicking insert is fairly time consuming. I'd rather not have to take my hands off the keyboard. How can I insert a new row above my current row using only the keyboard? ...

**34**

votes

**10**answers

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

**34**

votes

**2**answers

50k views

### How to search for ? (question mark) in Excel

When I try to find the question mark character (?) in an Excel sheet, Excel cannot locate the cells containing them. How can I search for ? in Excel?

**34**

votes

**8**answers

151k views

### 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?

**34**

votes

**14**answers

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

**32**

votes

**2**answers

195k views

### What is the Excel hotkey to re-calculate all formula in sheet?

I have a sheet that contains many formulas. But they do not recalculate automatically when I change input.
I'm looking for a hotkey, that can re-calculate the sheet. According to this page, F9 ...

**32**

votes

**4**answers

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

**31**

votes

**4**answers

3k views

### Get rid of all ##### errors at once in excel [duplicate]

In Excel, I usually solve ##### errors by double-clicking offending column headers. However, this can get a bit tedious when there are multiple error columns. Is there a quicker way to solve this?

**30**

votes

**7**answers

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

**30**

votes

**8**answers

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

**30**

votes

**4**answers

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

**29**

votes

**7**answers

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

**28**

votes

**4**answers

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

**27**

votes

**12**answers

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

**27**

votes

**6**answers

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

**26**

votes

**2**answers

88k views

### Go to specific row number in excel

Is there a way in Excel to go to a specific row number? For example I need to find an alternate way to not have to scroll but rather can immediately go to row # 3000

**26**

votes

**17**answers

115k 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, ...

**25**

votes

**3**answers

2k views

### How do I hide the #DIV/0! error while a referenced cell is blank?

In Column C I have Production. In column D I have Goal. In Column E I have variance %. My formula is =(D11-C11)/D11
However, how do you hide the cells down the sheet until you put something in D11 ...

**25**

votes

**2**answers

1k views

### How to simulate a full outer join in Excel?

Let's assume I have some data in Excel (and not in a real database). In one sheet, I have data, where one column functions as the ID, and I have made sure that the values in this column are unique. In ...

**25**

votes

**11**answers

142k views

### Excel Auto-Fill a Series Without Mouse (Keyboard Only)

So when I want to extend a series or something along those lines, I can highlight a group of cells, then click on the bottom right corner of the highlighting box and drag down. Excel will then ...