Solved

Using Excel to Create the customers profile

Posted on 2012-03-29
2
163 Views
Last Modified: 2012-08-13
Hello
I have an excel sheet like this example
Name      City       State      Age      Occupation
Jack      Boston      MA       25-40      Student
Paul      Miami      FL      40-50      Vetern
Nick      Boston      MA      40-50      Veteran
Nick      Los Angeles      CA      25-40      Business owner
                        


I want to filter the spreadsheet in an order to be able to create the customer profile for example  how many register from a specific State what is the average age and  occupation. After that a want to create a pie diagram to present the results.  
thank you
0
Comment
Question by:kriftsi
  • 2
2 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 400 total points
ID: 37785589
Excel has a tool called PivotChart that is perfect for your application.

Put your data in a Table
1) Select any cell in your data
2) Use the Insert...Table menu item (and click the prompt OK) to turn the data into a Table. Make sure the box for "My table has headers" is checked.

Create a PivotTable with PivotChart
1) Select any cell in your data
2) Use the Insert...PivotTable...PivotChart menu item and click OK in the resulting wizard. This will create a blank PivotTable on a new worksheet.

1) Drag State into the Axis field
2) Drag Name into the Values field
3) Right-click the chart and choose Change Chart Type ....Pie chart

Repeat the steps to create a PivotTable with PivotChart on previous page plus this one for Age and Occupation
PivotChartQ27655187.xlsx
0
 
LVL 81

Expert Comment

by:byundt
ID: 37785593
The preceding instructions are written around Excel 2007 and 2010.

Excel 2003 and earlier will vary somewhat because there is no Table feature. You can use a dynamic named range instead with a Refers to formula like:
=$A$1:INDEX($E:$E,COUNTA($E:$E))

If the wizard uses a fixed address for the data, replace it with the name of the Table (or the dynamic named range).

If you add, delete or change data, you will need to right-click your charts and choose Refresh to get them to update.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question