**1**

vote

**2**answers

3k views

### excel / google spreadsheet: count substring occurrences within a cell

I've been scratching my head trying to figure out a way to count the number of occurrences of a substring within a cell.
For example:
| | A |
| 1 |John,John,Tom,David|
What ...

**13**

votes

**3**answers

53k views

### What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx

Is there a limit? I couldn't find one posted.

**11**

votes

**5**answers

27k views

### How do I force excel (and other office products) to stop opening files in the same application?

Whenever I "double click" on an Excel file and another Excel file is open, the newly opened file automatically opens in the same application window as the previously opened Excel file. This isn't ...

**8**

votes

**2**answers

2k views

### Prevent Excel from clearing copied data for pasting, after certain operations, without Office clipboard

Whenever you copy a cell or a range of cells in Excel (my version is 2010 on Windows 7), and then do something else like insert new sheet row or simply edit another cell, Excel removes the clipboard ...

**7**

votes

**3**answers

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

**6**

votes

**2**answers

13k views

### Why is Excel removing leading leading zeros when displaying CSV data?

I have a CSV text file with the following content:
"Col1","Col2"
"01",A
"2",B
"10", C
When I open it up with Excel, it displays as shown here:
Note that Cell 2A attempts to display "01" as a ...

**5**

votes

**2**answers

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

**4**

votes

**2**answers

2k views

### Referencing multiple fields and items from a single cell - GETPIVOTDATA

I've already asked this over on StackOverflow, but realise it may be more appropriate here.
Is it possible to reference multiple fields and items from a single cell into a GETPIVOTDATA formula?
I ...

**4**

votes

**11**answers

22k views

### How to stop Excel Treating US dates as UK dates?

I'm in the UK, I've got a problem where I've got a list of dates supplied in US format. Excel seems to treat the ones that are valid in both formats as UK dates, (e.g. 03/01/2012 becomes 3rd of ...

**4**

votes

**9**answers

32k views

### Copy Excel worksheet and maintain relative cell reference in formulas

Another copy problem in Excel:
How can I copy a Worksheet from WorkbookA.xlsx into WorkbookB.xlsx without the copied Worksheet still referencing WorkbookA.xlsx e.g. the formula =B!23 becomes ...

**4**

votes

**1**answer

4k views

### Using INDIRECT and AND/IF for conditional formatting

I am having some issues with using INDIRECT to apply conditional formatting.
I have column G which carries a percentage. I want to conditionally format column H based on the percentage being greater ...

**3**

votes

**5**answers

2k views

### Is this Excel behaviour with a large hex number expected?

Sorry for the vague question title, but I'm not sure how best to couch this.
When I paste the following number into an Excel cell:
5000097208326954
it shows up as
5E+15
That's fine. I get that. ...

**3**

votes

**2**answers

10k views

### Split Excel table over multiple PowerPoint slides

I have a table in Excel which is too long for one PowerPoint slide. So I would like to split it over 2 or 3 slides, but have the header on each of the slides. What is the easiest way to accomplish ...

**2**

votes

**2**answers

13k views

### How to merge two Excel columns into one?

Let's say we have two columns/ranges A1:A10 (group A) and B5:B50 (group B) and we need to merge the two into one column in C1:C60 (60 in C = 10 from A + 50 from B)
Data in group A & group B can ...

**2**

votes

**1**answer

603 views

### Excel validation - can it be used to make sure duplicates are not entered?

I have an excel spreadsheet - is it possible to have validation so that a column cannot contain a value that already exists in the column?

**1**

vote

**2**answers

57 views

### Excel: how to permanently apply conditional formatting to the whole spreadsheet?

I have the following annoying problem with Excel 2010. I use Conditional formatting to apply bold formatting to each line of the spreadsheet that meets a condition expressed by a formula. In the ...

**1**

vote

**1**answer

3k views

### How to make a Pie radar chart

