Advanced Accounting Excel

Functions, Constraints, and Advanced Reports

Accounting & Financial Management 4.4(91)4h 3m24 lectures2 sections

What you'll learn

Use conditional sum, count, and average functions
Use the lookup functions Vlookup, INDEX, and MATCH
Create pivot tables and Slicer filters
Record macros to save time and effort
Use Goal seek, Solver, and Scenario to analyze decisions
Analyze the break-even point
Apply conditional formatting and advanced filtering
Build a complete accounting system in Excel

About this course

This course teaches advanced Excel for accounting, from conditional functions all the way to building a complete accounting system. You start with conditional sum, count, and average functions such as Sumif, Countif, and Averageif, then the nested IF function and the lookup functions Vlookup, Hlookup, INDEX, and MATCH, the Aggregate function that skips errors, and the types of cell references. Next you move to pivot tables, Slicer filters, database functions, and macros, then analysis tools such as Scenario, Goal seek, Solver, and break-even analysis. The course also covers conditional formatting with formulas, advanced filtering, custom lists, and advanced printing. It ends with a practical application on a complete accounting system from A to Z in two parts, with the files used available to download. It suits accountants, commerce students, and anyone who works with accounts and financial reports, and some basic Excel knowledge is recommended. By the end, you will be able to build accurate accounting files, analyze financial data, and prepare reports quickly.

Expected outcomes

Ability to apply advanced accounting tools in Excel.
Create accurate and effective accounting tables and reports.
Analyze financial data to make informed decisions.
Design a comprehensive accounting system tailored to business needs.

Learner reviews

These reviews are from learners who bought the content and completed at least 50% of it — there are no reviews from anyone who hasn't gone through the material.

4.4

★★★★☆

91 reviews

5★

4★

3★

2★

1★

★★★★★Mahmoud I.·منذ 2 شهرشاهد 100٪
★★★★★اسماعيل ع.·منذ 4 شهرشاهد 50٪
حلوةةة جدا
★★★★★khaled s.·منذ 4 شهرشاهد 50٪
شرح فوق الرائع واضح جدا وبسيط ويسهل علي اي حد فهمه من اول مره ..#كورس_تحفه
★★★★★ahmed A.·منذ 4 شهرشاهد 100٪
دورة مفيدة للمحاسب المبتدى

تم عرض كل التقييمات (4)

Course content

Advanced Accounting Excel

22 lectures

Introduction and Course TopicsIntroduction to the advanced accounting Excel course, outlining its topics: advanced math and lookup functions, pivot tables, What-If analysis, conditional formatting, advanced filter, break-even point, and a full accounting cycle.5:25
Advanced Sumif/Sumifs FunctionsExplains the Sumif and Sumifs functions for summing values by one or several conditions, with examples on salesperson and product sales, and the difference in argument order between the two.13:12
Calculating Average with One or More Conditions Averageif/AverageifsExplains the Averageif and Averageifs functions for calculating an average by one or several conditions, such as a salesperson's or a region's average sales, and how the results support decision-making.6:29
Advanced Countif/Countifs FunctionsExplains the counting functions Countif, Countifs, Countblank and Counta, for counting cells by one or more conditions, empty cells, or text cells, with examples on a staff survey and salesperson data.6:20
Logical IF Function (Simple and Compound)Explains Excel's logical IF function, in its simple form that tests a condition with two outcomes and its nested form for several possibilities, with examples on expenses versus budget and grade ratings.10:42
Vlookup/Hlookup FunctionsExplains the lookup functions Vlookup and Hlookup, their arguments, column or row index, naming a range, and the difference between exact and approximate match, with a tax-rate table example.11:04
Aggregate Function That Can Bypass ErrorsExplains the Aggregate function, which calculates while ignoring errors or hidden rows, and compares it with Sum, which isn't affected by hiding or filtering, clarifying the function number and option.7:48
INDEX/MATCH Functions and How to Combine ThemExplains the Index and Match functions separately, then combines them to search large tables, where Match finds the row number and Index returns the value from the required column.11:07
Types of Cell ReferencesExplains the types of cell references in Excel: relative, absolute with the dollar sign, and mixed references that lock the column or the row, and when to use each when dragging a formula.12:16
Pivot Table & SlicerExplains the Excel Pivot Table for summarizing a large sales table by sales rep and region, turning results into a chart, and using a Slicer to filter data and adjust its look.15:12
Database FunctionsExplains Excel database functions that start with the letter D, how to sum or find the minimum value by set criteria, and how to link criteria to a dropdown list for easier use.11:33
MacrosExplains Excel macros as a way to record repeated steps and run them all at once with a keyboard shortcut, with a practical example of building an invoice and running it on a new sheet.6:42
Using the Scenario Analysis Tool for Forecasting and Deriving Potential OutcomesExplains the Scenario Manager in Excel's What If Analysis for predicting how changing inputs affects results, using an employee budget example, creating scenarios and comparing them with Summary.8:46
Goal SeekExplains Excel's Goal Seek for reaching a target value by changing a single cell, with savings and net profit examples, and why it cannot change a cell that contains a formula.6:05
PrintingCovers advanced printing settings in Excel: margins, paper orientation and size, previewing page breaks, repeating column headers on every page, and adding a Header and Footer.8:36
Advanced Conditional Formatting Using FormulasExplains advanced conditional formatting in Excel using formulas, such as highlighting only text cells and coloring Friday and Saturday dates, and why a relative cell reference matters in the formula.8:49
Advanced FilterExplains Excel's Advanced Filter using a separate criteria table, such as a rep's sales in a given governorate above a set amount, and the requirement that column headers match exactly.9:03
Custom ListExplains creating Custom Lists in Excel, such as Arabic weekdays and months, so the drag-fill feature repeats them automatically, and how to add them from the file options.7:28
SolverExplains the Excel Solver add-in for resource allocation and deciding product quantities to hit a sales target, enabling it from Add-ins, and setting the objective cell, variable cells and constraints.7:04
Breakeven PointExplains breakeven analysis, the point where revenue equals costs, calculating it in Excel with a table of rising production volumes and charting it where the revenue and cost lines meet.10:48
Integrated Accounting System from A to Z (Part One)Builds a complete accounting system in Excel for a sample company, covering the chart of accounts, the journal, entry numbering, a linked general ledger, and accounting-style number formatting.29:51
Integrated Accounting System from A to Z (Part Two)Completes the Excel accounting system with the trial balance and its balance check, then the income statement and final statements, adding entries like depreciation and credit purchases that flow through automatically.28:59