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)

1
vote
1answer
10k views

Reference another Excel sheet using a cells value

My fx attempt: =IF(MATCH(B1,Sheet1!$AY$2:$AY$50000,0) > 0,Sheet1! <problem is here> ,"") so i need to go to the column number of the value of the initial sheets <B1> cell to get my ...
2
votes
3answers
8k views

Vertical Histogram in Excel with Conditional Formatting

I'm able to do data bar conditional formatting in Excel 2010, but I can't figure out how to switch the bar axis to make it vertical. Is there any way around this? Can I simulate a vertical histogram ...
6
votes
3answers
3k 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 ...
1
vote
2answers
1k views

MS Excel and Access - which is better for reports?

Where I work, staff have just started to use a basic table in excel (1 October) to record sales which has about 10 columns (name, client, renewed, discount, paid etc). I record the data (total sold ...
0
votes
1answer
2k views

Adding a “Printed by”-watermark in Excel

Google couldn't help me, but I know the participants in Super User sometimes have more knowledge then what is publicly available, so to my question: Is there any easy way to add a watermark to an ...
0
votes
1answer
75 views

Hyperlink to other worksheet using hyperlink function in excel 2003

I want to hyperlink a worksheet from a worksheet containing a cell A1 having name of the sheet to be hyperlinked & friendly name. For example I have a worksheet summary with Cell A1 having its ...
2
votes
2answers
12k views

check two conditions in two different columns in excel and count the matches

I've trying to create a Error Log to help me analyse my mistakes. So for simplicity, lets assume I have two columns "Type of Question" - with values SC,RC,CR and another column that indicates whether ...
0
votes
1answer
7k views

“Cannot group that selection” error when creating pivot table

I have some data from my friend. The first column is time, e.g. 01:02:59 The second is a number, e.g. 5 I want to create a pivot table and then group the time to be sorted by hours (between 01:00:...
2
votes
3answers
5k views

Are you able to use other languages instead of VB for Excel macros?

Are you able to use other languages instead of VB for Excel macros?
2
votes
2answers
9k views

Collapsing rows of duplicate dates in a column leaving one row with a subtotal?

I have several thousand rows of date, time, and values in columns. Each row is contains a date, time for that date, and a value for that time period (hour) So each 24 rows has the same date with ...
0
votes
1answer
69 views

Excel 2007 Ability to have other access the sheet with same format

I created an excel spread sheet that fits exactly on my screen and saved it in a shared drive so my team mates can access it. It has some macros and other sheets on the right side of the main ...
0
votes
2answers
8k views

Setting background colour of cells based on their string length

Suppose I have this column of data Canada Chile China ... How do I set the background colour of each cell if the length of the string in it is greater than say 5? ('Canada' cell would be ...
0
votes
1answer
141 views

Percentage to number in Excel

In Excel, I have a large column with percentage values, something like this : N% N% N% .. .. I don't want them to appear as percentages. I want to convert each percentage to the corresponding ...
0
votes
3answers
3k views

EXCEL: IF value for multiple cells

I have a 3 columns, some of which have countries in them like so I J K -------------- ----------------- ---------------- Denmark Finland ...
0
votes
2answers
964 views

Find and replace slashes with new linfeed in Excel 2010

Can anyone tell me how to replace a slash (/) with a linefeed in Excel 2010? I have a very long worksheet with words, origins, and definitions. In the origins column, some rows show multiple places ...
4
votes
2answers
5k 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 ...
1
vote
2answers
578 views

How to stop Excel 97 from prompting that file was created with a later version

I have an Excel spreadsheet that was initially created with Excel 2007. However, Excel 97 is used regularly to edit this document and the annoyance is that Excel always prompts before saving: The ...
1
vote
1answer
336 views

what would be the custom format code to display percents as if it were multiple by 1000

or how can this be solved by having the input and output on the same row. custom format code solved this for numbers -- Modify value of a cell using the old value as part of the new one? quotes: ".....
0
votes
1answer
329 views

Adding value of one cell with previous value of same cell if value of another cell matches in Excel

Here is my data in sheet1 of excel: A B A1 20 A2 2 A1 2 A3 3 Consider the above in an excel sheet. Column A contains duplicate keys and ...
1
vote
2answers
1k views

Delete all rows or columns if meeting all multiple conditions

What's the VB code or macro for performing this -- the previous question from months back "The 3 conditions are 'below a certain percentage/number.' It has to meet all conditions." didnt seem ...
0
votes
2answers
2k views

Microsoft Excel maintain formatting when importing

I am using a CSV to import data. The column of data has different and sometimes multiple numbers in one field. For example... 2924,2925, 2926 in one field 2927,2928, 2929 in another field. I ...
3
votes
1answer
6k views

What does “calcs” formula do in Microsoft Excel?

I encountered an XLS file with this formula in some of its cells. I searched the web for information on the formula, but I couldn't find anything. Could you help? The formula in question is: =calcs!$...
0
votes
1answer
280 views

What is the best way to graph this in excel

I have a table in excel with three columns: Name Start Date End Date I want to create a chart with count on the left hand side and date on the bottom to show the count of open item (defines ...
0
votes
4answers
2k views

Importing long numerical identifiers into Excel

I have some data in a database that uses ids that have the form of 16 digit numbers. In some situations i need to export the data in such a way that it can be manipulated in excel. So i export the ...
34
votes
3answers
227k views

How to split a string based on “:” in MS-Excel?

My excel column is filled with words like this: 1.) ABC:DCF 2.) DCF:FED I want to split each word based on " : " and put the result in adjacent columns such that "ABC:DCF" in cell "A:1" becomes "...
2
votes
1answer
4k views

How to group excel data by date and then pick a max reported value for each day?

I have a large amount of data for over a 20 year period that I need to sort and group. I have two columns right now, one with a date of the day the data was collected and the second with the collected ...
1
vote
1answer
406 views

How to do conditional formatting based on several criteria? [duplicate]

Possible Duplicate: Formatting a column based on another columns cell TEXT (not value) in Excel How can I implement the following as an Excel formula? If column j="renew" or "-" AND the date ...
2
votes
1answer
21k views

Copy only remaining rows after filter to new Excel Workbook [duplicate]

Possible Duplicate: Excel 2010 conditional formatting: selectively highlighting duplicates I have an Excel file with an external data connection set up. It pulls data in directly from a ...
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
662 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
262 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
735 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 ...