I want to create a circular chart in Excel with 8 sectors like in this example:
Instead of representing the percentage like in a pie chart, I want the actual value like in a radar chart.
Is it ...

**1**

vote

**1**answer

1k views

### How to force Excel to treat dates in CSV as text in a macro

I have a macro that lets the user select a CSV file and then manipulates it. However, it treats cells that contain things like 1/2/12 as a date. I need to keep these strings as text, not dates.
I ...

**14**

votes

**6**answers

87k views

### In Excel, how do I check if a cell is in a list of values (a range of cells)

I've got a range (A3:A10) that contains names, and I'd like to check if the contents of another cell (D1) matches one of the names in my list.
I've named the range A3:A10 'some_names', and I'd like ...

**6**

votes

**2**answers

3k views

### How do I keep Conditional Formatting formulas and ranges from automatically changing?

I've found that Conditional Formatting formulas and ranges will automatically adjust when you copy, delete, or move data around in a spreadsheet. While this is a nice idea, it tends to break things ...

**6**

votes

**6**answers

55k views

### Why does Excel not auto-update my equations anymore?

For some reason all of a sudden when I update cells in Excel, my equations are not being updated. For example, I have a simple equation =SUM(C4:C16). If I change any of the cells in C4-C16, the ...

**6**

votes

**1**answer

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?

**5**

votes

**3**answers

1k views

### Can I reactivate the cursor keys for modifying a cell reference in a formula?

When I enter an Excel formula by hand avoiding the mouse, I can conveniently reference cells by using the arrow keys (->,<- etc.). For example, I can enter the formula =A2&B2 in cell C2 by ...

**5**

votes

**4**answers

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

**5**

votes

**4**answers

22k views

### How to calculate hours and minutes between two time periods?

I’m working on a very simple time sheet for my work in Excel 2007, but I have run into trouble about calculate the hours and minutes between two time periods.
I have a field: timestart which could be ...

**4**

votes

**1**answer

345 views

### Space or tabs as separator in CSV

I usually put "sep=," or "sep=;" as first line on my csv files so i can easily open them with excel. But is there a way to use this with space and tab separated files?

**4**

votes

**0**answers

54 views

### Excel 2007 doesn't preserve asian characters when converting to csv [duplicate]

I am trying to save a spreadsheet as a CSV file. The file consists of korean characters, and the resulting csv has those characters literally converted to question marks (0x3F).
I am running on an ...

**4**

votes

**1**answer

323 views

### In which order does Excel process its formulae?

I've got a fairly large spreadsheet with major calculations going on, and it's starting to slow down every time a value that's part of a calculated field is modified. I'm in the process of optimizing ...

**4**

votes

**3**answers

16k views

### Only show a formula result if it meets given criteria

I like to keep my Excel spreadsheets fairly clean. So, often times I'll end up writing statements like this:
IF([formula x]=[value],"",[formula x])
What that essentially says is that if the ...

**4**

votes

**2**answers

4k views

### How to make two formulas reference the same cells in Excel?

I have one row that uses =MEAN(A1:A15), and another one that uses =SUM(A1:A15). But many times I have to alter this range to, let's say, A1:A13;A15 in both cells. I would like to alter the range in ...

**4**

votes

**1**answer

5k views

### What's the easiest way to save Task Manager's Processes tab as a CSV file?

I want to get the information in the Task Manager's Processes tab into Excel.
I want to do this because I am trying to make an image to deploy to a few workstation and need to see what I should ...

**4**

votes

**1**answer

534 views

### How can I fix a date that changes by 4 years and 1 day when pasted between Excel workbooks

In Excel dates are represented internally by a floating point number where the integer part is the number of days since "some date" and the fractional part is how far into that day (hence the time). ...

**3**

votes

**3**answers

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

**3**

votes

**2**answers

83 views

### Excel cell formatting

I want to insert numbers in an Excel cell (ex. 01-00), but the formatting wants it to default to a date (ex. Jan-1). How do I delete this formating so I can enter simple numbers?

