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

ExcelZone Compendium: Advanced data entry tips. By Simon Hurst

by
25th Jan 2007
Save content
Have you found this content useful? Use the button above to save it to your profile.

We already have an Excel Zone Compendium for Data Entry Basics. This digest looks at some more advanced techniques for working with data, particularly linking Excel to external data sources and using Data Validation and Excel form controls.

Spreadsheets are good at a whole range of things, but there lack of inbuilt structure doesn't make it very easy to control and manage the entry and storage of large quantities of data. If possible it's a good idea to enter and store your data in a database or dedicated application and then link Excel to the data. So to start with we'll look at how to get at the data within an external application and use Excel for analysis or reporting on the data.

Use a pivot table
One of the best ways to work with existing data is to use a pivot table. The mechanics of pivot table s are covered in great detail elsewhere on this site. The pivot table compendium is a good place to start. The key point to note is that the first step of the Pivot Table and PivotChart Wizard allows you to tell Excel where the data you want to analyse is. The key choices are:

  • Microsoft Office Excel List or database
  • External data source
  • Multiple consolidation ranges

.
The first option uses data held somewhere within Excel; the second option is perhaps the most important, and allows you to create a dynamic link from your Excel pivot table to the data in your database or accounts application; finally, the third option lets you include different ranges of Excel-based data in a single pivot table .

If you want to analyse your data in Excel, try a pivot table that links directly to an external data source first. If this doesn't do what you need then there are several other options.

Open a text file directly
If you can't link to your data directly, you may be able to create some sort of text file from your accounting or other system. Typically this will be a comma separated value (CSV) file, so-called because each element of the data is separated from the next by commas, and each record is on a separate line.

You can open text files directly in Excel. Just use File-Open and change the Files of Type option to Text Files. Excel treats text files differently depending on the file extension - if the file ends in .CSV Excel assumes that the file is a correctly constructed CSV file and automatically uses any commas to allocate different fields to different cells, and places each line of the text file on to a new row.

If the file ends in .TXT then the Text Import Wizard will open. This allows you to specify how to deal with the contents of your text file and copes with other text file structures than CSV. It can often be useful to use the Text Import Wizard even with a CSV file as it allows you to specify how to treat particular fields - for example you can tell Excel that a number field uses a trailing minus, or you can skip fields that you don't want to import.

Import External Data
The Import External Data option on Excel's Data menu gives you other options for working with external data. The Import Data option lets you choose from a wide variety of different data sources including databases and text files. Using this option with a text file will launch the Text Import Wizard and import the data into an existing workbook.

The imported data forms what Excel calls an 'External Data Range' and is still linked to the original file. If you right-click within the range you can manually refresh the data to incorporate any changes in the file and you can also review and edit the Data Range Properties. These are well worth investigating as they can be used to set up and control automatic refreshing of the data.

Lurking at the bottom of the Data Range Properties screen is a very useful option: 'Fill down formulas in columns adjacent to data'. If you need to manipulate the external data in some way by adding further columns, then this option will ensure that Excel automatically copies formula cells adjacent to the data range to fill the same number of rows as the external data occupies.

Import External Data also includes options to create new web queries and database queries. Excel Zone has covered the use of Microsoft Query in a series of tutorials based on linking Excel to Sage Line 50. Web queries can be useful in creating links to information held in a structured way on an internet site, but you do have to be aware that you have to rely on the structure of the page you are linking to remaining reasonably constant.

Text to columns
There is another option on the Data menu that can be useful when working with external data, particularly that derived from text files or maybe information downloaded directly from some Internet banking sites. The format of data provided by third parties can vary quite considerably and you may find that you end up with your data all in one column, rather than correctly allocated across several different columns. If this happens, you can select the data and then use the Data-Text to Columns option to launch the Text to Columns Wizard, which operates in the same way as the Text Import Wizard. You do need to be careful that you don’t have important data or formulas in the columns immediately to the right of the existing data, as the Text to Columns Wizard will use these columns to hold the newly separated data fields from you original column of data.

If you are working with imported data, it's certainly worth taking a look at the Excel Compendium on Importing and Exporting. This includes some useful help on sorting out problems with the imported data once you've got it into Excel.

Data validation
If you do need to enter data directly into Excel, the Data Validation feature can make it easier for the user and also exercise control over what is entered. Having selected the cell or range of cells that you want to use to enter a consistent type of data, choose Validation from the Data menu.

Data Validation allows you to allow the entry of only particular types of data - such as only whole numbers, dates or text of a particular length. Once you have specified the type of data, you can then set criteria to only allow entry of items within certain ranges. Having specified the data that is allowed, you can then set up an input message that the user will see when they click on the cell and also control how items that contravene the data validation rule should be treated.

An Excel Zone IT Basics tutorial covers simple Data Validation techniques.

A particularly useful option within Data Validation is to allow items from a list. When you choose 'List' as the allow value you can specify a source range of cells where the list values are stored. At first glance it seems that you can only link a Data Validation list to a range on the current worksheet - if you try and enter a reference to another sheet as the List source Excel will warn you, "You may not use references to other worksheets or workbooks for Data Validation criteria." However, if you allocate a range name to the group of cells on the other sheet, you can use that range by entering '=MyRangeName' as the source. (Where 'MyRangeName' is obviously replaced by the actual name that you allocated.

Frank Ahearn provided some very useful information in an Any Answers contribution as to how to work with data validation lists using Visual Basic.

Form controls
Another way to provide a user with some interactive help when entering data is to use the form controls. Select View-Toolbars and then select the Forms option. This should display the Forms toolbar which includes as whole series of different interactive controls. Many of these controls are concerned with entering information - check boxes and radio buttons, list boxes and combo boxes and spinners for example. The basic technique is similar for these controls. Click on the control you want to use, 'draw' it where you want it on your worksheet, then right click on it to set its properties and to assign a macro to the control if you need to. Whilst form controls are independent objects that sit on top of the cells in your worksheet, if you place a control within a single cell, you can then copy that cell and the form control it contains will be copied with it, although you'll probably still need to set one or more of the properties manually.

An Excel Clinic article has more on the comparison between data validation and form controls for working with lists.

About the author
Simon Hurst is the founder of Horsham-based technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty. Some of the tips in this guide are taken from from Simon's guide, 100 ways to save time with Microsoft Office, available from AccountingWEB.

Related articles

  • ExcelZone Compendium - a catalogue of more than 400 articles, queries and tips
  • ExcelZone Compendium: Data entry basics
  • Data validation for better spreadsheets
  • Want to learn about Excel pivot tables? Start here
  • ExcelZone's Five-Minute Pivot Table Tips
  • Subscribe to the ExcelZone newswire
    To keep up with spreadsheet issues, click the button below 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 (0)

    Please login or register to join the discussion.

    There are currently no replies, be the first to post a reply.