2020-21 Update: Excel

by Paula Guilfoyle
access120 days access
cpd hours4 CPD hours

This course will enable you to:

  • Improve workflows with the new notes and comments facilities and build dashboards using the new free images and icons
  • Identify patterns in data using the AI embedded in Ideas
  • Incorporate Stocks and Geography using rich data types and linked data types
  • Retrieve data from PDFs
  • Work with dynamic arrays and spill areas and new functions such as FILTER, SORT, SORT BY and UNIQUE
  • Explore more advanced new functions such as XLOOKUP, XMATCH and STOCKHISTORY

Excel is the tool of trade for most accountants and keeping up to date with its capabilities is an essential element in most accountants' CPD that will enhance both competence and productivity. The past year has many new features added to Excel including productivity enhancement tools such as Notes and Ideas, through to the complete reworking of the calculation engine and the release of Dynamic Arrays.

This course will bring you up to date with all the general improvements and enhancements that have been released, helping you to explore completely new ways to model data and carry out calculations using dynamic arrays, and will introduce you to more advanced functions such as XLOOKUP, XMATCH and STOCKHISTORY.

The updates described in this course are all available within Excel 365. If you are using any other version of Excel, you will not have access to the new features, but you can sign up for a free trial of Microsoft 365.

Recent improvements

  • Excel versions
  • How to check your version of Excel
  • Improvements to comments
  • Improvements to Ideas
  • Ideas in action
  • Workbook Statistics and Sheet View
  • Using free images and icons
  • Using images and icons

Working with data

  • Rich data types
  • Configuring rich data types
  • Using rich data types
  • Tips for rich data types
  • Pivot table improvements
  • Retrieving data from a PDF
  • Data profiling

Dynamic arrays

  • The basics
  • Changing how Excel works
  • Creating a unique list
  • Working with dynamic arrays
  • Dynamic data validation and SORT
  • Backward compatibility
  • Pivot table alternatives
  • Using SORT, SORTBY, and UNIQUE
  • Using dynamic arrays
  • Wrapping up UNIQUE, SORT, and SORTBY
  • Using the FILTER function

More new functions

  • Using XLOOKUP
  • Searching in XLOOKUP
  • Using XMATCH
  • Using the functions
Paula Guilfoyle is a member of the Institute of Certified Public Accountants in Ireland. She practiced in Industry for over 15 years, working with both small and large private companies, before moving on to teach Excel and Power BI to accountants and professionals. Paula has given many talks and presentations in the areas of Excel, spreadsheet use, PowerBI and business intelligence, and has published many articles on the same subjects.
ACCA logo

ACCA partner with accountingcpd.net to provide high quality CPD for members. As an ACCA member, you are required to complete at least 40 relevant units of CPD each year, where one unit is equal to one hour. 21 units must be verifiable; the other 19 can be non-verifiable.

Verifiable CPD
Your accountingcpd.net course counts as verifiable CPD, if you can answer "yes" to these questions:

  1. Was the learning activity relevant to your career?
  2. Can you explain how you will apply the learning in the workplace?

You select courses that meet these criteria, and as you complete each course you get a CPD certificate so you can provide ACCA with the evidence that you undertook the learning activity.