Free Udemy Courses Udemy Free Courses

Excel for Accountants: Mapping Tables

Excel for Accountants: Mapping Tables
Excel for Accountants: Mapping Tables

Excel for Accountants: Mapping Tables

Automate reporting tasks with a mapping table

What you’ll learn

Excel for Accountants: Mapping Tables

  • Store data in tables so that new rows are automatically included in related formulas
  • Perform conditional summing with the SUMIFS function
  • Create an intermediate mapping table so that Excel has the information needed to translate labels and aggregate data rows

Requirements

  • To work on the exercises, please have Excel 2007/2010/2013 for Windows.

Description

In this course, author and award-winning instructor Jeff Lenning CPA demonstrate the technique featured in his Journal of Accountancy article The Power of Mapping Tables. If you use Microsoft Excel to generate reports, this course is for you. We discuss the benefits of storing source data in tables, how to use the SUMIFS function to conditionally sum transactions, and how to translate labels with a mapping table.

Microsoft introduced tables in Excel in 2007, and this feature is a game-changer. If you do not store your data in tables, you are missing out on a simple way to improve the reliability of your workbooks. Converting an ordinary range into a table provides many special benefits, and we’ll discuss these special properties in the course.

Also with the roll-out of Excel in 2007, Microsoft introduced a new worksheet function: SUMIFS. This too is a game-changer. Performing multiple-condition summing in earlier versions of Excel was challenging and required clever functions such as SUMPRODUCT or array formulas. We’ll discuss the function’s arguments and demonstrate several practical applications.

Excel reports often aggregate data. Frequently, the data labels are different than the report labels. For example, the accounting system may export account names, such as Cash or Savings, but the corresponding report labels are financial statement items, such as Cash and Cash Equivalents. In this course, we’ll demonstrate the mapping table technique which enables our reports to retrieve values from data tables, even when the labels are different.

The course includes practice Excel files, corresponding answers files, and a solutions video. If you use Excel to create reports, this course will help you prepare them more quickly.

Who this course is for:

  • This course is designed for accountants seeking to use Excel to complete their reporting tasks more quickly. It is not designed for Excel users that are already familiar with tables, the SUMIFS function, and the mapping table technique.
Get Course Now



Categories



Categories






Categories