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)

4
votes
2answers
6k views

Remove password from an Excel Document

I'm providing internal support and one of our users has managed to put a password on an excel file by accident, I've done the proper checks to make sure that the user should have access to the ...
14
votes
9answers
34k 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 ...
33
votes
8answers
23k 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 ...
28
votes
8answers
29k 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 | ...
4
votes
1answer
1k views

Excel 2010 conditional formatting: selectively highlighting duplicates

I'm using Excel 2010 and would like to know if there's any way I can choose conditional formatting to highlight only instances of duplicates when it's found in ALL the columns I chose? For instance, ...
90
votes
5answers
170k 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 ...
2
votes
1answer
9k views

Sum Values in One Range Based on Criteria in Another

I have a simple spreadsheet, two columns "Amount" (col Q) and "Account" (col O). I want to get the sum of the "Amount" column where the corresponding "Account" forum equals "Paypal". This is as far ...
69
votes
13answers
224k 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 ...
1
vote
2answers
830 views

Extract values from a range that are missing from another

I've got two columns in Excel, "ROSTER" and "PRESENT", shown below: Is there a formula to achieve the "NOT HERE" column? I tried using VLOOKUP() and http://superuser.com/a/289653/135912 to no avail ...
4
votes
1answer
249 views

Grouping data columns by shared values

I don't know how to properly describe what I need to do, so I will give an example. A colleague has a data set in Excel like so: Col A Col B Col C aaaaa aaaaa bbbbb bbbbb ccccc ...
4
votes
1answer
1k views

Conditional formatting to highlight the whole row

I would like to highlight the whole row 5 if J5="T". I used to do it with the old Excel, the 2010 conditional formatting ignores it. So my rule is like this and it does not work. I used the formula ...
3
votes
2answers
4k views

How do I use Excel 2010 Personal Macro Workbook?

I want all of my macros that I've written to always be available to me when I'm using MicroSoft Excel 2010. How can I do this?
0
votes
3answers
7k views

I cannot open Excel file

I have office 2010 installed and the file was working but suddenly when I try to open it It gives me following error Excel cannot open the file ... because the file format or the file extension ...
6
votes
1answer
13k views

Recover sheet protection password in Excel

I have an Excel workbook. Two years before I set password protection option for one of the sheets. Now I have forgotten the password. Is there any way to recover the password of that Excel sheet?
21
votes
13answers
142k 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. ...
14
votes
8answers
202k 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
13
votes
5answers
50k 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
2answers
46k views

Run an SQL Query With a Parameter from Excel 2007

I have a query in an excel table. I followed this to do it. But how can I add a parameter from a cell into my query? I am looking for a way to do this select * from dbo.Customers where CustomerID ...
6
votes
2answers
14k views

How to control CSV import into Excel 2010

When I open CSV files in Excel 2010 I get entire rows in cells, not distributed according comma separation. How to control this?
5
votes
1answer
1k views

Excel: Is there a registry setting to suppress the warning messages when saving spreadsheets in CSV format?

I work with many CSV files, and am constantly annoyed by the Excel dialog that pops up when I try to save a file: "[your spreadsheet] may contain features that are not compatible with CSV (Comma ...
5
votes
2answers
23k views

Excel: Find a subset of numbers that add to a given total?

