Home » Posts tagged 'Excel tips'

Tag Archives: Excel tips

Some useful Excel links collected from the Internet

In my second-year undergraduate Management Accounting course, we spend a few weeks on learning the ins and outs of Excel. However, learning and improving on Excel does not stop with a mere test – it can only just be the beginning. Below is a list of interesting links to free readings (and even an online course) for those who want more than just adding and subtracting in Excel:

  • Excel with Wayne – 21-day free online course (very useful, highly recommended) LINK
  • How to make prettier, more readable spreadsheets LINK
  • How to create progress bars in Excel with conditional formatting LINK
  • Excel’s versatile CONVERT function LINK
  • How to craft the best Excel spreadsheet models LINK
  • Factoring in the time value of money with Excel LINK
  • How to use Excel in seasonal forecasting LINK
  • How to boost Excel efficiency with Power Query LINK
  • And finally a WSJ-article discouraging the use of Excel (or put it better: know when Excel works and when it has reached its limitations) LINK

Whilst not all of these may be relevant to your work, they a) may be in the future, and/or b) definitely deepen your understanding of Excel. And according to employers, Excel is a “skill” (if one was to reduce it to “one” skill) that accounting graduates still show deficiencies in.

Good practices when you design your Excel workbook

Microsoft Excel is the standard and go-to application when using spreadsheets – there is hardly any spreadsheet user who has not worked with Excel at one point in their professional career before. Now that Microsoft has spread out beyond the PC onto tablets, smartphones, and has become cloud-based, its distribution is ubiquitous.

As such, it has become even more important to create professional spreadsheets that can be understood and collaborated on in a seamless manner – there, Google Docs has clearly shown the way. In this blog post by J. Carlton Collins, he details 15 highly useful and – may I state – necessary rules for designing meaningful Excel spreadsheets that start with adequate documentation and a table of contents, well-organised spreadsheets, named ranges/cells, and quality management to avoid errors and misconceptions. These steps are not dealing with style per se, but with good practice that will enhance your reputation as spreadsheet expert.

Do you know what chart type to use in Excel?

In our book, we discuss the history and use of spreadsheets in accounting, chief amongst them Microsoft Excel. Management accountants in particular rely on the graphical depictions of data and information that Excel offers. However, all too often my Excel students in year 2 of their A&F programmes seem to be unsure what kind of chart type is best to illustrate what kind of information. In this blog post by Ryan Dube, he does not only explain when to use the most common chart types, but also provides hands-on examples on how they are to be created. It is highly recommended not only to have a good read, but also apply what he shows in Excel.

Spreadsheet news – There are 6 new functions in Excel 2016!

With the fairly new cloud-delivery model, many subscribers of Office 2016 will have received news and updates of their selected products over the last couple of months. One that captured my immediate attention announced 6 new, quite useful functions in Excel 2016. They are:

  • TEXTJOIN: joins text strings in a simpler way than the CONCATENATE-function; very useful for address or email lists;
  • CONCAT: this replaces the old CONCATENATE function, and works in a similar fashion than its predecessor;
  • IFS: this is a great new function, as it helps avoid the need for nested IF-statements that can become quite lenghty;
  • SWITCH: provides a similar use to IFS, but works to a different logic. An expression is evaluated against a list of values, and specifies return values specific to each value;
  • MAXIFS/MINIFS: the newest additions to the family of COUNTIFS, AVERAGEIFS, and SUMIFS functions; these work similar to MIN and MAX, but can be tied to conditions to be met.

This Office blog post here provides more detail on the new functions, as well as further links to the individual functions.

At the same time, this is a very good example how SaaS models can help update software on an ongoing basis without the need to roll out fully new versions. Of course, this requires a stronger attention by accountants to new developments in their area of expertise.

Excel tips – INDEX and MATCH

Here is a link to a very useful exercise on the Excel INDEX and MATCH functions. These functions are useful for looking up data in spreadsheets.