Microsoft Office/Create a spreadsheet with multiple worksheets

Objectives

• Create a Template
• Create and define a new style
• Research to find synonyms
• Create multiple worksheets
• Create Cylinder Charts
• Create headers and footers
• Link data from multiple sheets to a summary page

Vocabulary

• Consolidate - Summarizing data from multiple worksheets on to one worksheet.
• Dummy Data - Sample data used in place of actual data to verify formulas. Usually you check simple numbers and numbers where you expect a change to occur.
• Date Series - Excel will list dates in order for you - Jan, Feb, Mar, ...
• Auto Fill Series - Excel will create a set of the same number over and over again - 1, 1, 1, ...
• Linear Series - Excel will create a set of numbers in order - 1, 2, 3, ... or 2, 4, 6, ...
• Growth series - Excel can create a series that is multiplying by a number from one cell to the next - 2, 4, 8, 16, ....
• Round Function - you can set the number of decimal places by using the round function. =ROUND(number, number of digits) Example: =round(2.34567, 3) would give the value of 2.346
• Format Codes - a series of format symbols that defines how a cell entry assigned a format will appear. Example: [blue]#,##0.00; will make the number appear blue and will round to the closest hundred.
  • '# - digit placeholder Example: ###.## would give the format of any number of digits before the decimal, and only two digits after.
  • 0 - functions like a #, except places a 0 in the ones place if the number is less than 1.
  • . - Make it certain that excel will place a decimal point. Example: #0.00 forces two decimal places.
  • % - Displays numbers as a percent
  • , - Displays a comma to separate the numbers into thousands, millions, ...
  • ( ) - Places parenthesis around negative numbers
  • $ - places a dollar sign in front of numbers making them currency
  • '*' - Makes the dollar sign a fixed dollar sign "$*"
  • [color] - changes the color of the text or numbers
  • "text" - places text in with a number Example "Week" 1 would count as just 1 so you could use it in a formula
  • _ - places a space after the character that follows the underscore.
• Drilling an Entry - Enter a number once in the first worksheet and have it fill into all selected worksheets into the same cell.
• 3-D Range - A range of cells that spans multiple worksheets
• Sheet reference - The name of the sheet you are working with
• Annotate - Add text to define a part of the worksheet or chart, usually with a text box and arrow.
• Comments - Text attached to a cell that describes the function of the cell, they are not printed and should include:
  • worksheet title
  • author's name
  • Date created
  • Date last modified
  • Template used
  • Description of the purpose of the worksheet or cell or formula

Lesson

Create a Template

Titles

• Titles
  • A1 = SSWF Nursery
  • A2 = Nursery Profit Potential
  • Center both of these from A to G (remember you have to do them one at a time)
  • Format to look nice with
    • font style
    • color
    • size
    • shade color
• Column headings
  • A3 = Tree
  • B3 = On Hand
  • C3 = Average Unit Cost
  • D3 = Total Cost
  • E3 = Average Unit Price
  • F3 = total Value
  • G3 = Profit Potential
• Row Headings
  • A4 = Colorado Blue Spruce
  • A5 = Arkansas Amsonia
  • A6 = Angle Trumpet
  • A7 = Hybrid Poplar
  • A8 = Dawn Redwood
  • A9 = Weeping Willow
  • A10 = Lombardy Poplar
  • A11 = Pink Dogwood
  • A12 = flowering Crab
  • A13 = Red Maple
  • A14 = White Birch
  • A15 = Dwarf Apple - Red Delicious
  • A16 = Dwarf Peach

Using Dummy Data

Dummy data is used when you want to check your formulas to see if they are computing correctly. Usually you pick easy numbers to work with to do the math in your head.

• B5 = 1
• B6 = 2
• Select B5:B6
• Fill down to B16
• C5 = 2
• C6 = 4
• Select C5:C6
• Fill down to C16

Enter Formulas

• D5 = B5*C5
• E5 = ROUND(C5/(1-.57),2)
• F5 = B5*E5
• G5 = F5-D5
• Select D5:G5
• Fill down to row 16

Total the columns - AutoSum

Use AutoSum at the bottom of columns: B, D, F and G

Formatting Fonts

• Borders
  • Place a border between rows 4 and 5
  • Make the totals in row 17 formatted like totals from the styles
• Date
  • G3 = now()
  • format the date in G3 to be Day-Month-2010
• Currency
  • select C5:G5 press CTRL D17:G17
  • Right click
  • Choose FORMAT CELLS
  • Choose Currency
  • Make sure it is set to 2 decimal places
  • Choose $ for the symbol (English - US)
  • Choose the fourth format down
  • Click OK

