4. Data Analysis and Management Updates

There are some exciting changes to explore on the Get Data button's drop-down menu on Excel 2016's Data tab. In addition to a Launch Power Query Editor option on the main menu, you now have many more external database choices available on the From Database,  From Online Services, and From Other Sources submenus. To top it all off, Excel 2016 now sports a Navigator dialog box that makes importing data from any of these external data sources much more straightforward, and the Data tab supports two new data types, Stocks and Geography, that make it a breeze to retrieve pertinent information about stocks and places of interest.

Get Text Data

The new Navigator dialog box makes parsing text files that you want to manipulate in Excel a snap .After you click  the From Text/CSV command button on the Data tab and select the text file to import, Excel opens the text file in the Navigator with a preview of the how the text data will be parsed into separate columns and rows based on the delimiting character it is able to identify (See Fig. 4-1 in the Get Data gallery above). If the parsing looks okay, you can then go ahead and use the Load command button to import the parsed data into your worksheet. Otherwise, use the Delimiter drop-down button options to select a new character before loading the text data into your worksheet. Fig. 4-2 shows you the Employee text data parsed into separate columns (each with its own AutoFilter buttons) of new table after being loaded into the worksheet.

Get Web Data

When importing numerical data from a website into your worksheet, after clicking the From Web command button on the Data tab and designating the site's URL, the Navigator dialog box appears where you can select and preview a table of data to load (see Fig. 4-3 in the Get Data Updates gallery above). After you load the data into the worksheet as an Excel table (see Fig. 4-4), you can then filter, sort, and graph the imported web data in Excel.

Get Access Data

The Navigator's AI intelligence and preview capabilities make importing data tables from dedicated database programs such as Microsoft Access and rich data sources such as an Azure SQL data warehouse a great deal easier. Take for example the process of selecting, transforming, and loading the Customers and Orders data tables in the sample Access Northwind database illustrated in Figures 4-5 through 4-7 in the Get Data Updates gallery above. Fig. 4-5 shows the Navigator after selecting the Customers and Orders tables to be imported. Then, to manipulate the data in these tables with the Power Query Editor prior to loading them in Excel, you click the Transform command button. Fig. 4-6 shows the first part of the Customers data table loaded and previewed in Power Query Editor. Once you have manipulated the data in the selected tables as needed, you can load them into sheets of your current workbook using the Close & Load option. Fig. 4-7 shows the first part of the Orders data table in the worksheet with the Queries & Connections task pane displayed.

The Stocks data type

The Stocks data type enables you to define a stock abbreviation entered into a cell of a worksheet with its corporation. Once you do this, you can instantly get and insert all sorts of financial information about that stock into your worksheet. Fig. 4-8 in the Data Types Updates gallery above shows you how you do this. Cell A1 contains Microsoft's Nasdaq stock abbreviation, MSFT. To identify this label as a stock symbol, you simply click the Stocks command button on the Data tab when cell A1 is current. Excel then opens the Data Selector task pane where you click Select under Microsoft Corp. Excel replaces the abbreviation in cell A1 with the corporate name and displays a Show Card button (with the bank icon) in front of the name and an Insert Data button (with the lined pad icon) after it. You can then click the Show Card button to display a pop-up menu with a list of financial statistics (see Fig. 4-9) that you can insert into the worksheet by clicking the Insert Data button that appears to the right of them when you position the mouse I-beam pointer over them.

The Geography data type

The Geography data type enables you to define a country, region, or state name (or abbreviation) entered into a worksheet as a particular geographical place. Once you do this, you can then quickly and easily insert all sorts of data about that place into your worksheet. Fig. 4-10 in the Data Types Updates gallery above shows how you associate the label, China, in cell A1 with the country of China by clicking the Geography command button on the Data tab and then clicking the Select button under the initial China entry in the Data Selector task pane. Fig. 4-11 then shows selecting the GDP statistic to enter into the worksheet (in cell B1) by clicking the Insert Data button and then clicking GDP on its pop-up menu.