I have a column of numbers (lets say it's A1:A100) and I need to find a subset of them that sum to a certain total.
4
votes
1answer
3k views

Why does Office 2010 fail to open an Excel file?

I have a excel file I've put on a file server. When required i access it via a dll, fill it with some data in background only (through ExecuteExcel functionality). It used to work before. Recently I ...
9
votes
7answers
35k views

Excel Auto-Fill 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 ...
5
votes
1answer
6k views

Count number of rows with conditions met in two columns

I have some Excel columns that look like this. 1774 David Not Upheld 1770 James Upheld 1771 David Upheld 1772 Sam ...
3
votes
3answers
20k views

Any Excel function that will reverse a string?

Are there any built-in Excel functions that will reverse a string?
1
vote
4answers
6k views

How to add/subtract months from given date in Excel

Here is my scenario: I have to populate list with dates and some metadata of upcoming events for the following year in my Excel application. Some of the events are occurring on the same day each ...
1
vote
1answer
353 views

Microsoft Excel 2003 - Merge by Column

I'm trying to merge two excel files, here is the structure of the files: [A] [B] I want to add the column B from one file to another as column C, but the thing is that they have some rows under A ...
1
vote
6answers
80k views

Adding a zero before values in an Excel spreadsheet column?

While trying to fill in social security numbers into Excel 2007, my dad found out that he couldn't make the numbers start with a zero (0). Example: 0123456789 is turned into 123456789 What do I need ...
0
votes
6answers
16k views

Convert Excel Text string to Time

I'm trying to Export from an OLD System that stored Time in / Time out as a text string in 24 hour time. For example 3pm, is entered as 1500 (no colon) Is there a simple way I can convert these ...
28
votes
6answers
48k 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 ...
18
votes
7answers
89k 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 ...
14
votes
5answers
57k 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?
10
votes
4answers
24k views

Can you do Regular Expressions in Excel without VBScript?

I'm looking for an Excel function that I can put in a cell to do a regex search of the contents of another cell. Is there anything available to do this? I'd prefer not to have to add VB Script to ...
6
votes
2answers
13k views

How to enter a carriage return in an Excel file

How do you enter a carriage return or CHAR(13) in a worksheet cell manually ? I'm not talking about a line feed or CHAR(10). I've seen this in a file and the carriage return had a very weird effect ...
10
votes
15answers
61k views

How to get the current column name in Excel?

What is the function to get the current line number and the current column name for a cell in Excel?
10
votes
1answer
5k views

True difference between Excel CSV and Standard CSV

What is the true difference between an Excel CSV and a standard CSV? For example, when handling columns with line breaks inside one cell, how do they encode it differently?
4
votes
3answers
17k views

Where is “Browse in same window” in Windows 7

How in the world do you configure IE8 to NOT open excel documents in its window. There was a setting ("Browse in same window") in XP under Folder Options > File Types where you could specify the ...
2
votes
5answers
24k views

Can I split a spreadsheet into multiple files based on a column in Excel 2007?

Is there a way in Excel to split a large file into a series of smaller ones, based on the contents of a single column? eg: I have a file of sales data for all sales reps. I need to send them a file ...
1
vote
1answer
2k views

Excel - pivot values in one cell (as comma-separated value)

Excel - pivot values in one cell (as comma-separated value) I have two columns of data: Supplier1|Product1 Supplier1|Product2 Supplier1|Product4 Supplier1|Product7 Supplier2|Product3 ...
1
vote
2answers
2k views

Transpose matrix-style table to 3 columns in Excel

I have a matrix-style table in excel where B1:Z1 are column headings and A2:A99 are row headings. I would like to convert this table to a 3 column table (column heading, row heading, cell value). It ...
9
votes
3answers
2k views

Is it possible to get the sheetname?

How can I get the sheet name in a cell? (I have more than one sheet, I want to get the current on each sheet)
8
votes
1answer
8k views

How can you make Excel 2007 stop formatting large numbers as scientific notation?

Kind of the inverse of this question. I frequently download CSVs from outside sources as part of my programming work. I like to examine their data in Excel because, let's face it, Excel is an ideal ...
3
votes
4answers
6k views

Changing bounds of excel file embedded in powerpoint

When I embed an excel files into powerpoint, I'm having two issues. On some of the spreadsheets, empty columns are displayed on the right hand side of the powerpoint slide On other spreadsheets that ...
2
votes
1answer
3k views

Find and replace multiple values within selected cells

I want to run a find and replace on multiple values within a range in excel with values from 2 columns: A with the original word; B with the translation. I already found the VBA code to get this ...
2
votes
3answers
2k 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" ...
2
votes
1answer
4k views

Excel: why doesn't “File-> Save As” remember the last directory that you saved a file in

i have to save about 1000 different csv files and every time i click file save as, it goes to some default directory. is there anyway it can default to remember the directory that i saved the last ...
1
vote
2answers
373 views

Find and keep matches from 2 columns in Excel

In Excel I have two columns, one with about 3000 rows and one with about 100. I need to find all of the matches between the two columns and put them in a separate column. I have to do this every ...
0
votes
2answers
1k views

Excel - how to either have direct data input or cell value from formula

I would like to either populate a cell value from direct key input or have the cell value derived from a formula. Example: either directly input a number into cell A4 or have the value for A4 = A2 + ...
3
votes
3answers
36k 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 ...
1
vote
3answers
4k views

How to identify cells in Column A that contain any of the values in Column B

First off, I'm not that advanced of an Excel user. I'm certain Excel can do what I'm about to ask, but I've yet to find a way in any of my attempts or searches. My question is very similar to Find if ...