Excel & Access Blog
Practical Excel and Access guides for consulting, integrations, and accountable business systems—from the Excel Access Expert team
AI for Excel → related services
Using AI to Write Excel VBA Macros Safely
How to get working Excel VBA from ChatGPT or Copilot: prompt templates, a safety review checklist, common AI VBA errors, and when to hire a VBA developer.
Analyze Customer Feedback in Excel with AI
Turn survey comments and reviews into numbers in Excel: AI sentiment, fixed theme tagging, a PivotTable by theme, and checks that keep the analysis honest.
Automate Monthly Report Commentary in Excel with AI
Let Excel calculate variances and AI draft the narrative: a variance table design, threshold rules, a prompt that avoids invented reasons, and a review process.
Generate Product Descriptions in Bulk from Excel with AI
Write hundreds of accurate product descriptions from an Excel product sheet with AI: data structure, prompt template, review checks, and Shopify/WooCommerce export.
How to Call the OpenAI API from Excel VBA
Working Excel VBA code to call the OpenAI API: send a prompt per row, parse the JSON reply, store the key safely, handle errors and rate limits, and control cost.
How to Categorize Bank Transactions in Excel with AI
Auto-categorize bank transactions in Excel: a keyword rules table, formulas that apply it, AI for the leftovers, and a review loop that improves every month.
How to Use ChatGPT to Write Excel Formulas
Get correct Excel formulas from ChatGPT: the prompt structure that works, XLOOKUP, SUMIFS and FILTER examples, how to check AI formulas, and when it fails.
Clean Messy Excel Data with AI: Names, Addresses and Duplicates
A practical workflow to clean messy Excel data with AI and Power Query: standardise names and addresses, catch fuzzy duplicates, and verify results safely.
Copilot in Excel: What It Actually Does for Business Spreadsheets
What Copilot in Excel can and cannot do for business spreadsheets: licences, setup, prompts that work, accuracy limits, and when you still need automation.
Copilot vs ChatGPT for Excel: Which Should Your Team Use?
Copilot vs ChatGPT for Excel compared on formulas, VBA, data analysis, file handling, accuracy, privacy and cost, with a simple rule for which to use when.
AI Agents and Excel: What Actually Works for Businesses
What AI agents can and cannot do with Excel: a clear definition, real use cases, the hybrid design that works (rules plus agent), guardrails, and how to start small.
Excel LAMBDA Function Guide: Build Custom Functions Without VBA
Create reusable custom functions in Excel with LAMBDA: syntax, Name Manager, MAP, BYROW and REDUCE, six business-ready examples, and LAMBDA vs VBA.
Excel REGEX Functions: REGEXTEST, REGEXEXTRACT and REGEXREPLACE
How to use Excel's REGEX functions with 15 ready-to-use business patterns: emails, phone numbers, invoice IDs, ZIP codes, cleanup, and AI help writing patterns.
Sales Forecasting in Excel: FORECAST.ETS vs Linear vs AI
Build a seasonal sales forecast in Excel with FORECAST.ETS and Forecast Sheet: data prep, confidence intervals, accuracy checks, and when AI adds value.
Extract PDF Invoice Data into Excel with AI
Get supplier, invoice number, dates, totals and line items from PDF invoices into Excel: Power Query for digital PDFs, AI for mixed layouts and scans, plus checks.
Fix Excel Formula Errors with AI: #N/A, #VALUE!, #SPILL! and More
What each Excel error really means, the usual causes, how to diagnose them in minutes, and prompts that get AI to find the cause instead of hiding it.
Is It Safe to Use ChatGPT with Company Data?
Which AI plans train on your data, what never to paste, how to get Excel and Access help without sharing records, and a one-page AI policy for small businesses.
Send Personalized Emails from Excel with AI and Outlook
Draft a personalized message for every row with AI, review it in Excel, then create Outlook emails with VBA: templates, prompts, code, and spam-safe sending.
Python in Excel for Business Users
What Python in Excel is, how =PY() and xl() work, five business examples (forecasting, outliers, cohorts, charts), its limits, and when VBA still fits better.
Write Power Query M Code with AI
Use ChatGPT or Copilot to write and fix Power Query M code: get your query from Advanced Editor, prompts that work, real examples, and common M errors explained.
AI for Microsoft Access → related services
Redesign Microsoft Access Forms with AI
Use AI as a design partner for Microsoft Access forms: prompts for layout and workflow, screenshot reviews, modern styling, faster data entry, and validation patterns.
AI-Assisted Access to SQL Server Migration
Where AI genuinely helps an Access to SQL Server migration: query translation to T-SQL, VBA risk scans, view design and testing, plus where SSMA and people are still needed.
Data Quality Checks for Microsoft Access with Queries and AI
Find and fix bad data in Access: ten check queries for blanks, duplicates, orphans and ranges, an issues dashboard, and AI for fuzzy duplicates and contradictions.
Add AI Summaries to Microsoft Access Reports
Put a reviewed AI-written summary at the top of Access reports: generate from the report's own query, store and approve it, bind it to the header, and export to PDF.
Using AI to Write Microsoft Access VBA
Get reliable Access VBA from ChatGPT or Copilot: what to tell the AI, safe DAO and form patterns, a review checklist, and fixes for the errors AI code causes most.
Use ChatGPT to Write MS Access SQL Queries
Get working Access SQL from ChatGPT: the schema prompt, Access-specific syntax (wildcards, # dates, IIf, nested joins), worked examples, and common errors fixed.
Classify Emails and Tickets in Microsoft Access with AI
Turn a shared inbox into an Access ticket queue: capture Outlook emails, classify category and priority with AI, extract customer references, and route work.
Connect Microsoft Access to the OpenAI API with VBA
Call OpenAI from Microsoft Access VBA: a reusable function, a batch loop that writes results to a table, multi-user safety, key storage, cost control and error logging.
Document a Legacy Access Database with AI
Inherited an Access database nobody understands? Export every query, form and module, use AI to explain them, map data flows, and find what is safe to retire.
Does Copilot Work with Microsoft Access?
Copilot is not built into Access today. Here are the workarounds that do work: Copilot Chat for SQL and VBA, Copilot in Excel on Access data, and AI tools built for Access.
Generate Realistic Test Data for Microsoft Access with AI
Create safe, realistic test data for Access: AI prompts for rows and edge cases, a VBA generator for volume, keeping related tables consistent, and known-answer test sets.
Ask Your Access Database Questions in Plain English
How natural-language queries work on Microsoft Access: how AI turns questions into SQL, accuracy tips, safety checks, DIY vs DatabaseAI, and good first questions.
Parse JSON in Microsoft Access VBA
Parse API JSON responses in Microsoft Access VBA: set up VBA-JSON, read nested objects and arrays, handle nulls and dates, write results to tables, and build JSON to send.
Private, Local AI for Microsoft Access Data
Run an open-weight AI model on your own PC or server and call it from Access VBA: hardware, setup, working code, accuracy expectations, and when local beats cloud.
Scan Documents into Microsoft Access with AI
Turn scanned forms, delivery notes and certificates into Access records with AI: folder intake, extraction schema, staging table, validation queries and review form.
Industry playbooks → related services
Auto Repair Shop Database in Microsoft Access
Design an auto repair shop database in Access: customers and vehicle history, repair orders with labour and parts, technician efficiency, parts margin and service reminders.
Church Membership Database in Microsoft Access
Design a church membership database in Access: people and households, attendance, small groups, volunteer scheduling, pastoral care with restricted notes, and giving statements.
Cleaning Business Job Scheduling in Excel
Schedule residential and commercial cleaning in Excel: recurring jobs from a site list, cleaner assignment and availability, cover for absences, hours vs budget, and site profitability.
Clinic No-Show Tracking in Excel
Track and reduce patient no-shows in Excel: consistent no-show rate formulas, analysis by slot, lead time and provider, a simple risk score, and targeted reminders.
Construction Bid Tracking Spreadsheet
Track construction bids in Excel: log structure, due-date alerts, win rate by client and project type, a go/no-go score, lost-bid analysis, and AI for bid documents.
Construction Change Order Log in Excel
Build a construction change order log in Excel: columns, markup formulas, approval and at-risk tracking, revised contract value, billing status, and a monthly exposure report.
Dental Patient Recall Tracker in Excel
Build a dental recall tracker in Excel: due-date formulas by interval, outreach attempt logging, overdue buckets, a daily call list, and AI-drafted reminder messages.
E-commerce Order Reconciliation in Excel
Reconcile Shopify, WooCommerce and marketplace orders with payment payouts and bank deposits in Excel: imports, matching, fee and refund analysis, and exception lists.
Equipment Rental Tracking in Microsoft Access
Build an equipment rental database in Access: assets, rate cards, contracts, overlap-safe availability, day/week/month charge calculation, returns, maintenance and utilisation.
Fleet Maintenance Log in Excel
Build a fleet maintenance log in Excel: vehicle register, service intervals by miles and months, due and overdue flags, repair history, cost per mile and compliance date alerts.
Freight Load Tracking Spreadsheet
Track freight loads in Excel: load board, status and appointments, broker margin or carrier profit per load, deadhead and rate per mile, POD and invoice tracking.
HVAC Maintenance Agreement Tracker
Track HVAC service agreements in Access or Excel: agreement, site and equipment tables, automatic seasonal visit generation, renewal pipeline, and replacement leads.
IFTA Fuel Tax Spreadsheet in Excel
Prepare quarterly IFTA figures in Excel: trip and fuel logs, miles by jurisdiction, fleet MPG, taxable and net gallons, tax due from the rate matrix, and audit-ready checks.
Insurance Agency Renewal Tracker in Excel
Build an insurance renewal tracker in Excel: X-date work queue by line of business, renewal stages, remarketing flags, retention by producer, and AI renewal reviews.
Lab Sample Tracking Database in Microsoft Access
Build sample tracking in Access: registration and barcode IDs, chain of custody, test assignment, results against specs, out-of-spec flags, turnaround time and certificates.
Landscaping Route Scheduling in Excel
Schedule recurring landscaping and lawn care routes in Excel: property list, frequency-based visit generation, crew and zone assignment, rain-day rescheduling and per-property profit.
Law Firm Matter Tracking in Microsoft Access
Design a matter tracking database for a small law firm in Access: tables, conflict search, deadline and limitation tracking, tasks, time entries and AI note summaries.
Medical Billing Claim Denial Tracker in Excel
Track denied claims in Excel: columns, CARC code mapping, appeal deadlines, work queues by owner, denial rate formulas, root-cause dashboards, and where AI fits.
Month-End Close Checklist in Excel
Run month-end close from an Excel checklist: tasks by working day, owners and reviewers, dependencies, reconciliation status, overdue flags, close duration and automation targets.
NCR Nonconformance Log in Microsoft Access
Build a nonconformance (NCR) log in Access: tables, NCR entry form, disposition and cost tracking, supplier and part Pareto, CAPA linkage, and audit-ready reports.
Nonprofit Donor Tracking Database in Microsoft Access
Design a donor tracking database in Access: donors, gifts, pledges, campaigns, acknowledgements, retention and LYBUNT reports, year-end receipts, and AI thank-you drafts.
OEE Calculation in Excel for Manufacturing
Calculate OEE in Excel per shift and line: production log structure, the three factor formulas, downtime reason Pareto, the six big losses, and a weekly OEE dashboard.
Real Estate Commission Tracker in Excel
Track real estate commissions in Excel: deal log, split and cap formulas that handle crossing the cap, team and referral splits, pending pipeline, and year-to-date dashboards.
Rent Roll Spreadsheet in Excel
Build a rent roll in Excel that lenders accept: unit and lease columns, occupancy and loss-to-lease formulas, lease expiry schedule, arrears ageing, and monthly snapshots.
Reorder Point Formula in Excel
Set reorder points in Excel from your sales history: average demand, lead time, safety stock by service level, inventory position, EOQ, and a daily reorder list.
Restaurant Inventory Par Levels in Excel
Set restaurant par levels in Excel from real usage: count sheet, usage formulas, par and order quantity by pack size, supplier order sheets, and food cost variance.
Salon Commission Tracker in Excel
Calculate salon and spa commissions in Excel: import POS sales by stylist, tiered service commission, retail commission, back-bar deductions, base wage guarantee and payroll export.
School Attendance Tracking Database in Microsoft Access
Build an attendance tracker in Access for schools, academies and training centres: register form, absence codes, attendance percentages, chronic absence alerts and parent letters.
Staffing Agency Timesheet Tracker in Excel
Track temp worker timesheets in Excel: time entry table, placements with pay and bill rates, overtime, approval status, burden-adjusted margin, payroll and invoice exports.
Tax Season Client Document Tracker in Excel
Track expected vs received tax documents per client in Excel: checklists from last year's return, missing-items lists, reminder cadence, preparer queues and AI document sorting.
Access how-to & fixes → related services
Microsoft Access 2 GB Size Limit: Why You Hit It and What to Do
Hitting the Microsoft Access 2 GB limit? Find what is using space, stop bloat, compact safely, move attachments out, archive or split data, or move the back end to SQL Server.
Access AutoNumber Gaps and Reset: What to Do (and Not Do)
Why Access AutoNumber skips values, how to reset the seed safely with Compact or ALTER COLUMN COUNTER, and how to generate gap-free invoice numbers for multiple users.
Microsoft Access Crosstab Query Guide
Build Access crosstab queries: TRANSFORM and PIVOT syntax, fixed column headings with IN, form parameters, zeros instead of blanks, row totals, and crosstab reports.
Access Database Opens Read-Only: Causes and Fixes
Fix an Access database that opens read-only: folder permissions and the .laccdb lock file, file attributes, email and ZIP copies, exclusive mode, network shares and sync folders.
#Deleted in Access Linked Tables (SQL Server): Causes and Fixes
Fix #Deleted rows in Access linked SQL Server tables and views: missing primary keys, float and datetime keys, bigint support, linked view indexes, triggers, and rowversion.
Access Error 3218: Could Not Update; Currently Locked
Fix Access error 3218 (and 3260, 3188): what holds the lock, the right record locking settings, VBA patterns that avoid self-locking, retry logic, and when to move to SQL Server.
Access Multivalued Fields: Problems and How to Replace Them
Why multivalued fields cause problems in Microsoft Access (queries, SQL Server migration, exports), and a step-by-step conversion to a proper junction table with forms.
Access 'ODBC--Call Failed': Find the Real Error and Fix It
Fix ODBC--call failed in Access with SQL Server: read DBEngine.Errors, login and driver encryption issues, timeouts, constraint errors, schema changes and relinking.
Access 'Query Is Too Complex' Error: Causes and Fixes
Fix 'Query is too complex' in Microsoft Access: replace nested IIf with lookup tables or Switch, split queries into stages, simplify unions and ORs, and move heavy queries to SQL Server.
Access Security Warning: How to Set Trusted Locations
Remove the Access 'Security Warning: some active content has been disabled' bar: add trusted locations, network options, registry and Group Policy deployment, and blocked files.
Convert Access VBA to 64-bit: PtrSafe, LongPtr and #If VBA7
Fix 'must be updated for use on 64-bit systems' in Access: add PtrSafe, use LongPtr for handles, conditional compilation for 32/64-bit, common API conversions, and ActiveX issues.
Access with SharePoint Lists: Limits, Performance and Alternatives
Should you put Access data in SharePoint lists? The 5,000 item threshold, performance, relational limits, lookup columns, retired web apps, and better options for remote teams.
Deploy an Access Application with the Free Access Runtime
Distribute Access to users without licences: Runtime download and bitness, .accde builds, testing with /runtime, error handling, custom ribbons, trusted locations and updates.
DLookup Slow in Access? Faster Alternatives That Work
Speed up Access queries, forms and VBA that use DLookup, DSum or DCount: joins, totals subqueries, recordsets, Dictionary caching, indexes, and SQL Server considerations.
Export Microsoft Access Reports to PDF with VBA
Export Access reports to PDF with DoCmd.OutputTo: filter to one record, create one PDF per customer in a loop, safe file names, folders by month, and common pitfalls.
How to Create Forms in MS Access
Create MS Access forms for business data entry: wizard vs Design View, validation, and when to hire an Access consultant.
How to Split a Microsoft Access Database
Split a Microsoft Access database step by step: Database Splitter, UNC paths, one front end per user, automatic front-end updates, relinking, backups and common mistakes.
Import Excel into Microsoft Access with VBA
Import Excel into Access with DoCmd.TransferSpreadsheet: staging tables, data type pitfalls, ranges and sheets, validation, appending without duplicates, and folder imports.
Relink Microsoft Access Tables with VBA
Relink Access linked tables automatically with VBA: check links at startup, switch between dev and production, browse for a moved back end, and DSN-less SQL Server relinking.
See Who Is Logged Into an Access Database
Find who has an Access database open: the .laccdb lock file, the ACE user roster in VBA, a login log table, and a timed shutdown flag to clear users for maintenance.
Send Email from Microsoft Access with Outlook VBA
Send email from Access: SendObject vs Outlook automation, HTML bodies from record data, PDF report attachments, batch sending with logging, new Outlook limits and alternatives.
Track Who Changed What: Excel & Access Audit Logs
Build Excel and Access audit logs that capture who changed what, with practical accountability patterns for operations teams.
Excel how-to & automation → related services
Bulk Create PDF Invoices from Excel with VBA
Generate hundreds of PDF invoices from an Excel list: invoice template with formulas, FILTER for line items, VBA loop to export PDFs with proper names, numbering and email.
Connect Excel to a SharePoint List with Power Query
Pull SharePoint list data into Excel reliably: the SharePoint Online List connector, site URL vs list URL, expanding lookup and person columns, refresh, large lists and write-back options.
Dependent Drop-Down List in Excel
Create cascading drop-down lists in Excel: the Microsoft 365 FILTER method, the classic INDIRECT method, multi-word names, clearing child values, many rows, and three levels.
Email Excel Reports Automatically with Outlook VBA
Automate Excel report emails: refresh, export sheet to PDF, paste a range as HTML in the body, send a filtered version to each manager, log sends, and schedule options.
Excel Co-Authoring Not Working? Fix 'Locked for Editing'
Fix Excel co-authoring: requirements, 'locked for editing by another user', legacy shared workbook, sync and merge conflicts, macros, and when a shared workbook should become a database.
Excel Data Model and Power Pivot: When You Need Them
When to use the Excel Data Model and Power Pivot: multiple related tables, millions of rows, DAX measures like margin % and YTD, a date table, and when to move to Power BI.
Excel FILTER, SORT, UNIQUE and Dynamic Arrays
Use Excel dynamic arrays for live lists and reports: FILTER with several conditions, SORT and SORTBY, UNIQUE, the # spill reference, VSTACK, TAKE, GROUPBY and common errors.
Excel Macros Blocked: 'The Source of This File Is Untrusted'
Fix 'Microsoft has blocked macros from running' in Excel: what Mark of the Web is, how to unblock a trusted file, ZIP and email cases, trusted locations, and IT policy options.
Office Scripts vs VBA: Which Excel Automation Should You Use?
Office Scripts vs VBA compared: where each runs, what each can do, Power Automate scheduling, security, examples of the same task in both, and how to choose.
Power Query vs VBA: Which Should You Use in Excel?
Power Query vs VBA compared for real tasks: imports, cleaning, combining files, formatting, emailing and scheduling, with a decision guide and how to use them together.
Excel VBA: Loop Through All Files in a Folder
Excel VBA to process every workbook in a folder: Dir loop, FileSystemObject recursion, skipping temp files, reading data fast, error handling, and when Power Query is better.
XLOOKUP vs INDEX MATCH vs VLOOKUP: Which to Use
XLOOKUP vs INDEX/MATCH vs VLOOKUP compared with examples: exact match, left lookup, two criteria, last match, return multiple columns, version compatibility and speed.
Comparisons & upgrade paths → related services
Access vs Dataverse vs SQL Server: Choosing a Back End
Compare Access, Dataverse and SQL Server/Azure SQL as data platforms: size and user limits, remote access, security, Power Apps integration, cost and migration paths.
Microsoft Access vs Power Apps: Cost, Capabilities and When to Switch
Microsoft Access vs Power Apps compared: licensing cost, mobile and web access, data platforms, forms and reports, VBA vs Power Fx, development effort, and hybrid options.
Excel vs Airtable vs Smartsheet: Which Fits Your Team?
Excel vs Airtable vs Smartsheet compared for business teams: data structure, collaboration, automation, reporting, limits and cost, with recommendations by use case.