# Questions tagged [microsoft-excel]

A spreadsheet application by Microsoft. Use this tag along with [vba] if your question involves programming Excel in VBA, and with [worksheet-function] if it involves an Excel formula or worksheet function.

**64**

votes

**10**answers

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

**64**

votes

**11**answers

106k views

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

**1**answer

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

**19**

votes

**17**answers

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

**8**

votes

**2**answers

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

**2**

votes

**1**answer

229 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 ...

**100**

votes

**14**answers

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

**188**

votes

**13**answers

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

**16**

votes

**4**answers

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

**45**

votes

**11**answers

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

**10**

votes

**5**answers

174k 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:
...

**11**

votes

**7**answers

118k 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). ...

**264**

votes

**6**answers

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

**10**

votes

**4**answers

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

**5**

votes

**2**answers

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

**84**

votes

**16**answers

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

**55**

votes

**16**answers

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

**2**

votes

**3**answers

11k 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 no ...

**1**

vote

**2**answers

16k views

### How to Split one column in Excel into multiple columns of 55 rows

I have a movie list with over 1500 entries (A1-A1500), all alphabetized (Thanks to Excel) and I would like to split that into multiple columns of 55 rows each on a separate sheet if possible. So A1-...

**80**

votes

**8**answers

270k views

### How to get Excel to interpret the comma as a default delimiter in CSV files?

I have a number of .csv files. Some of them are comma delimited, some are tab delimited (maybe they should be called .tsv ...)
The csv extension gets associated with Excel when Excel is installed. ...

**25**

votes

**4**answers

81k views

### Excel function that evaluates a string as if it were a formula?

Suppose I have a text string like "11+5" or even "=11+5" stored in a cell. Is there a function in Excel that will allow me to actually evaluate that string as if it were a formula?
This would be ...

**31**

votes

**3**answers

29k views

### Prevent Excel from clearing copied data for pasting, after certain operations, without Office clipboard

Whenever you copy a cell or a range of cells in Excel (my version is 2010 on Windows 7), and then do something else like insert new sheet row or simply edit another cell, Excel removes the clipboard ...

**19**

votes

**4**answers

5k views

### Excel conditional formatting fragmentation

Often, I create a sheet with conditional formatting, and set the cell ranges so that the conditional formatting rules are only applied once to a range of cells, e.g.
Make $A$1:$A$30 red and
Make $B$...

**6**

votes

**2**answers

5k 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 transpose/...

**3**

votes

**1**answer

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

**4**

votes

**1**answer

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

**112**

votes

**10**answers

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

**132**

votes

**9**answers

576k 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 there ...

**92**

votes

**7**answers

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

**31**

votes

**6**answers

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

**32**

votes

**7**answers

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

**37**

votes

**29**answers

378k views

### Unable to get Excel to recognise date in column

I constantly have problems working with dates in Excel, I must be doing something wrong but I don't understand what.
I have a spreadsheet, exported from our exchange server, that contains a column ...

**8**

votes

**2**answers

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

**7**

votes

**3**answers

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

**125**

votes

**6**answers

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

**67**

votes

**4**answers

282k views

### Turn off scientific notation in Excel

I would like to prevent Excel behavior that any large number I enter is stored as a number, then abbreviated to scientific notation.
Is it possible to tell Excel 2007 to stop doing this?

**13**

votes

**3**answers

44k views

### Why is Excel truncating my 16-digit numbers? [duplicate]

If I type in a 16 digit number (format: number, no decimals) it changes the number on me. Example: 1234567812345678 changes the view to 1234567812345670.
If I type it in as a general format it ...

**7**

votes

**5**answers

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

**7**

votes

**6**answers

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

**1**

vote

**2**answers

24k 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 + ...

**8**

votes

**1**answer

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

**2**

votes

**2**answers

235 views

### Why is this simple Excel calculation not netting Zero?

It's supposed to be zero but for some reason the result produces a peculiar series of digits at the end.
Also, here's the .xlsx of it: http://83.212.111.36/Book1.xlsx

**0**

votes

**2**answers

28k views

### MS Excel: Unique random number generator within a range

I searched for a similar question but couldn't find one..
I want to generate 10 groups out of numbers ranging from 1-60 (including both), with each and every group containing random and non-repeating ...

**79**

votes

**8**answers

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

**87**

votes

**6**answers

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

**19**

votes

**3**answers

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

**42**

votes

**5**answers

237k 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.
http://www.microsoft....

**25**

votes

**16**answers

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

**25**

votes

**2**answers

79k views

### How to find and replace the character “*” in Excel

I am trying to find and replace the * (star, or shift+8) character in a bunch of formulas in Excel 2007. * of course matches the ENTIRE contents of the cell, which is not what I want. How do I ...

**10**

votes

**5**answers

100k views

### How do I merge two tables in Excel that have identical columns?

In Excel, I have a spreadsheet that pulls data from a SQL Database into a table and then generates a report based on that data. Unfortunately, the data in this SQL database is incomplete and I want to ...