How to Move Data from External Sources into Excel 2011 for Mac

By Geetesh Bajaj, James Gordon

You can find data in many different places and bring it into Excel 2011 for Mac workbooks in whole, or as the result of a query in a query table. The tools on the External Data Sources group of the Data tab in the Office 2011 for Mac Ribbon facilitate importing and refreshing data from sources that are external to Excel.


Refreshing a data table in your Excel workbook

If your query table is linked to a data source, you can update the data in your workbook on demand so that it reflects the current state of the data source. On the Ribbon’s Data tab, go to the External Data Sources group and click Refresh button’s triangle to display a pop-up menu, where you can choose from the options.

Opening a CSV or other text file containing data

The most common type of text file containing data is Comma Separated Values (.csv). Excel can open these straight away if you choose File→Open. If you have a text file that was saved using a character other than a comma as the column delimiter, you can open it by using the Text button on the Database tab of the Ribbon. Note that you need to be able to tell Excel which character was used as the delimiter when the file was saved. Clicking the Text button opens a wizard that helps you establish the content boundaries of each column.

Connecting to a database in Excel 2011 for Mac

If you have a database you want to connect to so that you can build a query, you start by clicking the Database button. You need to take some preparatory steps before you can successfully use options in the External Data Sources group in the Data tab of the Ribbon. First, you need to install an ODBC driver on your computer. (ODBC stands for Open Database Connectivity and is a standard that allows communication of data records between different database systems.)

You must also add at least one data source to the ODBC manager before you can get data into Office. When you have a data source, you launch Microsoft Query, an Office application that visually builds the query for you and brings the results of your query into Excel.

Importing HTML data

On the Ribbon’s Data tab, go to the External Data Sources group and click HTML to display an Open dialog. From this dialog, you can open a Web page that you saved from a Web browser. Excel imports the data in the Web page.

Importing from FileMaker Pro

On the Ribbon’s Data tab, go to the External Data Sources group and click the FileMaker button to display a Choose a Database dialog where you can choose a FileMaker Pro file to use as your data source.