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.

Filter by
Sorted by
Tagged with
52 votes
2 answers
9k 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
31 votes
2 answers
13k 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 ...
  • 421
28 votes
7 answers
13k 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 ...
19 votes
3 answers
80k 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 ...
18 votes
2 answers
16k 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?
's user avatar
12 votes
3 answers
210k 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 ...
  • 223
12 votes
3 answers
17k 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
111k 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,944
11 votes
3 answers
89k 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 ...
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 ...
8 votes
2 answers
12k 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
6k 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
1 answer
1k 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 ...
  • 339
7 votes
3 answers
639 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-?????-...
  • 105
7 votes
4 answers
4k 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 ...
  • 614
7 votes
3 answers
33k 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 ...
  • 417
7 votes
2 answers
10k 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
6 answers
37k 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,292
7 votes
1 answer
5k 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
31k 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
6k 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 ...
  • 215
6 votes
1 answer
13k 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 ...
6 votes
7 answers
14k 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 ...
  • 235
6 votes
3 answers
7k 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
2 answers
6k 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 ...
  • 163
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
3 answers
1k 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....
  • 105
5 votes
3 answers
3k 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
25k 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 ...
  • 188
5 votes
4 answers
4k 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 ...
  • 65
5 votes
3 answers
74k 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 ...
5 votes
2 answers
4k 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 ...
  • 379
5 votes
1 answer
256 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,393
4 votes
9 answers
72k 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
2k 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
14k 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 ...
  • 413
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.
4 votes
3 answers
2k 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
2 answers
5k 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 ...
  • 53
4 votes
1 answer
9k 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 ...
4 votes
2 answers
19k 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 ...
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 ...
  • 474
4 votes
1 answer
805 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 ...
4 votes
1 answer
3k views

Custom cell format for IP address to force leading zeros?

I have a column of IP addresses I want to sort. I don't want to use hidden columns like this answer. What I would like to do is simply format the cells so that they force leading zeros. For instance,...
  • 1,918
4 votes
2 answers
8k views

How to import CSV into Excel 2016

Each new version of Excel constantly becomes worse. In earlier versions of Excel I was just able to open CSV file and work with it. I was unable to this for Excel 2010: How to control CSV import ...
  • 11.4k
4 votes
2 answers
648 views

Identifying different permutations of some field in Excel holding others constant

In Excel 2016, I have a worksheet with five columns of headings (A through E) and many rows of data. I'm trying to find entries that have the same data for columns A through D and to identify what the ...
4 votes
1 answer
4k views

How can I dynamic change separators configuration in Excel 2016 for CSV files?

I've a CSV file opened with Excel. This file use comma separators (,) but the default separator for Excel (with European configuration) is a semicolon (;). Note: I know in America they use a comma ...
4 votes
2 answers
12k 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
4 votes
2 answers
7k views

How to create an Excel chart with no numerical labels?

I'm using Excel 2016 and I'm trying to create a graph that shows a list of skills against the skill level. I'm fine with the skill level being represented as a numeric value however I'd like the ...
  • 243

1
2 3 4 5
28