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)

1
vote
2answers
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
3answers
53k views
11
votes
5answers
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
2answers
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
3answers
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
2answers
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
2answers
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
2answers
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
11answers
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
9answers
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
1answer
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
5answers
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
2answers
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
6answers
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
2answers
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
6answers
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
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?
5
votes
3answers
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
4answers
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
4answers
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
1answer
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
0answers
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
1answer
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
3answers
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
2answers
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
1answer
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
1answer
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
3answers
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
3answers
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
7answers
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
1answer
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
1answer
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
1answer
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
3answers
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
1answer
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
3answers
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
1answer
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
3answers
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
4answers
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
3answers
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 ...