The 2010 version of Microsoft's spreadsheet application.

learn more… | top users | synonyms (1)

26
votes
4answers
93k 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 ...
25
votes
13answers
179k 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. ...
19
votes
7answers
38k 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", ...
14
votes
3answers
54k views
12
votes
3answers
4k views

How to insert a date into an Excel spreadsheet?

I want to insert date and time into a cell of an Excel spreadsheet. NOW() and TODAY() don't fit my needs. These functions insert dynamic date. But I want static: if I insert the date it won't change ...
11
votes
10answers
42k 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 ...
10
votes
2answers
18k 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?
10
votes
3answers
24k views

Excel 2010 on multi monitor screen

With office 2010 I would like to open two excel document on different monitor. The only way I achieved to do this was to widden the Excel main window over the both screen which is not very convenient ...
10
votes
3answers
7k views

How do I enter dates in ISO 8601 date format (YYYY-MM-DD) in Excel (and have Excel recognize that format as a date value)?

I tried to enter this date in ISO 8601 format (YYYY-MM-DD) in Excel 2010: 2012-04-08, but Excel automatically converts the date format to 4/8/2012. I tried scrolling through the different types of ...
10
votes
3answers
8k 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?
9
votes
2answers
7k views

Excel Compatibility Report: How do I find the offending cells?

I am using Excel 2010, and while saving a workbook as .xls, I get the usual compatibility report: I usually don't care much about it, but this time I need to guarantee that a user of a previous ...
9
votes
2answers
16k 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 ...
8
votes
7answers
27k views

Is there a way to move a column in between existing columns with drag-and-drop?

In Excel 2010, I want to move a column with some data to be in between two existing columns which also have data. However, a simple drag-and-drop of the column will just prompt me to replace the data ...
8
votes
3answers
42k 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? ...
8
votes
2answers
5k 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 ...
8
votes
4answers
8k views

Set Excel to search within entire workbook by default

In Microsoft Excel, when I search using CTRL+F, by default it only searches within the current worksheet. How can I set it to search within the entire workbook by default? I have Office 2010. Is ...
8
votes
2answers
2k 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 ...
7
votes
6answers
25k views

What's the keyboard shortcut to Unhide a column in Excel 2010?

My SHIFT key doesn't appear to be broken, so this one's got me stumped: Looking online, I've been told the keyboard shortcuts for hiding and unhiding columns in Excel are CTRL + 0 and CTRL + SHIFT + ...
7
votes
2answers
6k views

show Excel sheet tab in >=2 row

Some days I'm working with extremely large Excel files, they have to many sheet that can't be show in 1 sheet tab. (see image) So, I'm wondering that whether or not we can show Excel sheet on ...
7
votes
1answer
27k 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 ...
7
votes
2answers
20k views

How to autofill Excel cells by week?

Let's say I type in 7/1/2011 into cell A1. I autofill/drag down the grippie and the cells beneath fill with an incremental days. Is there a way to autofill by week? In other words, cell A2 should ...
7
votes
4answers
15k 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 ...
7
votes
2answers
2k views

How do I get excel to not mess around with the formatting of a .csv file

I have two issues with a csv file that occur when I try and edit it in Excel. I have an ISBN column that gets reformatted to scientific format every time I save the file The encoding for the file is ...
7
votes
1answer
555 views

Using optimization to assign by preference

I have 100 objects ("candies") that I need to distribute between five people so that each has an equal number of candies (in this case, 20 candies per person). However, each person has also expressed ...
7
votes
1answer
1k views

Where can I find guidelines for how to use Excel's “Data and Model” cell styles (specifically “Linked Cell”)?

