Adv Excel - W1Lab

• Agenda
• Task 1 - Introductions

  Task 2 - Course Outline

  Task 3 - Blackboard 

  Task 4 - MyITLab 

  Task 5 - Introduction to Excel 2013

  Task 6 - Agenda

  Task 7 - Quiz
• 

  Lab 1 - Introduction to Spreadsheets

  
  Part 1

    

  • 1.    Open Microsoft Excel and save the new workbook.
  • 2.      Select Cell A1, type “OK Office Systems Pricing Information” and press enter.
  • 3.      Select Cell A3, type “Item” and press enter.
  • 4.      Type “Computer System” and press enter.
  • 5.      Type “Desk Jet Printer” and press enter.
  • 6.      Type “Filing Cabinet” and press enter.
  • 7.      Type “Task Chair” and press enter.
  • 8.      Type “Solid Wood Computer Table” and press enter.
  • 9.      Type “Computer Monitor” and press enter.
  • 10.   Select B3, type “Cost” and press Tab.
  • 11.   Type “Markup Rate” and press Tab.
  • 12.   Type “Retail Price” and press Tab.
  • 13.   Type “Percent Off” and press Tab.
  • 14.   Type “Sale Price” and press Tab.
  • 15.   Type “Profit Margin” and press Tab.
  • 16.   Save (Ctrl + S)

  
  

     

  Part 2

    

  
  

  • 1.    Type the values in cells B4 through B9 as shown in the figure above
  • 2.      Type the values in cells C4 through C9 as shown in the figure above
  • 3.      Type the values in cells E4 through E9 as shown in the figure above
• 

  Lab 2 - Mathematics and Formulas

  
  Lab 2 – Mathematics and Formulas

    

  • 1.       To calculate retail price, add the formula “=B4*(1+C4)” to cell D4 as shown in the figure above.
  • 2.       To calculate Sales price, add the formula “=D4-D4*E4” to cell F4 as shown in the figure above.
  • 3.       To calculate Profit Margin, add the formula “=(F4-B4)/F4” to cell G4 as shown in the figure above.
  • 4.       Use auto fill to copy the formulas created for Retail Price, Sales Price and Profit Margin.
  • 5.       Change B5 to 350 and E9 to 0.25 while observing the changes automatically made to corresponding retail prices, Sale Price and Profit Margin.
  • 6.       Display all the formulas in your work sheet using ctrl + `.

    

  
  

  
  
• 

  Lab 3 – Workbook and worksheet management

  
  Use the workbook used in Lab 2 for following tasks.

  Part 1 – Managing worksheet

  • 1.       Rename Sheet 1 to “W1 L3”
  • 2.       Change the tab color of W1 L3 sheet to Red.

  Part 2 – Deleting a row

  • 1.       Delete the row containing data related to Task Chair

  
  

  Part 3 – inserting a new column

  • 1.       Insert a New Column for “Profit Amount” in-between “Sales Price” and “Profit Margin”
  • 2.       Use a formula to calculate first item’s Profit amount (Profit amount = Sales price – Cost)
  • 3.       Fill profit Amount for rest of the items.

  
  

  Part 4 – Inserting a new row

  • 1.       Right click row 4 heading and select “Insert”
  • 2.       Type “Electronics” in cell A4
  • 3.       Right click row 6 heading and select “Insert”
  • 4.       Type “Furniture” in cell A7
  • 5.       Insert a row between Computer Systems and Color Laser Printer

  Part 5 – Moving a row

  • 1.       Cut and paste the range of cells that hold data about “computer monitor” to the blank row in-between Computer system and Color Laser Printer.

  Part 6 – Adjust column width and row height

  • 1.       Adjust width of the Item column so that even the longest item name fits in the column.
  • 2.       Readjust the width of the above column to 25.
  • 3.       Adjust the row height of first row to 25.

  Part 7 – Hide and unhide columns

  • 1.       Hide column B & C
  • 2.       Unhide column B & C

  
  

  
  
• 

  Lab 4 - Formatting

  
  Lab 4 – Formatting

  Use workbook that was updated in Lab 3 to carry out all the tasks given below.

  Part 1 – Merge and center Title

  • 1.       Merge and center the title in relation to the table below
  • 2.       Bold and increase font size of the title to 14pt.

  
  

  Part 2 – Warp and Align Text

  • 1.       Wrap text in row 3
  • 2.       Bold the row 3
  • 3.       Center row 3 text horizontally
  • 4.       Center row 1 text vertically

  Part 3 – Number formats and decimal places

  • 1.       Apply Accounting Number Format to numbers under Cost, Retail Price, Sale Price and Profit Amount.
  • 2.       Apply Percent Style to numbers under Markup Rate, Percent Off and Profit Margin.
  • 3.       Increase decimal places of Markup Rate and Profit Margin to 2.
  • 4.       Decrease decimal places of Percent Off to 0.

  Part 4 – Apply Borders and Fill Color

  • 1.       Fill the range A3:H3 with Grey color
  • 2.       Add a thick border around data value under column Percent Off and Sale Price.

  Part 5 – Indent cell contents

  • 1.       Increase the width of column A to 26
  • 2.       Increase indent for the cells contents under both categories (Electronics and Furniture)
• 

  Lab 5 - Print Preview and Print

  
  Lab 5 – Page setup and printing

  Use the updated workbook from previous lab

  Part 1 – Page orientation

  • 1.       Set the page orientation of the worksheet to landscape

  Part 2 – Set Margin

  • 1.       Change the margin size to 1”

  Part 3 – Header

  • 1.       Add header
  • 2.       Include your name, Sheet name and current date in the header

  Part 4 – Print preview and print

  
  

  • 1.       Preview your worksheet and print worksheet