Using Microsoft Office for Mac as a relational database

by Jim Gordon, Microsoft Mac MVP, revised January, 2017
Co-author Office 2011 for Mac All-in-One For Dummies

Part 1 - Introduction

You can use Microsoft Excel 2011 or Excel 2016 for Mac as a fully relational database. With Excel has the ability to make data tables, data input forms, and reports. You can make Structured Query Language (SQL) queries using Microsoft Query, provided with Microsoft Office. Developers can use Visual Basic for Applications (VBA) to make automated solutions. All editions of Excel for Mac have this support built-in.

Excel for Mac supports these relational database elements:
Tables: Data arranged in rows and columns
Forms: More exactly, input forms, for entering data into the tables
Queries: Commands that specify what data to retrieve from the tables using Structured Query Language (SQL)
Reports: Query result sets can displayed on Excel worksheets in query tables and pivottables.
This tutorial is a series of web pages designed to help intermediate users of Excel become familiar with Excel for Mac's relational database features. This tutorial explains how to use an Excel workbook as a data source, run queries against the data source, and generate reports and pivottabes. This content can be generalized and can be applied to working any data source supported by ODBC drivers that work with Microsoft Office on the Mac.

Some pages in this tutorial have Visual Basic for Applications (VBA) code examples on them. These excerpts from the Office 2011 for Mac All-in-One For Dummies book may be of assistance regarding VBA on the Mac:
Here is the Table of Contents for this tutorial:
Part 1 - Introduction (this page)
Part 2 - Setup (next page in tutorial)
Part 3 - Organization
Part 4 - Tables
Part 5 - Forms
Part 6 - SQL and Queries
Part 7 - Simple step-by-step example
Part 8 - Refreshing a query table
Part 9 - Query table properties
Part 10 - Filtering records with SQL
Part 11 - Joining tables
Part 12 - Count and Group By Example
Part 13 - Matching strings using Like
Part 14 - Return unique records
Part 15 - Group records
Part 16 - Sort records
Part 17 - Making a calculated field
Part 18 - Use a database query to make a pivottable
Part 19 - Making a parameter query
Part 20 - Edit an existing query
Part 21 - Reserved words
Part 22 - Make a query using VBA and add calculated columns to the worksheet

My Excel Page
Part 2 (Next)