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
10answers
103k views

Autonumbering a column in Excel

In my first column, I want to autonumber it from 1, 2, 3, 4, ..., x. How do I do that?
9
votes
2answers
28k views

Compare two spreadsheets and get missing records

I have 2 CSV documents. one has 10.000 rows with 50 columns, first row is a unique SKU. Now I have a second document with 9.500 rows using the same identifiers. I simply want to create a third ...
9
votes
3answers
33k 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 ...
9
votes
6answers
93k 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 ...
9
votes
4answers
23k 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 ...
9
votes
4answers
56k 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 ...
9
votes
4answers
45k views

How can I set a keyboard shortcut to switch between Excel tabs?

I find the Ctrl+ PgDn/PgUpnot good enough. When I have a lot of tabs it's very annoying to get to the right tab, or move it and change the order, which is sometimes important. I need Excel to switch ...
9
votes
3answers
11k 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 (...
9
votes
1answer
2k views

In Excel's chart wizard, how can I use the arrow keys?

This has always bugged me: In the excel chart wizard (and many other places), when you are editing ranges to be used for data (e.g., the "Values" or "Name" fields), if I try to use the arrow keys to ...
9
votes
7answers
47k 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 ...
9
votes
4answers
32k 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 ...
9
votes
4answers
20k views

How to insert empty rows in Excel 2013 when I have copied rows in my clipboard?

This seems like something which should be painfully simple to do but I can't figure it out. So with an empty clipboard I can right click on a row header and say insert row, no problem, it inserts a ...
9
votes
5answers
110k views

Excel documents will open excel with blank window

When opening any excel document it will only bring up the excel window. To open the document I will have to goto file -> open. I am using Office 2013 on windows 7 64bit Only started happening the ...
9
votes
2answers
2k views

Excel conditional formatting fragmentation

Often, I create a sheet with conditional formatting, and set the cell ranges so that the conditional formatting rules are only applied once to a range of cells, e.g. Make $A$1:$A$30 red and Make $B$...
9
votes
4answers
12k 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?
9
votes
2answers
19k 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 co-...
9
votes
3answers
4k 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
3answers
89k 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 ...
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
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 ...
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
5answers
44k 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: = MAX(ABS(MIN(A1:A10)),ABS(MAX(...
8
votes
2answers
79k 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
8answers
32k 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
3answers
20k 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
6k 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
4answers
13k 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
5answers
43k views

How can I stop Excel from opening a second blank window when opening a file from Explorer?

When I open an Excel file from Windows Explorer, I always get a second Excel window as well. Annoyingly, when I close it, it doesn't close, but the other window does! This seems to be a common issue: ...
8
votes
1answer
27k views

How to have Excel chart connect data points when no value?

I'm charting daily values using a line chart. On some dates, I don't have any data. I would like the chart to connect the data points for which I have data across any gap in data. So for example if ...
8
votes
5answers
32k 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
22k 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
5answers
28k 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 ...
8
votes
5answers
25k 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
3answers
29k 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 always ...
8
votes
1answer
13k 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
22k 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
1answer
32k views

Plot date and time of an occurrence

I need to plot (over a period of day measurements) date of occurrence and time. An example is "we are tracking Billy's occurrences of aggression, date and time of the event". How can I show this?
8
votes
2answers
13k views

Open a .csv file in Excel and have it update whenever .csv file changes

Hi I'm working with some web scrapping and the data on my csv files will be constantly changing. I want to open these files on Excel to view and also have it refresh whenever the csv source changes ...
8
votes
1answer
9k 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
4answers
49k 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
2answers
40k views

Why does my excel document have 960,000 empty rows?

I have an excel document, Office 2007, on a Windows 7 machine (if that part matters any, I'm not sure but just throwing it out there). It is a list of all employee phone numbers. If I need to generate ...
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
27k 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 updated....
8
votes
3answers
10k 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
43k 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
2answers
50k 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
5answers
99k views

How to combine values from multiple rows into a single row in Excel?

I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this: ...
8
votes
4answers
78k 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 ...