Book

book-image

Excel University Series Overview

In today’s business world, Excel is a must have skill for accounting and finance professionals. The Excel University series focuses on the features, functions and techniques that enable you to get your work done faster. Whether you’re in public practice, industry, consulting, or not-for-profit, Excel University is an investment that pays handsome efficiency dividends. The series is written progressively, so start with Volume 1 and work through the sequentially. Each volume includes access to the related online resource library of Excel sample files and solutions videos.

Master your Excel skills and you will:

  • Improve process efficiency and personal productivity.
  • Save time each period with a streamlined workflow.
  • Discover ways to automate recurring processes.

Who needs Excel University?

  • Accounting professionals
  • CPAs
  • Bookkeepers
  • Other financial professionals
  • Students entering the job market

 


Excel University Volume 1

Excel University Volume 1 Cover
During live CPE training sessions, and through Excel articles featured in magazines such as the Journal of Accountancy and California CPA Magazine, Jeff Lenning CPA CITP has shown thousands of CPAs and accounting professionals across the country how to use Excel more effectively. Drawing on his experience as an auditor, a financial analyst in industry, an accounting manager at a public company, and a consultant, he has demonstrated how to leverage Excel in order to improve efficiency by reducing the time it takes to complete job tasks.

Volume 1 builds the foundation and covers content useful to accountants regardless of the type of work they do. Blown away by the power of Excel, readers are sure to find this series relevant, enlightening, and extremely easy-to-follow.

 

Volume 1 Topics

 

Opening Information

  • Chapter 1: Overview
  • Chapter 2: Book Conventions
  • Chapter 3: Excel Conventions
  • Chapter 4: How to Make the Most

Foundations

  • Chapter 5: Foundations Overview
  • Chapter 6: Selected Shortcuts
  • Chapter 7: Named References
  • Chapter 8: Tables
  • Chapter 9: Data Validation
  • Chapter 10: Conditional Formatting Basics
  • Chapter 11: Better Summing
  • Chapter 12: Skinny Row
  • Chapter 13: Hide Worksheets
  • Chapter 14: Highlight Input Cells
  • Chapter 15: Workbook Organization
  • Chapter 16: Worksheet Organization
  • Chapter 17: Nested Functions
  • Chapter 18: Selection Groups
  • Chapter 19: Workbook Design Principles

 

Volume 1 Resources

Downloads

Videos

Buy Volume 1

Media Type/Version

Excel 2010

Excel 2013

Paperback

amazon-icon-678 amazon-icon-678

Kindle

kindle-icon kindle-icon

Apple iTunes

ibooks-new-icon ibooks-new-icon

Excel University Volume 2

front_only_wide
Building on the firm foundation established in the first volume, the second installment in the Excel University series continues to explore the Excel features, functions and techniques relevant to accounting and finance professionals.  Volume 2 focuses on how to automate recurring-use reports.
Amounts in hands-free reports update the instant updated data is inserted into the workbook, for example, when an updated trial balance is pasted into the data sheet.  This volume covers the Excel items needed to build and automate reports, including lookups, dynamic headers, mapping tables, error trapping, conditional summing, and date related functions.
Excel practice files and solutions videos are available online so you can work hands-on as you read through the content.  The skills developed in this volume will help you get your work done in less time.

 

Volume 2 Topics

 

Opening Information

  • Chapter 1: Overview
  • Chapter 2: Selected Shortcuts

Conditional Summing

  • Chapter 3: Conditional Summing Basics with SUMIFS
  • Chapter 4: Remove Duplicates

Lookups

  • Chapter 5: Lookup Basics
  • Chapter 6: Improving VLOOKUP with MATCH
  • Chapter 7: Improving VLOOKUP with VALUE and TEXT
  • Chapter 8: Moving Beyond VLOOKUP with INDEX
  • Chapter 9: Trap Errors with IFERROR
  • Chapter 10: The IF Function
  • Chapter 11: List Comparisons
  • Chapter 12: The ISERROR Function
  • Chapter 13: Multicolumn List Comparisons with COUNTIFS
  • Chapter 14: Indentation
  • Chapter 15: Perform Lookups with SUMIFS

Date and Text Function Basics

  • Chapter 16: Determine the Last Day of the Month with EOMONTH
  • Chapter 17: Date Parts – MONTH and YEAR
  • Chapter 18: Build Your Own Date with DATE
  • Chapter 19: Concatenation Basics

Hands-Free Reporting

  • Chapter 20: Improve SUMIFS with CONCATENATE
  • Chapter 21: Dynamic Headers
  • Chapter 22: Horizontal Reports
  • Chapter 23: Mapping Tables
  • Chapter 24: Data Validation and Reporting
  • Chapter 25: Improving Error Check with Boolean Values and the Logical AND Function
  • Chapter 26: Concepts Applied

 

Volume 2 Resources

Downloads

Videos

Buy Volume 2

Media Type/Version

Excel 2010

Excel 2013

Paperback

amazon-icon-678 amazon-icon-678

Kindle

kindle-icon kindle-icon

Apple iTunes

Coming Soon Coming Soon

 

Excel University Volume 3

EUV3_front_240w
The third installment in the Excel University series picks up right where Volume 2 leaves off. In the final exercise of Volume 2, we created a formula-based report that required several features and functions. In Volume 3, we build that same report without writing a single formula. We create it with the PivotTable feature.

The first section of this volume covers PivotTable basics and concludes with a comparison between formula-based and PivotTable reports. The second section explores various report layout and design options that enable us to replace our formula-based reports with PivotTables when appropriate. In the final section, we consider the reporting process, including efficient ways to get data into our workbooks, how to prepare the data for use, and how to summarize it graphically with PivotCharts and other Excel features.

Excel practice files and solutions videos are available online so you can work hands-on as you read through the content. The skills developed in this volume will help you get your work done in less time.

Volume 3 Topics

 

Opening Information

  • Chapter 1: Overview
  • Chapter 2: Selected Shortcuts

PivotTable Fundamentals

  • Chapter 3: PivotTable Basics
  • Chapter 4: Row Fields
  • Chapter 5: Value Fields
  • Chapter 6: Column Fields
  • Chapter 7: Filter Fields
  • Chapter 8: Updating Data
  • Chapter 9: Groups and Subtotals
  • Chapter 10: Value Formats
  • Chapter 11: Report Type Comparison

Working with PivotTables

  • Chapter 12: Options
  • Chapter 13: Report Layout and Design
  • Chapter 14: Multiple Value Fields
  • Chapter 15: Calculated Fields
  • Chapter 16: Calculated Items
  • Chapter 17: Show Values As
  • Chapter 18: Sorting
  • Chapter 19: Filtering
  • Chapter 20: Hybrid Reports with GETPIVOTDATA

Obtain > Prepare > Summarize

  • Chapter 21: Web Data
  • Chapter 22: External Data
  • Chapter 23: Text Files
  • Chapter 24: Data Preparation
  • Chapter 25: Accounting System Data
  • Chapter 26: Getting Graphic

Volume 3 Resources

Downloads

Videos

Buy Volume 3

Media Type/Version

Excel 2010

Excel 2013

Paperback

Coming Soon amazon-icon-678

Kindle

Coming Soon kindle-icon

Apple iTunes

Coming Soon Coming Soon

 

The Strength is the Instructor

“The strength of this course is the instructor himself. He is an accountant who happens to be an MS Excel expert. He understands what accountants need from the program and what the program can do for accountants. This course and the following series will teach accountants how to use the MS Excel to improve productivity and efficiency.”

He Understands

 


Get access to the solutions videos and workbooks that complement your copy of Excel University when you order your copy