# 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.

830 questions

**-1**

votes

**0**answers

22 views

### How can I make Excel visually update cells?

I am using Excel 2016. I have a small spreadsheet that is exhibiting some unusual display problems.
When I recalculate a range, which includes all cells on the sheet with content or formulas, two ...

**0**

votes

**1**answer

17 views

### Conditional formatting for merged cells

I have these two rules for a set of cells:
The full formulas are
=$AB27=""
and
=LOWER($AB27)=ok
I thought this would make it so that if "ok" (with any capitalization) was entered into the the ...

**0**

votes

**1**answer

31 views

### Percentage calculation formula

I have two questions and I just can't figure it out. I have trying to calculate 3% into a base rate.
$28.54 + 3% = $29.40
The goal really would be to do:
$29.40 - 3% = $28.54
However, when I do ...

**0**

votes

**0**answers

35 views

### Conditional Formatting Wont Work

I created 4 conditional formatting rules and for some reason, only 3 of them work.
For all 4 of them, the rule type is set to "use a formula to determine which cells to format", as I want the whole ...

**0**

votes

**1**answer

15 views

### Excel euro negative numbers

I have the custom cell with the below formatting
_-[$€-x-euro2] * #,##0.00_-;-[$€-x-euro2] * #,##0.00_-;_-[$€-x-euro2] * "-"??_-;_-@_-
How to show negative number inside brackets ?

**-1**

votes

**1**answer

15 views

### Excel: New Powerquery from Web (csv) doesnt work

I want to build a worksheet where the user can input a Stock symbol and the balance sheet is automatically downloaded from morningstar to a table, and then some custom formulas are run on the data.
...

**0**

votes

**0**answers

18 views

### Using EXCEL 2016 'LEN' and 'CELL' functions, nested together

I need to combine two EXCEL 2016 worksheet functions, to later parse resultant text string. According to online Help Page...
(a) CELL("filename", $A$1) - "Filename (including full path) of the file ...

**1**

vote

**2**answers

128 views

+250

### Generate all possible permutations given input values

Please note my question is different from the suggested duplicate. The suggested duplicate does not show all possible variations based on the number of options, so an input of 3 can have a variation ...

**0**

votes

**0**answers

28 views

### Reconciling differences when data repeats in a sheet

In the example data, say we have Company 1 and Company 2. Under each company is the state in which employees work, followed by the name of the employee(s) which work(s) in that state. In the column ...

**0**

votes

**1**answer

49 views

### Complicated 'IF' function in Excel

In Sheet 1, I have the following:
If the Due Date is after the Date Submitted, then I want the value in column D of this sheet to be the Detail Status, which is on Sheet 2,
UNLESS the Detail Status ...

**0**

votes

**1**answer

65 views

### How do I perform a calculation in rows that meet several conditions?

I have two columns of numbers: a 2017 column and a 2018 column. Some cells are blank, the others have numbers.
(Or some cells contain zero and some cells contain positive numbers.)
I want to know ...

**0**

votes

**0**answers

16 views

### Combining mismatched data with specific criteria

Example Data
I am trying to turn ABCD into HIJK. from another forum i understand now =IFERROR(VLOOKUP(H1,A:B,2,FALSE),"") will find unique values, and i just apply this to both lists to find unique ...

**0**

votes

**1**answer

36 views

### Align two columns in excel based on 1 criteria

This has me stumped.
if you have data that looks like ABCD:
How do I get it to HIJK?
I am trying to put 2 pivot tables next to each other, so I can compare the two sheets, but when I do it ...

**0**

votes

**1**answer

16 views

### How to paste sparse columns so they line up with existing data?

So, I have a spreadsheet with about 5000 rows.
Each of these rows has a unique number in column A, followed by some data in column B:
| A | B |
|-------|------------|
| 5142 | Test ...

**0**

votes

**0**answers

27 views

### Excel match will not find true match

I've encountered a very annoying issue with my excel spreadsheet.
I'm ranking a bunch of values and attaching points to the values based on their rank, but consistently, the lowest value cannot be ...

**0**

votes

**1**answer

26 views

### Select user id for every date in excel

I want to select user id for every date in excel. Tried doing this =IF(A2=A1,(IF(B2>=B1,E1+1,0))) but not getting the exact expected result
Input
user_id date
10003 12/19/18
10003 12/19/18
...

**0**

votes

**1**answer

24 views

### A date which is pasted from some other source is not taken as date

In Excel 2016 when I type date as "May 02, 2008" it is taken as date but when I paste it (the same "May 02, 2008") it is not, Why?

**0**

votes

**0**answers

7 views

