A spreadsheet application by Microsoft. Use this tag along with [vba] if your question involves programming Excel in VBA, and with [worksheet-function] if it involves an Excel formula or worksheet function.

learn more… | top users | synonyms (2)

0
votes
2answers
1k views

Make comma separated list from column with blanks, fed by checkboxes

I want to make a spreadsheet where user can check boxes on one worksheet, and have those values then be brought over, comma separated, into one cell, on another worksheet. The values of the checkboxes ...
1
vote
1answer
658 views

Transpose unequal excel columns to rows [closed]

I have received an Excel table with different number of items per category: Category A Category B Category C 22 11 1 34 15 6 55 4 18 ...
1
vote
0answers
98 views

How to search unique dynamic data in a sheet and then copy relevent row in diffrent sheet?

I am getting data from internet (Data>From Web) In sheet1. Then I disperse that data in to three sheets based on three unique text. Like a,b and c. Rows are copied to sheet a,b and c sheets depending ...
2
votes
2answers
4k views

What function should I use in Excel for searching a (multiple) text string?

The title is a bit unclear, but I'll be explaining it now for better clarity. I have this: When I type in the Input field, I'd like Excel to show me the result in the Output field. For example, if ...
2
votes
1answer
144 views

How can I remove 'blank' or ' ' data in my excel spreadsheet?

I have a column with some cells have 'digits' data, and other have blank or ' ' data. How can I remove all the cell with blank or ' ' data so that when I plot a graph, it does not treat it as '0'? ...
3
votes
1answer
39k views

Formatting a column based on another columns cell TEXT (not value) in Excel [duplicate]

Possible Duplicate: Excel 2010 conditional formatting: selectively highlighting duplicates I have dates that are running off a formula in that column based on information it's collecting from ...
2
votes
2answers
128 views

MS Excel: How to work with a column chosen by value in header (row1)

I am importing a large dataset from a website and I want to build some summary stats on a new sheet. The column order is not fixed, so I want to be able to (for example) count all the cells with the ...
2
votes
1answer
914 views

How can I write an excel formula to do row based calculations; where certain conditions need to be met?