Custom Format Codes

• Select cells C6:G15
• Right click
• Click on Format Cells
• Make sure you are on the NUMBER tab
• Click on Custom under category
• Scroll down and look at all the custom choices
• Click on #,##0.00_);[Red](#,##0.00)
• Change the color (Blue, Green, Yellow) in the TYPE field
• Click OK
• Type in a negative number and see what happenes

Using Research Functions

• Double click in cell A1
• Select the word Nursery
• Go to the REVIEW tab
• Go to the Proofing group
• Click on Thesaurus
• Change the name of the company by choosing a different synonym.
• Type in the new name.

Using Templates

Saving a new Template

• Go to the office button
• Click on SAVE AS
• Change the SAVE AS TYPE to Excel Template
• Find the local drive to save to
• Name the template NURSERY

Applying a Template to a new workbook

• Close out of the workbook
• Open Excel
• Click on new
• Click on My Templates
• You should find the template to open
• Do a Save As and name it SSWF Nursery

Adding new worksheets

At the bottom of the window next to the SHEET 1, SHEET 2, SHEET 3 click on the sheet with the starburst to add a new sheet.

Copying contents from one worksheet to another

• Click in the square in the upper left at the top of the row headers, and to the left of the column headers
  • This should select everything
• Press CTRL+C to copy
• Click on Sheet 4 tab
• Hold down SHIFT and click on SHEET 3 tab
  • this should choose all three tabs
• Press CTRL+V
  • Scroll through the worksheets and notice that they are all the same.

Drilling an entry

This is when you enter data into one sheet and it is automatically entered into all of the sheets in the same cell.

• Click on sheet 1
• click on cell C4
• Hold down Shift
• Click on sheet 3
• Type .99
• press down arrow

Enter in all the date for the Average Unit Costs using this drilling method.

• C5 = 2.99
• C6 = 9.99
• C7 = 3.99
• C8 = 2.97
• C9 = 1.99
• C10 = .99
• C11 = 7.99
• C12 = 19.99
• C13 = 1.50
• C14 = 2.34
• C15 = 9.99
• C16 = 9.99

Modifying the worksheets

• Go to sheet 4
  • Right click the sheet number
  • Click Rename
  • Change the name to: Pittsburgh
  • Go to cell A2
  • Change the title to: Pittsburgh Nursery Profit Potential
  • Enter the date for the Unit on Hand.
• C4 = 546
• C5 = 341
• C6 = 189
• C7 = 624
• C8 = 758
• C9 = 398
• C10 = 826
• C11 = 67
• C12 = 21
• C13 = 418
• C14 = 279
• C15 = 82
• C16 = 57
• Go to sheet 1
  • Right click the sheet number
  • Click Rename
  • Change the name to: Missoula
  • Go to cell A2
  • Change the title to: Missoula Nursery Profit Potential
  • Enter the date for the Unit on Hand.
• C4 = 435
• C5 = 431
• C6 = 209
• C7 = 576
• C8 = 890
• C9 = 417
• C10 = 782
• C11 = 79
• C12 = 35
• C13 = 399
• C14 = 302
• C15 = 91
• C16 = 65
• Go to sheet 2
  • Right click the sheet number
  • Click Rename
  • Change the name to: Grand Junction
  • Go to cell A2
  • Change the title to: Grand Junction Nursery Profit Potential
  • Enter the date for the Unit on Hand.
• C4 = 497
• C5 = 402
• C6 = 170
• C7 = 563
• C8 = 798
• C9 = 416
• C10 = 794
• C11 = 82
• C12 = 41
• C13 = 427
• C14 = 268
• C15 = 107
• C16 = 67

Create a summary sheet

titles

• Go to sheet 1
• Change the name of the tab to COMPANY
  • Right click on the tab
  • Click on Rename
  • Type in the correct name
• Change the color of the tab
  • Right click
  • Go to Tab Color
  • Pick a color
• Change the sub-Title to Nursery Potential Profit

Create 3-D References

Create a 3-D reference for Units On Hand and

• Go to the summary tab
• Go to cell B4
• Click the AutoSum button
• Click on the tab for Pittsburgh
• Hold down SHIFT
• Click on the tab for Grand Junction
• Click on cell B4
• Press Enter

Create a 3-D Cylinder Chart

Create a chart

Make a 3-D cylinder chart of the trees potential profit

• Choose the items to be charted
  • Go to the Summary Sheet
  • Select A3:A16
  • Hold Down CTRL
  • Select G3:G16
• Select the Chart Type
  • Go to the Insert Tab
  • Go to the Chart Group
  • Click on Column
  • Click on the first one to the left under Cylinder
