Excel for Accounting Course | Small Business Accounting - GoSkills
Excel for Accounting
Gain the essential Excel skills you need for accounting and financial analysis with our free Excel for Accounting course.
In 34 short lessons, learn how to use Excel for accounting like a pro, including how to import, clean, and validate data to ensure it’s free of errors. Master specific Excel formulas for accounting and finance, create tax and debt calculators, and so much more.
The course also includes free downloadable Excel accounting templates and exercises to get up and running quickly. By the end of the course, you’ll be able to leverage Microsoft Excel to boost performance in day-to-day accounting, bookkeeping, and financial analysis tasks.
Learn how to:
- Import and clean data in Excel.
- Master conditional formatting and its formulas.
- Validate data to ensure information is error-free.
- Use auto filters to display data that meet a certain criterion.
- Group and ungroup data.
- Master text, data, time, logic, and IF functions.
- Use conditional math to calculate data based on different criteria.
- Look up exact and approximate data with VLOOKUP, HLOOKUP, XLOOKUP.
- Extract, combine, and report data.
- Convert data into a table.
- Analyze data efficiently using PivotTables.
- Present data using charts.
- Create calculators for tax and debt.
Once enrolled, our friendly support team is here to help with any course-related inquiries.
Syllabus
Download syllabus-
1
Importing Data into Excel: Fixing Text and Numbers How to recognize when text and numbers imported incorrectly and how to use TRIM and FIND and REPLACE to fix errors. 6m
-
2
Importing Data into Excel: Fixing the Date Use the text to columns button to separate the different parts of the date and the DATE function to put it back together as a proper date. 5m
-
3
Importing Data into Excel: Get Rid of Blank Lines in Your Data The video shows two different ways to get rid of blank lines in a dataset and how to sort data without unique identifiers back into its original order. 6m
-
1
Conditional Formatting: Using the Icons This lesson illustrates the basic options of conditional formatting with 4 different examples. 5m
-
2
Conditional Formatting: Manage Rules and Use Formulas in Rules What if the conditional formatting menu options do not cater to what I want? Let's look at how to customize conditional formatting by way of creating a dynamic To-Do list. 6m
-
3
Data Validation: Preventing Garbage Input We look at how to use basic data validation techniques to reduce garbage input and avoid errors. 7m
-
4
Data Validation: Create a Dropdown List In this lesson, we look at how to use data validation to create a dropdown list in Excel to limit input to pre-set options. 4m
-
5
Auto Filters: Display Records That Meet a Certain Criterion In a dataset, use auto filters to extract lists based on text or date criteria. Let's look at how to sort using auto filters. 4m
-
6
Auto Filters: Combined with Conditional Formatting We examine how to filter using numerical criteria, conditional formatting, identify duplicate items, and use SUBTOTAL to add values in a filter. 4m
-
7
Grouping Data: Create Your Own Collapsible and Extendible Sections How to group data, and add your own expandable and collapsible sections to hide and unhide portions of data. 4m
-
8
Grouping Data: Remove Groupings, Fix Borders and Copy Visible Data Let's look at how to remove created groupings, copy only visible data, and fix borders on cells to work with grouping. 6m
-
1
Text Functions In this lesson, we learn how to use LEN, FIND, SEARCH, LEFT, RIGHT and MID. 4m
-
2
Text Functions: Combine In this lesson, we'll use LEN, FIND, SEARCH, LEFT, RIGHT, and MID in combination to sort out more complex issues. 3m
-
3
Date Functions: Understanding How Dates Work We learn how to use dates as serial numbers, TODAY(), EOMONTH, WORKDAYS, and NETWORKDAYS. 5m
-
4
Time: Using Time in Calculations We examine a timesheet and a payroll calculation to illustrate time as part number, how to format time, and how to calculate gross pay based on time and an hourly rate. 5m
-
5
Logic Functions: Using Logic to Supply TRUE or FALSE as a Result We'll look at logic functions, including: >, <, =, <>, AND and OR. We also test single or double criteria, and illustrate the MONTH function. 7m
-
6
IF and IFS Functions: If You Want a Specific Result to a Logic Test In this lesson, we learn the basic uses of IF and IFS and how to combine it with AND()/OR(). 6m
-
7
Conditional Math: Calculations Based on a Single Column Criterion In this lesson, we learn the basic uses of SUMIF() and COUNTIF(). 5m
-
8
Conditional Math: Calculations Based on Multiple Columns as Criteria Let's look at how to use SUMFIS and COUNTIFS to calculate based on more than one criterion column and add numbers per month. 5m
-
9
Lookups: VLOOKUP and HLOOKUP Let's learn the basic uses of VLOOKUP and HLOOKUP by building your own invoice template. 5m
-
10
Lookups: INDEX and MATCH In this lesson, we learn the basic uses of INDEX and MATCH by building your own invoice template. 5m
-
11
Lookups: XLOOKUP In this lesson, we learn the basic uses of XLOOKUP by building your own invoice template. 3m
-
12
Lookups: Approximate Search for all Lookups How does an approximate lookup work? We look at one example to show VLOOKUP, INDEX & MATCH and XLOOKUP. 5m
-
1
Tables: How to Convert Data into a Table We learn about prepping data for a table, converting the data to a table, and the benefits of a table. 6m
-
2
Structured References in Tables What's up with the funny formula references in tables? What does it mean and how does it work? We learn the basics of using structured references. 6m
-
3
Power Query: Alternative Way to Get Data Into Excel What are the basics you have to check when you use Power Query? We learn how to get data into Excel in a much more effective manner than copy and paste. 6m
-
4
Pivot Tables: Analyze Data Quick and Easy We learn the basics of a pivot table, how to create a pivot, refresh and format. 7m
-
5
Charts: Basics In this lesson, we examine how to create a chart, chart elements, and format elements. 7m
-
6
Prepare to Print Learn how to prepare your file to be printed, page break previews, page layout options, and headers and footers. 5m
-
1
Repayment Calculator: Calculate Payments In this lesson, we calculate payments (PMT) using financial functions. 5m
-
2
Repayment Calculator: Create an Amortization Table Learn how to create an amortization table to show the interest and balances for each period. 6m
-
3
Tax Calculator: Understanding a Tax Scale In this lesson, we learn how to use Excel formulas to calculate your tax liability. 4m
-
4
Tax Calculator: Create Your Own In this lesson, we create a lookup table and use lookup formulas to calculate tax liability. 5m
Certificate
Certificate of Completion
Awarded upon successful completion of the course.
Instructor
Lydia Uys
Lydia is a Chartered Accountant with experience in financial management, lecturing, and consulting.
She first trained as an external auditor and then proceeded to work as a financial manager, consultant, and a tertiary lecturer and mentor for students undertaking a Bachelor of Commerce Accounting degree at the University of the Western Cape, South Africa.
Lydia Uys
Course design and review
Accreditations
Link to awards
FAQs
Who is this course for?
This course is ideal for accountants, small business owners, freelancers, and finance professionals who want to improve their Excel for accounting skills.
Some knowledge of accounting is recommended. No previous experience using Excel for accounting is required.
Is Excel important for accountants?
While the number of tools and resources that are available for accountants continue to expand, Excel remains one of the profession’s most widely used tools.
63% of American companies consider Excel a vital accounting tool, according to a study by Robert Half, the world’s largest accounting and finance staffing firm. When looking at smaller accounting companies, this percentage goes even higher.
What Excel skills do accountants need?
Accountants need to be able to use Excel efficiently to analyze data for budgets, prepare financial statements, forecast financial performance, and perform complex calculations.
Here are some key Excel skills for accountants that you will learn in this course:
- Turn data into tables
- Format and group data correctly
- Import, clean, and validate data
- Master key formulas including text, data, time, logic, and IF functions
- Use Pivot Tables to analyze data
- Create visually appealing charts
- Use PowerQuery to import and clean data
What are the most commonly used formulas in Excel for accounting?
VLOOKUP, HLOOKUP, IF (and its variants), and INDEX MATCH are some of the most commonly used formulas in Excel for accounting.
In this course, you will learn how to use these key formulas as well as additional text, data, time, and logic functions like FIND, SEARCH, MID, LEN, CONCATENATE, EOMONTH and more.
Do you have any Excel accounting templates?
The GoSkills Excel for Accounting course includes several free templates that you can download and use immediately to quickly get up and running.
By the end of this course, you will also be able to create your own functional tax and debt repayment calculators.
Do you have other Excel and finance courses?
We have a range of Excel courses and Finance courses for all skill levels.
Excel courses:
- Basic Excel
- Advanced Excel
- Basic & Advanced Excel
- Pivot Tables
- Macros and VBA
- Dashboards
- Power Query
- Power Pivot
- Power BI
Finance courses:
Can I train my team with GoSkills?
Absolutely. We work with many businesses, big and small, to train and upskill their staff. Get started here or contact us with your requirements.
How GoSkills helped Chris
I got the promotion largely because of the skills I could develop, thanks to the GoSkills courses I took. I set aside at least 30 minutes daily to invest in myself and my professional growth. Seeing how much this has helped me become a more efficient employee is a big motivation.
