The 2010 version of Microsoft's spreadsheet application.

**1**

vote

**3**answers

41 views

### Excel Converting Time to Decimal

I have a python script that collects a bunch of data. At the end, it prints the collected data to an excel spreadsheet. One of my columns is displaying the "Time Elapsed" in a form of minutes:seconds. ...

**1**

vote

**1**answer

88 views

### Index match function to return range category in Excel 2010

I have defined categories in columns on which the values represent the ranges for each item (rows).
By using the query (column G), I want to know in which category the item ("yellow", 60) falls into. ...

**0**

votes

**2**answers

48 views

### Formula to extract the string between the Nth square brackets

Cell A1, A2, A3, etc contain strings similar to the following...
1 x Product A [SG014] 1 x Product B [SG026] 1 x Product C [SG023]
Each cell in column A could be a string of up to 20 different ...

**-1**

votes

**2**answers

73 views

### if greater than zero copy from the range [duplicate]

I have data sheet with "product" list in range B38:B161 and "to order" column in range I38:I161 which will be updated manually. So when cells in range "I" is updated with number to order, the product ...

**1**

vote

**2**answers

50 views

### How do I identify duplicate rows in an Excel cell?

I have an excel file with thousands of rows of contact info.
I need to get a list of all the rows that have duplicate first and last names.
I have been able to use conditional formatting to ...

**0**

votes

**1**answer

28 views

### Display prefix units in excel cell

I have a specific cell in my excel sheet that contains a value from 0-100 (The value can be changed by the user). Whose value is used in some formulas at different places in the sheet.
I would like ...

**-5**

votes

**1**answer

64 views

### Formula to find the upper case in the sentence as true [closed]

i have one worksheet which has a sentence in-between uppercase words, i want to know which cells has a uppercase word in the sentence. Please someone help me out with the right formula to find.
I ...

**1**

vote

**1**answer

127 views

### Excel Copy Formulas Horizontally

On Row 1, Column A I have =Sheet!1K2, on Column B I have =Sheet!1K3, Column C =Sheet!1K3 and so on.
On Row 2, Column A I Have =Sheet!1L2, on Column B I have =Sheet!1L3, Column C =Sheet!1L3 and so on.
...

**0**

votes

**2**answers

83 views

### How to find time difference between two rows of one column in excel sheet?

I have an excel sheet containing the Entry and Exit time for users from bio metric software. I want to find how much time the user had stayed i.e. sum of difference between entry and exit times, but ...

**0**

votes

**1**answer

146 views

### Excel formula to calculate the total time spent on various tasks [closed]

I need a formula to calculate the total time spent on various tasks but I want the total splitting across the relevant interval each day.
For example, column A has the start time and column B has the ...

**2**

votes

**0**answers

34 views

### Date types management mismatch (2-digits year) - on copy/paste user action

I find a strange inconsistency when copying-pasting dates on MS Excel, when they have a 2 digits year.
I work on a machine Windows 7 Enterprise 64 bit and my Excel is a 2010 version.
PROBLEM ...

**0**

votes

**1**answer

81 views

### How do I create a table with multiple header rows

How can I create a table in Excel that has two or more headers rows. I want to keep the range as a table so I can use the built in tools such as re-sizing, totaling, formatting, etc...
I saw a ...

**1**

vote

**1**answer

34 views

### Conditional Formatting on a Specific Character only

I have the following Excel table :
> N
1 Open / Closed
2 O
3 C
4 O
5 O
6 C
I want to put a Conditional Formatting, putting in GREEN ...

**0**

votes

**0**answers

14 views

### Why does my Excel 2010 file add rows to the bottom of one of my sheets?

I have an excel 2010 file that continually adds extra lines to the bottom of the one of the sheets when I cut and insert rows from the top to the bottom. I have two sheets, one works just fine and ...

**1**

vote

**0**answers

78 views

### Calculated Fields Reference Error (#NAME?) on Pivot Table Refresh

I'm helping a colleague fix a spreadsheet with a similar issue as stated in this ozgrid post.
The problem: Pivot table with 31 calculated fields. Some calculated fields are calculations of the source ...

**0**

votes

**0**answers

17 views

### Accessing locally stored external data

I have an excel file that has an external connection in it to a SQL server DB. The external data from the last refresh is stored in Excel somewhere as i can create new pivot tables from that data set ...

**3**

votes

**1**answer

893 views

### How to automatically colour cell background with value selected from custom dropdown list in MS Excel 2010

