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)

0
votes
1answer
654 views

Turn one row into multiple rows in Excel

I have multiple items that are available in multiple distribution centers (i.e., a many-to-many relationship). There is currently one row per item, with one column for each distribution center. A cell ...
39
votes
11answers
85k 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 ...
2
votes
1answer
106 views

How can I auto generate date based on number of contacts in a company?

I have a 50,000 row spreadsheet with information such as company, names and dates. Every company has a starting date of 4/8/15 and successive entries in the date column (within each company) are 14 ...
6
votes
2answers
16k 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 ...
49
votes
11answers
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 | ...
63
votes
12answers
80k 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 ...
6
votes
3answers
6k views

How to repair a corrupted Excel file

I'm trying to restore some files for a friend who messed up a USB stick by pulling it out of the computer too soon. By searching invisible partitions I was able to restore the files. All files are ...
5
votes
2answers
30k views

How do I split one row into multiple rows with Excel?

I have a product database in Excel with several hundred entries, each of which has from 1 to 3 "tiers" of pricing: Standard, Deluxe, and Premium. Each tier has its own SKU (A, B, or C added on to the ...
73
votes
13answers
171k 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", ...
11
votes
13answers
52k views

Display Blank when Referencing Blank Cell in Excel 2010

I have an Excel 2010 workbook that contains a number of individual worksheets. The cells on one of the sheets are linked to individual cells on two other worksheets in the same workbook. I'm using a ...
30
votes
8answers
487k 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
2
votes
3answers
6k views

How do I transpose data in columns to rows?

How do I transpose data in columns to rows? If the data is: Joe some city some state Bob no city no state and I use Paste Special » Transpose, I get Joe some city some state Bob ...
5
votes
2answers
14k views

How do I use Excel 2010 Personal Macro Workbook?

I want all of the macros that I've written to always be available when I'm using Microsoft Excel 2010. How can I do this?
4
votes
5answers
34k views

Comparing Similar Text Strings in Excel

I am currently trying to reconcile “Name” fields from two separate data sources. I have a number of names that are not an exact match but are close enough to be considered matched (examples below). ...
49
votes
16answers
346k 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. ...
178
votes
5answers
440k 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 ...
6
votes
5answers
58k 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 ...
4
votes
1answer
3k 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, ...
62
votes
7answers
130k 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 ...
3
votes
1answer
19k 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 ...
0
votes
2answers
10k 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 + ...
4
votes
1answer
460 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 ...
0
votes
2answers
2k views

VLOOKUP with multiple criteria

I have a workbook with 2 sheets: spg and data From the data sheet I wish to do a vlookup in the spg sheet, based on the value of multiple columns in spg. I can do a =VLOOKUP(H5,spg!$D:$I,6,0) but ...
0
votes
3answers
12k 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 ...
25
votes
6answers
139k 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 ...
4
votes
4answers
79k views

Excel - How to vlookup to return multiple values?

I'm looking to use Excel to look up and return multiple reference values for a given key. VLookup does something very similar to what I need - but only returns a single match. I assume it'll involve ...
6
votes
4answers
80k views

How to combine values from multiple rows into a single row in Excel?

I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this: ...
4
votes
4answers
27k 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
2answers
26k views

How do I load external images into Excel? [duplicate]

I have a table with a column of links (URLs) to images. I want to load the linked images into the next cell over. For example: A1 contains http://example.com/image.jpg I want to load the image ...
6
votes
4answers
28k views

How can I merge hundreds of Excel spreadsheet files?

I have hundreds of excel files which are all of the same format (i.e. 4 worksheets per Excel file). I need to combine all the files into 1 all singing and dancing file which must have the same format ...
5
votes
2answers
4k views

Excel 2007 transpose/combine multiple rows into one

I have data like so: 1001NCCN 3618127 1001NCCN 208478 1001NCCN 207316 1001TEMN 409889 1001TEMN 801651 1001TEMN 273134 1001TEMN 208478 1001TEMN 207316 I need to ...
7
votes
1answer
19k 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 ...
6
votes
1answer
2k 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 ...
54
votes
6answers
86k 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 ...
15
votes
2answers
95k 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 ...
34
votes
8answers
149k 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?
74
votes
9answers
265k 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 ...
29
votes
7answers
134k 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
4answers
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. ...
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 ...
8
votes
2answers
73k 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.
13
votes
2answers
32k 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?
1
vote
2answers
3k 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 ...
11
votes
4answers
7k 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
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 ...
4
votes
3answers
2k views

Calculating the average of variably sized blocks of numbers within a column?

In column A I have a set of numbers (over 1,000), and I want to get an average of a block of them at a time, (e.g., 10, so A1:A10 then A11:A20 and so on) and write these averages into column B, such ...
2
votes
2answers
56k views

Automatically update cell in Excel with current timestamp when another cell is changed

In an Excel spreadsheet, I'd like to automatically update a cell with the current date and time when another cell is changed (like an update timestamp). There is a timestamp for each row. I'd like to ...
1
vote
0answers
8k views

How do I fix a corrupted XLS file? [duplicate]

I have an excel file that has recently become corrupted. It was created back in 2006 and I was wondering if anyone knows of a quick and easy way to extract the data. The exact error message is: ...
63
votes
7answers
79k 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 ...
11
votes
1answer
26k 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?