Chapter 5. Optimizing Excel

You can use Excel to perform a wide variety of number-crunching tasks on data, from the simplest calculation to the most complex formula. You can also use Excel to track and manage large quantities of data such as inventories, price lists, and more. You can even use Excel as a database, entering and sorting records.

You can use Excel to perform a wide variety of number-crunching tasks on data, from the simplest calculation to the most complex formula. You can also use Excel to track and manage large quantities of data such as inventories, price lists, and more. You can even use Excel as a database, entering and sorting records.

Data you enter into Excel is stored in a workbook. Each workbook contains individual worksheets, which hold your data. By default, Excel workbooks have three worksheets, each identified by a tab at the bottom of the screen, but you can add or remove worksheets as needed.

A worksheet is a grid, formed by columns and rows. Columns are labeled with letters, whereas rows are numbered. Every intersection of a column and row creates a cell. Cells are the receptacles for your Excel data. Every cell in an Excel worksheet has a unique name, also called an address or cell reference, which consists of the column letter and row number, with the column listed first. For example, cell A1 is the first cell in the worksheet. The next cell to the right is B1.

A group of related cells in a worksheet is called a range. Excel identifies a range by the anchor points in the upper left and lower right corners of the range, separated by a colon. For example, the range A1:B3 includes cells A1, A2, A3, B1, B2, and B3. Ranges are particularly useful when you begin creating formulas that reference groups of cells.

  • Automatically Open Your Favorite Workbook. 100

  • Automate Data Entry with AutoFill. 102

  • Color-Code and Name Worksheet Tabs. 104

  • Keep Cells in View with a Watch Window. 106

  • Protect Cells from Unauthorized Changes. 108

  • Generate Random Numbers in Your Cells. 110

  • Freeze Headings for Easier Scrolling. 111

  • Insert a Comment in a Formula. 112

  • Join Text from Separate Cells. 113

  • Add a Calculator to the Quick Access Toolbar. 114

  • Audit a Worksheet for Errors. 116

  • Create Projections. 118

  • Establish What-If Scenarios. 120

  • Set Goals with Goal Seek. 122

  • Define and Solve Problems with Solver. 124

  • Create a Database Table. 126

  • Add and Edit Records Using Data Forms. 128

  • Sort and Filter Records. 130

  • Restrict Cell Entries with Data-Validation Rules. 132

Automatically Open Your Favorite Workbook

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.

Create an Alternate Startup Folder

  • Create an Alternate Startup Folder
  • Create an Alternate Startup Folder
  • Create an Alternate Startup Folder

    The Save As dialog box appears.

  • Create an Alternate Startup Folder
  • Create an Alternate Startup Folder
    Create an Alternate Startup Folder
    Create an Alternate Startup Folder
  • Create an Alternate Startup Folder

Designate a Startup File

  • Designate a Startup File
  • Designate a Startup File
    Designate a Startup File

    The Excel Options dialog box appears.

  • Designate a Startup File
  • Designate a Startup File
  • Designate a Startup File

    Note

    Be sure to type in the full folder path accurately or Excel cannot locate your file.

  • Designate a Startup File

    The next time you open Excel, the designated file opens, too.

    Note

    To remove a startup file, repeat these steps and delete the path found in the Excel Options dialog box.

Tip

More Options!

If you use Excel every day, you can tell your computer to open the program automatically when you turn on your computer. You can place a shortcut to the Excel program in your Windows XP, Windows Vista, or Windows 7 Startup folder. Look up your system's Startup folder and place a shortcut to Excel in the folder.

Caution!

If you ever run into trouble with automatically launching a workbook, such as a system crash, you may have to visit the Advanced resources and enable the workbook startup again. Click the File tab and click Options to open the Excel Options dialog box. Click Advanced, and check the folder path in the General settings. If you accidentally moved the file, you may need to fix the designated path listed.

Automate Data Entry with AutoFill

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.

AutoFill a Text Series

  • AutoFill a Text Series
  • AutoFill a Text Series
    AutoFill a Text Series
  • AutoFill a Text Series
    AutoFill a Text Series

AutoFill a Number Series

  • AutoFill a Number Series
  • AutoFill a Number Series
  • AutoFill a Number Series
  • AutoFill a Number Series
    AutoFill a Number Series
  • AutoFill a Number Series
    AutoFill a Number Series

