The 2010 version of Microsoft's spreadsheet application.

learn more… | top users | synonyms (1)

0
votes
2answers
2k views

How to identify column where value drops below 0

I'm trying to figure out a formula to identify in which column the value falls below 0. I have thousands of rows and dozens of columns, so doing this manually isn't an option So if my columns and ...
2
votes
1answer
2k views

Pivot table : absolute value of grand total in Excel

My grant total is generated by summing values of COLUMNS of ITEMS for a given SUBJECT (For each SUBJECT I have a list of ITEMS and I want to make a grand total of the values of these ITEMS). The ...
0
votes
0answers
733 views

Create a Macro that searches, copies and Exports from two workbooks and imports into 1 master workbook

I am new to creating macros. I have tried searching for existing macros to help with my current request; however I have not found a helpful one. Please let me know if you can assist. Contents ...
1
vote
2answers
33k views

Swapping two rows or columns in Microsoft Excel sheet

I'm using Excel 2010. Suppose you have this data in a spreadsheet: line: content: 1 data 1 2 data 2 3 data 3 4 data 4 5 data 5 You made a mistake and now need to ...
1
vote
3answers
20k views

What is the simplest way to interpolate and lookup in an x,y table in Excel?

I would like to do a lookup and interpolation based on x, y data for the following table. I'd like the equation to be as simple as possible to reduce the amount of possible errors. The full table is ...
4
votes
1answer
34k views

Excel generate and format horizontal error bars

I have been trying to generate and format horizontal and vertical error bars in Excel 2010, and there is no problem with the vertical ones, but I cannot figure out how to manage horizontal error bars. ...
1
vote
2answers
281 views

Excel custom format: Date/Time written out?

I'm looking to format a datetime in Excel 2010 to display as something like this: 1.234 ==> 1 day, 5 hour, 36 minutes I know how to do something similar for simpler scenarios For just days, I ...
2
votes
1answer
463 views

Filling content of cell based on adjacent cell

I have a column of data like column B below. The letter "A" denotes software, "B" a service and "C" hardware. How do I match each product with its category to make it look like the table below? A ...
1
vote
1answer
613 views

Deleting entire row using macro in excel

I have and excel spreadsheet and I need to make a button that deletes the entire row in which the button is located. Lets say I have a button on cell G7. When I press it, I want the entirety of row 7 ...
1
vote
2answers
72 views

Is it possible to move to the end of document with some hot-key?

I've found this: Ctrl+End Move to the last cell with content on a worksheet. But it moves me to 1156 line when document actually ends at 74. Is there another possible way of doing it?
8
votes
2answers
90k views

Excel: filter a column by more than two values

I have a worksheet with a column, user_ID, that contains a large number of user IDs I'd like to filter it down to about a dozen IDs, but using Filter -> Custom Filter only allows me to filter by 2 ...
0
votes
1answer
53 views

Is it possible to reference a values column in a calculated field

I've created a pivot table in excel that has two value fields. Both are SUMs of their target columns. I want to create a calculated field that divides the first value field by the second. Is that ...
0
votes
1answer
108 views

remove duplicate values in a text file

I have a tab delimnated text file showing a comparison. like this: A B C D A 1 2 3 4 B 2 1 4 3 C 3 4 1 2 D 4 3 2 1 But I need to work with the data ...
0
votes
1answer
4k views

ActiveSheet.ChartObjects(“chart”) always returns Nothing?

I'm querying for a chart in the worksheet and I can see it's there but everytime I execute the following piece of code it evaluates to 'Nothing'. Why is that? Sub DeleteGraph() Dim graph As ...
0
votes
1answer
1k views

Sequential Number in Excel within Specified Range

