Microsoft Office Excel is a spreadsheet application written and distributed by Microsoft. Use this tag for problems specific to Excel 2007 for Windows.

learn more… | top users | synonyms (1)

0
votes
1answer
3k 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
2answers
49 views

Round any decimal number up to the next tenth number

In Microsoft Excel 2007, which formula is used to convert the following decimal values in this particular manner? 1.3 should convert to 10 2.5 should convert to 10 4.8 should convert ...
0
votes
2answers
62 views

How can I get the Max function to return a value in the winning cells column but not the winning cell itself?

1. Weeks 22 23 24 2. 3 7 1 3. 4% 9% 1% 4. 4% 2% 2% For example in row 3, how would I get the MAX function to have 9% win but then return the number 23 a row ...
0
votes
1answer
2k views

Expanding only selected rows in Excel using VBA Macros

I have written the following VBA code that contains a condition where if the Cell#="Yes" then expand the bottom row, else if Cell#="No" then collapse the bottom row. Private Sub ...
0
votes
1answer
1k 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 change from -4 to +4. A change ...
1
vote
2answers
1k views

How to activate a cell on clicking a radio button in Excel?

I have a radio button with YES/NO option. What I require is that another cell gets activated/highlighted ONLY when radio button YES is clicked, and not when radio button No is clicked. For example: ...
0
votes
1answer
174 views

How do I get Copy & Paste to work like Cut & Paste with formulas in Excel 2007?

Excel 2007 when I Cut & Paste =K62 to a new worksheet and I get =Scenario!K62. When I copy and Paste it I get =REF!. How can I get Copy & Paste to work the same way as Cut & Paste does?
4
votes
1answer
108 views

Line Graph using Row summations

I'm having some trouble in Excel producing a line graph for the following data: +------+--------+-----------+-----------+ | Id | Type | Owner | Date | ...
0
votes
2answers
95 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 ...
1
vote
1answer
26 views

Excel date issue

I have the formula =RANDBETWEEN(DATE(1930,1,3),NOW()-1) in a cell to give me a random date. I have the format of the cell set to a date so the random number looks like a date. For example 38562 looks ...
0
votes
1answer
218 views

String match using values in one worksheet with long list (52K) of values from a column in another worksheet

In worksheet 1: I have a fairly short list (~75) of values (text strings) and would like to find all matches of these strings with values from a column in another worksheet (worksheet 2)... these ...
0
votes
1answer
93 views

How to find the programming done for cells / worksheets in Excel, to be able to set precision?

I have a drawing in SolidEdge software, where the dimensions of the drawing are exported to Microsoft Excel. It has a column where various dimensions of the drawing are given in various forms in every ...
0
votes
1answer
29 views

Excel Data Rearrange and Sorting, Unwanted Data in Row

I have data like this in file: 37/39 TOY-TRUCK#9588 120 PCS PCKG: 60PCS/CTN/0.33 42-59 RECHARGE REMOTE CONTROL CAR#20181 216 PCS ...
0
votes
1answer
572 views

d/m/yyyy to dd/mm/yyyy

I want to change the data such as 9/4/2012(some cells with, green arrow in the right corner. ) into dd/mm/yyyy. I want to standardize all the data of such column into dd/mm/yyyy , I format the ...
0
votes
0answers
66 views

In excel how can I consolidate information in rows based on 2 critera?

I have a worksheet with columns A through BR. Column B contains customers (repeating values) Column J contains a filing date (repeating values) Column O contains loan information I would like to ...
0
votes
1answer
25 views

Date Field displayed as numbers - how to correct?

I have sheet with 133 rows of Contract Start Date & End Dates. Over 70 seem to be in wrong format When i do a VLOOKUP on dates to place them in a field some are coming out in 5 digit codes which ...
0
votes
1answer
86 views

How to change background color in excel on word cell value?

I want the cell to turn green when I type in "win" or red when I type in "lost". How do I do that? I tried with Conditional formatting but I was only able to do it for one word value. There was no ...
0
votes
1answer
177 views

How to add new data to the table with existing subtotals?

Consider this example: http://office.microsoft.com/en-us/excel-help/calculating-subtotals-and-working-with-levels-in-excel-HA001109798.aspx Say after calculating subtotals by week I then want to add ...
0
votes
4answers
3k views

