Software

A few ways to customize Excel to increase your productivity

Working with Excel's out-of-the-box defaults can waste your time. Change default settings to work more productively and efficiently.

Customizing a new install of Excel can help you work more efficiently from the get-go. How you customize Excel depends on you, but here are a few tweaks that most users will benefit from.

Number of sheets

By default, Excel launches a new workbook with three sheets. There's nothing wrong with that number, but if you routinely work with more or less, change this default, as follows:

  • 2010: Click the File tab and choose Options. Select General on the left. In the When Creating New Workbooks section, choose the appropriate number from the Include This Many Sheets control. Click OK.
  • 2007: Click the Office button and then choose Excel Options. Select Popular in the left pane. In the When Creating New Workbooks section, choose the appropriate number from the Include This Many Sheets control. Click OK.
  • 2003: Choose Options from the Tools menu. Click the General tab and set the number of sheets in the Sheets In New Workbook control. Click OK.

Default file location

Many users don't use the default file folder, My Documents, and they spend a lot of time browsing folders looking for files. Using the instructions above, you can control where Excel saves your workbooks. You can still change that location on the fly, but having Excel default to your most commonly used folder will save a lot of time.

User name

Many IS departments install Excel without customizing it at all. One of the first things you'll want to check is the user name and reset it if necessary (use instructions above to access this property). If everyone's using the same generic user name there's no way to claim ownership. Changing this property in Excel will change it in all of your Office apps.

Display the Developer tab

If you're using Excel 2007 or 2010, you'll probably want to display the Developer tab, which Excel hides by default. You don't have to be a developer to want quick access to recording macros or inserting form controls. To enable this tab, do the following:

  • 2010: From the Quick Access Toolbar dropdown, choose More Commands. In the left pane, choose Customize Ribbon. In the Customize The Ribbon list (to the right), check Developer. Click OK.
  • 2007: Click the Office button and then click Excel Options. Choose Popular in the left pane. In the Top Options For Working With Excel section, check the Show Developer Tab In The Ribbon option.

File format

If you're sharing 2007 or 2010 files with pre-ribbon versions, you might want to save your workbooks in the pre-ribbon format. There are other options, but if you're not making use of features specific to the new ribbon features, doing so might save your co-workers a bit of aggravation. To save files in another format, do the following:

  • 2010: Click the File tab and choose Options. Select Save in the left pane. In the Save Workbooks section, choose Excel 97 - 2003 Workbook (.xls). Click OK.
  • 2007: Click the Office button and then click Excel Options. Select Save in the left pane. In the Save Workbooks section, choose Excel 97 - 2003 Workbook (.xls). Click OK.

AutoCorrect options

These default settings try to help users by correcting entries that Excel finds inappropriate. Unfortunately, these settings probably annoy more users than any others. There are too many to review individually, but knowing you can disable them is a start. To change an AutoCorrect setting, do the following:

  • 2010 and 2007: Click the File tab and choose Options. Then, select Proofing in the left pane. Click the AutoCorrect Options button in the AutoCorrect Options section. Click tabs to review the different options.
  • 2003: Choose AutoCorrect Options from the Tools menu.

What Excel settings have you customized and why?

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

14 comments
srodgers1711
srodgers1711

Office efficiency is very important to me for my business. I always go by the old saying work smarter not harder. I recently got new business telephone systems for my office and they have been great. You wouldn't think that phones could increase productivity so greatly. Thanks for posting.

wehmeier
wehmeier

I have been trying to figure out how to change the default settings on pivot tables, to no avail. I was hoping that might be one of the topics in this article. Anyone have suggestions? Specifically, I want to be able to change Pivot Table Options > Layout & Format > Autofit column widths on update to unchecked, and in Totals & Filters > Grand Totals unchecked.

DBlayney
DBlayney

