Using multiple worksheets enables you to place summarized data first. To select an adjacent group, click the sheet tab of the first worksheet in the group, press and hold the Shift key, click the sheet tab of the last worksheet in the group, and then release the Shift key. To select a nonadjacent group, click the sheet tab of one worksheet in the group, press and hold the Ctrl key, click the sheet tabs of the remaining worksheets in the group, and then release the Ctrl key. To ungroup worksheets, click the sheet tab of a worksheet that is not part of the group. If a worksheet group includes all of the sheets in a workbook, click any of the sheet tabs to ungroup the worksheets. The text "[Group]" appears in the title bar to remind you that a worksheet group is selected in the workbook. When two or more worksheets have identical row and column layouts, as the quarterly worksheets in the Jackson workbook do, you can enter formulas with 3-D references to summarize those worksheets in another worksheet. The 3-D reference specifies not only the range of rows and columns but also the range of worksheet names in which the cells appear. WorksheetRange is the range of worksheets you want to reference and is entered as FirstSheetName:LastSheetName with a colon separating the first and last worksheets in the worksheet range. If the sheet names include spaces, they are surrounded by ' ' One benefit of summarizing data using formulas with 3-D references, like any other formula, is that if you change the value in one worksheet, the results of formulas that reference that cell reflect the change. CellRange is the same cell or range in each of those worksheets that you want to reference. An exclamation mark (!) separates the worksheet range from the cell or range. The results of a formula using a 3-D reference reflect the current worksheets in the worksheet range. If you move a worksheet outside the referenced worksheet range or remove a worksheet from the workbook, the formula results will change. 3-D references are often used in formulas that contain Excel functions, including SUM, AVERAGE, COUNT, MAX, and MIN. - Select the cell where you want to enter the formula. If both the source and destination files are open when you make a change, the destination file is updated automatically. If the destination file is closed when you make a change in a source file, you choose whether to update the link to display the current values or continue to display the older values from the destination file when you open the destination file. When both the destination and source workbooks are open, any changes you make in a source workbook automatically appear in the destination workbook. To update the destination workbook with the current data, you must specify that you want the update to occur. As part of the Excel security system that attempts to protect against malicious software, links
to other workbooks are not updated without your permission. The old values in the destination workbook are displayed and the links to the source files have an unknown status. The Edit Links dialog box lists all of the files to which the destination workbook is linked so that you can update, change, open, or remove the links Source—indicates the file to which the link points. The Urban Centers Audited 2018 workbook contains three links pointing to the Flint.xlsx, Jackson.xlsx, and Petosky.xlsx workbooks. Type—identifies the type of each source file. In this case, the type is an Excel worksheet, but it could also be a Word document, a PowerPoint presentation, or some other type of file. Update—specifies the way values are updated from the source file. The letter A indicates the link is updated automatically when you open the workbook or when both the source and destination files are open simultaneously. The letter M indicates the link must be updated manually by the user, which is useful when you want to see the older data values before updating to the new data. To manually update the link and see the new data values, click the Update Values button. Status—shows whether Excel successfully accessed the link and updated the values from the source document (status is OK), or Excel has not attempted to update the links in this session (status is Unknown). The status of the three links in the Urban Centers Audited 2018 workbook is Unknown. A hyperlink is a link in a file, such as a workbook, to information within that file or another file. Although hyperlinks are most often found on webpages, they can also be placed in a worksheet and used to quickly jump to a specific cell or range within the active worksheet, another worksheet, or another workbook. Hyperlinks can also be used to jump to other files, such as a Word document or a PowerPoint presentation, or rental centers on the web. You can insert a hyperlink directly in a workbook file to link to information in that workbook, another workbook, or a file associated with another application on your computer, a shared file on a network, or a website. Select the text, graphic, or cell in which you want to insert the hyperlink. The Blank workbook template that you have used to create new, blank workbooks contains no text or formulas, but it includes formatting—General format applied to numbers, Calibri 11-point font, text left-aligned in cells, numbers and formula results right-aligned in cells, column widths set to 8.38 characters, one worksheet inserted in the workbook, and so forth. Some of the task-specific templates available
from the Office.com site include: - On the ribbon, click the File tab, and then click New in the navigation bar. a workbook template you create that is ready to run with the formulas for all calculations included as well as all formatting and labels. A template can use any Excel feature, including formulas and charts. To create a custom template, you build the workbook with all the necessary labels, formatting, and data, and then you save the workbook as a template. The template includes everything but the variable data. You can also create a template from a chart or chart sheet. Custom template files stored in the Templates folder are available on the New screen in Backstage view. After you have saved a workbook in a template format, you can make the template accessible to other users. - Prepare the workbook—enter values, text, and formulas as needed; apply formatting; and replace data values with zeros or blank cells. |