Summary Report?

[sabroo2]

I'm working on some homework for a CIS class, that is pretty straight forward...except I don't know how to make a summary report in excel. any ideas?

-Stan

 

[silkfire]

Please present what you have and what you want. A detailed example. Copy from your spreadsheet.

 

[sabroo2]

<table style="border-collapse: collapse; width: 333pt;" width="444" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="5" width="64"> <col style="width: 93pt;" width="124"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 48pt;" width="64" height="17">State</td> <td class="xl65" style="width: 48pt;" width="64">Capital</td> <td class="xl65" style="width: 48pt;" width="64">Region</td> <td class="xl65" style="width: 48pt;" width="64">Population</td> <td class="xl65" style="width: 48pt;" width="64">Area</td> <td class="xl66" style="width: 93pt;" width="124">Population Density</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Delaware</td> <td class="xl65">Dover</td> <td>Middle Atlantic</td> <td class="xl65" align="right">783600</td> <td class="xl65" align="right">2057</td> <td class="xl66" align="right">380.9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Maryland</td> <td class="xl65">Annapolis</td> <td>Middle Atlantic</td> <td class="xl65" align="right">5296486</td> <td class="xl65" align="right">10577</td> <td class="xl66" align="right">500.8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">New Jersey</td> <td class="xl65">Trenton</td> <td>Middle Atlantic</td> <td class="xl65" align="right">8414350</td> <td class="xl65" align="right">7836</td> <td class="xl66" align="right">1073.8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">New York</td> <td class="xl65">Albany</td> <td>Middle Atlantic</td> <td class="xl65" align="right">18976457</td> <td class="xl65" align="right">49576</td> <td class="xl66" align="right">382.8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Pennsylvania</td> <td class="xl65">Harrisburg</td> <td>Middle Atlantic</td> <td class="xl65" align="right">12281054</td> <td class="xl65" align="right">45333</td> <td class="xl66" align="right">270.9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Arizona</td> <td class="xl65">Phoenix</td> <td>Mountain</td> <td class="xl65" align="right">5130632</td> <td class="xl65" align="right">113909</td> <td class="xl66" align="right">45.0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Colorado</td> <td class="xl65">Denver</td> <td>Mountain</td> <td class="xl65" align="right">4301261</td> <td class="xl65" align="right">104247</td> <td class="xl66" align="right">41.3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Idaho</td> <td class="xl65">Boise</td> <td>Mountain</td> <td class="xl65" align="right">1293953</td> <td class="xl65" align="right">83557</td> <td class="xl66" align="right">15.5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Montana</td> <td class="xl65">Helena</td> <td>Mountain</td> <td class="xl65" align="right">902195</td> <td class="xl65" align="right">147138</td> <td class="xl66" align="right">6.1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Nevada</td> <td class="xl65">Carson City</td> <td>Mountain</td> <td class="xl65" align="right">1998257</td> <td class="xl65" align="right">110540</td> <td class="xl66" align="right">18.1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">New Mexico</td> <td class="xl65">Santa Fe</td> <td>Mountain</td> <td class="xl65" align="right">1819046</td> <td class="xl65" align="right">121666</td> <td class="xl66" align="right">15.0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Utah</td> <td class="xl65">Salt Lake City</td> <td>Mountain</td> <td class="xl65" align="right">2233169</td> <td class="xl65" align="right">84916</td> <td class="xl66" align="right">26.3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Wyoming</td> <td class="xl65">Cheyenne</td> <td>Mountain</td> <td class="xl65" align="right">493782</td> <td class="xl65" align="right">97914</td> <td class="xl66" align="right">5.0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Connecticut</td> <td class="xl65">Hartford</td> <td>New England</td> <td class="xl65" align="right">3405565</td> <td class="xl65" align="right">5009</td> <td class="xl66" align="right">679.9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Maine</td> <td class="xl65">Augusta</td> <td>New England</td> <td class="xl65" align="right">1274923</td> <td class="xl65" align="right">33215</td> <td class="xl66" align="right">38.4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Massachusetts</td> <td class="xl65">Boston</td> <td>New England</td> <td class="xl65" align="right">6349097</td> <td class="xl65" align="right">8257</td> <td class="xl66" align="right">768.9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">New Hampshire</td> <td class="xl65">Concord</td> <td>New England</td> <td class="xl65" align="right">1235786</td> <td class="xl65" align="right">9304</td> <td class="xl66" align="right">132.8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Rhode Island</td> <td class="xl65">Providence</td> <td>New England</td> <td class="xl65" align="right">1048319</td> <td class="xl65" align="right">1214</td> <td class="xl66" align="right">863.5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Vermont</td> <td class="xl65">Montpellier</td> <td>New England</td> <td class="xl65" align="right">608827</td> <td class="xl65" align="right">9609</td> <td class="xl66" align="right">63.4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Illinois</td> <td class="xl65">Springfield</td> <td>North Central</td> <td class="xl65" align="right">12419293</td> <td class="xl65" align="right">56400</td> <td class="xl66" align="right">220.2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Indiana</td> <td class="xl65">Indianapolis</td> <td>North Central</td> <td class="xl65" align="right">6080485</td> <td class="xl65" align="right">36291</td> <td class="xl66" align="right">167.5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Iowa</td> <td class="xl65">Des Moines</td> <td>North Central</td> <td class="xl65" align="right">2926324</td> <td class="xl65" align="right">56290</td> <td class="xl66" align="right">52.0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Kansas</td> <td class="xl65">Topeka</td> <td>North Central</td> <td class="xl65" align="right">2688418</td> <td class="xl65" align="right">82264</td> <td class="xl66" align="right">32.7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Michigan</td> <td class="xl65">Lansing</td> <td>North Central</td> <td class="xl65" align="right">9938444</td> <td class="xl65" align="right">58216</td> <td class="xl66" align="right">170.7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Minnesota</td> <td class="xl65">Minneapolis</td> <td>North Central</td> <td class="xl65" align="right">4919479</td> <td class="xl65" align="right">84068</td> <td class="xl66" align="right">58.5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Missouri</td> <td class="xl65">Jefferson City</td> <td>North Central</td> <td class="xl65" align="right">5595211</td> <td class="xl65" align="right">69686</td> <td class="xl66" align="right">80.3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Nebraska</td> <td class="xl65">Lincoln</td> <td>North Central</td> <td class="xl65" align="right">1711263</td> <td class="xl65" align="right">77227</td> <td class="xl66" align="right">22.2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">North Dakota</td> <td class="xl65">Bismarck</td> <td>North Central</td> <td class="xl65" align="right">642200</td> <td class="xl65" align="right">70665</td> <td class="xl66" align="right">9.1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Ohio</td> <td class="xl65">Columbus</td> <td>North Central</td> <td class="xl65" align="right">11353140</td> <td class="xl65" align="right">41222</td> <td class="xl66" align="right">275.4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">South Dakota</td> <td class="xl65">Pierre</td> <td>North Central</td> <td class="xl65" align="right">754844</td> <td class="xl65" align="right">77047</td> <td class="xl66" align="right">9.8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Wisconsin</td> <td class="xl65">Madison</td> <td>North Central</td> <td class="xl65" align="right">5363675</td> <td class="xl65" align="right">56154</td> <td class="xl66" align="right">95.5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Alaska</td> <td class="xl65">Juneau</td> <td>Pacific</td> <td class="xl65" align="right">626932</td> <td class="xl65" align="right">589757</td> <td class="xl66" align="right">1.1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">California</td> <td class="xl65">Sacramento</td> <td>Pacific</td> <td class="xl65" align="right">33871648</td> <td class="xl65" align="right">158693</td> <td class="xl66" align="right">213.4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Hawaii</td> <td class="xl65">Honolulu</td> <td>Pacific</td> <td class="xl65" align="right">1211537</td> <td class="xl65" align="right">6450</td> <td class="xl66" align="right">187.8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Oregon</td> <td class="xl65">Salem</td> <td>Pacific</td> <td class="xl65" align="right">3421399</td> <td class="xl65" align="right">96981</td> <td class="xl66" align="right">35.3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Washington</td> <td class="xl65">Olympia</td> <td>Pacific</td> <td class="xl65" align="right">5894121</td> <td class="xl65" align="right">68192</td> <td class="xl66" align="right">86.4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Florida</td> <td class="xl65">Tallahassee</td> <td>South Atlantic</td> <td class="xl65" align="right">15982378</td> <td class="xl65" align="right">58560</td> <td class="xl66" align="right">272.9</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Georgia</td> <td class="xl65">Atlanta</td> <td>South Atlantic</td> <td class="xl65" align="right">8186453</td> <td class="xl65" align="right">58876</td> <td class="xl66" align="right">139.0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">North Carolina</td> <td class="xl65">Raleigh</td> <td>South Atlantic</td> <td class="xl65" align="right">8049313</td> <td class="xl65" align="right">52586</td> <td class="xl66" align="right">153.1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">South Carolina</td> <td class="xl65">Columbia</td> <td>South Atlantic</td> <td class="xl65" align="right">4012012</td> <td class="xl65" align="right">31055</td> <td class="xl66" align="right">129.2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Virginia</td> <td class="xl65">Richmond</td> <td>South Atlantic</td> <td class="xl65" align="right">7078515</td> <td class="xl65" align="right">40817</td> <td class="xl66" align="right">173.4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">West Virginia</td> <td class="xl65">Charleston</td> <td>South Atlantic</td> <td class="xl65" align="right">1808344</td> <td class="xl65" align="right">24181</td> <td class="xl66" align="right">74.8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Alabama</td> <td class="xl65">Montgomery</td> <td>South Central</td> <td class="xl65" align="right">4447100</td> <td class="xl65" align="right">51609</td> <td class="xl66" align="right">86.2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Arkansas</td> <td class="xl65">Little Rock</td> <td>South Central</td> <td class="xl65" align="right">2673400</td> <td class="xl65" align="right">53104</td> <td class="xl66" align="right">50.3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Kentucky</td> <td class="xl65">Frankfort</td> <td>South Central</td> <td class="xl65" align="right">4041769</td> <td class="xl65" align="right">40395</td> <td class="xl66" align="right">100.1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Louisiana</td> <td class="xl65">Baton Rouge</td> <td>South Central</td> <td class="xl65" align="right">4468976</td> <td class="xl65" align="right">48523</td> <td class="xl66" align="right">92.1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Mississippi</td> <td class="xl65">Jackson</td> <td>South Central</td> <td class="xl65" align="right">2844658</td> <td class="xl65" align="right">47716</td> <td class="xl66" align="right">59.6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Oklahoma</td> <td class="xl65">Oklahoma City</td> <td>South Central</td> <td class="xl65" align="right">3450654</td> <td class="xl65" align="right">69919</td> <td class="xl66" align="right">49.4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Tennessee</td> <td class="xl65">Memphis</td> <td>South Central</td> <td class="xl65" align="right">5689283</td> <td class="xl65" align="right">42244</td> <td class="xl66" align="right">134.7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Texas</td> <td class="xl65">Austin</td> <td>South Central</td> <td class="xl65" align="right">20851820</td> <td class="xl65" align="right">267338</td> <td class="xl66" align="right">78.0</td> </tr> </tbody></table>
Here are the problems...

