**8**

votes

**2**answers

13k views

### Copying a multiline text into a single cell in Excel

I want to copy the following:
line1
line 2
line 3
into a single cell in Excel.
I know that I can use the combination Alt+Enter on Windows and Ctrl+Alt+Enter on Mac to create a line break in ...

**8**

votes

**4**answers

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

**8**

votes

**3**answers

9k views

### How to paste Excel data as fixed-width text?

If I copy data in Excel and paste it in a text file, I get tabs between cells. That's not good, I want spaces - and I want the columns to be aligned. How can I achieve this without a lot of effort ...

**8**

votes

**1**answer

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

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

**8**

votes

**3**answers

8k views

### Why does pasting into an unprotected cell in Excel protect it?

While researching the answer to this question, I stumbled across a rather odd behavior in Excel related to unprotected cells in protected worksheets.
To see what I mean, create a new workbook, ...

**8**

votes

**1**answer

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

**8**

votes

**2**answers

13k views

### How to convert Excel file with multiple sheets to a set of CSV files?

I have an Excel file with 20 sheets on it. Is there a way to quickly convert the file into 20 CSV files - one for each sheet?
I can do it manually of course, but I'll have to be processing these ...

**8**

votes

**1**answer

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

**8**

votes

**2**answers

3k views

### Excel - Prevent cell text from overflowing into next (empty) cell

When you have text in an Excel cell that is too long to be shown in the visible area of that cell, and the next cell on the right is empty, Excel lets the text be displayed in that next cell (and the ...

**8**

votes

**7**answers

40k views

### Excel destroying special character when saved as CSV

I am working on a project that uses a CSV file supplied by the client to populate a database. The client is to create the spreadsheet, then save it as a CSV to be uploaded, which is then parsed into a ...

**8**

votes

**7**answers

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

**8**

votes

**2**answers

43k views

### Excel: How to display superscript text as data series on chart?

I have a line chart in Excel 2007 that is created from data in the worksheet. It has several series whose titles are displayed in the chart on the right side. The names of the series are of the form ...

**8**

votes

**9**answers

44k views

### Why is Excel 2010/2013 taking 10 seconds open any file?

I have a fast Windows 7 PC with two SSDs and 16GB of RAM, so I'm used to programs loading very fast. But recently, for no reason I can figure out, Excel has started taking way too long to open Excel ...

**8**

votes

**4**answers

73k views

### How to open ods files in Excel?

What is the best way to open ods files in Excel 2003?
Is there some plug-in available? Should I use some convertor to xls? Or would it be best if I just used LibreOffice (or OpenOffice)?
The file I ...

**8**

votes

**2**answers

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

**8**

votes

**4**answers

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

**8**

votes

**3**answers

75k views

### How to I auto-refresh an Excel auto-filter when data is changed?

How to I auto-refresh an Excel auto-filter when data is changed?
Use case: I change the value of one cell to a value that was filtered. I want to see the current row disappearing without having to do ...

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

**2**answers

10k views

### Unix command to get number of lines in a CSV file

I have to get the count of lines from incoming CSV files.
I have used the following command to get the count.
wc -l filename.csv
Consider files coming with 1 record I am getting some files with * ...

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

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

**1**answer

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

**7**

votes

**4**answers

943 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

910 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

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

**11**answers

20k views

### Can you convert an address to a zip code in a spreadsheet?

Given a column of street addresses with city and state but no zip in a spreadsheet, I'd like to put a formula in a second column that yields the ZIP code. Do you know a way to do this?
I'm dealing ...

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

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

**2**answers

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

**1**answer

883 views

### Pull a user-defined season from a date?

So I want to pull a season from a date. I have a seasons table and it goes a little like this.
Jan = Low (low/mid/high = season)
feb = low
mar = low
apr = mid
may = mid
jun = high
jul = high
aug = ...

**7**

votes

**1**answer

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

**3**answers

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

**3**answers

5k views

### How do I prevent Excel from locking files by default?

When I double-click on a CSV file on a network share, the file is opened in Microsoft Excel (which is what I want). However, Excel assumes that I'm going to modify the file, and that everyone else is ...

**7**

votes

**4**answers

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

**5**answers

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

**7**answers

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

346 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

**5**answers

12k views

### Excel CSV import treating quoted strings of numbers as numeric values, not strings

I've got a web application that is exporting its data to a CSV file. Here's one example row of the CSV file in question:
28,"65154",02/21/2013 00:00,"false","0316295","8316012,8315844","MALE"
...

**7**

votes

**5**answers

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

**1**answer

76k views

### Change cell color based on other cell value

I have encountered numerous questions related to changing a cell color based on its value, which can be achieve easily with Excel "Conditional Formatting". However I'm interested in changing a cell ...

**7**

votes

**7**answers

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

**2**answers

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

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

**3**answers

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

**5**answers

18k views

### Conditionally format a cell if it contains formula

Is it possible to conditionally format a cell if the cell contains formula (to alert myself and other users when updating the cell)?

**7**

votes

**1**answer

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