In MS Excel 2010 Professional is there a way to automatically copy the background colour of the custom dropdown values from somewhere else in a spreadsheet?
I have some Data Validation on a row of ...

**1**

vote

**1**answer

50 views

### Excel Stuck in Developer 'Design Mode'

We have a set of spreadsheets that contain scrollbars added in developer mode. Clicking these scrollbars changes a value and a diagram in the spreadsheet.
Suddenly these do not work. The mouse cursor ...

**1**

vote

**1**answer

73 views

### Using Chart BeforeDoubleClick with a chart object in worksheet

I am trying to get a macro to work on a chart in a worksheet using the Chart.BeforeDoubleClick event.
I have been able to use BeforeDoubleClick event (without the use of class modules) in a ...

**4**

votes

**4**answers

251 views

### Comparing the first part of a string in Excel

I'm trying to create an automated function that will determine the class identification for IP addresses after you input it into Column A's input section by checking the first section of the address.
...

**1**

vote

**2**answers

5k views

### Can't click on ActiveX control in Excel 2010 Standard

I have a .xlsm file that has ActiveX control button with a module macro assigned to it. Problem is I can't click the ActiveX button from my Excel 2010 Standard. I tried to repair and reinstall Excel ...

**0**

votes

**0**answers

21 views

### Oracle connection/Pivot tables

I'm using Microsoft ODBC for Oracle Connection to connect on Oracle 11g database through Pivot tables.
It works fine with old version of Excel (i.e. 2003) but when I'm saving file as 2010 .xlsx my ...

**0**

votes

**1**answer

29 views

### Adding Index to Match formula

I have the formula below working correctly, it is returning the max value in column G when the other conditions apply (valumes in B and F). However now I know what the Max number I want to return the ...

**0**

votes

**0**answers

17 views

### My image wont show up when I insert picture from file in Excel

I am trying to insert a picture into an excel spreadsheet.
I follow the normal procedures, but when it brings it into the excel sheet it either says "Image cannot be displayed" or shows up a clear ...

**0**

votes

**0**answers

23 views

### Why does Access return “Conversion Type Failure” if I'm importing text into… text?

For some reason, no matter what, I get a "Type Conversion Failure" (found in a generated log-table) after importing data from an Excel spreadsheet. It's only happening to a few columns that have ...

**0**

votes

**0**answers

12 views

### Keeping cells in the same row lined up when columns in the same row have 2 different data sources

I am working on a worksheet that references data on a different worksheet (within the same workbook). Each row in the referenced worksheet represents an order. Data from the referenced worksheet ...

**1**

vote

**1**answer

30 views

### Excel return a list of stringe where 'TRUE'

Ok so I have couple of cells listing warnings:
A B C
1 Warning1 TRUE
2 Warning2 FALSE
3 Warning3 TRUE
Now I want to return list of warnings that are set to ...

**1**

vote

**5**answers

43 views

### Using 3 conditions in Excel

I am trying to extract data using three conditions.
I have very sensitive information, so I am only able to provide some basic data below.
What I am trying to output from using 3 lists is that if a ...

**0**

votes

**0**answers

41 views

### merge multiple sheets in a work book excluding specific sheets excel 2010

How can I merge multiple sheets in a work book excluding specific sheets in excel 2010. I tried the macro below but it includes all the sheets in the workbook. Thanks.
Sub Combine()
Dim J As ...

**0**

votes

**1**answer

24 views

### Looking to total cells, but leave some blank in between

I've written a worksheet for selling work. I have one column "C" for the cost, and the column next to it "D" for a subtotal. Depending on the work to be performed, not all rows will have values to ...

**0**

votes

**2**answers

45 views

### Conditionally Formatting Across Cells

I am organizing an event and would like to do the following:
I have a General sheet with all the invitees names, addresses, etc. I also mark here if they replied and how many people will be ...

**0**

votes

**1**answer

73 views

### Chart labeling: durations in minutes and seconds

I am attempting to chart the durations of phone interactions. They are currently entered as time of day values (I did not build the workbook), even though they are actually duration values, and Excel ...

**0**

votes

**1**answer

21 views

### How to use excel 2010 functions to caculate the sum of distinct unique values?

How to use excel 2010 functions to caculate the sum of distinct unique values?
remove the duplicate values
sum of distinct unique values
but I don't need to know who are the distinct unique values, ...

**0**

votes

**0**answers

25 views

### Why is the autosum behaviour not as expected when used on an entire column?

