**8**

votes

**4**answers

13k 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

**3**answers

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

**3**answers

13k views

### Saving filtered results from an Excel workbook to a CSV file

I have an Excel workbook with a lot of rows in it. I filter it using the values of one column and then I want to save the filtered results to a CSV file. When I do "Save as..." I get all the rows. I ...

**8**

votes

**1**answer

2k 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

**8**answers

32k views

### Excel 2010 PowerPivot: How do I show items with no data?

In standard Excel pivot tables, there is an option for fields that allow you to force display of all items even if there are no results for your current selection. Here's the option:
However, using ...

**7**

votes

**4**answers

863 views

### Can I sort the text after a certain character?

I have a large list I'm trying to sort but I'd like to sort the list from the value after a certain character. eg:
241&name=A
123&name=C
645&name=B
So I'd like to sort the list ...

**7**

votes

**4**answers

835 views

### Excel, remove leading 0's from IP address

I have a list of IP addresses in the following form:
010.125.015.013
010.125.153.012
010.125.012.135
I'm unable to execute ping with that form as windows thinks it's dotted octal form.
...

**7**

votes

**5**answers

33k views

### Is there a function in Excel to find the maximum absolute value of a range?

I'm looking for a function in Excel that looks something like
= MAX(ABS(A1:A10))
except ABS() doesn't take a range of numbers.
The best that I can come up with is:
= ...

**7**

votes

**4**answers

40k views

### Go to row in Excel 2007

How do I go to a certain row in Excel 2007?
Amazingly, I can't find the answer to this simple question on Google. Ctrl+G doesn't do it. When I enter a row number under "Reference", it tells me the ...

**7**

votes

**3**answers

4k 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

**4**answers

28k views

### Specifying delimiter for Excel 2007 export

I'm trying to save an Excel 2007 file as a pipe-delimited text file but in the Save As list, I see only tab-delimited and csv options. How do I go about specifying the delimiter, which as I recall ...

**7**

votes

**3**answers

14k views

### Why is Excel truncating my 16-digit numbers?

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

**7**

votes

**2**answers

7k views

### In Excel, can I provide a “default value” in case my formula returns #NA?

For example,
Say the value of a cell is:
IF(ISNA(VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,FALSE)), 0, VLOOKUP(A3,somesheet!G:J,3,FALSE) * ...

**7**

votes

**4**answers

82k views

### Excel extract substring from string

I am looking for a way to extract a variable length substring from a string.
My cells will look something like:
ABC - DEF
ABCDE - DEF
ABCD - ABC
I want to split the string at the - character, so ...

**7**

votes

**5**answers

68k views

### Apply Formula to a Range of Cells without Drag and Drop

Typically, in Excel, one can autofill a column by drag a cell down. However, since a lot of a lot of cells to fill up, manually dragging and scrolling the spreadsheet is time consuming.
Is there a ...

**7**

votes

**3**answers

23k views

### Setting up a column of “enum”-fields in Excel

I'd like to setup a column in excel with several fixed possible values (like 'in-progress', 'done', 'canceled').
I'd like to set it up so the only possible values in these column are from the fixed ...

**7**

votes

**1**answer

34k views

### Formula to add minutes to date+time in Excel

If I have a cell formatted as date+time in Excel, how can I add to this in terms of minutes? It would need to work whether I was adding 20 minutes, or 2,500 minutes.
So, for example if I had this ...

**7**

votes

**4**answers

37k views

### How can one “split” an Excel (.xlsx) file that contains multiple sheets into separate sheet[n].xlsx?

I think the title says it all for this question but to elaborate a bit further:
I have a .xlsx file which contains a few dozen sheets. I want to output all of those sheets as separate .xlsx files. ...

**7**

votes

**3**answers

28k views

### Fixing the first row as sortable title in Excel 2007

I know this is super easy, I just couldn't find it.
I'd like the first row in an excel document to be the "title" field. I want that:
Whenever I scroll down, this row will remain fixed
I can sort ...

**7**

votes

**4**answers

40k views

### Dynamically reference a Named Table Column via cell content in Excel

How do I reference an Excel Table column dynamically in Excel 2007?
ie: i wanna reference a named column of a named table and what table it is will vary with the value of a cell.
I have a Table in ...

**7**

votes

**7**answers

19k views

### How do you keep Microsoft Excel running at full speed even when it does not have the window focus?

I've noticed that when Excel 2007 (and probably earlier versions) has the window focus, Excel runs at full speed which means that files open fast and long-running VBA macros execute quickly.
If the ...

**7**

votes

**2**answers

328 views

### Numbering of rows in a filter

In a range with filter, I want to have a column of row numbers which will change according to the filter, so that rows always count consecutively from 1. For example, the following is a range with ...

**7**

votes

**6**answers

20k views

### Using excel, how can I count the number of cells in a column containing the text “true” or “false”?

I have a spreadsheet that has a column of cells where each cell contains a single word. I would like to count the occurrences of some words. I can use the COUNTIF function for most words, but if the ...

**7**

votes

**4**answers

22k views

### How to batch convert .csv to .xls/xlsx

I am looking for a way to quickly convert a directory of .csv files into the .xls or .xlsx format (would be nice if I could do either/or).
Is there an easy way to do this or do I need to install a ...

**7**

votes

**5**answers

69k views

### Excel: Delete row if cell in certain column is blank?

