By default, Excel opens a new, blank workbook every time you launch the program. This is fine and dandy if you want to start a new file each time you use Excel. But if you find yourself using the same spreadsheet every time you use Excel, you can tell the program to automatically open a particular workbook for you without being prompted.

To set up a workbook to open automatically, you can store the file in the XLSTART folder or create an alternate startup folder containing only the Excel file you want to launch. The XLSTART folder is created when you install Excel on your computer, and the path to this folder varies depending on which operating system you are using. One way to find this folder path is to open the Trust Center to the Trusted Locations info. To make things easier, consider creating an alternate startup folder instead and save the workbook in the new folder. Once you have the required path and saved the file there, you can tell Excel to look for the workbook there every time you launch the program. You can also open more than one workbook at startup, if desired.

Often, the data that needs to be entered into an Excel worksheet is part of a series or pattern. In that case, you can use Excel's AutoFill feature to automate data entry.

For example, you might type the word Monday in your spreadsheet, and then use AutoFill to automatically enter the remaining days of the week. Alternatively, you might type January, and then use AutoFill to enter the remaining months of the year.

In addition to automating data entry using predefined data lists such as the ones described in the preceding paragraph, you can create your own custom data lists for use with Excel's AutoFill feature. For example, you might create a custom list that includes the names of co-workers who work on your team, or a list of products you regularly stock.

Along with enabling you to enter predefined or custom text series, AutoFill allows you to automatically populate cells with a numerical series or pattern.

A little-known organizing tip that most people never think about is formatting and naming the actual worksheet tabs. At the bottom of every worksheet, a tab marks the worksheet name and number in the stack. By default, the tabs are named Sheet1, Sheet2, and so on. The tabs themselves are very plain and nondescript. You can, however, use them to better organize your worksheet content.

For example you might color-code all the sheets related to the Sales Department in one color and all the sheets related to the Marketing Department in another. This can help you tell in a glance the purpose of each sheet in the workbook. You can assign different colors to different sheets using colors from Excel's color palette.

You can also rename sheets to better describe their content. A sheet named "Quarterly Sales" easily identifies what it contains and differentiates it from a worksheet named "Yearly Sales."

The longer your worksheet becomes, the more difficult it is to keep important cells and ranges in view as you scroll through your worksheet. You can use a Watch Window to monitor important cell data. A Watch Window displays the cell information no matter where you scroll in the worksheet.

For example, you may want to see the formula results in the cell at the very top of your worksheet while you make changes in the data referenced in the formula at the bottom of the worksheet. You can also use a Watch Window to view cells in other worksheets or in other linked workbooks.

After adding a Watch Window, you can resize the window or reposition it by dragging it elsewhere on-screen. The mini-window can also be docked, much like toolbars of previous incarnations of Excel, to the side or top of the sheet area. Just drag it to the edge of the worksheet; Excel immediately tries to dock it there in place.

You can also quickly visit the cell referenced in the Watch Window by simply double-clicking the cell reference.

You can now scroll in the worksheet and the Watch Window stays put.

Tip

More Options!

You can add and remove watched cells in the Watch Window as needed. To add more cells, click the Add Watch button in the window and follow the steps in this task to add more cells to watch. To remove cells from the window, select the cell in the list area and then click the Delete Watch button.

Remove It!

When you no longer want to watch cells, you can close the Watch Window. Simply click the window's Close button (

Excel offers several ways to protect data, but the differences between them can be a bit confusing. For optimal protection, you can protect your entire workbook file with a password which allows only authorized users access. With this scenario, you can control who opens the file or who has permission to make edits. This technique was described in Chapter 2.

You can also protect specific data within a spreadsheet. For example, if you share your workbook with a colleague, you may want to prevent changes in a cell or changes to workbook elements. You can choose to protect worksheet elements or protect the workbook structure, finding options for both on the Ribbon's Review tab.

Use the Protect Workbook feature to protect a workbook's structural elements, which include moving, deleting, hiding, or naming worksheets, adding new worksheets, or viewing hidden sheets. You can also use this feature to protect overall window structure, such as moving, resizing, or closing windows. Note that users can remove this level of workbook protection unless you assign a password.

