Power User Setup
I installed Office on a friend’s computer the other day, and it got me thinking: What Excel setting do I change when I install? It’s too long in between installs for me to remember. Usually, I just start using Excel then run into the same problems that caused me to change the setting in the first place. I started a list, but couldn’t remember many of them. I guess it’s time for a hard drive reformat so I can relearn this kind of stuff.
Here’s the start of my list. Comment, if you will, on what settings you change right out of the gate:
- Tools - Options - General - uncheck Ignore other applications
- T - O - General - increase MRU to maximum of 9
- T - O - General - decrease Sheets in new workbook to minimum of 1
- T - O - General - change default file location
There are definitely other settings that I care about, but I can’t remember if the default happens to be the same as the way I like it.
Tools - Options, Edit tab. Turn off ‘Move selection after enter’
Tools - Options, View tab. Turn off ‘Startup Task pane’ and ‘Windows in Taskbar’
View - Toolbars - Customize, Options tab. Make sure ‘Always show full menus’ is checked (I think this is the default, now).
Tools - AutoCorrect, SmartTags tab. Uncheck ‘Label data with SmartTags’
Tools - AutoCorrect, AutoFormat tab. Turn off the automatic hyperlinks options (available only in XL 2003).
Tools - Options, General tab. Change user name to Dick Kusleika — just kidding.
Custom install, not Typical.
VBA help Yes.
Office Assistant Never.
Tools - Macros - Security. Set security level to Medium
From VB:
Tools | Options
Auto Syntax Check = Off
Require Variable Declaration = On
View | Immediate Window
In my previous job as a Desktop Engineer, capturing defaults for installation packaging was something I’d frequently do.
I mostly used an inhouse tool for install packaging. I’d build a baseline PC, install the product, hit snapshot, make setting changes, hit the show changes button. It would report all the files and registry changed - ie. the Options I have configured.
It would take some massaging to get rid of antivirus entries, windows startup positions etc… Allowed good control over how the app behaved after rollout.
There are free tools out there which do similar.
The freeware tool RegShot can take before and after snapshots of the registry and report differences.
Also Desktop Engineers Junk Drawer is worth a read.
Cheers,
Rob
I’m sitting here at home early Friday evening while my 7-year old watches “Hacker” upstairs (I told her it’s a stereotype) reading Chapter 4 of Professional Excel Development (what an excellent book!) checking on my Kryptonite replacement (it’s finally coming) and reading this post. The very first one, J-Walk’s “Turn off ‘Move selection after enter’” and I’m slapping my forehead. How many times have I hit Enter and then clicked back into the cell? I changed the setting immediately. Now if I can finish this book, “Code Complete” and “Excel 2000 Formulas,” and then practice for 5 more years, I think I’ll start to get this crazy thing called Excel.
Anyways, thanks for the tips, and as always, Dick, for the wonderful place you’ve made here.
Hi Doug
…reading Chapter 4 of Professional Excel Development (what an excellent book!)…
Thanks! When you’ve got through it, it’d be great if wrote a review at Amazon.com.
Regards
Stephen Bullen
Stephen,
I’ll do that.
I’m sure Stephen meant “…review at Amazon
Thanks Doug. And yes, Dick, I should have used a link!
Lol. That link says
“Better Together
Buy this book with Excel Hacks by Raina Hawley, David Hawley (Editor) today!”
Small world, isn’t it ?
< << the publisher (O’Reilly) pays Amazon a fee to have their book listed in a ‘better together’ deal with another book (ours) that they presumably think will sell well>>>
LOL! I don’t think so.
You don’t think what, Dave? That the books are ‘better together’, or that O’Reilly would pay for that? I took my information from the Small Vendor Co-Op Program paragraph at Amazon. I was surprised to see them listed together, as the books target a very different audience.
Regards
Stephen Bullen
No no, nothing like that. I just thought is was perhaps bravado, to write “…that they presumably think will sell well” when speaking of ones own book.
Anyway, best of luck with your book.
Dave
I have to have add a bunch of buttons:
pivot table
insert sheet
Macros
Paste Values
Paste Formats
Erase Contents and Formats
Clear Contents
Calculate Now
Close
Save As…
Print… (as opposed to “Print”)
Probably some others, along with a few that have been mentioned by others.
Change your default startup location (tools–>options–>general) to c:\documents and settings\jcooper\favorites.
From excel. File–>Open
Now browse to the same location above. From File–>Open window, click tools and add “Favorites” to “My Places”. Now you’ll have a ‘Favorites’ folder on the LEFT side of this window. (May be down at the bottom and you might have to scroll down. You can right click to move it up.)
Now, add shorcuts to your favorites menu (Windows Explorer is probably the easiest way). The short cuts should point to the folders you use most often.
Now, you will quickly be able to navigate. If you drill way down into some folder and want to quickly get to your budget folder, click on the favorites button on the left side of the file open window and your shortcut will be right in front of you.
If this isn’t clear, I appologize but it works really really well and saves time.
Three custom buttons I can’t live without:
I keep these routines in my Personal.xls and link to custom buttons on the toolbar.
This one toggles between R1C1 mode and A1 mode. Handy for seeing your columns as numbers instead of letters (for when you’re dealing with offsets).
Sub ToggleR1C1()
With Application
.ReferenceStyle = IIf(.ReferenceStyle = xlR1C1, xlA1, xlR1C1)
End With
End Sub
This is just a shortcut to the Define Name dialog, which intuitively (to me) just does not belong on the Insert menu. Also the define name button is not accessible when customizing toolbars, as far as I know.
Sub ShowNameDialog()
Application.Dialogs(xlDialogDefineName).Show
End Sub
Finally, I was always formatting areas by going to the Borders dropdown, selecting the clear all icon and then the one with a single width line around it. This clears out the interior lines and boxes the outside of the selection. So I put it in a macro instead and run it from the toolbar:
Sub BoxOut() ‘Outline selected cells, remove interior borders
Dim i As Integer, nLine As Integer
For i = 1 To 4
nLine = Choose(i, xlDiagonalDown, xlDiagonalUp, xlInsideVertical, xlInsideHorizontal)
Selection.Borders(nLine).LineStyle = xlNone
nLine = Choose(i, xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
With Selection.Borders(nLine)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next i
End Sub
Dave: The shortcut for Define Names is CTRL+F3.