a. Open the chap4_mid4_population workbook and save the workbook as chap4_mid4_population_solution.

b. Click in cell F2 and enter the formula to calculate population density (population divided by area). Copy the formula for all states. Format the population as number with 1 decimal.

c. Sort the range by region and then by state.

d. Create a summary report showing population subtotals by region. Use the Subtotal command to display the associated subtotal dialog bow. The subtotal should be calculated each change in region. Use the SUM function to add the total to the population field. Check the boxes to replace current subtotals and to place the summary below data.

e. Add a fill color to highlight the cells that contain the region name and the region's total population. Create a clustered column chart on a separate sheet that shows total population by region. Be sure to format the chart in an aesthetically pleasing manner, include a descriptive tital. Rename the Chart1 tab to Region Chart.

f. Insert a row on Sheet1, then type the title Population Statistics. Select cells A2:F60 and convert the list of data to a data table. Format the table using one of the table styles and format the title of the worksheet to match.

g. Filter the table by clicking the Region column arrow and select only Middle Atlantic, Middle Atlantic Total, New England, New England Total, South Atlantic, South Atlantic Total.

h. Create a pie chart as an object on the worksheet showing total population by the three geographic regions. Format the chart so it is color coordinated with your data table.

i. Use the Page Setup command to create a custom footer with your name, the date, and the name of the class you are taking. Display gridlines and row and column headings. De sure the worksheed and chart fit in one page.

