Chapter 2: Excel in a Nutshell

IN THIS CHAPTER

• Introducing Excel's object orientation

• Gaining a conceptual overview of Excel, including a description of its major features

• Discovering the new features in Excel 2010

• Taking advantage of helpful tips and techniques

Thinking in Terms of Objects

When you're developing applications with Excel (especially when you're dabbling with Visual Basic for Applications — VBA), it's helpful to think in terms of objects, or Excel elements that you can manipulate manually or via a macro. Here are some examples of Excel objects:

• The Excel application

• An Excel workbook

• A worksheet in a workbook

• A range or a table in a worksheet

• A ListBox control on a UserForm (a custom dialog box)

• A chart embedded in a worksheet

• A chart series in a chart

• A particular data point in a chart

You may notice that an object hierarchy exists here: The Excel object contains workbook objects, which contain worksheet objects, which contain range objects. This hierarchy comprises Excel's object model. Excel has more than 200 classes of objects that you can control directly or by using VBA. Other Microsoft Office products have their own object models.

Controlling objects is fundamental to developing applications. Throughout this book, you find out how to automate tasks by controlling Excel's objects, and you do so by using VBA. This concept becomes clearer in subsequent chapters.

Workbooks

The most common Excel object is a workbook. Everything that you do in Excel takes place in a workbook, which is stored in a file that, by default, has an XLSX extension. An Excel workbook can hold any number of sheets (limited only by memory). There are four types of sheets:

• Worksheets

• Chart sheets

• Excel 4.0 XLM macro sheets (obsolete, but still supported)

• Excel 5.0 dialog sheets (obsolete, but still supported)

You can open or create as many workbooks as you like (each in its own window), but at any given time, only one workbook is the active workbook. Similarly, only one sheet in a workbook is the active sheet. To activate a sheet, click its sheet tab at the bottom of the screen. To change a sheet's name, double-click the tab and enter the new text. Right-clicking a tab brings up a shortcut menu with additional options for the sheet, including changing its tab color, hiding the sheet, and so on.

You can also hide the window that contains a workbook by using the View⇒Window⇒Hide command. A hidden workbook window remains open, but it isn't visible to the user. Use the View⇒Window⇒Unhide command to make the window visible again. A single workbook can display in multiple windows (choose View⇒Window⇒New Window). Each window can display a different sheet or a different area of the same sheet.

Worksheets

The most common type of sheet is a worksheet, which is what people normally think of when they think of a spreadsheet. Worksheets contain cells, and the cells store data and formulas.

Excel 2010 worksheets have 16,384 columns and 1,048,576 rows. You can hide unneeded rows and columns to keep them out of view, but you can't increase or decrease the number of rows or columns.

Versions prior to Excel 2007 used the XLS binary format, and worksheets had only 65,536 rows and 256 columns. If you open such a file, Excel 2010 automatically enters compatibility mode in order to work with the smaller worksheet grid. To convert such a file to the new format, save it as an XLSX or XLSM file. Then close the workbook and re-open it.

The real value of using multiple worksheets in a workbook isn't access to more cells. Rather, multiple worksheets enable you to organize your work better. Back in the old days, when a file comprised a single worksheet, developers wasted a lot of time trying to organize the worksheet to hold their information efficiently. Now you can store information on any number of worksheets and still access it instantly by clicking a sheet tab.

By default, every new workbook starts out with three worksheets. You can easily add a new sheet when necessary, so you really don't need to start with three sheets. You may want to change this default to a single sheet. To change this option, use the Office⇒Excel Options command, click the General tab, and change the setting for the option labeled Include This Many Sheets.

As you know, a worksheet cell can hold a constant value or the result of a formula. The value may be a number, a date, a Boolean value (True or False), or text. Every worksheet also has an invisible drawing layer, which lets you insert graphic objects, such as charts, shapes, SmartArt, UserForm controls, pictures, and other embedded objects.

You have complete control over the column widths and row heights — in fact, you can even hide rows and columns (as well as entire worksheets). You can specify any font size, and you have complete control over colors. You can display text in a cell vertically (or at an angle) and even wrap it around to occupy multiple lines. In addition, you can merge a group of cells to create a single larger cell.

In the past, Excel was limited to a palette of 56 colors. Beginning with Excel 2007, the number of colors has been virtually unlimited. In addition, Excel 2007 introduced document themes. A single click lets you apply a new theme to a workbook, which can give it an entirely different look.

Chart sheets

A chart sheet normally holds a single chart. Many users ignore chart sheets, preferring to store charts on the worksheet's drawing layer. Using chart sheets is optional, but they make it a bit easier to print a chart on a page by itself, and they're especially useful for presentations. Figure 2-1 shows a pie chart on a chart sheet.

FIGURE 2-1: A pie chart on a chart sheet.

XLM macro sheets

An XLM macro sheet (also known as an MS Excel 4 macro sheet) is essentially a worksheet, but it has some different defaults. More specifically, an XLM macro sheet displays formulas rather than the results of formulas. In addition, the default column width is larger than in a normal worksheet.

As the name suggests, an XLM macro sheet is designed to hold XLM macros. As you may know, the XLM macro system is a holdover from previous versions of Excel (Version 4.0 and earlier). Excel 2010 continues to support XLM macros for compatibility reasons — although it no longer provides the option of recording an XLM macro. This book doesn't cover the XLM macro system; instead, it focuses on the more powerful VBA macro system.

Excel 5/95 dialog sheets

In Excel 5 and Excel 95, you created a custom dialog box by inserting a special dialog sheet. Excel 97 and later versions still support these dialog sheets, but a much better alternative is available: UserForms. You work with UserForms in the Visual Basic Editor (VBE).

If you open a workbook that contains an Excel 5/95 dialog sheet, you can access the dialog sheet by clicking its tab.

I don't discuss Excel 5/95 dialog sheets in this book.

Excel's User Interface

A user interface (UI) is the means by which an end user communicates with a computer program. Generally speaking, a UI includes elements such as menus, toolbars, dialog boxes, keystroke combinations, and so on.

The release of Office 2007 signaled the end of traditional menus and toolbars. The UI for Excel consists of the following elements:

• The Ribbon

• The Quick Access toolbar

• Right-click shortcut menus

• Dialog boxes

• Keyboard shortcuts

• Smart Tags

• Task pane

About the Ribbon

In Office 2007, Microsoft introduced an entirely new UI for its product. Menus and toolbars are gone, replaced with a tab and Ribbon UI. Click a tab along the top (that is, a word such as Home, Insert, or Page Layout), and the Ribbon displays the commands for that tab. Office 2007 was the first software in history to use this new interface, and a few other companies have incorporated this new UI style in their products.

The appearance of the commands on the Ribbon varies, depending on the width of the Excel window. When the window is too narrow to display everything, the commands adapt and may seem to be missing. But the commands are still available. Figure 2-2 shows the Home tab of the Ribbon with all controls fully visible. Figure 2-3 shows the Ribbon when Excel's window is narrower. Notice that some of the descriptive text is gone, but the icons remain. Figure 2-4 shows the extreme case, in which the window is very narrow. Some of the groups display a single icon. However, if you click the icon, all the group commands are available to you.

FIGURE 2-2: The Home tab of the Ribbon.

FIGURE 2-3: The Home tab when Excel's window is narrower.

FIGURE 2-4: The Home tab when Excel's window is very narrow.

If you'd like to hide the Ribbon to increase your worksheet view, just double-click any of the tabs. The Ribbon goes away, and you'll be able to see about four additional rows of your worksheet. When you need to use the Ribbon again, just click any tab, and it comes back. You can also press Ctrl+F1 to toggle the Ribbon display or use the ^ control, to the left of the Help icon in the tab bar.

Contextual tabs

