ch07_09 - Solving Accounting Principles Problems Using...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Solving Accounting Principles Problems Using Excel for Windows to accompany Accounting Principles Ninth Edition Rex A Schildhouse, LCDR, U.S. Navy, Retired, M.B.A. San Diego Community College District, Miramar Campus, San Diego, CA Jerry J. Weygandt, PhD, CPA, Arthur Andersen Alumni Professor of Accounting, University of Wisconsin, Madison, Wisconsin Donald E. Kieso, PhD, CPA, KPMG Peat Marwick Emeritus Professor of Accountancy, Northern Illinois University, DeKalb, IL Terry D. Warfield, PhD, CPA, PricewaterhouseCoopers Research Scholar, University of Wisconsin, Madison, Wisconsin Chapter 7 CUSTOMIZING EXCEL Chapter Outline Excel Defaults View Tab Through Options Calculation Tab Through Options Changing the Default File Directory Edit Through Options Save As To Change File Locations And Names Excel Workbook / File Naming Recommendations Excel Defaults The “defaults” of Excel specify and control how Excel is going to respond in situations where the response is not specified by the user. For example, clicking on the “Save” icon on the shortcut bar, the 3 ½” disk icon, will result in the current workbook being saved back to the original location if it has been saved before. If it has not been saved before, Excel will look at the defaults and accept that the directory of the workbook should be utilized as controlled through the path Tools > Options > General. On this tab there is a window labeled “Default file location.” By changing this window’s values you can change the default location. Through a standard installation of Microsoft Office this location may be “Documents and Settings.” This same location is utilized by Word and other programs as their default save location. However, due to the large number of Excel files generated, I have chosen to change the default location to a new directory called “Excel Files” on the local “C” drive. The average user may not need to change the default values of Excel but knowing how to “read” them will enable the user to follow the path Tools > Options > General to see where Excel is saving their data. Another default established through the path Tools > Options > General is the font style and size. There are many fonts installed into the Office Suite through the default loading process. Almost all of these are available to Excel by changing the value of the “Standard font” selection. By changing the font and the size to that font to the values most commonly used, any new workbooks opened will utilize those default values to establish the style and size of the font. Through the path Tools > Options > View the presence of gridlines on the display screen is controlled. These gridlines are provide an orientation to the columns and rows and this value is usually left as selected as indicated by the checkmark in the selection box. However, if Excel is utilized as a presentation tool, this selection can be changed to remove the gridlines. The gridlines on the display screen are not the gridlines appearing on the printout. The printout gridlines are controlled through the path File > Page setup > Sheet. By selecting “Gridlines” under the “Print” category as indicated by a checkmark in the selection block, there will be gridlines on the printout. As with the display gridlines, this selection is exclusive to the printout. By default and standards to generate a “clean” printout, most Excel printouts do not utilize gridlines. Section 2, Page 39 Page 40 Solving Accounting Principles Problems Using Excel for Windows If Excel consistently does something that you want changed, chances are it is controlled by a “default.” To change a default within a file, you can check for that particular control element under several locations including File > Page Setup and its numerous tabs. Once the change is made, then save the workbook to preserve the default setting. To save the new default setting as a global change, you need to save the workbook as the default template to be opened by Excel each time upon opening. This can be done with the instructions and guidance of “Help” from the menu bar and “Customize How Excel Starts” as a query. Through this procedure you will be saving the workbook as a “Template” – a special file that Excel will utilize to open any new documents. These changes will not affect workbooks already created. View Tab Through Options By following the path “Tools > Options” and selecting the “View” tab you can set many of the presentation issues within Excel by selecting – having a checkmark next to it, or deselecting it – by not having a checkmark next to it or by selecting a value through a drop-down .window. Many of the specific options within the “View” tab are explained in their individual sections elsewhere. However, a quick scan of these options will allow you turn the gridlines on and off for screen presentation, display zero values, display horizontal and vertical scroll bars and reveal or hide comment indicators. Calculation Tab Through Options By following the path “Tools > Options” and clicking on the “Calculation” tab you gain access to controlling whether Excel will do a recalculation with every entry completion as marked by striking the “Enter” keys or leaving the current cell. Suppose you are working in a large workbook with many formulas. With each data entry affecting a formula the workbook recalculates the formulas. During this time you are waiting for the processor to give you keyboard access and update your video presentation. You can preclude this by deselecting, removing the checkmark, from the “Automatic” option and selecting “Manual” in the “Calculation” section of the tab. If you are working in a workbook and have entered an amount of data that you want to have calculated, you can strike the “F9” key in the “Function row” on the keyboard and all calculations will be updated without changing this selection. You can also follow the long path and reenter this tab through the path “Tools > Options” and then selecting the “Calculation” tab and clicking on the “Calc Now (F9)” button on the right. The last option of this particular selection, and a normal default if you are operating in the “Manual” calculation mode, is to recalculate upon shutdown. This may extend the time to save and close but it updates the data for the next opening. If you elect to deselect this event you may open a workbook with data that is presented but not taken into account with the formula throughout the workbook. This can be a dangerous proposition and position. The “Precision as displayed” selection on the tab means that if you set the worksheet to display to two significant digits – 0.00 – and enter 0.004999 the value will be rounded to 0.00 not only in presentation by in data entry. Multiplying this cell by any value will result in 0.00 since Excel converts the value to the displayed value of 0.00 as a hard value. Caution should be used and this rounding should be understood before this option is selected. Chapter 7, Page 41 Changing the Default File Directory The default Excel directory is usually “My Documents.” This can be changed if you desire segregation of data or want to reduce the burden of going to a separate, special directory. To change the default directory, follow the path Tools > Options from the tool bar, then select the “General” tab. As shown in this screen print, the default file location has been changed to “C:\Excel Files.” With this setting, when Excel opens, it will access this directory when any of the open actions such as clicking on the “Open File” icon on the tool bar, following the path “File > Open”, or using the keystrokes “CtrlO” (Control – O for “Open”). Any default save action will save the file here and make it be easy to locate as it is a “root directory” location – right on the “C:\” drive, not within another directory. Before making this change or any change of this type, you need to create the target directory. You can do this through the Windows Explorer by selecting a local hard drive such as the “C:\” drive and then follow the path “File > New > Folder” or through the Excel “Open” dialog box. On the Excel “Open” dialog box the “Create New Directory” icon on the tool bar is shown in this screen print. This directory will be created in whatever the active directory is as shown in the “Look in” window. If you want to create a directory within the “C:\” directory, make sure that the “Local Disk (C:)” directory is shown in the “Look in” window near the top at the dialog box. There is a second default directory Excel utilizes frequently. On the “Options” dialog box under the “Save” tab you tell Excel where to put the “AutoRecovery” file and how often to save it. If you change this directory, make sure you create the target directory before you make any changes to this directory. When Excel performs an “AutoRecovery” operation, it will appear to be non-responsive for the several seconds that it is performing the function. There may be a 3 ½” disk icon and “scaling bar” on the lower tool bar indicating the operation. Edit Through Options There are a couple of very important items on the “Edit” tab found through the path “Tools > Options.” The first is the “Move selection after Create New Folder Page 42 Solving Accounting Principles Problems Using Excel for Windows Enter – Direction” selection. This setting controls the direction that you want the active cell and cursor to move upon striking the “Enter” keys. The options are Up, Down, Left, and Right. You may utilize the “Fixed decimal places” option to preset the significant digits displayed. If this option is not selected and in a generally formatted cell the value 0.123456789 is entered, Excel will display 0.123456789 in that cell. If this value is inserted in a cell within a workbook with “Fixed decimal places” set to “2”, entering this value will result in a display of “0.12”. However, if a formula multiples this cell’s value by ten you will get 1.23. However, if the “Calculation > Precision as displayed” option is set to two, entering 0.123456789 will result in 0.12 being displayed and held as the “hard value” within Excel. Multiplying this cell by ten will result in a value of 1.20 being displayed and held within Excel as the “Calculation > Precision as displayed” influenced the values upon entry. Save As To Change File Locations And Names During the “Save as” operation you can change the location of the file. It does not matter how you get to the “Save” or “Save as” dialog box. To change the location of the file upon saving, drop-down the available locations by clicking on the drop-down arrow to the right of the “Save in” window at the top of the dialog box. If the target directory is located on the “Local Disk C:\” drive shown here, you can change the location of the saved file to another location. Where file will be saved The name of the file If you use the “quick save” icon – the 3 ½” disk icon on the tool bar, you will not be given the save or the save as dialog box unless it is the first time the workbook is being saved and it has a default name such as “Book1” or “Book2.” Once a file has been saved, Ctrl-S, the path File > Save, or clicking on the 3 ½” disk icon on the tool bar will result in the workbook being saved to the same location and as the same file name as it was last opened under or saved as. To change the location of the file or to change the file name follow the path File > Save As as explained here. If you use the Save As dialog route to save the file in a new location or under a new name, the old or original file is not removed or deleted. It is there for later use. Excel Workbook / File Naming Recommendations There are many ways to name an Excel Workbook file. In this text, if a specific workbook file is used by a subject area such as the “Vlookup” section, the file name referenced for that section is “vlookup” and Excel will understand that it is an Excel file through the extension of “xls.” If the Excel workbook file is for a chapter such as Chapter 3, that reference file may be named “chptr3.” Depending on how your specific default and viewing preferences are set in Windows Explorer you will see the file as “vlookup”, “vlookup.xls”, “chptr3”, or “chptr3.xls.” These titles tell you what the subject matter or relational reference of the workbook is. Had these files been named “File01” and “File02” you would have to look through every workbook file to find the material referenced for the vlookup function. In most cases capitalization is not an issue so “Chptr3” and “chptr3” would be the same. If you are working across a network with a file server such as you might find in a computer lab or business environment, capitalization may be an issue due to your operating system. Chapter 7, Page 43 Suppose that you are working on an inventory workbook for finished goods and you chose to call the file “fg inventory” which Excel would make “fg inventory.xls”. This file may contain all activities within the subject area and would be required for a long period of time as both an active file and as a reference file. If the file is continuously opened and saved as “fg inventory” and that file is lost or corrupted by some action such as a hardware failure, software failure, or virus, the file and all of its data may be lost. However, if you back this file up through a systems back up function on a recurring basis, you should be protected to some degree. If you add a date to this file name such as “fg inventory 01-01-08” and after working with it for some period of time such as a week, and use the save as function to save it as “fg inventory 01-08-08” you have built your own backup into the system. If “fg inventory 01-08-08” is not available for some reason you may be able to revert to “fg inventory 01-01-08” for usage. Upon opening the “fg inventory 01-01-08” file, you should immediately use the save as function to save it as your new working file such as “fg inventory 01-12-08” to protect your base file of “fg inventory 01-01-08”. You must understand that you have lost data entered only into the “fg inventory 01-08-08” file and will have to reenter that data but one week’s worth of data is less than all of the data in history of the file. Suppose that you are required to submit the “fg inventory 01-01-08” file to an instructor or to your boss in an electronic format such as Excel. You could save the basic file as “fg inventory 01-01-08” and then use the save as function to save it as “fg inventory 01-01-08 rex” so that upon receipt the instructor or boss can immediately identity the file as to subject and ownership. If you had to make a revision on the same day and resubmit it, try something like “fg inventory 01-01-03 rex rev a” to indicate the revision status and preclude overwriting the original file. In naming files there are a few constraints. A file name may contain more than one period such as “fg inventory.01-01-08.xls” but this may confuse several operating systems and users. It is best to avoid multiple periods. The forward or backwards slashes, “/” or “\”, should not be used as this indicated different levels of directories to an operating system. Some networking systems and operating systems are restricted to lower case letters or to not more than 8 characters in a file title. Excel will assist you in this matter if it detects what are referred to as “special use characters” in a file title. Due to these types of restriction dates should be put into file names as “01-01-08”, “010108”, or as “Jan 01 08”, not “01/01/08” or .”01.01.08”. Chapter 8 PRESENTATION Chapter Outline Comments Column And Row Size Charting Pivot Tables Protection Read Only Files And Templates Drawing On Worksheets Macros Macros On Objects Comments Frequently the presentation of purely numerical data is insufficient for the effective communication of financial data within a worksheet. At other times you want to document where the data came from or what the data represents. Excel, as shown, will accept text entry into the cells easily and effectively and you have vast formatting capabilities on that text and numerical data. Additionally, Excel presents you with another very effective text presentation mode referred to as “Comments.” Comments are “Pop-up” blocks associated with a particular cell. They New Comment, can contain a wide variety of information and the text Previous within the block can be formatted with many of the Comment, commands and features found elsewhere in Excel. The “Comments” data file on the data disk contains examples of Next Comment what can be done with comments. These examples can be and are extreme for presentation purposes. Comments have icons on the “Reviewing” tool bar or menu listing. The “Reviewing” tool bar can be brought up by right clicking on the menu bars near the top of the screen and selecting “Reviewing” from the pop-up menu. To attach a comment to a cell follow the path Insert > Comment, right click on the target cell and select “Insert Comment”, or click on the “New Comment” icon on the tool bar – an envelope with an “aura” or “rays” from the upper left corner. The “Comment Box” will pop-up attached to the cell. Contained inside the comment box may be the owner’s name of the Excel program by default. This name can be left or removed. To remove the ownership name from the comment box simply highlight the text and delete it with the “Delete” key. Enter the text and data as you desire. The comment box can be resized to show only part or all of its comments. It can also be repositioned as to the location that it appears in by dragging it. When the data entry is complete simply click on the worksheet outside the comment box and the comment box will disappear. Any cell with a comment attached will bear a red triangle in the upper right corner. You can place your cursor over a comment triangle and the comment will pop-up and remain in view until you reposition the cursor. The Reviewing tool bar need not be active or available to read comments. Section 2, Page 44 Chapter 8, Page 45 If you desire to edit the comment follow the path Insert > Edit Comment, right click on the cell and select the “Edit Comment” option, or click on the “Edit Comment” icon on the tool bar – an envelope with a pencil writing on it. In the edit mode, you can still resize or reposition the comment box or reformat the contents. There is an icon to display all comments and to hide all comments with one key click. There is also a “Delete Comment” icon on the tool bar. Delete comment is also available through right clicking the cell and selecting that option. The text within the comments boxes can be formatted within the font formatting capabilities of Excel. Simply highlight the text, all or some, and apply the desired formatting to the selected text. Tip: Spell check will scan comment box contents as well as the rest of the worksheet. Column And Row Size DO NOT DO THIS ON THE EXERCISE OR PROBLEM TEMPLATES. Many of the templates have long text strings in them with specific justification and this will cause rapid growth of the worksheet. The size of the columns and rows can be adjusted within Excel in several manners. The first is to “grab” the right border of a column identifier or the bottom border of a row identifier with the left mouse button depressed when it becomes a double headed arrow and drag it (or them), or collapse it (or them), to the size you desire by moving the mouse. If you highlight more than one column or row at a time, whether adjacent or not, they will all resize to the same dimension at the same time. Remember that you can select or highlight nonadjacent areas by holding the control key down while clicking the columns or rows with the mouse. An alternative is to “Autosize” the column or row by placing your cursor over the right border of a column identifier or the bottom border of a row identifier and double clicking it with the left mouse button when the cursor becomes a double headed arrow, as shown. This changes the column or row’s size quickly to the largest item in the column or row. Another method of sizing a column or row is to highlight the column or row and follow the path Format > Column or Format > Row, as appropriate, and select what action you want. With the “Width” or “Height” option you will be asked to manually enter a value. For a column, the default width is approximately the width of 0123456789, 10.0, in default font. For a row the default height is 12.75. If you resize a column or row to a width wider than the screen or a height greater than the screen, you can resize it through the path Format > Column or Format > Row and select “Width” or “Height” and set to a value of approximately 10 for the column width or 12.75 for the row to return it to near default value. If you have a column or row highlighted you will also find width and height setting options on the pop-up menu if you right click the highlighted column or row. There are no hidden columns or rows in the exercise and problem templates but there are merged cells. Merged cells are covered elsewhere in the text. Charting Excel will assist you in making charts through the Chart Wizard. This wizard is accessed through the charting icon on the tool bar, shown here, or through the path Insert > Chart. There are functional examples of charts on the “Charting” workbook of the data files. One of the most important concerns in charting is picking an appropriate chart type for your data. In the examples in the “Charting” data file the information supplied is “Sales item”, “Cost of Sales”, and “Sales Revenues” (per sale item). This information is charted as examples in several ways. The use of the two charts titled “Cost of Sales” and “Sales Revenues”, both pie charts, may be appropriate for the conveyance of the specific information of Cost of Sales or Page 46 Solving Accounting Principles Problems Using Excel for Windows Sales Revenues - clear, and somewhat attractive. However, when the goal is to show the relationship between costs of goods sold and sales revenues of the individual items the goal is not accomplished by these two pie charts as they are relationally sized amongst their contained data. Both charts would be 360 degrees if one contained $1 and the other contained $1,000,000 so the relationship BETWEEN the charts is not clear. To portray the information required, additional charts must be constructed and a pie chart format may be inappropriate. By utilizing the Column (Vertical bars) chart, shown on the worksheet in “Bad Columns” (off to the right), the cost of sales and sales revenues relationship is clear and distinct in a single chart but, by default, the data is incorrect. When left to the defaults, “Charting” actually summed the cost of sales and sales revenues into a nonsense number. It is corrected by computing the “Sales Revenues less Cost of Sales = Gross Profit.” So another column or field was constructed to be referenced – the “Gross Profit” column. While the charts utilize “Sales revenues” for a title, “Gross Profit” is utilized for numerical data. You can see this by right clicking on a blank area of the chart and then selecting “Data Source” from the pop-up menu. By clicking on the “Series” items of the dialog box you can inspect the way titles and data matrices can be related. The relationship of sales revenues – total height, to cost of goods sold, is now clear. On the “Charting” worksheet is an Area chart and a Surface chart. The area chart infers that there is a flow over a span of time or a link in the chain of events that may not correct. So, while the chart looks nice, what does it convey without explanation? For this example data, the Bar chart may be a better visual presentation. Tip: To obtain the best guidance as to which chart type to use to convey what information, scan your textbooks as well as professional publications conveying the same type of information and look at the chart or graph styles used by the professionals. The quickest way to build a chart within Excel is to highlight the data range before selecting the Chart Wizard. For the data file, this is from cell A1 through D6. You can use the data on the Working Chart Area worksheet of the data file for this exercise. The incorporation of the totals line may add an additional, unwanted, field that may have to be removed later. Now click on the Chart Wizard icon. The Chart Wizard will walk you through the process first by selecting a chart type. Excel has many chart types and by utilizing the “Press and hold to view sample” button at the bottom of the Wizard box you will see your data in the chart mode selected – one reason why your data was highlighted before selecting the Chart Wizard icon. Since charts are easily made, edited, and removed, try any chart you desire – except a valid type for the data. A correction mode will be shown later. With the (incorrect) chart type picked, click on the “Next” button and the Chart Wizard will ask you to confirm the data range and series. Check both tabs. If satisfied, click on the “Next” button, if not modify the data as desired or back up to the previous screen utilizing the “Back” button. The data portrayed in the chart does not affect the source data. When the “Next” button is clicked, the Chart Wizard will ask for chart titles, axis titles and other items for the chart. Some of these items may not be available because of the chart type selected, some may be filled in with information the Chart Wizard “learned” from the data. Enter what you desire and the Wizard will show you a working model of your chart as you build it. Under the “Data Table” tab of the dialog box, if presented, you can select an option to show the source data with the chart. A very nice feature for some data presentations. When satisfied, click the “Next” button and ensure that the chart will be placed on the current sheet, the default selection by the Wizard and click the “Finish” button. The Wizard will finish your chart. If your chart is against an edge you can drag the chart away from the border of the worksheet. If your chart does not seem large enough – not all of the data is visible, you can change its size by clicking on the chart and getting “Frame ears” or “handles” to appear on the exterior borders of the chart, then grab one of these frames or handles with the mouse and drag the chart into a larger (or smaller) size. Be aware that the chart consists of many objects and clicking “into” the chart and getting “Frame ears” or “handles” inside the exterior frame means you have grabbed an object in the chart, not the overall chart. Chapter 8, Page 47 Your chart is finished but since you selected an inappropriate chart presentation for your data, your chart does not clearly portray the information. No problem. Like many things in Excel, your chart is a dynamic, live, object. Right click into the chart and select “Chart Type” from the pop-up menu. You are back into the Chart Wizard and can select a more appropriate chart type and preview it again if desired. By right clicking the chart you can gain access to many of the chart functions, features and capabilities. This includes being able to format fonts. You can also add, remove, and reposition labels. Try clicking on a label, once “Frame ears” or “handles” appear, strike the delete key and the label goes away. The chart can be copied and pasted elsewhere. The chart is “live” – if the source data changes, the chart changes. Be sure to save your work. Pivot Tables A pivot table is the presentation of data with multiple classifications such as district, salesman, and quantity of various items sold into a logical matrix. The Pivot Tables data file contains a data matrix for the construction of a pivot table. One of the requirements of a pivot table is that you must have two layers of classification on the left side, at least one layer of classification on the top, and data at the intersections of the classifications. This data need not be presorted or arranged, the Pivot Table Wizard will handle that for you. For the example column A is region – North, South, East, or West, column B contains the salesman’s name, row 1 contains the items sold, and the data for each region is contained in the matrix defined by the cells C2 through I32. As with the Chart Wizard, it is handier to highlight the information before you start the Pivot Table Wizard. So, highlight the range from A1 through I32. This will incorporate labels, titles, and data. Then follow the path Tools > Pivot Table and Pivot Table Chart Reports…. The Wizard’s dialog box will appear asking you what you would like to accomplish and where is the data. Ensure that you are using Excel data in the upper selection and that a Pivot Table is selected in the lower portion of the dialog box, then click “Next.” The Pivot Table Wizard should present you with a confirmation that you are using the range A1 through I32 for the data, confirm this and click “Next.” The next dialog box asks “Where do you want the table to appear?”, select on a new worksheet and click Finish. Excel presents you with a blank table that is used to build the table itself. From the Pivot table field list drag the “Region” item to the top of the pivot table where it says “Drop column fields here”, then drag the “Salesman” item to the area where it says “Drop row fields here”, then drag each of the sales items into the center grid where it says “Drop data items here.” As you drop your items on the table Excel will start working. As you drop each item, the item will change go bold print in the list to indicate that it has been used. The resulting table will show you who is selling what items where as well as the totals sold for the item and the totals sold in each region. Through the use of the filters, activated through the drop down arrows on the titles, you can control what is displayed. Pivot tables are memory intensive events for the computer. If you are limited as to the number for fields or it takes more than a couple of seconds to generate this table that can be expected. Page 48 Solving Accounting Principles Problems Using Excel for Windows Protection In the “Protection” data file on the data disk search for the text strings “Hi!!!!” and “You found it!!!!” and you will find them in cells R555 and R556 respectively. However, there is one more occurrence of each on the worksheet. The “Protection” worksheet has had the cells round cells Y571 and Y572 formatted to “Locked” and “Hidden” through the “Format Cells” dialog box’s “Protection” tab, shown here, and then the worksheet was protected through the path Tools > Protection > Protect Sheet. There is no password so you may remove the protection if you desire. Even clicking into the cells will not reveal their contained text in the formula input window of Excel, but that text will be visible in the cells when the cells are highlighted. This text has been additionally hidden with white text formatting to make it harder to find, had it been black or another color text, it would have been visible without highlighting. Why have a “Protection” capability within Excel? You have assembled a large worksheet of inventory data consisting of thousands of lines of data and are about to distribute it for a meeting to be held in three days after the participants have had time to review it. “Protection” will preclude them from modifying their received file providing you a common reference document in the meeting. Or, because you are building templates and standardized worksheets and workbooks for the organization, you can structure the worksheet or workbook as you like and then format the areas you do not want modified leaving the user input areas open and available. Starting at the cell A34 and continuing down and right, part of the “Demo File” is pasted. The entire range of the demo file was protected, (for speed). Then the protection was removed from cells with the yellow highlighting by clicking into one cell and holding the Ctrl (Control) key down and clicking into the rest of the cells one by one. Once all the highlighted cells were selected, one cell was right clicked and through the “Format Cells” option and the “Protection” tab, the “Locked” and “Hidden” formatting was removed. Then the worksheet was protected – without a password through Tools > Protection > Protect Sheet. As an exercise, enter your name in the “Name:” entry area identified by the yellow filled highlighting. Not a problem. Try to change the “Instructor:” title to “Professor:”. Excel presents you with an advisory that the worksheet is protected and that capability is not available. This maintains standardization of the worksheet. The rest of the demo problem is protected so that data can only be placed in the yellow highlighted cells. Restricting your ability to edit or change the data. Note: None of the exercise and problem templates are protected so you have maximum flexibility within the data files. So exercise due care when working with these data files. “Protection” requires several actions to be effective. First, the general default of Excel is that every cell, if protection is invoked on the worksheet, will be protected. A good, safe, default but seldom a valid statement. Since you want to insure the configuration of all of your cells, select the entire worksheet through the “Select all” button above the first row number and to the left of column A indicator or use the keystrokes Ctrl-A. Next, right click the worksheet to get the pop-up menu and select “Format Cells” or follow the path Format > Cells to get the “Format Cells” dialog box, and select the “Protection” tab. Then, with the assumption that you want to protect most of the worksheet, or remove the checkmark from “Locked” allowing all the cells not later identified to be utilized by the user to be unprotected then click “OK.” Now, if you selected “Locked” while the worksheet was selected, click into the cells to select the cells you want the user to have access to and, with access to the “Format Cells” dialog box, remove the checkmark from “Locked.” You can access and format more than one cell at a time by holding the Ctrl (Control) key down while selecting cells by clicking on them. Again clicking “OK” at the completion of each selection process. If you unlocked all of the cells and then select the Chapter 8, Page 49 few cells you want to protect, you would place a check mark in the “Locked” window after selecting the cells you wanted locked. Then you would click “OK” to complete the selection process. After all the cells have been configured as “Locked”, or not locked by removing the checkmark in locked, follow the path Tools > Protection > Protect Sheet. The resulting pop-up dialog box will ask what privileges you wish to make available to the user and if you want to utilize password protection. If you do not provide a password, the simple process of Tools > Protection > Unprotect Sheet will remove the protection. This was the level provided on any protected worksheets on the data disk. If a password is utilized, make sure you can recover the password later as recovery of the worksheet may be very difficult without it. Clicking “OK” will complete the process and activate the protection. The workbook now needs to be saved to record this configuration into the file on the computer media. This does not make the workbook a template or make the workbook a read only document. Tip: By accepting the defaults of allowing a user to select locked and unlocked cells you can copy a protected worksheet and paste it into a new document recovering part of your “frozen” work if you lose the password. Read Only Files And Templates The data file titled “Data Form” with the extension of “XLT” is a special type of Excel file called a “Template.” This type of file has a special property associated with it in that, when opened within the later versions of Excel, it will create a standard Excel file titled “Data Form1” when opened the first time. When opened the second time it will create “Data New Workbook Icon Form2.” These files can be saved by their default names or can be saved with a new name and in a new location by using the File > Save As path to alter the title and / or path. In some versions of Excel you will receive an advisory screen pop-up that states this is a template and has “read only” properties. In these versions of Excel simply acknowledge the advisory pop-up and follow the path File > Save As to save the file as a new name and, if desired, to a new location. This feature of read only capability was built into Excel to allow you to build one standard reference and structure file, a template, and be advised that you do not want the core or base file to be populated with data since you will using that structure again with new data. Templates are frequently used for files that are used time and time again with one structure and new data, such as time sheets, travel / expense reports, and job logs. Note: The use of “Exercise and Problems Templates” as provided with this book simply means a standard data file that everyone in the class, course, or program will be using. The data files accompanying this book are not read only and can easily be written over. Drawing On Worksheets The first and foremost item to remember in this Text Box Icon section is that most, if not all, of these items are “on top” of the worksheet. They are not in cells or attached to cells. You can enter data beneath them and around them without a problem. To bring up the Excel “Drawing” tool bar right click on any menu bar and select “Drawing” from the options. On the “Drawing” tool bar, Excel gives you many preformatted and adjustable capabilities as well as some degree of free hand modifications. The “Drawing” tool bar is shown here with the standard or default options. The first item to be addressed is the ability to draw arrows. To draw an arrow, click on the arrow icon and then click on the point where you want the arrow to appear to be coming from. If the point of origin is not visible on the screen after you activate the arrow, Page 50 Solving Accounting Principles Problems Using Excel for Windows use the scroll bars to navigate the worksheet or hit “Esc” (Escape) two or more times to cancel the “Draw Arrow” command while you reposition the screen. Place your cursor over the point of origin and click and hold down the left mouse button. Now move the mouse cursor to the destination or target, if the target is not visible on the screen move your mouse cursor towards the general direction and Excel will scroll towards it. When you have the mouse cursor at what will be the arrowhead in position, release the left mouse button and the arrow will form. If the “weight” or thickness of the line is other than what you want, while the arrow is still “active” as indicated by empty circles at each end, click into the icon with the numerous different thickness lines on the “Drawing” tool bar and pick your choice. If the texture of the arrow is less than desirable, while the arrow is still active, click into the “Textures” icon, usually next to the “Weight” icon, and pick your choice. You can also right click the object and get a pop-up menu which will give you access to the “Format AutoShape” dialog box. If the arrow is not selected, simply click on it when your mouse cursor is placed over it or immediately around it and the cursor becomes a four-headed arrow icon. Once the arrow is drawn, place your cursor back on top of it and move the cursor slightly until you get a four headed arrow for a cursor. At the point that the cursor changes, left click the mouse to “grab” the arrow then right click it and a pop-up menu will appear. From this menu select “Edit Points.” With this option active, you can place your cursor on the arrow at any point and bend it around. To flex the arrow, move your cursor over the arrow and when it becomes a “crosshairs” type presentation, left mouse button down and drag it around. The point of origin and the destination will remain the same but the path will change. If the corners are abrupt, place your cursor on a corner, right click the arrow to get the pop-up menu and select “Smooth Points.” At this point on your arrow the corners should be rounded. If you place your cursor over a corner point, shown only while the arrow is in “Edit” mode, you will see a “line handle” appear, grabbing and moving this line handle changes the aspects of the corner. Whether you have an arrow or line selected, they work the same; you can change the weight, pattern, and color through the icons on the “Drawing” bar. There is a text box on the tool bar, it is a white sheet of paper with a letter “A” in the upper left corner and lines making it appear like a newspaper. Click and release on this icon and then click into the worksheet and keep holding the left mouse button down. While you are holding the left mouse button down, drag the cursor away from the point of origin to create a text box. At any time, you can drop the text box drag by releasing the left mouse button and start to enter text into the box. If the box was created to wrong size, click near the borders to get the handles or ears active, then grab a handle or ear and drag it to the correct size. To move the box, select the box, then move your cursor near the edge until it becomes a four arrow headed object, while the cursor is a four headed arrow, left mouse button down and drag it to the new location. Just like dragging other objects such as arrows and lines. Spell check will check the spelling inside text boxes. You can fill the box by selecting it and then selecting a “Fill” color. You can change its borders by selecting the box and selecting a line weight and texture from the tool bar while the box is the selected object. Many of the screen prints in this book were done with text boxes and arrows from the drawing tool bar. Additional formatting options are available by right clicking the text box and then selecting “Format Text Box” from the pop-up menu that appears. There are preformatted rectangles and ovals on the drawing tool bar. Simply click on the icon then click into the worksheet and start to drag them around until you get the shape you want. You can drag them to a new location by moving your cursor near an edge looking for the four headed arrow before left clicking down, just like arrows, lines, and text boxes. Hint: If you hold the shift key down on the keyboard while creating a rectangle it will remain a square as you resize it. If you hold the shift key down while creating an oval, it will remain a circle while you resize it. Holding the shift key down while drawing lines and arrows will give you straight lines. Chapter 8, Page 51 To delete an object, select it by clicking on it when the mouse curser becomes a four headed arrow and hit the “Delete” key. Using the cut command will allow you to paste it somewhere else – once. Using the copy command will allow you to paste it in numerous locations. It can be repositioned by dragging it once placed. Hint: If you want the object moved from its original location but placed in numerous other locations “cut” it from its original location. The paste it into its next location. While Cut to Paste is a one-time event, the object is still selected by default. Now you can simply copy it with Ctrl-C, Edit > Copy, right clicking it and selecting “Copy” or any of the other copy methods and then move to the next points to paste it repetitively without going back to the very first (original) location to delete it. An important issue with drawings, images, clip art, and pictures is their positioning within the presentation’s other objects. On the “Drawing” data file is a color JPEG image of a pencil. Click on the pencil to select it. Then right click the pencil. From the pop-up menu select the “Order” option. This establishes where the image will be in relation to other objects. From the options select “Bring to Front” and the pencil will no longer be hidden behind the other objects. Many of your objects within Excel and Word have this option available to them. On the tool bar and through the path Insert > Picture you can insert pictures and clip art onto the worksheet. This is a great feature to document fixed assets or merchandise for inventory type issues but the file sizes get large very quickly. WordArt is also available for use through the drawing tool bar as well as the path Insert > Picture > WordArt. Inserted here is a scanned image of a pencil. It is saved as a JPEG file and inserted into the text through the same process as it was inserted on the Excel Drawing data file. Do not miss the opportunity to play with the “Insert Diagram” or the “Organizational Chart” tool. Hint: These tools can be very effective when used well. It is recommended that you review professional magazines, newspapers, and textbooks to get examples of good presentation techniques. None of the examples in “Drawing” data file would be used in a professional format, they are provided as examples of the power (and the abuse of power) available in Excel. Macros Macros are small programs that you can build out of keystroke modeling – you record them by doing what you normally do for later, repetitive, use. When needed you activate the macro and have it do the task for you. To record a macro ensure you know the keystrokes and commands you desire to utilize in the macro. If you record an error in a macro you may have to edit the macro to remove the error or rerecord it. If not corrected, the macro will commit that error every time it is run. However, if the correction is made in the recorded keystrokes, it just consumes time and memory. You will build a simple macro for demonstration purposes that formats the cell to bold, italics, and underline in a single event. Place your curser into cell A10 of the Macros data file to make that cell the active cell. Bring up the macro dialog box by following the path Tools > Macro > Record New Macro. You will be asked for the name of the new macro and where you would like it stored. The name should not have spaces or special characters in it. Excel will tell you if the title is unacceptable. The demonstration macro, which is on the Macros data file is named BoldItalicsUnderline so try BIU for your macro. In the “Store Macro In” window select “This Workbook.” This will contain the macro to the data file allowing it to move with this Excel workbook. The dialog box asks if you want to assign a control key to the macro – enter the character “m” as a lower case letter without striking any other key. Click on the OK and Excel will commence recording the macro. Page 52 Solving Accounting Principles Problems Using Excel for Windows Tip: To see if a control key is assigned a function in Excel, click into Excel and try the key sequence. If Excel gives you a dull thud response, the key command was not recognized, or it is unavailable at the moment. Once Excel starts recording the macro, everything you do will be recorded. Click into cell A2 then click the “Bold” icon, the “Italics” icon, and the “Underline” icon on the tool bar. Then click on the stop recording button on the macro dialog box the appeared on the screen. This is a square button as shown. When you click the “Stop Recording” button your macro is stored and available for use. If you do not have a stop button showing you can utilize the path Tools > Macro > Stop Recording. Macro Dialog Box Macro Stop Button Now click into cell A3 and run the macro through the keystrokes Ctrl-m or through the path Tools > Macro > Macros and click on the “BIU” select and click run. The error with the macro is that it goes to cell A2 and puts bold, italics, and underline on cell A2. This is because the first thing you told the macro to do was go to cell A2. You could have avoided this by being in the target cell at the time you recorded it – this was intentional. Now we will edit the macro to preclude it from moving to cell A2 each time. Follow the path Tools > Macro > Macros, single click BIU, then select “Edit” from the button choices. The macro tools and the Microsoft Visual Basic window will open and you will see the macro command strings. One of the command strings reads “Range("A2").Select.” Highlight and delete this one line then close the window. You have now edited (corrected) the macro. Click into cell A3 and invoke the macro with the keystrokes Ctrl-m and cell A3 should become bold, italics, and underlined. Macros are powerful tools. They can be absolutes – go to cell A2 – as we originally recorded BIU. Or they can be focused on the target cell or the active cell as BIU was edited to. Macros will format worksheets, enter data, run spell check, and almost any other repetitive task you have. You can save the macros you build to the workbook or in a personal macro notebook. If the macro is saved to the personal macro notebook, it may not be available on the workbook if the workbook is distributed or transmitted. Clue: When a workbook contains a macro, there may be a warning about its presence. Unless you know the nature and intent of the macro it may be best to decline the macro’s enablement. You may be able to review the macro’s properties while disabled through the edit function on the macro dialog box. Tip: You can assign a macro to an object. This is addressed elsewhere in “Macros And Objects.” Hint: Macros are usually relational. To ensure that the macro goes where you want it to as the first step try Ctrl-G and select “A1” as the destination. This will ensure that the macro will go to the appropriate cells since the starting point is always cell A1. Macros On Objects Once an object is drawn in Excel it is possible to attach a macro to it. By doing this, clicking on the object will activate and run the macro. The “Macros On Objects” worksheet in the “Macros On Objects” data file has a pencil as an object on it, and a macro is attached to it. Place your mouse cursor over the pencil and the cursor will become a hand. At that point click the left mouse button to activate the macro. You can reset the macro by running another macro which is attached to the “Reset pencil macro” text box. To accomplish this task, click into the “Macros On Objects Playground” worksheet. As an exercise, draw or place an object on the worksheet. Then record a macro by following the path Tools > Macros > Chapter 8, Page 53 Record Macro as discussed in the “Macros” section. Assign a name such as “ObjectText” to the macro. No shortcut key is necessary. Once the macro starts recording, utilize the Ctrl – G sequence of keystrokes and instruct the “Go To” dialog box to go to cell A1. Now click into a cell below your object and type in a short text string such as “This object works just fine.” and press and release the “Enter” key to complete the text entry. Click back into the chosen cell to make it the active cell. Then highlight the cells about 5 columns across and two or three rows down. While still within the macro, follow the path Format > Cells. Within the “Format Cells” dialog box, on the “Font” tab, select “Script” for a font, then adjust its points or size to “18”. On the “Alignment” tab, select “Merge Cells”, “Wrap Text” and then, set both horizontal and vertical placement factors to “Center” and click “OK.” Now click on the macro recorder stop button or follow the path Tools > Macro > Stop Recording. The macro is recorded. You can edit it as explained in the “Macros” section if needed. Right click on the object you built or inserted. From the options select “Assign Macros” and the select your macro from the list shown and click “OK.” Then click on your object to see if it works. The pencil has a macro assigned to it. You can right click the pencil and see which macro is assigned. You can access the macros through the path Tools > Macro > Macros. By selecting the macro assigned to the object, you can read it through the “Edit” button. You can click on the pencil to run its macro. To reset the pencil macro, click on the pencil’s macro reset button. Lower in the “Macros On Objects” worksheet there is a “test” which shows to powers of macros and the convenience of objects as buttons. There is a reset button for the test. Hint: Macros are usually relational. To ensure that the macro goes where you want it to as the first step try Ctrl-G and select “A1” as the destination. This will ensure that the macro will go to the appropriate cells since the starting point is always cell A1. Chapter 9 PRINTING ISSUES Chapter Outline Page Setup Page Break Preview Print Area Print Preview Printing Page Setup The printed presentation of Excel data is dependent on page setup and page break positioning. The access to the Page Setup dialog box can be accomplished through the path File > Page Setup. The dialog box the pops up appears here. There are four tabs on this dialog box. The “Page” tab controls portrait or landscape view and the important function of “Fit to.” With the “Fit to” capability you determine the pages that your Excel worksheet will print by width and height. If you print a file only to find that one column of eight is contained on a second sheet, consider telling Excel to print to 1 sheet wide. You can always over guess the pages needed without penalty. For example, if you have four columns of 1” wide each and 40 rows approximating 0.25” in height each, you can instruct Excel to fit it to 10 pages wide and 20 pages tall and Excel will print the document on one single sheet since it is approximately 4 inches by 10 inches in size. If you set the “Adjust to” selection to a value such as 500%, Excel will obey that value and print the results. Taking your 4 columns and 50 rows and setting “Adjust to” to 500%, Excel will consume many sheets obeying your orders as the printed columns will be approximately 5” wide each (1” column X 500%) resulting in total columns width of approximately 20 inches in six and half inch width print area paper. The 40 rows are approximately one quarter of an inch each by default. When Excel multiples this value by 500% as instructed the sheet becomes approximately (0.25 inches X 40 rows X 500%) 50” long on a default print area approximately 9” long. The length of approximately 50” will be printed in a print area approximately 9” long resulting in about 6 sheets in length. This single print out will consume approximately 24 sheets of paper – as instructed. Excel will obey the last choice selected between the “Adjust to” and “Fit to” options. Only one can be invoked at a time. “Print Preview” and then “Page Break Preview” can be accessed from this tab as well. Print preview can be accessed through the “Print Preview” icon. The “Margins” tab controls the upper, lower, left and right margins as well as placement upon the page. By selecting the center horizontally and vertically, the data can be centered on the page for a very nice presentation, if appropriate. Seldom is financial data such as journals and ledgers centered both Section 2, Page 54 Chapter 9, Page 55 vertically and horizontally. The margins of the Excel templates has been defined as 1” top, bottom, left, and right. If your printer cannot handle this margin value you can adjust it through this tab. The “Header/Footer” tab allows you to place header and/or footer information on each printed sheet. This capability is on each of the templates. The template data shows the file name, the worksheet name, the page of pages, the date, and the time in each footer. This has been included so that the compilation of multiple papers is easier. Click onto the “Header/Footer” tab and click on “Custom Footer.” The data contained within the footer will be shown. Notice that these are codes calling for data during the printing process making them dynamic or live. Holding your cursor over any of the numerous icons for several moments will reveal that icon’s function. There are numerous default headers and footers that can be utilized through the drop-down menus. The “Sheet” tab contains numerous data bits that are very useful and versatile. The first bit of data is the established print area of the worksheet in the top data window. With the demo file this is B2:K176, or in English, the print area starts at cell B2 and contains all of the cells through row 176 as well as all of the columns from B through column K. This tab also controls whether a specific row or set of rows will appear at the top of every sheet printed and if a specific column or set of columns will be printed at the left of each page printed. Assume that you are printing inventory reports running 125 pages. They contain part number, location code, description, quantity on hand, quantity on order, quantity in WIP, and value. By placing the titles row on each sheet through this function, you will retain column identity without building it into the worksheet. This feature is utilized in the Excel templates provided to put your identity and course data on each printed page if more than one page is used for the printed results to retain identity of the student and exercise or problem. This transposes to columns to identify rows as well. You may invoke columns and rows at the same time. Also on this tab, you can select the order in which the pages will print. If you are printing data that is presented by rows and its taking four sheets wide by four sheets high, you may want to print the across and then down so that the first four sheets address the rows contained in the early rows before introducing the second set of rows presented on pages five through eight. Gridlines on the printed report, not the viewing screen are turned on or off through this tab. Many people do not like grid lines on their data. The templates have them turned off but this tab can change that for the individual template. This does not control the grid line presentation on the display. The grid line on the screen is controlled by the path Tools > Options > View. If viewed through that path, they will not print. Neither of these options affects cells that have borders placed on them. Page Break Preview “Page Break Preview” is found through the path View > Page Break Preview from the menu bar or from a button on the Print Preview screen. The “Print Preview” icon is the white sheet with the folded corner and magnifying glass on the tool bar shown here. “Page Break Preview” will show how the pages are going to print with the current printer and page settings. The page breaks can be moved and repositioned from within this view. Excel Print Hint: When resetting page breaks start in the upper left corner and work Preview down and to the right if your print order as shown in the Page Setup is down Icon and right. If the print order is across and then down, reset page breaks in that order. Each page setting affects the following pages so setting page 10 to an ideal presentation and then changing page 9 may cause Excel to reset your work and preferences on page 10. Starting at the bottom may also result in too much on page 1 or too little. To close “Page Break Preview” follow the path View > Normal from the menu bar. “Page Break Preview” is dependent upon page setup and printer functions. Most printers, but not all, can print to ½” of each edge of an 8 ½ X 11” sheet of paper. There are printers that require larger than 1” margins. Page 56 Solving Accounting Principles Problems Using Excel for Windows Tip: The Excel templates for the exercises and problems provided on the computer media have been designed to provide the largest application usage so the standards of 1” margins are seldom violated. Print Area As addressed previously, Excel can and will consume as much paper as instructed or needed (if left to default setting) to print a worksheet. Sometimes this is not necessary. For example, you have constructed a large worksheet computing the data but the actual data presentation is only one physical page. In construction of the worksheet you have identified various areas to clarify your calculations and work. Contained in these areas you have extensive detail and support material that are not required for the printed presentation. If not provided with parameters for printing, Excel will print in an 8 ½ X 11” format for standard bond paper a range from the upper left cell, usually cell A1 in a rectangular or square profile through the further right and lowest cells with data entered. So, if you have data in cell A1, cell Z1, and cell A55, your print area may default to A1 through Z55. Some versions of Excel may trim the last page or two from the printout if there is nothing on that page, to the right of that page, or below that page. It is safer not to assume what Excel will and will not print by defining your print area. In your large worksheet example, all of your supporting calculations are going to be printed and the printing will normally be in a vertical then left to right format in portrait mode. Redefining the print area is easily handled within Excel. The Demo File has a predefined print area to preclude this and can be used as an example. On the Demo File worksheet of the Demo File, you will notice that the worksheet is outlined by a gray border. This border has no function with the print area, it is merely provided by the author as a visual reference of the print area. Anything you enter inside the gray border will be printed on the sheet. Any item on or outside the border will not be printed on the worksheet printout. To examine the worksheet’s predefined printed area follow the path File > Page Setup > Sheet and read the values contained in the upper window of the “Print” area tab. This value should be “B2:K176” or from cell B2 down and right through and including cell K176. If you know the print area you want, you can enter it directly into the print area window on the “Sheet” tab of “Page Setup” by using cell references and separating them by a colon. However, you can also define the print area from the worksheet itself. To demonstrate this, click into and make cell A1 of the demo file the active cell. When this is done the cell will have a heavy border around it. Now, press and hold the left mouse button down, while holding the left mouse button down, sweep the mouse cursor down and to the right to highlight all of the cells down to and over to the right to include cell L20. Once the cells are highlighted, release the mouse button and do not click back onto the worksheet or your highlight will be lost. Follow the path File > Print Area > Set Print Area and click on the “Set Print Area” option. Now you have defined the print area without entering data or keystrokes. Utilize the “Print Preview” button or icon, discussed elsewhere in this book, to see the redefined print area. Most of the Demo File is not available in the print version since your print data selection is from cell A1 through L20. Also, the gray borders are part of the print area. Close the Print Preview screen through the “Close” button and reset the print area by following the path File > Page Setup then selecting the “Sheet” tab and entering “B2:K176” into the “Print Area” window overwriting the “A1:L20” value and then clicking OK.. All of the Excel templates have the print area defined for reasonable presentation of the exercises and problems. You can also print areas that are not adjacent to each other. For example, assume that you want to print the range from B2 through L50, and the range from R55 through Q90. Open up the “Sheet” tab of the “Page Setup” dialog box, click into the “Set Print Area” window, click into cell B2 on the worksheet and highlight down and right through cell L50, now type in a “,” (Comma) to define and instruct Excel that you have identified the first area and are now identifying a second print area, then click into cell R55 and highlight through Q90 and click “OK.” Excel will identify these two ranges in the window separated by a comma as “B2:L50,R55:Q90”. You can define multiple ranges this way. Remember that after highlighting and identifying the first area, you must insert a comma. You can use the arrow keys and / or the scroll bars to move to the second, third, and following areas. Chapter 9, Page 57 You cannot define one page to be printed portrait and another landscape within the same worksheet of Excel. Your solution may be to have one worksheet inside the workbook setup with landscape and the second worksheet setup portrait as the setup is to the worksheet, not workbook. Then simply have “Look to” formulas on one worksheet referencing the source worksheet so data does not have to be entered more than once. When this is accomplished, you can define how each worksheet will be printed without affecting the defaults and settings of the other worksheet. Once the first worksheet is printed, select the second worksheet and print it. Or, once the first page is printed, highlight the data for the second page and set your preferences through the “Print” dialog box by following the path File > Print and set the values and presentation techniques you desire – including landscape for the second print run. It is best if you always preview the worksheet before printing to avoid paper consumption. Tip: Once printed, review the document to insure that it is correct and complete. Just because a computer printed it does not establish or verify it’s accuracy. Print Preview By clicking on the “Print Preview” icon on the tool bar, a white sheet of page with the upper right corner turned over and a magnifying glass overlaying it, you will access the print preview of your document. If there is more than one page, there will be a scroll bar on the right side of the screen. If the print preview presentation fills the page you are probably in the “Zoom” mode. Simply click on the sheet or on “Zoom” on the menu bar and you should be presented with an overall presentation of your document. If there is more than one page or it is larger than the screen you should have scroll bars to maneuver through the document. Clue: In order for “Print Preview” to work, your system must have a printer installed in the operating system. The printer need not be available but Windows, Word, and Excel all provide “Print Preview” based in the defaults and settings of the currently selected printer. From this screen you will see how the document will appear in its printed mode. This includes features such as headers and footers as well as margins. You can also adjust the page while in this presentation. By clicking on the “Margins” button at the top of the menu bar you will be given a presentation of current placement of the margins and columns. You can adjust the margins and column widths at this point by placing the mouse cursor on the margin or column marker and getting it to change to a double headed arrow and then pressing and holding the left mouse button down. While the mouse button is being held down, drag the cursor left, right, up, or down, as desired. The margins at the outer areas of the sheet at the top and bottom of the sheet are those of the page and are defaulted to the values found under the path File > Page Setup > Margins. These margins are fully adjustable but will usually be at or outside of the file margins. If the worksheet data is printed on top of the header and footer data, utilize this presentation to move the data margins off the header or footer margins. The area between the page margins and the file margins is where the headers and footers are placed at the top and bottom. Print preview icon Page 58 Solving Accounting Principles Problems Using Excel for Windows There are numerous changes you can make within print preview besides margins and column width. By clicking on the “Page Setup” button or icon you can adjust almost every item accessible through File > Page Setup. Some are grayed or faded out as they are not available at this time but selection the Page Setup dialog box through the path File > Page Setup will usually present them as these particular items must be set from the normal view mode or from the page break view mode. Remember that under the “Margins” tab within “Page Setup” you can center your page horizontally, vertically, or both if you desire. Printing Excel will print from several different commands and paths. The quickest while using the templates is the “Print” icon on the tool bar. This symbol is the “Printer.” By clicking on this icon, Excel and Microsoft Windows will print the currently active document utilizing the document settings under “Page Setup” through the default printer as established by the Windows Control Panel settings for printers. When printing other worksheets this command may result in excessive paper being used in the printing process. The difference is that the Excel templates accompanying this book already have predefined layouts and paper breaks. For original worksheets, the layout and print area should be verified and adjusted as described in the “Print Area” and “Print Preview” sections elsewhere in this book. If you do not desire the Windows default printer you can follow the path File > Print and a dialog box, shown here, will appear: “Quick Print” icon to Windows default printer Installed printer selections Specific pages or all pages Number of copies From this dialog box, you can select printers installed on the desktop system, on the network, or fax drivers that may be installed. If your worksheet consists of multiple pages, you can select any relevant range of pages. For example, if your worksheet consists of five pages, you can select “All” to get pages 1 through 5, select from pages from 2 through 4, select from 3 to 3, or elect to print only the highlighted or selected area through the “Selection” within the “Print what” box below the Print range box. You can also determine the number of copies desired and whether those copies are collated or not. The default is to collate multiple prints. This may not be desired if presenting a large amount of information to a group as they may become involved in scanning the depths of the document during your presentation of page 1. By not collating the documents, you can pass single sheets out, containing exposure without having to resort the document. After all selections have been made, printing is initiated by clicking “OK.” Excel and Windows will normally print the document to the selected printer without further action. ...
View Full Document

Ask a homework question - tutors are online