Excel essentials — The Interface And Its Customization

This entry is part 2 of 3 in the series Excel — The Essentials

An entire book can be writ­ten about the Excel inter­face so I will be focus­ing on 10 key points that must be famil­iar to everyone.

The Excel Essen­tials is a series of posts on the most impor­tant Excel skills as deemed by me. I con­sid­er them the bare min­i­mum that any user should mas­ter regard­less of the indus­try or lev­el of involve­ment with spreadsheets.

1. Know your ribbon tabs — and expect them to change
Excel - Ribbon Tabs

It seems obvi­ous how impor­tant is know­ing by heart the ele­ment that is (almost) always vis­i­ble. How­ev­er, far too often I see peo­ple wan­der­ing around the screen in search of a com­mand with­out hav­ing the fog­gi­est idea where to find it in the vast Excel interface.

Even if you use key­board short­cuts, make the effort to be famil­iar with the rib­bon and what type of com­mands are sit­u­at­ed under the tabs. This does­n’t mean that you should be able to tell that by default the Insert sym­bol but­ton is the last one on the right on the Insert tab. How­ev­er, if this is the kind of oper­a­tion you are like­ly to use, you must be well aware where to find it.

Insert symbol button under the Insert tab.

In my opin­ion, all the com­mands on the Home tab should be well under­stood and locat­ed quick­ly by the user.

Fur­ther­more, extra tabs (called tool tabs) appear when objects are select­ed. This might be a chart, 

Selecting the chart shows new tabs on the ribbon.

a piv­ot table,

Selecting the pivot table shows new tabs on the ribbon.

or some­thing else (shapes; queries, etc.). The impor­tant thing is that new tabs and options might appear and you should keep an eye for them.

2. Know what is under the File tab

The File tab (or the Office but­ton if you are using now out­dat­ed ver­sion of Excel) is spe­cial with regard to the file/sys­tem-lev­el options it pro­vides. Take the time to explore what it offers. Don’t mem­o­rize it all but make a men­tal note about any fea­tures that might be use­ful to you. For exam­ple, if you often open the same spread­sheet, you can pin it in the Open sec­tion. This way you won’t have to always nav­i­gate to it.

Selecting file to pin.
Select the file you want to pin…
File that is already pinned.
…and make sure it is always above the recent spreadsheets.

Dig deep­er in the File menu and be aware which of the options there can be of use to you.

3. Be prepared for the icons to shrink

The Excel inter­face adapts to the win­dow size. This means that what you see when work­ing full screen (and poten­tial­ly on big mon­i­tor) is going to change when you size down your window:

Home tab - full screen
The Home tab when work­ing full screen
The Home tab - half screen.
The Home tab when snapped to the left half of the screen

Notice how the size of the icons, their names and even their rel­a­tive posi­tions change when I have allowed for them only half the real estate on the screen. More­over, the look of it all on the web will be slight­ly different.

The inter­face is not sta­t­ic and you must not be sur­prised by it chang­ing. Don’t try to learn every vari­a­tion of it but don’t pan­ic when it morphs. What­ev­er the change, it is nev­er too radical.

I admit that the move from clas­sic menus to the rib­bon (in Office 2007) was a seis­mic change. How­ev­er, this is not the norm and even though it seems like changes are com­ing, such big moves are rare.

4. Not all settings are up on the ribbon

Often you can find them in a sidebar. 

Formatting the data series - apart from the extra tabs on the ribbon, the sidebar is also visible.

The options there vary great­ly from one ele­ment to anoth­er but just like with tabs it is good deci­sion to mem­o­rize the most fre­quent­ly used so you don’t waste time search­ing for them.

5. Shave a little more time with the Quick Access Toolbar

The Quick Access Tool­bar is that zone on the top left of Excel’s win­dow that by default sits above the ribbon.

The Quick Access Toolbar in Excel.

The tool­bar does what the name sug­gests by allow­ing you to pin all sorts of com­mands on it. In the exam­ple above I have put the Eval­u­ate for­mu­la short­cut so I can eas­i­ly start trou­bleshoot­ing with­out always going to the For­mu­las tab or hav­ing to mem­o­rize the key­board shortcut.

If you click the lit­tle arrow extra choic­es will appear.

Customizing the Quick Access Toolbar.
Select­ing an option…
The Quick Access Toolbar with the command pinned.
…will pin it so it is always in reach.

If you choose More Com­mands… from the menu a new win­dow pops-out. From there you can pin even exot­ic functionalities.

Regard­less of what com­mands you use on reg­u­lar basis, it makes sense to put them up on the Quick Access Tool­bar so you can save time & efforts when you reach for them.

6. For some objects there are more options in a sidebar

Var­i­ous options will appear in a side­bar once you start to cus­tomize objects.

