What Every Financial Analyst Should Know About Excel

What Every Financial Analyst Should Know About Microsoft Excel

The use of spreadsheets is today indispensable in financial modeling and analysis, or any finance and accounting job. Good, fundamental Excel training will help financial analysts build a solid foundation to master the use of spreadsheets right from the start of their financial career.

Once you’ve gained mastery on the basics of Excel, you may start to look for ways to perform financial computations, analysis, reporting and other tasks faster and more accurately. This can be done, for instance, by using fewer keystrokes, shortcuts to certain functions, and programming macros to automate repetitive financial computations in Excel.

We have suggested a number of typical Excel tips that any good financial analyst should know in their route to attaining mastery of spreadsheets and Excel.

Firstly, a financial analyst may consider using line breaks in each Excel spreadsheet cell to avoid covering up original data. Many new users of Excel face the problem of the presumed loss of original numbers when more data is moved or entered into a spreadsheet cell.

This occurs when the Excel user has entered a lot of numbers into a spreadsheet cell, and then moving to a new spreadsheet cell to enter further numbers. The original data then gets covered up and is presumed “lost”.

Forcing line breaks in each spreadsheet cell will help rectify this problem:

  • Click on a cell and type the first line of data. Then, press Alt+Enter.
  • Now, type the second line of data, followed by pressing Alt+Enter when you are done.
  • For every subsequent line of data added, press Alt+Enter at the end.
  • When you’re finished, press Enter.

This handy Excel keystroke function forces line breaks in each spreadsheet cell, thereby eliminating the problem of covered-up original data.

At some point of time, a financial analyst may find the need to import text from one office productivity software application into another, such as importing tables of financial information from Microsoft Word into Excel.

The text import process normally carries over the original formatting from Word into Excel too, which may result in unaligned or wrong formatting in your Excel spreadsheet.

A financial analyst may therefore think of using the Paste Special function to enhance your control and power over the copy and paste function:

  • First, highlight the text you want to import, then press Ctrl+C to Copy the data.
  • Then, return to your Excel screen, click the Edit menu, and choose Paste Special.
  • This brings up the Paste Special menu which then allows you to either retain the original formatting of the Word table, or selective paste values, formats and other options so that your imported data adheres to the formatting you want to use for your spreadsheet cell.

In some instances, a financial analyst may find that scrolling down an Excel spreadsheet will eventually lead to header titles at the top of columns or the beginning of rows disappearing off the screen as you scroll.

A financial analyst may therefore choose to use the Freeze Panes function to immobilize a row of header titles to always display as he or she scrolls down on an Excel spreadsheet:

  • Select the column to the right or the row below of where you want to freeze.
  • Click on the Window menu, then click Freeze Panes.

Instead of presenting complex mathematical calculations as tables of data, a good financial analyst will always convert data tables instead into visual graphic charts and graphs. This is especially useful in presenting the results of a financial model in an easy to understand manner.

Here’s an example of how to use a pie chart to display a company’s revenue mix by products:

  • First, enter the sales of Product A in one column, and the sales of each subsequent product in adjacent columns.
  • Following that, highlight one of the columns in your Excel spreadsheet. Then, using the Insert menu, click Chart, then Pie, then Next, and Next again.
  • Now, select where you want your chart legend to appear by clicking on the Titles tab. In the Chart title box, enter your title (for example, “Revenue by Products”). Then click the Data Labels tab, check the Percentage tab, and click Next.
  • Insert the chart into a new Excel worksheet or as an object in your existing Excel worksheet.
  • You can update your data or change it to actual revenue, each time changing the chart itself to display, in “pie slice” format, the percentage of your revenue represented by each product.

Last but not least, a good financial analyst will always use keyboard shortcuts. Print out a handy copy of keyboard shortcuts for Excel and keep them next to your computer monitor screen. You will soon start to remember the most frequently used Excel shortcuts, and with time, only need to refer to your Excel keyboard shortcuts list for Excel shortcuts that are not used frequently.

There is a useful reference guide for using Excel for Financial Analysis and Excel Short Cuts check list available in the Finance 3.0 forums.

These documents require you to be signed in to Finance 3.0 for free access and download.

Not a member? Sign up today, free and quick registration process.

Visit Finance 3.0 - don't miss out on this high quality financial learning opportunity

Comment on this article