Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X. If your question is about programming Excel in VBA then also tag it excel-vba. If it is about an Excel formula or worksheet function, then also tag it worksheet-function.

learn more… | top users | synonyms (2)

7
votes
2answers
34k 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 ...
7
votes
2answers
13k 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
4answers
29k 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
2answers
4k 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
3answers
21k 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': ...
7
votes
1answer
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 ...
7
votes
2answers
5k views

Excel 2013 Help Offline View

Is there a way to enable offline viewing of Excel 2013's help window like that of 2010's? Every time I hit the F1 button, Excel wants to connect to the internet to help me! Thank you! Edit: Is ...
7
votes
2answers
1k views

Formula for finding 'near perfect' matches [duplicate]

I have a list of +-8000 items which is the result after applying numerous techniques to remove duplicates (the list started at over 10000 items). I now have the following issue - Example 1. Jack ...
7
votes
2answers
4k views

VBA Runtime error 1004: Method 'OpenText' of object 'Workbooks' failed

After an upgrade to Excel 2010 (from 2003), a script used for standardizing data from Access import has stopped working. It returns an error: "Run-time error '1004': Method 'OpenText' of object ...
7
votes
1answer
963 views

Using optimization to assign by preference

I have 100 objects ("candies") that I need to distribute between five people so that each has an equal number of candies (in this case, 20 candies per person). However, each person has also expressed ...
7
votes
4answers
16k 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 ...
7
votes
1answer
33k 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 the cells having a distinct value a unique color without manually creating a conditional ...
7
votes
1answer
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
1answer
274 views

Keyboard shortcuts for selecting range using Inputbox in Excel

I am using Excel 2010 and I have a problem with using keyboard shortcuts for choosing ranges in an Inputbox. I am using a standard Application.Inputbox with type:=8. The code prompts the user to ...
6
votes
3answers
34k views

Any Excel function that will reverse a string?

Are there any built-in Excel functions that will reverse a string?
6
votes
5answers
25k 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: = ...
6
votes
5answers
10k views

How to return everything between two characters in Excel?

I'm a programmer with lots of languages, but somehow I've never learned Excel macro or formula language. I'm sure there must be a simple way to do this, but I'm stumped. I have a column of file names ...
6
votes
2answers
23k 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 ...
6
votes
2answers
14k views

How do I insert formula in between sentences?

How do I insert formula in between sentences? For example, in A1 i want to write: Welcome =C1 So =C1 will replaced with anything in C1 cell. If in A1 I write Welcome, then in B2 I write =C1 then I ...
6
votes
3answers
21k 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 ...
6
votes
3answers
62k 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, ...
6
votes
10answers
15k 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 ...
6
votes
3answers
20k 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 ...
6
votes
1answer
32k views

How to bypass the VBA project password from Excel

I need help with password retrieval or bypassing the password on a project that was done at work. The author has used MS Excel to do the project, and it's in the client's server. The server that we ...
6
votes
8answers
20k views

Changing bounds of excel file embedded in powerpoint

When I embed an excel files into powerpoint, I'm having two issues. On some of the spreadsheets, empty columns are displayed on the right hand side of the powerpoint slide On other spreadsheets that ...
6
votes
3answers
9k 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?
6
votes
3answers
21k 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 ...
6
votes
3answers
6k views

xlsx files are being interpreted as zip files on download, why?

Background: For some reason, whenever a user tries to open an xslx (excel 2007) file from our intranet using MSIE, the file download dialog interprets it as a "zip" file. Admittedly, xslx files ...
6
votes
4answers
29k views

How do I merge two tables in Excel that have identical columns?

In Excel, I have a spreadsheet that pulls data from a SQL Database into a table and then generates a report based on that data. Unfortunately, the data in this SQL database is incomplete and I want to ...
6
votes
2answers
17k 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 ...
6
votes
3answers
9k views

Excel: Custom number formatting

I don't know much about the rules on custom number formatting in Excel, and I need to learn more. When I open the related window (Format Cells > Numbers > Custom), there are some predefined ...
6
votes
2answers
749 views

OpenOffice Calc: reduce file size when saving as Excel

I'm working on a spreadsheet together with another person, sending new versions forth and back by email. It contains nothing fancy. While he uses Windows I'm on Ubuntu 12.04.4 LTS. The first version ...
6
votes
2answers
3k views

How to paste table from MS Excel to Wolfram Mathematica?

When I am pasting a region from MS Excel, which contains multiple numbers, to Mathematica, I am getting just a long plain sequence of digits, i.e. one integer number in Mathematica. How to have a ...
6
votes
5answers
1k views

Excel Formula to Calculate Duration of Time Based on a String of Text Such As 9:00am — 12:30pm

I'm trying to develop an Excel formula that would calculate the actual time duration for a string of a time range of the following format: 1:00am – 3:00am According to this article it seems it should ...
6
votes
1answer
5k 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 ...
6
votes
2answers
14k views

Remove password from an Excel Document

I'm providing internal support and one of our users has managed to put a password on an excel file by accident, I've done the proper checks to make sure that the user should have access to the ...
6
votes
1answer
7k 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) ...
6
votes
5answers
10k views

Copy/paste filtered column in Excel - error message

In short, I have filtered a worksheet by column A, and I want to copy/paste from column B to column C. Obviously I don't wish to copy/paste values from rows that were filtered-out here. The above ...
6
votes
3answers
10k 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 ...
6
votes
4answers
21k views

How can I merge hundreds of Excel spreadsheet files?

I have hundreds of excel files which are all of the same format (i.e. 4 worksheets per Excel file). I need to combine all the files into 1 all singing and dancing file which must have the same format ...
6
votes
5answers
62k views

How to change x-axis min/max of Column chart in Excel?

Here i have a column chart of binomial distribution, showing how many times you can expect to roll a six in 235 dice rolls: Note: You could also call it a binomial mass distribution for p=1/6, ...
6
votes
1answer
3k views

break up a large CSV file

I have a large CSV file (about 6,000 rows) that I want to break up into smaller CSV files (about 100 rows each). Is there an easy way to do that?
6
votes
1answer
24k 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 ...
6
votes
3answers
8k views

Is there Something like a Dark Background Excel Theme?

Is there an easy way to apply a dark theme to my excel windows: black or gray background on all cells, light text. Ideally, this would be just a theme/view that could be applied, rather than ...
6
votes
2answers
23k views

Fit column width to text shortcut?

I know that I can double-click a column header with my mouse, but is there a keyboard shortcut to fit a column to the text width?
6
votes
4answers
7k views

How do I get a reference to the last occurrence of a value in an array in Excel?

So if A1:A5 = {3, 3, 4, 4, 5} what function could I put into B1 to get a reference to A4 (the cell containing the last occurrence of 4)?
6
votes
1answer
939 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 ...
6
votes
3answers
16k views

Excel chart on one column of date/times

I have a list of date/timestamps, and I would like to plot these to a chart in Excel. What I'm looking for is a chart that shows "events per hour" or something similar. Can this be done easily if I ...
6
votes
4answers
31k 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 ...
6
votes
5answers
23k 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 ...