New answers tagged microsoft-excel
0
votes
Auto Stoplight Colored Chart
The default "3 Traffic Lights" Icon Set conditional formatting works on a Low-Mid-High numeric scale for R-Y-G coloring. If you just fill that cell with one of three values rather than "...
- 46
0
votes
Can the Lookup_range and result_range of an X-Lookup refer to a column name (column_name) instead of the column range(A:A)?
Formatting your range as a structured table is far and away the easiest way to do this, as @Reddy Lutonadio indicated. If you're a masochist, you can also use the INDEX function with two MATCH ...
- 46
0
votes
How to maintain auxillary calculations on separate sheet in Excel?
Do you just want your side sheet to be a read-only copy of the main sheet? If so, you can just reference a range as an array. Even better, if you format your original data as a table, you can just ...
- 46
0
votes
How to maintain auxillary calculations on separate sheet in Excel?
In B1 you can enter:
=INDIRECT(ADDRESS(ROW(),COLUMN(),,,"Main"))*2
This formula can be copied and does not change when you insert or delete rows/columns in the Main sheet.
But it may be ...
- 2,320
1
vote
MS Excel graphs do not follow decimal separator setting
After changing the separators, the chart must be refreshed, i.e. the sheet must be scrolled so that the chart is drawn again or another sheet must be activated for a moment.
- 2,320
1
vote
How do I change the vertical axis on an Excel graph to display significant time periods? The gridlines are set at odd times
To change the vertical axis on an Excel graph to display significant time periods within a 24-hour period, you can follow these steps:
Right-click on the vertical axis and select "Format Axis&...
- 387
2
votes
Can the Lookup_range and result_range of an X-Lookup refer to a column name (column_name) instead of the column range(A:A)?
If you format your range as table, then you can use the table's header as shown on the example below.
=XLOOKUP(C11,Table1[City],Table1[Country],,0)
The headers are used for the lookup and return ...
- 19.1k
0
votes
calculating the average of column that is not a number
If names are given in column A of Sheet3 you can use a traditional formula:
=AVERAGE(IF(Sheet1!$A$1:$A$6=A1,LOOKUP(Sheet1!$C$1:$C$6,Sheet2!$A$1:$B$4)))
- 2,320
0
votes
Split cell into three columns without losing leading zero
To add hyphens can use one of the following methods:
Using TEXT() functions:
Option One:
=TEXT(B1,"000-000-0000")
Option Two:
=TEXT(B1,"000\-000\-0000")
And to split the data ...
- 2,593
0
votes
Split cell into three columns without losing leading zero
I suggest you use the formula =REPLACE(REPLACE(A1, 4, 0, "-"),8,0,"-")
- 3,692
0
votes
How do you create an Excel table with row headers instead of column headers?
It's now 2024 and I've also found myself wanting to use structured references in Excel but with the table transposed. In my case an electronics application with a mix of 11 user inputs and 15 ...
- 46
0
votes
calculating the average of column that is not a number
You may also use Power Query.
Click Data > From File > From Excel Workbook > Browser your workbook > Tick "Select multiple items", select 2 tables > Click "Transform ...
- 3,692
0
votes
calculating the average of column that is not a number
You could try using the following formula:
=LET(
a, XLOOKUP(C2:C7,E2:E5,F2:F5),
b, A2:A7,
c, UNIQUE(b),
HSTACK(c, MAP(c, LAMBDA(d, AVERAGE(FILTER(a,d=b))))))
Explanations:
Using ...
- 2,593
0
votes
How to count cells that are within specified tolerance with Excel?
The general way to work with tolerances, is by saying that:
abs(Value_exact - Value_estimated) <= tolerance
This can easily be integrated in Excel's CountIf() function.
- 2,283
0
votes
Accepted
Calculate duration between 2 milestones based on criteria
Try using the following formula:
• Formula used in cell B4
=LET(
_Seq, SEQUENCE(B2-B1+1,,B1),
ROWS(FILTER(_Seq, (1-ISNA(XMATCH(MONTH(_Seq),SEQUENCE(F2-F1+1,,F1)))))))
Or, Using TOCOL():
=...
- 2,593
1
vote
How do I find common text values from multiple (>2) rows in Excel?
Came across this old question and figured if I found it, others might come looking for a similar solution. Anyway, for what it's worth, I think a good way to do this is either using BYROW to check ...
- 46
4
votes
Accepted
Excel: Sum all quantities of unique items across all sheets
With Office 365 we can use TOCOL with a 3D reference. Then use either GROUPBY(Currently only available for Insiders(beta)) or UNIQUE,FILTER,and SUM.
=LET(
x,WRAPROWS(TOCOL(Sheet1:Sheet3!A2:B100,1),...
- 23.4k
0
votes
Excel: Sum all quantities of unique items across all sheets
This should be a comment but I'm not allowed to do that yet. There are a multitude of ways to solve this one.
A simple method is to use a grouping of SUMIFS to tally the total count of parts from ...
- 21
0
votes
is there a way to force minutes vs. months format
Wrap your formula with MINUTE() and then use a custom format of 00
- 1,126
1
vote
is there a way to force minutes vs. months format
Use the following format: [mm]
(the brackets allow you to format time as a duration and not a time of day)
- 5,556
0
votes
Excel: How to select a chart's data range using a corresponding value in another cell?
I am not clear on this. Posting a file may help more.
I am guessing that the names in the SummarySheet are the column header's names, right?
So then these would be used to select the second row ...
- 19
1
vote
Accepted
How do I indicate nested data groups side-by-side in an Excel stacked column chart?
To achieve your desired result you need to slightly restructure your data as below. The empty line is not necessary but leads to a better visualisation. Using this data structure, you will be able to ...
- 1,000
1
vote
Nested If's with Vlookups not working in Excel
My original hope to populate information into 2 cells with a formula in a single cell was not able to be done in Excel without using VBA (not how I wanted to solve the problem as it would be overly ...
- 21
0
votes
Excel VBA to list Sheets in selected Workbook and copy listed Sheets to end of Main Workbook
I have updated the user's code and wrote the comments -
Sub SelectWorkbookAndListWorksheets()
Dim dialogBox As FileDialog
Set dialogBox = Application.FileDialog(msoFileDialogOpen)
Dim sheet_name ...
- 101
0
votes
Excel Formula According to the attached table, the rank has been found according to Achieved %, in which some salesman does not have target
You could try using the following formula:
=LET(
_Data, VSTACK(A1:E1,SORTBY(A2:E15,E2:E15)),
IF(_Data=0,"",_Data))
And if you want to exclude the one which don't have a rank then ...
- 2,593
1
vote
How to make this macro work with a date?
To apply a filter to a date field using Date Filters > Equals..., do one of the following:
Option 1:
Apply the default Short Date format to the entire date field.
Then filter by the Value of the ...
- 361
1
vote
Creating a new column containing time in months based on specific conditions
Depending on your definition of a "month", perhaps:
=IFERROR(MIN(DATEDIF(B2,C2,"m"),60),60)
- 8,931
2
votes
How do I add new row in Excel *below* the current one?
I was looking for an answer and did not find a fast solution. So my recommendation is to use LibreCalc instead of Excel since there you always can insert rows above or below the current row and you ...
1
vote
IF cell is BLANK then VLOOKUP multiple criteria
Try wrapping with an IF() function before performing all the Nested IFs() with VLOOKUP() functions:
=IF(C12="","",
IFERROR(IF(C12="Route Stage",VLOOKUP(D12,'Route Stages'...
- 2,593
0
votes
How to add a cell reference to Date Filter in Excel Pivot Tables
Pivot tables are either filtered by selecting a value in the column headings or using slicers.
If you have a date field in the pivot table, I suggest you add a timeline slicer to the pivot table, set ...
- 23k
1
vote
Excel Parent-Child Array to identify and move children not working?
First off, there are some discrepancies between your description and the code used. You said parent rows are identified where the value in column B starts with "xDay_", but the code seems to ...
- 361
0
votes
Creating a new column containing time in months based on specific conditions
If you do not need an absolutely exact "month", then use e.g.
=ROUND( (C2-B2) / (365.25/12) ,0)
There is an average of 365.25 days in a year, and 12 months in the same year.
- 9,494
0
votes
I need to create a percentage from yes or no dropdown answers
That is a very unfortunate data layout. Issues:
You seem to have several "forms" on one sheet.
merged cells - these hinder more than they help
Your biggest problem is that you are ...
- 23k
1
vote
Excel HSTACK multiple arrays where an array may be empty
My proposal is to use a formula like this:
=CHOOSEROWS(CHOOSECOLS($A$1:$C$1,
--TEXTSPLIT(CONCAT(REPT(SEQUENCE(,COLUMNS($A$2:$C$2))&",",
$A$2:$C$2)),",",,TRUE)), SEQUENCE($E$2,,...
- 2,320
2
votes
Accepted
Loop over excel cells using for loop when cells are selected using CTRL
Cells or ranges that were selected via Ctrl+Click consist of multiple Areas. To use the For Next approach, loop through each Cell within each Area:
Option Explicit
Sub ForNextLoop()
Dim oRange As ...
- 361
1
vote
Accepted
Excel HSTACK multiple arrays where an array may be empty
This is the quick fix to your existing formula:
• Formula used in cell A3
=LET(
a, MAKEARRAY(5, A1, LAMBDA(x,y, "hello")),
b, MAKEARRAY(5, B1, LAMBDA(x,y, "world")),
...
- 2,593
0
votes
How to match a number in a column to make sure its in another column
You can use wildcards in a COUNTIF() function. This will count all the cells in column A that contain the value of B1 anywhere within it and ignoring letter case:
=COUNTIF(A:A,"*"&B1&...
- 5,126
0
votes
How to match a number in a column to make sure its in another column
First you can check whether column B number found in column A or not and where a(SEARCH(B1, A:A), if found it will return some number. And then logically you can ask it is number and their sum product ...
- 129
0
votes
Excel: making a chart with non-evenly spaced years on the x-axis
Another options is to use scatter graph with line.
Organize Your Data: Ensure your data is organized in two columns, one for Year and one for Quantity.
Insert a Scatter Plot:
Highlight the data ...
0
votes
How can I make a volatile Excel formula static, specifically a formula containing the TODAY function?
Here is a non-macro answer:
Allow circular references by going to File>Options>Formulas check "Enable iterative calculation" (for Mac it is Excel menu>Preferences>Calculation ...
- 1
2
votes
Accepted
In a pivot table - how to summarize by specific value
When you create your pivot table, set the values to show as % of Row Total
- 8,931
1
vote
Accepted
Dynamic Array - How to Conditionally Generate Sums?
If efficiency is a concern, it's best to avoid iterative methods like REDUCE with INDEX and VSTACK, as they tend to perform very poorly when the number of iterations exceeds 1,000.
As an alternative, ...
- 361
0
votes
Dynamic Array - How to Conditionally Generate Sums?
=LET(range,A2:F3,
REDUCE({"recieved_qty","po_number","po_item","stock_code","container_id","rec_process"},
SEQUENCE(ROWS(range)),
...
- 389
0
votes
Filter function for column with values only
You could try using the following formula:
=TOROW(IF(($H3=$A3)*($I3=$B$3:$B$6)*($C$3:$E$6<>""),$C$2:$E$2&" "&$C$3:$E$6,NA()),2)
- 2,593
0
votes
How to create a single stacked bar chart with dual y axes in excel?
I assume this is what you want:
As you can see you need to restructure your input data range.
Also look at :
Where series1 Looks like :
And as you already figered out:
- 101
0
votes
sum cells based on corresponding values in a data set in excel
Try using SUM() or SUMPRODUCT()
• Formula used in cell F2
=SUM((B2:E2="Yes")*(B$1:E$1=Itemtbl[Item])*Itemtbl[Assigned Number])
The above formula needs to copy down! Also consider using ...
- 2,593
1
vote
How to prevent Excel from converting numbers to scientific representation?
It appears this has finally been added as an option that can be disabled.
Under “File > Options > Data”, there's a section for “Automatic Data Conversion” and one of the check boxes is “Keep ...
- 11
1
vote
How to sum values based on unique values in other column when filter is applied
Try using the following formula:
=SUM(DROP(UNIQUE(FILTER(HSTACK(A2:A14,C2:C14),MAP(D2:D14,LAMBDA(α,SUBTOTAL(103,α))))),,1))
When Manager Peter is selected:
When Country France is selected:
- 2,593
1
vote
how can a cell be mirrored or flipped in excel
Here is one way you could try:
=TEXTJOIN(":",,SORTBY(TEXTSPLIT(TEXT(A1,"[h]:mm:ss"),":"),-SEQUENCE(,3)))
Updated solution based on OP's edit after the initial answer ...
- 2,593
1
vote
Can you combine stacked and clustered columns in an excel chart?
AFAIK it is impossible to achieve such an effect in a simple way. It can only be achieved by indirectly combining and overlaying data. The main and the secondary axes should be used.
From the data ...
- 2,320
Top 50 recent answers are included