To prevent accidental changes to your data, you can lock worksheet cells. When you lock selected cells, you cannot make changes to them until you unlock them. When you lock cells, users can unlock the data and make changes unless you add password protection to the worksheet. For security or confidentiality reasons, you might want to hide formulas from view. If so, you can hide or unhide them using the Protection tab in the Format Cells dialog box.
To preserve all your hard work—particularly if others use your files—protect it with a password. You can protect a sheet or an entire document. In each case, you’ll be asked to supply a password, and then enter it again when you want to work on the file. Passwords are case sensitive, so be sure to supply your password as it was first entered. If you forget a password, there is no way to open the file, so it’s very important to remember or write down your password(s). Keep your password in a safe place. Avoid obvious passwords such as your name, your company, or your favorite pet.
Click the Tools menu, point to Protection, and then click Protect Workbook.
Select the check boxes for the options you want protected in the sheet.
-
Structure. Select the check box to prevent users from viewing, copying, moving, or inserting worksheets. It also prevents users from recording new macros, displaying data from PivotTable reports, using analysis tools, or creating scenario summary reports.
-
Windows. Select the check box to prevent users from moving, resizing, or closing windows.
Type a password.
Click OK.
Retype the password.
Click OK.
When you’re working with others in a networked environment, you may want to share workbooks you have created. You may also want to share the responsibilities of entering and maintaining data. Sharing means users can add columns and rows, enter data, and change formatting, while allowing you to review their changes. When sharing is enabled, “[Shared]” appears in the title bar of the shared workbook. This type of work arrangement is particularly effective in team situations where multiple users have joint responsibility for data within a single workbook. In cases where multiple users modify the same cells, Office can keep track of changes, and you can accept or reject them at a later date.
Open the workbook you want to share.
Click the Tools menu, and then click Share Workbook.
Click the Editing tab.
Select the Allow changes by more than one user at the same time check box.
Click OK.
Click OK again to save your workbook.
Did You Know?
You can set file options to prompt to open as read-only. To prevent accidental changes to a document, you can display an alert requesting (not requiring) the user open the file as read-only. A read-only file can be read or copied. If the user makes changes to the file, the modifications can only be saved with a new name.
Open the workbook you want to share.
Click the Tools menu, and then click Share Workbook.
Click the Advanced tab.
To indicate how long to keep changes, select one of the Track changes options, and then set the number of days, if necessary.
To indicate when changes should be saved, select one of the Update changes options, and then set a time internal, if necessary.
To resolve conflicting changes, select one of the Conflicting changes between users options.
Select one or both of the Include in personal view check boxes.
Click OK.
Any cell on a worksheet can contain a comment—information you might want to share with co-workers or include as a reminder to yourself without making it a part of the worksheet. (Think of a comment as a nonprinting sticky note attached to an individual cell.) A cell containing a comment displays a red triangle in the upper-right corner of the cell. By default, comments are hidden and are displayed only when the mouse pointer is held over a cell with a red triangle.
Position the mouse pointer over a cell with a red triangle to read its comment.
Move the mouse pointer off the cell to hide the comment.
To show all comments, click the View menu, and then click Comment.
To navigate and work with comments using buttons on a toolbar, click the View menu, point to Toolbar, and then click Reviewing to display it.
You can edit, delete, and even format cell comments just as you do other text on a worksheet. If you are working with others online, they may want to delete a comment after reading it. You might want to format certain comments to add emphasis. You can use formatting buttons—such as Bold, Italic, Underline, Font Style, Font Color, or Font Size—on the Font panel or Formatting toolbar. When you no longer need a comment, you can quickly delete it.
Select the cell that you want to remove.
Click the Edit Comment button on the Reviewing toolbar.
-
You can also Control-click the cell containing the comment, and then click Edit Comment.
Make your changes using common editing tools, such as the Delete key, as well as the Font panel and Formatting toolbar buttons.
Press Esc twice to close the comment box.
As you build and fine-tune a workbook—particularly if you are sharing workbooks with co-workers—you can keep track of all the changes that are made at each stage in the process. The Track Changes feature makes it easy to see who made what changes and when, and to accept or reject each change, even if you are the only user of a worksheet. When you or another user applies the Track Changes command to a workbook, the message “[Shared]” appears in the title bar of the workbook to alert you that this feature is active. To take full advantage of this feature, turn it on the first time you or a co-worker edits a workbook. Then, when it’s time to review the workbook, all the changes will be recorded. You can review tracked changes in a workbook at any point. Cells containing changes are surrounded by a blue border, and the changes made can be viewed instantly by moving your mouse pointer over any outlined cell. When you’re ready to finalize the workbook, you can review each change and either accept or reject it.
Click the Tools menu, point to Track Changes, and then click Highlight Changes.
Select the Track changes while editing check box.
Select the When, Who, or Where check box. Click an associated list arrow, and then select the option you want.
Select the Highlight changes on screen check box.
To list changes on a new worksheet, select the List changes on a new sheet check box.
Click OK.
Make changes in worksheet cells.
Column and row indicators for changed cells appear in red. The cell containing the changes has a blue outline.
To view tracked changes, position the mouse pointer over an edited cell.
Click the Tools menu, point to Track Changes, and then click Accept or Reject Changes. If necessary, click OK in the message box.
If you want, change tracking, and then click OK to begin reviewing changes.
If necessary, scroll to review all the changes, and then click one of the following buttons:
-
Click Accept to make the selected change to the worksheet.
-
Click Reject to remove the selected change from the worksheet.
-
Click Accept All to make all of the changes to the worksheet after you have reviewed them.
-
Click Reject All to remove all of the changes to the worksheet after you have reviewed them.
Click Close.
See Also
See “Protecting a Worksheets and Workbooks” on page 322 for information on protecting a shared workbook.
For one reason or another, multiple users may maintain identical workbooks. At some point, you’ll want to integrate their data into one master workbook, known as the template. First, though, you need to compare the data to identify the differences between the worksheets. Excel can electronically combine the entries, which ensures the integrity of your data. When merging workbooks, all the workbooks must be identical to the file into which the data is being merged. To distribute copies of a workbook and merge the changes into the original, the workbooks must have sharing, change tracking, and change history turned on and use a different file names.
Open the shared workbook that you want to merge.
Click the Tools menu, and then click Merge Workbooks.
Click OK to save the workbook, if necessary.
Select the files you want merged with the active file.
To select more than one workbook to merge, press and hold Control, and then click other files.
Click OK.
Click the Save button.
Click the Tools menu, point to Track Changes, and then click Accept or Reject Changes.
Select the When check box, click the list arrow, and then click Not Yet Reviewed.
Clear the Who and Where check boxes.
Click OK.
Click the buttons to accept or reject changes, and then click Close.
Excel’s powerful functions make it easy to create powerful formulas, such as calculating payments over time. Sometimes, however, being able to make these calculations is only half the battle. Your formula might tell you that a monthly payment amount is $2,000, while you might only be able to manage a $1,750 payment. Goal Seek enables you to work backwards to a desired result, or goal, by adjusting the input values.
Click any cell within the list range.
Click the Tools menu, and then click Goal Seek.
Click the Set Cell box, and then type the cell address you want to change.
You can also click the Collapse Dialog button, use your mouse to select the cells, and then click the Expand Dialog button.
Click the To Value box, and then type the result value.
Click the By Changing Cell box, and then type the cell address you want Excel to change.
You can also click the Collapse Dialog button, use your mouse to select the cells, and then click the Expand Dialog button.
Click OK.
Review the goal seek status, and then click OK.
Because some worksheet data is constantly evolving, the ability to create multiple scenarios lets you speculate on a variety of outcomes. For example, the marketing department might want to see how its budget would be affected if sales decreased by 25 percent. Although it’s easy enough to plug in different numbers in formulas, Excel allows you to save these values and then recall them at a later time. The ability to create, save, and modify scenarios means a business will be better prepared for different outcomes to avoid economic surprises.
Click the Tools menu, and then click Scenarios.
Click Add.
Type a name that identifies the scenario.
Type the cells you want to modify in the scenario, or click the Collapse Dialog button, use your mouse to select the cells, and then click the Expand Dialog button.
If you want, type a comment.
Click OK.
Type values for each of the displayed changing cells.
Click OK.
Click Close.
Did You Know?
You can show a scenario.Click the Tools menu, click Scenarios, select the scenario you want to see, click Show, and then click Close.
You can create a scenario summary report.Click the Tools menu, click Scenarios, click Summary, click the Scenario Summary option, and then click OK. A scenario summary worksheet tab appears with the report.
In cases where you don’t need the data you are using from another source to be automatically updated if the source data changes, the most expedient way to get the data is to copy and paste it. In cases where you want to copy data from one program to another, you can convert the data to a format that the other program accepts.
Select the cell or range that you want to copy.
Click the Copy button on the Standard toolbar.
Open the destination file, or click the program’s taskbar button if the program is already open.
Click to indicate where you want the data to be copied.
Click the Paste button on the Standard toolbar.
Click the Paste Options button, and then click the option you want.
When you want to summarize information in a lengthy list using complex criteria, use the PivotTable to simplify your task. Without the PivotTable, you would have to manually count or create a formula to calculate which records met certain criteria, and then create a table to display that information. Once you determine what fields and criteria you want to use to summarize the data and how you want the resulting table to look, the Pivot Table Wizard does the rest. You can quickly update a PivotTable report using the PivotTable toolbar, which appears whenever a PivotTable is active. When you do want to add new data, Excel makes it easy by allowing you to drag data fields to and from a PivotTable. You can also change field settings to format a number or show the data in a different form. The field settings include functions such as Sum, Count, Average, Max, and Min.
Click any cell within the list range.
Click the Data menu, and then click PivotTable Report.
If using the list range, click the Microsoft Office Excel List or Database option.
Click Next to continue.
If the range does not include the correct data, click the Collapse Dialog button. Drag the pointer over the list range, including the field names, to select a new range, and then click the Expand Dialog button.
Click Next to continue.
Click the New sheet option or the Existing sheet option, and then specify the location of the existing sheet.
Click Finish.
Drag fields from the Field List to areas on the PivotTable Report.
Make any necessary change(s) in the worksheet where your list range resides.
If necessary, select a different worksheet, and then click any cell in the PivotTable Report.
Click the Refresh Data button on the PivotTable toolbar, or click the Data menu, and then click Refresh Data.
To add or remove a field in a PivotTable, position the pointer over the field that you want to add to or remove from the PivotTable, and then drag the field on the PivotTable to add the field or drag it off the PivotTable to remove the field.
In some cases, you’ll want to consolidate data from different worksheets or workbooks into one workbook, rather than simply linking the source data. For instance, if each division in your company creates a budget, you can pull together, or consolidate, the totals for each line item into one company-wide budget. If each divisional budget is laid out in the same way, with the budgeted amounts for each line item in the same cell addresses, then you can very easily consolidate the information without any retyping. If data in individual workbooks change, the consolidated worksheet or workbook will always be correct.
Open all the workbooks that contain the data you want to consolidate.
Open or create the workbook that will contain the consolidated data.
Select the destination range.
Click the Data menu, and then click Consolidate.
Click the Function list arrow, and then select the function you want to use to consolidate the data.
Type the location of the data to be consolidated, or click the Collapse Dialog button, and then select the cells to be consolidated.
Did You Know?
You can include all labels. Make sure you select enough cells to accommodate any labels that might be included in the data you are consolidating.
Click the Expand Dialog button.
Click Add to add the reference to the list of consolidated ranges.
Repeat steps 6 through 8 until you have listed all references to consolidate.
Select the Create links to source data check box.
Click OK.
Did You Know?
You can consolidate worksheets even if they are not laid out identically. If the worksheets you want to consolidate aren’t laid out with exactly the same cell addresses, but they do contain identical types of information, select the Top Row and Left Column check boxes in the Consolidate dialog box so that Excel uses labels to match up the correct data.
You can arrange multiple documents.Use the Window menu to move between documents or to arrange them so they are visible at the same time.
A link can be as simple as a reference to a cell on another worksheet, or it can be part of a formula. You can link cells between sheets within one workbook or between different workbooks. Cell data to be linked is called the source data. The cell or range linked to the source data is called the destination cell or destination range. If you no longer want linked data to be updated, you can easily break a link. Create links instead of making multiple identical entries; it saves time and ensures your entries are correct.
Select the cell or range that contains the source data.
Click the Copy button on the Standard toolbar.
Click the sheet tab where you want to link the data.
Select the destination cell or destination range.
Click the Paste button on the Standard toolbar.
Click the Paste Options button, and then click Link Cells.
Click the cell containing the linked formula you want to break.
Click the Copy button on the Standard toolbar.
Click the Edit menu, and then click Paste Special.
Click the Values option.
Click OK.
Did You Know?
You can include a link in a formula and treat the linked cell as one argument in a larger calculation. Enter the formula on the formula bar, and then select a cell in the worksheet or workbook you want to link. A cell address reference to a worksheet is =tab name!cell address (=Orders!A6). A cell reference to a workbook is =’[workbook name.xls]tab name’!cell address (=’[Product Orders.xls]Orders’!A6).
You can arrange worksheet windows to make linking easier. To arrange open windows, click the Window menu, click Arrange, and then click the option for the window arrangement you want.
If you have data in a database, you can use functions in Excel to retrieve data from a table in a database. To retrieve the data, you can select or create a data source, build a query to create a link to the data, and optionally, create a filter to limit the information. When you select or create a data source, you need to identify the database type and then connect to it. To build a query, you can use the Query wizard to step you through the process, or you can manually create a query the same way you do in a database. Before you can create a query, you need to first install the required ODBC (Open Database Connectivity) driver, such as ODBC for Access. You can also retrieve data from other sources. If you use the same table in a database for data, you can define and save the data source for use later.
Click the Data menu, point to Get External Data, and then click New Database Query.
-
If an alert appears indicating that no ODBC drivers are installed on your computer, click Go to page to display the Mactopia Web site, where you can access links to install the data source drivers you want.
Click the User DSN, System DSN, or File DSN tab.
Click Add.
Select a driver for the data source that you want to set up.
Click Finish.
Follow the wizard to create a new data source; steps vary depending on the data source.
-
Name, DSN Type, Description
-
Click Choose, navigate to the folder with the database you want to use, select it, and then click Choose.
Click Continue, and then click Done.
Click Cancel or select a data source, and then click OK to create a query.
Click the Data menu, point to Get External Data, and then click New Database Query.
Click the User DSN, System DSN, or File DSN tab.
Click the name of the data sources you want to use.
-
To select another database, select the source, click Configure, and then follow the instructions to change it.
Click OK.
Create the query using techniques from the data source.
In Microsoft Query for Access, for example, drag tables into Query View, select the fields from which you want to get data, and then specify the sort and filter criteria you want. Click Query View to display the data.
Click Return Data to continue back in Excel.
Click the Existing sheet option, and then specify a cell location, or click the New sheet option. If you want to create a PivotTable, click the PivotTable report option.
Click OK.
The data from the data source appears in your worksheet.
If you have data on the Web, you can retrieve it by using a query. You can create your own (see previous page) or use/modify one provided by Office 2008. The queries are located in the following folder: Microsoft Office 2008:Office:Queries. The sample queries include: MSN MoneyCentral Currencies, MSN MoneyCentral Major Indices, and MSN MoneyCentral Stock Quotes.
Open the workbook in which you want to insert text data.
Click the Data menu, point to Get External Data, and then click Run Saved Query.
Click the Where drop-down, and then select the folder where the query is located.
Select the query file you want.
Click Get Data.
Click the Existing sheet option, and then specify a cell location, or click the New sheet option. If you want to create a PivotTable, click the PivotTable report option.
Click OK to continue.
Enter a parameter value. To enter multiple values, separate them with commas.
Click OK.
Did You Know?
You can create refreshable Web queries. If you want to analyze Web data in a worksheet, you can use the Copy and Paste commands to bring the data from a Web page into the worksheet. The Paste Options button allows you to keep the data as it is or make it refreshable on the Web. As the data changes on the Web, you can use the Refresh button to quickly update it.
If you have data in a FileMaker Pro (version 5.0 - 9.0) database, you can import it directly into Excel. In addition to the database file, you must also have the appropriate version of FileMaker Pro installed on your computer along with Excel. Excel uses the FileMaker Pro Import Wizard to step you through the process of importing your data. After you complete the import, you will probably need to resort your data.
Open the workbook in which you want to insert text data.
Click the Data menu, point to Get External Data, and then click Import from FileMaker Pro.
-
You can also click the File menu, click Import, click the FileMaker Pro database option, and then click Import.
Navigate to and select the database file you want to import.
Click Choose.
FileMaker Pro starts, the database opens, and the FileMaker Pro Import Wizard appears.
In Step 1, click the Layouts or Tables drop-down, select a layout or table, select each field in the Available fields list, and then click Add to import these fields.
To change the import order, click the field, and then click the up or down button.
Click Next to continue.
In Step 2, specify the criteria you want to filter (remove) out data from the FileMaker Pro database.
Click Finish.
Click the Existing sheet option, and then specify a cell location, or click the New sheet option.
Click OK.
If you have data in a text file, you can either open the file using the Open command on the Office menu, or you can import the text file as an external data range using the From Text button on the Data tab. There are two commonly used text file formats to store data that you can import in Excel: Tab delimited text (.txt) and Comma separated values text (.csv). When you open a .txt file, Excel starts the Import Text Wizard. When you open a .csv file, Excel opens the file using current default data format settings.
Open the workbook in which you want to insert text data.
Click the Data menu, point to Get External Data, and then click Import Text File.
-
You can also click the File menu, click Import, click the Text file or CSV file option, and then click Import.
Click the Enable drop-down, and then click Text Files or CSV Files.
Click the Where drop-down, and then select the folder where the text file is located.
Click the text file you want to import.
Click Get Data.
If the file is a text file (.txt), Excel starts the Import Text Wizard. Step through the wizard (3 steps), and then click Finish.
Click the Existing worksheet option, and then specify a cell location, or click the New worksheet option.
Click OK.