Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for inventory controllers

For inventory controllers and storekeepers recording stock in Excel, The Complete Excel Course with AI Integration supplies lessons to apply to a stock register, but no stock dataset. This guide selects work on clean, long item lists, conditional totals for balances and reorder flags, code-based item lookups, dates for slow movers and expiry, and PivotTables showing stock value by category and store.

See the plans Watch free lessons

Taught by Sagnik Bhattacharya and Virginia Thorn

The Complete Excel Course with AI Integration: Sagnik Bhattacharya and Virginia Thorn

What you will be able to do

At work, you will be able to:

  • Calculate balances for each item at each location with SUMIFS applied to receipts and issues in an Excel Table stock register.
  • Use data bars to display stock on hand, with IF and conditional formatting highlighting items whose balance reaches or falls below the reorder level.
  • Value the difference between a physical count and the system quantity after using XLOOKUP to add descriptions, units and unit costs to the count sheet.
  • Use last-movement and expiry dates to identify slow-moving and expiring stock through date arithmetic, TODAY and FILTER.
  • Review stock movements and value across categories and stores using PivotTables with slicers.
  • Prepare count sheets for printing with print areas and scaling, and maintain consistent item codes and units using drop-down lists, TRIM and Remove Duplicates.

Projects

What you will build.

The course has 14 projects. These four matter most here.

  • Excel PivotTables and a PivotChart of sales data (SalesPlusPerformance): net sales by product category as a column chart, with State, Region and City filters.

    Project 05

    SalesPlusPerformance

    A sales question answered in seconds, straight from the order data.

    Use slicers to switch stores while viewing stock value by store and category in a few clicks, then refresh the summary following the next count.

  • XLOOKUP formula in Excel (PeopleInsight): each employee row looks up its location from another sheet.

    Project 06

    PeopleInsight HR workbook

    Employee data joined across sheets with one formula.

    Enter an item code once on a count sheet and retrieve its description, unit and cost from the item list through lookups.

  • Project 02

    Dunder Michael Paper Company employee database

    Practise preparing a list for use and printing: freeze its headers, split codes into columns, trim unwanted spaces and remove duplicates. These clean-up tasks also apply to an item list.

  • Project 03

    Sales Goal Tracker

    Apply the project's conditional totals and counts to stock records: IF, SUMIFS and COUNTIFS provide the formula work behind balances from receipts and issues and flags for reordering.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 53 of them, 4 h 33 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 19 Excel tables

If entering data and maintaining a simple list are familiar, start with Lesson 19 to make the Excel Table that underpins a stock register. This path includes the whole of its opening section, Managing data in Excel, available free by email with practice files when requested on the course page. Beginners should start at Lesson 1. If you already use Tables for your lists, start the formula work at Lesson 28 or item-code lookups at Lesson 67.

Get the free section by email

53 of 186 lessons · 4 h 33 min

03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 9 lessons · 1 h 1 min Use the employee database exercises to prepare a stock list: organise it as an Excel Table with a total row, sort and filter it, and keep headers frozen. Clean descriptions with TRIM and Find and Replace, remove repeated codes with Remove Duplicates, and separate code components with Flash Fill and Text to Columns. Printing lessons help fit a count sheet onto the page; the list-handling work also applies to items.
  1. 19 Start here Excel tables Create and format a table, structured references and total row 8 min
  2. 20 Sorting data in Excel Single and multi-level sort on an employee database 6 min
  3. 21 Filtering data in Excel AutoFilter, text and number filters on employee data 10 min
  4. 22 Cleaning data in Excel TRIM, Paste Special values and Find and Replace 13 min
  5. 23 Remove duplicates in Excel Find and delete duplicate rows in an employee list 6 min
  6. 24 Flash Fill in Excel Split first and last names automatically 3 min
  7. 25 Text to Columns in Excel Split data by delimiter into separate columns 6 min
  8. 26 Freeze panes in Excel Lock header rows and columns while scrolling 3 min
  9. 27 Printing in Excel Print area, page orientation, scaling and page layout 6 min
