An entire book can be written about the Excel interface so I will be focusing on 10 key points that must be familiar to everyone.
The Excel Essentials is a series of posts on the most important Excel skills as deemed by me. I consider them the bare minimum that any user should master regardless of the industry or level of involvement with spreadsheets.
1. Know your ribbon tabs — and expect them to change
It seems obvious how important is knowing by heart the element that is (almost) always visible. However, far too often I see people wandering around the screen in search of a command without having the foggiest idea where to find it in the vast Excel interface.
Even if you use keyboard shortcuts, make the effort to be familiar with the ribbon and what type of commands are situated under the tabs. This doesn’t mean that you should be able to tell that by default the Insert symbol button is the last one on the right on the Insert tab. However, if this is the kind of operation you are likely to use, you must be well aware where to find it.
In my opinion, all the commands on the Home tab should be well understood and located quickly by the user.
Furthermore, extra tabs (called tool tabs) appear when objects are selected. This might be a chart,
a pivot table,
or something else (shapes; queries, etc.). The important 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 button if you are using now outdated version of Excel) is special with regard to the file/system-level options it provides. Take the time to explore what it offers. Don’t memorize it all but make a mental note about any features that might be useful to you. For example, if you often open the same spreadsheet, you can pin it in the Open section. This way you won’t have to always navigate to it.
Dig deeper 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 interface adapts to the window size. This means that what you see when working full screen (and potentially on big monitor) is going to change when you size down your window:
Notice how the size of the icons, their names and even their relative positions change when I have allowed for them only half the real estate on the screen. Moreover, the look of it all on the web will be slightly different.
The interface is not static and you must not be surprised by it changing. Don’t try to learn every variation of it but don’t panic when it morphs. Whatever the change, it is never too radical.
I admit that the move from classic menus to the ribbon (in Office 2007) was a seismic change. However, this is not the norm and even though it seems like changes are coming, such big moves are rare.
4. Not all settings are up on the ribbon
Often you can find them in a sidebar.
The options there vary greatly from one element to another but just like with tabs it is good decision to memorize the most frequently used so you don’t waste time searching for them.
5. Shave a little more time with the Quick Access Toolbar
The Quick Access Toolbar is that zone on the top left of Excel’s window that by default sits above the ribbon.
The toolbar does what the name suggests by allowing you to pin all sorts of commands on it. In the example above I have put the Evaluate formula shortcut so I can easily start troubleshooting without always going to the Formulas tab or having to memorize the keyboard shortcut.
If you click the little arrow extra choices will appear.
If you choose More Commands… from the menu a new window pops-out. From there you can pin even exotic functionalities.
Regardless of what commands you use on regular basis, it makes sense to put them up on the Quick Access Toolbar so you can save time & efforts when you reach for them.
6. For some objects there are more options in a sidebar
Various options will appear in a sidebar once you start to customize objects.
Potentially, a large number of settings will be available. To me, some of the sidebars are among the messiest parts of Excel’s interface. However, this makes the ability to navigate them (and understand the preferences) even more valuable.
And navigation is not easy — the same set of customizations might be accessed in different manners. But if we focus on the navigation in the sidebar, there are clickable buttons in various shapes and sizes:
7. The right-click context menus are a must as well
Every experienced user will tell you that the less mouse you use, the better — you are saving a lot of time (mainly using keyboard shortcuts). This is absolutely true as long as you don’t find yourselves holding the phone; a sandwich; or a colleague(?) with one hand while trying to work in a spreadsheet with the other.
Simply because working with the mouse is slow, doesn’t mean you don’t have to know how to do it by heart.
8. Search for a command if you can’t find it anywhere
I admit that I have been there — spending too much time not being sure where to look for a command. Instead, I could just ask for it from the search bar that sits next to the tab names:
The tool can provide quick access to what you need but only if you know what you are looking for. You can get suggestions but in my experience heading to a search engine would find you results with fewer clicks.
9. Tabs can be shown/hidden on the ribbon
This is not a step for a beginner to take but tabs can be shown or hidden. If you go to the File tab and choose Options a new window will open. Selecting Customize Ribbon from the sidebar menu will allow you to add or remove tabs (together will all their commands) from the ribbon.
The Developer tab is not visible when you first install MS office. However, as you progress with Excel you are likely to need it. You can show it from the window in question.
Likewise, there is a possibility you don’t need the Page Layout 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 narrow down this part of the Excel interface by creating a new section. In the Customize Ribbon menu click the New Tab button — this will create New Tab that is marked as custom.
You can easily rename the new elements by selecting them first:
The next step is to choose what commands to be added. There are several “views” that will list commands from which to pick.
From the drop down choose an option that will work for you:
- Main Tabs and Tool Tabs will show commands you can already find on the ribbon — either through the tabs that are always visible or the ones that show when you work on a specific object.
- All commands will provide the most complete list of options and I often surprise myself with what I find there. On the downside — the names are not always descriptive enough.
- Macros is probably the most beneficial option but unless you know what and why appears there, don’t select from it. Macros can be harmful and the fact you are reading this post suggests you are not yet ready from them.
Add the commands to your custom tab.
In case you need it, you can add another group. Keep in mind you don’t need one to add options.
When you are ready, you new tab will appear in all its glory:
Bonus: Change the Office theme
Regardless of how beautiful the Excel interface 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 getting popular. The same can be achieved with Excel if you go for a darker theme.
From the File tab choose Options and find the Office Theme setting. Then check if one of the options there might not suit you better.
Keep in mind this setting is not just for Excel but MS Office-wide. Once you change it, the other applications (eg. Outlook, Word, One Note) will change their theme as well.
I find the colour schemes to be well design and I have noticed very little inconstancies with them. Hence, I highly recommend the change.
