A spreadsheet application by Microsoft. Use this tag along with [vba] if your question involves programming Excel in VBA, and with [worksheet-function] if it involves an Excel formula or worksheet function.

learn more… | top users | synonyms (2)

9
votes
2answers
12k 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
2k views

Excel: Is there a registry setting to suppress the warning messages when saving spreadsheets in CSV format?

I work with many CSV files, and am constantly annoyed by the Excel dialog that pops up when I try to save a file: "[your spreadsheet] may contain features that are not compatible with CSV (Comma ...
9
votes
1answer
1k 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 ...
9
votes
2answers
12k 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 * ...
9
votes
3answers
15k 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 ...
9
votes
0answers
24k views

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

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 ...
8
votes
2answers
72k 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 ...
8
votes
5answers
77k views

How do you insert the current file name in Excel?

I am trying to view the file name that I am working on in the Excel spreadsheet of interest using a formula. For example if the file was called workbook1.xlsx then I would want cell A1 to show ...
8
votes
8answers
28k 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 ...
8
votes
2answers
86k views

Excel: filter a column by more than two values

I have a worksheet with a column, user_ID, that contains a large number of user IDs I'd like to filter it down to about a dozen IDs, but using Filter -> Custom Filter only allows me to filter by 2 ...
8
votes
3answers
18k 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 ...
8
votes
2answers
5k 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.
8
votes
3answers
12k views

How to get file extension in MS Excel?

I have a column containing file paths. I want to make another column which contain the extensions of the paths only. How can I do this in Microsoft Excel?
8
votes
4answers
50k 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 ...
8
votes
5answers
28k 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 ...
8
votes
7answers
21k 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 ...
8
votes
1answer
8k 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 ...
8
votes
3answers
27k views

How can header cells in Excel be “pinned” or “frozen”? [duplicate]

Possible Duplicate: Excel: Keep part of a spreadsheet up during navigation How can you "pin" or "freeze" cells in the top row and/or leftmost column of an MS Excel sheet, so that they ...
8
votes
1answer
11k views

Excel set all column text to one value

I have a problem with Excel. I have added an extra column to my data, and I want to set all the rows of the column equal to the string "Male". Does anyone know how to achieve this?
8
votes
6answers
21k 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 ...
8
votes
6answers
84k 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 ...
8
votes
5answers
22k 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)?
8
votes
4answers
20k 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 ...
8
votes
1answer
8k 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 ...
8
votes
3answers
10k 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
1answer
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
1answer
25k 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
4answers
43k views

How to display or view non-printing characters in Excel?

Is there an option in MS Excel 2010 that will display non-printing characters within a cell (e.g. spaces or the linebreak character introduced by pressing Alt-Enter)?
8
votes
3answers
9k 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
1answer
38k 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
7answers
47k 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
2answers
47k 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
7answers
42k 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
4answers
11k 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?
8
votes
10answers
49k 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
4answers
76k 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
3answers
82k 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
4answers
14k 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
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
1answer
3k 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
2k 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 ...
8
votes
6answers
26k views

How can I make Excel documents open in different windows?

Office 2007, Windows Server 2008 x64. How can I make Excel so that when I double-click a document, it opens in a new Excel instance, so that I can easily view them side-by-side as separate windows ...
7
votes
4answers
986 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
5answers
40k 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
4answers
1k 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
4answers
40k views

Check if Excel cell text is numeric using formula only

I have to find out if my cells text is a numeric value and wanted to use an elegant non VBA method that doesn't impede on its current state or value. What I've found is that the ISNUMBER() function ...
7
votes
4answers
41k 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
4answers
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
1answer
892 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
3answers
26k 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 ...