- Today, we are excited to announce the first step in a journey to support Power Query in Excel for Mac. This is by far the most popular idea on Excel UserVoice under Excel for Mac category. In this release, you can refresh your Power Query queries on Mac from Text, CSV, XLSX, XML and JSON files, and you can author queries with VBA.
- Getting external data using Excel Web Query. Web Queries are an easy, built-in way to bring data into Microsoft Excel from the Web. You can point a Web query at an HTML document that resides on a Web server and pull part or all of the contents into your spreadsheet.
Join Date Location ONT, Canada MS-Off Ver Office 365 ProPlus Posts 5,663.
I am a huge proponent of using 3rd-party tools to manage SEM campaigns. Aside from being able to manage accounts from multiple publishers in one place, 3rd-party tools offer plenty of features not available from AdWords or Bing.
One of these features has become near and dear to my heart: web queries. Web queries give you the ability to build a recurring report in your platform (using Marin for this example); rather than format it as an Excel file or PDF, the report is generated as HTML tables that can be easily imported into Excel.
This is especially useful for reports that update on a daily or weekly basis. Rather than downloading a new CSV or Excel file, you simply hit the Refresh button in Excel where you have your web query housed, and the data will update. At the rate I use web queries, I have already saved myself days’ worth of time that can now be spent on higher-priority projects for my clients.
On the Windows version of Excel, dropping a web query into your report takes maybe 3-4 clicks. This is not the case in the much-maligned Mac version of Excel (which hasn’t seen an update since 2011), but it is possible!
In this guide, I will walk you through the process of dropping a web query into an Excel file on a Mac.
Step 1: Create the Web Query File
In order to call a web query on the Mac version of Excel, the query needs to exist in a specially formatted text file. Creating this file is pretty easy; start with opening Microsoft Word or whatever text editor you use and pasting the URL to your web query on the first line.
Next you are going to want to save this as an .iqy file. To do this, simply change the format to Plain Text (.txt) and add “.iqy” to whatever you want to name your web query.
Save this file wherever you would like; just remember the location because you will need to import the file in Excel. Your text editor may ask you to specify how you would like to encode the text file. If this is the case, select the radio button for MS-DOS.
Step 2: Import the file in Excel
Next you will need to import the file in Excel. To do so, first select which cell you would like the web query tables to start at. Once you have done that, you can go to Data -> Get External Data -> Run Saved Query…
Next you will be prompted to choose which file you would like to import. Browse to the web query file we created in step one, and select “Get Data”.
You may be prompted to enter your login information if your web query is being called from a 3rd-party platform. Enter your credentials here.
After you hit connect, if you followed these steps correctly, you should have imported your web query into Excel!
Congratulations! It’s not nearly as easy as using web queries in Windows, but don’t let the process deter you from using this excellent time-saving feature. Web queries are great for budget pacing, report building, or recurring tasks such as identifying poor keywords or opportunities.
In my next guide, I will walk through how I use this feature in Marin to keep track of all my client’s spend in one document.
Excel 2010 and 2007 for Windows have the option to import data from the web. Excel for Mac users don’t.
An integral part of working with Excel is using keyboard shortcuts. They make your life so much easier (in the Windows versions at least, in the Mac version I think they tend to shorten your life span).
In my last post I dealt with getting a Help Topic URL, here I’m going to use the web page Keyboard shortcuts in Excel 2010 and import to a spreadsheet.
Get a Help Topic Web Page Address
As you will see, it helps to have the web address or URL on the clipboard before importing data from the web. In this example I’ll use the following steps to get the URL for Keyboard Shortcuts for Excel 2010:
- Press the F1 key
- Type Excel keyboard shortcuts in the search box
- Click the link for Keyboard Shortcuts for Excel 2010
- Right click on the topic heading then select Properties
- Triple click the Address (URL) link then copy (Ctrl+C) to the clipboard
- Click Cancel and close the Help window
Now we have the URL on the clipboard.
Get Data From a Web Page
Choose Data > Get External Data > From Web to bring up the New Web Query dialog box. This dialog box functions as a Web browser and can be re-sized. Clear the Address bar and paste the URL from the clipboard, then press Enter or click Go.
Web Queries With Excel For Mac Os
The web page above will appear in the New Web Query window. Scroll down and you’ll see a right-arrow in a yellow box at the top of each table. Click an arrow to queue any table for import into Excel.
We want the entire page so I’ll click the right-arrow in a yellow box at the top-left corner of the web page. This will give us the entire page. Once you click the right arrow it turns to a green check in a box.
Now click the Options… button then select Full HTML formatting.
Since we’re importing the entire page this option will give the best formatting. Now click the Import button and Excel will ask where you want to put the data. I’m leaving the default location cell A1. Click OK.
The data on the web page is imported into the worksheet. This is now an active external query.
To Edit the Query choose Data > Get External Data > Refresh All > Connection Properties then select the Definition tab and click Edit Query. You’re now back to the Edit Web Query dialog box where you can make modifications.
To modify the data range properties, right-click any cell in the imported data range and select Data Range Properties from the pop-up box.
Web Queries With Excel For Macbook Pro
The great thing about a web query is that if the web page data is updated all you have to do is Refresh the query to update the worksheet.
Web Queries With Excel For Macs
Related posts: