Practical Exercise – Range names, Advanced Filters, Linking to other sheets

Please download this file :

And complete the following tasks as demonstrated by the videos at the end of this post:

Task Task Instructions
1Apply a range name to the table used in the vlookup. Name this range “PriceList”
2Update the Vlookup function to use the “PriceList” Name range
3Use a range name for the percentage and update the formula which calculates the vat
4Insert a new row for “Cinnamon rolls” which sold 2000 items
5Insert  a new column “Hot Seller?”. This column will contain a value of “Yes” if more than 1500 Items were sold for the month and “NO” otherwise. Implement this using an “if” function.
6Apply a custom sort, First sort by “Hot Seller” Descending then by “revenue per item” descending
7Apply an advanced filter using to show items that weren’t hot sellers but sold more than 1000 items
8Create a pie chart showing  the net revenue per item
9Create a barchart showing both the revenue per item and the vat per item
10Move the tables and constants to a new sheet, “Constants”. i.e the vat value and price list. Observe how the formula for the vlookup has changed.

Add a new column, “Discounted price” to the sales table .  Add a new constant called Discount to the Constants sheet with a value of 6%.  Use this value to calculate the discounted price per item in the sheet.

See the video demonstrations below for the tasks outlined above.

The above video was continued in another class session:

© 2020  Vedesh Kungebeharry. All rights reserved. 

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s