EXCEL LABS

💥Join us to master the most important logical function in Excel
Comment with your email for link to be sent to you

📊 Detecting Formulas in a Budget Sheet with the ISFORMULA Function

When working with financial models or budget templates, it is important to quickly identify which cells contain formulas and which contain manually entered values. This helps prevent errors and ensures calculations remain intact.

🧾 Scenario
You are reviewing a departmental budget sheet where totals should be calculated automatically. However, some staff may accidentally replace formulas with typed numbers.

You want to check whether the Total Cost column still contains formulas.

🛠 Step-by-Step

1️⃣ Assume the Total Cost is in column D.

2️⃣ In column E, enter this formula:

"=ISFORMULA(D2)"

3️⃣ Press Enter and copy the formula down.

📌 Result

✔ TRUE → The cell contains a formula
⚠ FALSE → The cell contains a manually typed value

🔎 Syntax

"ISFORMULA(reference)"

Reference – The cell you want to check for a formula.

💡 Practical Use
For finance officers, accountants, and budget analysts, this function helps maintain data integrity in financial reports and expenditure tracking.

✅ Follow our page for more practical Excel tips for finance, accounting, and business analytics.

📊 FREE ONLINE SESSION: PRACTICAL EXCEL IF FUNCTION FOR BUSINESS DECISIONS

📩 How to Join: Comment with your EMAIL ADDRESS and the Zoom link will be sent to you.

Do you use Excel in banking, accounting, finance, or business operations? Join this practical Zoom session and learn how to use the IF Function to automate real business decisions.

🔹 Topic: Practical Business Applications of the IF Function

🔹 Focus Areas: • Loan approval decisions in banking • Detecting suspicious transactions • Budget monitoring in accounting • Tax and payment status calculations • Automating financial decision making in Excel

🎤 Special Feature: A Live Q & A Session where participants can ask questions and get practical solutions to real Excel challenges.

🎯 Who Should Attend? • Bankers • Accountants • Finance Officers • Business Managers • Students and Excel users

📍 Venue: Zoom (Online) 📅 Date: 19th March 2026 ⏰ Time: 2:00 PM (GMT)

📩 How to Join: Comment with your EMAIL ADDRESS and the Zoom link will be sent to you.

💡 Make Excel work for you by turning data into smart business decisions.

📊 Using AVERAGEIF to Analyze Teller Performance in a Bank

In banking operations, supervisors often need to measure the average transaction value handled by specific tellers or transaction types. The AVERAGEIF function in Microsoft Excel helps do this quickly and accurately.

Master the Clean function for Accurate Budget Data Analysis 🧹📊

🔎 Brief Explanation

The CLEAN function removes non-printable characters from text in Excel. These characters often appear when data is imported from external systems or copied from websites or PDFs.
⚙️ Syntax
=CLEAN(text)
text – The text or cell reference from which you want to remove non-printable characters.

📊 Budgeting Data cleaning (Step-by-Step)
Scenario:
A district budget analyst imports expenditure data from a financial system. Some department names contain hidden characters, which disrupt pivot tables and lookups.
Step 1: Identify the Problem
You notice that the Department column has inconsistent formatting, causing duplicates in your budget summary.

Example:
Department
Budget (GHS)
Health Dept
120,000
Health Dept
95,000

The entries look identical but contain hidden characters.
Step 2: Apply the CLEAN Function
In a new column, enter:
=CLEAN(A2)

This removes hidden characters from the department name.
Step 3: Copy the Formula Down
Drag the formula down to clean all department names.
Step 4: Use the Cleaned Data
Copy and Paste Values over the original column.
Your budget summaries, pivot tables, and lookups will now treat the departments correctly.

✅ Result
Accurate budget aggregation
Reliable pivot tables
Improved data consistency for financial analysis
💡 Tip: Combine CLEAN with TRIM for even better results:

=TRIM(CLEAN(A2))
This removes hidden characters and extra spaces.

📢 Follow our page for more practical Excel tips for budgeting, finance, and data analysis.

1️⃣ Sales Order Automation System with Excel 🧾

🔎 About the XLOOKUP Function
The XLOOKUP function searches for a value in a range and returns the corresponding result from another range. It is a modern replacement for VLOOKUP and HLOOKUP, and it works in both directions.

Syntax:
XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

Problem:
The sales team records orders using Product ID, but they also need the Product Name and Price to complete invoices. Manually searching the product list slows down the process and increases errors.

Solution:
Use the XLOOKUP function to automatically retrieve product details from a product database.
Step 1: Create a product database containing Product ID, Product Name, and Price.
Step 2: In the Sales Order sheet, the user enters the Product ID.
Step 3: Retrieve the Product Name using:
=XLOOKUP(A2,ProductList!A:A,ProductList!B:B)
Step 4: Retrieve the Product Price using:
=XLOOKUP(A2,ProductList!A:A,ProductList!C:C)

✅ Result: Product details are filled automatically, making order processing faster, more accurate, and more efficient.

📊 Smart Business Applications You Can Build with the XLOOKUP Function

🏢 Business Scenario
A growing company, NovaTrade Ltd, manages several business operations in Excel including sales, HR records, pricing, and inventory. Employees often need to quickly retrieve information such as employee details, product prices, and customer records.

Manually searching through large datasets wastes time and increases the risk of errors.
💡 Solution: Use the XLOOKUP function to build automated business tools that instantly retrieve accurate information.

💰 Designing a Personal Budget Template

Managing money becomes easier when your budget automatically calculates totals, tracks spending, and highlights problems. Follow these steps to design a simple but powerful personal budget template in Excel.

🧾 1️⃣ Set Up the Budget Structure
Create a table with the following columns:
Category | Planned Budget | Actual Spending | Difference
Add common expense categories such as:
🏠 Rent
🍔 Food
🚗 Transport
📱 Utilities
🎓 Education
🎉 Entertainment
This structure helps you compare planned spending vs actual spending.

➕ 2️⃣ Calculate Total Budget and Spending
At the bottom of the table, calculate totals.
Total Planned Budget
=SUM(B2:B10)
Total Actual Spending
=SUM(C2:C10)
📊 These formulas automatically update whenever values change.

📉 3️⃣ Calculate Budget Variance
To track overspending or savings, calculate the difference between planned and actual spending.
Difference Formula
=B2-C2
Interpretation:
Positive value ➜ You saved money
Negative value ➜ You overspent
Copy the formula down for all categories.

⚠️ 4️⃣ Highlight Overspending Automatically
Use Conditional Formatting to flag overspending.
Steps:
Select the Difference column
Go to Home → Conditional Formatting
Click Highlight Cells Rules
Choose Less Than
Enter 0
🔴 Cells with negative values will appear in red.

📊 5️⃣ Add a Budget Summary Chart
Charts help visualize spending patterns.
Steps:
Select Category and Actual Spending columns
Go to Insert
Click Charts
Choose Pie Chart or Column Chart
📈 This quickly shows which categories consume the most money.

🔒 6️⃣ Prevent Incorrect Data Entry
Use Data Validation to control inputs.
Steps:
Select the Actual Spending column
Go to Data
Click Data Validation
Set Allow: Decimal
Set Minimum Value: 0
✅ This prevents entering negative values.

🧠 Key Excel Functions & Tools Used
Functions
SUM() – Calculate totals
IF() – Create spending alerts
MIN() / MAX() – Analyze spending limits
Excel Tools
Conditional Formatting
Charts
Data Validation
Excel Tables (Ctrl + T)
Autofill
📈 Practice this daily to boost your spreadsheeting skills.

💰 Cashbook Automation with Microsoft Excel

Automating a cashbook helps eliminate posting errors, saves time, and improves financial reporting — very useful for accounting students and small business owners.

🎯 Objective
Create an automated cashbook that:
Records receipts and payments
Calculates running balance automatically
Summarises monthly totals
Flags errors instantly

🪜 Step-by-Step Guide
🔹 Step 1: Design the Data Entry Sheet
Create columns:
Date | Details | Ref | Receipts (Dr) | Payments (Cr) | Balance
📌 Format:
Date column → Date format
Money columns → Currency format (₵)

