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)

10
votes
3answers
28k views

Is there any shortcut to iterate through Excel's worksheet tabs?

Is there any keyboard shortcut to iterate through Excel's worksheet tabs more easily? Maybe something that would allow me to see all the tabs together and select one of them to switch to it.
10
votes
6answers
33k 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 ...
10
votes
8answers
24k views

How to remove white space from a number

I have a column of number with white spaces in them. Like this: "32 445 423". How do I get rid of all the white space to get excel to treat it as numbers rather than strings? EDIT: Let me fill in ...
10
votes
2answers
5k views

Is is possible to get Excel to insert an absolute reference when clicking on cells to build a formula?

If you build a formula by clicking on a cell to insert its address into the formula, is there a way of making this reference absolute rather than relative? I've been using Excel for many years but ...
10
votes
2answers
8k 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 ...
10
votes
4answers
23k 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?
10
votes
4answers
30k views

Can you do Regular Expressions in Excel without VBScript?

I'm looking for an Excel function that I can put in a cell to do a regex search of the contents of another cell. Is there anything available to do this? I'd prefer not to have to add VB Script to ...
10
votes
1answer
2k views

Change Excel day name to other languages

How can I modify Excel List to get day names returned by TEXT(TODAY()) e.g. in other languages instead of English ?
10
votes
2answers
8k 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 ...
10
votes
4answers
24k 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 ...
10
votes
2answers
8k views

Keyboard shortcut to switch between sheets in Excel

Does Excel 2007 have a keyboard shortcut that switchs between worksheets?
10
votes
11answers
42k 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 ...
10
votes
1answer
18k 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?
10
votes
2answers
4k 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 ...
10
votes
2answers
2k views

Date based sum in Excel / Google Docs spreadsheets

I have a bunch of rows with a date and a dollar amount (expenses). I want to produce a list of the days of the month and what the balance of the expenses is. So, for example the 5th entry in the ...
9
votes
3answers
26k views

Is there an Excel formula to identify special characters in a cell?

We have about 3500 documents whose filenames need to be manually scrubbed to remove special characters like brackets, colons, semicolons, commas, etc. I have a text file that I've dumped into excel, ...
9
votes
1answer
46k 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 ...
9
votes
4answers
31k 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 ...
9
votes
9answers
37k views

Stop excel from converting copy-pasted number/text values to date

I'm copy-pasting some data from html table into excel. But excel automatically converts some text or number values to date! When I change the format, the number is perversed, the number is something ...
9
votes
6answers
33k 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 ...
9
votes
4answers
16k 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 ...
9
votes
3answers
24k 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
4answers
11k 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)?
9
votes
2answers
17k 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 ...
9
votes
2answers
23k 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?. ...
9
votes
1answer
12k 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
2answers
23k 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
6answers
4k views

Excel 2013: “We couldn't free up space on the clipboard. Another program might be using it right now”

Excel 2013 often complains that "We couldn't free up space on the clipboard. Another program might be using it right now" when I'm trying to copy cells. Some say it might be caused by Zone Alarm or ...
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
9k 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
23k 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
2answers
6k 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 ...
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
47k 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.
8
votes
6answers
13k views

How to tell if an Excel cell has a formula or is hardcoded?

Does anyone have a special tip (non-VBA strongly preferred) to tell systematically if a cell has a hardcoded value or is a derived formula? Am dealing with some data that has messy subtotals embedded ...
8
votes
1answer
10k 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
6answers
104k 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 ...
8
votes
3answers
14k views

In Excel how can I sum all the numbers above the current cell?

I want to have a column in Excel that consists of a header, a bunch of numbers, and then have the sum of those numbers at the bottom. I'd like the sum to adapt to the insertion of new numbers above ...
8
votes
2answers
40k views

Quick replace #N/A by 0 when vlookup

I use vlookup a lot in excel. The problem is with #N/A value when the seek value is not found. In that case, we often replace it by 0 using if(isna(vlookup(what,range,column,false));0; ...
8
votes
1answer
7k views

Stop Excel recognizing a dash

How can I stop my text cells which start with a dash from turning into cells where Excel expects a formula?
8
votes
6answers
56k 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
1answer
40k 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 ...
8
votes
4answers
32k views

Downsides to Microsoft Excel 2007 XLSB binary format? [closed]

Excel 2007/2010 offers an option to save my files in an "Excel Binary Workbook" format that seems to be smaller (50% of the size in some cases) and load more quickly. Is there a downside to this ...
8
votes
3answers
47k 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?
8
votes
1answer
19k 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
1answer
6k 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. ...
8
votes
1answer
21k 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
34k 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
23k 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
2answers
7k 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 ...