This is some strange behavior I encountered when using the Sigma to sum a column.
If I click on the open cell under the last number in a column and then hit the Sigma button and press enter I get the ...

**0**

votes

**1**answer

29 views

### Capturing Text from Pivot Table

I'm trying to create a pivot table that has 2 parts.
In order to push parameters into the table, you would add allows users to filter across any one of several parameters in a field. For instance, ...

**0**

votes

**1**answer

18 views

### How do I assign a key based on count of available keys and indicator value?

I need some help writing a formula that does the following:
Looks to see if an indicator=1
If indicator=1, lookup the key value based on the number available for that particular key
Keep looking up ...

**0**

votes

**1**answer

24 views

### converting a cell specifcally formatted as 1h 25m (not excel time such as 1:25) and converting the cell to 85m

I would like to convert a cell with the following text/numeric string 1h 25min to a cell that displays 85min. Unfortunately, the original cell is only in the 1h 25min format and not in a true excel ...

**0**

votes

**1**answer

34 views

### Mass change the appearance of URLs in Microsoft Excel

I wish to change the appearance of the URLS in a Microsoft Excel worksheet. At present the current URL's look something like: http://www.example.com/101010101010101010101010101. I know how to change ...

**0**

votes

**2**answers

43 views

### Using COUNTIFS for a series of values at once

Working a step higher then COUNTIFS, I appose a challenge to write a formula without VBA code. The basic data is combined from 1000s of rows with:
Column A: rows with values from 1 to 3
Column B: ...

**1**

vote

**1**answer

61 views

### How to leave out certain rows in input data when creating a line chart [closed]

I created a line chart in Excel and manipulated the chart data that is attached to it; however, I have some rows of my chart data that I do not want displayed on the line chart. How can I make these ...

**0**

votes

**1**answer

57 views

### How can I make Excel conditionally highlight cells based on the contents of cells? [duplicate]

I have a few things that I would like to do on Excel:
How do you highlight a row [A-U] if only the first cell A has had text entered in it?
When you enter text in cell A cells B-U have a yellow ...

**0**

votes

**1**answer

33 views

### What is this argument of AGGREGATE function doing in my formula?

I need to understand this part ($B$1:$B$15<>"") in the following formula used to represent a column of data with blank cells removed:
...

**0**

votes

**3**answers

1k views

### Compare multiple columns for any two or more cells within the same row having the same content

I need a formula to compare multiple columns for any two or more cells within the same row having the same content. If that is true, then display "TEXT A" (can be anything such as "TRUE"). If all of ...

**2**

votes

**3**answers

195 views

### How to compute the number of occurrences of a string in the entire workbook?

I want to compute number of times a specified string appears in my entire workbook in some column. I have tried this formula =COUNTIF(sheet1:sheetn!C2:C150; "*string*") But it renders the #VALUE! ...

**5**

votes

**4**answers

518 views

### How can I record a transition from a 0 to 1 in a column in Excel?

I need to record the number of transitions from a down state (0) to up state (1) in a single column in excel and I am wondering whether there is some kind of logic like this available in Excel or ...

**-4**

votes

**1**answer

83 views

### How to copy and paste “ONLY VALUES” from one workbook to another?

Can someone teach me how to copy and paste "ONLY VALUES" from one workbook to another?
The workbook contains about 20 worksheets, and I don't want to copy and paste each sheet one after another into ...

**1**

vote

**1**answer

25 views

### Excel 2010 : How to compute the number of non blank cells in a column among the entire workbook

Let's say I want to compute the number of non blank cells in some column (A for exemple) among the entire workbook, for a fixed range of cells. How can I do this ? knowing that for just one worksheet, ...

**0**

votes

**0**answers

127 views

### Check if substring of cell in one row matches with another cell on column values in another row

I have a table with 2 columns (ID number, Description) as shown below:
ID no Description
1001 A letter to george william
1001 ...

**0**

votes

**1**answer

23 views

### MS Excel: Count the max spaces between a number frequency

I have a column of numbers (i.e. 7,2,5,2,6,7,5,3,7,8,9,2,3,5,6,7) What I am trying to do is count how many numbers are between each 7. Not just the first, second, third, etc. but each instance. So, ...

**1**

vote

**1**answer

29 views

### Microsoft Excel 2010, reference external *.csv data into table

I have multiple *.csv files with rows of data being added periodically.
I want to have all of this data be referenced in a table in an Excel 2010 worksheet. If data is updated/added to the *.csv, ...