🔹 Step 2: Create Running Balance Formula
Assume:
Opening balance is in F1
First transaction is on row 2
In F2, type:
=F1 + D2 - E2
Then drag the formula down.
✅ This updates the balance automatically after each transaction.

🔹 Step 3: Add Data Validation 🛑
To avoid errors:
Select Receipts & Payments columns
Go to Data → Data Validation
Allow: Decimal
Minimum: 0
This prevents negative entries.

🔹 Step 4: Prevent Double Entry 🚨
To ensure only receipt OR payment is entered:
Select D2:E100
Home → Conditional Formatting → New Rule
Use formula:
=AND(D2>0,E2>0)
Apply red fill.
This flags any row where both columns have values.

🔹 Step 5: Monthly Summary Section 📊
Total Receipts:
=SUM(D:D)
Total Payments:
=SUM(E:E)
For monthly receipts (example March 2026):
=SUMIFS(D:D,A:A,">=01/03/2026",A:A,"

📊 Mastering LEFT, MID & RIGHT Functions in Excel

🏢 Business Scenario
BrightMart Ltd exports customer data from its sales system into Excel. However, important details such as Region Codes, Product Categories, and Serial Numbers are embedded inside long ID codes.
Example of Product Code:
NR-ACC-2026-015
Management wants to extract:
Region → NR
Category → ACC
Serial Number → 015

🎯 Objective
Use LEFT, MID, and RIGHT functions to extract specific text from structured codes.

🔹 1️⃣ Using the LEFT Function
📘 Purpose: Extract text from the beginning (left side) of a string.
📌 Syntax:
=LEFT(text, num_chars)
📊 Application: Extract Region Code (NR)
If product code is in cell A2:
=LEFT(A2,2)
✅ Result: NR
💡 Meaning: Excel takes the first 2 characters from the left.

🔹 2️⃣ Using the MID Function
📘 Purpose: Extract text from the middle of a string.
📌 Syntax:
=MID(text, start_num, num_chars)
📊 Application: Extract Category (ACC)
From NR-ACC-2026-015
=MID(A2,4,3)
✅ Result: ACC
💡 Explanation:
Start at position 4
Extract 3 characters

🔹 3️⃣ Using the RIGHT Function
📘 Purpose: Extract text from the end (right side) of a string.
📌 Syntax:
=RIGHT(text, num_chars)
📊 Application: Extract Serial Number (015)
=RIGHT(A2,3)
✅ Result: 015
💡 Excel extracts the last 3 characters.

⚙️ Automating Reports with Macros & Buttons in Microsoft Excel

🎯 Scenario
You prepare a monthly sales report and always:
Clear old data
Format the sheet
Insert today’s date
Apply borders and totals
Instead of repeating this manually every month, you decide to automate it using a Macro with a Button. 🚀

🗂 Step 1: Enable the Developer Tab
Click File
Select Options
Click Customize Ribbon
Tick Developer ✅
Click OK
You will now see the Developer tab on the ribbon.

🔴 Step 2: Record the Macro
Go to Developer tab
Click Record Macro
Name it: Format_Report
Choose: This Workbook
Click OK
Now perform the actions you want automated:
✔ Clear old data
✔ Apply formatting
✔ Insert =TODAY()
✔ Add totals
Click Stop Recording
Your macro is now saved. 🎉

🧩 Step 3: Insert a Button
Go to Developer
Click Insert
Choose Button (Form Control)
Draw the button on the worksheet
Assign Macro → Select Format_Report
Click OK
Right-click the button → Edit Text → Rename to:
"Generate Monthly Report"

▶ Step 4: Test the Button
Click the button.
✨ Instantly:
Old data clears
Formatting applies
Date updates
Totals calculate
Your repetitive work is now done in seconds!

💼 Business Value
✔ Saves time
✔ Reduces human error
✔ Standardizes reporting
✔ Improves productivity
Perfect for Admin Officers, Accountants, Budget Analysts, and Managers. 📊

⚡ Pro Tip
To edit your macro:
Developer → Visual Basic → Modify the VBA code.
You can also add message boxes like:
MsgBox "Report Generated Successfully!" 🎯