Using Microsoft Office for Mac as a Relational Database

By Jim Gordon, co-author of Office 2011 for Mac All-in-One For Dummies.

Part 9 - Adjusting Query table properties

You can set query table properties when you first import data by clicking the Properties button in the Return External Data to Microsoft Excel dialog (see Part 7 of this tutorial). In Part 7 we accepted the default settings. In this part of the tutorial we explore query table properties more fully.

The QueryTable properties dialogs have been improved for Office 2016. The dialogs now look and work like their Excel for Windows counterparts. Excel 2016 will be discussed first, followed by Excel 2011.

EXCEL 2016
To display QueryTable properties, select a cell anywhere in the QueryTable, then, on the Data Tab of the Ribbon, choose Connections > Properties. The smaller button on the Ribbon labeled Properties controls different aspects of the QueryTable.

EXCEL 2011
Right-click in data > Convert to Range. Right-click in data again > Data Range Properties

Data range properties

Name: You can name the query. This is not the name of the table nor is it the name of the data range in which the result set exists.
The name should be short, have no spaces or special characters and should not conflict with other range or query names in your workbook.

Query Definition:
Refresh control: Discussed in Part 8.

Data Layout: Choose options as desired. Checking the box for AutoFormat data will cause Excel to automatically re-size columns to fit the data,

Fill down formulas in columns adjacent to data:
When checked, you can use Excel's AutoFill feature to populate adjoining columns based on cell formulas. See how this works in Part 22.

Use Table: Check this box to display your data using an Excel Table that can be formatted using options on the Table tab of the Ribbon.

These properties can be adjusted using VBA.
Sub AdjustQueryTableProperties()
    Range("A2").Select 'Select any cell in the query table range
    With Selection.QueryTable
        .Name = "ExternalData_1"
        .UseListObject = False
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .HasAutoFormat = False
        .RefreshOnFileOpen = True
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .TablesOnlyFromHTML = True
    End With
End Sub

Note: Most other query table properties found in the Windows version of Office are not supported.

Part 1
Part 8 (Previous)
Part 10 (Next)