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 excel-vba. If it is about an Excel formula or worksheet function, then also tag it worksheet-function.

learn more… | top users | synonyms (2)

9
votes
3answers
19k 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 ...
9
votes
2answers
6k 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 ...
9
votes
2answers
7k views

Keyboard shortcut to switch between sheets in Excel

Does Excel 2007 have a keyboard shortcut that switchs between worksheets?
9
votes
1answer
13k views

allow two possible values in a Excel cell

I would like to create a cell format for one cell to only allow the value "yes" or "no" for this cell. How could I do this?
9
votes
10answers
36k views

Multi-row headers and excel 2007 sort/filtering

I have a spreadsheet which uses 2 rows for header information. Filtering implicitly is assuming only 1 row of header information. Sorting has a header row checkbox but that only toggles treating the ...
9
votes
1answer
11k views

How can you make Excel 2007 stop formatting large numbers as scientific notation?

Kind of the inverse of this question. I frequently download CSVs from outside sources as part of my programming work. I like to examine their data in Excel because, let's face it, Excel is an ideal ...
9
votes
1answer
19k views

Hyperlink font in Excel always changes to the default font face/size

For cells containing hyperlinks the font changes automatically into the default one (even though I have selected all cells and applied a different font). How can you configure Excel to use the same ...
9
votes
2answers
3k 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 ...
9
votes
3answers
3k views

How can I dismiss the floating “Auto Fill options” button in Excel 2010?

Reproduce the issue Go to Excel and type fill in the first 2 rows of a column Select both rows of the column Drag down the anchor of the selection (bottom right) 1 row down Now you will see this: ...
9
votes
2answers
8k 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
19k 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 ...
9
votes
1answer
3k views

Setting a default value for a dynamic cascading validation list in Excel 2010

I have finally figured out how to build a dynamic cascading set of lists in Excel using Data Validation and the INDIRECT function. However, I'm running into a problem. I would like to set up my ...
8
votes
2answers
35k 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. ...
8
votes
8answers
12k views

Excel hyperlink not redirecting properly (bug?)

I have an Excel hyperlink problem: I click on, let's say A1, copy the link in it (http://www.godaddy.com/domains/searchresults.aspx?ci=54814), right click on hyperlink and copy that SAME URL as the ...
8
votes
1answer
34k 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 ...
8
votes
1answer
2k views

How do I stop the excel file being in use? [duplicate]