You can use the Protect Sheet feature to prevent others from editing individual worksheet elements, such as cells, rows, columns, and formatting. Note that users can also turn off this protection feature unless you assign a password to the worksheet.

You can use the RAND() function to generate random numbers in your worksheet cells. For example, you may want to generate random lottery numbers or fill your cells with random numbers for a template or as placeholder text. Depending on how you define the variables, you can generate a number between 0 and a maximum number that you specify. For example, if you define 100 as the maximum, the function randomly generates numbers between 0 and 100.

After assigning the function to one cell in your worksheet, you can use the fill handle to populate the other cells in the sheet with more random numbers. The numbers you generate with the RAND() function take on the default numbering style for the cells. By default, Excel applies the General number format, with means that decimal numbers may appear.

To limit your random numbers to whole numbers, you can set the style to Number style and the decimal places to 0 using the Format Cells dialog box. You may want to do this before applying the function; from the Home tab, click the Number group's icon to open the Format Cells dialog box, select the Number category, and adjust the decimal places to suit your needs.

As you work with longer worksheets in Excel, it may become important to keep your column or row labels in view. The longer or wider your worksheet becomes, the more time you spend scrolling back to the top of the worksheet to see which heading is which. Excel has a freeze feature you can use to lock your row or column headings in place. You can freeze them into position so that they are always in view.

If you print out the worksheet, row and column headings appear as they normally do in their respective positions on the worksheet. You can, however, instruct Excel to print column or row headings on every printed page using the Page Setup dialog box. In the Page Setup group on the Page Layout tab, click the Page Setup icon to open the Page Setup dialog box. Click the Sheet tab, and under the Print titles section you can specify the row or column heading cell range to repeat.

You can add comments to your formulas to help explain the formula construction or purpose, or remind you to check something out about the formula. For example, you can add instructions about how to use the formula elsewhere in the worksheet.

Ordinarily, when you want to add a comment to your Excel worksheet, you use the comment text boxes. Comments can include anything from a note about a task to an explanation about the data that a cell contains. To add a comment to a formula, you use the N() function instead of comment text boxes. The N() function enables you to add notes within the formula itself without affecting how the formula works.

The N() function is one of the many hundreds of functions available in Excel. To learn more about functions, check out the Excel Help feature.

You can use the CONCATENATE function to join text from separate cells into a text string. For example, for a spreadsheet that lists the last, first, and middle names of a list of people in three separate columns, you can use the CONCATENATE function to join the names to print out or paste into another document.

When you use the CONCATENATE function, it is important to include spaces between the text strings to mimic spaces between names. In the formula, you can indicate spaces by entering actual spaces within quotes. If the combined names require other punctuation, such as a comma, use a comma within the quotes between cell references. After establishing the formula for the first name in the list, copy the formula down the rows of the worksheet to join together the remaining names in the list.

You can use this same technique to join other types of text strings in Excel, such as product names and prices to print out for a customer, or dates and locations to give to a colleague.

You can add a digital version of a hand-held calculator to the Quick Access toolbar to that you can perform your own mathematical calculations. By activating the Calculator button, you can open a Calculator window and use the number pad buttons or the numeric keypad on your keyboard to enter calculations. You may find the Calculator window handy for a variety of calculating tasks. For example, if you need to add several numbers together before entering them into a worksheet cell, you can use the Calculator window to quickly total the data.

To add the Calculator to the Quick Access toolbar, you must customize the toolbar with a little help from the Excel Options dialog box. The Calculator tool, when added, appears as a tiny calculator icon on the toolbar. As its own window, you can move it around, minimize it to the Windows taskbar, and close it when you no longer need it.

If you see an error message, you should double-check your formula to ensure that you referenced the correct cells. One way to do so is to click the Smart Tag icon that Excel displays alongside any errors it detects; doing so opens a menu of options, including options for correcting the error. For example, you can click Help on This Error to find out more about the error message.