Tip

Customize It!

To add your own custom list to AutoFill's list library, first enter the contents of the list in your worksheet cells. Then do the following:

  1. Select the cells containing the list you want to save.

  2. Click the File tab.

  3. Click Options to open the Excel Options dialog box.

  4. Click Advanced.

  5. Scroll down to the General group and click Edit Custom Lists.

  6. In the Custom Lists dialog box, click Import. Excel adds the series to the custom lists.

    You can also create a new list by clicking Add and typing your list.

  7. Click OK to close both dialog boxes.

Color-Code and Name Worksheet Tabs

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."

Color-Code Sheet Tabs

  • Color-Code Sheet Tabs
  • Color-Code Sheet Tabs
  • Color-Code Sheet Tabs
    Color-Code Sheet Tabs
    Color-Code Sheet Tabs

    Note

    Click another tab to see the color change in the tab you edited.

    Color-Code Sheet Tabs

Name Sheet Tabs

  • Name Sheet Tabs
  • Name Sheet Tabs
    Name Sheet Tabs

    Note

    You can also double-click the tab name.

  • Name Sheet Tabs
  • Name Sheet Tabs

    The name is assigned.

Tip

Remove It!

To remove color-coding from a worksheet tab, right-click it, click Tab Color on the pop-up menu, and then click No Color from the palette. This resets the tab to its original default status.

Try This!

If your workbook consists of dozens of sheets, you may tire of endlessly scrolling to find the one you want. Instead, try this trick: Right-click a scroll arrow to the left of the tab names. This displays a pop-up list of all the sheets in the workbook. Just click the one you want to view.

Keep Cells in View with a Watch Window

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.

  • Keep Cells in View with a Watch Window
  • Keep Cells in View with a Watch Window
    Keep Cells in View with a Watch Window

    The Watch Window opens.

  • Keep Cells in View with a Watch Window

    The Add Watch dialog box opens.

  • Keep Cells in View with a Watch Window
  • Keep Cells in View with a Watch Window
    Keep Cells in View with a Watch Window
Keep Cells in View with a Watch Window

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

