Animal Rescue Foundation Adoption Analysis
1
Excel Exercise
Practical 1
Animal Rescue Foundation Adoption Analysis
Edward Corwin works for the Animal Rescue Foundation. One of his responsibilities is
to collect and analyse data on the animals that enter the shelters. He has compiled a
list of the cost of housing animals by the local shelters for the past four years. After
following the directions below to complete the worksheet, your solution will be similar
to that shown here.
1. Open the workbook
Animal Housing.
2. Spell-check the worksheet and
correct any misspelled words.
| |
To Spell Check: From the Review tab, click the Spelling button. |
| 3. Adjust the width of column A so all the data is fully displayed. |
|
| | To Adjust Column Widths: Point to the dividing line |
| |
between the column
letters and when the
mouse pointer changes
to a double-headed
arrow then drag.
| | You can also double-click which will automatically |
set the column width to
the best fit.
4. Modify the title in cell B2 so the first letter of each word is capitalized.
5. Insert a blank row above row 6.
| |
To Insert a Blank Row: Right click on the row number and then choose Insert from the pop-up menu. |
You can drag over the row numbers to select more than one row and then
right-click and choose Insert to insert more than one row.
2
6. Increase the font size of the title in cell B2 to 14 point and change the row
height to 22.5 (30 pixels).
| |
To Change Row Height: Change Row Height by dragging the row between 2 and 3 to 22.50 (30 pixels) |
7. Merge and centre both title lines across columns A through E.
Merge cells by selecting A2 through to E2 and press to Merge and
Centre. Repeat for the subtitle.
8. Delete the label ‘Shelter’ in cell A5.
The easiest way to delete the cell contents is to press the [Delete] key.
9. Bold and centre the remaining headings in row 5.
10. In row 17, enter a function to total the data under the 2009 column and a
function to total the data under the 2010 column.
Enter SUM Function:
In B17 click the AutoSum button (Home tab, ) to enter function:
=SUM(B7:B16) to total the data for 2009.
11. Copy the Total function in cell B17 to calculate the total for each of the new
years.
| |
Using AutoFill (to copy into adjacent cells): For 2010 you have to use the same function but change the cells to C7:C16. |
An easy way to do this is to use AutoFill. Select cell B17 which has the SUM.
Point to the bottom right corner of cell and when the mouse pointer
changes to a small black cross then drag to fill the formula into other cells.
12. Format the numbers in rows 7 and 17 using the Accounting style with zero
decimal places. Format the numbers in rows 8 through 16 using the Comma
style with zero decimal places.
| |
To Format in Accounting Style: Select numbers in row 7 and 17 and choose the Accounting Style from the Home Tab – General – Accounting |
Click on to decrease decimals to zero
decimals.
Choose to set comma style for numbers in
row 8 through to 16. Again decrease decimals
to zero decimals.
3
13. Edward has just received the information for the last two years. Enter the
following data in the cells indicated.
| Row | Col D | Col E |
| 5 | 2011 | 2012 |
| 6 | ||
| 7 | 142600 | 152800 |
| 8 | 213500 | 220300 |
| 9 | 12500 | 14700 |
| 10 | 2200 | 4200 |
| 11 | 1400 | 1600 |
| 12 | 148500 | 142400 |
| 13 | 11700 | 10500 |
| 14 | 18900 | 25300 |
| 15 | 14200 | 13500 |
| 16 | 26700 | 29900 |
14. Format the column heads to match the style of the corresponding information
in columns B and C. Use AutoFill to copy the SUM function in cell C17 into cells
D17 and E17.
15. Move the row of ASPCA data to above the row of FOCAS data.
| |
To Move a Row: Select the row by clicking on the row number. The whole row will be highlighted. Point to the top or bottom edge of the row and wait for the mouse pointer to change to a four-cornered arrow. Hold down the [Shift] key and then drag the row to insert it at its new position. If you do not hold [Shift] then the row will copy over another row. |
| | |
| |
16. Add font and fill colours to the worksheet as you
like.
17. Insert a blank row on row 17 (or just move the
Total heading down one row).
18. Add a thick box border around A1 through E18.
| |
To Add Thick Box Border: Select A1 through to E18 and choose ‘Think Box Border’ from Borders on the Home tab. |
4
19. Next, you want to create a chart showing the annual data for the three largest
shelter groups. Select the range A5 through E9 and create a chart displaying the
data. Move the chart to cell A20.
| |
To Create a Column Chart: Select A5 through to E9 and go to Insert tab and click on Column Chart – 3-D Clustered Column Chart. |
| |
To Move a Chart: Point anywhere on the background of the chart and when a four-corner arrow appears then drag. |
20. Enter your name in cell A35 and the current date in cell A36. Format cell A36 to
display the month, day, and year (14 March 2001) date format.
21. Save the workbook as Animal Housing Analysis to your solution file location.
22. Preview the worksheet using landscape orientation so that it fits on one page.