Questions tagged [microsoft-excel-2010]
The 2010 version of Microsoft's spreadsheet application.
4,701
questions
245
votes
13
answers
677k
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 ...
- 14.4k
169
votes
14
answers
500k
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 ...
- 2,000
132
votes
9
answers
506k
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 ...
- 1,755
105
votes
16
answers
794k
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. ...
- 4,031
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 ...
- 1,285
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.
- 882
66
votes
9
answers
383k
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 ...
- 1,331
65
votes
22
answers
794k
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 ...
- 1,538
61
votes
2
answers
8k
views
Excel - can I set a cell to equal a certain value, no matter what is typed in it?
This is to play a joke on someone...not sure if it's possible to do what I want. We are circulating an Excel file among friends to select players for a golf tournament. I would like to set a cell (...
- 601
60
votes
3
answers
55k
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 ...
- 704
56
votes
17
answers
382k
views
Copy Excel worksheet and maintain relative cell reference in formulas
Another copy problem in Excel:
How can I copy a Worksheet from WorkbookA.xlsx into WorkbookB.xlsx without the copied Worksheet still referencing WorkbookA.xlsx e.g. the formula =B!23 becomes =[...
- 2,069
52
votes
6
answers
258k
views
Ignore multiple "number stored as text" errors at once
I have a spreadsheet where a certain column has a lot of data which generates "number stored as text" errors. I want the numbers to be stored as text in this column for certain formatting purposes. ...
- 13.8k
52
votes
2
answers
310k
views
Create a pie chart from distinct values in one column by grouping data in Excel
I have an Excel document containing information from a survey. I want to create a pie chart over location (countries).
How can I make Excel group all the distinct values together and then display ...
- 981
47
votes
10
answers
280k
views
Is there an Excel function to create a hash value?
I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real ...
- 1,749
40
votes
4
answers
62k
views
Disable navigate to end of list when double-clicking on border of selected cell
I'm not sure whether this is a feature but it's very irritating. When you double click on a border of a selected cell, you go to the end of the list depending on which side of the selected cell you ...
- 579
38
votes
4
answers
448k
views
Excel: filter a column by more than two values
I have a worksheet with a column, user_ID, that contains a large number of user IDs
I'd like to filter it down to about a dozen IDs, but using Filter -> Custom Filter only allows me to filter by 2 ...
- 817
38
votes
3
answers
287k
views
Conditional formatting of TRUE / FALSE values in an Excel 2010 range
I have a range containing formulas that evaluate to TRUE or FALSE.
How would one apply conditional formatting to this range, so that TRUE cells are Green, and FALSE cells are RED?
- 792
37
votes
7
answers
144k
views
How to add cell padding in Excel 2010?
Is there a way to add padding to cells in Excel 2010?
The spreadsheet I'm using has cells in one column with varying row heights. I don't want to center the text, I just want it padded slightly ...
- 481
36
votes
4
answers
192k
views
How to copy conditional formating to another Excel Worksheet
I have set up conditional formatting in a set of cells of "Sheet2" on my workbook. I'd like to reuse this formatting on "Sheet1" (I've spent quite some time setting it up). Is there a way to do it?
...
- 1,017
35
votes
3
answers
118k
views
Pasting text into Excel without splitting on spaces (or other delimiter)
I recently used the Text to Columns feature in Excel to split a column of data into multiple columns based on the space character. Now when I paste text into Excel it automatically splits it into ...
- 1,856
34
votes
20
answers
210k
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 ...
- 349
33
votes
3
answers
238k
views
MS Excel: How to count occurrences of items in a list?
I have a list of items that looks like this:
A
B
C
A
A
B
D
E
A
Now I want to count the number of occurrences of each item. The result should look like this:
A 4
B 2
C 1
D 1
E 1
How can I do that? ...
- 1,795
32
votes
3
answers
78k
views
Grouping labels and concatenating their text values (like a pivot table)
I've got a spreadsheet with data like this:
Product | Attribute
----------+----------
Product A | Cyan
Product B | Cyan
Product C | Cyan
Product A | Magenta
Product C | Magenta
Product B | Yellow
...
- 599
32
votes
1
answer
124k
views
What does the [@[bar foo]] operator do in Excel, in particular the at sign?
I've seen someone construct what seemed like cell references in Excel that looked like
=[@[column title]]*someothercell
What is this feature called, are the rectangular brackets and the at sign ...
- 2,060
29
votes
11
answers
119k
views
Why does this Excel file keep asking to save?
A client sent me some Excel .xlsx documents, and every time I open one of them and then close it, Excel asks if I would like to save changes, even if I didn't make any. This doesn't happen with any ...
- 2,489
29
votes
11
answers
92k
views
How do I keep Conditional Formatting formulas and ranges from automatically changing?
I've found that Conditional Formatting formulas and ranges will automatically adjust when you copy, delete, or move data around in a spreadsheet. While this is a nice idea, it tends to break things ...
- 13.8k
28
votes
4
answers
16k
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$...
- 4,568
28
votes
3
answers
134k
views
Excel pie chart: How to combine smaller values in a single "Other" slice?
I have this pie chart in Excel 2010 (based on a pivot table):
I'd like to combine all the values smaller than X% in a single "Other" slice. In previous versions of Excel, it was possible to use the "...
- 912
26
votes
2
answers
39k
views
How to import data in Excel from the clipboard as if it was in a text file?
Using Excel you can import data from a text file with a wizard available at -> Data -> From text.
Is there a way of using the same wizard to import data coming from the clipboard without having to ...
- 2,065
25
votes
3
answers
64k
views
Why do I have to hit tab twice to Alt+Tab away from Microsoft Excel?
In other apps, when I hit Alt+Tab it switches to the previous app. Before I actually release the Alt key I can see a list of windows as well and the previous windows is selected. But in Excel 2010 (...
- 2,714
23
votes
6
answers
48k
views
Reference a cell of the previous row in the same table in Excel?
How can I reference the cell of
the Color column,
in the previous row,
of the same table,
from a cell of the same table? Without relying on the absolute row / column numbers of the worksheet if ...
- 862
22
votes
4
answers
4k
views
How does Excel handle composition of functions?
I was trying to use functions to get today's day as a number in Excel. For example, when I wrote this question I should have obtained $18$.
I tried to use:
the function TODAY() which returns today'...
- 425
22
votes
4
answers
83k
views
Excel VLOOKUP by second column using table name as range
Using the example table below, I can use the formula =VLOOKUP("ABC123456",Table1,3,FALSE) to lookup the Demand value, but I want to do be able to perform the lookup by using the Cust Part field ...
- 6,040
22
votes
4
answers
69k
views
Show Excel sheet tab in ≥ 2 rows
Some days I'm working with extremely large Excel files.
They have too many sheets that can't be shown in one sheet tab. (see image)
So, I'm wondering whether or not we can show Excel sheet on more ...
- 1,410
22
votes
2
answers
54k
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?
- 12.7k
22
votes
5
answers
37k
views
How do I export only visible cells from Excel to CSV?
I am running Excel 2010. At the top of my spreadsheet I have about 10 hidden rows.
When I export to CSV, how do I export only visible cells?
- 221
21
votes
13
answers
129k
views
How to toggle Scroll Lock in Excel 2010 without a Scroll Lock key in the keyboard
I have a notebook with no Scroll Lock key (or any shortcut using the 'Fn' key).
Somehow Excel 2010 got the Scroll Lock on. I see it left down in the status bar and now I can't navigate through cells ...
- 417
21
votes
10
answers
159k
views
Excel table not formatting new rows appropriately - where does formatting come from?
I have an excel table and all rows are formatted the same. When I add a new row, the formatting does not match the rows above - so every time I add a row I have to select the above row and copy/paste ...
- 525
20
votes
5
answers
17k
views
How do I get the =LEFT function in excel, to also take the number zero as the first number?
My data contains a column of room numbers. Each room number's first 2 digits is the floor number where the room is located. The =LEFT function works well for data that does not start with zero. The =...
- 201
20
votes
3
answers
45k
views
How to inspect / view an Excel macro before "enabling macros" for a sheet?
I'm receiving occasionally an xls file that contains a macro which I should use. If I don't have complete trust in the sender, I wish to look at the macro's source in order to verify that it indeed ...
- 9,853
20
votes
6
answers
28k
views
How do I export a chart as a png in Excel 2010?
Given a chart (normal column chart of whatever), how do I export that chart and only that chart as a PNG?
20
votes
6
answers
76k
views
Can you pull Excel data from an external (and dynamic) .csv file?
I've got a script that outputs a number of performance metrics for a computer into a .csv. The Excel does a basic stats check for standard deviation, etc.
I realize you can import .csv files into ...
- 325
20
votes
7
answers
84k
views
Median in Pivot Table in Excel 2010?
Astonishing that this functionality is not present in such an ancient application
Is there a known workaround?
I'm on about the part where you can change the aggregation type for a value field:
It ...
- 2,067
19
votes
6
answers
107k
views
Check if Excel cell text is numeric using formula only
I have to find out if my cells text is a numeric value and wanted to use an elegant non VBA method that doesn't impede on its current state or value.
What I've found is that the ISNUMBER() function ...
- 513
19
votes
2
answers
4k
views
Excel week number inconsistent results
I am using a spreadsheet that includes the week number for a particular date. After some research, I found a function ISOWEEKNUM() that I could apply to a column of dates and this appeared to work ...
- 193
19
votes
3
answers
71k
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 ...
- 191
19
votes
4
answers
125k
views
convert text string to date/time format
I have a column that is text formatted like 20110408_2041. How can I format it to date/time format?
- 326
19
votes
8
answers
38k
views
Increase size limit of data import from csv into Excel
I have a load of data in a csv file that I'm importing into Excel, most of it is fine, but one of my columns contains up to 1000 characters of data. Unfortunately, Excel has taken it upon itself to ...
- 528
18
votes
6
answers
70k
views
Delete ALL custom cell styles EXCEL
Is it possible to delete ALL the custom/created cell styles in a workbook ? Just leaving the default styles.
Without having to delete them all one by one
- 7,435
18
votes
2
answers
100k
views
How can I create a properly aligned curly brace that spans multiple rows or columns in Excel?
I would like to group a couple of columns or rows together and add a note to them. When I try that with normal letter-resizing, it gets ugly, misaligned to the rows, too bold and the centered text ...
- 1,473