Splitting Lists - Excel 2003 and 2007

Splitting Apart Lists (Excel 2003 & 2007)

For additional information, check out the Excel Functions and Formulas class.

This is one of my favorite Excel tips for 2 reasons:

1. It is incredibly simple and easy, once you get the hang of it, 
2. Most people do not know about it.

I am surprised to find, even when teaching intermediate to advanced-level students, how many are not aware of this tip.

Here it is.

Sometimes we receive information that needs to be parsed. For example, a list of names that needs to split into first and last names. Excel makes it easy with the little used Text to Columns feature in the Data menu.



Before you begin:


Make sure you have enough columns to the right of your list. If, for example, your list contains, first, middle & last names, you should have at least 3 blank columns to the right of your list.


To Split Text (Excel 2003 Instructions).

1. Click the Data menu, then Text to Columns.
   
2. If characters, spaces, commas or tabs separate your list, select Delimited.
   
3. If columns or spaces separate fields, select Fixed width.
   
4. The next screen will show you what your list will look like parsed.
   
5. If it looks okay, click the Finish button.
   
   
   
   

To Split Text (Excel 2007 Instructions).

1. Select the Data tab.
   
2. Click the Text to Columns button on the Data Tools option.
   
3. Follow steps 4-6 shown above.
   

Access Database Design Process - Overview

Designing a database is both an art and a science. In our Access Database Design Bootcamp course, I teach a step-by-step process to designing a database. The class also includes a case study, where students have the opportunity to design a database in class.

Source: Access Database Design Bootcamp.

1. Gather Requirements.
   Interview the primary decision maker and key database users to determine the purpose and scope of your database. Collect sample files and documents to determine what they are currently using to accomplish database activities. In some cases, the team may be using a hodge-podge of Excel and Word files to accomplish the tasks they want the database to handle.
   
   
2. Sketch Forms & Reports.
   Sketch all forms and reports on paper and give them to the key users to approve.
   
   
3. List fields.
   Use the approved forms to list every single item you need to track in the database. These will become your database fields. Describe what each field is supposed to track.
   
   
4. Map fields to tables (on paper).
   Create a preliminary list of tables. Assign each field to a table.
   
   
5. Normalize your draft tables (on paper).
   Normalization will reduce redundancy and errors.
   
   
6. Establish relationships between tables (on paper).
   Link tables to establish 1-to-many relationships, and create linking tables to implement many-to-many relationships.
   
   
7. Create tables (in Accesss).
   Now that you've sketched your tables on paper and mapped your relationships, create your tables in Access and enter sample data.
   For more information, see our Access Module I - Tables Class.
   
   
8. Create your queries, forms and reports (in Access).
   Use the documents you've worked on thus far to create your queries, forms, and reports. For more information, see our Access Queries, and our Forms & Reports classes.
   
   
9. Test your database.
   Test your database yourself, and then present it to your key users to review and test. Repeat this process until you have a deliverable database.
   

Excel 2007 - Creating Custom Lists

In a recent Introduction to Excel 2007 training session in Atlanta, I went over one of my favorite Excel features: lists.

Excel's lists feature is an incredible time saver. If Excel recognizes certain items as a list, you do not have to enter these items. All you do is enter the first item, click and drag and Excel will fill in the rest.

Here's an example. If you enter the word "January" in a cell, and use your mouse button to drag down from the lower right hand corner of the cell, Excel will recognize this as the beginning of a list. As you can see from the diagram below, a box pops up showing the subsequent months.




When you release the mouse, Excel will fill the cells with the months
as shown below.




Excel's fill series even works if the list moves from left to right.




Anything that Excel recognizes as a series can be used in this way.

Here are some examples:

• Days of the week,
• Years,
• Quarters (ex., Qtr 1, Qtr 2, etc.).

Here's where this feature gets even more powerful - did
you know that you can create your own list to use in Excel?

Two doctors were taking my Excel training class because they were opening a medical practice in Atlanta. One list they thought would be extremely helpful would be a list of their insurance companies.

Setting up a custom list of insurance companies would provide the doctors the following benefits:

• Save time - Because they wouldn't have to keep typing the same information over and over.
• Reduce typos - Since the list is stored in one place, there is no chance of reducing the typos.
• Increase efficiency - When an insurance company is added or removed, they simply have to edit the list.

Setting up a custom list.

Enter the values for your list in a range of cells (ex., Cigna, Aetna, Kaiser). Make sure to enter the items in the list in the order you need them to appear. For example, if it's important that the names of the insurance companies are in alphabetical order, type them in alphabetical order (ex., Aetna, Cigna, Kaiser).

Select the range of cells.




Click the Microsoft Office button.




When the menu expands, click the Excel Options button.




Click the Popular category.

Under Top Options for working with Excel, click Edit Custom Lists.





In the Custom Lists dialog box, you will notice the cell reference of the items that you selected is displayed in the Import list from cells box.




Click Import to add items in the list that you selected to the List Entries
box.




Click OK twice.


Now you can type any word in the list, click and drag, and Excel will fill in the rest.