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)

15
votes
2answers
35k views

How to control CSV import into Excel 2010

When I open CSV files in Excel 2010 I get entire rows in cells, not distributed according comma separation. How to control this?
15
votes
1answer
24k views

Saving a file in a CSV type in Excel always removes the BOM

I've been trying to find a reasonable solution/explanation (unsuccessfully) to find out why Excel defaults to removing the BOM when saving a file to the CSV type. Please forgive me if you find this a ...
14
votes
8answers
288k views

How can I get a cell in Excel to automatically adjust its height to fit the wrapped text contents? [duplicate]

I have a cell with wrapped text content in Excel and I want to format the cell so that its height will adjust to fit the content which can span over several lines. How can I achieve this behavior?
14
votes
6answers
85k 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 ...
14
votes
3answers
80k views

Excel: Replace occurences of one character with another in a String?

I want an excel formula that will change Haverford, PA to haverford,+pa. (Text is to lowercase, and spaces are replaced with plus characters.) The following will get the first space, but not all of ...
14
votes
4answers
117k 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 ...
14
votes
8answers
52k views

How to export selected columns into csv file

I want to export specific number of columns from excel into .csv file. I have around 10 columns like lname, fname, phone, address, email and so on. What should I do to export only certain columns like ...
14
votes
3answers
10k views

Sorting Excel column by IP address

I have a fairly large (over 200 row) Excel spreadsheet that lists items in my network (i.e. printers, servers, workstations, network projectors, etc), and one of the first columns is the IP Address ...
14
votes
2answers
159k views

Excel chart with two X-axes (horizontal), possible?

Is it possible to create an Excel chart which has two X-axes (i.e. horizontal axes, not vertical Y-axes!) ? I want to use the second axis to plot the normalized value of the default horizontal axis. ...
14
votes
2answers
16k 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 ...
13
votes
3answers
12k views

How to insert a date into an Excel spreadsheet?

I want to insert date and time into a cell of an Excel spreadsheet. NOW() and TODAY() don't fit my needs. These functions insert dynamic date. But I want static: if I insert the date it won't change ...
13
votes
11answers
255k views

Recovering Excel documents that were closed without saving

Can I ensure that if I mistakenly close a Microsoft Excel document without saving the latest version, that I can recover some of the work I've done? I'm using Excel 2003.
13
votes
6answers
50k 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?
13
votes
2answers
68k views

Excel: How to convert “string” to a “number”?

I have a user with a query from an SQL Database that returns a series of string in this form: 000123123.23000 000123123.23000 000123123.23000 000123123.23000 000123123.23000 These are really ...
13
votes
4answers
53k 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, ...
13
votes
2answers
17k 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 ...
13
votes
3answers
44k 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 ...
13
votes
6answers
78k views

How to open xlsx files with Excel 2013 by default on windows 8?

My xslx files open systematically with Excel 2010 when I'd like them to use Excel 2013. Everytime I right click select open with point to C:\Program Files\Microsoft Office 15\root\office15\EXCEL....
13
votes
2answers
10k views

True difference between Excel CSV and Standard CSV

What is the true difference between an Excel CSV and a standard CSV? For example, when handling columns with line breaks inside one cell, how do they encode it differently?
13
votes
8answers
48k views

Table Formatting in Excel 2007: How do I remove it?

I've used the new Table Formatting option in Excel 2007. Now I can't remove it. I've dragged the little blue square up to the last cell on the top left, but it just won't go any further. In fact it ...
13
votes
3answers
130k views

2 Monitors 2 Excel Sheets? [duplicate]

I have 2 Monitors and want to observe on one monitor 1st sheet and on the other monitor 2nd sheet but no matter how many excel files I open they are all on one monitor. Can I split them to two ...
13
votes
3answers
31k 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?. ...
13
votes
3answers
85k views

Color Cell Based On Text Value

An Excel column contains a text value representing the category of that row. Is there a way to format all cells having a distinct value a unique color without manually creating a conditional format ...
13
votes
14answers
3k views

What is an alternative to Excel for editing spreadsheets?

I often have to manipulate Excel and CSV spreadsheets to prepare data being imported into MySQL. I need to do things like find and replace values, insert and remove columns, sort rows, etc. I ...
13
votes
5answers
29k views

