5 tips to becoming an Excel Master

Bard Cloete is a Financial Business Analyst for the BroadReach Group, based in our HQ offices in Cape Town. He is a 2020 Founders Awards finalist, recognized under the category We are enterprising for his innovative, resourceful, and agile approach to financial reporting. We’re in good hands on this topic then, read what he has to say below.

Personally speaking, Excel falls within the category of things that I wish I was taught in school but wasn’t. Just like doing my taxes or spotting online scams, working in Excel is a skill that I never needed in my youth, until the day when it went from having zero importance to having serious life consequences if not handled correctly.

My university taught us some basics, but it was only when I started to apply it in everyday work that things start to sink in. When I started working, I quickly saw that having solid Excel skills would save me hours in my day and help avoid boring, repetitive tasks. It became a trade-off between education time and working time – every hour invested in learning led to hours saved through efficiency.

In the spirit of #wearelearners I wanted to share what I have learned to get things done quickly. Some of the tips you might find surprising and some you may already know. Should you apply these, there is no doubt you will be on the path to Excel mastery!

 

Tip #1 Shortcuts are key

Shortcuts are by far the greatest timesavers. Basic tasks such as selecting data, copy/pasting, and typing out formulas make up most tasks performed in Excel – so shaving off a few seconds every time you do them adds up in the long run!

At the beginning of my career, I was stunned when I saw our local office Excel Guru navigating around Excel without using a mouse. He only used the arrow keys, Shift and Ctrl. Check out this video to see what I’m talking about!

Knowing the basic shortcuts for your most common tasks such as copy (Ctrl + C) and paste (Ctrl + V) are essential. For more, check out this link.

Finally, did you know that every command in Excel can be accessed through a keyboard shortcut? By pressing Alt once, you will see letters appear on the taskbar on each of the menu items on the top of the screen.

Pressing Alt reveals the magic letters. Pressing Alt again makes them go away

Go ahead, give it a try! Don’t worry if you mess something up, you can always Ctrl + Z to revert 😉.

To engrain these shortcuts in your way of working, I recommend that you write out the shortcut for your 3 most used functions and keep this next to your desk. Remind yourself that every time you use these functions, you use the shortcuts on your list. In no time, this will become your new way of working, bringing you one incremental step closer to Excel Enlightenment.

 

Tip #2: Own your interface

You can customize your own Quick Access Toolbar to add bespoke functions that you use most. These will always be visible on the top bar and can be accessed by pressing Alt + the position of the icon, counting from the left. For example, if I want to use Quick Filter in the example below (the funnel looking thingy), I will press Alt + 6.

Customize the toolbar by clicking the drop-down and clicking “More Commands”

 

Tip #3: Understand absolute referencing (& experiment!)

Referencing is a fundamental part of working in Excel, like knowing left from right. We all mix it up sometimes (or is that just me?) – the important thing is knowing what it’s about.

If you’re they type of person who begins to sweat when they see the “$” symbol in a formula, don’t despair (I have been there). I’d recommend this video which explains it quite well.

In short, Excel references can be either relative, or absolute. Relative references move as the formula gets copied. Absolute references don’t. Hitting “F4” is how you toggle a selected reference between the states. Watch the video. It will all make sense.

 

Tip #4: Understand the core functions

There is a famous saying “if you give a man a hammer, every problem becomes a nail”. Excel is exactly like a toolbox and knowing the core functions is the equivalent of filling your toolbox with tools that are the best at performing specific tasks. With physical tools, the association is common knowledge. Nail? Hammer. Screw? Screwdriver. Use cases in Excel are very similar.

If you have a large data set that you need to summarise? Use a Pivot Table

If you want to view-only a subset of a larger data set? Try Filters

Do you need to add a column of data to your data set from another table? Use Xlookup (or Vlookup if you are running an older version of Excel)

Some other core functions I would recommend getting the hang of are:

• Basic (very essential):
  • SUM
  • IF (with AND/OR)
  • COUNT/COUNTA
  • AVERAGE
• Advanced:
  • SUMIF/SUMIFS
  • COUNTIF/COUNTIFS
  • SUMPRODUCT

There are tons of good, free instructors on YouTube. Find a teaching style that works for you and spend some time with them :).

 

Tip #5: Be patient

Most of us have heard the saying “you have to spend money to make money”. With Excel, it’s the same, but instead of money, it’s time. To see a real improvement in your productivity, you have to be okay with investing the time upfront. Changing old habits or learning new ones can be frustrating and initially things may take longer – be patient with yourself and trust the process.

Stick to a manageable pace of learning that you can maintain and implement regularly. After a year of keeping at it, you will have completely transformed your relationship with Excel. And, importantly, reward yourself by celebrating your accumulated time savings with some well-earned you-time.

 

I’ll leave you with this: Excel is designed to make our lives as working professionals easier: automating boring tasks to free up time for the things that are more important in life. I hope this article helps you do just that, getting you closer to mastery of this great toolkit. Happy learning!