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)

4
votes
2answers
2k views

Excel Trailing Spaces

I have a column in an excel document that each entry has a trailing space. When I copy/paste the value into another form I have to manually remove the trailing space. Is there an easy way to batch ...
3
votes
2answers
101 views

Reverse-tracing a list of numbered items

Is there a programmatic, hopefully non-macro way to reverse-trace a set of numbered items? Example: The first two columns are ones which data is entered in. You can see that item A has a ...
2
votes
1answer
2k views

Aligning two tables in Excel on unique keys

I have two tables with the same columns. Each table has a "name" column (unique key basically) and two amount columns. Each name may appear in either or both table. What I want to do is align them ...
0
votes
1answer
493 views

Pulling only one piece of data with an Excel web query?

I have a bunch of data in the format: somedata,otherdata,dataparameter in an Excel spreadsheet. Dataparameter combines with a URL to get me to a page that has a piece of data I want to suck into ...
1
vote
1answer
263 views

Pie Chart in Excel

I tried to google this but no luck. It is easy to add a pie chart when you have prcentace data but what if you just have a list of partially repeated data? For simplicity imagine there is a table in ...
1
vote
1answer
805 views

Hiding and locking a column in Excel

We have performance appraisals in Excel format for all of our employees. Employees use one column to rate themselves and their director uses another column to rate them. The idea is to have the HR ...
13
votes
1answer
19k 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 ...
2
votes
2answers
2k views

How do I copy a worksheet without creating local copies of workbook-scoped named formulas?

I have a workbook with some workbook-scoped named formulas. When I copy a worksheet that has some (different) worksheet-scoped names, the copy ends up also having worksheet-scoped names that are ...
4
votes
1answer
14k views

How to change font color in Excel based on cell value?

How do you change the font color of a column of cells based on their entered value? I've got a column with the heading at row 1 and the values starting at row 2 going on down. I want to change font ...
1
vote
3answers
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 ...
0
votes
2answers
4k views

How to have sequential numbers keep writing themselves?

At work, we use Microsoft Office 2003, and we have to write many numbers that are sequential, e.g. 2590, 2591, 2592, etc. These numbers are either in Excel or in a Word table. How can I ...
1
vote
1answer
2k views

Getting VBA error when opening Excel 2007 on new computer

I have a user with a new computer that whenever Excel is opened gives the error message Opening the VBA project in this file requires a component that is not currently installed The message is ...
2
votes
1answer
823 views

Embedded Excel table in Word doc is getting blacked out when printing to PDF

I am working with a client that has a Word doc that has an Excel table in it. When the user tries to print to PDF using PDFcreator or Acrobat the table gets blacked out in the PDF. Does anyone know ...
2
votes
1answer
2k views

Hyperlink in Excel

I am using MS Excel 2007 (previously familiar with Excel 2003). I create a simple hyperlink in cell A1 (in sheet Sheet1) so when I click cell A1, the cursor moves to cell B10. It's very easy, no ...
0
votes
2answers
437 views

Excel: counting unique phrases, separated by slashes

I have some Excel data that looks a bit like this: John / Tim Upheld Sam Upheld Tim / Dave Upheld Sam / John Not Upheld Tim Not Upheld Dave / Tim Upheld ...
7
votes
1answer
12k 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 ...
8
votes
6answers
52k views

What is the difference between function and formula in Excel?

What is the difference between those two when speaking of Microsoft Excel?
1
vote
1answer
2k views

Parse Excel Column, First Name (<optional>)Middle, Last name into 3 columns?

How do you parse Excel Column, First Name ()Middle, Last name into 3 columns? Ivan P. Freely Jordan Smith How would you parse the above Excel column list of full names into 3 distinct columns with ...
21
votes
16answers
66k views

How to make cells perfect squares in Excel?

In Microsoft Excel, I want to make the height of first three rows and first four columns (12 cells in the top left corner) such that these cells are squares. How can this be done? Surprisingly, ...
0
votes
1answer
102 views

How do I ensure only integers and with no duplicated values can appear in a column in Excel 2007?

How do I ensure only integers with no duplicated values can appear in a column in Excel 2007?
0
votes
4answers
964 views

Difference between CSV in Excel and CSV in OpenOffice.org

There are different implementations of Comma Separated Values; two of them are Microsoft Excel and OpenOffice. Is there any difference between these CSV implementations? For example both can open ...
1
vote
4answers
4k views

How to input only unique values in a column in Excel 2007

I'd like to know the easiest way to make sure that only unique strings are entered in a particular column in MS Excel 2007? I know I have to put a formula in the Data > Data Validation, but I don't ...
3
votes
2answers
2k views

Microsoft Excel - Copy Pasting while maintaining reference to a single cell

Let's say that I have a value in cell A5 that is being used to calculate a value in A3. Example: A3 = (A1 + A2) * A5 When I copy paste cells A1:A3 and paste onto B1:B3, I'd like the resulting ...
1
vote
1answer
2k views

Excel or open office calc how to remove last white space? [closed]

Excel or open office calc how to remove last white space?
0
votes
1answer
813 views

How can I make this formula work =MAX(LEN(A2:A9)) in Excel 2007?

How would one make this formula work =MAX(LEN(A2:A9)) in Excel 2007? I want the value of the longest field in A2:A9 I found this... http://www.mrexcel.com/td0021.html but it's old now.
0
votes
1answer
1k views

Small but full featured xls (Excel) reader for Mac OS X?

Is there a program that allows me to not only read the contents of an Excel spreadsheet on a Mac without being a large download? I presume OpenOffice does it, but I don't have the bandwidth to ...
1
vote
3answers
7k views

