Change the spreadsheet to print in Landscape mode - PBDD
Module Three: Formatting and PrintingWelcome to the third lesson in the PRC’s Excel Spreadsheets Course 1. This lesson focuses on the appearance of the spreadsheet, both on the display screen and on the printed page. But before that material, the lesson covers what might be the most important topic in Course 1 – how to get and use Help. After the course is over, after your instructor has changed his phone number, after you have forgotten how to make Excel dance to your tune, Help is still available if you remember how to use it. That’s why it is such an important topic. After completing this lesson, you will be able to format both the spreadsheet as a whole and individual cells within the spreadsheet, and you will be able to take advantage of formatting schemes contained in a library of built in styles. Topics TOC \t "Section Hdr,1" Relative Cell Reference3. PAGEREF _Toc499825060 \h 2Get help3. PAGEREF _Toc499825061 \h 3Change the look of a spreadsheet3. PAGEREF _Toc499825062 \h 4Formatting Fonts3. PAGEREF _Toc499825063 \h 6Print a Spreadsheet3. PAGEREF _Toc499825065 \h 8Set page breaks3. PAGEREF _Toc499825066 \h 9Setup the Printed Page3. PAGEREF _Toc499825067 \h 10Exercises TOC \t "Exercise Hdr,1" Exercise A - Use Microsoft Excel help3. PAGEREF _Toc499825108 \h 13Exercise B - Format the Budget Spreadsheet3. PAGEREF _Toc499825109 \h 14Exercise C - Enhance the Budget Spreadsheet Format3. PAGEREF _Toc499825110 \h 17Exercise D - Enhance the Auto Mileage Spreadsheet3. PAGEREF _Toc499825111 \h 19Summary3. PAGEREF _Toc499825112 \h 22ObjectivesGet and use Help Format the straightforward and uncomplicated aspects of a spreadsheetSet up spreadsheet features for the printed pageRelative Cell ReferenceThe concept of relative cell reference is not easy to grasp. This module begins with a short review of the topic as it was introduced in the previous module. The short phrase, “by default,” means that a software program will behave in a certain way unless the user directs otherwise. By default, the formulas entered in Excel use relative reference. That is, even if a formula contains a cell reference that appears to point to a specific cell, Excel understands the point in terms of where that cell sits in relation to the cell containing the reference. This is best understood with a simple example. Starting with a blank spreadsheet, enter the information shown below. Figure 3.1 Cell References in Formulas Remember that although you enter the formula =A1 into cell C2, the value of the formula is displayed in C2. The formula, itself, is shown on the Formula bar when C2 is selected.This is about as simple a formula as is possible. It says, “Display the contents of cell A1 in cell C2.” Or that’s what it seems to say. Excel understands the formula to say, “Display the contents of the cell two columns to the left and one row up from the cell where the formula resides.” In other words, Excel is using the relative position of the two cells, C2 and A1, to determine what should be displayed in C2. You can verify this with a small experiment.Select cell C2. Using the Copy and Paste commands on the Home tab, Clipboard group , or Ctrl+C followed by Ctrl+V, copy the formula from C2 and paste it into D3. Cell D3 now displays 5, and the Formula bar now shows =B2. Cell B2 is two columns to the left and one row up from cell D3, where the formula resides. The relative position of cells C2 and A1 has been preserved in the relation between cells D3 and B2. All methods of copying, including Autofill and Ctrl + Drag and Drop, will preserve relative reference.Delete the formula in cell D3. Select cell C2. Using the Cut and Paste commands on the Home tab, Clipboard group , or Ctrl+X followed by Ctrl+V, move the formula from C2 and paste it into D3. Cell D3 still displays 1, and the Formula bar still shows =A1. The relative position of cells C2 and A1 has not been preserved in the relation between cells D3 and B2. All methods of moving, including Drag and Drop, will not preserve relative reference.By default, copying preserves the relative reference by changing the formula; moving preserves the formula by changing the relative reference.Get HelpAfter you finish this course, you must face the brutal reality of being without an instructor the next time you tackle a problem with Microsoft Excel. So the most important thing for you learn in this course is how to get Help with a capital H. Excel has extensive Help, both on line and off. The next several paragraphs discuss the Help that is available off line; that is, unconnected to Microsoft Support on the Web.Help is invoked by clicking on the Help symbol (circle with a ? inside) located at the far right on theTab bar in the Ribbon or by hitting the F1 key. The Help button is a bit hard to find but it is located in the upper right corner like in all 2007 Microsoft Applications and it looks like a question mark. Figure 3.2 Help Button Type your topic or question in the search box to find information on a specific topic in Help. See Figure 3.2 Figure 3.2 Help Topics. For example you can type “How do I round off numbers?” or simply “Round Numbers” and Excel will display all related topics. The more specific you make your query, the better chance you have of getting the information you are looking for.3.3 Example of information returned from SearchChange the look of a spreadsheetAfter you have created your spreadsheet, the next step is to change the look both on the screen and as a printed document. There are a number of obvious changes you can make, like type style, type size, color, etc. However one useful tool is viewing the formulas in your spreadsheet. Viewing the formulas can help you find errors and provide a way of documenting your hard work. Figure 3.4 is an example of the Mileage Chart spreadsheet with the formulas exposed.Figure 3.4 Sample spreadsheet with formulas displayedSelecting the Show Formulas button on the Formulas tab, Formula auditing group changes the cells from values to formulas. Pressing the Show Formulas button will toggle between values and corresponding formulas. Figure 3.5 The Formulas TabThere are many creative changes you can make to a spreadsheet to enhance your final product. For example,just like the button to show formulas, there is also a checkbox to the left of Gridlines in the View tab, Show/Hide group. Selecting Gridlines will remove or restore the horizontal and vertical gridlines from the spreadsheet.Note: The Gridlines command does not have any effect on printing. Printing gridlines is controlled in the Page Setup area. How to print gridlines will be discussed later in this lesson.While you’re entering data, gridlines can help the eye focus on the correct row. Once your data has been entered, removing the gridlines makes your spreadsheet more appealing.Formatting FontsFonts come in a variety of shapes and they are categorized into families. For example, Arial is the name of a popular and commonly used font. Figure 3.6 Sample fonts found on most computersWithin the Arial family, there are Arial Narrow, Arial Black, Arial Rounded MT Bold. Many fonts have strange names, like Bodoni Poster. Courier is very similar to the type found on Selectric typewriters. Times New Roman is the font used in this paragraph and throughout the course material. Font size is measured in points. 72 points is equal to one inch. The type you’re looking at now is 10 points. It is common to make titles and headings a few points larger than the data in your spreadsheet.The fonts installed on your computer can be quickly selected by using the Font and Font Size drop-down menus in the Home tab, Font group within the ribbon. Figure 3.7 The Font and Font Size drop-down toolbar menus within the Home tab, Font groupBy clicking on the down arrowhead to the right of the font name, a drop-down menu reveals all the fonts available to you.The Formatting tool bar can also be displayed by right clicking the button while the cursor is in any cell. Figure 3.8 The Toolbar is displayed when right clicking on any cellIn addition to size, a single font can have one of four styles, it can be underlined and/or colored, and it can display one of three effects. These choices are available in the same toolbar. The three most frequently used styles, Bold, Italic, Underlined, and COLOR have buttons on the Formatting toolbar which makes it easy to apply these styles. Figure 3.9 Common Formats UsedYou also have additional formatting choices by right clicking on any cell and choosing the Format menu. Figure 3.10 The Format Cells DialogOther options like borders and shading will be discussed in a later lesson.Preset formatting for cells and tables within an Excel spreadsheet can be found on the Home tab, Styles group.Print a SpreadsheetPrinting a spreadsheet is somewhat more complicated than printing a word processing document. The reason can be traced back to the original intent of the two programs. Originally, word processing documents were bound, or closely-coupled, to a standard 8.5 by 11 page size. Originally, spreadsheets were freed, or de-coupled, from the standard, multi-column accounting pad. Printing parameters like physical page size, margins around all four edges of the physical page, printing orientation, headers, footers, etc. are supported by both programs, but the method of implementation is different. You set these parameters in Page Setup… under the Office Button->Print->Print Preview view. Figure 3.11 Getting to the Page Setup DialogThe first issue you need to resolve is where page breaks, both horizontal and vertical, will occur in your spreadsheet. Excel will try to figure how much will fit onto the page defined in Page Setup as shown above and will automatically insert page breaks for you. So a good first step is to see what Excel has already done automatically. This can be done two ways: (1) select Page Break Preview under the View tab, Workbook views group or (2) select Print Preview under the Office button as shown above.Figure 3.12 Showing Page Break View by clicking Page Break View Using (2) is preferable to (1) because headers and footers are also displayed in Print Preview.If the results of the preview are satisfactory then you are ready to print. To print a spreadsheet,select the Print Menu->Print command on the Office Menu Button. The appearance of the Print dialog box varies depending on your brand and type of printer, but it should give you an opportunity to choose which pages you would like to print and how many copies you want printed. Figure 3.13 Print Command under Print Menu in Office ButtonSet page breaksIf the results of the preview are not satisfactory, you can override the automatic settings and place a page break, both horizontal and vertical, anywhere you want, as long as that placement results in a smaller page size than the automatic setting. Excel will then adjust the automatic settings to compensate for your manual settings. Automatic page breaks appear as a dashed line with very short dashes. To set a page break, click on the row heading below where you want the break to occur or click on the column heading to the right of where you want the break to occur. Then select the Breaks->Insert Page Break command on the Page Layout tab, Page Setup group. Manual page breaks appear as a dashed line with longer dashes.If you experience strange results when you print your spreadsheet, check for unwanted page breaks. Tip: It is easier to see page breaks, and the difference between automatic and manual page breaks, if gridlines are off. It is always smart to call for a Print Preview before printing.To remove a vertical page break that you have manually inserted you must select a cell or column immediately to the right of the dashed line. For example, if the dashed line is between column C and column D, select any cell in column D to remove the page break. Then select the Breaks->Remove Page Break command in the Page Layout tab, Page Setup group. To remove a horizontal page break that you have manually inserted you must select a cell or column immediately below the dashed line. For example, if the dashed line is between row 11 and row 12, select any cell in row 12 to remove the page break. Then select the Breaks->Remove Page Break command in the Page Layout tab, Page Setup group. Figure 3.13 Page Breaks Command on Page Layout tab, Page Setup groupIf the results of manually setting page breaks are satisfactory then you are ready to print. To print a spreadsheet select the Print command on the Office Button Menu. The print dialog box gives you an opportunity to choose which pages you would like to print and how many copies you want printed.Setup the Printed PageIf the results of manually manipulating page breaks are not satisfactory, or there are other print parameters that you want to change, then you need to select the Page Setup group under the Page Layout tab. (see Figure 3.13)You can also access the Page Setup dialog box by selecting Print > Print preview under the Office button (see Figure 3.11)Page setup dialog box is is sub-divided into four areas:Orientation and SizeMarginsHeaders and FootersOther OptionsUnder the Page tab you will find the Portrait or Landscape orientation setting, Scaling, and Paper Size. There are 22 pre-defined paper sizes built into Excel. If you cannot find the size you need in that list, you can define your own, custom size. Your printer must be able to accommodate whichever size you choose.. Figure 3.16 The Page Setup dialog box looking at Orientation and SizeThe Print Quality option is usually overridden by the settings you make for your printer. You can also set where you want page numbering to begin on the Page tab. This is handy when your workbook contains a number of pages including one that acts as a cover sheet on which you do not want a page number to appear.The margins settings are located under the Margins tab and can be set to inches and/or fractions of an inch for the top, bottom, left and right. While working in your spreadsheet you will not see these margins. You must use Print Preview to see if the margins are correct before printing. You may want to minimize your margins to get more rows/columns printed on the paper. The header and footer margin text boxes are used to set the distance from the edge of the paper to the top of the header or the bottom of the footer.Figure 3.17 The Page Setup dialog box looking at MarginsIn Figure 3.17 the distance from the edge of the page to the top of the header is 0.5 inches and to the top of the spreadsheet is 1.0 inches. This creates a header textbox that is 0.5 inches high. If the material you put into that textbox needs more than 0.5 inches, you will have to come back to this dialog box and adjust the Top margin.____________________________________The following exercises will help solidify the topics taught in this lesson. The sample spreadsheets can be found on the computer hard drive in the Excel 1 2007 Files folder. Explore the samples and have fun making changes.Exercise A - Use Microsoft Excel helpIn this exercise you will use a blank spreadsheet and the Microsoft Excel Help system to locate information on several topicsStart Microsoft Excel.1Select the Help Menu and put the cursor in the search box.You are interested in finding help on copying the results of a formula2Enter “How do I copy a formula?” then click Search.3Select the help document Move or Copy a Formula.4Adjust the widths of the windows to make reading easier5Read through the description (on the right) in the Help window and check the meaning of the phrase “absolute” shown in maroon.Now you are interested in some information on changing formats6Enter How do I change formatting? and then click Search.7In the window below, double-click on Change the font or font size in Excel8Read through the description (on the right) in the Help window and check the meaning of the phrase “defaul” shown in maroon.Congratulations! You have successfully completed Exercise AExercise B - Format the Budget SpreadsheetIn this exercise you will continue working on the budget spreadsheet from Lesson Two. The budget spreadsheet can be found on your hard drive as 1.3budget.xls. Make changes to the spreadsheet and save it as 1.3budget_rev.xls.Select the Excel 1 2007 Files folder on your computer.1Start Microsoft Excel by selecting it from among the Programs in the Start Menu. It should open with a blank spreadsheet named Book12Select Open under the Office Button3In the Open dialog box, find the My Documents folder4Click on (Select) the Excel 1 2007 Files folder icon 5Click on (Open) to open the folder containing the Excel 1 files6From the list of files, click on 1.3budget and then click the Open button.To preserve the original spreadsheet, save the spreadsheet using a new name.7Select the Office Button and then select the Save As... command.8Select Excel Workbook at the top of the list9In the File name: text box, type the new name 1.3budget_rev10Verify that the Excel 1 2007 Files is displayed in the Save In: text box11Click on the Save buttonThe budget spreadsheet should now be open.Changing font styles to draw attention to the titles12Click in cell A1 to select the spreadsheet title cell13Click on the Home tab14Under the Font group choose the Times New Roman font, 18pt, Bold and the color Blue under Fonts group.Use the small scroll bar to see the Times New Roman font selection15You may need to adjust the row height after changing the font size in A1. To change the row height, double-click on the border between the row headings for rows 1 and 216Click in cell A2 and change the font style to Bold and Italic using the buttons on the Home tab, Font group. 17Click in cell A21 and make the same changes18Click in the row heading on Row 3 and make the month titles Bold. Repeat for the month titles in Row 2219Click and drag from cell A4 through cell A16 to select all the expense titles20Click on the Home tab (if you are not there already)21Choose Bold and the color Dark Red under the Fonts group 22Select all the income titles, click and drag from cell A23 through A2723Choose Bold and the color Dark Green under the Fonts group 24Change the remaining titles to BoldExpense TotalGrand TotalIncome TotalGrand TotalDifference25Using the font size selection box on the Home tab, Font group, change the font size in cell A2 and A21 to 16 points.You may need to adjust the row height after changing the font sizes in cells A2 and A21.26Click in the row heading for Row 2 to select the row27In the Home tab, Cells group select the Format drop down menu and select Autofit Row Height28Repeat the same procedure for Row 2129Press Ctrl+Home to return to cell A130Remove the gridlines by selecting checkmark next to Gridlines under the Show/Hide group in the View tab.31With the gridlines removed, select the Print Preview command under the Office Button to view your handy work.32While in Print Preview, the scroll in the mouse will toggle you between page 1 and 2 and the Close Print Preview button will close Print Preview. You can also click on the Next Page/Previous Page buttons.Change the spreadsheet to print in Landscape mode33Select the Page Setup group under the Page Layout tab 34Select the Orientation drop down menu and and choose Landscape..35Select the Print/Print Preview command under the Office Button36Click the Close Print Preview button to return to the spreadsheetAdd a page break between June and July to split the year into 6 month intervals37Click in the column header just to the right of where you want the vertical page break; in this case, click in the column header of column H38Select the Breaks button under the Page Setup group in the Page Layout tab39Select Insert Page Breaks40You can see the two types of dashed lines indicating a page break. To see what it would look like on the printed page, go to the Office Button, Print option and click the Print Preview button .41Use the Next Page button on the Print Preview screen to see page 2, then press the Close button to continueRemoving a page break is an important skill42To remove a vertical page break, click in any cell which borders on the right of the dashed line. In this case, any cell in Column H43Select the Remove Page Break command on the Breaks Button in the Page Setup group in the Page Layout tab. Removing horizontal page breaks works basically the same way. You must click in the cell just below the gray dashed line. With the page break removed and if you are satisfied with the changes, print you spreadsheet44Restore the gridlines by selecting the checkmark next to Gridlines on the Show/Hide group in the View tabRemember that this option only affects the Gridlines on the screen. Printed Gridlines are controlled under Page Setup/Sheet under the Page Setup group in the Page Layout Tab.45Print your spreadsheet either by selecting the Print/Print command under the Office Button .46Return to cell A1 by pressing Ctrl+HomeYour completed spreadsheet should look like Figure 3.18 below.Figure 3.18 Exercise B completed spreadsheet47Save your work using the Save command under the Office Button.Congratulations! You have successfully completed Exercise BExercise C - Enhance the Budget Spreadsheet FormatIn this exercise you will continue working on the budget spreadsheet. 1The 1.3budget_rev spreadsheet should still be open. If it is, skip to Step 4.2Use the Office Button and click on Open3Open 1.3budget_rev under My Documents\Excel 1 2007 Files folderThe budget spreadsheet should now be open.4Select the range of cells B4:M165Find the Home tab, Number group 6Use the increase decimal button to increase the decimals to 2, then select “,” button, 7Repeat the same for the income range of cells B23:M27Center the Title8Click in cell A1 and drag to cell M19Merge the cells and center the title by clicking on the Merge Cells dropdown oin the Home tab, Alignment group. Choose Merge & Center from the dropdown menu Figure 3.19 The Merge Cells button on the Home tab, Alignment group10Click outside the current selection, in any blank cell, to turn off the highlighting11Select the Page Layout tab in the ribbon 12Use the Orientation dropdown to choose Landscape14Select the Margins dropdown menu and choose Custom Margins15Double-click in the Left Margin: text box and type .416Repeat in the Right Margin: text box then click OK.17Find the Sheet Options group on the Page Layout tab18Uncheck the Gridlines Print box and uncheck the Headings Print box19Click on Print Preview20Verify that the budget spreadsheet fits all on one page. If it doesn’t go to the Scale to Fit group on the Page Layout tab. Under the Width dropdown menu choose “1 page” and under the Height dropdown menu choose “1 page”.21Click on CloseYour spreadsheet should look similar to the one in Figure 3.20 on the next page.Figure 3.20 Exercise C completed spreadsheet22Select the Office Button and then select the Close command – do not save the spreadsheetCongratulations! You have successfully completed Exercise CExercise D - Enhance the Auto Mileage SpreadsheetIn this exercise you will continue working on the auto mileage spreadsheet from Lesson 2. The auto mileage spreadsheet can be found in the Excel 1 2007 Files folder as 1.3auto.xls. Make changes to the spreadsheet and save it as 1.3auto_rev.xls.1Close all open Excel documents.2Using the Office Button|Open sequence, open 1.3auto in the Excel 1 2007 Files folderTo preserve the original spreadsheet, save the spreadsheet using a new name.3Using the File | Save As… sequence, save the document as 1.3auto_rev in the same folderThe auto mileage spreadsheet should now be open.Changing the font type for the entire spreadsheet4Click on the small button just left of the Column A heading button and just above the Row 1 heading button. This button is called the “Select All” button. The entire spreadsheet should be highlighted. Figure 3.21 The Select All button or how to highlight all cells in a spreadsheet5Click on the Home tab, go to the Font group and change the font type to Times New Roman. You will need to click on the drop down arrowhead on the right-hand side of the font box to see all the font choices.6Also change the font size to 12 points using the font size box on the Formatting toolbarThe contents of the spreadsheet now looked “scrunched” into their cells. This is because, while you made a global change in font size, Excel was not smart enough to change the Row height to make room for your change. Excel will automatically adjust Row height for local changes to individual cells.7Starting at Home tab, in the Cells group, use the Format | Autofit Row Height sequence to adjust Row height8Click once in cell A1 to turn off the highlighting.Selectively change the spreadsheet titles9Click in cell A1 and change the title to 20 points, Bold10Click and drag to select cells D2 and D3, change them to Bold and Italic11Click in the row heading for Row 5 and change the titles to Bold and Blue. Font color can be selected from a button in the Font group.Readjust the column widths12Click and drag to the right across the column headings Column A through Column E13Select the Autofit Column Width command using the Format button in the Cells group14Click into cell A1 to remove the highlightingOops, Column A is too big15Position the mouse pointer on the borderline between the Column A heading and the Column B heading. Once the mouse pointer turns into the vertical adjust pointer, click and drag the dotted vertical line to the left until Departure City just fits, thereby making Column A narrower.16Click and drag to select all Departure Cities A6 through A54 and change the color of the font to any color. Font color can be selected from a button on the Home tab Font group.17Change the color of the font on the Destination Cities B6 through B5418Click and drag down across cells E2 and E3 and change their color to Red19Click and drag from cell C6 through cell E54 and change to Italic20Press Ctrl+Home to return to A121Remove the gridlines by deselecting the Gridlines checkbox in the Show/Hide group on the View tab (note: you can also delect the View Gridlines from the PageLayout tab, Sheet Options group)22With the gridlines removed, select the Print Preview command under the Print menu in the Office Button to view your handy work.The spreadsheet spans across two pages. Use the Next Page and Previous Page buttons to change which page is displayed. Use the Close button to close Print Preview.Adjust the margins to fit all the data on one page23Open up the Page Setup dialog box in the Page Layout tab under Page setup group24Type over the 1” number highlighted in the Top margin text box and type .2525Press the Tab key and make the Bottom margin the same, .2526Click the OK button27Click on the Print Preview button on the Print menu in the Office buttonThe data still overflows onto Page 2. Let’s try changing the space between the rows of information.28Click and drag in the row heading from Row 6 to Row 54 to select all the detailed information.29On the Home tab select the Format button in the Cells group, the click Row Height. Change the size to 13 and click the OK button.30Now click on the Print Preview button on the Print Menu in the Office ButtonSuccess!!! By reducing the size of the margins and the spacing between rows, you were able to fit the spreadsheet on one page.40Print the Auto Mileage spreadsheetYour completed spreadsheet should look like the one in Figure 3.22 on the next page.Figure 3.22 Exercise D completed spreadsheet41Save your work by selecting the Save command under the Office button.Congratulations! You have successfully completed Exercise DSummaryNow you can...Use the Show Formulas command in the Formulas tab, Formula Auditing group to display spreadsheet formulas.Remove gridlines when viewing a chart and add them to printed reports.Change Fonts and Font sizes.Set formatting preferences for your spreadsheet using the commands in the Page Setup group under the Page Layout tab.Identify and manipulate Page Breaks.You have explored how to change the appearance of a spreadsheet both on the display screen and on paper. In the next lesson you will learn about functions like sum, average, min and max to further enhance your spreadsheets. You will also explore sorting and spell check to create complex and comprehensive spreadsheets.NOTES ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- using excel for calculation activities ks1 2
- excel activity 12 sum function
- an introduction to excel
- change the spreadsheet to print in landscape mode pbdd
- excel calculations self test duke university
- albright s dadm tools add in iu
- module one get started with excel
- introduction to excel formulae and functions
Related searches
- how to print 2 sided in word
- change excel spreadsheet to pdf
- how to print in word
- how to print something in java
- how to print in java
- how to print a character in c
- how to print list index in python
- how to change the index pandas
- python program to print the fibonacci sequence
- change the decimal 0 050 to a ratio
- how to print a double in c
- how to print in windows 10