Myrtle Beach Computer Repair On Managing Lists With Excel
One great use of Excel that businesses often overlook is list management. You can purchase a list of local contacts from your Chamber of Commerce or from a trade magazine. What do you do with the list? Here are a few tips on using Excel to manage your list.
Are the first and last names in the same column? Excel has an easy way to separate the names.
One great use of Excel with lists is the filter feature. To turn on the filter option, simply click on the filter button on the Data ribbon bar. Excel will place a down arrow next to each of your column headings. You can filter on a single value or get more sophisticated with your sort. For example, if you are filtering on text data, use the text filter options for words that begin with a letter. I really like the “Contains” option. For example, I have a column that says where people heard about our courses. Since this field is a free form fill, they will type in all kinds of data. I can use the “Contains” to find all the records for CCAR or some other organization.
If you are working with numbers, you can use the numeric filter options to find values that are over or under a certain amount. This same filter would apply to dates. Spend some time experimenting with filter options and you will find out valuable information about your list.
One great option with Excel 2007 and above is the remove duplicates. Prior to creating a mailing label or some other form of communication, you will want to remove duplicates. Make sure that you have a backup copy of your spreadsheet prior to doing this function. On the data menu is a Remove Duplicates button. You will be asked which fields you want Excel to look for duplicates. Be careful with this. If you say first and last name, you may have two John Smiths. Add another field for clarification. If you have multiple contacts at one address, you may want only one piece of mail to go to the location. You can remove duplicates based on address and city.
Once you have cleaned up your list, you can use Microsoft Word to create letters or mailing labels. There is a wizard in Word that will walk you through this process. Use the Mailings tab and the Start Mail Merge button. Then select Step-by-Step Mail Merge Wizard. The biggest caution I can give you is to be sure to click on the button that says Update All Fields when you get to step on adding the address block. This is where I see most people go wrong with their merges.
Another useful tool with lists is the Pivot Table tool which is found under the Insert tab. You can add a pivot table to the existing worksheet or a new worksheet. Use the chart on the right and start with a simply table by selecting what you want to be displayed as rows and what values you want to display. I have a worksheet that I keep all of my personal deductions for tax purposes. I list each check I write and the amount and the deduction type. By selecting the deduction type as my row headings and the amount as the field I wish to summarize, I will get a simple list showing my deductions (donations, interest, insurance, etc) and the total amount for that deduction.
You can get more detailed with your table by adding a filter or column headings. It is called a pivot table because you can move fields around and get a different point of view on your data. I encourage you to play with this feature as a great analysis tool.
Call us today at 843-488-4100 to schedule a private training session on any of the features in Excel, Word or other Microsoft Office products. You will learn and apply these principles quickly with personalized training.
One great use of Excel that businesses often overlook is list management. You can purchase a list of local contacts from your Chamber of Commerce or from a trade magazine. What do you do with the list? Here are a few tips on using Excel to manage your list.
Are the first and last names in the same column? Excel has an easy way to separate the names.
- Simply insert two new columns next to the name column (select the two columns, the right click and choose insert.)
- Select all of the names in the column you wish to separate.
- Click on the Data tab and click on the Text To Columns button.
- Select space delimited and click on OK.
If you are working with numbers, you can use the numeric filter options to find values that are over or under a certain amount. This same filter would apply to dates. Spend some time experimenting with filter options and you will find out valuable information about your list.
One great option with Excel 2007 and above is the remove duplicates. Prior to creating a mailing label or some other form of communication, you will want to remove duplicates. Make sure that you have a backup copy of your spreadsheet prior to doing this function. On the data menu is a Remove Duplicates button. You will be asked which fields you want Excel to look for duplicates. Be careful with this. If you say first and last name, you may have two John Smiths. Add another field for clarification. If you have multiple contacts at one address, you may want only one piece of mail to go to the location. You can remove duplicates based on address and city.
Once you have cleaned up your list, you can use Microsoft Word to create letters or mailing labels. There is a wizard in Word that will walk you through this process. Use the Mailings tab and the Start Mail Merge button. Then select Step-by-Step Mail Merge Wizard. The biggest caution I can give you is to be sure to click on the button that says Update All Fields when you get to step on adding the address block. This is where I see most people go wrong with their merges.
Another useful tool with lists is the Pivot Table tool which is found under the Insert tab. You can add a pivot table to the existing worksheet or a new worksheet. Use the chart on the right and start with a simply table by selecting what you want to be displayed as rows and what values you want to display. I have a worksheet that I keep all of my personal deductions for tax purposes. I list each check I write and the amount and the deduction type. By selecting the deduction type as my row headings and the amount as the field I wish to summarize, I will get a simple list showing my deductions (donations, interest, insurance, etc) and the total amount for that deduction.
You can get more detailed with your table by adding a filter or column headings. It is called a pivot table because you can move fields around and get a different point of view on your data. I encourage you to play with this feature as a great analysis tool.
Call us today at 843-488-4100 to schedule a private training session on any of the features in Excel, Word or other Microsoft Office products. You will learn and apply these principles quickly with personalized training.
No comments:
Post a Comment