Making a Shopping List in Microsoft Excel
This week we will be making a shopping list in Microsoft Excel. However, this will not just be a list of items that we want to buy when we go to the shop, it will also calculate the cost of the items that we want to buy. To do this, we can use formulas within excel so that everything is calculated automatically.
To begin, we need to first set up the page.
- Go to Page Layout -> Orientation -> Landscape
- Go to Page Layout -> Margins -> Custom Margins. Change top, bottom, left and right to 1cm. Change header and footer to 0cm.
- Change the width of column A to 125 pixels and the width of column B to 85 pixels.
- Select cells B5 to B28, right click, go to format cells, click on "Currency" and then press "OK".
Now write the following items in your Microsoft Excel Document. Write the first item (Apples) in cell A5 and its price in B5, the next item in A6 and its price in B6 etc. You do not need to write the information after the dash.
Apples $3.78- Red Delicious Prepacked 1kg
Baked Beans $1.28- Coles Baked Beans 425g
Bread $1.50- Coles Smart Buy Wholemeal Bread 700g
Bread Crumbs $1.34- Coles Smart Buy Breadcumbs 500g
Carrots $1.65 - Carrots Fresh Prepacked 1kg
Cheese $4.16 - Coles Cheese Shredded Tasty 250g
Corn Chips $3.19 - Doritos Corn Chips Cheese Supreme 175g
Eggs $4.49 - Coles Eggs Free Range 12 pack 600g
Flour $0.95 - Coles Flour Plain White 1kg
Jam $3.20 - Coles Jam Strawberry 500g
Lettuce $2.98 - Lettuce Iceberg Bagged 1 each
Margerine $2.68 - Coles Spread Canola 500g
Milk $2.00 - Coles Milk Full Cream Bottle 2L
Oil $3.52 - Coles Canola Oil 750mL
Oranges $4.95 - Oranges Navel Prepacked 3kg
Peanut Butter $3.21 - Coles Peanut Butter Crunchy 375g
Pears $2.78 - Pears Packham Prepacked 1kg
Frozen Peas $2.90 - Coles Peas Australian Frozen 1kg
Potatoes $5.99 - Organic Potatoes Brushed Prepacked 2kg
Rolled Oats $1.27 - Coles Smart Buy Oats Rolled 900g
Sugar $2.13 - Coles Smart Buy White Sugar 2kg
Tomato Soup $2.02 - Heinz Condensed Big Red Tomato Soup 420g
Tomatoes $2.98 - Tomatoes Cherry Prepacked 250g
Weet-Bix $2.88 - Sanitarium Weet Bix 375g
- Merge cells A1 to N1 and give your Excel document a title, such as "Weekly Shopping List <Your Name>". It is very important that you include your name.
- Merge cells D3 to E3 and write "Week 1". Merge cells G3 to H3 and write "Week 2". Merge cells J3 to K3 and write "Week 3". Merge cells M3 to N3 and write "Week 4"
- In cell A4, write "Item"
- In cells B4, E4, H4, K4 and N4 write "Price"
- In cells D4, G4, J4 and M4 write "Quantity"
- In cell A30 write "Weekly Total"
- In cell A31 write "Monthly Total"
- Select cells E5 to E28, right click, go to format cells, click on "Currency" and then press "OK".
- Select cells H5 to H28, right click, go to format cells, click on "Currency" and then press "OK".
- Select cells K5 to K28, right click, go to format cells, click on "Currency" and then press "OK".
- Select cells N5 to N28, right click, go to format cells, click on "Currency" and then press "OK".
Now it is time to start using our knowledge of Excel forumulas. In column E, we want to determine how much it will cost us if we buy the quantity we stated in column D. To do this, we will multiply the price (found in column B) by the quantity that we bought (column D). The formula for Week 1 is shown below, but you will need to determine the formula for the later weeks.
- In cell E5, multiply cells B5 and D5 together. (It should look like "=B5*D5")
- Copy this formula down until E28. To do this, select cells E5 to E28 and press Control-D.
- In cell E30, sum together from E5 to E28. (It should look like "=sum(E5:E28)")
Complete the formulas for weeks 2, 3 and 4. If you are not exactly sure what to do, give it a try. The worst that can happen is that excel will tell you that you have entered the formula incorrectly.
- In cell B31, write a formula that will calculate the cost of the monthly shopping bill. Hint: It is easier to add together the weekly totals.
- Go through your shopping list and place in realistic quantities for how much of each item you want to buy each week. Realistically, you would not be buying any more than three of any given item.
You have now completed your weekly shopping list. You may now experiment with the column widths, row heights, font size and colors to make the shopping list look really good. You do not need to add as much color as what we used for the classroom timetable. There many also be a few cells that need to have their 'type' changed to currency.
IMPORTANT: MAKE SURE THAT IT FITS ONTO ONE A4 PAGE. If you are not sure if it is all fitting onto one page, go to print preview.