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,267
questions
0
votes
1
answer
67
views
How to sort in column and row in order to get specific data in table Excel?
Given that image below is a simple table where the variable is DATE and TYPE. I do know on how to to filter of things in terms of only using one variable.
For instance, if I create those table, I can ...
0
votes
1
answer
535
views
Need Formula to calculate number of months into 2023 given duration and start date
In an excel file I have been given the start date of a contract and it's duration. I need to get the number of months this contract will be active in 2023.
So for example if the start data is 10/1/...
0
votes
1
answer
191
views
EXCEL formula column how to return cell reference
The values in columns E:G are calculated from data in columns A:D. The respective formulas are as follows:
E=A+B
F=B*C*D
G=D-C-B
The excel sheet looks as follows:
Assume I want to find which formula ...
0
votes
1
answer
639
views
Count number of occurences of [x] amount of zeros only after the occurence of first 1 in a row
I have a table which has only 0s and 1s in ~50 columns and ~13k rows.
What I want to do is to calculate how many times did a series of specific amount of zeroes occured in each row, after the first 1 ...
0
votes
0
answers
31
views
Data Source to Excel Date
I've been trying to import data from excel to google sheet. The data I got was in the format of dd/mm/yyyy, which makes sense as we use it in where I live; however, once I tried to copy paste the data ...
0
votes
1
answer
469
views
How do I replace text in rows by the respective columns?
I need to replace text "vegetables" in rows 1-5 entirely with the respective car make names in column A.
For example row 1 should find the word BMW and replace it with all words containing &...
0
votes
2
answers
465
views
Highlight a cell that is greater than or equal a value when another cell is a specific text
I am creating a tracker,
In cell A1 I want to define the value with a 1, 2,or 3.
In Cell B1 I want to highlight if the Cell Value >=60 when A1 is a 1,
and In Cell B1 I want to highlight if the ...
0
votes
1
answer
397
views
sum of cells if they are less than 1000 if more than 1000 then input difference
I am really not sure how to word this.
I am trying to come up with a formula that the weekly total does not exceed 1000 anything over is non-qualified "sales amount"
- Example:
Monday - ...
1
vote
1
answer
126
views
VBA Changing font color
S.No
Topic
Priority
Action
From
Date
Responsible
Target
Done
Comment
1
FuSi
3
kjkj
SSR0
26.07.2022
MR.BBBB
18.08.2022
kjkjkj
2
FuSi
3
kjkj
SSR0
26.07.2022
MR.BBBB
19.08.2022
kjkjkj
simple task. I ...
0
votes
1
answer
79
views
In Excel, How to transform data in row seperated by commas to column with value?
I have information in excel organised like this.
name
type
data
Thomas
table,height
red,180
Jail
cross,dog
young,cow
How do I convert it to this?
name
table
height
cross
dog
thomas
red
180
Jail
...
0
votes
1
answer
32
views
Adding column description of one spreadsheet sheet in another spreadsheet of same excel
I have an excel sheet having 4 different spreadsheets. I need description of column header of three spreadsheets in 4th spread sheet.
Example:
Suppose I have sheet 1 with 6 headers for 6 columns, ...
0
votes
1
answer
25
views
How to properly format combinations generated in VBA script for Excel?
So I have this VBA script that I'm using in Excel to get all possible combinations from data in 4 separate rows. It works well, however I'm trying to figure out how to make it not use a / at the end ...
0
votes
1
answer
1k
views
Calculate and display Best Seller?
I'm wondering if anybody knows a solution before i start trying and failing for a few hours...
I have a table of all my orders since March, which includes the value of the orders, the date the order ...
0
votes
1
answer
77
views
how do i show formula result if any two selected cell has value in excel?
I have a scenario where i have two cell for input Data E24 and I24 and one cell for calculation k24. What i want is if there is value in any of those input cell the calculation should show in the ...
0
votes
1
answer
208
views
How can shift page number all the way to the edge of the margin in Excel 2007?
I need to move page number from here
to here
Is that possible in Excel 2007? I'm not even sure what to search. To be clear, I know how create page numbers and how to position them in the basic sense....
0
votes
1
answer
121
views
SUM function returning 0 value
DATA SET PREVIEW
I Used the sum function to calculate the time, as the trip duration is in seconds i used =SUM(cell/60) and then convert them to HH:MM:SS, but some of the cells are returning 00:00:00
...
0
votes
0
answers
33
views
Auto adjust cell values if one cell values changed
how to adjust values automatically if cell values A1 has changed means then the changed values has to adjusted with B2 values and the final cumulative values remains unchanged.
Suppose i have list of ...
1
vote
3
answers
1k
views
Calculate average in excel excluding cell contains date or zero or text
In B6 I want to calculate average for range c6:h6 skipping cells which has date, zero or non text values.
Result should be - 400
I tried using averageif with filter ex: =AVERAGE(FILTER(C6:H6,ISNUMBER(...
1
vote
1
answer
651
views
Is it possible to link an entry in a filtered sheet to the master sheet, matched on a key variable? (Excel)
I am trying to link a column in a filtered sheet to another column in the master sheet using a key category (i.e., id). Below is the illustrated dataset:
[]
As shown above, I have the master sheet, ...
1
vote
1
answer
255
views
How to append to formula if a condition is met in microsoft excel?
I have a bunch of formulas dotted about my excel tab that are of the form:
=cell_1+0.0049>SUM(cell_2:cell_3) for example-
=O10+0.0049>SUM(H10:N10)
I want to find all instances of these cells and ...
0
votes
1
answer
4k
views
Automatically duplicate the same formula when adding new rows
Let's say we have a formula in column B, for example =LEFT(A2; FIND(" "; A2)) but it could be anything else.
How to make that, when adding a new row in the future (here on row #4), the ...
1
vote
2
answers
440
views
FInd Replace value of a formula in excel but incremental and loop
Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
Dim p As Integer
Dim q As Integer
p = 3
q = p + 1
fnd = "$3"
rplc = "$" & q
For Each sht In ActiveWorkbook....
0
votes
1
answer
93
views
Excel 2007: How to infer the range to use in a function from that used in another function
My document has two relevant columns that I regularly do math on: Hours (B) and Client (D). I create weekly totals of the hours with the function =SUM(B15:B40). However, I also total the hours for one ...
1
vote
0
answers
67
views
Excel 2007 line chart won't add dates past 2014 when using full data range
For reference, the Excel file in question: https://ufile.io/mlczqpkb
When I try and include the entire range of dates and river heights, it always cuts it off at 2014. If I slide the ranges down a few,...
1
vote
2
answers
516
views
Converting positive number values to boolean TRUE in excel
I'm trying to sum up all the entries based on several specific conditions.
The goal is to count all entries that have a 'yes' ticked in them in cells J or K or L or M and have '1st' in H
This is the ...
0
votes
1
answer
587
views
Microsoft Excel- prioritizing based on two date columns
I have two due dates I have to look out for at work, and I want to have a priority column that assigns a critical, critical,high, medium, low , low priority if either of those dates are within -120 , ...
0
votes
1
answer
86
views
Highlight entire rows based on cell values
I want to highlight entire cell values when the row values changes.
For example, row contains
A
A
B
B
B
C
D
E
E
Final result : i want to highlight above the row by means of A has to in one color and B ...
0
votes
1
answer
60
views
How to perform same operation on non concurrent excel cells
I am trying to perform the same operation on excel on a range of non-concurrent cells.
Let's say I have range C:C, within this range I need to perform this operation IF(ISNUMBER(C2),(C2*A2),(0)). How ...
0
votes
1
answer
86
views
Excel macro to highlight common values between two different sheets
I'm having list of account numbers(AA, AAB, AAC, ADC) in column A in sheet 1 and also I'm having some common account number in sheet 2 (AAB,ADC)which is already available in sheet 1. Now i need a ...
0
votes
1
answer
59
views
Tried multiple IFS formula. None is working
So I have a column which has country names plus a few blanks.
So I want the formula to go by this :
IF the country name is "India" put it as India and if the country name is anything else ...
-1
votes
1
answer
664
views
Restarting excel serial number
I wants to add a auto serial number into my excel sheet which should restart from 1 automatically as soon as the text in the next column changed. For example I have column of subjects i.e English, ...
0
votes
0
answers
46
views
Combine then count combination of text in cells in a table
I have an attendance table that I need to verify every day. Every day different employees are billed on different projects, locations, and hours. Each has individual columns while the employees have ...
3
votes
3
answers
578
views
How to unroll a row in Excel, to have a better view for editing?
Let's say we have an Excel spreadsheet like this:
Is there an easy way, ideally built-in in Excel, to "unroll a row" to be able to:
have a better view of all the data in this row
have an ...
0
votes
1
answer
1k
views
Conditional Formatting not updating when referencing new data from another sheet
I have a sheet called 'Email' in which I reference cells from the sheet 'CL Upcoming'. What I am finding is that when I add new rows to the 'CL Upcoming' sheet that the new rows in the 'Email' sheet ...
0
votes
1
answer
275
views
Concatenate sequential column cells into row cells
I need to transpose the columns of data in this format:
Input.
A1
B1
C1
D1
E1
F1
A
W
1
2
3
B
X
1
2
C
Y
1
2
3
4
D
Z
1
2
3
Into this desired result:
Output.
A1
B1
A
W1
A
W2
A
W3
B
X1
B
X2
C
...
0
votes
1
answer
79
views
Excel: Count Weighed Cells
I've an excel-spreadsheet, which is my timetable. this means, that I've some tasks, which is almost always a connected cell, sometimes I connect 20 cells, sometimes only two. Example
Since every cell ...
9
votes
6
answers
8k
views
Prevent Excel to automatically "Wrap text" in a cell
Let's say we have a spreadsheet with all rows having the same default height.
In a cell, let's write Hello ALT+ENTER World. I indeed sometimes need to enter multi-line text in a cell, to write some ...
0
votes
1
answer
150
views
Recognize datetime in 20211124_1502 format with Excel
I have a column in an Excel table with datetimes in this format:
20211124_1502
I tried different formatting options for these cells (Date, Short date, etc.) but the date is never recognized/parsed (...
0
votes
0
answers
56
views
Extracting Totals and Summary of raw data
I wonder if someone may be able to help.
I have a sheet with a list of events a person (email address) has attended and I'd like to extract/summarize the data in columns either at the end or on a new ...
0
votes
1
answer
1k
views
Group multiple excel rows just like SQL
I have some trading data as below :
I want to group data on some columns and do sum for Quantity and Average for price.
And output the data as SQL style like below.
How can i achieve this in excel. ...
2
votes
1
answer
972
views
Excel Gantt chart: time line fixed start 1st each month in major steps?
I am using this an excel Gantt chart like this one, to name any: https://www.vertex42.com/Files/download2/themed.php?file=project-task-list-with-gantt-chart.xlsx
No matter what I try, the Gantt chart ...
0
votes
1
answer
565
views
How do i auto change a cell upon entry of a another cell
I have tried to google the answer but fail to came up with any answers other than doing VBA. I am a user who do not have any knowledge of programming
I am trying to do an excel sheet to track a person'...
0
votes
2
answers
413
views
Highlight all numbers in column B with duplicates that are in column A?
I use old Excel 2007 and have two columns. Column A contains 250 unique values. Column B contains 1000 values where some are duplicates.
If a value in column A is in column B, I want to highlight/...
0
votes
0
answers
62
views
Combining sets of columns without duplicates but while filling in blanks
So, suppose I have some data:
First name
Last name
Position
John
Orangeman
Kathy
Coolidge
Captain
Eric
Smarty
Sidekick
And then another 3 columns of related data:
First name
Last name
Position
...
1
vote
2
answers
95
views
Share of current Excel users who can only open .xls files? [closed]
We're creating a tool in Excel, using the .xlsx format, that we would like to provide on a broad basis as an open source document. Ideally, this spreadsheet should be usable to all Excel users, but we'...
0
votes
1
answer
524
views
Excel plotting many data sets each with their own x-axis values on the same graph
I know how to plot each data set so that they all show up on the same chart together but the way I'm doing it is very labour intensive and will take a long time to do this for all sets of data (50+ ...
0
votes
1
answer
55
views
Filtering entries in Excel with multiple connected data on adjacent column
I'm sorry if my title is confusing, I'm fairly new to excel.
Below is a sample image, basically what I want to do is be able to filter the Topics. But when I filter to see -- Climate Change for ...
0
votes
1
answer
303
views
How do I sort pivot chart groups by their actual value, not their stringified group name?
When I group by a date field, it formats the grouped field as a string and then sorts the recordset as a string rather than its actual value (see screenshots). When grouping by day, it turns 8/10/2021 ...
0
votes
0
answers
228
views
If Cell Contains Text Then Copy to Another Sheet
I want to copy a collumn on to another excel sheet where the first cell starts with: "Not Found"
If a cell in the first collumns contains "Not Found" I want to display them ...
0
votes
2
answers
77
views
Compare two sheets in excel
I have two excel sheets:
-Sheet1 (Giant list)
C2: Application name
D2: Local Name
E2: Description
-Sheet2 (Less application and local names than in sheet2)
A1: Application name
A2: Local name
I want ...