Application Exercises: Project 1 – Excel

Exploring Microsoft Excel 2016, Chapter 1

Home Town Realty

You work for a local real estate agency called Home Town Realty. It has a running Excel workbook of all properties it is permitted to show including both sold and unsold properties. The sales people use this workbook as a reference point to see whether houses have sold or new listings are available when out with prospective buyers. You received a new set of listings from another agency and need to add them to your existing workbook.

Perform Preliminary Work

You will create a new worksheet title row to enhance the worksheet’s appearance.

1. Open 01_test_chap_data1 and save as 01_test_chap_LastFirst.
2. TypeHome Town Realty in cell A1. Merge and center cells A1:H1.
3. Change the column width of column D to 0 characters (40 pixels).

Add Newly Provided Listings

North Hills Properties has provided new listings that need to be added to the existing listings you have on file.

1. Add a new worksheet named NH Listings.
2. Open 01_test_chap_data2 and copy the new listings. Close 01_test_chap_data2. Use the Paste option (Keep Source Column Widths)to paste the new listings into the NH Listings worksheet.
3. You realize the column names do not match your existing listings. Inserta column beforeStreet and enterHouse IDin A1. Type112 into cell A2.
4. Create a formula in cell A3 that increases cell A2 by 1. Use AutoFill to add House ID to each row in the worksheet.
5. Insert a column before Soldand label itListing Agency. Add the listing agency in cell F2 as North Hills Properties. Use the AutoFill to fill the remaining Listing Agency fields as North Hills Properties.
6. Copy cells A2:H5 from the NH Listings worksheet and paste them into cell A13 in the Listing worksheet.
7. Apply text wrapping to column F so that the entire listing agency name is visible.

Manually Update Records

You are missing house 111 from the list that was sold last week. You need to re-enter the data.

1. Insert a row after House ID 110 and enter the following information:
   • House ID = 111
   • Street = 1243 Ben Franklin Pkwy.
   • City, State, Zip = Philadelphia, PA 19121
   • Listing Agency = Home Town Realty
   • Sold = Yes
   • Date = 4/12/17
2. One of the sellers has decided to not sell their property and wants to have their listing removed. Delete the entire row for House ID 108.