### Copy ONLY cells that are locked from one worksheet to another

I would like to copy all the locked cells of a worksheet while leaving the data in the corresponding unlocked cells in the new sheet unchanged. The purpose is to be able to update formulas in a ...

**0**

votes

**0**answers

11 views

### How do I copy dropdown lists from Excel to Google Sheets?

I have an Excel sheet with dropdowns. Each dropdown has anywhere from 40 to 1000 values. Right now, to copy from those values, I need to individually select those values and copy it to a dropdown in ...

**0**

votes

**2**answers

16 views

### Excel - Need a formula/ or any other help in extracting Data

I need to find a faster way to extract data form a list. I have a file i wanted to attach so you may have an idea of what I am actually referring to, but couldn't.
On the first tab I have a list ...

**0**

votes

**1**answer

40 views

### Deleting Duplicate Transactions - VBA or Function

Example Transaction Set:
Note the 3 transactions shown. The two transactions highlighted in green are the ones I'd like to delete or flag (doesn't matter).
Using VBA, the code is way too clunky. The ...

**0**

votes

**2**answers

21 views

### Excel: How do I compute a compound product & sum of two columns?

I have two columns of numbers.
I need to write a formula to multiply the two columns together, and then add all of the products together.
In this case: (16*1) + (6*0) + (4*0) + (4*0) + (4*0) + (2*0) =...

**0**

votes

**0**answers

36 views

### Excel double click autofill not working on non-empty merged cells

In Excel (latest 2016 Professional Plus version 1811 on Windows 10) you can copy the formula in a selected cell (cells) down by double clicking on the dot in the bottom right corner. This also works ...

**1**

vote

**1**answer

54 views

### How to re-select selected cells in Excel

Let's say I've selected a few cells in the sheet:
In particular: A3, C3, B5, D6, C7, E8, A9, C9, E9
But then I've accidentally clicked away to a different cell (i.e. E1) and the whole selection is ...

**0**

votes

**1**answer

34 views

### Excel Generate Sequence Using Specified Format