Keep Cells in View with a Watch Window

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 (

Keep Cells in View with a Watch Window

Protect Cells from Unauthorized Changes

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.

Protect Workbook Structure

  • Protect Workbook Structure
  • Protect Workbook Structure

    The Protect Structure and Windows dialog box opens.

    Protect Workbook Structure
  • Protect Workbook Structure
  • Protect Workbook Structure
  • Protect Workbook Structure

    The Confirm Password dialog box appears.

  • Protect Workbook Structure
  • Protect Workbook Structure

    Excel assigns the password to the workbook. The next time you or any other user opens the workbook, features for deleting, moving, and renaming worksheets will be unavailable.

Protect Worksheet Elements

  • Protect Worksheet Elements
  • Protect Worksheet Elements

    The Protect Sheet dialog box opens.

  • Protect Worksheet Elements
  • Protect Worksheet Elements
    Protect Worksheet Elements
  • Protect Worksheet Elements
  • Protect Worksheet Elements

    Excel prompts you to retype the password.

  • Protect Worksheet Elements
  • Protect Worksheet Elements

    Excel assigns the password to the worksheet. The next time you or any other user opens the worksheet, only the features you selected will be available.

Tip

Caution!

The best passwords contain a mix of uppercase and lowercase letters, numbers, and symbols. Remembering your Excel passwords is critical. If you lose a password, you cannot make changes to a password-protected file. Consider writing the password down and keeping it in a safe place.

Remove It!

If you no longer want to password-protect a workbook or worksheet, you can easily remove the password protection. To unprotect a password-protected workbook, click the Review tab in the Ribbon and click Protect Workbook. The Unprotect Workbook dialog box appears; type the password and click OK. Unprotect a password-protected worksheet by right-clicking the sheet's tab and choosing Unprotect Sheet; in the Unprotect Sheet dialog box that opens, type the password and click OK.

Generate Random Numbers in Your Cells

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.

  • Generate Random Numbers in Your Cells
  • Generate Random Numbers in Your Cells
    Generate Random Numbers in Your Cells
  • Generate Random Numbers in Your Cells
    Generate Random Numbers in Your Cells
  • Generate Random Numbers in Your Cells

    Excel fills the cells when you release the mouse button.

Freeze Headings for Easier Scrolling

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.

  • Freeze Headings for Easier Scrolling
  • Freeze Headings for Easier Scrolling
  • Freeze Headings for Easier Scrolling
  • Freeze Headings for Easier Scrolling
    Freeze Headings for Easier Scrolling
Freeze Headings for Easier Scrolling
Freeze Headings for Easier Scrolling
Freeze Headings for Easier Scrolling

Insert a Comment in a Formula

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.

  • Insert a Comment in a Formula
  • Insert a Comment in a Formula
  • Insert a Comment in a Formula
    Insert a Comment in a Formula
  • Insert a Comment in a Formula
Insert a Comment in a Formula

Join Text from Separate Cells

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.

  • Join Text from Separate Cells
  • Join Text from Separate Cells
    Join Text from Separate Cells

    Note

    Do not forget to press the spacebar between the quotation marks to add space between the names you join.

    Note

    Be sure to write the cell references in the order in which you want them to join together.

  • Join Text from Separate Cells
Join Text from Separate Cells

Add a Calculator to the Quick Access Toolbar

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.

  • Add a Calculator to the Quick Access Toolbar
  • Add a Calculator to the Quick Access Toolbar

    Note

    You can also add the Calculator tool to any Ribbon tab. See Chapter 1 to learn more about customizing the Ribbon.

    Add a Calculator to the Quick Access Toolbar

    The Excel Options dialog box opens to the Quick Access Toolbar settings.

  • Add a Calculator to the Quick Access Toolbar
  • Add a Calculator to the Quick Access Toolbar
  • Add a Calculator to the Quick Access Toolbar
  • Add a Calculator to the Quick Access Toolbar
    Add a Calculator to the Quick Access Toolbar
  • Add a Calculator to the Quick Access Toolbar
    Add a Calculator to the Quick Access Toolbar
  • Add a Calculator to the Quick Access Toolbar

    The Calculator window opens for you to perform calculations.

Add a Calculator to the Quick Access Toolbar

Tip

Try This!

You can save yourself some repetitive typing time by simply copying and pasting a calculation result from the Calculator window to an Excel worksheet cell. With the Calculator window active, press Ctrl+C to copy the results to the Windows Clipboard. Next, click inside the cell where you want to paste the data and press Ctrl+V. Voila! The data appears in the cell.

Remove It!

You can remove the Calculator tool from the Quick Access toolbar just as easily as you placed it there. You can choose from two methods. One method is to reopen the Excel Options dialog box as shown in this task, click the Calculator's Custom name in the right list box, and click the Remove button in the center of the dialog box. Click OK and the icon is removed from the toolbar. Another method is to right-click the button and click Remove from Quick Access Toolbar.

Audit a Worksheet for Errors

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).

Check Errors

  • Check Errors
  • Check Errors
    Check Errors
    Check Errors
    Check Errors
    Check Errors
  • Check Errors
    Check Errors
  • Check Errors

    In this example, a typo in the formula is corrected.

  • Check Errors

    When the error check is complete, a prompt box appears.

  • Check Errors

Trace Errors

  • Trace Errors
  • Trace Errors
  • Trace Errors
  • Trace Errors
    Trace Errors
    Trace Errors
    Trace Errors
  • Trace Errors

Tip

Did You Know?

You can click Evaluate Formula in the Formulas tab's Formula Auditing group to check over a formula or function step by step. Simply click the cell containing the formula you want to evaluate and click Evaluate Formula; Excel opens the Evaluate Formula dialog box, where you can evaluate each portion of the formula to check it for correct references and values.

Try This!

To quickly ascertain the relationships among various cells in your worksheet, click a cell, click the Formulas tab on the Ribbon, and click Trace Precedents or Trace Dependents in the Formula Auditing group. Excel displays trace lines from the current cell to related cells — that is, cells with formulas that reference it or vice versa.

Create Projections

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

  • Determine a Linear Trend
  • Determine a Linear Trend
  • Determine a Linear Trend
  • Determine a Linear Trend
  • Determine a Linear Trend

    A context menu appears.

  • Determine a Linear Trend
    Determine a Linear Trend
