EzDevInfo.com

Show MS Excel Syntax as you type Formula

I have Office 2013 on my work computer and Office 2016 on my personal computer. On my work computer in Excel 2013 when you type a formula you get the suggested syntax for formulas as you type, but on my personal computer I am not getting this. On my work computer, for example, if I type the SUM formula I will see the suggestion:

SUM(number1, [number2], ...)

but I won't get this syntax suggestion for my personal computer (all I get is SUM)

Is this just the version of Excel that I have on the different computers or is there a setting that I can turn on to show syntax suggestions?

Thanks for the help.

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 working on a large spreadsheet and try scrolling up/down, Excel will snap you back. To say it's infuriating is an understatement - I'm close to downgrading.

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?

Plot an Excel Chart where the date is on the x-axis and the different times as the Two Series

I have data like this in a table:

Date    Time        Min RTT
3/28/16 10:00 AM    42.91
3/28/16 10:00 PM    41.81
3/29/16 10:00 AM    70.61
3/29/16 10:00 PM    42.81

I would like to plot it where the dates are on the x-Axis and the two different series are the 10 am and the 10 pm values. The Y-Values are the Min-RTT.

I have been trying for hours and just can't get it to work the right way.

Could someone please point me in the right direction. Thank You!

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:

1. A2 value begins with 4 OR 9
2. A2 value length is 8

Otherwise output "No".

This is the formula I've come up with:

=IF(AND(OR(LEFT(A2;1)="4";LEFT(A2;1)="9");LEN(A2)="8");"Yes";"No")

Problem is, it just doens't work :(

Any ideas?

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 labels to be textual when it comes to displaying the chart.

Skill level chart:

I'd like the numbers to represent:

1 = Beginner
2 = Novice
3 = Intermediate
4 = Proficient
5 = Advanced 
6 = Expert

And I'd like those labels to be displayed on the x-axis instead of the numerical values, like this:

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 must click a second time on the cell to cause Excel to move to the cell.

This seems to be a special case of a more general behavior of Excel. Excel ignores mouse clicks on inactive windows. For example, clicking a ribbon button does nothing (except the window becomes active).

Other Windows applications have the opposite behavior. They do not ignore clicks on inactive windows. The click activates the window but also does whatever a click normally would do. Clicking in an inactive Notepad window in the main text buffer activates the window but also moves the cursor to the clickpoint, clicking a menu button activates the menu, and so on.

I have looked through the choices available under File ... Options, and tried several Google searches for the answer. I did not find any relevant information.

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 use "ALT-A-T" to create filters, the workbook takes 3-5 seconds to process. Alternatively, when I simply press the button in the ribbon, the workbook responds instantly.

I already turned off cursor animations in the OPTIONS > ADVANCED > GENERAL settings. Is there any other way to turn off whatever extraneous activity is happening in the background?

Thanks! Lisa

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')

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 marquee border) and then copy and paste the data from an external application.

In Excel 2013, I did not have to cancel the marching ants. Copying data from an external application would cancel the marching ants automatically and allow me to paste the data into Excel.

Having to press Esc every time I need to paste is very annoying. How do I disable this, or how do I restore the behavior of older Excel versions?

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 fields containing unicode characters (™, ®, °, so on and so forth) into a MariaDB table.

We initially tried just exporting it into a .csv file and putting it in the table but it seems to have been unable to use the special characters and it's actually cut off the rest of the data that was in the same cell(s) which obviously isn't ideal. When I opened the .csv file in a text editor (Sublime Text 3), it had all the special characters displayed as question marks in diamonds so we took that to mean that there was some kind of conflict between the formats.

If someone could advise me on how to export to a UTF-8 or UNICODE .csv file so we can preserve the characters, that'd be great.

