My Holiday Wish List
You have been asked to put together a Holiday wish list for your family & friends.
You must list the items with prices and the place they can be purchased. You will
be asked to list gifts that range in price. This will allow your family and friends
to find an item that falls within their budget.
Goal: To practice using formatting features in excel. Practice with formulas
and charts.
- Search the web to create your perfect holiday wish list. You will need at least
10 Gifts in the following price ranges. (10 gifts total, with a Min of 1 in each
price range)
- $1-$5
- $5-$10
- $10-$50
- $50 to $80
- $80 & up (THE BIG GIFT!!!!!)
- Open a New Workbook. Rename the Sheet tab Holiday.
- Type the following:
(Format as a heading using cell styles)
- In A2 key – GIFT
- B2 key – PRICE
- C2 key – PLACE
- D2 key – WHO WILL PURCHASE.
- Now key the NAME of each gift in column A & Hyperlink the web address
where they can find the gift. (Hint: Keep the names short)
- Now key the listed price in column B. Format as currency with 2
decimal places.
- Now key the name of the store they can be purchased. Hyperlink the
store’s web address to the name. (Ex. www.bestbuy.com)
- In the last row Key TOTAL. (A13)
- Use the sum function to add up your gift total. (in B13)
- Use the IF function in column D to determine who will purchase
the gift for you. If the gift is under $15 a friend will purchase it for you,
otherwise your family will purchase it for you. (Ex: B3>15)
- Create a bar chart that compares the prices of the gifts & format your chart
using bright colors.
- Add a chart title
- Add a hoirizontal axis title & a vertical axis title
- Add data labels
- Include a legend
- Format your worksheet displaying holiday cheer! Use bright colors
and borders. Insert an appropriate background. (Page Layout Tab > Background.)
Apply border lines
- Use the filter feature to sort the list from A to Z by actual
price. (Data Tab > Filter)
- Add conditional formatting from B3:b12. Make any item that is >$80 stand
out. (Home tab>Conditional Formatting> Highlight Cell rules)
- Center on the page vertically & horizontally. (page layout tab>margins>custom
margins>margins tab>under center on page, check of horizontally & vertically)