How to generate sequence numbers from 00.0000 to 99.9999 in excel?
E.g.
00.0000
01.0001
01.0002
----
99.9999
Update
Sub test()
[A1:A100000000] = [index((row(1:10000000)-1)/100000,)]
[A1:...

**0**

votes

**0**answers

29 views

### Using COUNTIFS to count times within next 6 hours, next 12 hours, etc

I currently have COUNTIFS set up to count times in specific 6 hour time frames, for instance 12:00-18:00, but would like to change it to 6 hour increments FROM the CURRENT time: 6, 12, 18, and 24.
...

**0**

votes

**4**answers

103 views

### Remove text up to (inclusive) second to last period (or `://` if only one period)

I have the following data:
http://123.456.789.10.com
http://789.12340.com
http://456.789.1123123rs.com
http://12321.com
What I'm trying to do is get the text up to the second to last period. for ...

**0**

votes

**0**answers

20 views

### How to create a tabular data sheet from 2 sheets

I have a list of "locations" and a list of "people" that I would like to dynamically blend together into a tabular formatted data sheet.
List of locations is in 1 sheet under column 1
|Column 1|
|...

**0**

votes

**0**answers

19 views

### How to auto fill a column with same data in blank cells every time the data changes to another sequence

I am needing help figuring out how to auto fill data into blank cells in a column that does not follow any particular pattern. The number of blank cells to be filled will vary. I do not want the ...

**0**

votes

**1**answer

25 views

### select previous row of particular event in excel

I am current working on a problem.. where i need previous event of applaunch in excel
Input
notification_dismiss
notification_dismiss
notification_dismiss
AppLaunch
...

**0**

votes

**3**answers

43 views

### Select 1st day of each user id in excel

Trying to figure out away to get the expected output from the id and date columns. How do i select 1st days of each user id .. the formula i tried is this IF(A2=A1,(IF(B2>B1,FALSE))) but not getting ...

**1**

vote

**1**answer

54 views

### VBA code executing very slowly

I'm new to VBA so the code I've written is very sloppy. My apologies.
The essence of my problem is that I need to delete duplicate rows from a worksheet with approx. 45,000 rows. Each iteration ...

**0**

votes

**1**answer

75 views

### How to get Excel to graph multiple series where series are “mixed” in a table? [duplicate]

I've got a table that contains multiple data series - and they aren't uniformly distributed either. Example below. As you can see there are multiple series here: Income, Short-Term Capital Gain, and ...

**0**

votes

**1**answer

22 views

### Excel Match / Index - Fill in blank cells based on two other cells matching

I need to fill in blank cells with valued cells based on matching cells in two different worksheets.
MasterCAP Table
BESTRFS Table
The cells in MasterCAP[RFS_ID] must be filled in with the value in ...

**0**

votes

**1**answer

46 views

### Excel 2016: Return total quantity of matching part #s over multiple lines if conditions are met

I have 1 sheet that needs to get a total quantity of a part number from a 2nd sheet based on if other columns in the second sheet meet specific criteria.
Here is an example:
And I am trying to use ...

**1**

vote

**0**answers

11 views

### How to use old dot notation for ranges in Excel formulas?

As a veteran spreadsheet user I am accustomed to using dots for ranges. Excel supported this in previous versions: For example, if I entered =sum(b2.b21) then when I hit enter Excel converted it to =...

**0**

votes

**2**answers

37 views

### Finding the difference in quantity of matching items from two sheets

I have two different sheets in Excel with two columns in each sheet. The first column contains the name of the item and the second column the quantity.
How do I get the difference in the quantities?
...

**-1**

votes

**1**answer

51 views

### What is Excel #N/A error abbreviation, looking for?

In MS-Excel we have below functions return errors:
#N/A #REF! #NAME? #DIV/0! #NULL! #VALUE! #NUM!
I think about what is the complete error massage, which each one are represents?
Of ...

**0**

votes

**1**answer

26 views

### Extract Unique Substring from Range of Excel

I have cell values with names that are fairly close to each other. I would like to extract a common sub-string.
Here is an example.
1100_250_Jump_12HR_100MD_S_run1
1100_250_Jump_12HR_100MD_S_run2
...

**0**

votes

**1**answer

41 views

### Count the number of times a combination of two values occurs in a cell

I want to count the number of time a combination of two values occurs in a cell. For example:
ADD UINTRAFREQNCELL:RNCID=1,CELLID=3,NCELLRNCID=1,NCELLID=2;
ADD UINTRAFREQNCELL:RNCID=1,CELLID=3,...

**0**

votes

**1**answer

32 views

### Removing rows in an excel worksheet if duplicate is found in another worksheet

I have two worksheets: "compiled" and "firstpickup". What I'm looking to do is delete rows in the sheet "firstpickup" if the value in column A can also be found in column A of "compiled". "compiled" ...

**0**

votes

**1**answer

32 views

### Combining multiple excel formulas

I have three different formulas that I would like to combine into one formula;
In cell I2 : =IF(ISNA(MATCH(CONCATENATE(A2,D2),INTRAFREQNCELL!E:E,0)),"NO","YES")
In cell J2 : =IF(ISNA(MATCH(...

**0**

votes

**1**answer

33 views

### I need to apply two formulas in one cell (one formula is logic one)

I’m currently working on a Risk Register document in Excel and have populated 2 separate columns: 1) Risk Impact and 2) Risk Probability
Both of those fields can have values from 1 to 5. It depends ...

**0**

votes

**2**answers

89 views

### Keep only one copy of the duplicated data

I am trying to clear out a list of content that appears 2,3,4 or more times repeatedly.
I have two columns and I would like to eliminate the whole row of any repeatedly entry of column B.
My ...

**1**

vote

**1**answer

52 views

### Cell conditional formatting, based on the format of the same row's cells of another column

I've highlighted my duplicate text in column B, using Conditional Formating. No I want to highlight the same field on column A (and afterwards delete all non highlighted texts), but I am not sure how ...

**0**

votes

**1**answer

19 views

### Data extraction from hyperlinked workbooks

I have hundreds of different workbooks to work with and extract certain data from them and summarize these data in a separate workbook. This overview workbook contains hyperlinks to the single ...

**0**

votes

**1**answer

41 views

### How does Increase decimal and Decrease decimal work in Excel

How the increase decimal operation is performed in Excel and how they done it?
Case 1 :
I enter a number in a cell whose number of digit is less than 11.
For instance A1 cell value be : 123
If I ...

**0**

votes

**1**answer

39 views

### Opening a text file with Excel 16 instead of Notepad

I have an accounting software program that produces all files as .txt files. Before upgrading to Excel 16, I was able to choose Excel as the program to open those files. With Excel 16, I have not ...

**0**

votes

**0**answers

19 views

### How to add a checklistbox to an Excel workbook

I'm working as a support engineer on a complex solution, based on several distinct applications, and regularly I get questions on a certain feature, covered by (multiple) applications, mentioning ...

**0**

votes

**0**answers

23 views

### Excel 2016 opening files with grey sections

Some files on Excel 2016 will sometimes open with some of the rows just showing as grey (see image). When this happens, opening the file menu will also show as completely grey. The only way to get out ...