04 Essential formulas and functions (IF, SUMIF and more) 13 lessons · 1 h 17 min The sales figures in these lessons provide practice for stock calculations. Build item and location balances with SUMIF and SUMIFS, count lines for a store with COUNTIFS, and calculate days since movement using TODAY and date arithmetic. IF supplies a reorder flag, while AND and OR let flags test two conditions.
  1. 28 Logical functions in Excel TRUE and FALSE comparisons for beginners 5 min
  2. 29 The IF function in Excel Pass or fail results with a single condition 3 min
  3. 30 IF function practice in Excel Categorise sales data with IF formulas 6 min
  4. 31 Nested IF and IFS in Excel Customer value tiers with nested IF 6 min
  5. 32 Nested IF and IFS in Excel Sales performance labels with the IFS function 5 min
  6. 33 AND and OR with IF in Excel Flag high-value deals with multiple conditions 8 min
  7. 34 SUM, COUNT and COUNTA in Excel SUM with FILTER and counting numbers with COUNT 7 min
  8. 35 SUM, COUNT and COUNTA in Excel COUNTA, unique counts and completeness checks 4 min
  9. 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  10. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
  11. 38 The Excel function library Insert Function dialog and Formula AutoComplete 5 min
  12. 39 Date and time functions in Excel TODAY, NOW, MONTH and date arithmetic 6 min
  13. 40 Excel Ninja Challenge COUNTIF, SUMIF, IF, COUNTIFS, SUMIFS and IFS practice 8 min
05 Text functions and data validation (enhancing data quality) 7 lessons · 35 min Make item codes and descriptions consistent by correcting case with UPPER and PROPER, replacing text with SUBSTITUTE, and separating it with Text to Columns. CONCAT and TEXTJOIN combine text, while drop-down lists keep entries for units and stores consistent.
  1. 41 CONCAT, the & operator and TEXTJOIN in Excel Combine text from multiple cells 6 min
  2. 42 TRIM, UPPER, LOWER and PROPER in Excel Clean messy text and fix capitalisation 6 min
  3. 43 FIND, SEARCH and SUBSTITUTE in Excel Locate and replace text with formulas 5 min
  4. 44 Text to Columns wizard in Excel Split a column by a comma delimiter 4 min
  5. 45 Data validation in Excel Create drop-down lists for consistent data entry 4 min
  6. 46 Ninja Challenge: problem Clean messy data with text functions and validation 4 min
  7. 47 Ninja Challenge: solution PROPER, Text to Columns and drop-down lists 7 min
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min Turn stock movements into summaries of value and movement by month, category and store with PivotTables, then explore them with slicers and PivotCharts. Refresh the results after a count. The exercises summarise sales orders, and their approach also works for a stock movement table.
  1. 57 PivotTables 101 in Excel What a PivotTable does and the sales dataset 5 min
  2. 58 Preparing data for PivotTables Clean data with TRIM, PROPER, find and replace 5 min
  3. 59 Create your first PivotTable in Excel Insert a PivotTable from a sales table 1 min
  4. 60 PivotTable fields in Excel Rows, values, columns and Value Field Settings 2 min
  5. 61 PivotTable summaries in Excel Sales by product category and grouping by date 4 min
  6. 62 Slicers in Excel Filter PivotTables with clickable buttons 3 min
  7. 63 PivotCharts in Excel Create interactive charts from a PivotTable 5 min
  8. 64 PivotTable design Tab Report layouts, PivotTable styles and number formats 5 min
  9. 65 Refresh a PivotTable in Excel Update pivots when source data changes 2 min
  10. 66 Excel Ninja Challenge PivotTable sales by region, category and monthly trend 1 min