To help you with errors that arise when dealing with larger worksheets in Excel, you can use Excel's Formula Auditing tools to examine and correct formula errors. In particular, the Error Checking feature looks through your worksheet for errors and helps you find solutions.

Auditing tools can trace the path of your formula components and check each cell reference that contributes to the formula. When tracing the relationships between cells, you can display tracer lines to find precedents (that is, cells referred to in a formula) and dependents (cells that contain formula results).

You can use Excel to create projections in a manner similar to using the program's AutoFill feature. Excel offers a few options for creating projections. One is to determine a linear trend — that is, to add a step value (the difference between the first and next values in the series) to each subsequent value. Another is to assess a growth trend, in which the starting value is multiplied by the step value rather than added to the value in order to obtain the next value in the series, with the resulting product and each subsequent product again being multiplied by the step value.

The easiest way to create a projection is to use Excel's automatic trending functionality. With it, you can simply right-click and drag to generate a projection. You can also create projections manually, entering a start value, a stop value, and the increment by which the trend should change. If your data is in chart form, you can still generate projections and even include a line in your chart to indicate the trend.

Determine a Linear Trend

• 
• 
• 
• 
• 

  A context menu appears.

• 
  

Determine a Growth Trend

• 
• 
• 
• 
• 

  A context menu appears.

• 
  

Tip

More Options!

Instead of automatically projecting linear and growth trends, you can project them manually. To do so, enter the first value in the series in a cell, and then select the cell. Next, click the Home tab and, in the Editing group, click Fill and select Series. Specify whether the series should cover columns or rows, enter the value by which the series should be increased, select Linear or Growth, select the value at which you want the series to stop, and click OK.

Did You Know?

You can use chart data to create projections, adding a trend line to your chart to represent the projection. For more information about creating charts in Excel and adding trend lines to those charts, see the Excel Help feature.

You can perform what-if speculations on your data. For example, you might do so to examine what would happen if you increased shipping fees or product prices. To perform these what-if speculations, you use Excel's Scenario Manager tool. Scenario Manager keeps track of what-if scenarios you run on a workbook, enabling you to revisit and make changes to the scenarios as needed.

It is a good practice to save a copy of the original workbook before using Scenario Manager to perform what-if scenarios on it. That way, you ensure that your data is not permanently replaced with the what-if data by accident. Alternatively, create a scenario that employs the original values; then, you can revert to that original scenario any time.

You can generate a report that summarizes what-if scenarios performed on a workbook. This report lists all inputs and results for each scenario.

To remove a scenario you no longer want, reopen the Scenario Manager dialog box, click the scenario you want to remove, and then click Delete.

Where Excel's Scenario Manager enables you to run "what-if" speculations by tweaking various variables in your spreadsheet, Goal Seek does just the opposite: It enables you to set the desired result and work backward to determine what variables need to be tweaked to attain it. For example, suppose you are trying to calculate how much you can afford to spend each month on a new car. By entering the maximum amount you are willing to spend, Goal Seek can help you work backward to determine the maximum loan amount based on such variables as interest rate, duration of loan, and so on.

You can use Goal Seek to solve single-variable equations of any kind. For example, although one of the most popular uses of Goal Seek is figuring out loan amounts and payments, as shown here, you can also use Goal Seek to help you figure out how much you need to sell to reach a sales goal (for example, to determine how many units must be sold to attain net earnings of $50,000).

Tip

More Options!

You use Goal Seek when you want to produce a specific value by adjusting one input cell that influences the value. If you require adjustments to more than one input cell, use Excel's Solver tool instead. Solver is an add-in you can use for complex problems that use multiple variables. For more information about Solver, see the next task.

Try This!