Choosing to format chart's sidebar.
For­mat­ting the axis will bring out a sidebar
Formatting chart axis - the sidebar.
The side­bar will offer var­i­ous cus­tomiza­tion options

Poten­tial­ly, a large num­ber of set­tings will be avail­able. To me, some of the side­bars are among the messi­est parts of Excel’s inter­face. How­ev­er, this makes the abil­i­ty to nav­i­gate them (and under­stand the pref­er­ences) even more valuable.

And nav­i­ga­tion is not easy — the same set of cus­tomiza­tions might be accessed in dif­fer­ent man­ners. But if we focus on the nav­i­ga­tion in the side­bar, there are click­able but­tons in var­i­ous shapes and sizes:

Sidebar - changing the element that is customized.
Sidebar - other clickable buttons.
7. The right-click context menus are a must as well

Every expe­ri­enced user will tell you that the less mouse you use, the bet­ter — you are sav­ing a lot of time (main­ly using key­board short­cuts). This is absolute­ly true as long as you don’t find your­selves hold­ing the phone; a sand­wich; or a col­league(?) with one hand while try­ing to work in a spread­sheet with the other.

Sim­ply because work­ing with the mouse is slow, does­n’t mean you don’t have to know how to do it by heart.

Excel right click menu.
There are plen­ty of com­mands acces­si­ble from the right click menu.
8. Search for a command if you can’t find it anywhere

I admit that I have been there — spend­ing too much time not being sure where to look for a com­mand. Instead, I could just ask for it from the search bar that sits next to the tab names:

Searching for a command in Excel.

The tool can pro­vide quick access to what you need but only if you know what you are look­ing for. You can get sug­ges­tions but in my expe­ri­ence head­ing to a search engine would find you results with few­er clicks. 

9. Tabs can be shown/hidden on the ribbon

This is not a step for a begin­ner to take but tabs can be shown or hid­den. If you go to the File tab and choose Options a new win­dow will open. Select­ing Cus­tomize Rib­bon from the side­bar menu will allow you to add or remove tabs (togeth­er will all their com­mands) from the ribbon. 

Excel's options - Customize the ribbon

The Devel­op­er tab is not vis­i­ble when you first install MS office. How­ev­er, as you progress with Excel you are like­ly to need it. You can show it from the win­dow in question.

The Excel Developer tab.
The new tab will appear once you con­firm your choice with the OK button.

Like­wise, there is a pos­si­bil­i­ty you don’t need the Page Lay­out tab. Remove the tick to hide it — this will not delete it.

10. Create your own tab with your favourite commands

If you don’t want to jump from tab to tab, you can nar­row down this part of the Excel inter­face by cre­at­ing a new sec­tion. In the Cus­tomize Rib­bon menu click the New Tab but­ton — this will cre­ate New Tab that is marked as custom.

Create new tab on the Excel interface.

You can eas­i­ly rename the new ele­ments by select­ing them first:

Rename new tab or its group.

The next step is to choose what com­mands to be added. There are sev­er­al “views” that will list com­mands from which to pick.

Pick a list form which to choose commands to add to the custom tab on the ribbon in Excel.

From the drop down choose an option that will work for you:

  • Main Tabs and Tool Tabs will show com­mands you can already find on the rib­bon — either through the tabs that are always vis­i­ble or the ones that show when you work on a spe­cif­ic object.
  • All com­mands will pro­vide the most com­plete list of options and I often sur­prise myself with what I find there. On the down­side — the names are not always descrip­tive enough.
  • Macros is prob­a­bly the most ben­e­fi­cial option but unless you know what and why appears there, don’t select from it. Macros can be harm­ful and the fact you are read­ing this post sug­gests you are not yet ready from them.

Add the com­mands to your cus­tom tab.

Adding commands to the custom tab.

In case you need it, you can add anoth­er group. Keep in mind you don’t need one to add options.

New group in the custom tab on Excel' interface.

When you are ready, you new tab will appear in all its glory:

A custom tab on the Excel ribbon.
Bonus: Change the Office theme

Regard­less of how beau­ti­ful the Excel inter­face is out of the box, it is a bit too bright to look at all day long. In recent years dark modes and night modes are get­ting pop­u­lar. The same can be achieved with Excel if you go for a dark­er theme.

From the File tab choose Options and find the Office Theme set­ting. Then check if one of the options there might not suit you better.

Changing the office theme.

Keep in mind this set­ting is not just for Excel but MS Office-wide. Once you change it, the oth­er appli­ca­tions (eg. Out­look, Word, One Note) will change their theme as well.

Excel - the balck theme.

I find the colour schemes to be well design and I have noticed very lit­tle incon­stan­cies with them. Hence, I high­ly rec­om­mend the change.

Series Nav­i­ga­tion« Excel — The essen­tial skills for the reg­u­lar userExcel essen­tials — Key­board shortcuts »