ACCT 311: Automation Lab

Excel Macros & Audit Logic

Automate AP Cleaning + Flag Duplicates

What is a Macro?

A Macro is like a digital recorder for Excel. It watches your mouse clicks and keystrokes, memorizes them, and saves them as a small program (VBA). You can then "replay" those steps instantly on new data.

Accounting Use Case: Imagine you download a messy Accounts Payable report every Friday. Instead of spending 30 minutes formatting it manually (bolding, filtering, sorting), you press one button, and Excel does it in 3 seconds.

In this lab, you will record a macro to clean a dataset, apply audit logic to find duplicates, and build a one-click exception report tool.

Files

Download Student File (.xlsx)

Objectives

  • Record a macro for formatting
  • Automate Duplicate Detection
  • Assign macros to buttons
  • Understand basic VBA concepts
1

Environment Setup

Turn on Developer Tab

File → Options → Customize Ribbon → Check box for "Developer" (on the right side).

Save as Macro-Enabled

File → Save As → Change file type to Excel Macro-Enabled Workbook (.xlsm)

Create "Exceptions" Sheet

Create a new blank sheet and rename it exactly Exceptions. Leave it empty for now.

2

Demo 1: The "Cleaning + Logic" Macro

We will record a macro named Clean_AP_Report. This will format the data AND automatically add our audit formula.

Prep: Copy this formula logic (No Equals Sign!)

We will type the = sign manually during the macro recording.

IF(COUNTIFS([Vendor],[@Vendor],[Amount],[@Amount])>1,"DUPLICATE","OK")

Recording Checklist

Pro Tip: Don't click random cells! Follow exactly:

  1. Start Recording: Developer Tab → Record Macro. Name it Clean_AP_Report.
  2. Bold Headers: Select Row 1 → Home → Bold.
  3. Create Table: Click any single cell in the data (e.g., A1) → Press Ctrl + T → OK.
  4. Number Format: Select 'Amount' column (E) → Home → Number (or click Comma Style).
  5. Autofit: Select all columns (Ctrl+A) → Double click line between headers A and B.
  6. Freeze Panes: View → Freeze Panes → Freeze Top Row.
  7. Sort: Click inside table → Data → Sort (Vendor A-Z, Add Level: Amount Largest to Smallest).
  8. Create Logic Column: Click cell G1 → Type "Dup_Flag".
  9. Paste Formula: Click G2 → Type = → Paste (Ctrl+V) → Enter.
  10. Conditional Format: Select Column G → Home → Conditional Formatting → Highlight Cells → Text that Contains... "DUPLICATE" → OK.
  11. STOP RECORDING: Developer Tab → Stop Recording.
3

Understanding the Logic

Great job! Your macro now automatically inserts a complex audit formula. Let's understand what that formula actually does.

=IF(COUNTIFS([Vendor],[@Vendor],[Amount],[@Amount])>1,"DUPLICATE","OK")
  • COUNTIFS: This counts rows where both criteria match.
  • Criteria 1: Does the Vendor match this row's Vendor?
  • Criteria 2: Does the Amount match this row's Amount?
  • >1: If it finds more than 1 match (itself + another one), it's a duplicate.
4

Your Turn: The "Exception Report"

Challenge: Create Macro "Make_Exception_Report"

Start recording, then perform these EXACT steps.
Goal: Copy duplicates to the existing Exceptions sheet.

  1. Filter: Click the arrow on header Dup_Flag. Uncheck "OK". Keep "DUPLICATE" checked.
  2. Copy: Click inside the table. Press Ctrl + A twice to select the whole table. Press Ctrl + C.
  3. Go to Destination: Click on the Exceptions sheet tab.
  4. Select A1: Click cell A1.
  5. Paste Values: Right-click cell A1 → Select Paste Values (123 icon).
  6. Stop Recording: Developer → Stop Recording.
Final Step: Add the Button

Go back to your AP_Raw sheet.

  1. Go to Developer → Insert → Button (Form Control).
  2. Draw the button near the top of your data.
  3. Select Make_Exception_Report from the list → OK.
  4. Right-click the button → Edit Text → Rename to "Build Report".
5

The Real Test: Next Month's Data

The true power of a macro isn't cleaning this data—it's cleaning next month's data instantly.

Scenario: It's February 1st
  1. Reset: Delete ALL data in your AP_Raw sheet.
  2. Clear Exceptions: Go to the Exceptions sheet and delete the old data (Select all & delete). Do NOT delete the sheet itself!
  3. Get New Data: Click the button below to copy the "February Data".
  4. Paste: Paste it into cell A1 of AP_Raw.
  5. Run: Click your macro buttons (Clean & Build Report).
6

Did it Crash? (Debugging)

Common Error: "Subscript out of range"

If you get Run-time error '9', it usually means the macro is looking for a sheet that doesn't exist.

Did you delete the "Exceptions" sheet? The macro expects that sheet to be there waiting.

How to fix it:
  1. Click End on the error popup.
  2. Create a new blank sheet.
  3. Rename it exactly: Exceptions
  4. Try pressing your button again.