How can I escape special characters in cell formatting in Excel

I'm trying to create a custom cell format that looks like a ratio. I.E. 24 : 1 In the custom formatting option on the cell when I try to put the following in I get an error saying it's not a valid ...
2
votes
1answer
248 views

Macro to merge cells in a column if the same rows are merged in a different column

I need help finding a macro that can merge cells across rows in a column if those same rows are already merged in another column. Below is a screenshot of what I have now that shows the cells that ...
0
votes
2answers
692 views

Excel Formula Help: If Reference Cell = 1, value of Cell1 = “X”, If Reference Cell = 2, value of Cell1 = “”

I have a cell in my Excel Workbook - Range("D5") - that gets its values by selecting from a dropdown list. The choices are 1 or 2. If the choice is 1, I need to set Range("J13").value = "X". If the ...
0
votes
1answer
277 views

Excel Worksheet Function: How to refer to a dynamic column?

Note: Trying without VBA Let's say I have in C1 the word "Hello" From another cell, lets say A1, I want to return C:C, because in the first row 1:1, contains the word "Hello" In A1, I could put: ...
0
votes
1answer
530 views

excel: merging to data sets based on a common field

I have two excel .csv files that have a single numerical field in common, FIELD_A. What I'd like to be able to do is to leverage this common field in order to merge data between the two, so that I ...
1
vote
1answer
187 views

in Excel Range copy, How can I copy without changing the active sheet

There're several tabs in a workbook(Excel file). I want to get a copy from other tabs not in active. When I try this, the control changes from current tab to source tab, so the monitor blinks shortly ...
1
vote
1answer
56 views

How to set a password to open an Excel 2007 document [duplicate]

How to set an Excel password to protect a workbook from unauthorized access by setting a password on it? I once saw an Excel document with a password required to open it. I found out how to do it and ...
0
votes
1answer
35 views

Customise one or more Columns (to also map future updates from worksheet 1) in all worksheets

