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,844
questions
0
votes
0
answers
8
views
Check string from cell for match with any string from range, return corresponding value from different range
Cell A1 contains a long text string. Cells B1:B6 contain various shorter text strings. Cells C1:C6 contain values.
I want to search the string in cell A1 for partial matches in any of the strings from ...
0
votes
2
answers
39
views
Best way to define a 2D lookup in excel using lambdas?
I'm surprised that Excel still doesn't have a simple function 2D_LOOKUP(array, row, col). For instance, I have a complicated excel sheet I'm managing and I frequently want to pull the value for a ...
0
votes
1
answer
14
views
How to use excel lambdas and/or array formulas to generate the cross-product of two arrays?
As a late entrant to using lambdas with excel, I"m extremely excited to see how they can simplify my company's sheets.
One area we would love to see simplified: we have a list of values that look ...
1
vote
1
answer
27
views
VLOOKUP ERROR. Works for some cells, does not work on others
So, I have a vlookup formula
=VLOOKUP(A2, Vouchers!E:K, 7, FALSE)
and it's been copied down the table and has been working great. But suddenly, it has stopped working for new rows.
I double checked ...
0
votes
0
answers
16
views
Importing CSV into Excel via Data -> From Text/CSV: Can I automatically apply the Power Query column formats?
Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20124) 64-bit
I have an external program that creates CSVs. Some of the columns have eg. $ or %. The import query is smart ...
-1
votes
0
answers
29
views
Three variable data table
I have a financial model in excel that projects the ending cash balance for each of 12 months. Each column represents a month starting in January. The rows are:
Opening Cash Balance: In January, ...
0
votes
1
answer
33
views
Does selecting all tabs include hidden tabs?
Will following the steps below select only the unhidden tabs or hidden tabs also?
Click on the left-most tab (i.e., the tab for the first sheet). Shift+click on the right-most tab. You have now ...
0
votes
1
answer
30
views
return format color if multiple values are true
I tried different formulas in conditioning formatting, but none seems to work. Can someone please help me with the following:
I need cell J2 to turn red if I2 show completed, but F2 is smaller than G2....
1
vote
3
answers
49
views
Countif number matches pattern in excel
How can I count the number of cells in a column that start with a specified prefix of 3 characters, and have a total length of 6 characters?
E.g. for the column
123000
123001
123002
546000
1230101
...
0
votes
1
answer
39
views
Trying to hide excess info on a running balance
Ok, it has been a while since I did spreadsheets. I have the running balance working great, all the information is spot on. However, when I try to extend the formula it shows the balance all the way ...
1
vote
1
answer
47
views
Excel: SUMPRODUCT and cells containing ""
In cell N30 I have this formula:
=(SUMPRODUCT(A2:B2;$A$29:$B$29)/SUMPRODUCT(--(A2:B2<>"");$A$29:$B$29))
This is a simplified version, hopefully it still makes sense. If the cells in A2:...
-1
votes
0
answers
23
views
Need help to Format Big Number with condition in Excel
Change the numbers format of millions in 2 digits, if the number is less than 1 Million, it should show as 0.95 - How to format number with this condition in Excel
2
votes
2
answers
45
views
Excel xlookup formula
I'm trying to come up with a formula that will yield the position of two criteria. Per below, looking for Product B from column A and then the position of the first non-zero value. In this case, the ...
1
vote
1
answer
33
views
Check string from single cell for match with any string from a range of cells
Cell A1 contains a long text string. Cells B1:B6 contain various, shorter text strings.
I want to return TRUE if any of the strings from B1:B6 appear anywhere in the text string in A1, and FALSE if ...
-3
votes
0
answers
36
views
Excel: How to find length of sublist in list. Sublist are separated by blank cell [closed]
I have a list with sublists in Column A consisting of a Name followed by 1 to 10 Dates. The sublists are separated by a blank cell.
Example:
Jones, Bobby
1/5/24
1/9/24
3/12/24
(blank cell)
Smith, John
...
0
votes
1
answer
22
views
How to return a list of values from a filtered column?
I'd like to return a list of visible values (including zeroes) from a filtered column from one sheet into an another one so that the list will update if a new value is added into the original dataset ...
1
vote
3
answers
40
views
Set number of decimal places input by user and truncate number
I'm trying to create an Excel spreadsheet that the number will sometimes result in the answer having no decimal places and other times numerous decimal places. I want to be able to let the user input ...
0
votes
2
answers
38
views
Counting enrollments by duration (<=4 years and >4 years) from yearly data in Excel
In Excel, I have these numbers of new people who have enrolled in our program each year (10, 0, 0, 480, 0, 1800, 0, 10720, 0, 14, 0, 24, 0). This data is recorded on row 26 across columns D to P. That'...
-2
votes
0
answers
33
views
Excel formula to show two months? [closed]
Trying to see if there's a formula that would pull data from a combination of cells and return showing two months?
someting similar to the following.
Cell showing the two months since its the end of ...
0
votes
0
answers
32
views
How do I run a specific formula in Excel but have it ignore the formula if blank?
I'm currently running this formula =ROUND((AVERAGE(B3:AX3)/2)*100, 0) but if B3:AX3 has no values at all, I get #DIV/0! appearing in the calculation cell.
How do I adjust my formula so that excel will ...
0
votes
1
answer
43
views
IN VBA, How to sum hours and minutes exceeding 24 in memory (not in ranges of excel)
I'm getting some data in the format of text(like for example "12:47" that means 12 hours and 47 minutes) from a URL document and calculating some parameters like overtime work, shortages and ...
0
votes
1
answer
37
views
Dynamic Sheet Referencing in Excel, incrementing months
I have an excel workbook with mutiple sheets. The sheets generally following the naming format like this: Jan23, Feb23, Mar23....Jan24, Feb24, and so on (mmmyy).
I also have a final sheet at the end, ...
1
vote
1
answer
28
views
Disable truncation of labels of treegraph in excel
I'm creating a treegraph in Microsoft Excel, but my labels get truncated unnecessarily. See the image below:
As you can see, the second digit of the labels of the smallest rectangles, which are the ...
0
votes
2
answers
66
views
Why some cells can not be sorted in Excel
I am met with very strange things with spreadsheets in Excel now. Some cells with a date like 2023-10-22 can not be sorted with other cells in the same format in a column. I changed the format of the ...
-1
votes
0
answers
38
views
I am using Excel within Microsoft 365 on a Lenovo laptop [closed]
I have 4 cells with different formula that are all trying to get to a final result. All the formulas individually work in each of the cells:
=(K85+AG85+BC85)/3 (this formula is located in cell BF86)
...
1
vote
1
answer
24
views
Excel Pivot Table showing same count as the first column
I'm a beginner at Excel and am currently practicing pivot tables.
The data that I'm dealing with is this table:
In the ROW Labels, I have added the field "Quiz and Assignment Mark Range" ...
1
vote
1
answer
70
views
Excel lookup function with two text conditions
I would appreciate any advice on designing the right formula for my problem below.
I am trying to come up with a formula that would take into account multiple text conditions. Across the y axis I have ...
2
votes
1
answer
32
views
Excel number format - units of hundredths, thousandths, millionths
For custom number formatting in Excel, there are endless search results to display numbers in units of hundreds, thousands, millions, etc..
Like, to display bits in units of kilo and mega for example
...
-1
votes
1
answer
68
views
Sorting grouped cells in excel based on one column with alpha and numerical values
So my original dataset is structured such that each row represents one street and then there are three sets of columns that represent a recommendation for a design feature to change (like add trees), ...
-1
votes
1
answer
30
views
Excel formula to track streak
I've been using a table to give myself a daily grade on some tasks, each row is laid out like
Do stretches:| A | A | B | C | A did yoga | F forgot | A | B | A | B | A
I would like to add a cell that ...
0
votes
1
answer
41
views
Replacing numbers in a row with another number from another column if the numbers are lower than 1000
I need to substitute numbers in a row if they are lower than 1000 with the mean of numbers in that row calculated without inclusion of the numbers in the row that are lower than 1000.
I've ...
0
votes
0
answers
22
views
CONCAT 3 fields, compare result to a column, return corresponding field in column next to valuated result
I am trying to concatenate 3 fields, then take that result, look down a list in another column, and then return the value next to that result.
EXAMPLE:
Concatenate cells (B2,C2,D2)
Column F contains ...
0
votes
0
answers
13
views
Automatic change of keyboard language in a worksheet in Microsoft Excel
In Microsoft Excel, and using visual basic, how to set a default language for different columns? For example, columns A & C should be set to English by default and columns B & D to Persian. ...
0
votes
1
answer
36
views
How to compare two columns in Excel with a conditional IF statement?
I have three columns that have some expense figures: Actual (column A), Budget (column B), Variance (column C):
For these expenses, if Actual is greater than Budget, I want the variance column result/...
0
votes
2
answers
38
views
How to repeat a filter function multiple times (per dynamic column range)
I have a table A1:C8.
G1 and H1 contain a range begin and end.
This is used to create a sequence of columns for the range (starting in K1 with function: =SEQUENCE(1;(H1-G1)+1;G1;1))
Now K2 has a ...
0
votes
1
answer
54
views
I want to use countif function to get the count of total devices on each ID i have applied the formula but no answer
I want to use COUNTIF() function to get the count of total devices on each ID i have applied the formula but no answer.
Refer to attach image for data.
0
votes
0
answers
70
views
VBA Excel Multiple files saved in newly created folder named with text in cells range and date
I am trying to create a folder and save as copy of my workbook in this folder for all the filled cells in range B11-B14 of my workbook. I managed to create the folder and the file with the correct ...
0
votes
0
answers
29
views
arabic words does not show correctly
I have some words which are not the same in edit mode (upper view) and cell view in excel. I have Arabic language proofing support installed already.
Video
Please see the video. Any suggestions ?
...
0
votes
1
answer
27
views
Excel automatic reduced print size - all print size change to small, all positions are wrong
My company have one file shared for users, it provided by another company and set the print area NOT allow to change (password protected)
User A, B using same configuration computer, same OS and ...
1
vote
2
answers
50
views
Excel Conditional Formatting, alternating groups of three columns
In columns A, B, and C, I have the original data. In columns J, K, and L, the conditional formatting gives us Green for a match and Red for a mismatch. I'd like to apply that same formatting to ...
0
votes
1
answer
30
views
Why is row 8 conditional formatting showing an incorrect result?
Using Excel 2019, I created a conditional formatting rule in J4 and applied it to J4:L9. However, J8:L8 is clearly displaying incorrect formatting. If I change A8 from zero to a '1', then row 8 is ...
0
votes
2
answers
24
views
Quick and easy way to plot a copied list of numbers as a line chart in MS Excel?
I have a copy pasted list of numbers.
I want to plot it in Excel as a line chart.
How can I do this, without having to add labels etc. to the data?
0
votes
1
answer
105
views
Excel Macro Code Disappearing in Network Trusted Location
This problem -may- have started around when I switched from Office 2019 to 365, however I've been doing so much upgrades lately (windows 11, server 2022, etc) that my memory is foggy.
Over the past 6 ...
0
votes
0
answers
30
views
Excel - is there a solution for autocomplete in dropdowns?
I know this feature was introduced to Excel and removed since it was buggy. It's working in Excel online but VBA doesn't, and I need it too.
I am looking for ideas to get the same functionality ...
0
votes
1
answer
28
views
Auto-fill Drop Down Cells
Let's say I have data that looks like this on a data sheet for a workbook:
NAME 1
NAME 2
Bob
Jeff
I would like to make drop-down cells on another sheet that would auto-fill the other column, ...
-2
votes
1
answer
31
views
Link drop down menu to update automatically cell values when selecting in the menu
I have created a drop-down menu that has all the name of all the tabs other than the one I am working in V1-V10.
I am trying to get the linked cells below in the form to update automatically with the ...
-4
votes
0
answers
27
views
Customer Order tracking that depletes inventory automatically
I have a worksheet system with items for babies listed in two storehouses. Right now I have a one main inventory sheet for two storehouses and a different "orders" sheet that has the same ...
2
votes
1
answer
81
views
Excel VBA Else Without If Error -- I can't seem to find where the error is?
I am configuring a long IF statement, where if the financial period is the same, it merges the cells together under the same period header, and if not, it creates a new header with the new period name....
2
votes
1
answer
79
views
Excel: FILTER two columns from a table and output in one column
In Excel, I have a function that outputs filtered contents from several tables that are combined using VSTACK().
As can be seen in the example of what I have now, this works but only outputs the 'Name'...
0
votes
1
answer
27
views
Very weird print margin error, not follow custom margin
My company recently got three new Dell PCs, all the same model, equipped with Windows 11 and Office 2021. One of our users needs to open an Excel file and print it. However, when they try to print, ...