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.
26,092
questions
0
votes
0
answers
8
views
Password protected Excel file on usb drive file behaving strangely when clicked
Nothing unusual at first glance - a password-protected Excel file on a usb stick. Previously, put the stick in the usb port, double-click the file and enter the password to work on the file.
Now ...
- 1
0
votes
0
answers
17
views
Dynamic Array - How to Conditionally Generate Sums?
I have a formula that's meant to iterate through a dynamic range, generate cumulative rows (the #/# in "Tally" represents a number of pieces and a length in ft.) and stack them into a new ...
0
votes
1
answer
14
views
Filter function for column with values only
I have a master database that lists the company with associated products and the clinical trial phase the product is in for different diseases. I am trying to only pull in columns that have values in ...
- 1
0
votes
1
answer
26
views
How to create a single stacked bar chart with dual y axes in excel?
I have data pertaining to price-quantity bids are sorted according to price and looks like this:
I wish to plot a single stacked bar chart, something that looks like this image of single stacked bar ...
0
votes
1
answer
11
views
sum cells based on corresponding values in a data set in excel
Can someone kindly help with the following please, I would like to calculate the sum for each person if it says yes in the columns with the corresponding number
Person
Car
House
Job
Holiday
Sum
1
...
- 1
0
votes
1
answer
13
views
How to sum values based on unique values in other column when filter is applied
I have the following problem. There are order numbers in column A which are duplicated due to a different products in column B. What I need is to sum order values in column C only for unique order ...
0
votes
1
answer
43
views
how can a cell be mirrored or flipped in excel
Let's suppose I have cells in Excel with time duration data from a website, copied and pasted in a text file. The format is (hh:mm:ss), like 00:16:30 (equivalent to 0 hours, 16 minutes, and 30 seconds)...
- 486
0
votes
0
answers
26
views
I would like to automatically filter a large dataset for reference elsewhere based on the filters selected in a PivotTable
I would like to take this dataset and put it into a pivot table where it will be sorted into the items I want to select. I then want that to update the table with the dataset so that I can reference ...
0
votes
2
answers
49
views
How can I associate, group, or connect rows in Excel?
This might be a niche question so I'll try to generalize it as much as possible.
Upon extracting data from a folder with PDF files and after some data cleaning, I have the following in PowerQuery:
...
- 129
3
votes
2
answers
53
views
Can you combine stacked and clustered columns in an excel chart?
I am analysing failure modes of a machine using pareto analysis, but I keep adding more data and struggling to represent it correctly on the chart.
In the image linked below, the three bars represent ...
- 33
1
vote
0
answers
51
views
Strange Excel 365 font behavior while editing in cell after Aptos update [closed]
My Microsoft 365 installation just got updated to use the new Aptos fonts. Excel is now using Aptos Narrow (11 pt) as its default font. This is not a question about the merits, or lack thereof, of ...
- 160
0
votes
0
answers
23
views
Excel custom filter hierarchy
Is there any way in excel to have a custom filter on a column that works like when the filter is on the date column when you get a hierarchy of year>month>day>time
If it could work where a ...
- 43
0
votes
1
answer
41
views
How to change from 3.023.842.96 to the format of 3,023,842.96 and I would like to able to sum the numbers too. Thank you
How to change from 3.023.842.96 to the format of 3,023,842.96 and I would like to able to sum the numbers too. Thank you
0
votes
1
answer
34
views
How do I lookup sheet names and create hyperlinks to sheets in a workbook?
I am managing a workbook with many sheets and I would like to make a separate sheet for navigation.
The sheets' names have a numeric prefix, like:
000-ATable
001-AnotherTable
002-YetAnotherTable
The ...
0
votes
0
answers
31
views
how to sum up using multiple criteria?
i am calculating sum of profits (column O) based on the list of comments as u can see from column V. this is working fine. now i want to add additional criteria where i want it to sum up for me based ...
1
vote
1
answer
76
views
How to count the number of cells that have been given a certain colour in MS Excel?
I'm trying to identify the number of cells with given colours using a formula. I've found online references such as this https://learnexcel.io/count-colored-cells-excel/ but have been unable to make ...
- 1,135
0
votes
0
answers
12
views
VBA: Row being pasted needs to all be in the same row, not look at last blank in that column
The VBA code I have currently pastes data into the last blank cell "End (x1Up). But in column C, there is often blank cells, so I need it to look column As last blank to know where it is pasting.
...
- 1
0
votes
0
answers
30
views
How to parse a dynamic vector instead of a static while comparing in a INDEX function - EXCEL
I'll like to apologize for the vagueness on the title but I didn't find a better way to put this in words
Now, better explaining my question:
I'm creating my own dynamic VLOOKUP to auto completed data ...
- 1
0
votes
0
answers
41
views
Excel Sortby formula on alpha numerics e.g. A1 to A100 to return 4 rows of associated data
I have used this formula to sort the column A1 to A100 so that A
=SORTBY(A1:A206,COLUMN(INDIRECT(A1:A206)),1,ROW(INDIRECT(A1:A206)),1)
thanks to one of your posts: Advanced sorting of alphanumeric ...
- 1
0
votes
0
answers
35
views
Search specific text string in single cell - Excel [closed]
I feel like this should be relatively simple but can't manage on my own.
I basically need to be able search a table for specific text string inside a cell, then use this to create a dynamic array (or ...
- 1
0
votes
0
answers
20
views
Excel PowerQuery locking source file for Git
I am using PowerQuery in Excel to load a CSV config file.
= Csv.Document(File.Contents("myFile.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None])
This ...
- 101
0
votes
1
answer
62
views
Parse year only in excel spreadsheet [closed]
I wish to parse the “year” column which has various date formats as shown below and just leave the year only in the column,
13 march 1939,
October 1920,
December 10 1915
- 1
0
votes
1
answer
32
views
How do I sum a series or rows by a formula that considers two columns in excel
Im after a formula that can calulate the sum of an operation between two columns without creating another column.
e.g. Given the below table
Column Value
Column Percent 1
Column Percent 2
100
50%
80%...
- 1,135
0
votes
0
answers
54
views
Add total of Numbers from Multiple Sheets
I have 2 workbooks. One that one employee will use and one that another will use. On Workbook "A" they are scanning and logging when cars are brought into our garage. THey log the Car Number,...
0
votes
0
answers
28
views
ByVal target range returned is in unexpected format
The target.address variable that is passed, is $127:$127. In terms of range, what does it mean?
Private Sub Worksheet_Change(ByVal target As Excel.Range)
If r Is Nothing Then Set r = New Revenues
...
- 297
0
votes
1
answer
61
views
Vlookup inconsistence or confusion(?) using true range lookup
I want to make a commission that is within a certain percentage range. However, the results given are not matched, instead the grade is. Does it have anything to do with the percent value?
The ...
0
votes
0
answers
67
views
Multiple Slicers with Multiple Selection in each with AND condition in Power Bi
I am working on a model that filters items based on the criteria given in the slicers. In this I want to filter the item through three slicers
Hierarchy Slicer
Attribute Slicer
Attribute Value Slicer
...
- 13
0
votes
0
answers
49
views
How to debug corrupt Excel File?
I have an excel file and whenever i open it with the official microsoft excel i get an error/warning message like this:
We found a problem with some content in ’File.xlsx’. Do you want us to try to ...
- 101
0
votes
0
answers
51
views
Variable dice simulation total
I've created dice in Excel, but now I'm trying to calculate the value of the dice based on x amount of dice. ie if I want 3d6, it only pulls values of 3 dice instead of all 15. Would I use COUNTIF or ...
- 101
0
votes
1
answer
34
views
Excel Match Values Between Two Named Tables
I am struggling to find matches between two named tables in excel. Any help would be much appreciated please.
The scenario is:
I have two named tables in two different sheets of the same workbook. ...
1
vote
1
answer
24
views
SEARCH formula in Excel returns #VALUE! error
I have picked this apart but continue to get a VALUE error in Excel. Can anyone tell me what I am doing wrong? My sheet has a cell named "MaterialType" which is populated from a dropdown ...
- 121
0
votes
1
answer
38
views
How do I pull data into a new list separated by commas
What is a formula to pull the States of overlap in a list separated by commas like in this example?
iXcg.png
- 19
0
votes
0
answers
35
views
Excel cell center/middle alignment not truly center or middle
I have cells in Excel that, when I select center alignment, they are still slightly offset to the left of center. When I select middle vertical alignment, they are slightly offset to the bottom of ...
- 1
0
votes
0
answers
23
views
Can an Excel VB textbox be unlocked so the user can drag it around freely in a protected worksheet?
I'm writing a scheduling app in Excel for a testing center and the idea I had was to create textbox objects (VB objects, not Excel objects) of different heights that represent tests of different time ...
0
votes
0
answers
28
views
In Excel, I'd like to add a value to another if a field changes state [closed]
I'm trying to correlate an inventory tab to an orders tab.
I keep track of how many devices are on the shelf on the inventory tab
When I need more, I record that I ordered them on the Orders Tab
When ...
-1
votes
2
answers
44
views
What is the Excel formula to filter these three lists?
What I am trying to accomplish:
Source Data:
A
B
C
D
E
F
G
H
I
J
K
L
M
List A
30
0
0
14
0
0
9
0
0
17
0
0
6
List B
17
13
0
0
12
22
21
0
8
0
0
0
7
List C
31
0
0
5
42
1
0
0
0
0
0
0
41
Output:
List ...
- 19
0
votes
0
answers
25
views
Date picker in Excel 2016
I have cells in an Excel 2016 spreadsheet that are date values and formatted as dates.
Is there a way to make it so a date picker pops up when a user edits a date cell? That would encourage users to ...
- 157
1
vote
2
answers
177
views
Formula to return row pairs of data based on number in second row
In Excel, I have two rows of data.
In each column,
Row 1 contains a letter of the alphabet.
Row 2 contains a number associated with the letter above it.
How do I return the values from only those ...
- 19
-3
votes
1
answer
35
views
Is there function that compares two ranges and returns the number of cells that changed values
I have two ranges and I want to count how many values have changed between them. I am trying to find a function that compares the two ranges and returns the number of cells that change. I know I could ...
- 180
0
votes
1
answer
75
views
Formula to return employee codes from a lookup table of names and codes
In Excel, I have five employees each with a unique emploee code.
In column A I have hundreds of rows of these employee names and I want to return each name's corresponding code in column B.
In columns ...
- 1
0
votes
2
answers
44
views
Is there simpler than OFFSET in Excel to get a cell range?
Let's say you want a range of cells beginning at D3, of height given in cell A1 and width 2, then:
=OFFSET(D3, 0, 0, A1, 2)
works.
Is there something even simpler than OFFSET, like RANGE(D3, A1, 2)?
- 1,900
-2
votes
2
answers
54
views
How to easily take a column of data in excel and turn it into a list in which each value repeats several times before continuing to the next value [closed]
Basically, I want a way to take a column of values, numerical or strings, in Excel, like this:
Val1
BigVal2
SmallVal3
MediumVal6
And turn it into this:
Val1
Val1
Val1
Val1
BigVal2
BigVal2
...
0
votes
0
answers
29
views
Excel VBA to list Sheets in selected Workbook and copy listed Sheets to end of Main Workbook
I have a working VBA code present in a Main Workbook which is designed to browse and select Another Workbook and generate a list of names of all Worksheets present in the selected book in the Main ...
1
vote
0
answers
44
views
Using Power Automate to refresh Stock Level data with Bloomberg [closed]
I have a 'master' file with 500 stocks/tickers and around 100 data points for each ticker that I need to pull. I've been running into the issue where the file often gets stuck, crashes, or just takes ...
- 123
0
votes
1
answer
75
views
Excel formula gives different result than manual calculation
I have a more or less complex formula in my sheet to calculate the remaining amounts of an asset after a transaction happened. As of right now, unfortunately, instead of showing 0 after selling all of ...
- 158
0
votes
0
answers
43
views
Can't save CSV file from Excel (Windows 11, MS Office Home and Business 2019) to WSL 2 folder
I have an Excel file (MS Office Home and Business 2019, running under Windows 11) that I want to save as CSV file in a WSL folder underneath my WSL home folder. WSL is running as version 2 with Ubuntu ...
- 181
0
votes
2
answers
39
views
Issues finding a formula that have 3 cells involved
Need a formula that automatically puts an expiration date 1 year or 6 months after a date entered in a previous cell.
Example:
IF cell A>=90, then Cell C is 1 year after Cell B date
&
IF cell A&...
0
votes
1
answer
48
views
Copy paste from Excel to HTML
(I'm not that good at English so I used Google Translate.)
I have an Excel sheet with data in A1, B1, etc. A text is formed from several parts in the Excel sheet:
="● from "&TEXT(DATE(...
- 1
1
vote
1
answer
45
views
How do I actually automate with PowerQuery?
I had been under the impression that one of the key features of Excel's PowerQuery add-in was the ability to automate/reuse repetitive processes. I thought this would be achieved extracting the steps, ...
- 129
0
votes
0
answers
23
views
Excel Pivot Chart Seconday Axis Disappearing
I have an Excel pivot chart, used to measure quoting metrics in our sales department (showing the top 5 clients based on most activity), consisting of a combo chart (stacked column & line).
Below ...
- 1