Excel University Book Series

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.


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

 

 


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

 

 

 


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

 

 


Excel University Volume 4

The fourth installment in the Excel University series covers the Excel features, functions, and techniques that will help you build reliable workpapers.
The first part of this volume explores a variety of useful features, including outlines and protection. We discover that formatting choices impact productivity and learn how to use accounting underlines and custom number formats. We also use hyperlinks to help organize our digital files.
The second part examines numerous functions that help make our workbooks efficient to maintain and update each period. Our examination includes lookup functions, text functions, date functions, and alternatives to the IF function.
The third part discusses several techniques that combine multiple features and functions. We revisit topics presented in prior volumes in more depth, including conditional formatting, names, wildcards, and tables.
The fourth part applies what we know to common accounting and finance tasks. We streamline our journal entries, budgets, and a variety of other common workpapers. The items covered in this volume will help improve your productivity so you can get your work done faster!

Volume 4 Topics

 

Opening Information

  • Chapter 1: Overview
  • Chapter 2: Selected Shortcuts

Features

  • Chapter 3: Sorting and Filtering
  • Chapter 4: Outlining
  • Chapter 5: Formatting
  • Chapter 6: Printing
  • Chapter 7: Protecting
  • Chapter 8: Hyperlink Feature

Functions

  • Chapter 9: HYPERLINK Function
  • Chapter 10: IF Alternatives
  • Chapter 11: Lookup Functions
  • Chapter 12: Text Functions
  • Chapter 13: Date Functions
  • Chapter 14: Insert Function

Techniques

  • Chapter 15: Ribbon Review
  • Chapter 16: Conditional Formatting Revisited
  • Chapter 17: Names Revisited
  • Chapter 18: Wildcards Revisited
  • Chapter 19: Tables Revisited
  • Chapter 20: Start Here Revisited

Applications

  • Chapter 21: JE Preparation
  • Chapter 22: Budget Ideas
  • Chapter 23: Common Workpapers