Excel for Accountants: Volume 1

Learn how to complete your job tasks more quickly and build a strong foundation.
4.6 (15 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.
481 students enrolled
$95
Take This Course
  • Lectures 92
  • Length 3.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 12/2014 English

Course Description

Developed specifically for accountants, this course discusses the Excel features, functions, and techniques that are practical, relevant, and sure to save you time.

My Excel University series of books are available online in paperback and digital Kindle versions. My online Excel University courses teach the content of the books in video format. Now, for Udemy, I've combined the book text and the lecture videos of Excel University Volume 1 and made them both available in this Excel for Accountants Volume 1 course.

Course Format

Each course section will begin with the lecture video. You can work through the sample Excel file to practice. Each section also provides the text of the book which reinforces and enhances the content presented in the lecture video. I then provide additional resources and related Excel University blog posts and articles. These elements provide an effective training experience.

Instructor

Author and award-winning instructor, Jeff Lenning, is a certified public account and Microsoft certified trainer, and has helped thousands of accountants use Excel more efficiently.

What are the requirements?

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

What am I going to get from this course?

  • Recall at least 3 shortcuts to improve your speed
  • State how to name a cell with the name box
  • Recall the special properties of tables
  • Identy the feature that can create an in-cell drop down
  • State which feature will format a cell based on the value of a cell
  • Recognize which feature can simulate a bar chart
  • State why the SUBTOTAL function is preferred to the SUM function in many situations
  • Identify a technique that helps ensure that a new row inserted between the last data row and the formula row is included in the total row formula
  • State whether or not values on a hidden worksheet are available to formulas on other sheets
  • Recall the cell style name introduced for input cells
  • State the purpose of an error check worksheet
  • Identify whether or not it is a good idea to split the data from the report
  • Recognize at least three workbook design principles

What is the target audience?

  • This course is designed for accountants seeking to use Excel to complete their tasks more quickly.
  • This course is not designed for people seeking generic Excel training, as the features covered are relevant to accountants.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Chapters 1-5: Overview
Welcome!
Preview
Article
Chapters 1-5 Lecture Video
Preview
12:18
Conclusion and Additional Resources
Article
Chapters 1-5
2 questions
Section 2: Chapter 6: Selected Shortcuts
08:57

This chapter discusses my Top 5 favorite Excel shortcuts. We’ll cover others later, but for now, please commit the following five shortcuts to memory and begin using them immediately:

  • Arrow keys - Navigate worksheet; Ctrl=Jump; Shift=Extend.
  • F2 - Edit
  • F4 - Cell Reference
  • Doubleclick - Fill Down
  • Ctrl + PageUp/PageDown - Next worksheet; Previous Worksheet
Exercise 1 - Arrow Keys
Article
Exercise 2 - F2
Article
Exercise 3 - F4
Article
Exercise 4 - Double-Click
Article
Exercise 5 - Ctrl PageUp / PageDown
Article
Conclusion and Additional Resources
Article
Chapter 6 Quiz
2 questions
Section 3: Chapter 7: Named References
07:45

An Excel Named Reference is a friendly name that refers to a cell, range, or other item.

The benefits of using names are numerous:

  • Easier to navigate (select the name from the Name Box to instantly navigate to the range)
  • Easier to write formulas (because you don’t need to navigate to a sheet and select a range)
  • Easier to understand formulas (names make it easier to interpret function arguments)
  • Faster to insert names into formulas with the paste name (F3) key
  • Easier to define a Data Validation list source
  • Easier to define the source for PivotTables and charts

The simple way to insert a name is to highlight the cell or range, type the name in the Name Box, and then press the Enter key on your keyboard. Alternatively, you can enter a new name, as well as edit or delete an existing name, by activating the Name Manager.

Exercise 1 - Name a Cell Quickly
Article
Exercise 2 - Name a Range Quickly
Article
Exercise 3 - Navigate
Article
Exercise 4 - Name Manager
Article
Conclusion and Additional Resources
Article
Chapter 7 Quiz
2 questions
Section 4: Chapter 8: Tables
10:37

An Excel Table is essentially any range of data that has been defined as a Table. It is easy to convert a normal range of data into a Table by using the following command:

  • Insert > Table

Now that the normal range of data has been converted into a Table, it contains many special properties, including:

  • Table Name
  • AutoExpansion
  • Structured References
  • Automatic FillDown
  • Automatic Totals
  • Column Label Replacement
Exercise 1 - Table Name
Article
Exercise 2 - Auto-Expansion
Article
Exercise 3 - Structured References
Article
Exercise 4 - Automatic Fill-Down
Article
Exercise 5 - Automatic Totals
Article
Exercise 6 - Column Label Replacement
Article
Exercise 7 - Other Ribbon Settings
Article
Conclusion and Additional Resources
Article
Chapter 8 Quiz
2 questions
Section 5: Chapter 9: Data Validation
11:56

Excel's Data Validation feature allows you to control what a user can type in to a cell.

To apply Data Validation to a cell (or range), begin by selecting the cell (or range). Next, activate the Data Validation dialog box with:

  • Data > Data Validation

You’ll find that the Data Validation dialog box has three tabs, Settings, Input Message, and Error Alert.

  • The Settings tab is where you set the rule that defines a valid entry: for example, any whole number between 1 and 1000 or any date between 12/1/12 and 12/31/12.
  • The Input Message tab is where you optionally define a message that will pop up when the cell is selected: for example, “Please enter a whole number between 1 and 1000.”
  • The Error Alert tab is where you optionally define a message that will pop up when the user enters invalid data, for example: “Error: Please enter a whole number between 1 and 1000.”
Exercise 1 - Decimal
Article
Exercise 2 - Whole Number
Article
Exercise 3 - List on Same Sheet
Article
Exercise 4 - List on Different Sheet
Article
Exercise 5 - List with Named References
Article
Exercise 6 - List with Table
Article
Conclusion and Additional Resources
Article
Chapter 9 Quiz
2 questions
Section 6: Chapter 10: Conditional Formatting Basics
06:20

Excel's Conditional Formatting feature allows you to control the format of a cell based on the value of the cell. The basic idea is to first identify the cell to be conditionally formatted. Then, define the condition(s), or rule(s). Finally, define the format to apply when the cell value meets a condition.

To apply Conditional Formatting, simply highlight the cell or range of cells, and then activate the Conditional Format dialog box by using the following:

  • Home > Conditional Formatting
Exercise 1 - Greater Than / Less Than
Article
Exercise 2 - Equal To / Not Equal To
Article
Exercise 3 - Duplicate Values
Article
Exercise 4 - Data Bars
Article
Exercise 5 - Icon Sets
Article
Conclusion and Additional Resources
Article
Chapter 10 Quiz
2 questions
Section 7: Chapter 11: Better Summing
08:03

There is a function in Excel that is better for summing than the SUM function for accountants. In fact, I use it instead of the SUM function almost all of the time.

The SUBTOTAL function excludes other SUBTOTAL functions in its sum range.

SUM no more!

Exercise 1 - Balance Sheet
Preview
Article
Exercise 2 - Department Report
Preview
Article
Exercise 3 - SG&A
Preview
Article
Conclusion and Additional Resources
Preview
Article
Chapter 11 Quiz
2 questions
Section 8: Chapter 12: Skinny Row
07:14

The Skinny Row is any blank row between the data region and the formula row. The row height is decreased so that it is skinny. I typically use a row height of 4, but you can also use other values, such as 3, 2, 1 or .75. Since the row is skinny, it is not noticeable on printed versions of the sheet. After you create the Skinny Row, you must write your formula to include it.

Exercise 1 - Customer Sales
Article
Exercise 2 - Department Report
Article
Exercise 3 - End of Data Regions
Article
Exercise 4 - Skinny Columns
Article
Conclusion
Article
Chapter 12 Quiz
2 questions
Section 9: Chapter 13: Hide Worksheets
04:08

Sometimes, the workbook administrator may include notes, settings, assumptions, or other data that is not relevant to the user. In some cases, the productivity of the user may be increased when this extraneous information is hidden (that is, “out of sight, out of mind”).

It is often convenient to place your admin data on a worksheet, and then hide the worksheet so that it does not appear to a user. Once the sheet is active, navigate to the following command:

  • Home > Format > Hide & Unhide > Hide Sheet
Exercise 1 - Hide Admin Sheet
Article
Conclusion and Additional Resources
Article
Chapter 13 Quiz
2 questions
Section 10: Chapter 14: Highlight Input Cells
06:17

Identifying the cells that require a user’s input will result in workbooks that are more efficient, because they will take less time to update. Also, these workbooks will generally be more accurate because formula cells are less likely to be overwritten by user input.

Once the cells have been selected, apply the style by using the following command:

  • Home > Cell Styles > Input
Exercise 1 - Monthly Payment
Article
Exercise 2 - Journal Entry
Article
Exercise 3 - Reporting
Article
Exercise 4 - With Data Validation
Article
Conclusion and Additional Resources
Article
Chapter 14 Quiz
2 questions
Section 11: Chapter 15: Workbook Organization
09:25

Did you know that you could gain efficiency just by the way you organize the worksheets within your workbooks? There are a handful of workbook organization concepts, and we’ll work through them one by one, including the following:

  • Name All Sheets
  • No Extra Sheets
  • Start Here
  • ErrorCk
  • Admin
  • Match Data Flow
  • Color Code
  • Print Order
  • Index

Please note: there is no Answers version of the Excel file for this chapter.

Exercise 1 - Start Here Worksheet
Article
Exercise 2 - ErrorCk Worksheet
Article
Exercise 3 - QBData Worksheet
Article
Exercise 4 - Report Worksheet
Article
Conclusion and Additional Resources
Article
Chapter 15 Quiz
2 questions
Section 12: Chapter 16: Worksheet Organization
10:30

Setting up workbooks the right way helps to automate recurring processes and minimize the manual steps involved.

Let’s discuss several specific worksheet design concepts, including:

  • Split Data from Report Sheets
  • Flat Data
  • Clean Data
  • Uniform Data
  • Accommodate Minor Structure Changes
  • As It Comes
  • Subtotals Outside
  • Subtotals Above
  • Consistent Formulas in a Region
  • Sheet Title
  • Company Name
  • Column A for Labels; Begin Data in Column B
  • Sheet Names Short but Descriptive
  • Prefer Lookups to Direct Cell References
  • No Blank Columns
  • Consistent Headers

Please note: there is no Answers version of the Excel file for this chapter.

Exercise 1 - Flat
Article
Exercise 2 - Clean
Article
Exercise 3 - Uniform
Article
Exercise 4 - Split
Article
Exercise 5 - Subtotals Outside
Article
Exercise 6 - Subtotals Above
Article
Exercise 7 - Consistent
Article
Exercise 8 - Column A
Article
Conclusion and Additional Resources
Article
Chapter 16 Quiz
2 questions

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Jeff Lenning, 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.

Ready to start learning?
Take This Course