I have a workbook with 6 different worksheets but I want some columns to stay the same for all worksheets (and map any changes I may make to column A in Worksheet 1 - so that I don't have to manually ...
2
votes
2answers
119 views

Combine PivotTable with prices to calculate billings by person

I have the following: date, name, item, unit, quantity, deliverer, job in a pivot table using name and job in the row field; item in the column field and quantity as the value field. My item's ...
8
votes
2answers
502 views

How can I get Excel's undo/redo functionality to work in a more traditional *per window* manner (and not for the entire instance of Excel)?

This has been driving me crazy lately. I'm not used to having having a program have a global undo/redo functionality (as in when I undo something, it will undo the last action in any currently open ...
0
votes
1answer
395 views

Apply data validation and formula in a cell in excel

I want to apply data validation (based on list) and formula to calculate value in a cell. Problem: I have to make a excel template which includes Task, Task_Priority(values:1,2,3,4,5) and ...
1
vote
2answers
1k views

How can I stop Excel (2007) from copying hidden cells?

Let's say I have some rows or columns that are hidden, and drag a selection across where those hidden values would normally be. Is there a way to prevent Microsoft Excel from copying those hidden ...
2
votes
2answers
242 views

Drawing sample points versus time in Excel

I have a set of data as follow: As you can see the first column has either R or P, the second column is a measured value, and third one is obviously time. I need to present this in a chart (I ...
1
vote
1answer
534 views

Excel puts a comma-separated set of values in one column

I have a file which is clearly comma separated but when I open it in Excel 2007 it puts all the data in a single column. I think the problem is with my Windows localization (set to Dutch) but I am not ...
1
vote
2answers
7k views

Plotting multiple series in a line graph in excel with different time values?

I have the following data: Time v 0 5.4 5 4.5 10 5.3 15 6 20 5.1 25 7.4 30 6.7 40 6.1 50 6.9 60 5.9 70 6.1 80 6.2 Time v 0 4.7 5 4.8 10 5.5 15 6.1 20 6.8 25 7.5 30 7.9 ...
1
vote
1answer
259 views

Does the Excel IF statement make a calculation sheet faster than using IFERROR?

My scenario is that I have a "Dashboard" style sheet that looks up data from a large table based on the cell in column B which is always to its left. The cell in column B contains an INDEX, MATCH, IF, ...
3
votes
3answers
2k views

Exclude outliers from average calculation

I want to calculate the average of numbers given in Column A as shown in the figure below. But I want to base the average on values equal to or greater than 15 and equal to or less than 25, via the ...
0
votes
2answers
540 views

Single search criterion multiple results

I need to output the results from a column of sorted data with only a single search criterion. There are sometimes multiple occurrences for the same criterion. LOOKUP finds the first occurrence only. ...
-5
votes
1answer
68 views

Check four conditions in IF formula

I have four conditions as follows: 17.5 < value < 21.5 60 < value < 125 value < 0.03 value > 0.98 If all these four conditions are true then the answer must be "Pass" and if any ...
-4
votes
1answer
78 views

How to open files when the sources are in a column of another file

I have a large three-column Excel file: first column is "data (Excel file) name" second column is the "data source(of that Excel file's data)" and third column is "remarks" visit link for see ...
-1
votes
1answer
80 views

Rearrange font sequence in home menu font drop down list

How can I rearrange font sequence in home menu font drop down list in Excel 2007? For example I want first position to be (Hindi language) font KURTIDEV 11, then second position to be English font ...
1
vote
2answers
868 views

How to combine values from multiple rows into a single row

I have an Excel 2007 spreadsheet with 2250 rows and 19 columns. In these rows, I may have two rows of duplicate customer information that need to be combined, but only if the cell above is empty. I ...
0
votes
1answer
176 views

Excel SUM or COUNT based on related fields from two Tables

I have two tables in Excel The small table with 50 rows...tblGroups[#Headers]= Employee ID; Name; Group. The second table with 3000+ rows...tblData[#Headers]= EventID; Date; Employee ID; ...
0
votes
2answers
58 views

Home key but want to back to column header only

Currently when I click Ctrl Home, it will go to top left of the sheet. I would like to find out how to go to top of the column only, any shortcut key? Example, from F181 go back to F1. Any idea?
-1
votes
4answers
97 views

Automatic sum function in Excel

I have three columns with 1500 rows in this form: Name codenb area X1 1 12 X2 1 8 X3 2 16 X5 3 2 X6 4 20 X7 5 5 ... ... .. Xn Anyone have an ...
-1
votes
2answers
95 views

Calculate 9 to 5 hrs

I want to calculate number of business hours between 2 days. For eg. A B 15/05/2013 13:25 31/05/2013 10:20 19/05/2013 09:12 22/05/2013 21:10 I want to get result in C ...
0
votes
0answers
382 views

How can I extract images from an Excel sheet, and control their file names?

I have an Excel sheet with the following columns Product ID Product Name Images Images are placed on the worksheet but not in any cell. I want to extract the images as files (jpg, png, gif, etc) ...
4
votes
2answers
3k views

Multiple Y-axis labels in Excel 2010 line chart

I have a table containing cycling data. Date, distance, time, calories burned, max heart rate and average heart rate. They all have different value ranges. Excel scales the chart to show the calories ...
2
votes
1answer
275 views

Excel Conditional Formatting cell's value that has number suffix, like 99th, 23rd, or 1st

So I am filling a cell with Blue, Green, Yellow, or Red based on a Percentile Rank that is returned, that is numbers like ( >=90th=Blue, >=60th=Green, >=30th=Yellow, <30th=Red ) Currently ...
0
votes
1answer
146 views

Changing temporary/default save location

At a work place with 60 something employees a user (Vista computer, Excel 2007) is finding that when she attempts to open Excel files directly from a mail or Internet Explorer it says the disc is full ...
2
votes
2answers
183 views

Adjust Fixed width option in Text to Columns without a mouse?

Is it possible to apply "Text to Columns" without a mouse (we can use only keyboard)? If yes, then how can we CREATE/DELETE/MOVE a break line for Fixed width Original data type?
0
votes
1answer
122 views

Pivot table on filtered data

There doesn't be a way in Excel to run a pivot table on an Excel sheet that already has columns filtered. Thus far, I've had to manually copy and paste the sheet into another sheet and then run a ...