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.
71
votes
5answers
115k 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
...
53
votes
14answers
157k 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 ...
28
votes
4answers
5k 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 ...
26
votes
6answers
14k 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 ...
26
votes
6answers
18k 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 ...
21
votes
2answers
12k 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 ...
21
votes
2answers
2k 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 ...
20
votes
4answers
17k 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 ...
19
votes
2answers
678 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, ...
19
votes
7answers
26k 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 ...
18
votes
3answers
25k 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 ...
17
votes
5answers
567 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 ...
16
votes
9answers
65k 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-seperated list in another cell ...
16
votes
11answers
39k views
In Excel format number with optional decimal places
Simple: 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 2DP and if they have no decimal places it doesn't show any.
For ...
16
votes
4answers
42k 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.
...
15
votes
3answers
70k 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
...
15
votes
3answers
29k 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 ...
15
votes
5answers
18k 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 | ...
14
votes
7answers
10k 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 | ...
13
votes
8answers
33k 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 ...
13
votes
4answers
70k 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?
12
votes
5answers
608 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 ...
12
votes
3answers
56k 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 ...
12
votes
9answers
2k views
Excel for programmers [closed]
Recently as part of my job I have had to edit and create a lot of excel spreadsheets. I am familiar with some Excel formulas but while editing the spreadsheets I don't feel that I'm using the full ...
12
votes
4answers
27k 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 ...
12
votes
12answers
15k 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, ...
12
votes
2answers
17k 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?
12
votes
8answers
15k 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 ...
12
votes
13answers
1k views
What is an alternative to Excel for editing spreadsheets?
I often have to manipulate Excel and CSV spreadsheets to prepare data being imported into MySQL. I need to do things like find and replace values, insert and remove columns, sort rows, etc. I ...
12
votes
4answers
34k views
Need two excel windows side by side, on different monitors, in the same instance
I have to do work between two spreadsheets and I need to view them side by side.
I can't just open the workbooks in separate instances because I need to make use of features like paste formatting, and ...
11
votes
6answers
2k views
i just deleted a worksheet that was critical, what do i do now?
i also saved it
is there any way to recover it?
11
votes
3answers
1k views
How to insert a date into an Excel spreadsheet?
I want to insert date and time into a cell of an Excel spreadsheet. NOW() and TODAY() don't fit my needs. These functions insert dynamic date. But I want static: if I insert the date it won't change ...
11
votes
8answers
127k 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
11
votes
4answers
22k 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 ...
11
votes
4answers
10k 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 ...
11
votes
2answers
4k 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", ...
11
votes
4answers
37k 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 ...
11
votes
3answers
32k 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" ...
10
votes
3answers
34k 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.
10
votes
6answers
23k 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 ...
10
votes
5answers
11k 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 ...
10
votes
4answers
17k 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 ...
10
votes
1answer
10k views
How do I put logical operators in an Excel =IF Formula?
I'm trying to enter a formula to display text according to an IF condition. The best I can manage is something like...
=IF(myval>=minval & myval <= maxval, "OK", "Not OK")
But this ...
10
votes
11answers
62k 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. ...
10
votes
2answers
10k views
Saving a file in a CSV type in Excel always removes the BOM
I've been trying to find a reasonable solution/explanation (unsuccessfully) to find out why Excel defaults to removing the BOM when saving a file to the CSV type.
Please forgive me if you find this a ...
9
votes
5answers
21k 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?
9
votes
6answers
6k views
How to remove white space from a number
I have a column of number with white spaces in them. Like this: "32 445 423". How do I get rid of all the white space to get excel to treat it as numbers rather than strings?
EDIT: Let me fill in ...
9
votes
2answers
24k views
Excel: How to convert “string” to a “number”?
I ahve a user with a query from an SQL Database that returns a series of string in this form:
000123123.23000
000123123.23000
000123123.23000
000123123.23000
000123123.23000
These are really ...
9
votes
5answers
16k views
Excel: keyboard shortcut to move (swap) column left or right?
Is there a way to move a column (or row) to the left using only the keyboard?
I known I can use ctrl-space (shift-space) to select a column (row), but how to move it to the left or right, actually ...
9
votes
3answers
38k 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?