(I originally asked for "well-known" guidelines, but got no answers. In light of that I think it's fine is anyone just wants to answer with their own guidelines or a link to some they like.) I have ...
6
votes
4answers
16k views

Diagnosing slow Excel spreadsheets?

A client contacted me, complaining of Excel spreadsheets that were taking far too long to open. They use Excel for creating invoices, so they have hundreds of Excel spreadsheets with light formatting ...
6
votes
3answers
17k views

Excel Scatter Chart with Labels

I have three columns of data. Column 1 has a short description, column 2 has a benefit number and column 3 has a cost. I can create a cost/benefit scatter chart, but what I want is to be able to ...
6
votes
2answers
3k 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 ...
6
votes
1answer
6k 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 ...
6
votes
3answers
19k views

How to refresh data in shared Excel 2010 workbook

I have a Excel 2010 workbook that is shared by other users who periodically update various cells. Is there a shortcut key, like F5 with most web browsers, that updates the document with any changes ...
6
votes
6answers
86k views

How do I resolve the error “Excel found unreadable content in (filename)”?

Has anyone else seen this? Sometimes when I open certain worksheets in Excel 2010 I see the message in the title. Excel then asks if I want to repair the file. I say "Yes" and Excel reports that ...
6
votes
2answers
3k views

VBA Runtime error 1004: Method 'OpenText' of object 'Workbooks' failed

After an upgrade to Excel 2010 (from 2003), a script used for standardizing data from Access import has stopped working. It returns an error: "Run-time error '1004': Method 'OpenText' of object ...
6
votes
4answers
11k 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 ...
6
votes
4answers
2k views

How can I create a hyperlink to an HTML file's specific sections within Excel?

I have a HTML file which includes anchors within it. I'm able to get to a specific section via a browser if I specify the anchor name, so when I write ...
5
votes
2answers
11k views

How do I select an entire row in Excel?

How do I highlight all cells in an Excel row? I'm using Excel 2010 on Windows.
5
votes
2answers
12k 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. ...
5
votes
3answers
7k 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 ...
5
votes
4answers
6k 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 ...
5
votes
1answer
45k views

How does one add an axis label in Microsoft Office Excel 2010?

As the title says: How does one add an axis label in Microsoft Office Excel 2010?
5
votes
3answers
32k 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?
5
votes
2answers
22k views

How do I create custom axes in Excel?

I'm creating a "histogram" and need the values of "frequency" to be displayed in ascending or descending order, which means that I need custom values for the x-axis. Instead of having: 1 2 3 4 5 ...
5
votes
3answers
65k views

how to add an average line to the graph in excel 2007 or 2010

In OpenOffice I'm getting trend-line and average-line properties by RightClick. But in Excel 2007 I see only trend-line. Same with Excel 2010. How do you make an average-line in Excel?
5
votes
2answers
3k views

How can I stop Excel from rapidly refreshing/flickering while data is being loaded from external sources?

I have an Excel spreadsheet with several charts on the same worksheet. Each chart's data comes from a different table. Each table's data is pulled from an Access database, and the connections for each ...
5
votes
2answers
421 views

Split Excel column with XML inside

Is there a way to break an Excel column containing XML into several columns like this: BEFORE | Apples | Pears | XML | | ------ | ----- | ...
5
votes
2answers
228 views

How do I expand columns of items in Excel so that each item is on its own line?

I have this: -----A-----|-----B-----|-----C----- 1|cherry |apple |banana 2|kiwi |banana |cantaloupe 3|peach |grape |peach 4| |peach |watermelon 5| ...
5
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 ...
5
votes
3answers
5k views

Excel formula for “select sum(B) where month(A)=3”

I have excel data as follows: I want show the Total Cost for each month. I can achieve this in Google Spreadsheets with the following formula: =INDEX(QUERY(COSTS!$1:$1101,"select sum(B) where ...
5
votes
4answers
19k views

Change border color of multiple cells in Excel 2010?

While it is easy to change the font color and fill color of multiple cells at once in Excel 2010, I can't find a way to do this with border colors. Is this possible?
5
votes
2answers
2k views

Percentile of order in excel pivot-table

So I have a spreadsheet with orders that I have organized into a pivot-table that shows the right things etc. Where there are currently column totals (summing all the rows before), I want to add ...
5
votes
1answer
138 views

Excel spreadsheet undoes/reverts to previously saved state

A very unusual problem hunts my colleague From time to time when she works with Excel 2010 (numerous workbooks, some of them quite large), the computer "decides" to undo everything that was done since ...