Outline- Microsoft Excel Class #2 -


Formatting Worksheets, Formulas, Absolute/Relative Cell References

PART 1: This is based on the data that goes with the Go Office 2007 book for Chapter 10 (Excel) - open e10A_Ticket_Sales. There are 3 sheets, and the first looks like this:
    Area #1            
    Sales            
                 
    Date Child Adult        
    8/3            
    8/4            
    8/5            
    8/6            
    8/7            
    8/8            
    8/9            
                 
                 
                 
    Child Ticket Price 2.5        
    Adult Ticket Price 5        

  1. Right click on the sheet name - choose "Select all Sheets" to make changes on all sheets.
  2. Format the title in Cambria font size 14, italic.
  3. Remove "Sales" from row 2, and remove the color.
  4. Try different date formats, and choose one.
  5. Add additional titles to row 4: Child Ticket Total; Adult Ticket Total; Total Ticket Sales
  6. UNGROUP THE WORKSHEETS by clicking on a worksheet tab.
  7. Click on each sheet tab, and enter the data as directed by the instructor
  8. Do computations in D5, E5 and F5
  9. For columns D and E use a computation with an ABSOLUTE CELL REFERENCE for the values in D15 and D16 - so they'll be $D$15 and $D$16
  10. Use the Fill Handle to copy formulas down to the other rows
  11. Type in Titles on each Worksheet, and Use Merge and Center to center headers.
  12. Insert a new Sheet, for summary information. Have row titles: Golden Grove Mall, Post Office, City Hall, Totals. Have titles in row 1 and 2 for "Summer Fair and Arts Festival", "Pre-Sales of Tickets August 3-9"
  13. Have titles in B3, C3, and D3 as follows: Total Sales Child; Total Sales Adult; Totals. Use this sheet to SUMMARIZE the other three sheets.
  14. Use Page Layout ( Page Setup Dialog Box) to display gridlines, and to change landscape orientation.
  15. Save the document in "My Documents", as "Concession Stand" followed by your name
  • Note: Relative Cell References represent the usual way of naming cells (Example, A2). Absolute Cell References have a $ before the Column letter, the Row Number, or both. (Example, $A$2). Relative Cell References will change when they are copied to other cells.
  • Note: When entering formulas - if the sheet is in "EDIT MODE", you can enter a cell name by just clicking on the cell (called POINT MODE).