In addition to the standard tabs, Excel includes contextual tabs. Whenever an object (such as a chart, a table, a picture, or SmartArt) is selected, tools for working with that specific object are made available in the Ribbon.

Figure 2-5 shows the contextual tabs that appear when an embedded equation is selected. In this case, Excel displays two contextual tabs: Format (for working with object) and Design (for working with the equation). Notice that the contextual tabs contain a description (Drawing Tools and Equation Tools) in Excel's title bar. When contextual tabs are displayed, you can, of course, continue to use all the other tabs.

FIGURE 2-5: When you select an object, contextual tabs contain tools for working with that object.

Types of commands on the Ribbon

For the most part, the commands in the Ribbon work just as you'd expect them to. You'll encounter several different styles of commands on the Ribbon:

• Simple buttons: Click the button, and it does its thing. An example of a simple button is the Increase Font Size button in the Font group of the Home tab. Some buttons perform the action immediately; others display a dialog box so that you can enter additional information. Button controls may or may not be accompanied by text.

• Toggle buttons: A toggle button is clickable and also conveys some type of information by displaying two different colors. An example is the Bold button in the Font group of the Home tab. If the active cell isn't bold, the Bold button displays in its normal color. But if the active cell is already bold, the Bold button displays a different background color. If you click this button, it toggles the Bold attribute for the selection.

• Simple drop-downs: If the Ribbon command has a small downward-pointing arrow, then the command is a drop-down list. Click it, and additional commands appear below it. An example of a simple drop-down is the Merge and Center command in the Alignment group of the Home Tab. When you click this control, you see four options related to merging and centering information.

• Split buttons: A split button control combines a one-click button (on the top) with a drop-down (on the bottom). If you click the button part, the command is executed. If you click the drop-down part, you choose from a list of related commands. You can identify a split button because it displays in two colors when you hover the mouse over it. An example of a split button is the Paste command in the Clipboard group of the Home tab. Clicking the top part of this control pastes the information from the Clipboard. If you click the bottom part of the control, you get a list of paste-related commands (see Figure 2-6).

• Check boxes: A check box control turns something on or off. An example is the Gridlines control in the Show/Hide group of the View tab. When the Gridlines check box is checked, the sheet displays gridlines. When the control isn't checked, the sheet gridlines aren't displayed.

• Spinners: An example of a spinner control is in the Scale to Fit group of the Page Layout tab. Click the top part of the spinner to increase the value; click the bottom part of the spinner to decrease the value.

FIGURE 2-6: The Paste command is a split button control.

Refer to Chapter 22 for information about customizing Excel's Ribbon.

Some of the Ribbon groups contain a small icon in the lower-right corner, known as a dialog launcher. For example, if you examine the Home⇒Alignment group, you'll see this icon (refer to Figure 2-7). Click it, and it displays the Format Cells dialog box, with the Number tab preselected. This dialog box provides options that aren't available in the Ribbon.

FIGURE 2-7: This small dialog launcher icon, when clicked, displays a dialog box that has additional options.

In Excel 2007, end users couldn't modify the Ribbon. In Excel 2010, users can easily add or remove commands. See Chapter 22 for information about customizing the Ribbon.

The Quick Access toolbar

The Quick Access toolbar is a place to store commonly used commands. The Quick Access toolbar is always visible, regardless of which Ribbon tab you select. Normally, the Quick Access toolbar appears on the left side of the title bar. Alternatively, you can display the Quick Access toolbar below the Ribbon by right-clicking the Quick Access toolbar and selecting Show Quick Access Toolbar Below the Ribbon.

By default, the Quick Access toolbar contains three tools: Save, Undo, and Redo. You can, of course, customize the Quick Access toolbar by adding other commands that you use often. To add a command from the Ribbon to your Quick Access toolbar, right-click the command and choose Add To Quick Access toolbar.

