Excel for Accountants: Mapping Tables
4.3 (1,774 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
27,274 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel for Accountants: Mapping Tables to your Wishlist.

Add to Wishlist

Excel for Accountants: Mapping Tables

Automate reporting tasks with a mapping table
4.3 (1,774 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
27,274 students enrolled
Created by Jeff Lenning
Last updated 10/2014
English
English [Auto-generated]
Price: Free
Includes:
  • 31 mins on-demand video
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • 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
View Curriculum
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 demonstrates 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 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 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. For many workbooks, these can be replaced with the SUMIFS function. 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. When the labels are different, we are prevented from using lookup functions such as VLOOKUP to retrieve values. 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. The course is structured by topic, and within each topic, there is a PowerPoint discussion, a lecture video that works through the item in Microsoft Excel 2013 for Windows, and sample exercises for you to work hands-on. If you use Excel to create reports, this course will help you prepare them more quickly.

Who is the target audience?
  • 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.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
+
Course Introduction
1 Lecture 01:23
This course is a presentation of the topics contained in the instructor's Journal of Accountancy article titled The Power of Mapping Tables. The course presents a clever Excel technique that can be used to streamline and automate recurring-use reporting workbooks.
Course Overview
01:23
+
Tables
2 Lectures 10:36
This lecture videos provides an overview of the table feature and explores several special table properties.
Introduction to Tables
04:05

This lecture video works through the related Excel exercises.

For more information on Tables, visit the Table Total Row blog post below. If you do...please let me know by posting a comment!

Tables Hands-On Exercises
06:31
+
Conditional Summing
2 Lectures 04:31
This lecture video provides an overview of the multi-condition summing function SUMIFS.
Introduction to Conditional Summing
01:41

This lecture video works through the related Excel exercises.

If you need to do SUMIFS with OR instead of AND logic, please visit the blog post below. If you check it out, please let me know by posting a blog comment!

Conditional Summing Hands-On Exercises
02:50
+
Mapping Tables
3 Lectures 12:57
This lecture provides an overview of the mapping table technique.
Introduction to Mapping Tables
02:22

This lecture video works through the related Excel exercises.
Mapping Tables Exercises
02:41

After you have worked on the Mapping Tables homework exercises, watch this lecture as I demonstrate how to complete the exercises and the extra credit exercise.
Solutions to Mapping Table Exercises
07:54
+
Course Recap
1 Lecture 01:14
Course Recap
01:14
About the Instructor
Jeff Lenning
4.4 Average rating
1,768 Reviews
27,274 Students
1 Course
Microsoft Certified Trainer

Award-winning instructor and author of Excel University, Jeff Lenning CPA has helped thousands of accountants work more efficiently with Microsoft Excel. Jeff is a Microsoft Certified Trainer and a Microsoft Certified Excel Expert. He has provided continuing education to CPAs for the past 10 years, and many of his Excel articles have been featured in industry publications such as the Journal of Accountancy and California CPA Magazine.

Highlights

Jeff began his career in the audit practice at Arthur Andersen. He was a senior financial analyst at Gateway computers and the accounting manager at Interpore Cross International. In 2000, he founded Click Consulting Inc., a technology consulting firm. In 2012, he launched Excel University.

Professional Involvement

He has served in a variety of leadership positions in the California Society of CPAs, including president of the CalCPA OCLB Chapter, chair of the CalCPA State Technology Committee, chair of the CalCPA OCLB Chapter Technology Committee, board member of the CalCPA OCLB Chapter, and a CalCPA Council Member.

He provides Excel training to Fortune 500 companies and is an instructor for the AICPA, the California CPA Education Foundation, and other organizations. A Microsoft Office Certified Excel Expert, he is a nationally recognized speaker and Microsoft Certified Trainer. He was recently recognized with the California CPA Education Foundation’s Award for Instructor Excellence.