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