I'm opening an Excel file and I'm getting the File in use pop-up that says it's locked for editing by me. However, I've not got it open on this computer (I think I had it open and my machine ...
8
votes
4answers
25k views

Select each nth row in Excel

I have an Excel spreadsheet with thousands of rows. I want to select every 7th row from that spreadsheet. (By "select," I mean delete all of the other rows, or copy the selected rows into a new ...
8
votes
4answers
17k views

Excel: How do I reference an entire row except for a couple of cells?

I want to be able to sum together the values of an entire row except for the first 2 cells. So something like this: =sum(A3:A*infinity*) How do I do this?
8
votes
4answers
19k 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 ...
8
votes
6answers
45k views

What is the difference between function and formula in Excel?

What is the difference between those two when speaking of Microsoft Excel?
8
votes
4answers
11k views

What is a formula to remove the whitespace from an Excel cell when trim doesn't work?

I have a table of Excel data that I copied from an html table in Internet Explorer. A column of table values all seem to have a space at the end of them, but Trim won't remove it. When I paste-special ...
8
votes
4answers
7k views

Where to find a VBA function's source in Excel

I have a workbook that uses a certain vba function which doesn't appear in the Insert | Insert Function... dialog box list of available functions. How do I find it's source (to see what it does)?
8
votes
1answer
17k views

What's the difference between - CSV (MS-Dos), CSV (Macintosh), CSV (comma delimited)

What's the difference between - CSV (MS-Dos), CSV (Macintosh), CSV (comma delimited) file types in excel 2010? They are all listed as a save file type, but ultimately are Comma Separated Value files.
8
votes
4answers
20k views

How to stop Excel from Auto-formatting and making it work like a number crunching program

I have a real problem with Excel in csv files where it is formatting numbers in certain ways and I cannot seem to get Excel to display the data right no matter what I do. Imagine I have a colum ...
8
votes
1answer
18k 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?
8
votes
2answers
28k views

How do I use a Private Sub in Excel?

I'm want to use the Private Sub functions provided here. Usually a put VBA code in the Visual Basic Editor, I then close the editor and I run the code using "Run this Macro" on the Developer tab. But ...
8
votes
4answers
20k 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 ...
8
votes
4answers
9k 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
1answer
981 views

Where is data validation text stored in Excel?

I have an Excel 2003 sheet with lots of different data validation rules. Is there anyway that they can be viewed other than through the regular data validation dialog? It would be helpful to export ...
8
votes
3answers
3k views

Microsoft Excel Mac, keyboard shortcut to edit in cell

In Microsoft Excel for Mac 2011 (14.0.0) how can I modify the enter/return key to edit in cell instead of moving the selection down? I know that in Preferences > Edit you can uncheck the box that says ...
8
votes
2answers
5k views

Move open Excel document to separate window

Related (but not the same): Open Excel files in new window If I've got an instance of Excel with multiple documents open in the same window. Is there a way to "tear off" one of the windows in ...
8
votes
1answer
312 views

How does the NOW() function get evaluated cross time zones in Excel Online?

How does EXCEL ONLINE evaluate the NOW() function when a spreadsheet is accessed from different time zones? For example, if user PAUL is in London, UK and accesses a sheet at 5PM GMT and user SUE is ...
8
votes
1answer
16k views

Why won't my Excel columns hold the cell format I enter?

I have a spreadsheet which has data linked in from other tabs on the same spreadsheet. Each of the source tabs has a column formatted as a number with no decimal. The tab they're linked to has the ...
7
votes
6answers
27k views

Swap cell contents in Excel?

Is there an easy way to swap the contents of two cells in Microsoft Excel? By easy, I mean either a keyboard shortcut or menu item, without involving copying to temporary cells or writing VBA scripts ...
7
votes
2answers
36k 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.
7
votes
2answers
26k 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
7
votes
3answers
3k views

Excel elapsed time format

In Excel, given a number representing some amount of time (e.g. 25.5 hours), is there a way to format it so that it shows as 1d 1h 30m? Bounty question : Is there a way to add months to this format? ...
7
votes
4answers
21k views

Is there a shortcut in excel that will expand the column width to show the entire column header

Is there a shortcut in excel that will expand all the column width(s) to show the entire column header as well as complete column data? Now I need to go to each and every column in the excel sheet ...
7
votes
1answer
3k views

How do I force a column in excel to be stored as text?

I have a column of hexadecimal strings: 506078 363624 505974 505644 I need excel to store these as strings, not integers or floats.
7
votes
2answers
7k 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
8answers
140k views

How can I get a cell in Excel to automatically adjust its height to fit the wrapped text contents?

I have a cell with wrapped text content in Excel and I want to format the cell so that its height will adjust to fit the content which can span over several lines. How can I achieve this behavior?
7
votes
6answers
12k views

Excel's date format without a year

I'm putting in employees' birthdays, but they come without years for sensitivity reasons. But when I put the month and day in, Excel automatically adds 2012 as the year! Obviously this is not right. I ...
7
votes
2answers
27k 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
6answers
75k views

Why does Excel not auto-update my equations anymore? [duplicate]

For some reason all of a sudden when I update cells in Excel, my equations are not being updated. For example, I have a simple equation =SUM(C4:C16). If I change any of the cells in C4-C16, the ...
7
votes
1answer
4k views

Excel automatic hyperlinks

After years of not wanting Word and Excel to automatically turn things into hyperlinks, the one time I want them to they refuse. I have a list of strings in Excel which I am concatenating with a ...
7
votes
5answers
26k views

Change year in many date fields in excel?

So I've noticed in a worksheet I have many dates that are four years off, no doubt because of improper copy/paste from earlier question. Is there an easy way to convert a bunch of dates I select to a ...
7
votes
2answers
20k views

Why does excel always say “..may contain features that are not compatible with CSV” when saving a CSV file?

Every CSV I've ever tried to save from Excel (2010, anyway) says filename.csv may contain features that are not compatible with CSV (Comma delimited). Do you want to keep the workbook in this format?. ...
7
votes
1answer
28k views

How can I display the name of the current worksheet in a cell?

I want to be able to display the name of the current worksheet in a cell on that worksheet. How can I do that without resorting to VBA scripts? If it matters, the particular flavor of Excel I'm ...
7
votes
1answer
5k views

Why don't arrows keys work in Microsoft Excel?

Watch this video. http://screencast.com/t/SRyUxusqQ In this video, you are seeing me press the down arrow three times, and the up arrow three times. It might have been reversed, I don't remember. ...
7
votes
5answers
30k views

Select an entire column minus header row in an Excel macro

How would I access a range that corresponds to an entire column starting at row 2 (there is a header row)? The end of the selection should be the last non-empty cell in the column.