Excel has quite a few commands that aren't available in the Ribbon. In most cases, the only way to access these commands is to add them to your Ribbon or Quick Access toolbar. Figure 2-8 shows the Quick Access toolbar section of the Excel Options dialog box. This area is your one-stop shop for Quick Access toolbar customization. A quick way to display this dialog box is to right-click the Quick Access toolbar and choose Customize Quick Access toolbar.

Accessing the Ribbon by using your keyboard

At first glance, you may think that the Ribbon is completely mouse-centric. After all, none of the commands has the traditional underlined letter to indicate the Alt+keystrokes. But, in fact, the Ribbon is very keyboard friendly. The trick is to press the Alt key to display the pop-up keytips. Each Ribbon control has a letter (or series of letters) that you type to issue the command.

FIGURE 2-8: Add new icons to your Quick Access toolbar by using the Quick Access toolbar section of the Excel Options dialog box.

You don't 'need to hold down the Alt key as you type the keytip letters.

Figure 2-9 shows how the Home tab looks after I press the Alt key to display the keytips. If you press one of the keytips, the screen then displays more keytips. For example, to use the keyboard to align the cell contents to the left, press Alt, followed by H (for Home) and then AL (for Align Left). If you're a keyboard fan (like me), it will just take a few times before you memorize the keystrokes required for common commands.

After you press Alt, you can also use the left and right arrow keys to scroll through the tabs. When you reach the proper tab, press the down-arrow key to enter the Ribbon. Then use the left- and right-arrow keys to scroll through the Ribbon commands. When you reach the command you need, press Enter to execute it. This method isn't as efficient as using the keytips, but it's a quick way to take a look at the choices on the Ribbon.

FIGURE 2-9: Pressing Alt displays the keytips.

Excel 2010 still supports the menu-oriented keyboard shortcuts from Excel 2003. So if you've memorized key sequences, such as Alt+ES (to display the Paste Special dialog box), you can still use those shortcuts.

Shortcut menus and the Mini Toolbar

The only menus that remain in Excel are shortcut menus. These menus appear when you right-click your mouse. The shortcut menus are context-sensitive. In other words, the menu that appears depends on the location of the mouse pointer when you right-click. You can right-click just about anything — a cell, a row or column border, a workbook title bar, a toolbar, and so on.

Right-clicking some objects displays a Mini Toolbar above the shortcut menu. This toolbar provides quick access to commonly used formatting commands. Figure 2-10 shows the Mini Toolbar when a cell is selected.

Although you can't customize the Ribbon by using VBA, you can use VBA to customize any of the shortcut menus. You can't, however, modify the Mini Toolbar.

Refer to Chapter 23 for more information about customizing shortcut menus.

FIGURE 2-10: Right-clicking some objects displays a Mini Toolbar in addition to a shortcut menu.

Dialog boxes

Some Ribbon commands display a dialog box. In many cases, these dialog boxes contain additional controls that aren't available in the Ribbon.

You'll find two general classes of dialog boxes in Excel:

• Modal dialog boxes: When a modal dialog box is displayed, it must be closed in order to execute the commands. An example is the Format Cells dialog box. None of the options you specify are executed until you click OK. Use the Cancel button to close the dialog box without making any changes.

• Modeless dialog boxes: These are stay-on-top dialog boxes. For example, if you're working with a chart using the Format dialog box, your changes are reflected immediately in the chart. Modeless dialog boxes usually have a Close button rather than OK and Cancel buttons.

Many of Excel's dialog boxes use a notebook tab metaphor, which makes a single dialog box function as several different dialog boxes. In older dialog boxes, the tabs are usually along the top. But in newer dialog boxes (such as the one shown in Figure 2-11), the tabs are along the left side.

FIGURE 2-11: Tabbed dialog boxes make many options accessible without overwhelming the user.

Developers can create custom dialog boxes by using the UserForm feature. As you'll see, you can create a wide variety of dialog boxes, including tabbed dialog boxes.

Refer to Part IV for information about creating and working with UserForms.

Keyboard shortcuts