• Move the Chart to its own sheet
  • Click on the Chart
  • Go to the Chart Tools/Design tab
  • Go to the Location Group
  • Click on Move Chart
  • Click on the radio button for own sheet
  • Name the sheet Potential Profit
  • Click on OK

Create WordArt

Add a title to the Chart with Word Art for a special look

• Go to the Insert Tab
• Go to the Text Group
• Click on the WordArt button
• Select the style of Word Art you want
• Type Potential Profit
• Click off the WordArt box
• Place the title at the top of the chart but not over top of any of the columns.
• Go to the Drawing Tools/Format tab
• Play with the WordArt Styles

Format the Chart

Delete the legend

Change the Y-Axis Units

We would like to have less horizontal units lines, so we need to format them.

• Click on the Y-Axis so that each horizontal line has end marks (circles)
• Right click on the Y-Axis
• Click on Format Axis
• Change the Major unit from Auto to Fixed
• Type in 2000 in the units
• Click Close

Change the background color

• Change the side wall
  • Click on the side wall (Y-Axis)
  • Right Click on the front edge of the side wall
  • Click on Format Side Wall
  • Change the colors
• Change the back wall
  • Click on the back wall
  • Right click on the back wall
  • Click on Format Back Wall
  • Change the colors
• Change the Floor
  • Click on the floor
  • Right click on the floor
  • Click on Format Floor
  • Change the colors

Make sure you can still see the columns and the WordArt when you are done.

Create an Annotation

We want to highlight what tree has the most potential.

• Create a Text Box
  • Go to Insert tab
  • Go to the Text Group
  • Click on the Text Box Button
  • Click and drag to draw a text box in the upper left corner of the chart
• Add Text to the text box
  • Type: Greatest Potential Profit
  • Click out somewhere
• Format the Text box
  • Resize the text box to fit the words with the resize handles
  • Right Click on the Text box
  • Click on Format Shape
  • Change the fill color
  • Change the line width and color
  • Change the shadow and angle of shadow
  • Center the text from the HOME tab
• Add an arrow
  • Go to the INSERT tab
  • Go to the Illustrations Group
  • Click on Shapes drop down
  • Click on an arrow
  • Click and drag to draw an arrow from the text box to the tallest column
• Format the Arrow
  • Right click on the arrow
  • Click on Format Shape
  • Change the fill color
  • Change the line color
  • Change the lien style
  • Change the shadow

Insert Comments

Sometimes it is good to leave comments about why a certain cell contains that value. Other times it is good just to leave a comment to the purpose of the whole spreadsheet, but you do not want it to print. In these cases it is good to use comments.

• Add a comment for the markup on price
  • Go to the Summary Tab
  • Go to cell E4
  • Right Click on the cell
  • Click on Insert Comment
  • Type: This is the same as a 232% markup on the price.
  • Click off somewhere

Formatting the workbook

Headers and Footers

To add a header to each page of a document you need to go to page set up. Headers and Footers are different in Excel than in Word.

• Go to the Page Layout Tab
• Go to the Page Set up Group
• Click on the Page Set up More button
• From the page setup dialog box click on the Header/Footer tab
• Click on Customer Header
• Left Section
  • Type in Your Name
  • Press Enter
  • Type Potential Profit
• Center Section
  • Click on the Insert Sheet Name button
• Right Section
  • Type Page and a space
  • Click on The page number button
  • Type OF
  • Click on the number of pages button
• Click OK

Margins

From the page layout tab you can set the margins for your page. If you click on the Custom margins button you can set them as you need.

• Top to 1"
• Bottom to 1"
• Right to 0.5"
• Left to 0.5"
• Click on the center horizontally and vertically check boxes

Print Layout

From the PAGE tab you can set up how the page will layout.

• Click on the PAGE tab
• Select the Landscape radio button
• Click on the fit to 1 page wide by 1 page tall

Sheet layout

• Click on the SHEET tab
• Click on the Grid lines check box
• If you want to print the comments out, you can choose AT END OF SHEET from the comments drop down.

Print Area

If you only want to print a part of a page you can set that up.

• click and drag over the section you want to print
• Go to the Page Layout Tab
• Go to the Page Setup Group
• Click on Print Area
• Click on Set Print Area

Print Multiple Sheets

If you want to print multiple sheets at one time that can also be set up.

• Click on the first sheet tab you want to print.
• If the tabs are next to each other then press SHIFT and click on the next one.

Print Non-Adjacent Worksheets

• If the tabs are NOT next to each other then press CTRL and click on the next sheet you want to print.

Test this out with the print preview.