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
52
votes
2
answers
10k
views
Excel number type changes 1.25 to 43125
When I open a plain new Excel sheet (2016), change the type of the cell to Number and enter the number 1.25, it will change the entered number to the value 43125,00. I get similar results when I ...
- 603
32
votes
2
answers
14k
views
What is the significance of the number of columns (16384) in MS Excel 2016?
I just noticed that the MS Excel column count increased from ZZ to XFD, i.e., 16,384 columns. What is the significance of this particular value? Why didn’t MS go further, up to ZZZ? Why did they stop ...
- 431
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
22
votes
3
answers
91k
views
How to remove Document Inspector warning in Excel
I'm running VBA scripts on a series of files that are XLSM type. While I'm trying to automate my processes, I'm getting stopped by this warning about the Document Inspector, meaning I have to click OK ...
- 251
19
votes
2
answers
17k
views
How to remove the tell me what you want to do search box in Ms Office Excel 2016
Is it possible to remove the annoying search bar in Excel 2016?
user222864
17
votes
3
answers
151k
views
Count with grouping in Excel
I want to count a number of rows for each value appearing in a column. Is there an easy way to do this in excel?
Let's say I have the following table:
A B C
1 Cat A
3 Cat A
3 Cat ...
- 373
15
votes
2
answers
246k
views
Creating a "grouped" bar chart from a table in Excel
Firstly, I have almost no experience with Excel charts. :)
I'm trying to create a fairly simple chart from a fairly simple table. But I'm having more problems than I thought I would have and spending ...
- 253
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
11
votes
7
answers
121k
views
Why do all my Excel 2016 files open to the size of a postage stamp?
The start of each day all my Excel files open very small. After I maximize them, they will open and close just fine for the rest of the day. But the next day they all open small again. Here are some ...
- 1,974
10
votes
3
answers
12k
views
Multiple instances upon opening multiple Office Documents
Starting with Version 16.0.8625.2121 of Office (tested with Word and Excel) - when you select multiple documents in the Explorer and hit Enter to open them, you will end up with instances to the count ...
- 133
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
7
answers
54k
views
MS Excel: How to use format painter with just the keyboard?
If you want to paste a format, you can copy and use the keyboard shortcut to paste special (Ctrl + Alt + V). MS Excel also has a useful format painter tool. You can select it using a keyboard shortcut ...
- 1,402
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
3
answers
50k
views
Excel function to insert variables into string
Looking for a way to achieve the following. Imagine I have 2 columns
A | B
---------------
NAME | SURNAME
---------------
Ned | Stark
Arya | Stark
Sansa| Bolton
I would like column C to ...
- 467
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
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
8
votes
7
answers
19k
views
How to combine a range and cell into a new range within a formula in Excel
I have a formula in Excel that takes an array/range of values as one of the input.
I have a range of values, let's say A1:A6, and a single cell C11. I want to combine the range with the single cell ...
- 255
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
7
votes
4
answers
5k
views
How to replace with leading zeros in Excel?
In an Excel file, I need to replace, say, "foo" by "007".
I selected all the file and I explicitly put the cell format to be in Text mode.
So, why it replaces me "foo" by "7" and not by "007" as I ...
- 723
7
votes
2
answers
13k
views
Excel: Graph square wave using time ranges for y=1, and a lack of them for y=0
Sorry for the bad question name.
I have a set of data in a CSV that contains time ranges (nanoseconds since epoch) like so
StartTimestamp,EndTimestamp
284473439207159,284473441207159
284473458747908,...
- 173
7
votes
3
answers
1k
views
Excel IFBLANK equivalent for IFERROR
IFERROR works great because it shortened the formula significantly by having a default behavior, returning the results if condition is not met (no error).
I'm looking for the IFBLANK version of this ...
- 107
7
votes
1
answer
24k
views
How can I stack all non-blank cells from a column?
I am looking for a formula that will take all non-blank values in one column and stack them in another column.
Please see example below. Essentially I am trying to go from column A to B using a ...
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 ...
6
votes
3
answers
2k
views
Given the line between points 1 and 2, what is the change in angle to point 3?
I have polyline vertices in an Excel table.
ASSET_ID VERTEX_NUM X Y ANGLE_CHANGE
---------- ---------- ---------- ---------- ------------
10 1 118.56 3....
- 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
6
votes
2
answers
8k
views
How to disable/enable office clipboard
Question: Is there any way that you can disable clipboard in Office 2016?
I don't mean show/hide status when coping or similar stuff from clipboard options. I mean to completely turn it off. It so ...
- 499
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
6
votes
2
answers
44k
views
Not able to sort columns
I have a pivot table and I would like to sort some columns. But the "sort" function is not working at all. When I click "sort from A to Z" nothing happens. I am just able to sort the first column of ...
- 61
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
6
votes
2
answers
6k
views
How to protect worksheet with writeable cells that retain formatting and comments
Is there a way to protect a worksheet such that certain cells are writeable (e.g. user can put numbers into these cells), but the formatting and comment box cannot be changed? I am able to create a ...
- 389
6
votes
3
answers
8k
views
Excel bug? Inconsistent behavior when Cut/Copy-Pasting across filtered data
Problem
I'm seeing the following inconsistent behavior in Excel 2016, when Copying/Cutting/Deleting data across filtered data:
Copying Cells:
Data gets "copied" from the visible cells only, and not ...
- 161
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
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
5
votes
3
answers
4k
views
Find keywords in paragraphs of text (in Excel)
Edit: All answers work great, but for a big dataset the macro approach worked better. Try all of them and see what works best for you.
I'm trying to solve this problem in Excel for performance (I ...
- 73
5
votes
2
answers
7k
views
Conditional Formatting: Min Value Across Multiple Columns in Excel
I have three columns--Order, Buy, and Craft--and want to identify the minimum value for the row. In the image below, I identify the value in an additional column. I want to avoid the need for the ...
- 63
5
votes
2
answers
26k
views
Excel Power Query date format messup
I'm trying to import dates from a csv file to Excel. The input is in the format of m/d/yyyy, and the same is my system setting:
However, when I change the Data Type setting upon importing, it messes ...
- 197
5
votes
4
answers
5k
views
Avoid cell resizing when creating a table using excel
I've searched far and wide on the Internet, but all the solutions for the questions I find are on how to keep excel from resizing cells in an already existent table or pivot table. I also searched in ...
- 85
5
votes
3
answers
92k
views
Put a checkbox in a cell in Excel v15 for Mac
I am trying to make a checklist in Excel.
Basically I am trying to make a cell:
Into a checkbox that can be checked or unchecked, and saved with the spreadsheet:
It's super-duper easy to do it in ...
- 222
5
votes
1
answer
357
views
MS Excel: Displaying custom lower error bars
I have an odd issue with custom error bars in MS Excel 2016 (from MS Office 365). The upper error bars are showing but not the lower ones. At least not all the time... only once in a while...
See the ...
- 283
5
votes
0
answers
13k
views
Change keyboard shortcuts in Microsoft Excel 2016 in Windows [duplicate]
In Excel 2016, I would like to change the keyboard shortcut for the Search command from CTRL+B to CTRL+F so that this command has the same keyboard shortcut as in other programs. However, I could not ...
- 2,569
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
4
votes
1
answer
3k
views
Excel format from mm/dd/yyyy to mm-dd-yyyy
I am trying to convert a date from a format with slashes to one with dashes. however when i try:
=SUBSTITUTE(B3,"/","-")
It converts my the would-be date into a date integer. I have tried formatting ...
- 147
4
votes
2
answers
8k
views
How can I modify the "Applies To" property of a Cell's Conditional Formatting rule using VBA?
I'm attempting to use VBA to create a conditional formatting rule and apply it to multiple cells.
I've attempted this is by creating the rules for one cell only and then editing the appliesto ...
- 347
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
4
votes
1
answer
10k
views
Always open 'Blank Workbook' when clicking (File) > (New), and when Excel first opens
I use a lot of temporary workbooks, and when I open existing workbooks, I do it from my desktop or elsewhere. I don't ever want to see Excel's list of Featured or Personal templates that I have-never ...
- 616
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
4
votes
3
answers
3k
views
Calling a Sheet Sub from a Userform
I'm trying to build a macro that will read the dates from cells in my Worksheet and check them against a date (month and year) inputted into a Userform. To do this, I have the Userform call a Sub ...
- 53
4
votes
10
answers
78k
views
How to rectify Excel error "We couldn't get the data from table in the workbook..."
I am dealing with the following problem using MS Excel 2016:
The Excel file contains a huge data table and several pivot tables in separate sheets.
The current Excel file was saved under a new file ...
- 41
4
votes
1
answer
2k
views
Why is my Excel chart grouping all my x-axis time values at 0:00?
I am using Excel 2016 for Mac to produce a time-based chart to record two temperature. One column for the date and time, two more for temperature readings. However, Excel appears to be ignoring the ...
- 494
4
votes
1
answer
906
views
Excel treating text as numbers in a COUNTIF for duplication highlighting
I'm trying to highlight duplicates in a excel sheet which we have listing our network ports.
Even though the cells are formatted as text, when in the =COUNTIF(E:E,E4)>1 forumula they appear to be ...
- 6,773