Excel has many useful keyboard shortcuts. For example, you can press Ctrl+D to copy a cell to selected cells below it. If you're a newcomer to Excel — or you just want to improve your efficiency — I urge you to check out the Help system (access the Accessibility main topic and go from there). Learning these shortcuts is key to becoming proficient in Excel. The Help file has tables that summarize useful keyboard commands and shortcuts.

And, as I note previously, you can access the Ribbon commands by using the keyboard.

Smart Tags

A Smart Tag is a small icon that appears automatically in your worksheet after you perform certain actions. Clicking a Smart Tag reveals several options. For example, if you copy and paste a range of cells, Excel generates a Smart Tag that appears below the pasted range (see Figure 2-12) and provides you with several options regarding the formatting of the pasted data.

FIGURE 2-12: This Smart Tag appears when you paste a copied range.

Task pane

Excel 2002 introduced a new UI element known as the task pane. This multipurpose user interface element is normally docked on the right side of Excel's window (but you can drag it anywhere). The task pane is used for a variety of purposes, including displaying the Office Clipboard, displaying a pivot table field list, inserting clipart, providing research assistance, and mapping eXtensible Markup Language (XML) data. Figure 2-13 shows the Clip Art task pane.

FIGURE 2-13: Locating clipart is one of several uses for the task pane.

Customizing the Display

Excel offers a great deal of flexibility regarding what is displayed on-screen (formula bar, gridlines, row and column headings, and so on). These commands are located in the View tab.

In fact, Excel makes it possible to develop an application that doesn't even look like a spreadsheet. For example, by choosing View⇒Workbook Views⇒Full Screen, you can get rid of everything except the title bar, thereby maximizing the amount of information visible. To exit full-screen mode, right-click any cell and choose Close Full Screen from the shortcut menu.

Notice the zoom control in the right side of the status bar. This control makes zooming in or out very easy. In addition, you can right-click the status bar and specify the type of information you'd like to see.

Data Entry

Data entry in Excel is quite straightforward. Excel interprets each cell entry as one of the following:

• A numeric value (including date and time values)

• Text

• A Boolean value (True or False)

• A formula

Formulas always begin with an equal sign (=). Excel accommodates habitual 1-2-3 users, however, and accepts an each-at symbol (@), a plus sign (+), or a minus sign (–) as the first character in a formula. Excel automatically adjusts the entry after you press Enter.

Formulas, Functions, and Names

Formulas are what make a spreadsheet a spreadsheet. Excel has some advanced formula-related features that are worth knowing. They enable you to write array formulas, use an intersection operator, include links, and create megaformulas (my term for a lengthy and incomprehensible — but very efficient — formula).

Chapter 3 covers formulas and presents lots of tricks and tips.

Excel also has some useful auditing capabilities that help you identify errors or track the logic in an unfamiliar spreadsheet. To access these features, use the commands in the Formulas⇒Formula Auditing group.

You may find the Formulas⇒Formula Auditing⇒Error Checking command useful. This command scans your worksheet and identifies possibly erroneous formulas. In Figure 2-14, Excel identifies a possibly inconsistent formula and provides some options.

Worksheet functions enable you to perform calculations or operations that would otherwise be impossible. Excel provides a huge number of built-in functions.

The easiest way to locate the function that you need is to use the Insert Function dialog box, as shown in Figure 2-15. Access this dialog box by clicking the Insert Function button on the formula bar (or by pressing Shift+F3). After you select a function, Excel displays its Function Arguments dialog box, which assists with specifying the function's arguments.

Beginning with Excel 2007, the Analysis ToolPak functions became part of Excel. In other words, you can use these function even if the Analysis ToolPak add-in isn't installed.

FIGURE 2-14: Excel can monitor your formulas for possible errors.

FIGURE 2-15: The Insert Function dialog box is the best way to insert a function into a formula.

Excel also lets you create your own worksheet functions by using VBA. For details about this powerful feature, see Chapter 10.