Median in Pivot Table in Excel 2010?

Astonishing that this functionality is not present in such an ancient application Is there a known workaround? I'm on about the part where you can change the aggregation type for a value field: It ...
12
votes
3answers
51k views

Any Excel function that will reverse a string?

Are there any built-in Excel functions that will reverse a string?
12
votes
4answers
74k views

How to search for newline or linebreak characters in Excel?

I've imported some data into Excel (from a text file) and it contains some sort of newline characters. It looks like this initially: If I hit F2 (to edit) then Enter (to save changes) on each of the ...
12
votes
2answers
8k views

Can I make Excel beep when a calculation is finished?

I have these really long calculations in Excel. I'd like to divert my attention from the screen and get something done at my desk, but be alerted for when the calculation is finished. Any ideas?
12
votes
12answers
96k views

Unable to get Excel to recognise date in column

I constantly have problems working with dates in Excel, I must be doing something wrong but I don't understand what. I have a spreadsheet, exported from our exchange server, that contains a column ...
12
votes
5answers
117k views

Excel: If statement with #N/A

I have over 6000 records and half of them are formulas that are missing a variable so they result in #N/A on the spreadsheet, what i want to do is if the cell is #N/A then leave the cell blank, ...
12
votes
3answers
16k 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 ...
12
votes
3answers
13k 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 ...
12
votes
3answers
47k views

How do I copy Word tables into Excel without splitting cells into multiple rows?

I have data in a Word table that includes line and paragraph breaks. When I copy the data into Excel, it splits each line and paragraph I want into multiple cells. How do I copy the data to Excel and ...
12
votes
1answer
71k 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 ...
12
votes
2answers
33k views

How can I create a properly aligned curly brace that spans multiple rows or columns in Excel?

I would like to group a couple of columns or rows together and add a note to them. When I try that with normal letter-resizing, it gets ugly, misaligned to the rows, too bold and the centered text ...
12
votes
1answer
16k 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?
12
votes
2answers
16k views

Keyboard shortcut to switch between sheets in Excel

Does Excel 2007 have a keyboard shortcut that switchs between worksheets?
12
votes
3answers
60k views

Can I make an Excel VBA macro available to all workbooks?

Is it possible to write a VBA script in one workbook, and use it in other workbooks in the machine? I guess I'm looking for a way to write a macro package. EDIT Following the instruction in the link ...
12
votes
1answer
28k 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?
12
votes
1answer
29k 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.
12
votes
2answers
39k 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 ...
11
votes
7answers
32k views

i just deleted a worksheet that was critical, what do i do now?

i also saved it is there any way to recover it?
11
votes
13answers
64k views

Display Blank when Referencing Blank Cell in Excel 2010

I have an Excel 2010 workbook that contains a number of individual worksheets. The cells on one of the sheets are linked to individual cells on two other worksheets in the same workbook. I'm using a ...
11
votes
3answers
31k views

Excel formula to convert boolean values into {0, 1}

What is the Excel formula to convert boolean values {FALSE, TRUE} into {0, 1}? Supposing there is one shorter than =IF(cond,1,0).
11
votes
1answer
36k views

How to enter a carriage return in an Excel file

How do you enter a carriage return or CHAR(13) in a worksheet cell manually ? I'm not talking about a line feed or CHAR(10). I've seen this in a file and the carriage return had a very weird effect ...
11
votes
4answers
40k views

How to set the default page layout from RTL to LTR in Excel 2010?

Whenever I open an Excel spreadsheet, it is displayed in a right-to-left layout by default (i.e the right most column is A and I need to scroll to the left to reach further columns). I can change ...
11
votes
4answers
55k 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. ...
11
votes
3answers
110k 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; vlookup(...
11
votes
8answers
126k views

Map one column to x axis second to y axis in excel chart

I'm getting my ass kicked trying to plot simple data into a simple chart in excel (2010 beta). My data is a very simple one: Value Year ----- ---- 1 1900 5 1902 3 1912 ...
11
votes
3answers
28k views

Save microsoft excel chart as jpg

I want to save bar chart from Microsoft Excel 2010 for using as jpg. Is there a way for saving it?