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. Also add the the specific version tag (e.g. [microsoft-excel-2019] or [microsoft-excel-365]) as features and functions may vary between them.
25,845
questions
345
votes
7
answers
1.1m
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
...
243
votes
13
answers
674k
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 ...
220
votes
10
answers
1.1m
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 ...
175
votes
10
answers
386k
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 move-top/...
173
votes
14
answers
1.2m
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 ...
168
votes
14
answers
495k
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 ...
158
votes
5
answers
114k
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 ...
152
votes
5
answers
78k
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 ...
146
votes
8
answers
687k
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?
143
votes
10
answers
376k
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 ...
139
votes
8
answers
705k
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?
137
votes
6
answers
375k
views
How do I 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 ...
135
votes
7
answers
119k
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 ...
132
votes
6
answers
317k
views
Formatting a comma-delimited CSV to force Excel to interpret value as a string
I've been searching around for a while trying to figure out how to output a CSV file in such a way to force Excel to interpret the values as a string and not try to convert them to numbers or dates.
...
132
votes
9
answers
502k
views
Excel - Prevent cell text from overflowing into next (empty) cell
When you have text in an Excel cell that is too long to be shown in the visible area of that cell, and the next cell on the right is empty, Excel lets the text be displayed in that next cell (and the ...
131
votes
9
answers
485k
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. ...
124
votes
20
answers
545k
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?
124
votes
5
answers
553k
views
How to set character encoding when opening a CSV file in 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 ...
122
votes
14
answers
288k
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.
...
120
votes
7
answers
213k
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 ...
119
votes
5
answers
933k
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?
118
votes
2
answers
193k
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?
110
votes
15
answers
222k
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 ...
105
votes
16
answers
792k
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. ...
103
votes
22
answers
242k
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 | ...
102
votes
8
answers
755k
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 ...
101
votes
9
answers
838k
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 ...
96
votes
13
answers
20k
views
For x=3 in Excel, why does = - x^2 + x result in 12 instead of -6?
Suppose my cell A1 in an Excel spreadsheet holds the number 3.
If I enter the formula
= - A1^2 + A1
in A2, then A2 shows the number 12, when it should show -6 (or -9+3)
Why is that? How can I ...
95
votes
3
answers
363k
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
87
votes
5
answers
313k
views
Can I use an excel formula to extract the link location of a hyperlink in a cell?
I have a spreadsheet with a large number of cells containing hyperlinks with display text different than the hyperlink location
ie:
cell location: A1
display text = "Site Info"
hyperlink location =...
86
votes
8
answers
110k
views
How do you view the bottom of really tall cells in Excel?
When there are very tall cells in Microsoft Excel, trying to scroll via mouse or even via scrollbar (clicking on "<-" "->" pointers) just jumps below to the next cell, hiding the bottom content of ...
84
votes
3
answers
30k
views
Excel Conditional Formatting Escaping a Question Mark
I would like to use a conditional formatting rule in an excel file that would color any box with a question mark in it red. It seems that Excel is using a question mark as a wild card and will turn ...
81
votes
7
answers
482k
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?
81
votes
10
answers
207k
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 ...
80
votes
8
answers
380k
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 ...
79
votes
24
answers
586k
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, ...
78
votes
21
answers
611k
views
Excel Auto-Fill a Series Without Mouse (Keyboard Only)
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 attempt ...
76
votes
7
answers
249k
views
How to delete rows not in filter
I have a very large table in Excel (1000's of rows) and I filter it to only show 10 rows.
I wonder if there is a way to delete the rows not shown (i.e. don't meet filter conditions)? This would ...
74
votes
3
answers
309k
views
What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx
Is there a limit? I couldn't find one posted.
73
votes
12
answers
136k
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 | ...
71
votes
4
answers
161k
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 ...
69
votes
2
answers
463k
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 ...
66
votes
11
answers
256k
views
Excel copy-paste: always match destination formatting
I use Excel to crunch numbers, so I usually don't want to copy the formatting along with the text. But this is the default. Is there a way to default to always match destination formatting when ...
65
votes
9
answers
380k
views
How to force Excel to open CSV files with data arranged in columns
I generate a CSV file with an extension .csv in which every piece of data in one line is separated with a comma:
1,2,3,4
1,2,3,4
The file is sent via email and I want that when a customer opens it ...
65
votes
22
answers
789k
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 ...
63
votes
2
answers
350k
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
...
62
votes
5
answers
371k
views
How to split a string based on ":" in MS-Excel?
My excel column is filled with words like this:
1.) ABC:DCF
2.) DCF:FED
I want to split each word based on " : " and put the result in adjacent columns such that "ABC:DCF" in cell "A:1" becomes "ABC"...
62
votes
10
answers
78k
views
How to stop Excel following hyperlinks when I click on them?
In Word I can use Ctrl+Click on the rare occasion that I want to follow a link rather than, you know, do the editing that Word is actually for.
Is there some way to get similar behaviour in Excel ...
61
votes
13
answers
153k
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 ...
61
votes
3
answers
18k
views
Why does the same data take more space in a text file than in an Excel file?
I have around 100000 rows of data. If I store this data in a text file format, it takes more space than if I store it in an Excel file format. Why is that?