Does Excel have a row filtering function (more general version of SUMIF/COUNTIF)

SUMIF and COUNTIF functions are handy for simple SUM and COUNT operations on only some of the values in a range, but is there a more general version that would allow me to plug in any other function I ...
3
votes
1answer
15k views

Embed Excel sheet in Word 2010

I wanted to know if there is any way of doing the following in Word 2010. I want my sheet the whole Excel sheet to be able to paste it into Word document. Whenever I modify the data in the Excel ...
0
votes
3answers
997 views

How to open text files in excel

I have a large text file and I want to open it so that it appears as columns in excel 2003. When i open it though the columns are lost and its all one horizontal line? Help
0
votes
3answers
520 views

how i can put last column contain value in 1 cell in excel

how i can put last column contain value in 1 cell in excel? i have figures from F4 to F83 ( each week the last figures will be in different cell between 4 and 83 - for exp this week it's was 82 rows ...
3
votes
5answers
19k views

Excel - Delete specific word in each row?

Is there a way to have Excel delete a sepcific word in every row? For example, each row says Mr. and Mrs. {first name} {last name}, and I want to delete the {first name} from each one. (Or ...
2
votes
1answer
648 views

Divide pivot table data by an arbitrary column in another table

I have this data from a pivot table: Countries P1 P2 Total Country 1 10 69 Country 2 36 2 92 Country 3 21 24 100 Country 4 22 77 Country 5 13 79 Country 6 12 1 ...
7
votes
3answers
19k 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': ...
40
votes
5answers
85k views

How to expand all columns in Excel spreadsheet?

I have a spreadsheet with many columns. The columns are all a small standard width. Double-clicking the partition line between columns will expand the column width to encompass the longest string in ...
1
vote
2answers
3k views

Excel how to reference a list

Say if I have a list A1:E5 and I want have reference 2 columns of the list, say B and D. So it will looks like: A B C D E G H (reference to B1:B5 and D1:D5) 1 a b c d e b d 2 ...
0
votes
1answer
909 views

Excel display a list if value not equal to zero from another list

Say source data is like col1 col2 a1 2 b1 b2 2 c1 1 d1 d2 2 d3 I want to make another list that looks like col1 col2 a1 2 b2 2 c1 1 d2 2 How ...
1
vote
2answers
11k views

Excel bar chart - how to make axis as time scale

My data has column 1 = date (date format), column 2 = quantity (number format). The data looks like: date qty 1/3/2010 5 2/3/2010 2 2/4/2010 3 2/9/2010 1 4/13/2010 5 5/31/2010 5 ...
0
votes
1answer
4k views

Excel 2007 - Can you give groups names?

I want to group a set of rows then hide them. Once hidden can you make it display some sort of text message. Like right now it just hides the rows. It is not very obvious that the rows are hidden. ...
1
vote
1answer
130 views

How to set the focus on a comment in Excel 2007?

In Excel 2007, is there a way to "focus" on a comment? In an IDE, you would do this by pressing F2 for those familiar. One example of the issue is with Freeze Panes. Say for example: I have ...
0
votes
1answer
182 views

Excel formula - sometimes array formula, sometimes not

I want to write some data to an Excel file, and I want the first page to show a summary of the data that are spread all over the sheets. The individual data items on a sheet can be included or ...
2
votes
3answers
3k views

Excel 2007 using web services (specifically Google Finance)

Can Excel 2007 access web services, specifically Google Finance or Yahoo Finance? I'd like to be able to have my workbook automatically update share/fund prices from either of these sites. I know ...
1
vote
2answers
44 views

MS Excel 2007 ref

How to transfer data Row to Columns?
1
vote
1answer
3k views

Excel: Filter on column merged downwards

I have an excel spreadsheet with 3 columns. The first column is merged downwards to be the length of the other cells and contains a document name. The other 2 columns contain an id and some text ...
3
votes
4answers
1k views

Export to csv options using Numbers

I'm using Numbers on my Mac at the moment and exporting to a CSV file. Is there a way to enclose every value with ""? For example I'm getting the csv file like this: 1,2,3,4,5,6,7,8,9 When I ...
2
votes
2answers
857 views

Excel Macros 2007: How can I Bold the Row if a number is in Column A?

My long list of data has numbers in Column A, but in random rows. How can a macro be written to find a number in Column A and then bold the corresponding data in that row? I know if I record my ...
0
votes
3answers
356 views

Formula in Excel

Below is the data: A B C xxx 2 5 xxx 3 4 bbb 2 3 bbb 2.5 2 nnn 1.5 2 nnn 2 1 I want to ...
2
votes
2answers
3k views

Excel: lock sheet, but keep tables expandable

My goal is to protect a sheet, but make users able to edit and add rows to the contained tables. When I try to protect the sheet with no cells locked, the tables become unable to add rows even though ...
1
vote
1answer
606 views

Excel 2003 to Excel 2007 performance on large files

We have a user that uses Excel 2003 for a few of their acturial formulae. At the moment they are using a ESX host VM with 2 gig of RAM and a single core. The issue is that their huge burgeoning ...
3
votes
1answer
1k views

How do I set search option defaults in MS Excel 2007?

I'm using Excel 2007. Every time I do a search I have to set the options to search within 'Workbook' and look in 'Values'. Is there a way to permanently set the default search behaviour?
4
votes
1answer
9k views

Shortest Method To Reference A Single Row/Column of a Named Range in Excel?

I have a named range (NamedRange1) and I need to reference the first row and first column of that range within a formula. I came up with the following: First row: INDIRECT("R" & ...