Microsoft Office Excel is a spreadsheet application written and distributed by Microsoft. Use this tag for problems specific to Excel 2007 for Windows.
0
votes
1answer
14 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
18 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
36 views
How to set a password to open an Excel 2007 document
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
30 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
33 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 ...
7
votes
2answers
106 views
+300
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
43 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 ...
0
votes
1answer
31 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 ...
1
vote
2answers
72 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
30 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
43 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
votes
0answers
42 views
Text functions and formulae
Could I please have the formula to use when the value returned should be text and not the actual formula. I need this as the returned values are used for subsequent formulae. My only option is to copy ...
-2
votes
0answers
45 views
Dynamically indent a cell based on existing corresponding value
This code indents column B only if I manually change the corresponding cell value in column A. I need column B to indent according to the values of Col A when I copy / paste the values (there are over ...
1
vote
1answer
51 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, ...
2
votes
3answers
78 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
44 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
46 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 ...
-2
votes
1answer
72 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
25 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
79 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
28 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
46 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
61 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
70 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
60 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) ...
3
votes
2answers
85 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
72 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
48 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 ...
-1
votes
0answers
30 views
Excel catalog EAN generation automation
My catalog EAN generator file is over 9 Mb large. So I am constantly trying to improve the performance by removing inter-page references. But even then it's no less than 9 Mb, and takes a lot of my ...
2
votes
2answers
40 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
48 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 ...
-2
votes
0answers
44 views
Cannot copy worksheet to open workbook
I have a problem copying a worksheet to another open workbook. Both files are Excel 2007. So no problem for the version. In the "To Book:" column, I do not see an option to choose the open workbook. I ...
0
votes
3answers
49 views
Excel - How many shops account for 5% of sales
Suppose I have a table where one column is "Shop Address" and another column is "Sales". I want to have a table that shows:
5% of total sales are generated by the top X1 shops
10% of total sales are ...
3
votes
2answers
40 views
Get first and last value from range in a spreadsheet using other column's data for filter criteria
I have data in my spreadsheet in a worksheet like this:
Where the data goes in this format
[ID] | [Date] | [Hour] |
And I have another worksheet where I want the data to be like this:
Where I ...
1
vote
1answer
86 views
Search for single character
I have several cells in Excel 2007 that contain long sentences with the letter r between words. For example:
"The product code is r A23000"
I tried typing "r" in the Find box but the results ...
1
vote
1answer
39 views
Excel time lapse when date and time is entered
I need to calculate the time lapsed between 2 cells in a spreadsheet. Both date and time are entered in the cells M2(5/20/13 2:00 PM) & J2(5/21/13 3:59pm). I need the result to show how many ...
-1
votes
0answers
52 views
replace images with text in Excel
I have 3 columns in an excel sheet.
ID Product
Name
Image
The Image column has images pasted as objects. I have extracted the images by saving the file as html which gave me an html file and a ...
0
votes
1answer
39 views
Excel: URL with normal Text in a Cell
How can you add an HTML URL to a cell with normal text?
I have a cell with normal text, but want to add a URL, but I don't want the whole cell to be the URL.
i.e.
Please go to this link: ...
-1
votes
0answers
35 views
Trying to match values in a worksheet to a range of values in a 2nd worksheet. both worksheets may contain list/array of values
I have two worksheets, but the there is really only 1 column in each worksheet that is giving me a headache.
Both Worksheet 1 & Worksheet 2 has a column E that each contains only 1 value, the ...
0
votes
1answer
58 views
probable cause of excel files getting corrupted
how to find out the probable cause of corruption of excel files...here i m not saying of a single file...files are getting corrupted one after the other...can
1)using windows 8 with excel 2007 and ...
-1
votes
0answers
47 views
Using excel, how to make a “closed form solution” [closed]
I know the starting and ending point of a curve but i don't know how the curve is exactly. I want to build a closed form solution in excel in order to solve this problem (i have to use a certain ...
2
votes
1answer
64 views
How to automate transforming a table into a matrix
I've been looking for a way to automate transforming data in an Excel table into a matrix.
For example, from the left table into the matrix on the right, as shown in the following image:
Apparently ...
0
votes
1answer
24 views
Same cells (formatting, formulae and all the rest) across 32 sheets
I need to have the same headings for the daily report table (31 sheets in one file and a 32nd one for totals). From time to time I need to add a column, or change formatting.
Is there a way I can ...
0
votes
1answer
48 views
Is it possible to build a cumulative formula that will sum on new worksheet added daily?
I have a worksheet that the user opens each day and clicks a button that copies the current sheet to a new sheet and renames (Day 1, Day 2, etc.).
I want to keep a cumulative count in some of the ...
0
votes
3answers
76 views
How do I separate three different phone numbers concatenated in one cell into three new rows?
I am doing work for a client and need to know how to separate these phone numbers.
I have
0121 396 0501 / 0121 123 434* / 0797 978 8265
in one cell and I want to end up with the same row ...
0
votes
1answer
55 views
Merge multiple tables by comparing multiple rows
I have multiple three column tables that share two rows in common:
DailyCosts: DailyPrices
CodeRole CodeRegion DailyCost CodeRole CodeRegion DailyPrice
1 1 ...
3
votes
1answer
52 views
Permanently increase 'Recent Documents'
I've tried to look around for a similar question but it didn't seem as though there was one, so here's my case:
I've tried to increase the number of recent documents in MS Word/Excel for a while now ...
2
votes
1answer
39 views
Excel visual basic function across workbooks?
This seems like it should be a common issue, but I couldn't find anything on it, maybe I'm just searching for the wrong thing.
Basically I get sent a lot of workbooks that need specific modifications ...
0
votes
3answers
41 views
Counting occurrences in first column of a file in Excel
I need code to find duplicate numbers and continue to count the number of times the duplicate was found.
Example: I have 20,000 entries and 303 unique numbers. I need to know how many times each ...
0
votes
1answer
75 views
Conditional Formatting - Highlight An Entire Row by Comparing Two Cells of a Column
I've dug through the questions on this site and they address both problems I have, but separately. I've been trying to combine the solutions for a couple hours with no success.
Basically, I would ...
