**7**

votes

**4**answers

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

**1**answer

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

**7**

votes

**1**answer

2k views

### Formula does not show superscript character

I have a formula which I want to show a result which includes a superscript character, but I cannot get said character as superscript.
Normally I would just enter text into a cell and I can select ...

**7**

votes

**5**answers

36k views

### Select an entire column minus header row in an Excel macro

How would I access a range that corresponds to an entire column starting at row 2 (there is a header row)?
The end of the selection should be the last non-empty cell in the column.

**7**

votes

**2**answers

140 views

### Conditional concatenate cell content across rows

I have a worksheet with thousands of rows which contain a unique ID A, and one 3 different error codes M. Each individual ID may have 1, 2 or 3 errors. They will always be in the same order.
I have ...

**7**

votes

**3**answers

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

**7**

votes

**2**answers

905 views

### Excel — inserting multiple rows at once

Anybody know how to insert multiple blank rows in Excel all at once? Right now I right-click->Insert but that does just one row.
Thanks

**7**

votes

**3**answers

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

**7**

votes

**1**answer

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

**7**

votes

**4**answers

35k views

### How can I obtain the year quarter from a date?

In Excel, how can I obtain the year quarter from a date, using a worksheet formula? I have a column with dates spanning multiple years.
Jan-01 to Mar-31 = Q1
Apr-01 to June-30 = Q2
etc...
Bonus ...

**7**

votes

**6**answers

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

**7**

votes

**1**answer

5k views

### Get column number of an address in Excel

In cell LJ131 I have written the text string "$HG$86" (w/ out the quotes).
I need to get the column number of the address value represented by that string, $HG$86. Everything I've tried thus far ...

**7**

votes

**4**answers

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

**7**

votes

**6**answers

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

**7**

votes

**1**answer

15k views

### Count number of rows with conditions met in two columns

I have some Excel columns that look like this.
1774 David Not Upheld
1770 James Upheld
1771 David Upheld
1772 Sam ...

**7**

votes

**4**answers

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

**2**answers

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

**2**answers

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

**2**answers

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

**3**answers

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

**1**answer

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

**2**answers

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

**2**answers

24k views

### How to disable the annoying compatibility warnings in Excel?

Every time I edit a CVS or 2003-2007 Excel file it gives a warning that it will be saved in compatibility mode. I have this habit of saving every other minute via Ctrl+S and the window keeps popping ...

**7**

votes

**2**answers

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

**2**answers

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

**1**answer

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

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

**1**answer

290 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

**5**answers

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

**3**answers

60k views

### excel - how to enter formula for entire column

I know if I select the entire column and then enter the formula in first column and then press ctrl+enter, it would be taken care of. However, my first row is title row. How else should I do it?

**6**

votes

**5**answers

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

**3**answers

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

**4**answers

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

**6**

votes

**2**answers

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

**3**answers

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

**10**answers

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

**3**answers

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

**6**

votes

**3**answers

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

**8**answers

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

**4**answers

237 views

### Average numbers if another column contains a certain substring text in Excel

I'm looking for a way to average the numerical numbers in one column, say B, only if the string in column A contains a certain text.
For example:
(In column A) (In column B)
Hello table . . . 1
...

**6**

votes

**5**answers

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

**6**

votes

**6**answers

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

**6**

votes

**2**answers

9k views

### What's the difference between a Table and a Named Range in Excel 2007?

Can someone explain the difference between Excel Tables and Named Ranges in Excel 2007? It seems that in addition to having the features of Named Ranges, they're somehow marked as Tables which gives ...

**6**

votes

**3**answers

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

**4**answers

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

**3**answers

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

**2**answers

813 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

**1**answer

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

**2**answers

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

**5**answers

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