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. Project Rubric