A name is an identifier that enables you to refer to a cell, range, value, formula, or graphic object. Formulas that use names are much easier to read than formulas that use cell references, and creating formulas that use named references is much easier.

I discuss names in Chapter 3. As you can see there, Excel handles names in some unique ways.

Selecting Objects

Selecting objects in Excel conforms to standard Windows practices. You can select a range of cells by clicking and dragging. (Learning the keyboard shortcuts is more efficient, however.) Clicking an object that has been placed on the drawing layer selects the object. To select multiple objects or noncontiguous cells, press Ctrl while you select the objects or cells.

Clicking a chart selects a specific object within the chart. To select the chart object itself, press Ctrl while you click the chart.

If an object has a macro assigned to it, clicking the object executes the macro. To actually select such an object, right-click it and press Esc to hide the shortcut menu. Or press Ctrl while you click the object.

Formatting

Excel provides two types of formatting: numeric formatting and stylistic formatting.

Numeric formatting refers to how a number appears in the cell. In addition to choosing from an extensive list of predefined formats, you can create your own formats (see Figure 2-16). The procedure is thoroughly explained in the Help system.

Excel applies some numeric formatting automatically, based on the entry. For example, if you precede a number with a currency symbol (a dollar sign in the United States), Excel applies Currency number formatting. You can also use the conditional formatting feature to apply number formatting conditionally, based on the magnitude of the number.

Stylistic formatting refers to the formatting that you apply to make your work look good. Many Ribbon buttons offer direct access to common formatting options, but you'll want to access the object's Format dialog box for the full range of formatting options.

The easiest way to get to the correct dialog box and format an object is to select the object and press Ctrl+1. You can also right-click the object and choose Format xxx (where xxx is the selected object) from the shortcut menu. Either of these actions brings up a tabbed dialog box that holds all the formatting options for the selected object.

FIGURE 2-16: Excel's numeric formatting options are very flexible.

Excel's conditional formatting feature is particularly useful. This feature, accessed by choosing Home⇒Styles⇒Conditional Formatting, allows you to specify formatting that will be applied only if certain conditions are met. For example, you can make cells that exceed a specified value appear in a different color.

Excel 2007 introduced several conditional formatting options, including data bars, color scales, and icon sets. These features have been enhanced in Excel 2010. Figure 2-17 shows the data bars conditional formatting option that displays a histogram directly in the cells.

FIGURE 2-17: The data bars option is one of the conditional formatting features.

Protection Options

Excel offers a number of different protection options. For example, you can protect formulas from being overwritten or modified, protect a workbook's structure, password-protect a workbook, and protect your VBA code.

Protecting formulas from being overwritten

In many cases, you might want to protect your formulas from being overwritten or modified. To do so, perform the following steps:

1. Select the cells that may be overwritten.

2. Right-click and choose Format Cells from the shortcut menu.

3. In the Format Cells dialog box, click the Protection tab.

4. In the Protection tab, clear the Locked check box.

5. Click OK to close the Format Cells dialog box.

6. Choose Review⇒Changes⇒Protect Sheet to display the Protect Sheet dialog box, as shown in Figure 2-18.

7. In the Protect Sheet dialog box, select the options that correspond to the actions to allow, specify a password if desired, and then click OK.

By default, all cells are locked. The locked status of a cell has no effect, however, unless you have a protected worksheet.

Figure 2-18: The Protect Sheet dialog box.

You can also hide your formulas so that they won't appear in Excel's formula bar when the cell is activated. To do so, select the formula cells and make sure that the Hidden check box is marked in the Protection tab of the Format Cells dialog box.

Protecting a workbook's structure

When you protect a workbook's structure, you can't add or delete sheets. Choose the Review⇒Changes⇒Protect Workbook command to display the Protect Structure and Windows dialog box, as shown in Figure 2-19. Make sure that you enable the Structure check box. If you also mark the Windows check box, you can't move or resize the window.

