Questions tagged [microsoft-excel-2016]
The 2016 version of Microsoft's spreadsheet application. Unlike previous versions, this Office Suite is cross-platform, working on both Windows and OSX. Because of this, when using this tag, it may be applicable to also tag your question with the Operating System you're on.
1,437
questions
0
votes
2
answers
307
views
Count consecutive cells multiple times in Excel without repeating
I'd like to have a formula that counts consecutive cells that are either blank or non blank and group them in rows depending upon how many groups there are.
If B1 is blank, B2-B3 have data, and B4-B6 ...
- 17
0
votes
0
answers
153
views
How to find scatter plot point in the source data?
I have points in an Excel 2016 scatter plot chart:
I've hovered my mouse over a specific position in the chart.
The points at that position are possible outliers, so I want to look them up in the ...
- 157
0
votes
1
answer
127
views
Feedback button missing from Excel 2016 file menu
In Excel 2016 (Microsoft Office Professional Plus 2016):
The Feedback button seems to be missing from the File menu:
I've heard that the menu ought to look like this:
Question:
How can I get the ...
- 157
0
votes
3
answers
62
views
How to request improvements to Excel from Microsoft? [closed]
Software vendors often have a "Suggest an idea" or "Request an ehancement" website where users can request improvements.
Question:
Does Microsoft have such as site?
For example, I ...
- 157
0
votes
1
answer
636
views
How can I concatenate to reference a cell from a deleted sheet without breaking the formula?
Let me try to explain myself, I'm working in a Excel file, where I created some formulas that reference another sheet, but when this sheet was deleted, all my formulas used to break, so I found on the ...
- 15
1
vote
2
answers
702
views
getting Max/Min value in range based on a cell's condition
Greetings to all Excel gurus..
I've got the dataset shown in the image attachedenter image description here..
I'd like to show the MIN and MAX value of "Price/Oz" based on the condition if ...
- 13
0
votes
2
answers
55
views
How can I calculate if a user is in time? (How does NOW() work?)
I know there's a lot of tutorials and stuff about this kind of things, but I have searched and based in what I watched here and here (sorry if both are in spanish).
I want to now, using the hour of ...
- 15
0
votes
2
answers
397
views
Normal equation in Excel (statistics)
I'm attempting to learn about statistics in the infrastructure asset management industry.
I have an existing exponential regression equation that is used to find the condition of a given asset:
Y = ...
- 157
0
votes
3
answers
7k
views
Why is my conditional formatting not working?
I have a column of consecutive dates. I would like to highlight the rows for Saturdays. I have Conditional Formatting applied to $2:$112, and the formula for formatting is =weekday($a2) = 7. However, ...
- 13
0
votes
1
answer
79
views
How to read specific value from thousands cells in Excel?
I have data structure like below on the screen:
https://gyazo.com/fdbc15aa55a21649d238241184277aa4
ID column has thousands records.
Every id has different prices in each country(max 4 values because ...
0
votes
2
answers
907
views
How can I make an excel spreadsheet log that will put multiple entries under one record
So I'm fairly new-ish to excel and I'm making a log to track our purchase orders. My issue is I can't figure a way to enter them in without merging rows; for example purchase order #5 will have 1 item ...
- 1
1
vote
1
answer
57
views
How to automatically diff changes from master database?
I have a problem, which I can’t get around. I thought maybe someone of you already had some similar experience/problem and would be able to help the rest of the community or share some useful ...
4
votes
3
answers
11k
views
Excel - copy sheet to another workbook WITHOUT names and links
I would like to copy sheets from one workbook to another, which is very convenient. However, I've noticed sadly, that even if one sheet is copied, the other stuff from the source workbook comes either....
- 133
0
votes
1
answer
337
views
Counting a cell IF it contains one or more of a list of words
I have a spreadsheet which lists pupils who attend one of our homework centres. The spreadsheet lists the languages that the pupils speak, and I want to count the instances of pupils speaking specific ...
0
votes
1
answer
100
views
Lookup Table to choose Column then find nearest value
I have a spreadsheet that calculates a battery's constant current requirement. I want to be able to find a suitable battery from the set load duration that has a constant current greater or equal to ...
- 3
0
votes
1
answer
2k
views
Put binary numbers per row one by one
My aim is convert decimal number to binary numbers and putting binary numbers one by one per column in EXCEL. I did convert decimal to binary using BASE command and do not know how to put it one by ...
- 1
1
vote
1
answer
2k
views
Change Color Based on Color Value Defined in Cell
I want to use conditional formatting to change the color of cell B1 to the color I have defined in cell A1 (using hex values or anything else that will work).
For example, if I have "#FF0000"...
- 13
0
votes
1
answer
364
views
Excel: Take on the value of a formula cell when other cell is equal to x?
I know that title isn’t great but essentially:
Let’s say
A1 = some number
And
A2 = some formula involving A1
I want to store values of A2 in a column for different values of A1 so like a function ...
- 1
1
vote
1
answer
971
views
Converting Multiline cell data into Rows
I need to convert the multiline text data in cell to individual rows. Also, there's more information corresponding to the cell data in similar format so I need the new row to take that into ...
- 13
0
votes
1
answer
154
views
Why doesn't my small-formula return a value
I have the following array formula in one column of my spreadsheet:
{=SMALL(IF('PFMEA 2020-2021'!$N$16:$N$1048576="H";ROW('PFMEA 2020-2021'!$N$16:$N$1048576);"-");1)}
where the ...
- 958
0
votes
1
answer
616
views
Array Formula - Hide zero until data is entered -
I need to hide the zero for R4 until I input the data.
R4 it's an array formula - R4 (Plan) sums and updates F through Q based on when data for row 5 comes (Act.)
When the data comes, IF THE RESULT IS ...
- 15
0
votes
1
answer
80
views
Excel Hyperlink within the same sheet
I have an excel workbook with 2 sheets. Sheet1 and Sheet2. I want to stay on sheet1 and create a hyperlink to sheet2 but I want the information that is available in sheet2 to be displayed in sheet1 ...
0
votes
1
answer
191
views
Sum cells option does not appear in Excel's Quick Analysis Tool
I past values in Excel Spreadsheet (single column from a PowerPoint
table).
I select the cells in Excel, and a little icon appears at bottom
right of selection.
I click that icon, options appear, I go ...
- 1,753
0
votes
1
answer
654
views
Pivot table column name and horizontal bar in Pivot Chart
My pivot table has a problem.
As you can see,
the first column has a heading of "Row Labels" (in cell A10).
I want it to say "Months".
Also, I'd like to have a horizontal line ...
-2
votes
1
answer
138
views
Formula - Omit values until new month begins
I'm looking to omit existing values until a new month begins.
Currently YTD Plan =Sum(H4:Q4) I'm summing values from Jan through Oct. When the new month begins (November) is there a formula or ...
- 15
1
vote
1
answer
467
views
Formulas - Conditional Formatting
I need the cells from Aug to Dec to be formatted automatically based on the "plan numbers" if actual numbers are higher (green); if lower (red)- I'm trying to set it up so when data comes (...
- 15
0
votes
0
answers
478
views
(How) can I share a column between two sheets in an Excel workbook?
My spreadsheet has become too wide. I had thought to split it across two tabs, as it is just tabular data, rather than calculations.
The leftmost column (A) is text to explain what each row is about.
(...
- 4,465
0
votes
1
answer
288
views
How can I make a cell that has a sum formula already in it, show 1% if the results are =/less than 0?
I have a formula set to show me the SUM of 2 cells(we'll say F10 & F21).
How would I make that cell showing the results of F10+F26 reflect 1% if the answer is less than or equal to 0?
So if F10-...
0
votes
0
answers
99
views
Can I type on multiple Excel cells simultaneously, not copying the entire cell, but just typing on certain parts of each cell
I am dealing with a task that involves repetition in the values of cells, not the entire cell but only on a certain part of it.
In this
I highlighted in pink the values that are similar, from top ...
0
votes
1
answer
37
views
How to calculate Time difference based on conditions i.e different strings and new date?
I am trying to solve this problem but I cannot think of a formula to do in Excel.
Date
Time
Photographer
TimeWorked
04/09/2021
11:17:00 AM
alizée16
#NUM!
04/09/2021
11:23:00 AM
alondra
#NUM!
04/09/...
- 5
0
votes
1
answer
2k
views
How to plot two columns based histograms with Excel?
I want to plot a two column table (two variables) in a single histogram. Is that possible ? what's a good approach is this case ? and what are the alternatives if histograms are not recommended ?
...
-2
votes
1
answer
1k
views
How to make a Histogram where there's both stacked AND grouped bars?
I want to create a histogram in Excel where one bar is a stacked one (detailing some values) and the others being grouped bars (normal histogram) is this possible (straighforwardly without overlaying ...
0
votes
1
answer
90
views
Excel - Want to populate a cell based information in another cell (multiple answers trigger same output)
I am trying to populate a cell in Excel (2016-WIN) based on the information in another cell. I am currently doing this using
=IF(C27="James","Dog",IF(C27="Paul","Cat&...
0
votes
0
answers
4k
views
How to remove a specific field value from Pivot Table
My pivot table is sourcing data from a dynamic table where the number of rows can increase or decrease based on certain conditions.
As such, the range for my data source is something like $A:$M so ...
0
votes
1
answer
31
views
FILTER function not allowing two criteria on one column
Say I have the following table, and I want to get all rows where COL2 = 2 and 3
A
B
1
COL1
COL2
2
A
2
3
S
3
4
F
4
5
E
5
When I run the following formula:
=FILTER($A$2:$B$5,(B2:B5=2)*(B2:B5=3),&...
0
votes
1
answer
6k
views
Can you use FILTER function on top of results from another FILTER function? i.e nesting two FILTER functions
Excel's FILTER function takes in the following parameters:
=FILTER(array, include, [if_empty])
Let's say I use the above function to product a new table, titled table_2. Can I then nest table_2 ...
0
votes
1
answer
615
views
Highlight all the cells if the keyword in the cell matches to any value of a list
For one of my excel sheet, it is required to highlight the cells that has specific keyword in it.
To solve this, i have created a list called my_list which contains value1, value2 & value3.
I have ...
2
votes
1
answer
1k
views
Excel: Plot multiple lines with different X and Y values
I have a few dataset that looks like this:
spend
return
category
50
20%
A
60
30%
A
70
40%
A
15
15%
B
20
25%
B
30
40%
B
45
20%
C
50
30%
C
55
50%
C
50
50%
D
55
55%
D
60
60%
D
How do I plot ...
- 21
0
votes
2
answers
2k
views
Excel VBA - Checking Cell Value in Relation to Another Cell
I want to highlight a row of cells in columns B-K if that part has less than 5 QOH (Column J). I also want it to only highlight the row if the part is in the subinventory "W1" or "...
user1462614
-2
votes
1
answer
97
views
Find value in column, if present AND has different last digit, return text
I have to check If the material number is the correct variant. To do so, I have to compare it to the New Version list. If in the New Version the 10 digit match and the last digit is "2", BUT ...
- 5
-1
votes
2
answers
62
views
IF+AND function
I have to check if the value in C2 is in the column F, IF it is AND the last digit of C2 is "1", I want it to return as "Incorrect Variant" or blank.
How do I write the correct ...
- 5
0
votes
1
answer
1k
views
How to remove blanks from drop down in Excel Pivot Table
I'm trying to create a pivot table that represents a parent-child assembly list. It works nearly perfectly except for one issue. Anywhere there is a blank, a full row with the very little info it has ...
- 101
0
votes
1
answer
502
views
How to find last digit IF next column has a condition?
In Tab 1 I have 2 columns, B with the material numbers of 11 digit, and C with the commentary.
In Tab 2 I have one column (E) with the 10 material number digits.
In column F I have to find the last ...
- 5
2
votes
2
answers
734
views
Extract the latest data in an Excel worksheet
I have data in an Excel worksheet. To simplify a bit, there are three columns:
the first one (column A) is the name of an individual
the second column (column B) is the date a performance was ...
- 425
1
vote
1
answer
2k
views
Alternative for a filter formula
I used a filter formula to find all data from another sheet and column, that start with "US".
In the column, I have about 5000 rows that have different data with different markets. And the ...
- 5
2
votes
1
answer
562
views
How can I use Excel Formulas to return multiple values using nested IF statements if I need one of the returnable values to be blank?
This is Excel 2016.
I am using a workbook to manage some personal workflow tracking and I have toggles that mark H column cells as "True", "False" or having a zero character string ...
0
votes
1
answer
459
views
VBA - Setting Side Borders in Varying Columns and Rows
Everyday I run a list, it is always different. It will have different amounts of rows with different values in them every day.
I have coded it so I have horizontal borders exactly as I want, but now ...
user1462614
1
vote
4
answers
4k
views
Find all missing dates between two dates in excel 2016
I have over 500,000 rows of data in a sheet. In column D of that sheet, each row has a date between Sep 18 2016, and May 31, 2021. I want to use an array formula to list all the dates between those ...
- 15
1
vote
2
answers
663
views
VBA Excel - Checking Text Similarity in Same Column
Image 1
Image 2
I am trying to check for text similarity for the text in the "Line-Side Loc" in Column J. If the text in column J isn't the same, I would like a thick bottom border applied ...
user1462614
0
votes
1
answer
4k
views
Excel CTRL-V not working properly
Edit This can be worked around by cutting the text from source cell in the cell-edit mode but it's a bit cumbersome. The paste-in-cell-edit fails even if the target cell is empty.
Excel 2016 + Win 10 ...
- 310