**3**

votes

**1**answer

2k views

### Coloring partial text within cell in Excel

In Excel there are several ways to dynamically color a full cell based off of its value, but is there any way to dynamically color only part of the cell based on its value?
For example, say I am ...

**3**

votes

**1**answer

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

**3**

votes

**1**answer

240 views

### How to make many column formulas ignore the same lines in Excel?

I have multiple columns and at the end of each one I have a SUM and MEAN lines.
Now I want to ignore some lines thus making =SUM(A1:A15) into =SUM(A1:A13;A15) if I want to ignore line 14. Doing this ...

**3**

votes

**2**answers

2k views

### Can I use SQL to build an Excel data table from other Excel files?

I know SQL well enough to perform basic queries and manipulate data split into multiple tables in a database. I know Excel 2007 a bit better and use it frequently to manage my mechanical engineering ...

**3**

votes

**3**answers

3k views

### Multiple versions of Excel

I'm running Windows 7 and Microsoft Office 2003, 2007, 2010 together. I would like to open all .xls files per default with Excel 2003 instead of Excel 2010. How do I do this on Windows 7?

**3**

votes

**7**answers

33k 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?

**3**

votes

**1**answer

13k views

### In excel, cannot figure out why background color for a cell will not change?

Ok, so I inherited an excel workbook full of excel forms to modify. I am not the original author of the form. I cannot for the life of me figure out why it wont let me change the background color of ...

**2**

votes

**1**answer

125 views

### Any way to have Excel 2010 treat a quoted value “08540” as text not number when opening a CSV

I have a CSV with data like this:
"08540",1,"PRINCETON","NJ"
I know that it is possible to specify "Text" when using the Import Wizard and defining columns. But is there a way to tell Excel to ...

**2**

votes

**1**answer

473 views

### Can I provide RGB hex strings in an Excel worksheet for pie chart colors without using code?

Like user1813558 in his question "Change the pie chart colors" I would like to choose the colors that Excel uses for a pie chart (indeed for any chart) as I need them to be consistent with other data ...

**2**

votes

**3**answers

21k views

### XYZ 3D-chart in Excel

I have a table of data as follows:
X Y Z
N1 2 5 6
N2 2 7 8
N3 0 1 2
N4 0 4 5
N5 9 10 10
N6 4 9 9
N7 0 2 4
N8 0 7 8
N9 0 7 8
Now I would like to ...

**2**

votes

**1**answer

5k views

### How can I VLOOKUP in multiple Excel documents?

I am trying to VLOOKUP reference data with around 400 seperate Excel files.
Is it possible to do this in a quick way rather than doing it manually?

**2**

votes

**3**answers

12k views

### How can I sum values within a specific time and date range?

I'm trying to work out which formula to use for summing values within a period of time and date. I have the following data:
A B C
Jan 1, 2011 8:00 AM 10
Jan 1, ...

**2**

votes

**1**answer

11k views

### Importing CSV files into Excel using a macro

I want to develop a macro where in my Excel worksheet I type a date in a specific cell, and the macro will go into a folder containing text files. A database you could say. I want it to find the ...

**2**

votes

**3**answers

1k views

### Simple excel math wrong

Firstly I do not want to round my numbers, I am using base 10 figures so excel should have no bother doing simple add and subtract.
A| 665.110000000000000000000000000000
B| ...

**2**

votes

**4**answers

10k views

### Using two conditions with a SUMIF function in Excel 2003

For a given row on Sheet1, I use the SUMIF function to see if the value from the first cell of that row is on a list of historical values in Sheet2.
That will basically summarize all the values on my ...

**2**

votes

**3**answers

2k views

### Average of cells that match criteria and removing duplicates in Excel

I have an Excel spreadsheet where I want to remove the duplicate names and take an average for each person's score. I have a list of the following type:
Al 1
Bob 1
Bob 2
Bob 1
Joe 4
...