Startup folder: Contains workbook and sheet templates formatted for printing; basic custom styles. Also my Personal macro workbook with lots of useful stuff in it. This also avoids the need to set the number of sheets ina new workbook (who needs two enopty sheets every time?) Quick access toolbar: I need at least a 22" screen to see mine. before Excel 2007 I had three custom toolbars (top, left and bottom), I would to eb able to do that again. As many recently-used files as possible. I also use the "Quickly access this number of Recent Workbooks" option (at the bottom of the Recent Workbooks page - first in 2010?) Custom lists: One of my favourites is "High, Medium; Low" for sorting prioritised lists Default file location:

ssharkins
ssharkins

I change the Paste Values default -- that thing drives me nuts if I don't! ;)

jody.burton
jody.burton

At work, I keep the default "after Enter, move selection down." But at home, personal spreadsheets are entered left-to-right, so I set the action to "move selection right." I also add "Paste as Values" to the Quick Access Toolbar" for one-click access, since I use it frequently.

dogknees
dogknees

I work in a firm that has a number of offices around the country. As I'm often preparing reports or whatever on an Office basis, I'm forever inserting the list of office names. Instead, use a "Custom List" to get Excel to help. As you probably know, if you enter Jan in a cell then use the fill handle to expand the selection, Excel fills in the month names in order. This is a Custom List. If you also work with "standard" lists that you are always re-entering or copying from somewhere, create Custom Lists for your data. In Excel 2010 Enter the items you want in your list in a column on a worksheet. Then go to File/Options and then to the Advanced section. Scroll to the General area and click the "Edit Custom Lists..." button. In the dialog that comes up, click the "select arrow" next to the Import button, select your list of information, and then click Import. Now, if you type an item in your list in a cell and do a fill, you'll get your Custom List of items. Hope that someone finds this useful. Regards

sawriters
sawriters

I use a Custom VBA script found online to get rid of Blank Sheets before I send or upload a Excel workbook. I've used it ever since Office 2007 came out http://www.vbaexpress.com/kb/getarticle.php?kb_id=396 It's one of the first Macros that I put in place on a New Excel install, on the Quick Access toolbar right next to the Undo button.

mountjl
mountjl

In fairness, if you're working in an environment where the IT Team did not customise the Office install, you're probably in an environment where the Owner of a file can be easily determined my NTFS attributes. And if things are desperate enough to be scrapping between colleagues as to who created document X, you may want to reconsider where you're working. ; )

Mark W. Kaelin
Mark W. Kaelin

I have been a heavy user of Excel (and Lotus 123 before that) for many years and I the first thing I do with each new install is customize it the way I like it. Which default Excel settings do you change?

jody.burton
jody.burton

Those options are not available until you create a Pivot Table. They only show up on the contextual tab that appears when a Pivot Table is selected, not in the general options. They could probably be changed programatically with VBA after the fact.

Marshwiggle
Marshwiggle

... it has to be executed, as you said, each time you send or upload a workbook. Not a big deal, but by setting the defaults as Susan indicated, which you can do just one time, you wouldn't have any blank sheets that needed to be deleted.

ssharkins
ssharkins

Why make it so hard? I think it's find to not customize, but there are good reasons to do so. First, there's consistency -- everyone on the same page, which is important to some orgs. Second, efficiency. If the issues are known, it's no big deal. It's not a huge issue, it's just one of the things that can be done to make things run smoother.

M in the beach
M in the beach

Great post - I do many of these each install. Especially number of tabs - you should never send a document with unused tabs, since it prompts people to check to make sure there isn't anything important on the other tabs. "Show this number of recent documents" (Advanced Option) is too low by default, I like to set it to the max. I also add "Format Painter" and "Send by Email" to the Quick Access Toolbar - these are some of the most common functions I'm too impatient to hunt for.

sawriters
sawriters

However, I work in an environment which require me to receive, edit and return/upload a lot of users' Excel workbooks. My personal settings is set a default of 2 sheets but I use the Macro to clean up the User workbooks I receive before returning them.