Excel for Accounting

How to recognize when text and numbers imported incorrectly and how to use TRIM and FIND and REPLACE to fix errors.

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.

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.

This lesson illustrates the basic options of conditional formatting with 4 different examples.

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.

We look at how to use basic data validation techniques to reduce garbage input and avoid errors.

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.

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.

We examine how to filter using numerical criteria, conditional formatting, identify duplicate items, and use SUBTOTAL to add values in a filter.

How to group data, and add your own expandable and collapsible sections to hide and unhide portions of data.

Let’s look at how to remove created groupings, copy only visible data, and fix borders on cells to work with grouping.

In this lesson, we learn how to use LEN, FIND, SEARCH, LEFT, RIGHT and MID.

In this lesson, we’ll use LEN, FIND, SEARCH, LEFT, RIGHT, and MID in combination to sort out more complex issues.

We learn how to use dates as serial numbers, TODAY(), EOMONTH, WORKDAYS, and NETWORKDAYS.

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.

We’ll look at logic functions, including: >, <, =, <>, AND and OR. We also test single or double criteria, and illustrate the MONTH function.

In this lesson, we learn the basic uses of IF and IFS and how to combine it with AND()/OR().

In this lesson, we learn the basic uses of SUMIF() and COUNTIF().

Let’s look at how to use SUMFIS and COUNTIFS to calculate based on more than one criterion column and add numbers per month.

Let’s learn the basic uses of VLOOKUP and HLOOKUP by building your own invoice template.

In this lesson, we learn the basic uses of INDEX and MATCH by building your own invoice template.

In this lesson, we learn the basic uses of XLOOKUP by building your own invoice template.

How does an approximate lookup work? We look at one example to show VLOOKUP, INDEX & MATCH and XLOOKUP.

We learn about prepping data for a table, converting the data to a table, and the benefits of a table.

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.

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.

We learn the basics of a pivot table, how to create a pivot, refresh and format.

In this lesson, we examine how to create a chart, chart elements, and format elements.

Learn how to prepare your file to be printed, page break previews, page layout options, and headers and footers.

In this lesson, we calculate payments (PMT) using financial functions.

Learn how to create an amortization table to show the interest and balances for each period.

In this lesson, we learn how to use Excel formulas to calculate your tax liability.

In this lesson, we create a lookup table and use lookup formulas to calculate tax liability.