Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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
votes
0answers
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
2answers
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
0answers
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
2answers
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
1answer
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
2answers
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
0answers
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
1answer
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
1answer
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
0answers
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
4answers
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
0answers
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
0answers
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
0answers
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 ...