If the Goal Seek dialog box is covering cells you need to select, click the Collapse Dialog button (

Goal Seek enables you to produce a specific value by adjusting one input cell that influences the value. In contrast, Excel's Solver tool enables you to produce a value using multiple variables.

To use Solver, you define the target cell along with the cells that Solver can modify to obtain a different value in the target cell. Solver then analyzes the formulas used to establish the value in the target cell and makes changes to the cells you specify to come up with different solutions. For example, if you are opening a new car dealership and have only $1,000,000.00 to spend on new cars, you can use Solver to help you determine how many different kinds of cars you can purchase for your stock and meet that goal.

Solver is one of several add-in programs that come with Excel. In order to use Solver, you must first load the add-in. To do so, click the File tab and click Options. Click the Add-Ins entry along the left side of the Excel Options dialog box, click the Manage drop-down arrow along the bottom of the dialog box, click Excel Add-Ins, and click Go. Finally, in the Add-Ins dialog box, click the Solver Add-in check box and click OK.

You can use an Excel worksheet to build a database table to manage large lists of data. A database table is simply a collection of related records, such as a phone directory, address list, inventory, and so on. After creating a database table, you can perform a variety of analysis, sorting, and filtering techniques on the data in the database table.

A database table is composed of fields, which break the table into manageable pieces. For example, a database table containing an address book will likely include fields with labels such as Name, Address, and Phone Number. You fill in these fields to create a database record. A database record might consist of the name, address, and phone number of a single individual.

Before you create a database table in Excel, take a bit of time to plan it out, deciding what kind of data you want the database table to store and how it should be organized. Otherwise, you may later discover that you have omitted important fields and have to reorganize your database table.

If you have converted data in a spreadsheet into a database table, you can add information to that table using a data form instead of typing it directly into the cells in the worksheet. (For help converting data in Excel spreadsheets into database tables, refer to the preceding task.)

In Excel, data forms are special dialog boxes that contain all the fields in your table. For example, if your database table contains a Name field, an Address field, a City field, a State field, and a ZIP field, so too will the database table's data form dialog box. You can type the information for a database record into the dialog box rather than into the spreadsheet. (Note that you can also edit existing records from a data form dialog box.)

To access the command used to display the data form dialog box, you must first add it to the Quick Access toolbar. To do so, click the Customize Quick Access Toolbar button at the end of the toolbar, click More Commands, and, in the dialog box that appears, click the Choose Commands From drop-down arrow and choose Commands Not in the Ribbon. Then scroll down the list and click Form, click the Add button, and click OK.

You can sort your database table to reorganize the information it contains. For example, you might want to sort a client table alphabetically by last name.

The easiest type of sort is a quick sort, which enables you to sort by a single criterion — for example, by last name. To perform a quick sort, simply click the drop-down arrow next to the name of the field by which you want to sort and click Sort A to Z (that is, ascending order) or Sort Z to A (that is, descending order). In the case of numbers, an ascending sort lists numbers from lowest to highest, and a descending sort lists numbers from highest to lowest.

Another way to sort is to use the Sort dialog box. With it, you can sort by multiple fields. For example, if you have an employee table, you might want to sort by name, longevity, and salary.

Yet another option is to filter your records using Excel's AutoFilter tool. Unlike conducting a sort, which sorts the entire table, using AutoFilter selects certain records to display based on your criteria, while hiding the other records that do not match the criteria.

Sort with the Sort Dialog Box

• 
• 
• 

  The Sort dialog box opens.

• 
• 
• 
  
• 
• 
  

Note

Click Undo (

Filter with AutoFilter

• 
• 
  
• 
  

Note

Click Undo to return the list to its original state.

Tip

More Options!

You can click the Advanced command on the Data tab to open the Advanced Filter dialog box. Here you can further customize the filter by selecting operators and values to apply on the filtered data.

Try This!

If you clear a filter but want to apply it again, click the Data tab and click the Reapply button in the Sort & Filter group.

To ensure that the data entered into your database table is of a valid type, you can set up your database table to control exactly what kinds of data are allowed in the cells. This is handy if other people use your database table to enter records. You can make sure that they type the right kind of data in a cell by assigning a data-validation rule.

In addition to ensuring that the right types of characters are entered — for example, text or numbers — you can also set up data-validation rules to, for example, restrict entries to a certain range of dates. You can also set up rules to limit data-entry choices by offering a drop-down list of options.

If users type the wrong data, such as text data instead of numerical data, Excel displays an error dialog box. You can configure Excel to include the text of your choosing in this dialog box — for example, instructions to the user with regard to what type of data can be entered.