Thanks in advance.

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 I want to change the default date format of Excel to YYYY-MM-DD so that if I open an Excel sheet or a CSV file with dates, it displays this format in the column. (In the case of a CSV file, if the format is already YYYY-MM-DD, I don't want it switch to the default format).

Alternatively, how can have this format show up as an option under Format Column -> Date -> US Dates? I end up having to switch my country to Belgium before I can find this option.

(<rant>@Microsoft -- I'm pretty sure in 2016, people in the US use the YYYY-MM-DD format; at least make it an option!</rant>)

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 commands, or edit existing shortcuts.

I have googled for this and searched the support site for this and haven't found anything yet.

What surprises me most is that this feature would exist in Word (see here) and not in Excel.

Has anyone found a solution or workaround?

Excel 2016 Freezing when entering data into a spreadsheet with multiple formulas

When working in spreadsheets with multiple formulas, if users enter data into a cell then leave the cell, Excel freezes for a few seconds to a couple minutes. The time varies depending on which spreadsheet they are working in. What I have found using resource monitor is that during these freezes the CPU is hitting a hard usage cap of 25%. On our high powered workbooks it is using 100% of two of the eight cores, but on my laptop which is less robust, it is spreading out over all four cores but still locking up at 25%. According to the MSDN forum on Excel it is normal for some user defined formulas for all versions of Excel to cap out at 25%, however I have tested it in Excel 2010 and the process is instant and there is barely a blip in CPU usage on the same model of laptop with same RAM configuration.

I have checked the CPU allocation for formulas with Excel and it is set to use all available CPU power.

If you turn off automatic calculations the problem persists.

I tried copying a few lines of one worksheet into a new workbook and it only froze for about a tenth of a second, but as soon as I added the rest of one work sheet the time of the freeze increased to about half a second. The more data added the longer the freezes become at a surprisingly rapid rate. The half a second freeze was in a single worksheet that only had 50 lines, each line had formulas in five or six columns.

I have tried disabling links to other workbooks, and isolating this one but that has no effect on the duration of the freezing.

I can understand excel limiting itself to 25% of the CPU but what I can’t understand is why this process is using that 25% of the CPU when in 2010 the exact same spreadsheet caused no discernible CPU usage whatsoever.

What appears to be happening is that every time data is entered in a cell, Excel is recalculating every single formula in the entire workbook. That may be normal behavior or it may not be. According to a few forums I have visited in researching this, that is the way Excel works, and always has. What is clear though is that something has changed. If it recalculated every formula every single time in 2010 it did so with efficiency, whereas in 2016 it is hanging the whole application. I have found other reports of this happening in 2013 as well but in none of those instances has anyone suggested a solution that I have not already tried.

Any advice that any of you might have would be appreciated as the finance users have several spreadsheets of this nature which require hundreds of entries each month. They cannot function efficiently or with any degree of sanity if they have to wait for every single entry they make to process. Users have already stated that if we can’t find a solution they will need to be rolled back to Office 2010 as this makes impossible to do their job in a timely fashion.

Here you can find a sample line showing the formulas in one of the documents I have hidden columns in which there are no formulas or data. You can click on the image to expand it and make it readable.

How do I fix a long delay in Excel 2016?

I just bought a new computer (Lenovo ThinkPad E560 with Solid State Drive and Intel Core i5 processor) with Windows 7 and the local version of Office 2016 (NOT Office 365). I have noticed when using Excel 2016 that there is sometimes a long delay when using the buttons in the top ribbon to paste values or to change the fill color of a cell. Oddly, the delay does not happen when I do a regular paste and then use the drop-down menu that appears in the file immediately after the paste to select paste values--it is only when I use the paste values command underneath the paste button in the top ribbon. The delay varies in length, but sometimes seems as long as 30 seconds or more. During this time, Excel essentially freezes up and I am unable to do anything with any open Excel window. The delay eventually clears, the operation (whether pasting or changing cell fill color) completes, and I am able to use Excel normally again. At first I thought it had to do with the number of cells being affected, but this has happened with large and small cell ranges alike, so I don't think that is the issue. The problem is intermittent. Has anyone else seen something like this? Do I need to reinstall Office?