Accounting, Finance

Excel Hacks That ALL Finance Professionals Need to Master

November 16, 2016

Other than being a finance and accounting geek, I am also an Excel geek.

Although I havn’t reached God-like status like some Excel masters who can use Excel without a mouse, I am fairly experienced in building complex financial models, using macros and VBA coding once in a while.

Being someone lazy, I always question myself ‘How can I automate everything using Excel?’ As a finance professional, I use Excel for month-end closing, forecasting, reconciliation, creating forms… the list goes on.  One thing I love about Excel is that all these things I mentioned can be automated using Excel with a simple mouse click, with the help of macros sometimes. Honestly, I can’t imagine how people in the past did the above before Excel was invented.

So, as a huge fan of Excel, my eyes lit up when I heard about the largest financial modelling competition for the best and brightest Excel geeks from all over the world, Modeloff. Every November and December, students and working professionals from over 100 countries will be presented with a variety of challenges testing innovation, speed, data, risk and of course modelling, and the top 16 finalists will battle in front of world-class judges in London for the coveted Modeling World Champion Crown. For geeks like me who love a little challenge, that got me very interested. Unfortunately, I was not able to join this year, but I will certainly do so at least once in my lifetime.

Source: Modeloff website – The Legend

Anyway, if you decide to compete like me, you will need to practise loads (past questions of the competition are posted online here) to train up your speed in using Excel. But even if you are not intending to compete, it is always good to know some Excel hacks that will save you time and make you look like an Excel guru in front of your friends or colleagues.

There is a long list of Excel shortcuts and formula that you can find online, and you probably do not have to master every single one. Let me highlight some simple ones that every Excel geek wannabe should know, starting from the basics.

(You can skip the first section, if you are already fairly proficient in Excel)

Most Basic Shortcuts which You Should Already Know

– Ctrl S : To save a file [Most important shortcut in my opinion. You will want to save your file regularly, or else your hard work will go to waste if your computer crashes]

– Ctrl O : To open a file

– Ctrl Z : To undo

– Ctrl X : To cut

– Ctrl C : To copy

– Ctrl V : To paste

Extremely Useful Basic Shortcuts that have Saved Me Hours using Excel

These are shortcuts that are really basic, but extremely useful if you have to use Excel without a mouse.

– F2, or the most useful key in Excel : Use it to edit the active cell.

Let’s say you are navigating across the spreadsheet, and you found a cell that you want to edit the formula. Instead of clicking on the formula bar or double-clicking, just press F2 to edit the formula.

– F4 : Repeat the last action.

Let’s say you want to format some randomly scattered cells (colour the cells yellow for example). Colour the first cell yellow. Then you just go to the other cells, and press F4 to repeat the last action.

– Ctrl + [ : Select all cells referenced by cell formula

– Ctrl + Home : Shortcut to Cell A1

– Shift + Ctrl + End : Shortcut to select all cells up to the end of worksheet

I normally use Ctrl + Home, followed by Shift + Ctrl + End immediately, to select and highlight all the cells that are in use in the worksheet. (very useful if you want to select all the working cells for some formatting.

– Ctrl + PgUp / PgDown: To toggle to the next / previous worksheet

Now let’s move on to some Excel formula that I believe all finance professionals should use in their work. If you are not using these, you are probably not maximizing the use of Excel for your analysis or operations.

7 Useful Excel Formula Finance Professionals Should Try To Master

– IF :Love it!

– SUMIFS : I use this almost all the time!

– VLOOKUP : I use this quite regularly!

– COUNTIF : I use this occasionally!

– & (or CONCATENATE) : I use this occasionally!

– INDEX : I use it together with MATCH. I use it once in a while.

– MATCH : I use it together with INDEX. I use it once in a while.

– INDIRECT : Seldom but useful!

I won’t bore you with the definitions of these Excel formula. But I can confidently say that these are the ones (other than plus, minus, multiply, divide and sum) you can probably apply to your spreadsheet models 90% of the time! It will definitely be worth your time to master these key Excel formula!

Personally, I have used these to create spreadsheet models to save me time in the following – month-end reporting, cash flow forecasting, creating dashboards out of raw data extracted from ERP systems, e.g. SAP, creating intercompany netting system to net AP and AR, data analytics, and many more projects, and I will love to share more with you, if you are interested!

Check out the infographic below by GetVoip which will take your Excel knowledge to the next level!

Excel Hacks

 

Disclaimer:

That said, learning these Excel hacks alone will probably not be enough for you to win the Modeloff World Champion Crown in the near future. You probably still need to put in hours of practice, and master more advanced tools like PivotTable, Goal Seek, etc., before you can consider yourself to be a true Excel geek and join the ranks of the real elites in achieving modelling greatness.

 

about author

Lee is currently pursuing a Master’s degree in Finance at INSEAD. Prior to his Master’s, he has worked for about five years in the treasury and accounting space. He graduated from SMU with a double degree in Accountancy and Finance, and is also a Chartered Accountant (Singapore). Other than building Excel spreadsheets and poring through annual reports, he spends his time reading and watching sci-fi movies.