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
1answer
386 views

How do I select a blocked chart in a Excel 2010 chart?

How can I select a blocked or protected chart in Excel 2010? When I right-click on the chart, the cursor changes to a circle with a diagonal line within it - a "blocked" sign. I'm doing a study ...
2
votes
1answer
13k 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?
0
votes
1answer
407 views

Excel Pivot table: Calculated field based on only the first row of a group

I've got the following data and pivot table: The Total column in the pivot table is the sum of the following calculated field: =start-TIME(7, 30, 0) I know that this calculation is wrong for ...
0
votes
1answer
1k views

Hashed pattern in bargraphs - MS excel

I am drawing graphs for a paper that supports only black and white graphs. I need to show more than 3 histograms and want to have different patterns on them like hash, dotted, double hashed etc ...
4
votes
1answer
1k views

Excel Pivot Chart - historical Data Filter values stay visible?

I'm using Excel with a SQL data connection to generate a Pivot Chart. The issue I'm having involves the data filters at the top of each column, allowing you to filter the visible values. Upon ...
0
votes
3answers
3k views

Highlighting duplicate column-pairs in Excel

Example, this is the data in Excel: 1 2 2 2 3 4 3 4 3 3 2 2 2 2 1 2 The rows which have consecutive duplicate column-pair values (in bold font) should be highlighted: 1 2 2 2 3 4 3 4 3 3 2 2 2 2 1 ...
2
votes
1answer
461 views

Recovering Excel file

I have auto recovery on my Excel program and I save my work several times a day. Tonight, I turned on the computer and it had shut down down for some apparent reason. When I pulled up my Excel ...
1
vote
2answers
28k views

How to identify cells in Column A that contain any of the values in Column B

First off, I'm not that advanced of an Excel user. I'm certain Excel can do what I'm about to ask, but I've yet to find a way in any of my attempts or searches. My question is very similar to Find if ...
2
votes
3answers
521 views

Excel formula to calculate power of [closed]

Given the following equation: 2^X - 2 = N I'd like to solve for X. N is known. What function could I use in Excel to do this? Thanks in advance.
3
votes
3answers
6k views

How to exempt rows from being hidden/filtered in Excel 2010?

Consider a spreadsheet that starts looking like this: I want to be able to filter for Name 1 on the left column and have it look like this: Yes, I realize that the simple answer is to filter for ...
0
votes
2answers
1k views

Counting number of adjacent conditional formatted cells by color

I've a file in which some cells have been red colored after using conditional formatting across the columns. Now, I would like to count the number of red colored cells across the rows. An example of ...
2
votes
2answers
3k views

Excel subtotal for blank cells

I have data in excel which looks like 10 15 12 3 2 5 3 I would like it to return subtotals like 10 15 12 37 3 2 5 5 3 8 Is there a way to achieve this in Excel?
0
votes
1answer
97 views

Date-based conditional formatting

I have a column with dates in column A. I.e.: 01/01/2012 01/01/2012 01/01/2012 02/01/2012 02/01/2012 03/01/2012 04/01/2012 04/01/2012 04/01/2012 Is it possible to have a conditional formatting ...
2
votes
1answer
2k views

How to split cell in a row with Excel

I am trying to create a macro to split a cell containing two or more line of characters. I would like to keep the others cells of the row. For example _____________ | | A | |Row 1| B | | ...
1
vote
1answer
83 views

Excel 2007: The empty look of a pivot table

How is it that in Excel 2007, an empty pivot table sometimes looks like this: and sometimes it looks like this: So what's the difference? How can I make it always look like the first one?
2
votes
2answers
769 views

I can't write credit card number when I write the last number becomes 0

While trying to fill in credit card numbers into Excel 2010, when I write 4645890379859736 last number becomes 0 Example:4645890379859736 is turned into 4645890379859730 Note: I can't use any - or ...
0
votes
1answer
1k views

Excel Macro To Lookup a User Entered String, and return data from the field next to it [closed]

On worksheet A, a user is prompted to enter a product number, such as BCI610. On worksheet B somewhere, that value exists. I want excel to lookup/find that value, and then return the data in the ...
2
votes
1answer
480 views

Why am I unable to use a named range beginning with “Corona” in Excel charts?

I'm running into a ridiculous problem in Excel 2010 where I cannot seem to use a named range whose name begins with "Corona" in a chart. I've created a new workbook with a simplified version of the ...
2
votes
1answer
12k views

Error when refreshing data from external source in excel. Data source name not found and no default driver specified

I have an excel workbook which has an external data source to create a pivot table. This file was sent to me. I can open it up correctly, but when I try to refresh the data I get the following error: ...
3
votes
1answer
35k views

How to create dynamic Scatter Plot/Matrix with labels and categories on both axis in Excel 2010?

Let us consider a following data set: Name | Age | Hair Color ----------------------------- John | Young | Brown Sophie | Old | Blond Adam | Mature| Blond Mark | Teen | Dark Jeremy | ...
1
vote
1answer
486 views

Excel Getting Floating point number

I have a excel sheet that has some values something like below. When I want to get the values by concatenating with a string, points are being erased. The cells format are Number by the way. If I ...
0
votes
2answers
1k views

How to lookup a value in a table with multiple criteria

I have a data sheet with multiple values in multiple columns. I have a qty and a current price which when multiplied out gives me the current revenue (CurRev). I want to use this lookup table to ...
4
votes
4answers
909 views

Microsoft Office 2003 document (Excel and Word), intermitently, takes 30 seconds to load

I am trying to figure out why a simple .XLS EXCEL workbook is taking, randomly, 30 seconds to open. Before answering: Please, bear mind the following: Main symptoms Using Windows Explorer, here is ...
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
958 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
328 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
401 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 ...