Determine a Linear Trend

Determine a Growth Trend

  • Determine a Growth Trend
  • Determine a Growth Trend
  • Determine a Growth Trend
  • Determine a Growth Trend
  • Determine a Growth Trend

    A context menu appears.

  • Determine a Growth Trend
    Determine a Growth Trend
Determine a Growth Trend

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.

Establish What-If Scenarios

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.

  • Establish What-If Scenarios
  • Establish What-If Scenarios
  • Establish What-If Scenarios

    The Scenario Manager dialog box opens.

  • Establish What-If Scenarios
    Establish What-If Scenarios

    The Add Scenario dialog box opens.

  • Establish What-If Scenarios
  • Establish What-If Scenarios
  • Establish What-If Scenarios
  • Establish What-If Scenarios

    The Scenario Values dialog box appears.

  • Establish What-If Scenarios
  • Establish What-If Scenarios
    Establish What-If Scenarios
  • Establish What-If Scenarios
    Establish What-If Scenarios
  • Establish What-If Scenarios

    When you close the Scenario Manager, the last applied scenario remains in the worksheet.

Tip

Try This!

To generate a summary report that compares the results of multiple scenarios either by listing them side by side or by arranging them in a pivot table, click the What If Analysis drop-down arrow in the Data tab's Data Tools group, click Scenario Manager, and then click Summary. In the dialog box that appears, click Scenario Summary or Scenario PivotTable Report and enter the references for cells whose values are changed by the scenario. Then click OK.

Apply It!

