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.
57
questions
28
votes
7
answers
15k
views
In Excel, when I enter 22222.09482 then I see 22222.0948199999 number in the formula bar
Could you please help - as I have a weird situation that when I enter a number 22222.09482 in cell then I see a different number 22222.0948199999 in the formula bar. Below is the snapshot of the ...
- 305
4
votes
2
answers
14k
views
excel - activate addin through a Group Policy object
I have an addin that I'm installing on the roaming profile of users.
Yet I have to activate it manually for every users.
I thought about activating it automatically with GPO.
I read that but it ...
- 392
2
votes
2
answers
510
views
Dynamic Charts In Excel
Is it possible to add a new series to a chart when adding a new column to a table?
For example, I have the following chart:
Pulling data from this table:
If if I add a new column to the table, for ...
- 21
2
votes
3
answers
2k
views
Return sum of multiple vlookups
Okay, so I have an issue I'm trying to solve all in one formula.
I have a setup of tables:
How many NFPI (Number of Fruit x Percent Intensity) do I have for Apples?
I need to scan column G of Table ...
- 35
2
votes
2
answers
275
views
Unpivot/Transpose columns (without blanks)
I have an asset hierarchy table in Excel:
For testing purposes, the table can be copied/pasted into Excel by using the text below:
Hint: Copy the text from the Stack Overflow edit mode--instead of ...
- 157
1
vote
3
answers
1k
views
Strange residue on cell with Data Validation when using Excel VBA
Thanks to Raystafarian I get rid of that left over Data Validation residue box by deleting all of them from the work book before exiting.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
...
- 25
0
votes
1
answer
355
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 ...
10
votes
1
answer
2k
views
How can I click on an inactive Excel window and have Excel move to the cell I clicked on?
Can I customize Excel so that when the Excel window is not active, and I click on a cell, Excel will move to that cell?
Clicking on a cell activates the window, but Excel does not move to the cell. I ...
- 349
9
votes
2
answers
8k
views
Inactive window scrolling not working with excel
Inactive window scrolling:
With windows 10 there is a new feature called inactive window scrolling, which means if your mouse hovers over a window you can scroll on it, without it needing to be the ...
- 193
8
votes
2
answers
13k
views
How to create custom shortcuts in Excel 2016 for mac
I recently updated to Excel 2016 from Excel 2011 for mac, and I'm missing a very important feature which saved me a lot of time: custom keyboard shortcuts, i.e. being able to create new shortcuts for ...
- 183
7
votes
1
answer
7k
views
How do I remove or disable downloadable fonts in Microsoft Word and Excel?
I have a Microsoft Office 365 subscription. In Microsoft Word 2016 and Microsoft Excel 2016 the font drop-down menu in the Font section of the Home ribbon includes cloud fonts that can be downloaded ...
7
votes
3
answers
1k
views
Left trim: Remove characters after last instance of a number
I have text in Excel that is a combination of numbers, letters, and symbols.
010-038-310-1500-93745
010-038-310-3000-xxxxx-xxxxxxxx-xxxxxxxx
010-038-???-????-?????-????????
010-038-190-3109-?????-...
- 157
6
votes
3
answers
51k
views
Stop Excel formula from changing when inserting/deleting rows
I'm trying to set up a budget workbook for my personal budget using 13 sheets, 1 for the totals and the other 12 for each month. I cannot for the life of me figure out why the formulas I have change ...
- 161
5
votes
2
answers
15k
views
How to make MS Excel cells display dark background with white text instead of the reverse which is default?
By default, MS Excel cells displays white background with black text. This is glaring to the eyes. Some even find it painful. How can we make MS Excel to display black/dark background with white text ...
- 473
4
votes
2
answers
63k
views
Excel - pivot table does not group dates
I am trying to group the dates in the pivot table into months and years. My original date data is in mm/dd/yyyy format in a table that is refreshed through an embedded SQL script.
For some reason ...
- 49
4
votes
9
answers
91k
views
Excel chart formatting lost when Refresh All or individual Right Click on Data > Refresh
I have 4 pivot charts that rely on data that is refreshed from a connection.
When I click refresh all I lose all the formatting I set up (colours/borders/line and bar and 2nd axis choices)
I have ...
- 97
3
votes
1
answer
2k
views
Match with interval
I have this table
I have to get the index of column which has the interval that contains 165 (Tier 2).
For example
165 -> Tier 2
15 -> Tier 0
...
I tried to split the text interval in MIN and MAX
...
- 145
3
votes
1
answer
44k
views
How to conditional format based on cell color
I have excel 2016 I am trying to conditionally format adjacent cells based on the color of the cell i.e. if B5 is green then A5 should become green. Is this possible? I'm not concerned at this time ...
- 31
3
votes
2
answers
3k
views
Numbers in scientific notation imported to Excel 2016 as text
This is a repeat of Error in importing the numbers in scientific notation. That question was never answered and I am not allowed to comment. I am having the exact same problem with Excel 2016.
In ....
3
votes
1
answer
315
views
Transpose unique rows to columns while IF statement conditions are met but do not consolidate
Current State:
A B C D E
FG Component FieldKey Cost$ Qty
7120 7120 1220 $4.71 9,783
7120 9907062 1220 $7.80 4,061
7120 ...
- 45
3
votes
2
answers
87
views
I want to count IF both condition are true Logically count by AND excel
I have two Columns both are categorical columns. Like Age_group and Engagement_category.
And I want to get count no. of each engagement_category in each Age_group.
| Engagement_category | Age_group |
...
- 171
3
votes
1
answer
158
views
Autocomplete with Excel with any data in the same column
Let's say you have 3 lines in a column: abc, def, hgi.
If the cursor is in the next line, and you start writing a, auto-completion is offered, which is handy
If there is an empty line, and you start ...
- 1,900
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 ...
2
votes
1
answer
7k
views
Microsoft Excel UTF-8 encoding without importing
Before you tell me to search more and to look at other people's questions and their answers read my precise question.
Most other topics here deal with how to make UTF-8 the default option within the ...
2
votes
2
answers
3k
views
Is it possible to convert MCQs on excel into Word?
Is it possible to convert MCQs (Multiple choice question) on excel into Word?
I have an excel file which has many MCQs with the options and the correct answer (each one of them is in separate column) ...
2
votes
0
answers
731
views
Change PivotTable source from external data source connection to table/range
In Excel 2016, I have several PivotTables (and corresponding PivotCharts) that use external data sources. The data sources were created via Microsoft Query (an Excel mechanism) and have complex SQL ...
- 157
2
votes
1
answer
221
views
Change cell color when a different value is encountered
Hello there. I Inserted an SQL query in Excel to pick for me data like the above.
Is there a way I can set Excel to automatically Change The Background color each time it encounter a different value....
- 57
2
votes
1
answer
244
views
How to have Office 2013+ Applications Open to Blank (Workbook, Document, etc) automatically?
Every time I launch microsoft excel 2013 (2016) (or Word, etc) it gives me a screen where I need to select which template I want to open -
How do I avoid this selection screen, like in other ...
- 21.8k
2
votes
1
answer
2k
views
Auto-populate cells in Excel
As you can see in my screenshot,
I have a table called "Payment Details Form" that needs to be filled and
printed for each individual of 100 employees:
In another worksheet, I have a data table of ...
2
votes
1
answer
15k
views
How to automatically import data from csv file and append to existing Excel table
I have a .csv file and a master excel file. The master file contains a table and I would like to automatically append the data in the .csv file to the existing table. The data has the same headers and ...
- 31
1
vote
0
answers
222
views
Localized shortcuts for official US, not working ones
Apparently Microsoft maintains only one list of Excel keyboard shortcuts for Windows: Keyboard shortcuts in Excel for Windows.
But in localized versions of Excel with various keyboard layouts not all ...
- 2,716
1
vote
1
answer
299
views
Excel, list comments then hyperlink to original comment location
I can use the function called ListComments() from https://www.myonlinetraininghub.com/working-with-comments-in-vba.
'
' Written by Philip Treacy
' https://www.myonlinetraininghub.com/working-with-...
- 67
1
vote
1
answer
6k
views
How to ignore column values under a certain threshold when making a chart in Excel
I have a column of about 14,000 populated numerical cells, and when graphing this column I would like to ignore all values that are less than 1000. These values are due to error in sensors and show up ...
- 11
1
vote
1
answer
386
views
Reinstate worksheet protection upon save and close
I currently have a VBA code that will automatically unprotect all of the worksheets in my Excel 2016 file by username.
Currently when I open the file it asks for a password. I do not want this to ...
- 27
1
vote
1
answer
621
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
...
- 1,708
1
vote
2
answers
30k
views
Excel: Calculate average number of days between a range of dates
I'd like a bit of help for a scenario I'll be working on with Excel. Any bit of help will be highly appreciated.
I will have an ever growing range of dates for specific items and I need to make an ...
- 353
1
vote
1
answer
601
views
Excel: How to arrange similar values right after the 1st consecutive block of rows containing the same value
I have an Excel sheet where values in a column are duplicate and in non-consecutive rows.
The values are not sorted in any particular order.
I want to bring all rows containing the same value together,...
- 376
1
vote
1
answer
2k
views
Finding the top 10/15/20 percent (excel 2016 or google sheets)
I have an alphabetical list of students in Column A. In Column E is the total number of award dollars they have earned. I need to find the top 10% award earners, top 11-15%, and then the top 16-20%. ...
- 11
1
vote
1
answer
2k
views
Multiply two columns and add the products for a range of rows or cells?
How to multiply two columns and add the products (SUM()) even if the number or rows increases?
For example, given a spreadsheet like this:
| A B
--------------
1 | 2 132.27
2 | 2 122.33
3 | ...
- 1,060
1
vote
1
answer
112
views
MS Excel - Combine tables with only some matching data
I have 2 sets of data that I want to combine into one table. I have tried to use Power Query but I don't think it can quite do what I am after and was wondering if there is another way.
It might be ...
- 45
1
vote
1
answer
127
views
Dynamic Excel Formulas For Ranges
How can I make a formula that:
I can vertically drag down on Sheet2...
That TEXTJOINS two arrays (total of 28 vertical cells) of data on Sheet1...
Which after it TEXTJOINS those 28 cells, goes to ...
- 13
1
vote
1
answer
638
views
Attempting to transpose two column table in excel using first column to group values in second column
I have a two column table in Microsoft Excel 2016 that simply lists a expense category in first column and expense amount in second column. expense category are not unique and will repeat multiple ...
1
vote
1
answer
6k
views
Improving Excel poor filtering performance [closed]
I am having some serious performance issues with Excel when trying to filter relatively small data sets (50,000-250,000 rows).
Filtering a single column on a 100k row dataset to exclude a value may ...
- 253
1
vote
2
answers
21k
views
View Source code Microsoft Excel
Is there an equivalent to view source code in microsoft excel for either the entire excel workbook or possibly for individual cells?
Ideally I would like a way to view a list of every possible option ...
- 1,398
1
vote
2
answers
11k
views
Extract numbers after a specific word in a cell
I have an XLS file that lists hundreds of various times that are all a single string in a single cell, all in the following format:
Days: 103, Hrs: 12, Mins: 15
Days: 34, Hrs: 8, Mins: 27
Days: 10, ...
- 174
0
votes
2
answers
272
views
Check if parent row has corresponding values
I have a table in an Excel spreadsheet that has hierarchical rows:
(Hint: The data below can be copied from the EDIT-view in Stack Exchange and pasted into Excel. This method will result in proper
...
- 157
0
votes
2
answers
1k
views
Split text at last instance of character (retain left hand side)
I have hierarchy path values in column A of an Excel spreadsheet:
FLEET
FLEET \ AIR SYSTEM
FLEET \ AIR SYSTEM \ COMPRESSOR - DRYER
FLEET \ AIR SYSTEM \ VALVES
FLEET \ AIR SYSTEM \ RESERVOIRS
FLEET \ ...
- 157
0
votes
2
answers
3k
views
Excel 2016 Macros - Windows/Mac Issue
I'm using Excel 2016 (Desktop - not online) and I've created a worbook with a Macro that I've signed using a self-signed certificate.
I've set the 'trust center' settings on Windows so it will only ...
- 84
0
votes
2
answers
156
views
How to TEXTJOIN/CONCATENATE based on constraints for specific no of rows count
I need to concatenate certain attribute value based on the sequence number for specific material number and a formula to automate the process.
I have columns as listed in the below screen shot and I ...
- 13
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