I am a complete newbie to Excel so please excuse me if this is something easy to do. I have looked through a lot of options but couldn't find what I needed.
Basically, I want to delete all of the ...

**7**

votes

**7**answers

10k views

### Can Excel show a formula and its result simultaneously?

I know that it's possible in Excel to toggle between displaying values and displaying formulas. I'm required to turn in assignments for a statistics class as a printed Excel sheet showing both the ...

**7**

votes

**1**answer

9k views

### How to conditionally create a hyperlink based on value of a cell

ColA Col B ColC
Project A A.pdf A.pdf
Project B No Link
I have a list of project records. One column (e.g., B) ...

**7**

votes

**5**answers

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

**7**

votes

**1**answer

6k views

### Shortcut to accept suggested formula in Excel

I'm working with a lot of formulas in Excel 2007 and I would like to know what shortcut key you need to press to accept the suggested formula.
When I type in =DEG the first suggested formula is ...

**7**

votes

**1**answer

3k views

### Formula does not show superscript character

I have a formula which I want to show a result which includes a superscript character, but I cannot get said character as superscript.
Normally I would just enter text into a cell and I can select ...

**7**

votes

**4**answers

37k views

### Using find() in Excel to remove text from string

I'm using find to remove some of the test string in lookup cell value
For instance I look-up the following in cell A2 Application: sales_rep_support and use find =RIGHT(a2,FIND(" ",a2)+4) to change ...

**7**

votes

**1**answer

29k views

### Excel 2011 Mac auto-recover file lost

Yesterday morning my colleagues Excel 2011 (on Mac OSX 10.8) crashed. He reopened Excel and was asked if he wanted to recover the previous document. Yes, he said and then got a document named ...

**7**

votes

**3**answers

15k views

### How to Label / Tag Items in a Spreadsheet

I'm a novice at spreadsheets, so I hope you'll forgive me if I'm asking a super obvious question.
Is it possible to use tags in Excel/Google Spreadsheet? I'm creating a spreadsheet to log all the ...

**7**

votes

**1**answer

29k 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?

**7**

votes

**4**answers

39k views

### How can I obtain the year quarter from a date?

In Excel, how can I obtain the year quarter from a date, using a worksheet formula? I have a column with dates spanning multiple years.
Jan-01 to Mar-31 = Q1
Apr-01 to June-30 = Q2
etc...
Bonus ...

**7**

votes

**5**answers

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

**7**

votes

**2**answers

337 views

### Conditional concatenate cell content across rows

I have a worksheet with thousands of rows which contain a unique ID A, and one 3 different error codes M. Each individual ID may have 1, 2 or 3 errors. They will always be in the same order.
I have ...

**7**

votes

**1**answer

19k views

### Compare two excel files

I need to compare two Excel Spreadsheets to see if values of each user in a specific column were changed. The new Spreadsheet may have additional users that were added making the row old users ...

**7**

votes

**1**answer

50k views

### How to concatenate a date in MS-excel and receive a date (not a number)

When I try to concatenate two cells in MS-excel in the formula I have the date as a integer number (eg: 04 april 2012 become 41006).
How can I display the date also in the formula cells?

**7**

votes

**3**answers

13k views

### why does an Excel column formatted as number lose its leading zero when the column is formatted as text?

I have to export a spreadsheet to CSV. One of the columns is a zipcode with east coast zipcodes that have a leading zero. When I reformat that column as Text hoping to preserve the leading zero on ...

**7**

votes

**2**answers

974 views

### Excel — inserting multiple rows at once

Anybody know how to insert multiple blank rows in Excel all at once? Right now I right-click->Insert but that does just one row.
Thanks

**7**

votes

**2**answers

6k views

### Export of a custom table style in MS Excel

How to export a custom table style in MS Excel 2007/2010?
I know how to export a custom chart, but I can't see how to export a custom table style.

**7**

votes

**4**answers

9k 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?

**7**

votes

**4**answers

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

**7**

votes

**1**answer

6k views

### Get column number of an address in Excel

In cell LJ131 I have written the text string "$HG$86" (w/ out the quotes).
I need to get the column number of the address value represented by that string, $HG$86. Everything I've tried thus far ...

**7**

votes

**6**answers

35k views

### Merging two cells in multiple rows

I have an excel spreadsheet with two cells in each row that I'd like to merge. Highlighting all the cells of interest and just merging from the cell formatter seems to just merge all the cells ...

**7**

votes

**1**answer

18k views

### Count number of rows with conditions met in two columns

I have some Excel columns that look like this.
1774 David Not Upheld
1770 James Upheld
1771 David Upheld
1772 Sam ...

**7**

votes

**2**answers

15k views

### How can I determine which version of Excel was used to create a .xls file?

Does anyone know of a way to determine which version of Excel was used to create a file? We have some .xls files and need to determine the version. They open fine in Excel 2007 and higher, but a ...

**7**

votes

**2**answers

5k views

### Importing CSV into Excel with commas in quoted fields

I have a CSV file that contains a text field that can have commas in it. When importing it into Excel, it's picking up these commas as field separators. I have the field in quotes, and I've tried ...

**7**

votes

**3**answers

26k views

### Import json data into Excel

I have a text file in json format and want to read it into Excel. A very simplified example of the json file has the following structure:
{ [
{ 'a': 10, 'b': 20 },
{ 'a': 20, 'b': 22 },
{ 'a': ...