Q

Custom Formula Screenshot

Q

Fake Reference Formula Screenshot

Q

How to Break Links Screenshot

Q

Break Links Before/After Screenshot

Overview

Excel offers a world of powerful customizations you can use to work more efficiently, but most people don’t take advantage of them.  It’s like buying a smartphone and not downloading any apps!  This isn’t just an Excel issue – if you’re familiar with the 80/20 rule, studies often show that 80% of people tend to use things just as they were given to them – making no customizations or putting in much effort to learning more ways to use them.

For any apps that you’re going to use virtually every day, it’s worth taking a course, following experts, and networking with other users.  It’s amazing how much time and frustration that can save.  Shameless plug: I train corporate groups and often host webinars on Excel and other topics!

In this article, we’ll focus on Excel and look at some powerful customizations you can make to work more efficiently and make Excel more powerful.  In the box below, you can grab an example template of some of the customizations you can make to Excel, and it also comes with a cheatsheet that summarizes this article.  Make sure to grab yours today!

Why customize Excel?

Like the analogy we started with above, imagine using a smartphone with no downloaded apps – just the stock ones – and no shortcuts, folders, or any other personalizations.  It would be a bit frustrating, right?  That’s exactly what it’s like when you don’t customize Excel, only most people don’t know you can customize it!

Here are four key reasons to customize your Excel environment:

  • They make you faster – by adding shortcuts and eliminating redundant steps
  • They help you make less mistakes – by removing manual steps (and often adding checks for you)
  • They reduce frustration – no one likes doing the same boring steps over and over again
  • They let you do things you can’t otherwise do

The customizations

1. The Quick Access Toolbar (aka QAT)

Look at the very top of Excel – that dark green bar.  If all you see is the save icon, you haven’t yet added anything.  You can add several icons up there as shortcuts (hence “quick access”).  There are hundreds of icons you could add, so don’t go nuts.  If you add too many, you’ll spend time just trying to find them!

For the actions you do the very most, or for icons that take several clicks or you struggle to find quickly, consider adding them to the QAT where you can click them super-quickly.

There are two ways to add icons to the QAT:

  1. Right-click any icon anywhere on a ribbon menu, and click “Add to Quick Access Toolbar”
  2. Go to Files > Options > Quick Access Toolbar Toolbar, and add several of them.

Here are three other things to know about the QAT:

  1. The first 10 QAT icons can be triggered with the keyboard shortcuts Alt+1 through Alt+0.
  2. The QAT is also available in other MS Office apps (i.e. PowerPoint, Word, Outlook, etc.).
  3. You can import and export your customizations, making it easy to back them up and share!

See below for an example of the before and after look of setting up the QAT.

2. Custom Ribbon Tabs

If the QAT gets you excited, you can take it even further by making your own ribbon tab, full of your favorite actions and icons.  While I do see many people customize the QAT, I rarely see people customize their own ribbon tabs.

Why do that?  Consider this – the stock ribbon tabs are built somewhat logically – formula stuff on the formula tab, view stuff on the view tab, etc.  While they are logical, there are plenty of items on each tab you likely never use.  Why not pull all the ones you DO use together on a custom tab?!?

The options to start customizing the ribbon are very similar to the QAT above:

  1. Right-click anywhere on the ribbon and click “Customize the Ribbon”.
  2. Go to File > Options > Customize the Ribbon.

Either way you get there, you’ll probably want to click New Tab and/or New Group.  While you can customize the existing ribbon tabs, I don’t recommend this as it might make things tougher to find for you and others.  Make your own tab, your own groups, and fill them with your preferred icons.  Then reorder them – optionally putting your new tab(s) at the top (which puts them on the left in the ribbon tab).

Like the QAT, any customizations you make to the ribbon will be included in the export file, and you can also customize the ribbon in other MS Office apps!

See below for an example of a custom ribbon tab – the one I use that combines several helpful icons in one place!

3. Default Settings

Be honest – how many times do you add or delete sheets as soon as you start a new file?  Or change the font size/style?  These are just some of the defaults you can set so that ANY new Excel file start just the way you want!  It may not seem like much effort, but all those clicks really add up over time and slowly drain your energy and focus.

There are two approaches to adjusting the default settings that you can mix and match:

  1. Go to File > Options (defaults are spread out among several of these tabs).
  2. Set up a blank workbook the way you want – fonts, colors, sheet names, etc., and save it as an Excel template in Excel’s startup templates folder.

Among all those defaults you can set is your AutoCorrect options – when you type something like initials, it will replace for you.  This one is located in File > Options > Proofing > AutoCorrect.  To make those edits in bulk and share them, check on the XLEV8 Excel Add-in custom tool called Bulk Edit AutoCorrect!  Bonus – any AutoCorrect changes will be reflected in other MS Office apps!

See below for a screenshot of some of the baseline defaults you should consider setting.

 

4. Styles and Themes

Whether you think much about it or not, formatting is core to your Excel files – as much as body language is a key part of what you say when you communicate.  A best practice of formatting is consistency, and a great way to use formats consistently is to use styles and themes.

Styles allow you to set several formats at once – fonts, colors, borders, number formats, etc. – and even better, you can modify those styles in one place and they update all throughout your file!  Styles are linked to themes – the themes you choose drive the colors and other formats you see in the styles available.

To set the themes in your files, go to the Page Layout ribbon tab.  On the left side, choose a full theme or part of a theme.  As you hover over them, you’ll see a preview with your data.  Any theme changes will likely update styles and formats all throughout your file – just be aware and prepared for that!

To set cell styles, go to the Home ribbon tab. Near the middle, click on Styles, and you’ll see several native styles available.  Hover over them to see a preview with your data.  You can modify them (just right-click one), and you can add your own custom styles (click the New Cell Style button at the bottom).  You’ll see a variety of formats you can apply.  To set the cell style for a cell, simply select one or more cells, then set a native or custom cell style.

See below for a screenshot of the default cell styles available.

5. Add-ins

Perhaps the most powerful customization you can make to Excel is leveraging add-ins.  Add-ins can enhance Excel in several ways:

  • Directly connect to other systems
  • Set custom shortcuts
  • Automate repetitive tasks
  • Standardized reports, visualizations, and calculations
  • Add custom interfaces and validations

Add-ins can subtly apply custom keyboard shortcuts or more noticeably change the user interface – the ribbon and even the right-click menu.  Aside from the best add-ins I’ve used do three things really well:

  • Apply defaults and favorites very quickly, such as formats or formulas
  • Navigate quickly between files, sheets, and references
  • Complete high-volume tasks in one bulk step

If you’re looking for some examples, check out the XLEV8 Excel Add-in – it has hundreds of tools designed to enhance Excel in all of these ways.

See below for an example of it in action and several enhancements it provides – typical of a robust add-in.

Video

Summary

If you’ve just been using your standard-issue copy of Excel, these customizations should get you started on a more powerful and enjoyable Excel experience.  At a minimum, I’d add some icons to the QAT for the actions you’re doing the most – especially those hard-to-find ones!

From there, track the time you spend doing tasks big and small.  Chances are there is a built-in customization you can make or a powerful add-in that makes it much easier to get your work done.  One last shameless plug: the XLEV8 Excel Add-in has hundreds of tools that make your life easier, designed to save you 200+ hours a year.  Check it out here!

What customizations have you made to Excel that have really paid off?  Let us know in the comments below!

Don't miss great tips, tricks, news, and events!

  • Get our 105 Excel Tips e-book free!
  • Get monthly insights and news
  • Valuable time-saving best practices
  • Unlock exclusive resources

Almost there! We just need to confirm the email address is yours. Please check your email for a confirmation message.