Setting #1: What Happens On ENTER
We’ve all seen that when entering text, values, or formulas in an Excel cell, pressing the enter key provides three actions:
- We exit the editing mode; the program features once again become available.
- We commit the entered data to the cell.
- The cursor (green box) moves to the next row in the same column.
It’s this last behavior that many users change in the program settings.
This is beneficial if you are entering data in a column-wise fashion, but what if you enter data in a row-wise fashion? This would incur the extra step of moving the cursor back to the original row and over to the next column.
Other users prefer to remain in the cell being edited so they can perform operations like filling a formula down a column or formatting the entered text.
TIP: To remain in the current cell, you can press CTRL-ENTER instead of ENTER.
If you prefer moving down, right, up, left, (these last 2 are good for pranking your co-worker) or even remaining in position, you can change this behavior by performing the following steps:
- Click File (tab)
- Click Options (bottom-left of the window)
- Click the Advance category (on the left side of the Excel Options window)
- In the section labeled Editing Options, change the dropdown for “After pressing Enter, move selection”.
If you don’t want the cursor to move, rather remain in the current cell, uncheck the feature.
Featured Course
Excel Essentials for the Real World
Setting #2: Long Phrases Shortcut
If you find yourself typing the same phrases or terms, and some of these may be quite long, like a company name:
ACME Sprockets and Widgets, Inc.
Think of how much time added up over a year that you would spend typing this company name potentially thousands of times. And better yet, think of the number of instances where you make a small, unseen typo.
Wouldn’t it be nice to type something like “ASW”, press enter, and the computer changes that to “ACME Sprockets and Widgets, Inc.”?
Think of all the abbreviations you could type that could be automatically converted to the full-length version.
(See the end of this post for a recitation on the difference between abbreviations, initialisms, and acronyms. It’s more interesting than you may think.)
This automatic conversion is handled by the same feature that corrects many of the more common spelling errors, like converting “teh” to “the”: the AutoCorrect feature.
To add an abbreviation and its corresponding full form, perform the following steps:
- Click File (tab).
- Click Options (bottom-left of the window)
- Click the Proofing category (on the left side of the Excel Options window)
- In the section labeled AutoCorrect Options, click the button labeled “AutoCorrect Options…”.
- On the AutoCorrect tab, enter the abbreviation for the phrase in the “Replace:” field, and the full version of the phrase in the “With:” field.
- Click Add to add the entry to the AutoCorrect library.
Test your abbreviation by selecting a cell and typing the short form version. Pressing ENTER (or Space) should reveal the long-form version.
PRO TIP: If you have a long phrase where sometimes you wish to use the shorter, abbreviated version (like “MSDN”) but other times you want the fleshed-out version (like “Microsoft Developer Network”), enter the shortcut in the AutoCorrect library with a small modifier, like an “x” on the end (ex: “msdnx”).
This way, if you want the abbreviation, you will type “MSDN” as you normally would, but when you want the long version, you will type “msdnx”.
Typing this additional character (or not) allows you to quickly choose between the two variants.
I like to think of the “x” as standing for “eXpanded”. In other words, “Give me the expanded version of ‘bla-bla’”.
Setting #3: Create Custom Lists
If you are a fan of the Fill Series tool (the little handle in the bottom-right of the green cursor), you’ve no doubt discovered many ways to save typing time using this tool.
The ability to type a month name, a day name, dates, quarters, dates, numbers, etc.
But what about lists that are specific to you? Lists that you type day after day, week after week.
For example, a list of departments.
Wouldn’t it be nice to be able to type “Admin”, pull the Fill Series handle down, and be presented with the other departments?
To create a Custom List, take the following steps:
- Type your list into your spreadsheet. TIP: The order that you enter your list here is the order it will be presented when using the Fill Series feature. Consider sorting your list alphabetically, numerically, or chronologically.
- Highlight the list and click the File (tab).
- Click Options (bottom-left of the window)
- Click the Advanced category (on the left side of the Excel Options window)
- In the section labeled General, click the button labeled “Edit Custom Lists…”.
- On the Custom Lists dialog box, the highlighted range of cells should be occupying the “Import list from cells:” field. Click the Import button to add the list to the Custom Lists feature library.
NOTE: If you had not created the list earlier in Step 1, you could have selected NEW LIST and entered the list of departments manually in the List Entries field, then click Add.
Test the feature by selecting an empty cell and typing any of the department names. Use the Fill Series handle to create the remainder of the list.
This newly added custom list is not limited to the file you are working on. This new list can be used in any new or existing Excel file on this computer.
The custom list is restricted to the computer you created it on. The custom list does not travel with files you send or share with others.
Setting #4: Pivot Table – Set Your Preferred Layout
For users who have been working with Pivot Tables regularly, there are likely certain customizations that always seem to be made to get the “standard” look that a user prefers.
This may include changes to Pivot Table options like:
- Presence/position of Subtotals
- Presence/position of Grand Totals
- Report Layout (Compact, Outline, or Tabular form)
- Blank Lines between hierarchy levels
- Repeated item labels
- Error message customization
- Empty cell customization
These changes are not difficult to make, but they do take time and can lead to inconsistent table styles if performed in a hurry and certain changes are missed.
Luckily, Excel allows the default settings for these and other Pivot Table customizations to be made at a program level. This way, newly created Pivot Tables start with the desired settings and configurations.
To customize the default Pivot Table appearance, perform the following steps:
- Click File (tab).
- Click Options (bottom-left of the window)
- Click the Data category (on the left side of the Excel Options window)
- In the section labeled Data Options, click the button labeled “Edit Default Layout…”.
- The Edit Default Layout dialog box provides the previously mentioned Pivot Table adjustments that can be set to deliver the best “right from the start” experience.
Now, instead of starting a pivot table like this…
You can start the same pivot table like this.
You can’t customize every Pivot Table option, but you can eliminate a sizable number of steps when it comes to repetitive feature changes.
Pro Tip: If you have an existing Pivot Table with all the desired cosmetic changes in place, you can import those settings by performing the following steps:
- Click anywhere in the existing Pivot Table.
- Open the “Edit Default Layout…” dialog box.
- Click the Import
Setting Default Pivot Table Colors
If you have a color scheme from the PivotTable Styles gallery that you prefer, you can make that color scheme the default by right-clicking the color scheme option and selecting “Set as Default”.
Featured Course
Power Excel Bundle
Bonus Setting #1: Change Startup Settings
Are you old enough to remember when starting Excel placed you immediately in a blank workbook; when Word placed you in a blank document, and PowerPoint placed you on a blank slide?
These days, Office applications start you in what’s called the Getting Started panel.
This panel is a collection of destinations that allow you to decide which direction you wish to travel. You can choose from some of these popular actions:
- Start a new blank file.
- Open a file you have previously worked with.
- Start a new file from a template.
- Open an existing file you have not opened before.
- Check your version of Microsoft Office
- Examine the status of your computer account.
- Provide feedback to Microsoft on all the amazing features that you can’t live without (or perhaps a few that you can.)
- Change the program options (the focus of this post.)
The Getting Started panel is loved by many. But what if you prefer “the way it used to be” when the program just opened in that blank document?
You can deactivate the automatic launching of the Getting Started panel by performing the following steps:
- Click File (tab)
- Click Options (bottom-left of the window)
- Click the General category (on the left side of the Excel Options window)
- In the section labeled Startup Options (at the very bottom), uncheck the option labeled “Show the Start screen when this application starts”.
If you need to return the Getting Started panel to its former glory, just repeat the same steps above, rechecking the box for this option.
Bonus Setting #2: Preserve Leading Zeroes
When typing numbers that have leading zeroes, and those zeroes need to be preserved, it can prove frustrating to Excel users. Pressing ENTER tells Excel to throw those zeroes out into the ether.
The reason for this is that Excel doesn’t like to display insignificant zeroes. Those leading zeroes don’t add anything to the value of the number.
Or do they?
What if you have numbers with leading zeroes in situations like:
- ZIP/Postal codes
- Part numbers
- Employee numbers
- Credit card numbers
- Phone numbers
The list of scenarios is limitless.
The old way to deal with preventing the loss of those leading zeroes was to either enter the number as text (prefacing the number with a single quote) or convert the needed cells to text cells before entering the numbers.
Now there’s a better way!
Excel is testing a new feature that is currently only available to Beta Channel users (Office Insiders) but will hopefully be rolled out to all users very soon.
In the Advanced section of Excel Options, there’s a new category called Automatic Data Conversion (BETA).
Here we can change some of the default ways that Excel deals with data conversion.
Unchecking the option labeled “Remove leading zeroes to convert to a number” will solve our current dilemma.
Another useful new feature applies to the opening of delimited text files, like .CSV or .TXT files.
If the file contains numbers with leading zeroes, Excel will present a notification that allows you to decide whether to keep or remove the leading zeroes.
This feature will no doubt make many Excel users very happy.
BONUS CONVERSATION
Abbreviations versus Acronyms
If you have ever wondered what the difference between an abbreviation and an acronym is; today you’re about to find out.
Many people use these words interchangeably, not realizing that they are very different things. Let’s lift the veil of mystery.
Abbreviations
An abbreviation is where a word is shortened, like using “Corp.” instead of “Corporation“, or “St.” instead of “Street“.
Acronyms
An acronym is formed by taking the first letter (typically) of each word in a set of words to produce a new, pronounceable word. An example of this would be taking “National Aeronautics and Space Administration” and forming the word “NASA“. The catch is that the new word must be pronounceable as if it were itself a word.
Initialisms
But what about “CIA” for “Central Intelligence Agency“?
Many people refer to this as an abbreviation, but it is closer in for to an acronym. The issue is that you don’t pronounce C-I-A as a word (like, “see-eye-ah”). Each letter is pronounced. This is what is known as an initialism.
Now that you know the difference, go forth and impress your computer geek friends with your vast English knowledge.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
