Questions tagged [microsoft-excel-2007]
Microsoft Office Excel is a spreadsheet application written and distributed by Microsoft. Use this tag for problems specific to Excel 2007 for Windows.
2,269
questions
2
votes
1
answer
3k
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 ...
- 23
0
votes
2
answers
5k
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 ...
- 101
0
votes
2
answers
7k
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:
=...
1
vote
2
answers
26k
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 ...
- 817
1
vote
1
answer
679
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 ...
- 11
1
vote
1
answer
817
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
1
answer
39
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 ...
- 1
2
votes
2
answers
1k
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 ...
- 21
19
votes
3
answers
6k
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 ...
- 964
0
votes
1
answer
8k
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 ...
- 115
4
votes
6
answers
90k
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 ...
- 964
4
votes
2
answers
1k
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 ...
- 594
2
votes
1
answer
5k
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 ...
- 594
6
votes
2
answers
179k
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
...
- 831
2
votes
1
answer
5k
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, ...
- 413
7
votes
3
answers
30k
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 ...
- 187
0
votes
2
answers
2k
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. ...
- 1
-6
votes
1
answer
275
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 ...
- 1
-3
votes
1
answer
98
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"
See large image
I ...
- 190
-1
votes
1
answer
335
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
2
answers
3k
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 ...
- 11
0
votes
1
answer
3k
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; Value
Now ...
- 413
0
votes
2
answers
90
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?
- 143
-1
votes
4
answers
203
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
-1
votes
2
answers
131
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 column ...
- 1
1
vote
0
answers
2k
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) and ...
- 183
4
votes
2
answers
15k
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 ...
- 601
3
votes
1
answer
1k
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 ...
- 413
1
vote
1
answer
772
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
2
answers
4k
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?
- 21
5
votes
1
answer
40k
views
Pivot table on filtered data
Is there a way 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 filter on that.
Is ...
- 817
0
votes
3
answers
434
views
How to find 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 ...
- 209
3
votes
2
answers
3k
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 want ...
- 135
1
vote
1
answer
331
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 show ...
- 93
1
vote
1
answer
642
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 ...
- 11
1
vote
1
answer
2k
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: www.123....
- 447
2
votes
1
answer
6k
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 ...
- 813
3
votes
1
answer
756
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 ...
- 31
0
votes
1
answer
38
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 ...
-1
votes
1
answer
1k
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 ...
- 1
0
votes
3
answers
7k
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 duplicated ...
0
votes
1
answer
127
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 ...
- 103
3
votes
1
answer
308
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 ...
- 4,936
2
votes
1
answer
276
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 ...
- 469
0
votes
3
answers
2k
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 ...
- 11
0
votes
1
answer
2k
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 ...
- 9
0
votes
1
answer
208
views
Using the advanced filter in Excel
I have 40 rows in Excel starting with a SerialID in sheet 1. In sheet 2 i have 109 rows which I would like to filter based on sheet 1. Sheet 1 contains records i want to hide from sheet 2.
- 131
2
votes
7
answers
203k
views
How do you add extra value to a cell that already has an existing number in it
I find this hard to explain, but I'm stocktaking beers, spirits, and liqueurs and I want to keep one column for my ullage count for each product. However, as I go through the days, I often come across ...
- 21
3
votes
1
answer
1k
views
Calculating mean of value across random trials/refreshes in Excel?
Say I have a 1000 cells filled with random values and a particular cell that computes a final answer based on those 1000 values. Now I wish to track the value of the answer over 1000 trials i.e., I ...
- 349
1
vote
3
answers
61k
views
Excel: how to convert a table with daily dates to weeks
Have an excel file that tracks daily dates against a daily metric.
What I'd like to do is convert the table into weekly metrics. Is there an easy way to do this, perhaps with pivot tables?
- 817