I am given: An excel sheet contains around 200 tasks (described in rows 2-201 in Column A). Each task can be elegible for a max of two projects (There are 4 projects in total, called "P1-P4" - drop ...
1
vote
1answer
261 views

Excel cut-n-paste behaviour

I am using spreadsheets to quickly sort the garbled tables. For example, I have an address sheet with columns for region, city (republic, zipcode, address, etc.). Some cities are erroneously in the ...
0
votes
1answer
424 views

Change the temp directory for a user with Microsoft Word 2000

i have a project with a very strange architecture : several Microsoft Word are creating documents based on copy/paste of Excel graphs. Each Word is running with its specific account at the same time. ...
0
votes
0answers
208 views

work with huge 15000 lines excel files containing formulas

We work on a huge excel file. it contains a sheet setting 22000 lines We use Excel 2011 on Mac and Windows on Intel i7 with 8Gb Ram. We have disabled the automatic computation of the file the file ...
1
vote
2answers
731 views

How to format date dynamically in Excel 2010

Could anyone help me to provide the format as below. You can use Macro, if you want to format, else if any other easy step, tell me. I searched other threads and all refer to specific cells, but I ...
4
votes
2answers
25k views

Excel Formula: What is the equivalent of =MATCH(), but for multiple columns?

Problem This formula will return the row number where the value "Bob" appears. Say the value happens to be in $A$10, the formula would return: 10 =MATCH("Bob",A:A,0) I would like a formula to do a ...
23
votes
2answers
2k views

Why are Excel weekdays wrong for 1900?

This question is based on the observations of AdamV in his answer on How do I get the day name into a cell in Excel? When A1 has the value 2009-08-01, then: =WEEKDAY(A1) will obtain 7 =TEXT(7, "...
0
votes
2answers
445 views

Has this gradient fill disappeared in Excel 2007 & 2010?

I am drawing column chart in Excel 2007 & 2010: The slides of my colleagues are all made in Excel 2003, they have a gradient fill as follows: I really would like to fill the same style in my ...
3
votes
3answers
5k views

how to extract data from an image graph

I'm looking to get that from a graph. but problem is that graph is in image form. Now I want to extract data in excel from that graph. how can i do it? I'm using office 2003.
2
votes
3answers
2k views

Excel: count number of unique/distinct row in range with condition

I have an excel sheet with: in Col A: week numbers in Col B: dates (timesheet entries) I need to know the number of days worked for each week. So I need the number of unique date entries per week ...
1
vote
0answers
225 views

Updating one sheet and having it carry over to another sheet

I have a workbook that contains 2 sheets. One is a master forms list and the other is a log for document control changes. What I would like to have happen is, when I make a new entry into the ...
0
votes
2answers
4k views

Complex rota hours calculation in excel

OK so there are a number of different shifts in operation here and I want a total hours formula. problem is the rota is written using a number of different notations for different shifts some are 2-...
0
votes
1answer
191 views

Excel Line chart multiple related series, colouring

I'm using MS Excel 2010. I have a worksheet that looks like this ---/ 1001 / 1002 / 1003 / 1101 / 1102 / 1201 / 1202 / 1203 / 1204 R2 / ... R3 / ... ... I would like to create a line chart with ...
3
votes
2answers
1k views

Is there any function formula in excel I can use to get a weighted geometric mean?

The normal GEOMEAN function is assuming that all the various numbers are equally weighted, but in this case it's not, so how can I calculate a weighted geometric mean?
0
votes
1answer
2k views

Can't use IFERROR

Am using a bloomberg Add-in, and I get an error that shows #N/A N/A and I've also got some #NUM! errors. IFERROR doesn't work because #N/A N/A isn't a standard excel error. Is there any excel ...
5
votes
3answers
8k views

How to use vlookup to return the last value in a table?

For example, my table which I will call Table1 (I have defined this as a name) is added to as new data comes in. How can I use vlookup to retrieve the 2nd column value of the final (last) data point ...
-1
votes
2answers
3k views

What excel formula can I use to select the last non-error value in a range (that is vertical)?

Ok, for example if my range is B1:B5000. This is a time series. At certain point in time, the values all become errors that are #NUM! So is there any formula I can use to return the last value that is ...
0
votes
2answers
10k views

How to divide the sum of cells by 7 and then transpose them in Excel

I need to take a vertical row of numbers in excel and add them all and divide by seven. I then need to transpose the result into 7 equal rows (one for each day of the week.) I have tried the ...
3
votes
2answers
13k views

How to tell excel to ignore #NUM! errors in plotting a line graph?

I am plotting a line graph which is a time series. After a certain period of time, the time series shows #NUM! How can I get excel to ignore the #NUM! and plot nothing. Currently, it plots all values ...
0
votes
1answer
2k views

I have a line graph and the line is too thick, which option can I choose to make it thinner?

I want to make the line thinner for improved viewing. How can I do this?
2
votes
1answer
4k views

Spreadsheet VLOOKUP: Search in second column and return first

In Excel or OpenOffice calc I have | Foo | Bar | +-----+-----+ | A | 1 | | B | 2 | Is it possible to use VLOOKUP to search a value in the Bar column and return the value in the Foo column?
1
vote
2answers
140 views

Can I run a macro automatically on an edit, instead of having to reopen an application?

I have two spreadsheets, I want the second spreadsheet to be updated once a person changes the first speradsheet. I can do this with a simple if statement but it wont update until the person working ...
-1
votes
2answers
589 views

I have 5 time series in a graph, I want to hide the labels of 3 of them, how can I do this?

This means that while the labels of 2 are shown, the labels of 3 are not. How can I 'hide' labels? UPDATE: To clarify - how do I make the legend automatically disappear when it's a blank series, ...
0
votes
4answers
7k views

Want to use HLOOKUP to drag down the whole column, how can I do this?

See the following screenshot: This is only an example, my real worksheet is more complicated than this and has hundreds of rows. My current formula is =HLOOKUP(A7,A1:D4,2,0), but this formula ...
-1
votes
1answer
88 views

How to have 2 time series data in an excel chart, with different format?

Ie. For example I want one time series to be dots, while the other to be a joined line. How can I do this?
2
votes
1answer
3k views

Using Excel Linest function with blank cells

I need LINEST function to be applied with blank cells to get the slope parameter as well as the associated standard error. I can get the slope parameter with a normal function that may resolve the ...
2
votes
2answers
58k views

How to change row color based on a single cell's value? [duplicate]

Possible Duplicate: Conditional formatting of TRUE / FALSE values in an Excel 2010 range I have a spreadsheet where I have cell within a row that will contain specific text via data validation....
0
votes
1answer
838 views

What is the quickest way to replace value in cells via VBA

Certain cells in my workbook contains value 'A'. I want to replace the value 'A' with 'B'. They scatter in several sheets. What is the most efficient way to implement the replacement in VBA?
0
votes
0answers
84 views

Excel 2010, changing on its own

Excel 2010 has began changing my chart tab names from the ones that I set, back to "chart 1" "chart 2", etc. I went through and changed them all to what I had set again, and the next day they were ...
4
votes
1answer
532 views

Grouping data columns by shared values

I don't know how to properly describe what I need to do, so I will give an example. A colleague has a data set in Excel like so: Col A Col B Col C aaaaa aaaaa bbbbb bbbbb ccccc ...
1
vote
2answers
4k views

Create table, by conditionally copying rows from another table - possible in MS-Excel?

Every week, a new Excel file is generated by a CRM system, which has the date-stamp in it's file-name, but placed in a folder at a fixed location (a sharepoint URL). This file is quite large, and ...
0
votes
1answer
131 views

Equivalent of Application.UserLibraryPath in Excel for Mac 2004?

This page on "Creating An XLA Add-In For Excel" indicates that the folder referenced by the Application.UserLibraryPath property is the right place to save a user-defined add-in. However, in Excel ...
0
votes
1answer
1k views

Replacing a certain string after a certain character in Excel

I'm organizing my Mame files in a way that all the games marked with mature (meaning adult content) are filtered out. I have a text file in Excel (one game per line) and I'd like to accomplish the ...
0
votes
2answers
727 views

Excel 2010 - want to multiply numbers without typing excessively

I have a column B, so my data is from B1 to say B500. I want C1 to be B1 C2 to be B1*B2. C3 to be B1*B2*B3 C4 to be B1*...*B4 ... C500 to be B1*...*B500 Is there an easier way to do this rather ...
1
vote
3answers
24k views

How to add up all values in column F that has the same value in column A?

i.e. if I'd like to sum the total score of a student from a sheet like this: Student ID | Score 123456 | 12 654789 | 14 585822 | 5 982531 | 22 982531 | 32 123456 | ...
2
votes
5answers
14k views

Find, match whole word only workaround

I just noticed, there is no option in Excel Find, to "match whole word only" (I am not talking about "match entire cell content") e.g. searching for "part" should not match "partner". MS Word has this ...
1
vote
2answers
5k views

How to have Excel data validation display different data in drop down than is actually validated

How can I provide a user with a drop-down menu in a cell that displays the contents from one column but actually writes the value from a different column to the cell and validates against the values ...
0
votes
1answer
1k views

How to insert a blank line after each group?

I have the spreadsheet below. Note that there are cities (usually taking up 3 to 5 rows). I'd like to insert a blank line after each group of cities. So for instance, I'd like to have the ...
1
vote
1answer
904 views

Excel data validation formula

I have a table with an input column and have set up two tables with the allowable input values. The correct table depends on the value in a cell at the end of each row. To determine which table is ...
1
vote
2answers
83 views

Excel - determine average using 1/2 value for flagged cells

Is there a way to calculate an average of a column of numbers using 1/2 the value for cells that are flagged by an asterisk in an adjoining column without having to insert a separate column?
3
votes
2answers
23k views

How can I lock images to a cell in excel 2010?

Ok, so I am using Microsoft Excel 2010 and have a set up currently where I have 2 views expanded and deflated using the Group or +/- function. My problem is that the UI has images on the workbook, too....
1
vote
1answer
2k views

Remove URL Parameter in Excel

I have a URL of the following form: http://www.example.com/page.html?param1=asdf&param2=asdfg&param3=asdfgh I want to extract the value of param2 or 3 from the URL as well as remove that ...
1
vote
2answers
855 views

Why does Excel try to open password-protected URL links itself instead of just giving to the browser?

I have links to secure sites inside Excel spreadsheets (currently 2010, but I'm pretty sure it's the same with Office 2007). When I click on them, instead of just launching the system browser (...