FIGURE 2-19: The Protect Structure and Windows dialog box.

Applying password protection to a workbook

In some cases, you may want to limit access to a workbook to only those who know the password.

To save a workbook file with a password, choose File⇒Info⇒Protect Workbook⇒Encrypt With Password to display the Encrypt Document dialog box (see Figure 2-20). In this dialog box, you can specify a password that's required to open the workbook.

FIGURE 2-20: Use the Encrypt Document dialog box to save a workbook with a password.

Protecting VBA code with a password

If your workbook contains VBA code, you may wish to use a password to prevent others from viewing or modifying your macros. To apply a password to the VBA code in a workbook, activate the VBE (Alt+F11) and select your project in the Projects window. Then choose Tools⇒xxxx Properties (where xxxx corresponds to your Project name) to display the Project Properties dialog box.

In the Project Properties dialog box, click the Protection tab (see Figure 2-21). Enable the Lock Project for Viewing check box and enter a password (twice). Click OK and then save your file. When the file is closed and then reopened, a password will be required to view or modify the code.

Keep in mind that Excel isn't really a secure application. The protection features, even when used with a password, are intended to prevent casual users from accessing various components of your workbook. Anyone who really wants to defeat your protection can probably do so by using readily available password-cracking utilities (or by knowing a few “secrets”).

FIGURE 2-21: Protecting a VBA project with the Project Properties dialog box.

Charts

Excel is perhaps the most commonly used application in the world for creating charts. As I mention earlier in this chapter, you can store charts on a chart sheet or float them on a worksheet. You can also create pivot charts. A pivot chart is linked to a pivot table, and you can view various graphical summaries of your data by using the same techniques used in a pivot table.

A new feature in Excel 2010 is Sparkline charts. These small charts fit inside a cell. This type of chart is completely separate from Excel's standard chart feature. Figure 2-22 shows a worksheet with some Sparkline charts added.

FIGURE 2-22: Sparkline charts in a worksheet.

Shapes and SmartArt

As I mention earlier in this chapter, each worksheet has an invisible drawing layer that holds charts, pictures, controls (such as buttons and list boxes), and shapes.

Excel enables you to easily draw a wide variety of geometric shapes directly on your worksheet. To access the Shape gallery, choose Insert⇒Illustrations⇒Shapes. The shapes are highly customizable, and you can even add text. You can also group objects into a single object, which is easier to size or position.

A feature introduced in Office 2007 is SmartArt, which you use to create a wide variety of customizable diagrams. Figure 2-23 shows an example of a SmartArt diagram on a worksheet.

FIGURE 2-23: A SmartArt diagram.

Database Access

Over the years, most spreadsheets have enabled users to work with simple flat database tables. Excel has some slick tools.

Databases fall into two categories:

• Worksheet databases: The entire database is stored in a worksheet, limiting the size of the database.

• External databases: The data is stored in one or more files and is accessed as needed.

Worksheet databases

Generally, a rectangular range of data that contains column headers can be considered a worksheet database.

Excel 2007 was the first version that enabled you to specifically designate a range as a table. Select any cell in your rectangular range of data and choose Insert⇒Tables⇒Table. Using a table offers many advantages: an automatic summary row at the bottom, easy filtering and sorting, auto-fill formulas in columns, and simplified formatting. In addition, if you create a chart from a table, the chart expands automatically as you add rows to the table.

Tables are particularly useful when working with columns of data. Each column header is actually a drop-down list that contains easy access for filtering or sorting (see Figure 2-24). Table rows that don't meet the filter criteria are temporarily hidden.

FIGURE 2-24: Excel's table feature makes it easy to sort and filter rows.

External databases

To work with external database tables, use the commands in the Data⇒Get External Data group. Excel 2010 can work with a wide variety of external databases.

Internet Features

Excel includes a number of features that relate to the Internet. For example, you can save a worksheet or an entire workbook in HyperText Markup Language (HTML) format, accessible in a Web browser. In addition, you can insert clickable hyperlinks (including e-mail addresses) directly in cells.

