CPE PACKAGES (Incl. Ethics) for Multiple States and Qualifications @ $4/credit. CLICK HERE to view.

11 Timesaving Worksheet Functions

You need to register for the webinar to watch the video.

Click Here to Register

Before starting this self study program, please go through the instructional document.

Overview

  • Excel Versions
    3 mins
  • VLOOKUP Text vs. Numbers Causes
    13 mins
  • Comparing Cells Without using IF
    24 mins
  • IFS Function
    39 mins
  • SUMIF Introudction
    48 mins
  • SUMPRODUCT as CUMIF Alternative
    70 mins

Course Description

Microsoft Excel offers hundreds of worksheet functions, so how do you know which ones can save you time? In this webinar, Excel expert David H. Ringstrom, CPA, has chosen a selection of worksheet functions that will help you work more efficiently. 

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the Microsoft 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.

  • You will learn how to make decisions with the IF function or streamline complex decision-making with IFS. 
  • Identify the largest or smallest number from a list, either specifically or based up on criteria. 
  • Lookup functions such as VLOOKUP can safe significant amounts of time, but you can go farther with functions like SUMIF, SUMFS, SUMPRODUCT, and XLOOKUP.
  • Microsoft 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.

Topics covered:

  • Comparing the MIN, SMALL, MAX, and LARGE functions.
  • Comparing two cells directly to return TRUE or FALSE if they’re exactly the same or not without using IF.
  • Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces.
  • Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts.
  • Employing the SUMIF function to sum values related to multiple instances of criteria you specify.
  • Explaining the new XLOOKUP worksheet function in Microsoft 365.
  • Extending the functionality of the SUMIF function by incorporating wildcard characters within a formula.
  • Identifying situations where VLOOKUP may return #N/A instead of a value.
  • Improving the integrity of spreadsheets by using SUMIF to look up values in a more flexible fashion than VLOOKUP.
  • Incorporating comparison operators within SUMIF to sum numbers based on range criteria, such as greater than, less than, and so on.
  • Incorporating decisions into calculations with Excel's IF function.
  • Learning about the MAXIFS function available in certain versions of Excel.
  • Learning about the MINIFS function available in Excel 2019 and Microsoft 365.
  • Reconstructing spreadsheet data to use VLOOKUP as a better alternative to nesting IF functions.
  • Returning multiple columns of data with XLOOKUP from a single formula by using Microsoft 365's dynamic array functionality.
  • Streamlining the decision-making process with the IFS function in Excel 2019 and Microsoft 365 (formerly Microsoft 365 (formerly Office 365)).
  • Summing results from multiple columns with a single XLOOKUP function nested within a SUM function.
  • Understanding how the VLOOKUP function allows you to look up data instead of having to manually reference individual cells.
  • Understanding the risks and complications of nesting too many levels of IF functions.
  • Using the SUMIFS function to sum values based on multiple criteria.
  • Using VLOOKUP to perform approximate matches.
  • Viewing the pros, cons, and limitations of nesting IF functions.

This Webinar is useful for all the tax practitioners, Accountants, CPA to apply excel functions in their practice that saves their time without affecting accuracy.


Click to access more webinars on Excel Courses | Free CPE webinars | Free Ethics Courses

Learning Objectives

  • To define the arguments used with the VLOOKUP function.
  • To recall the character to use with SUMPRODUCT to separate criteria arguments when using the function to sum or count values.
  • To identify how the SUMIFS function returns if a match cannot be found.

Who Should Attend?

  • Accounting Firm
  • Accounting Managers
  • Accounting Practice Owners
  • Accounts Director
  • Chief Accounting Officer
  • Cloud Accountants
  • CPA (Industry)
  • CPA - Mid Size Firm
  • CPA - Small Firm
  • Entrepreneurial CPA
  • Senior Accountant
  • Staff of Accounting Firm
  • Young CPA

Testimonial

4.3

(3)
33%
67%
0%
0%
0%