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
6
votes
2
answers
9k
views
Range mapping in Excel
I have a column of data in Excel with numbers [0..100]. I need a new column, which maps these to a new set of values such that:
> 90 : 4
80-90 : 3
70-79 : 2
60-69 : 1
< 60 : 0
I attempted to do ...
- 225
1
vote
2
answers
7k
views
PHONETIC function doesn't work in Excel
I stumbled across the PHONETIC function, which apparently is supposed to generate furigana readings for Japanese kanji. So if I put in 東京都 to Cell A1, and in cell A2 I write:
=PHONETIC(A1)
It should ...
- 645
3
votes
1
answer
4k
views
How to disable “marching ants” from overriding my external clipboard data in Excel 2016?
Did the copy-and-paste behavior change in Excel 2016?
Every time I want to copy something from an external application, I have to press Esc first in Excel 2016 to cancel the marching ants (selection ...
- 1,061
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
10k
views
hide filter in pivot table
I created a pivot table from the following data:
Salesperson | Amount | Include?
John | 100 | Yes
Jane | 350 | Yes
Jim | 200 | No
Jack ...
- 135
0
votes
1
answer
2k
views
Excel - opening file creates 'dummy' task bar icon
I just upgraded to Excel 2016 (Windows 7), and notice that when I open an excel file, I get two icons in the Task Bar. One is the file, which is correct, but the other (when clicked) just goes to a ...
- 96
1
vote
1
answer
633
views
count comma delimited list in excel
I have a column in Excel that contains a comma delimited list:
Header
A, B
A
A, B, C
D, A
I would like to extract this data so that I can count the occurrences of each item so that I would have the ...
- 135
1
vote
1
answer
21k
views
Unable to sort rows because Excel 2016 turned spreadsheet into a table. How do I change back to a spreadsheet?
I created a regular spreadsheet in Excel 2016. After using it for several weeks I now find that my 'sort' functionality normally accessed from the 'Data' tab does not work when selecting the entire ...
- 13
6
votes
1
answer
14k
views
As a US user, how can I change the default date format in Excel 2016 (Mac) to YYYY-MM-DD?
Note: I see several questions (q1, q2) on this topic that describe how to accomplish this with the Windows version of Office, but I haven't found anything on Mac version of Office.
I'm a US user, and ...
- 377
0
votes
1
answer
66
views
How to get column header if sub-string is found in sheet?
I am attempting to get the column header of a cell in Sheet1 when that cell contains a substring that is defined by cell Sheet2!A2. I have used this formula successfully for cases where the string=...
- 3
0
votes
0
answers
293
views
Painting Rows based on Cells' Values
Let's say I have a spreadsheet with ten rows (numbered 1 to 10) and ten columns
(represented alphabetically A through J). Here's what I'm trying to do:
Firstly, I want a given row to be painted ...
- 921
0
votes
1
answer
2k
views
VBA - Type mismatch error with for loop and 2D array
What I am trying to do is copy one array to a temporary array while I resize the original array and then copy the temporary array back to the resized original.
But I am running into the error "type ...
0
votes
3
answers
748
views
DateDiff() producing inaccurate answers
I am trying to write a bit of code that will use the DateDiff function to get the difference in days between two dates. I will then use this number to determine where to enter data on my spreadsheet.
...
0
votes
2
answers
4k
views
Hide a Control Box in Excel
I have some radio buttons in Excel (2011 Mac), and they are grouped by a Group Box.
I want to make the Group Box invisible. I searched on the internet and found that I could do that with VBA by ...
- 195
1
vote
1
answer
1k
views
VBA - Argument Not Optional Error With .Find
I am trying to use .Find to find the cell that contains the name that the user selected, with this I intend to find out information that is in the cell next to it. But when the debugger gets to the ...
1
vote
1
answer
1k
views
Excel: Separate Currency Values from Rest of Line
I have the following conundrum, which I think will be helpful for a lot of people. I have been pulling down data from bank statements (yes, I could use Mint; no, I don't want to for various reasons), ...
0
votes
2
answers
545
views
Add custom category type column to an Excel 2016 Power Query
Used the power query feature of Excel 2016 to import several CSV files containing personal financial history and trying to use the power pivot thing.
Problem is I really need to group the ...
- 39.9k
1
vote
1
answer
76
views
Creating and adding to dynamic arrays
I am trying create a sign in userform for sports club that I help run but I am having trouble adding to a Dynamic Array.
Private Sub UserForm_Initialize()
Dim SignedInNames() As String
Dim ...
1
vote
1
answer
59
views
Excel chart showing text over date
I am trying to show a chart based on a data set similar to this one.
My intention was to show possible patterns of the Items over a period of dates.
Does somebody know how I can create a chart like ...
- 263
1
vote
1
answer
251
views
Loading data to power pivot via power query vs. directly to power pivot
If I'm fine with doing everything in PowerPivot, aside of future proofing in case I may want to edit queries in the future, are there any reasons to put Power Query buffer in between my data source (...
- 11
1
vote
1
answer
93
views
Excel condition comparing two cells then fetching data
I'm relearning Microsoft Excel after moving to Numbers a couple years back. I have an idea in mind that I would like to put in practice, but having a rather hard time trying to do. I'm not sure if ...
- 13
2
votes
2
answers
956
views
Error extracting year when converting date to text in Excel
I have a cell with a date, formatted as a date, in Excel 2016.
When I insert a function like the following...
=TEXT(L1;"yyyy")
I expect to get a string like "1980". Instead I get the text "yyyy" in ...
- 123
0
votes
1
answer
236
views
Can I format the Y-axis in this way?
I'm using Excel version 15.15 on a late 2014 Mac Mini running El Capitan 10.11.1.
I am trying to represent 3 things; Profit & Loss for Rates & FX and for Credit (two different asset classes), ...
- 1
1
vote
2
answers
18k
views
Macro for Reapply Filter on Excel 2016
I have a Mac and am running Excel 2016. I have an Excel sheet which is auto-filtered by date.
Until recently, I had a macro saved which allowed me to press Command+Option+n and it would reapply the ...
- 11
3
votes
1
answer
3k
views
Slow shortcuts on Excel 2016
I have just installed excel 2016 on my computer for work. I am processing a fairly average data set. However, I am finding the keyboard shortcuts much slower than in Excel 2013.
For example, when I ...
- 31
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
0
votes
1
answer
97
views
How to delete one of each set of duplicate values in Excel?
I have a spreadsheet that contains many duplicates. Instead of removing all of the duplicates, I would like to remove one of each duplicate. I want to remove one record from each set of duplicates.
...
0
votes
0
answers
141
views
Wrong decimal separator when inputting axis range in Excel
I have my decimal symbol set to a decimal point in Windows Regional Settings. This particular computer is running Windows 10, but I am experiencing this issue on Windows 7, 8, and 8.1 too.
In ...
- 21
2
votes
1
answer
164
views
Combining OR with AND in Excel
I'm trying to write a formula in Excel 2016 which outputs a "Yes" if the following two conditions are met:
A2 value begins with 4 OR 9
A2 value length is 8
Otherwise output "No".
This is the ...
- 23
4
votes
1
answer
21k
views
How to move columns with drag and drop in Excel 2016?
How to move/reorder columns with drag and drop in Excel 2016 for Mac? The method that worked for Excel 2011 no longer works in 2016. The cursor never changes to the pointer or hand.
- 245
1
vote
1
answer
2k
views
Frequency function in excel 2016
I'm attempting to array data using the frequency function in excel. When I select my data and bin, hit control enter, I get only the answer of the first cell. When I attempt to drag the t cursor down ...
- 11
12
votes
3
answers
18k
views
How to disable snap/jump scrolling in Excel 2016 for Mac
I'm desparately looking for a way to disable snap-scrolling in Excel 2016 for Mac. Basically as you scroll Excel will continually try to keep the cell you're focused on in the centered. So when you're ...
- 121
8
votes
2
answers
7k
views
Microsoft Excel 2016 Mac: Smooth cell transition
Changing cells in Excel 2016 Mac by clicking arrow keys results in smooth (i.e., painfully slow) transitions. Is there a way to disable this?
(Note: distinguish this, I think, from 'smooth scrolling'...
- 183
6
votes
5
answers
8k
views
How to export a spreadsheet to a UTF-8 .csv from Excel 2016 on mac?
Hoping you can help me out with this one...
For my work, I've been assisting a DBA for the last few weeks and we've now been given the task of uploading a very large spreadsheet which has several ...
- 160
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
2
votes
4
answers
7k
views
Can't insert current date and time into Excel 2013
Ctrl+; is not working to insert the current date and time into Excel 2013.
How can I fix that?
UPDATE
Glitch disappeared when Excel restart, so nobody knows the reason and I awarded just "direct" ...
- 12.8k
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