Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in

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

Status
Not open for further replies.
TomBarsh

TomBarsh

Structural
Jun 20, 2002
1,003
0
36
CA
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 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!
 
Replies continue below

Recommended for you

Sort by date Sort by votes
[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.
 
Upvote 0 Downvote
"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! faq731-376 forum1529 Entire Forum list
 
Upvote 0 Downvote
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



Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
"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! faq731-376 forum1529 Entire Forum list
 
Upvote 0 Downvote
"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.
 
Upvote 0 Downvote
And all these customizations for QAT can be applied to All workbooks or specific workbook(s).

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
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!

menu_tqydo0.png
 
Upvote 0 Downvote
"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!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BhagyeshVashi
  • Locked
  • Question
Need a rename macro for excel
Replies
11
Views
20
sdz
sdz
Kenja824_home
  • Locked
  • Question
Excel - Looking for a formula please 2
Replies
14
Views
23
BridgeSmith
BridgeSmith
Kenja824
  • Locked
  • Question
Im looking for some formula help please --- Again. lol
2
Replies
37
Views
38
Kenja824
Kenja824
Kenja824
  • Locked
  • Question
Formula help please
Replies
11
Views
20
SkipVought
SkipVought
vinodnx7
  • Locked
  • Question
Micro to compare two excel file for mirror change
Replies
4
Views
11
vinodnx7
vinodnx7
Back
Top