Supercharge Your Spreadsheets: How to Customize Excel

Overview

Customizing Excel is a great way to eliminate a lot of repetitive steps you’re likely taking in almost every one of your workbooks.  Setting defaults is a fundamental way to apply an efficiency mindset where you think once, apply often, and refine when necessary.  In this article, we’ll discuss several great ways to customize Excel to save time, reduce frustration, and make Excel work better for you.

Settings to consider updating

The areas below are worth exploring under the File ribbon tab – options menu 🖼️.

General tab

• Font – set the font type and the font size to what you use most as your default.
• # of sheets – set the default number of sheets in each new file.  I personally use just one sheet because you can always add additional ones.  It’s a habit from my audit days because we had to review all of them!
• User name – the default user name may not be what you prefer, so you can change it here.  Some people like to use their initials instead of their full name.
• Office theme – you may want to change the standard “colorful” office theme if there’s another theme that better fits your personality or is easier on your eyes.
• Start screen – do you find the launch screen annoying and want to get right into a blank workbook?  Then turn this setting off to skip it!

Formulas tab

• Error checking – those green triangles can be quite helpful for finding errors and anomalies, but they can also be distracting and annoying. There are a number of different error types you can turn on and off based on your preferences. And you can even change the color from green if you’d like, though that might confuse others when they see you working.

Data tab

• Edit Default Layout – use this to set defaults for the way your PivotTables will display.

Proofing tab

• AutoCorrect – use this to set up your own text shortcuts and correct words/phrases you often type incorrectly. You can set shortcuts for company names, team names, etc. (i.e. “MLB” >> “Major League Baseball”). See below for a macro that can help you edit these in bulk using an Excel sheet!

Save tab

• Default file save type – you might want to change this setting if you often add macros to your files and want to change the default file type to .xlsm.
• Default file location – set this to a logical place to spend less time navigating not just when you save, but also when you’re searching for files to open later on!

Advanced tab

• Display section | Show recent workbooks | Quickly access workbooks – use this to show your recently accessed files directly on the file ribbon tab!
• General section | Edit custom lists – set up lists you often use to quickly enter them in one step. Examples: locations, departments, names, status labels, etc. See below for a macro that can help you edit these in bulk using an Excel sheet!

Customize ribbon tab

• Build your own ribbon tab with your favorite actions 🖼️!
• Add several tabs/groups to customize the way you want the ribbon to look.
• You can modify existing ribbon tabs, but this may confuse others when they see you working.

Quick access toolbar tab

• Add your most common actions to the top bar above the ribbon (or move it below the ribbon) 🖼️!
• Consider including those obscure commands you struggle to find or that take several clicks to get to.
• The first 10 QAT items will run when you use the keyboard shortcuts Alt+1 through Alt+0.

Add-ins tab

• Add-ins are a great way to significantly extend the usefulness of Excel.
• There are thousands of add-ins available that can serve particular needs, connect to applications, or automate a lot of otherwise manual tasks.
• The XLEV8 Excel Add-in is a great example that adds hundreds of shortcut and bulk automation tools.

Macros That Help

• Formula Error Picker – this allows you to quickly toggle formula errors (the little green triangles) on and off.
• Bulk Edit Autocorrect – this lets you adjust your auto-correct text shortcuts in one bulk worksheet step!  Easily share those settings with others or transfer them to another computer.
• Bulk Edit Custom Lists – this lets you adjust your custom lists in one bulk worksheet step!  Easily share those settings with others or transfer them to another computer.

Summary

If you’re a daily Excel user, customizing some of these Excel settings could be quite helpful and provide several shortcuts for working more efficiently.  In my experience, adding your own icons to the Quick Access Toolbar has been the most important.  Even though I try not to use the mouse much, it’s still helpful to have some of those commands up there.  And the QAT works in several other MS apps too!  Don’t be like 80% of people and just accept the standard default settings – spend a few minutes and make them your own!

How have you customized Excel to make it easier to use?  Let us know in the comments below!