**10**

votes

**3**answers

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

**6**answers

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

**8**answers

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

**2**answers

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

**2**answers

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

**4**answers

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

**4**answers

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

**1**answer

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

**2**answers

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

**4**answers

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

**2**answers

8k views

### Keyboard shortcut to switch between sheets in Excel

Does Excel 2007 have a keyboard shortcut that switchs between worksheets?

**10**

votes

**11**answers

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

**1**answer

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

**2**answers

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

**2**answers

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

**3**answers

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

**1**answer

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

**4**answers

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

**9**answers

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

**6**answers

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

**4**answers

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

**3**answers

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

**4**answers

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

**2**answers

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

**2**answers

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

**1**answer

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

**2**answers

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

**6**answers

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

**3**answers

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

**2**answers

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

**2**answers

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

**2**answers

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

**1**answer

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

**2**answers

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

**6**answers

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

**1**answer

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

**6**answers

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

**3**answers

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

**2**answers

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

**1**answer

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

**6**answers

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

**1**answer

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

**4**answers

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

**3**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**4**answers

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

**2**answers

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 ...