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
2answers
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 ...
30
votes
2answers
12k 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 ...
28
votes
7answers
12k 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 ...
18
votes
2answers
15k 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?
17
votes
3answers
70k 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 ...
12
votes
3answers
16k 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 ...
11
votes
3answers
190k 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 ...
10
votes
7answers
100k 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 ...
9
votes
3answers
11k 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
2answers
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 ...
8
votes
2answers
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'...
7
votes
3answers
569 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-?????-...
7
votes
4answers
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 ...
7
votes
2answers
8k 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,...
7
votes
3answers
58k 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 ...
7
votes
2answers
23k 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 ...
7
votes
5answers
26k 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 ...
7
votes
1answer
4k 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
1answer
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 ...
6
votes
2answers
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 ...
6
votes
5answers
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 ...
5
votes
3answers
22k 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 ...
5
votes
3answers
2k 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 ...
5
votes
1answer
12k 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 ...
5
votes
2answers
23k 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 ...
5
votes
4answers
3k 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 ...
5
votes
1answer
218 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 ...
5
votes
0answers
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 ...
5
votes
2answers
5k 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 ...
4
votes
1answer
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 ...
4
votes
9answers
55k 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 ...
4
votes
1answer
20k 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
2answers
11k 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 ...
4
votes
3answers
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 ...
4
votes
2answers
4k 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 ...
4
votes
1answer
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
1answer
727 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
1answer
2k 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,...
4
votes
2answers
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 ...
4
votes
2answers
4k 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 ...
4
votes
2answers
535 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
1answer
3k 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
2answers
11k 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 ...
4
votes
2answers
5k 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 ...
4
votes
1answer
80 views

Force Excel to re-parse a whole column of dates

I have a series of dates in a column, currently recognized as text. Thus sorting doesn't work correctly. When highlighting the column and changing the format to Date, Excel still recognizes it as ...
4
votes
1answer
97 views

Summing array in Excel

I have four tables. The first one shows amount of ingredient each menu uses. Menu_Ingredient menu | ingredient | amount --------+------------+-------- Foo | A | 0.1 Foo | B ...
4
votes
2answers
3k 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 ...
4
votes
1answer
7k views

How to apply the same conditional formatting to other cells?

My spreadsheet has multiple rows. Each row has a checkbox. If a checkbox is ticked, conditional formatting will highlight that row. I am curious to know if there's a way to apply the conditional ...
4
votes
1answer
243 views

Sort an Excel range that is greater than an Excel table

I have 2 parallel (side by side) same-height Excel tables with a completely blank column (no data, nor header) in between them. The rows of a table correspond to the same rows of the other table (so ...
4
votes
4answers
8k 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 ...

1
2 3 4 5
26