Questions tagged [microsoft-excel]
A spreadsheet application by Microsoft. Use this tag along with [vba] if your question involves programming Excel in VBA, and with [worksheet-function] if it involves an Excel formula or worksheet function. Also add the the specific version tag (e.g. [microsoft-excel-2019] or [microsoft-excel-365]) as features and functions may vary between them.
25,937
questions
0
votes
1
answer
16
views
Excel Calculation
Column A has a 'date' in or 'Allocation Block' in
Column B has a 'date' in
My formula to see how many days are between both is below
=(@columnB))-(@columnA)
However, how can I ensure if Column A has '...
- 1
-1
votes
0
answers
8
views
How to Loop cluster / ranges horizontal to vertical via vba
Newbie here and would like to get a loop working in Microsoft excel. Basically, to arrange clusters / ranges from horizontal to vertical. I have tried transpose function but have not been successful ...
- 1
0
votes
1
answer
14
views
A formula to select the highest cell in a row that then shows the entire row
I would like to do the following:
Find the highest cell in a column in an excel spreadsheet.
Display that entire row.
I have a spreadsheet that tracks our Walks that shows:
Number of Walks (Column A)
...
0
votes
0
answers
28
views
How to make a first class function cell in Excel
It is possible in excel to treat a cell akin to a first-class function?
For example, the typical behavior would be to set cell C1's value to =A1+B1 which is an immediate execution function. The ...
- 101
0
votes
1
answer
26
views
Using a matrix constant as input for control elements and validity
Given that you have in Microsoft Excel a matrix constant named Values defined as ={"One","Two","Three"}, how can I use the constant in control elements like listbox or ...
- 302
-1
votes
1
answer
41
views
Need help using index/match/vlookup
enter image description here
I need assistance creating a formula with match statement(s). My current formula isn't reading all my columns and gives me an "N/A" the moment I change "FOB&...
- 1
0
votes
0
answers
18
views
How to refer to the range of values of an Excel pivot table from a formula
I've got a pivot table with FieldR in Rows and FieldC in Columns, both with some values selected and others filtered out. I've got Sum of FieldV in Values, with values shown as % of Column Total.
I ...
- 101
0
votes
0
answers
25
views
Why don't values fit on the page in editing mode but do actually fit when printing?
In editing mode with the view set on "page break preview" some values don't fit on the page.
However, if I go to print, they fit on the page.
How does this work?
It might not look like it, ...
-2
votes
0
answers
31
views
How to cumulative average in excel? [closed]
i need to make an cumulative average for the price depends on the ticker name , and restart to cumulative average if the number more than zero value
0
votes
1
answer
36
views
Want to give more options in an IF formula
Current formula is
=IF(AND(RIGHT(A3,2)="00",B3="Vinyl",H3="No",I3="01 - UMGI",G3="Catalogue"),"8M Block required","None")
Instead ...
- 1
0
votes
1
answer
37
views
IF formula using vlookup
I am trying to create an IF() formula using VLOOKUP(), my VLOOKUP() is this
=VLOOKUP(A14119,'CoO Flags Report'!D:L,9,0)
but I want it to state no if the VLOOKUP() finds #N/A
- 1
0
votes
0
answers
33
views
Finding values in Column A that aren't in Column B [closed]
I have two large datasets and I am trying to identify values from Sheet 1 column D that do not exist in Sheet 2 column D, I've seen lots of answers for the same table, but if they are two different ...
1
vote
3
answers
71
views
How do I sort an Excel sheet vertically = reverse the order of the full sheet = put the sheet bottom up = turn the worksheet upside down
Data in an Excel sheet needs to be sorted the other way round. I cannot sort the data by the two columns date and time since there are some lines in it that do not have the same pattern, for example ...
- 2,331
0
votes
0
answers
31
views
Linking columns on different sheets
I'm very new to VBA coding (only dabbling for this specific project), so I would really appreciate any advice on an issue I'm having. I'm attempting to independently link multiple column ranges on ...
- 1
-1
votes
1
answer
32
views
Hiding "display values", the numbers 45396 are showing where there is no data entered into the cell. How do I hide this?
enter image description here
Image for explanation. I used the =days(end,begin) formula to calculate the number of days that has lapsed since servicing. HOW DO I HIDE THE 45396 number?????? Super ...
0
votes
1
answer
59
views
What gives Excel/csvs their formatting functionalities?
When I open a csv file by double-clicking it, it opens, and as one would expect, displays the data in a big array of cells with a ribbon of formatting options at the top (Clipboard, Font, Alignment, ...
- 3
0
votes
3
answers
212
views
How do I get the sum of hours:minutes in MS Excel in the status bar and in a formula if the cells come in with a text format?
I know that there are questions around that are much the same, like Sum the number of hours in Excel, which asks you to format [h]:mm, which I do and it still does not work.
Also not so far is How do '...
- 2,331
0
votes
2
answers
50
views
Pivot table in excel: get difference of two rows in original table as a value
I have a huge table that looks something like this:
Period
Retailer
Area
Section of people
Goods type
Metric 1: Quantity sold
q1
some dude
area1
1
doll
111
q2
some dude
area1
1
doll
100
q1
some ...
- 1
0
votes
1
answer
27
views
Chain array formulae
Is it possible to chain array formulae?
i.e.)
Assume a column of values such as "Max", "Michael", "Kyle", "Michael" starting in A1
Then a formula such as =...
- 1
1
vote
1
answer
25
views
How to vlookup all columns and sum
I have two table.
The first table contains menu and price:
Menu
Price
Pizza
20
Pie
10
Beer
5
Burger
10
The second one contains a buyer and menu. I want to get the sum of price based on menu like ...
0
votes
0
answers
35
views
How can I copy a range of cells to my actual column?
I have this Excel sheet A:
And this Excel sheet B:
What I need is to copy all content from Excel B sheet from A2 to A6 to the selected cell of sheet A, in this case A1. This would be the result by ...
1
vote
1
answer
16
views
Excel formula - returning a list of values from multiple criteria
I’ve had a real struggle with this one and can’t crack it for the life of me.
I need to create an excel formula that does the following:
*Looks for all Values in Forces!A1:A5000 that match the value ...
1
vote
0
answers
35
views
Calculate the average wait in days with a condition based on another column
I'm trying to calculate the average wait for a location in days. I have managed to calculate the average wait for a person in days using
=DATEDIF('Jun24'!V8,'Jun 24'!AA8,"d")
This gives me ...
-1
votes
0
answers
20
views
ForFiles export file
I would like to run the following and hopefully be able to see the total size of all of the data being deleted.
forfiles /p "C:\randomfilepath" /s /m *.* /D -180 /C "cmd /c echo @file @...
- 1
0
votes
0
answers
25
views
Unknown object in Excel 2021 cells
In creating a very simple excel sheet in Excel 2021, I am now seeing a small bold cross (Not the one for copy/drag) in several cells. It will neither cut nor delete. It does not enlarge or change with ...
0
votes
0
answers
18
views
put escalated cost value into every nth column [closed]
I Have a list of items Col A, with current cost Col B, that get replaced in varying years (7, 10, 20 etc.years) Col C.
I have an escalation formulae.
I have 30 years in row at top.
Need to know Excel ...
- 1
0
votes
1
answer
18
views
Excel formulae to use conditional formatting each cell in a column which is < or > than the 4th column to the right of that cell
I have a spreadsheet where each cell in each column is compared to the value on the same row in the 4th column from the right and should the value be greater or smaller the font colour is changed ...
0
votes
3
answers
45
views
Microsoft Excel: Highlight decimal higher than .00
I am trying to highlight a cell based on the point decimal value. I understand how to highlight the point decimal with a MOD function but exclude a full value.
The data is quite simply based on ...
- 1
1
vote
1
answer
24
views
limiting excel columns to a maximum
After I import a csv file and double-click to make the columns wide enough to show data for each column contextually, many columns are way too wide.
Is there a routine I can run in an xlsx file that ...
- 427
0
votes
1
answer
39
views
Excel conditional formatting not applied to all cells as configured in "applies to"
The following (link to a) screenshot shows that I have applied conditional formatting to cells O5 through P8 based on the value in column M (of their corresponding row)...
It can be seen that the ...
0
votes
1
answer
14
views
Trying to concatenate a row and variable columns
I'm trying to concatenate a static row and column data that is variable. In Column A1 is Job Roles. So in A2 the value is "Security Analyst", in A3 the value is "Security Engineer&...
- 1
0
votes
1
answer
44
views
How to add cell color based of values with color gradient from red to yellow to green with a formula
I have values like this:
If the values are higher than -5 or +5 they are also red. everything towards 0 gets greener like the screenshot.
Formatting with the conditional format for each column is a ...
0
votes
0
answers
65
views
Need help to optimize my VBA code
What I'm trying to do is I want to copy the rows to each different type based on record type.
Raw file tab - there are hundreds of thousands rows of data containing 6 different record types.
PRPO TAB -...
-1
votes
0
answers
27
views
Automatically populate a template in excel from table with input values
I would like to have an table with input. Press a button. And use excel to generate 1-20 lines with the same input. For every row of input there are 3 output lines.
Simplified Example input table:
...
-1
votes
1
answer
34
views
Excel formula that allocates weight into 3 different sized containers
I have an excel sheet that i use to calculate a raw material cost per kg for my customer.
effectively i want the excel sheet to calculate how many jerry cans are needed for the total weight of the ...
0
votes
1
answer
33
views
Conditional Formatting starting and ending based upon cell values in a range
In a given range of x by y cells, I'm looking for a formula that will format cells in a row starting every time a cell value is "x" and ending every time a cell value anywhere in the range ...
- 883
0
votes
0
answers
23
views
Excel Pivot Table On Separate Row
Is there anyway I can make the Contract Value Covered it's own separate row instead of being a column?
enter image description here
0
votes
1
answer
65
views
How to count instances of repeated values in a cell range while ignoring blank cells
I have a dataset of subjects (one per row), each with scores taken over several days. Each day has multiple scores, but the number of scores varies such that there are many blank cells.
See a small ...
- 1
0
votes
1
answer
60
views
Join/Mix dynamic and static values on Array = alternative to HSTACK that works in older MS Excel version (No VBA, please)
I need to join some value(s) from some cell(s) with a "static" bunch of other values into an Excel array... BUT I'd like to avoid using VBA for it and I need something that works also with ...
- 1,986
1
vote
2
answers
56
views
How to take out hours from d hr m s in excel?
I have a column in excel as "2 d 9 hr 29 m 37 s" and I just want the total time in hours so the output should be 57.49.
The below formula works well until there is a cell with just seconds e....
1
vote
1
answer
33
views
In Excel define name using name box with scope set to Sheet
Using the name box in Excel to define a new name. How can I set the scope of the name to the current sheet?
When I use the name box to define a new name, by default the scope of that name is the whole ...
- 198
0
votes
0
answers
25
views
A query on Power Query: Faster way to transfer data from one file to the other
I am currently consolidating financial statements for 2023 with comparative data for 2022 across multiple agencies.
Now, I have to do the following tasks:
A) Make a way so that the previous year's ...
0
votes
2
answers
31
views
How to make a dynamic range out of a 2D array in Excel?
This is the 2D version of How to make a dynamic range out of a list in Excel?.
Imagine I have a 2D array of data. I'd like to generate a dynamic array out of it so that formulas that use it ...
- 1,925
0
votes
3
answers
52
views
How to make a dynamic range out of a list in Excel?
A fairly common problem I have in excel is that I import a large set of data and want to do various calculations on it. But first I have to "fill down" my data rows, like such:
But if the ...
- 1,925
0
votes
0
answers
77
views
ATAN2 in Excel doesn't work with references
I tried to convert a pair of columns for x-values and y-values into two columns for radii and angles.
For the radii I could write =SQRT($x^2 + $x^2), but for =ATAN2($x,$y) I get complaints about ...
- 103
0
votes
2
answers
56
views
Excel Merge Multiple Row Text Join Formula
I need to be able to Merge Multiple Rows of text bases on the value in the first two columns through a formula
See below for Data Source and Expected outcome (Updated)
Source
No.
Type
Line No.
...
1
vote
1
answer
33
views
How to display benchmark for each product in this stacked column chart?
See example data and chart below. I want to display the benchmark for each region on its corresponding column. How do I go about this? I have tried plotting the benchmarks on the secondary axis and ...
- 25
0
votes
0
answers
19
views
Refreshing data only after every time interval from live master sheet
I have a master sheet that receives live data. I want to link that data to another sheet, which I have already done.
The data on the master sheet refreshes instantaneously (which is almost every ...
0
votes
3
answers
34
views
Excel removes delimiters from first field of "foreign" CSVs
Here's a CSV (UTF-8 BOM encoding), with some values delimited with quote marks:
AAAAA,BBBBB,CCCCC,DDDDD
"a2",b2,c2,d2
a3,"b3",c3,d3
a4,b4,"c4","d4"
a5,"...
- 101
0
votes
0
answers
37
views
two columns compare to result true false excel
I have tried this formula. it's not working the way I want it to. If there is a matching value in column E and B, I want it to write TRUE or FALSE in column G like below screenshot.
In column E, if ...
- 421