Segmentation is the process of classifying information into groups that share common characteristics. In marketing, for example, it is valuable to know which segments of a market are most likely to purchase a product before investing in packaging and advertising. Microsoft Excel 2007 gives you a variety of ways of segmenting data. The more data you have, as well as different types of data, the more useful segmentation analysis will be.
With Excel 2007 it is only possible to look at segmentation for one type of data at a time. Because you will probably not know which types of data are important until you have started segmenting them, you should create a new worksheet for each type of data to be analyzed. For example, if you have a worksheet for 100 clients showing age, gender, and income, you could look at segmentation for age on one worksheet, gender on a second and income on a third. Each of the variables should be organized in columns. Use numbers instead of letters to identify gender (e.g. 1 for male, 2 for female) or other non-numerical data.
Segmentation by Frequency
The Frequency function in Microsoft Excel is located in the Statistical Function Category, which can be found by clicking the arrow beside the AutoSum button. This function requires two columns of data: a data array and a bins array. The raw data is sorted through the bins. To create a bins array, add a new column at the end of the worksheet and type the numbers you want the data to be sorted through. For example, to sort ages into groups, you could type "20," 30," "40" etc. in a new column. The Frequency function will determine how many people are 20 years or younger, 30 years or younger and so on. When you select the Frequency function, a new window opens. You can then highlight the columns for the data array, then the bins array to select them for analysis.
Segmentation by Relative Distribution
After segmenting your data by frequency, you can use Excel to calculate the relative distribution of each segment compared to the total. Before calculating relative distribution, you should first calculate the total of the frequency segments, which you can do simply by clicking the first blank cell at the bottom of the frequency column and clicking the "AutoSum" button. This number should be the same as the total number of instances of raw data. In a new column, you can then divide each segment by the total to calculate its relative distribution and display it as a percentage of the whole.
Comparing Multiple Segments
Once you have examined the different sets of data in segments, you may find some segments are relevant while others are not. You should then open a new worksheet and look at segments within the most relevant groups. For example, if you discover clients are evenly divided between gender and income level, but a majority are below the age of 30, you could then remove everyone above that age and look at segments within this major group. Separating two segments, such as gender, and analyzing them independently may also reveal important subsegments within them. For example, you may find that most women are in one age group or income level, while men are in another.