08 Advanced Excel functions: lookups, text and arrays 12 lessons · 54 min Retrieve descriptions, units and costs from an item list with XLOOKUP, VLOOKUP or INDEX and MATCH, and handle missing codes with IFERROR. Extract code components using LEFT, RIGHT and MID. For date and stock reviews, apply TODAY and EOMONTH to expiry and use UNIQUE, FILTER and SORT to produce a slow-mover list.
  1. 67 Lookup functions in Excel The PeopleInsight HR dataset and why lookups matter 2 min
  2. 68 VLOOKUP in Excel Find employee data across sheets with exact match 4 min
  3. 69 XLOOKUP in Excel Exact match, if not found and two-way lookup 6 min
  4. 70 IFERROR in Excel Handle lookup errors in VLOOKUP and XLOOKUP formulas 3 min
  5. 71 INDEX and MATCH in Excel A flexible alternative to VLOOKUP 6 min
  6. 72 CONCAT and TEXTJOIN in Excel Combine employee names and text with a delimiter 3 min
  7. 73 LEFT, RIGHT and MID in Excel Extract parts of a text code like DAL-SAL-0001 7 min
  8. 74 TODAY, EOMONTH and NETWORKDAYS in Excel Date calculations for HR data 5 min
  9. 75 UNIQUE function in Excel Extract a distinct list with dynamic arrays 6 min
  10. 76 FILTER function in Excel Dynamic array filtering with AND and OR criteria 5 min
  11. 77 SORT and SORTBY in Excel Sort data dynamically with formulas 4 min
  12. 78 Excel Ninja Challenge XLOOKUP, totals and text concatenation lookup challenge 3 min
09 Project: task automation with data validation and forms 1 of 9 lessons · 8 min TaskFlow teaches conditional formatting through completed and overdue tasks. Apply its date-based row colouring to stock that has passed its expiry date, and use conditional formatting to highlight balances below the reorder level.
  1. 82 Conditional formatting in Excel Highlight overdue and completed tasks 8 min
09 Project: task automation with data validation and forms 1 of 9 lessons · 6 min The TaskFlow work on icon sets and data bars can show stock levels visually: bars represent quantities on hand, and icons distinguish low, medium and healthy levels.
  1. 84 Data bars and icon sets in Excel Visual priority and progress in TaskFlow 6 min

Instructors

Who teaches it.

Virginia Thorn smiling at the camera, dark hair tied back, wearing a navy top.

Instructor, Excel with AI

Virginia Thorn

Emmy Award winner. Teaches Excel with AI at Coding Liquids.

Questions

Asked before starting.

Which Excel course covers stock register skills for inventory controllers and storekeepers?

The Complete Excel Course with AI Integration from Coding Liquids covers the Excel skills used in a stock register. Its exercises use an employee database, sales orders and an HR workbook rather than stock data. You can apply Tables and data cleaning to item lists, SUMIFS to balances, IF and conditional formatting to reorder flags, XLOOKUP to item codes, and PivotTables to stock value. The selection on this page contains 53 lessons, taking 4 hours 33 minutes out of the course's 16 hours.

Can a storekeeper build an Excel stock register that flags when to reorder?

Yes. You can combine skills taught in The Complete Excel Course with AI Integration to record receipts and issues in an Excel Table, calculate balances by item and location with SUMIFS, and compare those balances with reorder levels using IF. Conditional formatting colours a row when stock is low. There is no ready-made stock template in the course, so you arrange the columns to suit your store.

Can inventory controllers use the course to compare physical counts with system stock?

Yes, for the Excel calculations. Apply the XLOOKUP work in The Complete Excel Course with AI Integration to retrieve system quantities and unit costs by item code on a count sheet. Subtract to find the quantity difference, multiply to value that difference, and use IFERROR to flag codes absent from the system. You still need to investigate the reasons for count discrepancies.

Does the course cover inventory methods such as FIFO, EOQ and safety stock?

No. Inventory management methods are outside The Complete Excel Course with AI Integration: it does not cover FIFO or weighted average valuation, EOQ or safety stock models, or stock modules in ERP systems or Tally. It teaches Excel formulas that can carry out a method you already understand after you set that method up.

Excel with AI

See the whole course.

The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.

See the plans Watch free lessons

The same course

For another job, level or topic.

By job

Finance and accounting
HR and office administration
Operations and supply chain
Data and reporting
Sales, marketing and customer service
Business and management
Engineering, construction and manufacturing
Retail, property and hospitality
Healthcare and life sciences
Education and careers
Public sector, non-profit and legal
Technology

By level and topic