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,934
questions
3
votes
2
answers
2k
views
Turning binary data into string data in Excel
I have some raw data on Excel that is organized in a way that is not easily readable. For example, for a table showing which user likes which color:
A B C D E
User | ...
- 33
1
vote
1
answer
92
views
Compare list contents in excel
So here's my situation: I am creating a sheet where I have 9 groupings of 3 lists where the items chosen are all contained in lists made via data validation. What I need to do is add a score to each ...
0
votes
1
answer
339
views
Excel Match with reverse wildcards
this is my table:
A B c
1 foo.com/page1 foo.com/page1?xyz
2 foo.com/page2 foo.com/page1?abc
3 foo.com/page3 foo.com/page8?xyz
for each item in ...
1
vote
1
answer
144
views
Simplify a formula with lots of IF/AND/OR statements possibly by detecting conditional formatting
I am trying to develop a formula that analyzes a specific material (corresponding to a Part Number value) that has multiple values of MS tied to a hierarchy. This happens in many related ERP-using ...
-1
votes
1
answer
1k
views
Find all rows according to criteria [closed]
Could you please help me. How to extract all the rows which matches criteria, INDEX and MATCH show just first row and I need to see all the rows.
Will appreciate any help.
3
votes
3
answers
5k
views
How to handle duplicate values using LARGE() function?
I want to calculate the top 3 results with duplicate values but different names.
Example data:
Name score
A 40
B 55
C 37
D 55 ...
- 33
2
votes
1
answer
62
views
how to use text of one cell to make a function in another
I have one cell i want to put a file path in, say [C:\users\me\desktop\abc.xlsm]
In another cell reference that text and add on SHEET'$A1 to the end so it references the first cell and then takes the ...
0
votes
1
answer
52
views
Is it possible in excel to have data pull from different cells based on a dropbox?
So I am not sure what to look for as far as what it would even be called but i am trying to set up a sheet where it changes multiple cells formulas when you change to a different sequence in a drop ...
0
votes
1
answer
3k
views
Excel macro stopped working after Windows update kb4103729
Since Windows version 1803 update kb4103729 my Macro for generating a pdf of and mailing an invoice stopped working. It's a Macro with multiple commands in it and references to both the worksheet and ...
- 1
0
votes
1
answer
147
views
Excel: Formula convenience
I am currently trying to pull prices from a separate spreadsheet with the following formula:
=INDEX(table_Data,MATCH(1,(A!2=table_Data[Program])*("Yes"=table_Data[Type]),0),Prices!C!2!:C!53!)
For ...
0
votes
0
answers
754
views
Using table column headers as variables in Excel
Does anyone know if I can plug in a cell address (my variable) into tbl_source[ ] or will it only accept column titles, and #header, #all.... etc.
1
vote
1
answer
624
views
Why is most of my Excel sheet missing and how can I see the missing cells?
I have been given an Excel file that I need to modify. One of the sheets only has a few rows and columns visible for the user as a dashboard and most of the logic is in the cells that just... aren't ...
- 230
0
votes
1
answer
189
views
Align columns properly in Excel
I want to sort Excel columns in a particular way that I'm having trouble figuring out, but I know there should be a simple solution.
I have 4 columns:
Column A is a hostname, column B is its ...
-1
votes
2
answers
555
views
How to add a decimal to a 5 digit number
I get a column of numbers that looks exactly like this:
4320923
What I need to do is turn the number into this:
+43.20923
How do I do this in format cell? I've been messing around and it looks ...
0
votes
1
answer
449
views
Extract specific string from a string with specific ending character
I have a text file and using Notepad++. I have input lines as below (with new line and no set pattern) and want to extract the number before T and output the numbers as pipe delimited.
Input
(...
0
votes
1
answer
456
views
Delete Duplicate rows except rows with zero and null values
Sub Workbook_Open()
Dim value As Integer
value = Range("B3:N1955").value
If IsEmpty(value) Or value > 0 Then
Range("B3:N1955").RemoveDuplicates Columns:=13, Header:=xlsm
Else
End If
End Sub
I want ...
- 11
0
votes
1
answer
549
views
How to not have sum on pivot-table excel spreadsheet
I have a pivot-table excel spreadsheet.
As you can see below, I'm currently having sum in it.
What I would like is to have just, the value without summing.
I checked on the internet and so far, no ...
- 392
1
vote
1
answer
3k
views
TEXTJOIN cells based on criteria
I have a sheet with the following columns:
I'd like to be able to show, for a given experience, all the programs sorted into the following categories:
Never done
Done over 12 months ago
Done 9-12 ...
1
vote
1
answer
45
views
AutoHide/Show Multiple Sheets based upon a cell value?
I have the following VBA and it works great. It Auto/Hides or Auto/Shows perfectly.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("'Client Info'!D20"...
- 11
2
votes
2
answers
8k
views
Multiple date formats in single column in excel
I have a csv file with a date column with dates in various forms as follows:
dd-mm-yyyy
yyyy-mm-dd
yyyy-mm
yyyy
This irregular data is in a single columns.
How do I convert all of them to the dd-...
- 162
0
votes
0
answers
87
views
Text input and column width change for multiple cell and columns respectively at once
I want know the shorten the code for inserting multiple texts to cells and reducing width of column at once. So, is there a way to do that.
Columns("A:A").ColumnWidth = 2
Columns("B:B").ColumnWidth = ...
- 43
1
vote
1
answer
2k
views
How do I Mirror Various Cells between Sheets in Excel
I have a worksheet that needs single cells to mirror single cells on another sheet and vice versa if it gets changed. I found this code on superuser:
For Sheet 1:
Private Sub Worksheet_change(ByVal ...
- 13
0
votes
1
answer
871
views
How to add multiply watermarks in Excel?
I try to find a way to add a lot of watermarks in same sheet in Excel.
Under meaning of watermark I mean a picture behind the grid. Excel allows to add only one picture per sheet (behind the grid), ...
- 15
0
votes
1
answer
154
views
Issues with getting RSS feed from reddit into excel
I have an excel sheet that all of my colleagues and I share and open each day.
I have been thinking and trying to get an RSS feed from Top comment on /r/Jokes from the last 24 hours into being ...
- 45
1
vote
0
answers
2k
views
Is it possible to put multi-line clipboard information into one cell in Excel with just copying and pasting?
I'm unsure if this is possible, but I'm hoping it is a setting.
My "issue" is that when copying multiple lines from somewhere and pasting them into a cell, Excel puts each line into a separate cell. ...
- 45
1
vote
0
answers
1k
views
use excel data analysis toolkit's histogram function for multiple columns
Good day all,
I have been using Excel's data analysis toolkit's histogram function to define bins and create frequency tables for small datasets. My datasets usually have had 5 -10 columns, with each ...
- 149
0
votes
1
answer
1k
views
Updating column values from one Excel spreadsheet to another
I had no idea what to Google for this. Basically I have 2 Excel files: File A and File B.
Here is File A:
And here is File B:
I am trying to loop through File B. I want to look at each row's first ...
1
vote
3
answers
77
views
Evaluate multiple conditions in Excel and return an incremental value if true
I've been searching the web for about 3 hours now and decided it was time to ask how to do the following:
=IF
('SHEETNAME'!A:A1000)="SALESMAN"
AND
('SHEETNAME'!B:B1000)="WON"
THEN
INCREASE ...
- 13
1
vote
2
answers
176
views
Robust linking of cells Excel
Is there a way to link cells so that not only content updates automatically, but also format (fill, border, font, etc.)?
Also, is there a way, other than doing each cell individually, to avoid blank ...
- 111
0
votes
1
answer
1k
views
How to create a count table of two variables in excel pivot
Is it ok to cross post this question?
I am struggling with creating a count table of two variables at the same time. Ultimately, I would like to create a bar graph of the table.
Assuming I have two ...
- 125
5
votes
2
answers
7k
views
Conditional Formatting: Min Value Across Multiple Columns in Excel
I have three columns--Order, Buy, and Craft--and want to identify the minimum value for the row. In the image below, I identify the value in an additional column. I want to avoid the need for the ...
- 63
2
votes
3
answers
29k
views
Excel Pivot with percentage and count on bar graph
Is it possible to create a Pivot bar graph that Shows percentage and Count in the same bar? I only have Excel 2010
MWE:
Firm Product
1 Car
2 Ship
3 Car
4 Ship
5 Car
I can easily ...
- 125
2
votes
2
answers
272
views
Excel macro generate a report based on raw datas
I am currently working on a macro for creating a report of students score and show a percentage at the end of each student.
I think the picture is self-explanatory. I have data of students like shown ...
- 43
2
votes
0
answers
278
views
How to paste HTML headings into Excel
In a previous answer (vba - html table to excel worksheet) about parsing/pasting HTML table contents into an Excel sheet, wbeard2 shared this very helpful, illustrative piece of code. He/she notes ...
- 21
-1
votes
2
answers
2k
views
How do I cut every nth row and paste it on every nth row in Excel?
Let's say I have a list of cities at rows 1, 4, 7... 3n - 2
And let's say I have a list of states at rows 3, 6, 9... 3n
The cities and states are in the same column
How do I cut the states so that ...
0
votes
1
answer
426
views
Concatenate Merged Header Rows
I have a spreadsheet with two header rows. I want to generate the "full column name" as shown on row 3.
A | B | C | D | E | F |
------------------------------...
- 27.9k
1
vote
3
answers
2k
views
Excel: How to ignore wildcards in COUNTIF
I'm trying to write a function that counts duplicate entries in a character column.
(See column function in image below)
However, the function reports incorrect information for some characters.
For ...
- 1,042
4
votes
4
answers
315
views
Not sure how to shorten a formula
I created this formula:
=IF(B8="Jan","Feb",IF(B8="Feb","Mar",IF(B8="Mar","Apr",IF(B8="Apr","May",IF(B8="May","Jun",IF(B8="Jun","Jul",IF(B8="Jul","Aug",IF(B8="Aug","Sep",IF(B8="Sep","Oct",IF(B8="Oct","...
1
vote
1
answer
2k
views
Conditional Formatting using average function in Google Sheets/Excel
I am trying to figure out how to conditionally format a particular cell depending on the average value of a number of other cells. So I've tried a variety of solutions all either ERROR-ing or not ...
- 13
0
votes
2
answers
2k
views
Increment number with letter in cell
I have a column of random numbers that all begin with 'B*****' where the stars are numbers.
I need a formula to add 1 (+1) to the cell on the right of the number and take away 1 (-1) to the left of ...
- 143
1
vote
0
answers
1k
views
Excel : How to restrict maximum number of users to an shared excel file in our organisation's server
I have shared excel workbook on my organisational file server. I created this for online attendance system for my org. We have around 20 sections. One tab (excel sheet) is dedicated to each section ...
- 105
0
votes
1
answer
880
views
Excel conditional formatting based on absolute numbers
I am unable to get Excel conditional formatting working based on a percentage from 0 to 100. It seems to format based on the minimum and maximum number in the selection.
I have 3 separate rules with ...
- 3
0
votes
1
answer
11k
views
Excel scatter plot x axis displays only sequential numbers but not real data selected for x axis
I have an excel scatter plot with 5 different data series on single chart. First 4 series are working well. When I want to add a new series with similar x-axis data (0.0, 0.4, 0.9 .. ) the plot is ...
- 69
0
votes
0
answers
915
views
Moving Excel document breaks hyperlinks URL format
Scenario: I am creating an Excel spreadsheet with file locations and using hyperlinks to open either the file or directory it is stored in.
Problem: I noticed when I hyperlinked a file it used ..\..\ ...
- 1,383
0
votes
2
answers
172
views
How to display text in contains in certain cell number at different sheet in Excel?
I have a list of number in Sheet A and I want to call and display the list of text (ENTIRE ROW) that represent by the same list of number in sheet B.
HELP!
0
votes
0
answers
2k
views
Highlighting of cells disappears in Excel
In excel, I change the border color of cells. The cursor turns into a little pen with which I can click on the border of cells and change its color. But after I do this, I try to highlight some cells ...
- 1
1
vote
1
answer
1k
views
VBA How to keep a specific comma after string split
I am trying to keep a comma after string split so when I input some data in the cell to be like- C1 width 2 ,-keep this comma C8 merge 2 and not like the current output-C1 width 2 C8 merge 2. So this ...
- 37
0
votes
1
answer
233
views
Get column header of first cell of longest consecutive set of cells below a given threshold
I have a formula that calculates the length of the longest group of consecutive cells in a row of data that fall below a certain threshold.
I would like to show the column header of the first cell of ...
- 1
52
votes
2
answers
9k
views
Excel number type changes 1.25 to 43125
When I open a plain new Excel sheet (2016), change the type of the cell to Number and enter the number 1.25, it will change the entered number to the value 43125,00. I get similar results when I ...
- 603
1
vote
1
answer
55
views
Excel 2013 change result of preset formula in a column of cell
I have created an Excel workbook for my client to paste the data which are copied from a system for preparing reports.
However, there is a critical column of data format being changed after copying ...
- 13