Using Microsoft Excel to Manage Data

Discussion in 'Windows XP / Older OS' started by jhonybush, Sep 1, 2010.

  1. jhonybush

    jhonybush MDL Novice

    Sep 1, 2010
    Microsoft Excel was designed primarily for number crunching and financial analysis; however, it also has some great data manipulation features that any small business can use. Yes, if you want to do anything with numbers - add, subtract, multiply, divide, total, average, you name it - then Excel is the program to use. Add to that the functionality of Charting your data and you have the power to run your business at your fingertips.

    In preparing for a recent webinar on Excel Tips, we asked our participants to send in their questions. Many of the questions involved the ability to manipulate data and Excel has some great features for this purpose.

    Completing data - the software we use to deliver our webinars provides a list of participants in an Excel spreadsheet. I need to add a column to the worksheet with the name of the webinar. I simply add a column heading and type the title in the first cell of the column. I can then double-click on the auto-fill handle (a thin plus sign in the lower right hand corner of the cell) and Excel will fill down the column with the same text until it sees a blank cell in the column to the left. I can then take the information in this spreadsheet and copy and paste it into my master spreadsheet of all webinars we have offered to date.

    Sorting - There are two icons on the ribbon bar that allow you to sort your data. One is for ascending (a-z) and the other for descending (z-a). Click in the column you wish to sort and select the desired direction. If you wish to sort by more than one column, you would use the Data/Sort menu option and Excel allows you to sort by up to three columns.

    Filtering - You can limit your view by filtering what you see. You will need to have a column heading in row one and then select Data/Filter/Autofilter. This will place a down arrow next to each heading. By clicking the down arrow and then a selection from the list, you are filtering based on your selection. Use the Custom option on the list to filter by set criteria such as "over a certain dollar value."

    Subtotals - This is a great feature in Excel that allows you to create totals, counts, averages based on the change in a column. In my example of webinar attendees, I can count the number of people who attended each webinar. I simply sort the webinar column, and then use Data/Subtotals. A dialog box will appear asking me what I am counting (in my case attendance), what function I want to use, Count, and what column am I subtotaling - the webinar name.

    Creating labels or letters - When doing a mail merge with Microsoft Word, one of the data choices is Excel. You can select your list and create a marketing letter or labels. By using the Filter feature mentioned above, you can limit the number of letters to specific criteria, for example a thank you letter to customers you serviced in the last month.