The Excel 2016 edition of Power Map offers a filter feature. There is one oddity: the Filter dropdown will never show more than 50 items. If you need something beyond the first 50 items, you have to use the Search box.

Figure 1049 Filtering in Power Map.

EXCEL DATA TO MAILING LABELS IN WORD

Problem: I have address information in Excel and I have to make mailing labels.

Strategy: You can use the Mail Merge in Microsoft Word to make the labels. Here are the steps:

1. Make sure your data in Excel is set up with each address going across a row. 2. Have headings in row 1.

3. Close the file in Excel.

Figure 1050 Set up your data properly in Excel.

4. Open a blank document in Microsoft Word.

5. In Word, go to Mailings, Start Mail Merge, Labels.

6. Choose the right size labels in the Label Options dialog. Click OK. You get a document full of blank labels.

7. Go to Mailings, Select Recipients, Use an Existing List....

8. Browse to and select your Excel file.

Figure 1051 Choose an existing list.

9. In the Select Table dialog, choose Sheet1$. Make sure First Row of Data Contains Column Headers is selected. Click OK. You now a confusing <<Next Record>> in all but the first label.

10.Notice that the insertion point is in the first label. Go to Insert Merge Field and choose the first line of your address field.

11.This is tough to do, but carefully press Shift+Enter to go to the next line without inserting a bunch of space.

12.Repeat steps 10, 11, 10, 11, 10 until you have all four lines of the address in the label.

3

13.If you want any special font, add that formatting to the first label.Figure 1052 Build the first label.

14.Press the Update Labels button to repeat your fields in all of the labels of the sheet.

15.Choose Finish & Merge, Edit Individual Documents. You will have labels from the Excel data. Gotcha: Step 14 seems to be the non-obvious step that trips most people up.

EXCEL 2013 ALLOWS SLICERS ON REGULAR TABLES

New in Excel 2013, you can filter a regular data set, provided you convert the data to a table using Ctrl+T first.

Figure 1053 Convert a regular data set to a table using Ctrl+T

From the Table Tools Design tab, choose Insert Slicer. Select the fields for the slicers. Use the Slicer Tools

Design tab to change the number of columns, colors, and so on.

Figure 1054 As you select from the slicers, the data set is filtered.

USE POWER QUERY TO LOAD MANY WEB PAGES

Problem: I need to load data from a series of web pages. The Web Query technique from Excel 2003 rarely works with modern web pages.

Strategy: Use a hack with Power Query to create a Query of Queries.

Power Query is a new tool that is a free download for Excel 2010 & Excel 2013 and is built in to the Excel 2016 Data ribbon.

In this example, you are trying to pull data from a website for Dallas for January 2, 2015. You can see there are paremeters in the URL that specify the city and the date.

Figure 1055 This website is a great candidate for scraping web data.

Use Data, New Query, From Other Sources, From Web. Enter the URL. Use Power Query tools to isolate the information from the web page. In my case, the query returns one row with Min & Max temperature and precipitation.

Figure 1056 Results of the query for one web page.

Once the query for a single web page is working, you will convert this query to a function. Edit the query. use the View tab in Power Query and choose Advanced. You will see the code behind the query. Insert a new line above the word Let. Type (MyURL)=>. Scan through the remaining code and replace the actual “URL” with the word MyURL.

Figure 1057 Use the Advanced view to add a parameter.

When you close the Advanced view, you will notice several alarming things. The list of Applied Steps will be replaced with a single step. Power Query will be asking you to enter the Parameter for MyURL. Ignore these apparent problems. Type a new name for the query such as fxWeather. (Note that the fx prefix is geek-speak for Function).

Figure 1058 Rename the query. You will need this exact name later, with the exact case.

When you choose Home, Close & Load, your original returned results will disappear. Don’t be alarmed.

Nothing will load, but you will see a query panel indicating there is a connection-only query called Weather.

3

Figure 1059 The data is gone, but the query is still there.

Next, you will build a table of all of the URL’s. In my case, I created a worksheet where I could change the airport code and the start date. Formulas in the table then generate the list of URLs. Make sure to Format as Table using Ctrl+T.

Figure 1060 Use Excel formulas to generate a list of URLs.

Select one cell in the date table. Select Data, Get & Transform, From Table. In Power Query, choose Add

Column, Add Custom Column.

Figure 1061 Use the fnWeather query to create a custom column.

Because the function is returning an array of three values, you have to click the Expand icon and choose to add all three columns. Make sure to unselect the Prefix option.

Figure 1062 Expand the column to show all three fields.

When you close & load, you will notice that Power Query is taking a few seconds to update each row. If you were returning thousands of rows, you would want to load just before going to lunch.

Figure 1063 Each row contains data from a separate web page.