All Articles

00:00:00 Track who owes you how much and  for how long and also how much you owe to others. This is the latest version  of my accounts receivables and payables tracker with a bunch of powerful  new features. So let's check it out. Hi everyone. Welcome to this latest  version of my accounts receivables and payables tracker. This tracker now has dashboards  to give you a bird's eye view of who owes you how much and for how long. You have a main  dashboard which shows you Accoun

Overview In this session, we will build a fully functional invoice template in Excel. By the end of this exercise, you will be able to: Design a professional invoice layout Use formulas to calculate totals and VAT Apply conditional logic Format a clean business document Prepare a reusable invoice template STEP 1 – Set Up the Workbook Open Excel. Click Blank Workbook . Save the file as: Invoice Template Training.xlsm (If using macros, save as .xlsm. If not, .xlsx is fine.) Re

Macros in Excel are used to automate repetitive tasks . Instead of performing the same steps repeatedly, a macro records your actions and allows you to replay them with a single click. Macros save time, improve efficiency, and reduce manual errors. What Is a Macro? A macro is a recorded sequence of actions stored in Excel using VBA (Visual Basic for Applications) . When you record a macro, Excel captures every click, format change, and command you perform — and turns it into

An Excel Dashboard  is a visual summary of key information, designed to present data clearly and quickly. Instead of scrolling through rows of numbers, a dashboard displays charts, totals, and performance indicators all in one place. Dashboards help turn data into decisions. What Is an Excel Dashboard? A dashboard is typically a single worksheet that includes: Key performance indicators (KPIs) Charts and graphs PivotTables or summary tables Slicers for interactive filtering C

Power Query  is one of Excel’s most powerful tools for importing, cleaning, and transforming data. It allows you to automate repetitive data tasks and prepare large datasets quickly and accurately. If you regularly copy, clean, or combine data, Power Query can save you hours of manual work. What Is Power Query? Power Query is Excel’s data transformation and automation tool. It allows you to: Import data from multiple sources Clean and reshape messy data Combine multiple files

PivotTables are powerful on their own — but Advanced PivotTables  take your data analysis to a professional level. They allow you to perform deeper analysis, create dynamic reports, and build interactive dashboards with ease. If you work in finance, operations, or reporting, advanced PivotTable skills are essential. What Makes a PivotTable “Advanced”? Advanced PivotTables go beyond simple totals. They include features such as: Calculated Fields Grouping (Dates and Numbers) Cu

The SORT  function is a modern Excel formula that allows you to sort data automatically using a formula. Unlike manual sorting, SORT creates a dynamic, live version of your data that updates whenever the source data changes. It’s a simple but powerful tool for building professional reports and dashboards. What Does the SORT Formula Do? SORT arranges data in ascending or descending order based on a column or row you specify. For example: Sort sales from highest to lowest Arran

The UNIQUE  function is a modern Excel formula that extracts distinct values from a list. It allows you to remove duplicates automatically and create clean, dynamic lists without manual sorting or filtering. If you work with large datasets, UNIQUE is a simple but powerful tool. What Does the UNIQUE Formula Do? UNIQUE returns a list of values that appear only once in a range — removing duplicates automatically. For example: Generate a list of unique departments Extract distinc

The FILTER  function is one of Excel’s modern dynamic array formulas. It allows you to extract and display only the data that meets specific criteria — automatically and instantly. Instead of manually filtering or copying data, FILTER creates a live results table that updates as your data changes. What Does the FILTER Formula Do? FILTER returns rows from a range or table that meet conditions you define. For example: Show only overdue invoices Display sales for a specific regi

The combination of INDEX and MATCH  is one of the most powerful lookup methods in Excel. While many users rely on VLOOKUP, INDEX + MATCH offers greater flexibility, accuracy, and control — especially in advanced spreadsheets. If you want to move from intermediate to advanced Excel skills, this is an essential technique to master. Step 1: Understanding MATCH (Beginner Level) The MATCH  function finds the position of a value within a range. Structure: =MATCH(lookup_value, looku

The SUMIFS  function in Excel allows you to add values based on multiple conditions . It is an advanced version of SUMIF and is widely used in finance, reporting, and data analysis roles. If you need to calculate totals that meet more than one requirement, SUMIFS is the solution. What Does SUMIFS Do? SUMIFS adds numbers that match all specified criteria . For example: Total sales for January For the Finance department Where the amount is over £1,000 Instead of filtering manua

The IFERROR  function in Excel helps you manage and clean up error messages in your formulas. Instead of displaying errors like #DIV/0! , #N/A , or #VALUE! , IFERROR allows you to control what appears instead. This makes your spreadsheets look more professional and easier to understand. What Does IFERROR Do? IFERROR checks whether a formula results in an error. If there is no error , it returns the formula result. If there is an error , it returns a value you choose. The Stru

Excel’s IF function  is powerful on its own — but when combined with AND , OR , and nesting, it becomes a highly effective decision-making tool. These functions allow you to create formulas that evaluate multiple conditions and return results automatically. 1️⃣ The IF Function (Quick Recap) The basic structure of IF is: =IF(logical_test, value_if_true, value_if_false) Example: =IF(A2>=50,"Pass","Fail") If the value in A2 is 50 or more, Excel returns “Pass”. Otherwise, it retu

Understanding Relative and Absolute Cell References  is one of the most important fundamentals in Excel. It ensures your formulas copy correctly and calculate accurately when dragged across cells. If you’ve ever copied a formula and seen unexpected results — this is why. What Is a Cell Reference? A cell reference tells Excel where to find data. For example: =A1 This tells Excel to use the value in cell A1. But how Excel treats that reference depends on whether it is relative

The SUMIF  function in Excel allows you to add up values that meet specific criteria . It’s perfect for summarising data, tracking totals, and analysing reports quickly — without manually filtering or calculating numbers. SUMIF is widely used in finance, administration, sales reporting, and operations. What Does SUMIF Do? SUMIF adds together only the numbers that match a condition you specify. For example: Total all sales over £1,000 Sum invoices for a specific client Calcula

The COUNTIF  function in Excel is a powerful tool for counting cells that meet specific criteria. It helps you quickly summarise data, track occurrences, and spot trends without manually scanning your spreadsheet. COUNTIF is widely used in finance, administration, HR, and reporting roles. What Does COUNTIF Do? COUNTIF counts the number of cells in a range that meet a single condition. For example, if you have a list of orders and want to know how many exceed £1,000, COUNTIF d

In Excel, protecting worksheets and workbooks helps secure your data, prevent accidental changes, and control access . Whether you’re sharing reports, financial models, or dashboards, this feature ensures your work remains accurate and professional. What Does Protection Do? Excel allows you to: Lock cells  so they cannot be edited Protect entire worksheets  to prevent changes to formulas or structure Protect workbooks  to prevent adding, deleting, or moving sheets Restrict ac

Excel’s What-If Analysis  tools let you explore different scenarios and see how changes to your data affect outcomes. It’s a powerful feature for budgeting, forecasting, and decision-making in finance, operations, and project management. Instead of manually recalculating every possibility, What-If Analysis lets Excel do it automatically. What Is What-If Analysis? What-If Analysis allows you to test “what happens if”  scenarios in your spreadsheet. For example: What if sales i

Excel is not just a tool for storing data — it’s a powerful platform for analysing information and making decisions . Even with basic techniques, you can extract insights, spot trends, and summarise data quickly. These skills are essential for finance, administration, operations, and reporting roles. What Is Data Analysis in Excel? Data analysis involves examining your data to identify patterns, trends, and meaningful insights. Even simple analysis can help you answer questio

PivotCharts are an extension of PivotTables that allow you to visualise your data dynamically . They make it easy to see trends, patterns, and insights from large datasets — all without creating static charts manually. If you regularly report data or create dashboards, PivotCharts can dramatically improve clarity and presentation. What Is a PivotChart? A PivotChart is a dynamic chart linked to a PivotTable . When you update or filter the PivotTable, the PivotChart updates aut