×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

List your fave tips for Excel 2016, Please : - )
2

List your fave tips for Excel 2016, Please : - )

List your fave tips for Excel 2016, Please : - )

(OP)
I have been dragged kicking and screaming into using Excel 2016. "Upgrading" from my old Excel 2007 (which was itself another kicking and screaming story).

Okay, it seems to run all of my VBA and standard workbooks without too many hitches [okay, in hindsight that was TIP #3]. But I just don't like the interface. On the other hand, unlike Excel 2007 the "Office bar" can be modified easily by the user [that was TIP #1] (in 2007 the Office bar could be modified but required some very fancy legwork that I was never able to follow, at least in any decent amount of time; one of our programmers here did do so for our commercial software and he attempted to larn me on it).

I've mostly accommodated myself to the new GUI. But the one thing that I just couldn't get accustomed to (well, there is the fact that each workbook now opens in its own window, don't think I'll ever get used to that ... but I am able to use some existing VBA routines to set up a special view of 3 related workbooks, so I'm thankful for that), back to the point, I just can't get used to the abrupt screen change when selecting the File menu. Well, obviously, this is not something new to Excel 2016 and I'm certainly not the only person to be "shocked" by this, as this article indicates http://www.informit.com/articles/article.aspx?p=20.... Okay, and maybe that view has some utility with the Info that's provided.

But I really detested the abrupt change when all I needed to do was to create a new workbook. Even worse is that using the File menu I have to select a Template from which to start my new workbook. (Truth be told, I think even Excel 2007 did this as well but I must have put some tool buttons on the bars to immediately create a new file).

Well, the point of this post is that I am a happy camper now. I put the standard tool button into the Quick Access Toolbar to avoid the full screen File view. But this still made me select a Template. Inefficient use of my time! So then I created a VBA routine to directly create a new blank workbook and I put that in the QAT [that was TIP #2]. I'm very happy now.

What tips do you have? Please share, thanks!

RE: List your fave tips for Excel 2016, Please : - )

(OP)
[TIP #4] I have been using both Excel 2016 and Excel 2007 to access (and save) a workbook that I use daily. Depends on what computer I am using, and on one computer I have both versions. I have not had any problems with incompatibility or data loss by saving in either version and opening in the other. Probably not a good idea for long-term habit, but it eases my transition.

[TIP #5] Should you happen to have multiple versions of Excel installed it seems that they all use the same personal.xlsb macro file.

For that matter, I'm thinking that they use the same VBE, but I'd have to go back and double check that on the computer on which I have both versions installed.

RE: List your fave tips for Excel 2016, Please : - )

"well, there is the fact that each workbook now opens in its own window"

Hallelujah! That's of the best new features I've wanted FOREVER. One of the reasons I have two screens is to have documents side-by-side, and stretching a single window across two displays is just so noob.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

RE: List your fave tips for Excel 2016, Please : - )

Hi,

Regarding your Tip #2: There is a QAT Icon named New and one named New....

The New icon (ctr+N) creates a plain vanilla workbook.

The New... icon opens opens the File > New window, from which you can choose a template.

You might want to check out...

thread770-425996: Little-known Excel features and functionality

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: List your fave tips for Excel 2016, Please : - )

(OP)
IRStuff, As I recall, one of the features of Excel 2007 (or earlier) was showing each workbook in a new window, with a new button on the taskbar. I didn't like this myself and as I recall (it's been so long since I set this) there is a setting too control this.

RE: List your fave tips for Excel 2016, Please : - )

IRstuff,
I am with you on that one! I HATED having to have two excel workbooks in the same parent window. I would go as far as opening a new instance of excel just to have them separate, but then as I'm sure you know, you could not copy and paste formulas between the two instances. Office 2013/2016 are much better in that respect.

RE: List your fave tips for Excel 2016, Please : - )

(OP)
Skip, that's interesting. I was looking under the filtered "File Tab" selection and only the one option is available. The option for a plain new workbook is available under the "All Commands" selection.

RE: List your fave tips for Excel 2016, Please : - )

filtered "File Tab" selection

???

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: List your fave tips for Excel 2016, Please : - )

(OP)
Skip, that would be: Customize QAT -> More Commands (the "Excel Options" dialog opens) -> from the "Choose commands from" selection list -> select the "File Tab" to get a filtered list

RE: List your fave tips for Excel 2016, Please : - )

"But this still made me select a Template. Inefficient use of my time! So then I created a VBA routine to directly create a new blank workbook and I put that in the QAT [that was TIP #2]"

You must have had something wonky in the setup. The default "New" button on the QAT is a standard blank worksheet.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

RE: List your fave tips for Excel 2016, Please : - )

Got it. I’ve been accustomed to making my QAT selections under All Commands.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: List your fave tips for Excel 2016, Please : - )

BTW, did you notice that the Customize QAT dialog box has the "Customize Ribbon" on the left hand side? You can add new tabs or new commands to existing tabs

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

RE: List your fave tips for Excel 2016, Please : - )

(OP)
"BTW, did you notice that the Customize QAT dialog box has the "Customize Ribbon" on the left hand side? You can add new tabs or new commands to existing tabs "

yes, that is a great new feature versus Excel 2007. Maybe it was in intermediate versions but new to me.

RE: List your fave tips for Excel 2016, Please : - )

And all these customizations for QAT can be applied to All workbooks or specific workbook(s).

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: List your fave tips for Excel 2016, Please : - )

I've recently "discovered" flash-fill, offering UDF-free, quick and easy extraction of numbers from text:

More here: Using Flash Fill

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: List your fave tips for Excel 2016, Please : - )

IDS,
Thanks for that tip. I hadn't seen Flash Fill or the ExtractNums() function before. Those may come in handy.

RE: List your fave tips for Excel 2016, Please : - )

forget QAT, create your own ribbon tab with all your custom tools (macros) & standard excel tools from other tabs all in one place. You'll never go back, and if you do it right you'll spend much less time in other tabs....

It now drives me mad when I have to deal with other spreadsheets that aren't written to take advantage of this.

Its made me a lot more efficient at developing spreadsheets!



RE: List your fave tips for Excel 2016, Please : - )

(OP)
"forget QAT, create your own ribbon tab with all your custom tools (macros) & standard excel tools from other tabs all in one place. You'll never go back, and if you do it right you'll spend much less time in other tabs.... "

Thanks! I'll check that out. Oh, I guess I have already. I saw the "new" (to me) option to do this customization. This sort of thing was taken away in Excel 2007, prior to that I used custom menus, which was great. This ability is indeed wonderful. Thanks for pointing this out!

RE: List your fave tips for Excel 2016, Please : - )

You can create miniature trendlines in Excel 2013 using their "Sparklines" feature.

RE: List your fave tips for Excel 2016, Please : - )

The latest update for Excel has subscript and superscript buttons for the quick access toolbar and ribbon.

RE: List your fave tips for Excel 2016, Please : - )

Quote (LRJ)

The latest update for Excel has subscript and superscript buttons for the quick access toolbar and ribbon.

About bloody time!

Thanks for that. I hadn't noticed.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: List your fave tips for Excel 2016, Please : - )

... Only took 20 years...?

RE: List your fave tips for Excel 2016, Please : - )

Could you? I never noticed it until the latest update to my Excel 2016 version when it was noted as a new feature in a pop-up.

Whatever the timing, I'm glad it's possible at last.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close