Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel Zone Compendium: Working with external data (Excel 2007 update)

by
24th Sep 2008
Save content
Have you found this content useful? Use the button above to save it to your profile.

Although Excel includes some functionality to help you work with data, it's generally easier and more reliable to leave data collection, processing, management and storage to packages that are designed for the job, writes Simon Hurst. But this warning doesn't mean you can't use Excel to interrogate, analyse and report on that data. Used sensibly, Excel has a range of tools that can help you get at data held in external systems including text files and web pages.

Practical issue: Use Excel as a window on your data sources

Because of its familiarity and flexibility, many people prefer to carry out analysis work and create reports within a spreadsheet. If you have access to external sources of data, one of the first issues you need to consider is whether you want to work on a static copy of the data, or create a link to the actual data, that can be refreshed manually or automatically to bring in the up-to-date data.

Solutions: Create a static copy of the data

If you just want to grab a set of data and work on it as is, with no need to update it in the future, then a simple copy and paste will do the job. For example, you can drag an entire Access database table or query into an Excel spreadsheet, or use copy and paste.

If you just want some of the records, or some of the fields, then you can open the table or query, select all or some of the rows and columns and copy and paste again. Whichever way you do it, the data will automatically be split into separate cells within the Excel spreadsheet. The data source doesn't need to be an actual database. Excel will cope with a variety of different data formats: for example items - email, contacts, events - from a list view in Outlook, a Word table and, rather less reliably in terms of format, information from a web page.

If you had a more complex requirement, maybe combining data from more than one source, then you could use a database such as Access as an intermediary step to assemble the data before copying it to Excel.

Create a dynamic link to existing data

The term "import" might suggest a one-time static transfer of data into Excel, but Excel's data import options actually create refreshable links to the underlying data. The Excel 2007 data ribbon makes the available data tools much more visible than they used to be. In former versions the different data import options could be found on the Data-Get External Data menu, or from Excel XP onwards via the Import External Data option.

There is a very significant difference between the way Excel 2007 works with external data and previous versions. Prior to 2007 the Get/Import External Data-New Database Query option launched the Choose Data Source dialogue. Once you had chosen your database you were taken to the Query Wizard screen to select the fields of data that you wanted to become the columns of data in Excel:

In Excel 2007 the From Access option in the Get External Data section of the Data ribbon will let you choose the required database, but then only gives you the option to bring in "all or nothing" of each table and query within your database, with no chance of selecting particular fields:

To use the query wizard in Excel 2007 you need to choose the From Other Sources option (even if you are using an Access database) and then choose the From Microsoft Query option. This will take you to the Choose Data Source screen that you would have used in previous versions of Excel and which leads on to the Query Wizard.

Choose your linked PivotTable fields in Excel 2007

Just as in previous versions of Excel, when you insert a new PivotTable you are given the choice of using an External Data Source. If you choose to do so, you will be able to use the Choose Connection option to link the PivotTable to your chosen data source but, just like the From Access option described above, this will only allow you to choose whole tables or queries rather than individual fields within those tables or queries. You can then just choose to add the fields you are interested in to the Pivot Report itself.

If you only want to work with a restricted range of fields, then it is possible to still use the Query Wizard with a PivotTable. Rather than using the Insert, PivotTable option, you need to use the Data ribbon, Get External Data section, and From Other Sources option (again described above). When you have selected the fields you want, and choose Return the Data to Microsoft Excel, the Import Data screen will appear with the option to turn the data into a PivotTable Report or PivotChart and PivotTable Report.

Link to data on a web page

Although the ability to bring in data from a web query existed as far back as Office 97, from Office XP onwards Excel has used a visual approach. Data-Import External Data-New Web Query (Excel 2007: Data ribbon, Get External Data section, From Web) will let you browse to the required page and then select one or more tables of data within the page. The different tables are indicated by small yellow boxes containing arrows that change to green boxes with a tick when selected. Of course, apart from any security concerns of linking to external data, you are also dependant on the web page using tables in a way that makes the data usable in Excel. Perhaps even more importantly, if you link to a web page that is outside of your control, any changes in the design of that page are likely to have unpredictable effects on your linked data range.

Manage external data ranges

Once you have linked an Excel workbook to your chosen data, the data will duly appear. Prior to Excel 2007 it will look like any other Excel list, but when you click in it the External Data toolbar should appear, and if you right click in the area, the shortcut menu will include options to refresh the data, edit the underlying query and edit the External Data Range Properties. The properties dialog can be used to change the range name, and control the refresh options. In addition, you can control the formatting and how Excel deals with changes in the number of rows in the data range after a refresh.

To convert an external data range back to a normal Excel list that is no longer linked to the original data, untick the Save query definition option in the External Data Range Properties dialogue.

In Excel 2007 the data will appear as a new Excel 2007 table. Some of the main properties of Excel tables have been covered previously on AccountingWEB.co.uk. Instead of an External Data toolbar and an external range right click menu, the Properties option in the Connections section of the Excel 2007 Data ribbon will become active when you click in a table linked to external data. Most of the options from the old External Data Range Properties dialogur are accessed via the Connections button in the Connections section. This lists all the connected data ranges in the workbook and you can select a particular connection to view its Properties and see where it is used within the workbook, or to remove it.

Though it's not specific to external data, the Excel 2007 Data ribbon includes a very useful tool in the Data Tools section to Remove Duplicates from a list. This allows you to select one or more columns that could contain duplicates and it will then delete any items where the data in the chosen columns is duplicated.

Export Excel data

You can of course use copy and paste to extract information from within an Excel spreadsheet for use in another application. To export the data from an Excel spreadsheet into a specific format, try File-Save As and investigate the different available file types in the Save as type box. (Excel 2007, Office Button, Save As, Save as type) .

Further reading
ExcelZone Compendium: Importing and Exporting digest
Importing data via scanning and OCR
Export Excel data to a tax software system
An introduction to Excel and Microsoft Query
Use MS Query to extract and analyse accounts data - David Carter's reporting tutorials
ExcelZone Compendium: Working with accounts data and other applications
ExcelZone Compendium: Imports, exports & ODBC and links to more queries and answers.

Subscribe to the ExcelZone newswire
To keep up with spreadsheet issues, click the graphic to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

Replies (1)

Please login or register to join the discussion.

avatar
By bsnashall
31st May 2017 23:05

Sage data to Excel 2003/2007/2010

The Analyser.  - Excel plug-in

At the click of a button, “The Analyser” extracts actual current and budget data from nominal codes, departments and funds for any date range and for any combination of nominal ranges. Additional benefits include the option to add prior year’s data alongside the current year for comparative purposes and to report on single or multiple Sage companies, all in one workbook

The Analyser was born out of the constraints other report writers place upon the user. It is an impressive yet simple, timesaving Reporting Tool that extracts the figures directly from the Sage data into Excel

Also the drill down feature allows the user, from the face of their management information, to drill down to the nominal activity not just for one code but any combination of codes all from Excel without having to open Sage.

Together with The Organiser they could then go one step further and display the scanned image. Again, all from Excel without opening Sage.

The added benefit is that the numbers are returned to Excel so the user is able to use Excel reporting and graphing capabilities to further enhance their reports. Its as good as you are in Excel.

[email protected]

 

Thanks (0)