I have been looking for the macro since last week but with no luck till yet. Would appreicate the efforts if you please get me out of the problem that i am facing, I have created excel template (used ...
0
votes
1answer
17k views

How to show an additional single point on a line graph in Excel?

I have a data set which shows the "efficient frontier" curve and a data point showing the decision made. I wish to have the data point be plotted in relation to the frontier but as an isolated point ...
1
vote
1answer
1k views

Excel 2010 links work for me but not for other people

We have a large data file in Excel 2010. Several other files have data linked from that file. If the data file gets modified, the other files get automatically updated. This works when I do it on my ...
1
vote
2answers
5k views

Get Excel to base tick marks on 0 instead of axis ends (with fixed maximum or minimum)

In other words, I want Excel to anchor the tick marks at 0. I am trying to get an axis that goes from -35 to 35, but with the ticks on multiples of 10: 30, 20, 10, 0, -10, -20, -30 I have set set ...
0
votes
1answer
346 views

exec 2010 VBA related error: “can't find project…”

Have made a (refinery process industry-)model under Excel 2010 with many macro's (based on a Excel 2003 sheet) Had some difficulties getting it to run wih the right addins but OK now. However, other ...
0
votes
2answers
989 views

In Excel 2010, how can I create a template with a function automatically ends in the last cell with data?

I want to create a template in Excel with a function that includes a range, but the number of cells in this range will change every time the template is applied. How can I identify that I want the ...
0
votes
1answer
126 views

How to use match & index or lookup function based on a number derived from a time calculation

I have created a gantt chart to use as part of my rota system. I want to be able to use a formula (either LOOKUP or MATCH & INDEX) to look up the hours worked against another sheet and pull in the ...
2
votes
1answer
292 views

Sorting Common Values by Row in Excel

I have two sets of data from two years as below which needed to be sorted by product and row. Eg. I need A2 and its product sales on the same row for both years.I have had a look at the the solution ...
1
vote
1answer
13k views

Any trick to calculating percentages when the base is a negative number

I am trying to calculate percent change when the base number (at time 1) can be a negative and the resulting number is positive (at time 2). My problem is the percent changes from -4 to +4. A change ...
0
votes
3answers
383 views

Formatting Times (durations) in Excel

Does anyone have a good quick way of entering times, specifically durations, NOT time of day, into Excel? All of the times in this case will be short, minutes and seconds. Nothing as long as an ...
1
vote
2answers
2k views

Changing the axis labeling in a Excel 2010 scatter plot

I am trying to represent a list of data points in such a way that the graph starts at X coordinate '1' but that labeling starts at coordinate '100' and I have been unable to find out how to do it as ...
0
votes
1answer
26k views

Conditional formatting to highlight the whole row if a cell is left blank

Could anyone tell me please how to highlight a row if the first cell in that row is missing a value?
1
vote
1answer
85 views

How to select whole column (e.g. B) except single cell in the middle (e.g. B43) for formula?

I want to do a countifs formula, but I want the range to be whole column B except B43. What should I put in the formula?
0
votes
2answers
198 views

Excel specific cell conditional formatting

I am trying to format a whole column (A1:A100) based on the values on each row. For example: If A1 > 0, then cell should be red If A2 < 0, then cell cell should be green And so on ...
0
votes
2answers
2k views

Find Last Date Value in Excel 2013?

I have from B1 and onward(C1,D1,etc), I want to find the last date that is filled in. How can I do this in excel?
-2
votes
1answer
39 views

Excel 2010 if lookup in multiple lines

I am trying to do the following: one sheet | in another sheet ----------------------------------- C | R S 448 | 497 517 | 526 542 453 | 812 814 ...
2
votes
1answer
707 views

Excel 2010 Pivot table to calculate average profit/ loss

I want to calculate average profit/loss in Excel pivot table. Say the source data is in a Excel table and it looks like Date Net income 1/1 5 1/1 -3 1/1 7 1/2 -10 1/2 4 My solution ...
-1
votes
1answer
295 views

How to fill Excel 2010 Cells with a recursion idea

What I'm trying to do is have cells auto populate and un-populate dependent on the value of another cell. For instance, Cell B3 has a set value of -0.1, Cell B4 has a set value of 0. Cell D3 is the ...
0
votes
1answer
3k views

Line Graph Adding Value of Series 1 to Series 2

I'm working on a line graph that contains two lines (each representing two different sets of data) with percentage values for each time period. In the first time period, the value for Set A is 3% ...
0
votes
1answer
4k views

Excel: COUNTIF for Filtered Data

I'm trying do a "COUNTIF" on a list of 24 items (a list of times, in number of hours). For some background, the formulas : =COUNTIF(H2:H25,7) =COUNTIF(H2:H25,"<7") return to me a list ...
2
votes
2answers
13k views

Filtering DateTime column for Time Range?

I have two columns that contain date/time pairs as MM/DD/YYYY HH:MM:SS, and I need to filter both columns so that only times between 15:59:59 and 20:00:00 are displayed, the date does not matter. I ...
1
vote
1answer
2k views

How can I transpose and group data in Excel 2010?

I don't think I'm phrasing the question correctly so I'd be pleased if someone could edit it to something sensible! I'm using Excel 2010 and I have a set of data like this: Art 12 Bob 15 Bob 18 Kev ...
0
votes
1answer
35 views

MS Excel Average function (value1, quantity1, value2, quantity2,..)

Is there any function in Excel that calculates average value and takes arguments like (value1, quantity1, value2, quantity2,..)? And the function should return value of (value1*quantity1 + ...
1
vote
1answer
5k views

Microsoft Excel cumulative calculation performance

I know 2 ways to calculate cumulative values in Excel. 1st method: A B Value Cumulative total 9 =A1 8 =B1+A2 7 =B2+A3 6 =B3+A4 2nd method: A B ...
0
votes
2answers
566 views

I wanted to count unique numbers in a excel and combine it with a countifs function

I have a data sheet with these specific columns (among others) Date time (in one column) Campaign name (I work at a call center) in another Agent ID number in a third What I am trying to do is pull ...
1
vote
1answer
344 views

How do I scroll up in the Excel window (not a worksheet window)

I've just opened an excel worksheet, and it's appeared in a restored down window. I can't access the top of the window because it has opened off the top of the excel window (see image below). Is there ...
3
votes
1answer
725 views

Find 5 second peak value in Excel

How do I find the peak value within a 5, 10 or 15 second time period if I have a range of data like the one below? In other words, which 5, 10 or 15 second period has the highest utilization and what ...
0
votes
1answer
55 views

Finding specific column heading and filling table from that column

I have a problem which I've actually managed to solve but I'm currently using around 60 IF-statements which seems ridiculously inefficient. Two workbooks have the same table in them (see pictures ...
0
votes
2answers
7k views

How can I get “Calculated Columns” to work in excel?

Using Excel 2010, I want a single formula to apply to all cells in a column. I see documentation for a feature called "Calculated Columns". That is exactly what I want, but when I follow the ...
1
vote
1answer
1k views

Insertion of rows without changing formulas on other sheets

I have two separate spreadsheets which feed each other (one person completes the main one so all others autofill). I have set up formulas so it autofills but when I insert new lines into the main ...
1
vote
2answers
8k views

Creating a command to clear clipboard in Excel 2010

I would like to make a command that will clear (reset) Clipboard while I am using Excel 2010. I would prefer this as a button in Excel, but it can be a shortcut on the desktop as well. Is there any ...
4
votes
1answer
6k views

Reference a cell of the previous row in the same table in Excel?

How can I reference the cell of the Color column, in the previous row, of the same table, from a cell of the same table? Without relying on the absolute row / column numbers of the worksheet if ...
0
votes
2answers
201 views

Select a table using mouse in Excel 2010

Normally I can select a table by placing the mouse cursor at the upper left corner. And the cursor changes shape to imply we can select the table by a click. On one particular workbook this doesn't ...
0
votes
2answers
54 views

How do I add 30min to 10-Sep-2012 07:28AM

I'm trying to adjust a logbook and need to add 30min to a value 10-Sep-2012 07:28AM. Is there a formula to add an amount of time to this value?
0
votes
2answers
507 views

Looking for a Match in two excel worksheets

I am trying to compare a serial number field in two excel worksheets. If it finds the matching serial number I want to reflect the date in the field next to it. Sheet 1 123456 654321 6789 9876 ...
1
vote
0answers
313 views

How might I evenly assign arbitrary categories smoothly across a large population?

Does Excel have a feature or set of features that can be used to accomplish the opposite of sorting? So, rather than sorting rows so that similar values are contiguous, I want similar values optimally ...