I am mainly having a problem with d, if anyone can help

 

[Legacy 68668]

Record the macro first and show us the code.

 

[sabroo2]

I don't know how to do that...

 

[Legacy 68668]

I'm working on some homework for a CIS class

You've got a homework that you haven't studied yet ?

 

[sabroo2]

Yea, I don't have my lab for it until Fridays and the homework is always due online on Thursdays...so I usually end up learning how to do it after the homework it due...kinda asinine if you ask me...and I'm sure it doesn't help that I'm a procrastinator...But I'm pretty good with computers and I have an A in there so far, I just like to keep on top of things. Any ideas?

 

[shemayisroel]

I note you are mainly wanting help with item d.

Apart from the excel help file on SUBTOTALS maybe the below may help...

http://www.contextures.com/xlFunctions01.html#Filter

 

[Legacy 68668]

Yea, I don't have my lab for it until Fridays and the homework is always due online on Thursdays...so I usually end up learning how to do it after the homework it due...kinda asinine if you ask me...and I'm sure it doesn't help that I'm a procrastinator...But I'm pretty good with computers and I have an A in there so far, I just like to keep on top of things. Any ideas?

I don't know what you mean by good with computers...
How can I believe it, if you don't know even how to record a macro ?

 

[silkfire]

He's good with computers but he doesn't know all the functions of the Excel application.

I agree, 90% of the population only use 10% of Excel.