In versions prior to Excel 2007, HTML was a round-trip file format. In other words, you could save a workbook in HTML format and then reopen it in Excel, and nothing would be lost. That's no longer the case. HTML is now considered an export-only format.

You can also create Web queries to bring in data stored in a corporate intranet or on the Internet. Such a query can be refreshed, so the data updates as new information is posted. Figure 2-25 shows an example of a Web query.

FIGURE 2-25: Create a Web query to import data into a worksheet.

Analysis Tools

Excel is certainly no slouch when it comes to analysis. After all, that's what most people use a spreadsheet for. You can handle most analysis tasks with formulas, but Excel offers many other options:

• Outlines: A worksheet outline is often an excellent way to work with hierarchical data such as budgets. Excel can create an outline (horizontal, vertical, or both) automatically, or you can do so manually. After you create the outline, you can collapse or expand it to display various levels of detail.

• Analysis ToolPak: In previous versions of Excel, the Analysis ToolPak add-in provided additional special-purpose analysis tools and worksheet functions, primarily statistical in nature. Beginning with Excel 2007, these features are built in. These tools make Excel suitable for casual statistical analysis.

• Pivot tables: Pivot tables are among Excel's most powerful tools. A pivot table is capable of summarizing data in a handy table, and you can arrange this table in many ways. In addition, you can manipulate a pivot table entirely by VBA. Data for a pivot table comes from a worksheet database or an external database and is stored in a special cache, which enables Excel to recalculate rapidly after a pivot table is altered. Figure 2-26 shows a pivot table.

FIGURE 2-26: Excel's pivot table feature has many applications.

See Chapter 17 for information about manipulating pivot tables with VBA.

• Solver: For specialized linear and nonlinear problems, Excel's Solver add-in calculates solutions to what-if scenarios based on adjustable cells, constraint cells, and, optionally, cells that must be maximized or minimized.

The Solver add-in has finally been updated in Excel 2010. It has a new look as well as some performance improvements.

Add-Ins

An add-in is a program that's attached to an application to give it additional functionality. To attach an Excel add-in, use the Add-Ins tab in the Excel Options dialog box.

In addition to the add-ins that ship with Excel, you can download additional add-ins from Microsoft's Web site (http://officemicrosoftcom), and you can purchase or download many third-party add-ins from online services. You can use the coupon in the back of the book to acquire a discounted copy of the Power Utility Pak add-in. And, as I detail in Chapter 21, creating your own add-ins is very easy.

Macros and Programming

Excel has two built-in macro programming languages: XLM and VBA. The original XLM macro language is obsolete and has been replaced by VBA. Excel 2010 can still execute most XLM macros, and you can even create new ones. However, you can't record XLM macros. You'll want to use VBA to develop new macros.

Part III of this book is devoted to the VBA language.

File Format

A key consideration is file compatibility. Excel 97 through Excel 2003 all use the same file format, so file compatibility isn't a problem for these four versions. Microsoft introduced a new file format with Excel 2007, and it's also used in Excel 2010. Fortunately, Microsoft has made a compatibility pack available for Excel XP and Excel 2003. This compatibility pack enables these older versions of Excel to read and write the new file format.

It's important to understand the difference between file compatibility and feature compatibility. For example, even though the compatibility pack enables Excel 2003 to open files created by Excel 2010, it can't handle features that were introduced in later versions.

Refer to Chapter 4 for more information about Excel's file format and read Chapter 26 for more information about compatibility issues for developers.

Excel's Help System

One of Excel's most important features is its Help system. When you get stuck, simply click the question mark below the title bar (or press F1). Excel's Help window appears, and you can search or use the Table of Contents.

The Search button in the Help window is actually a drop-down control. Use the options to help narrow your search or to specify the source to search (see Figure 2-27).

FIGURE 2-27: Excel's Help window.