To view the results of a scenario, open the Scenario Manager dialog box as normal (click What If Analysis in the Data tab's Data Tools group and click Scenario Manager), click the scenario you want to view, and click Show.

Set Goals with Goal Seek

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).

  • Set Goals with Goal Seek
  • Set Goals with Goal Seek
  • Set Goals with Goal Seek
    Set Goals with Goal Seek

    The Goal Seek dialog box opens.

  • Set Goals with Goal Seek
    Set Goals with Goal Seek
  • Set Goals with Goal Seek
  • Set Goals with Goal Seek
  • Set Goals with Goal Seek
    Set Goals with Goal Seek

    The Goal Seek Status dialog box opens.

    Set Goals with Goal Seek
  • Set Goals with Goal Seek

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 (

Set Goals with Goal Seek

Define and Solve Problems with Solver

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.

  • Define and Solve Problems with Solver
  • Define and Solve Problems with Solver

    Note

    If no Solver button appears on the Data tab, try unloading and reloading the Solver add-in. (To unload the add-in, deselect the Solver Add-in check box in the Add-Ins dialog box mentioned in the introduction to this task and click OK.)

    Define and Solve Problems with Solver

    The Solver Parameters dialog box opens.

  • Define and Solve Problems with Solver
  • Define and Solve Problems with Solver
  • Define and Solve Problems with Solver
  • Define and Solve Problems with Solver

    Note

    To enter multiple noncontiguous cells, separate each cell reference with a comma.

  • Define and Solve Problems with Solver

    The Add Constraint dialog box opens.

  • Define and Solve Problems with Solver
  • Define and Solve Problems with Solver
  • Define and Solve Problems with Solver

    In this example, the constraint is that at least five of the cars must be economy cars.

  • Define and Solve Problems with Solver
    Define and Solve Problems with Solver
  • Define and Solve Problems with Solver

    The Solver Results dialog box opens.

    Define and Solve Problems with Solver
  • Define and Solve Problems with Solver
    Define and Solve Problems with Solver
    Define and Solve Problems with Solver
  • Define and Solve Problems with Solver

Tip

More Options!

You can set multiple constraints for the adjustable cells, the target cell, or any other cells related to the target cell to limit the solutions provided by Solver. To add more constraints, click the Add button in the Solver Parameters dialog box and define the constraints in the Add Constraint dialog box. To add more constraints within the Add Constraint dialog box, just click the Add button.

Create a Database Table

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.

  • Create a Database Table
  • Create a Database Table
    Create a Database Table
  • Create a Database Table
  • Create a Database Table
  • Create a Database Table
  • Create a Database Table

    The Create Table dialog box opens.

    Create a Database Table
    Create a Database Table
  • Create a Database Table
    Create a Database Table
Create a Database Table
Create a Database Table
Create a Database Table

Tip

Did You Know?

Entering data into a table can be tedious. To speed up the task, you can use Excel's PickList feature, which is activated as soon as you create the first record in your database table and remembers the previous field entries so you can repeat them, if necessary:

  1. Right-click a cell in a new record.

  2. Click Pick From Drop-down List.

    A list of choices appears.

  3. Click an entry to repeat it in the current cell.

Add and Edit Records Using Data Forms

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.

  • Add and Edit Records Using Data Forms
  • Add and Edit Records Using Data Forms
    Add and Edit Records Using Data Forms

    A data form dialog box opens displaying data from the record you selected in step 1.

  • Add and Edit Records Using Data Forms
  • Add and Edit Records Using Data Forms
  • Add and Edit Records Using Data Forms
    Add and Edit Records Using Data Forms
  • Add and Edit Records Using Data Forms
    Add and Edit Records Using Data Forms
Add and Edit Records Using Data Forms

Tip

Try This!

Click the Find Prev and Find Next buttons in the data form dialog box to navigate records in a database table. Edit records by typing over the existing values in the data form dialog box's fields.

Delete It!

To delete a record, click the first cell in any record in the table and open the data form dialog box. Then click the Find Prev or Find Next button as many times as necessary to locate the record you want to delete. Finally, click the Delete button in the dialog box, and click OK to confirm the deletion. Alternatively, select the row in the table that contains the record you want to delete and then press Delete. This deletes the row content. To remove the row entirely, right-click the row number and click Delete.

Sort and Filter Records

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

  • Sort with the Sort Dialog Box
  • Sort with the Sort Dialog Box
  • Sort with the Sort Dialog Box

    The Sort dialog box opens.

  • Sort with the Sort Dialog Box
  • Sort with the Sort Dialog Box
  • Sort with the Sort Dialog Box
    Sort with the Sort Dialog Box
  • Sort with the Sort Dialog Box
  • Sort with the Sort Dialog Box
    Sort with the Sort Dialog Box
Sort with the Sort Dialog Box

Note

Click Undo (

Sort with the Sort Dialog Box

Filter with AutoFilter

  • Filter with AutoFilter
  • Filter with AutoFilter
    Filter with AutoFilter
  • Filter with AutoFilter
    Filter with AutoFilter
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.

Restrict Cell Entries with Data-Validation Rules

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.

  • Restrict Cell Entries with Data-Validation Rules
  • Restrict Cell Entries with Data-Validation Rules
  • Restrict Cell Entries with Data-Validation Rules
  • Restrict Cell Entries with Data-Validation Rules
    Restrict Cell Entries with Data-Validation Rules

    The Data Validation dialog box opens.

  • Restrict Cell Entries with Data-Validation Rules
  • Restrict Cell Entries with Data-Validation Rules
    Restrict Cell Entries with Data-Validation Rules
  • Restrict Cell Entries with Data-Validation Rules
  • Restrict Cell Entries with Data-Validation Rules
  • Restrict Cell Entries with Data-Validation Rules
  • Restrict Cell Entries with Data-Validation Rules
    Restrict Cell Entries with Data-Validation Rules
  • Restrict Cell Entries with Data-Validation Rules
  • Restrict Cell Entries with Data-Validation Rules
  • Restrict Cell Entries with Data-Validation Rules
  • Restrict Cell Entries with Data-Validation Rules
  • Restrict Cell Entries with Data-Validation Rules
  • Restrict Cell Entries with Data-Validation Rules

Tip

Try This!

To create a drop-down list of options from which users can choose when entering data into your database table, click the Allow drop-down arrow in the Data Validation dialog box's Settings tab and choose List. Then, in the Source field, type the options you want to appear in the list, placing a comma between each entry. (Note that the width of the cell to which data validation is being applied determines the width of the drop-down list. To ensure that items in the list are not truncated, consider widening the column.)

Remove It!

To remove data validation, select the range containing the data validation rule and then reopen the Data Validation dialog box. Click Clear All; this turns off the data-validation rules.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.191.218.187