- Create new workbook (Your Name ) and insert the following data from A1 Cell
Data Table
Last First Address Hire Date Salary
402 Elm St.
Dawes Gregory 3/13/2013 $2117.29
Merrill, MI 48637
1025 Drake Ave.
Garcia Susan 3/14/2014 $2415.79
Exeter , NH 03833
5 North Ln.
Torbet Dr. Lilla 3/17/2013 $3212.99
Oswego, NY 13126
24 Mountain Dr.
Rhoden Tony 3/24/2015 $2615.79
Auburn, ME 04210
- Rename the worksheet to (main)
- Insert new column (Number) before (Salary) column as the following
Number
10
20
30
40
- Insert new row before (Rhoden) row as the following
15 South Ln.
Scoot Mark 12/1/2011 $2525.99 30
Greater, LS 14572
- Change the font size to 15 for the entire table
- Change the font type to Arial for the entire table
- Change the font of the first row to olive green accent 3 lighter 40%
- Hide the following row
1025 Drake Ave.
Garcia Susan 3/14/2013 $15.79 20
Exeter , NH 03833
- Format the borders of the table as appears above
- Format the fill color of the first and second row to be green
- Create new range (abc) from cell B1 to cell C6
- Freeze the first two columns
- Insert comment for A2 as the following massage (employee last name)
- Insert new worksheet and name it (copy data)
- Copy the entire data from (main) worksheet and paste it in (copy data) worksheet
- Go to (copy data) worksheet : delete the first row
- Unhide the following row
1025 Drake Ave.
Garcia Susan 3/14/2013 $15.79 20
Exeter , NH 03833
- Sort the table base on First Name column
- Filter the table to display the last name start with G or R
- Hide worksheet (copy data)
- Go back to (main) worksheet , and do the following :
Insert header to the worksheet as the following:
- Left section : your name
- Center section : file name
- Right section : time // Date
Insert footer as the following :
- Left section : Page number / number of pages
- Center section : insert any picture (manage the size of the picture)
- Right section : sheet name
Change the page orientation to landscape
- Create the worksheet (sales) and inter the following data from A1 cell
Maximum Minimum Total
Unit List Sales
Product Quantity Discount Target commission Sales Sales Sales
Price Price Price
price price price
Beauty
150 24 ? ? ? ? ? ? ? ?
Products
Handbags 120 15 ? ? ? ? ?
Perfumes 200 6 ? ? ? ? ?
Accessories 80 65 ? ? ? ? ?
Summer
110 35 ? ? ? ? ?
Clothes
Shoos 50 24 ? ? ? ? ?
Commission
?
rate
Status_1 ?
- Enter a formula to find List Price
List Price = Unit Price X Quantity
- Enter a formula to find Discount
Discount = List Price / 25
- Enter a formula to find Sales price
Sales Price = List Price – Discount
- In Target column : Use a function to display (excellent sales) if the sales price more than
4000 and display (average sales) if the sales price more than 3000 otherwise display
(bad sales)
- In commission column : use absolute reference ($) to find out the commission as the
following : commission rate X sales price
- Use appropriate function to find out the maximum sales price
- Use appropriate function to find out the minimum sales price
- Use appropriate function to find out the total sales price
- Status1 cell : use appropriate logical function to display “true” if the quantity of Beauty
Products more than 50 and the quantity of Handbag more than 60 and the quantity
Perfumes more than 50 otherwise display “false”
- Create a column chart from product column to Sales Price column
- Create Pie chart for the Handbags, and Show Legend at bottom
- Insert a now column before the product column as the following
City
Jeddah
Jeddah
Makkah
Makkah
Jeddah
Abha
- Insert a pivot table (in new worksheet) to display the City, product , unit price and sales
price for Jeddah and Makkah
- Go back to worksheet (sales)
- Use data validation tools to restrict the quantity column to accept number between 1
and 100
- Use data validation tools to Restrict the city column to display the following list (Jeddah ,
Makkah , abha)
- Use conditional formatting tools for sales price column as the following:
more than 4000 : green color
between 2000 and 4000 : yellow color
less than 2000 : red color
- Go back to worksheet (main) ,
- Insert new column (days) after hire date column then use data and time functions to
find out how many days between the hire date and today.
- Create new worksheet (link data)
- Go to link data worksheet : in A1 cell :write the following formula :
total sales price from (sales) worksheet X salary of the first employee from the (main)
worksheet
- Go back to (Sales) worksheet , copy the product , unit price , quantity , list price ,
discount and sales price columns and paste it in new worksheet (searching)
- Go to searching worksheet : in A12 cell : use vlookup function to display the sales price
of (Perfumes)
- Create a macro in the current file (This workbook) and name it Myformat that will format
column A and column B as follows:
Fill color: Yellow, Font color Blue, Font size 20, Bold, Italic, Center
