Chapter 1: 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 2013

• Taking advantage of helpful tips and techniques

About Excel

Excel is, by far, the most commonly used spreadsheet product in the world. Because you're reading this book, you are probably familiar with Excel and have used the product for several years. But even a veteran user sometimes needs a refresher course — especially if your experience is mostly with Excel 2003 or earlier versions.

In this chapter, I provide a quick overview of Excel and introduce the concept of objects — an essential component in mastering VBA programming.

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 makes up 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 only one workbook is the active workbook at any given time. 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 and hiding the sheet.

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 2013 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 2013 automatically enters compatibility mode 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 reopen 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.

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

You control the column widths and row heights — you can even hide rows and columns (as well as entire worksheets). You can specify any font size, and you control the 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 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 are especially useful for presentations. Figure 1-1 shows a pie chart on a chart sheet.

Figure 1-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 with 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, which were used in Excel 4.0 and earlier. Excel 2013 continues to support XLM macros for compatibility purposes. This book doesn't cover the XLM macro system.

Excel 5 and 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 Visual Basic Editor (VBE).

If you open a workbook that contains an Excel 5 or 95 dialog sheet, you can access the dialog sheet by clicking its tab. I don't discuss Excel 5 and Excel 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, and keystroke combinations.

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

• Ribbon

• Quick Access Toolbar

• Right-click shortcut menus

• Mini toolbar

• Dialog boxes

• Keyboard shortcuts

• Task pane

Excel 2013 can also be run on touch-enabled devices. This book assumes that the reader has a traditional keyboard and mouse, and it does not cover the touch-related commands.

About the Ribbon

In Office 2007, Microsoft introduced a new UI for its product. Menus and toolbars were 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; 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, some commands may seem to be missing, but they are still available. Figure 1-2 shows the Home tab of the Ribbon as it appears for three different window widths.

On the top Ribbon, all controls are fully visible. The middle Ribbon is when Excel's window is narrower. Note that some descriptive text is gone, but the icons remain. The bottom Ribbon appears when the window is very narrow. Some groups display a single icon; click that icon, and all the group commands become available.

If you'd like to hide the Ribbon to increase your worksheet view, just double-click any tab. 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 the Ribbon comes back. You can also press Ctrl+F1 to toggle the Ribbon display or use the Ribbon Display Option control, located in the window's title bar.

Figure 1-2: The Home tab of the Ribbon, for three window widths.

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 on the Ribbon.

Figure 1-3 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 continue to use all the other tabs.

Figure 1-3: 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 on 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 the color it displays. 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, the command is a drop-down list. An example is the Orientation control in the Alignment group of the Home tab. Click the control and additional commands appear below it.

• Split buttons: A split button control combines a one-click button with a drop-down. 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. 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 1-4).

• 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 selected, the sheet displays gridlines. When the control isn't selected, 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.

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

Some 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 1-5). Click it, and it displays the Format Cells dialog box, with the Number tab preselected. This dialog box provides options that aren't available on the Ribbon.

Figure 1-4: The Paste command is a split button control.

Figure 1-5: This small dialog launcher icon, when clicked, displays a dialog box that has additional options.

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 choosing Show Quick Access Toolbar Below the Ribbon.

By default, the Quick Access toolbar contains three tools: Save, Undo, and Redo. You can customize the Quick Access toolbar by adding other commands that you use often. To add a command on 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 on the Ribbon. In most cases, the only way to access these commands is to add them to your Ribbon or Quick Access toolbar. Figure 1-6 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.

Figure 1-6: Add new icons to your Quick Access toolbar by using the Quick Access toolbar section of the Excel Options dialog box.

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 pop-up keytips. Each Ribbon control has a letter (or series of letters) that you type to issue the command.

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

Figure 1-7 shows how the Ribbon looks after I press the Alt key, followed by M to display keytips in the Formulas tab. 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 press AL (for Align Left). If you're a keyboard fan (like me), you'll memorize the keystrokes required for common commands after just a few times.

Figure 1-7: The Ribbon, with keytips displayed.

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.

Excel 2013 supports the menu-oriented keyboard shortcuts from Excel 2003. This is handy if you've memorized key sequences, such as Alt+ES (to display the Paste Special dialog box).

Shortcut menus and the Mini toolbar

Apart from the menus in Visual Basic Editor, 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, an element in a chart, 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 1-8 shows the Mini toolbar when a cell is right-clicked.

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 21 for more information about customizing shortcut menus. Note, however, that the new single document interface in Excel 2013 makes customizing shortcut menus more challenging.

Dialog boxes

Some Ribbon commands display a dialog box, from which you can specify options or issue other commands. 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 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 stay-on-top dialog boxes remain visible as you continue to work. An example is the Find and Replace dialog box. Modeless dialog boxes usually have a Close button rather than OK and Cancel buttons.

Some Excel dialog boxes use a notebook tab metaphor, which makes a single dialog box function as several different dialog boxes. An example is the Format Cells dialog box, shown in Figure 1-9.

Figure 1-8: Right-clicking some objects displays a Mini toolbar in addition to a shortcut menu.

Figure 1-9: 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 modeless dialog boxes and tabbed dialog boxes.

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

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, providing research assistance, and mapping eXtensible Markup Language (XML) data.

The task pane plays an enhanced role in Excel 2013. For example, chart formatting and other object formatting is now done in a task pane rather than in a modeless dialog box. Figure 1-10 shows the Format Shape task pane.

Figure 1-10: Formatting objects is one of several uses for the task pane.

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 (search for keyboard, 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.

Data Entry

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

• Numeric value (including date and time values)

• Text

• Boolean value (True or False)

• Formula

Formulas always begin with an equal sign (=). Excel accommodates habitual 1-2-3 users, however, and accepts an 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 2 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 1-11, for example, Excel identifies a possibly inconsistent formula and provides some options. Excel can also monitor your formulas for potential errors as you work. Error-checking options are available in the Formulas tab of the Excel Options dialog box.

Figure 1-11: Excel can monitor your formulas for possible errors and inconsistencies.

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 1-12. 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.

Figure 1-12: 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 8.

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 create and to read than formulas that use cell references.

I discuss names in Chapter 2. 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 1-13). 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.

Figure 1-13: Excel's numeric formatting options are flexible.

Stylistic formatting refers to the formatting that you apply to make your work look good. Many Ribbon commands offer direct access to common formatting options, but you'll want to access the object's Format task pane 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 action displays the task pane that holds all the formatting options for the selected object.

Excel does not provide a task pane for formatting cells.

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. The conditional formatting feature also has several data visualization options, including data bars, color scales, and icon sets. Figure 1-14 shows the data bars' conditional formatting option that displays a histogram directly in the cells.

Figure 1-14: The data bars option is one of the conditional formatting features.

Protection Options

Excel offers a number of 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 1-15.

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.

Figure 1-15: The Protect Sheet dialog box.

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

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 selected 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. Make sure that you select the Structure check box.

If the Windows check box is selected, the user cannot move or resize the workbook's window.

In Excel 2013, the Windows check box is disabled. The new single-document interface does not allow fixed-position and nonsizable workbook windows.

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 1-16). In this dialog box, you can specify a password that's required to open the workbook.

Figure 1-16: 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 want 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 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 1-17). Select 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.

Figure 1-17: Protecting a VBA project with the Project Properties dialog box.

Keep in mind that Excel isn't 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 tricks).

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. Excel 2013 has some new tools that makes customizing and fine-tuning a chart easier than ever.

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.

Sparkline graphics, a feature introduced in Excel 2010, consist of small charts that fit inside a cell. This type of chart is separate from Excel's standard chart feature. Figure 1-18 shows a worksheet with Sparkline graphics.

Figure 1-18: Sparkline graphics 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 you can size or position more easily than multiple objects.

A feature introduced in Office 2007 is SmartArt, which you use to create many different customizable diagrams. Figure 1-19 shows an example of a SmartArt diagram on a worksheet.

Figure 1-19: A SmartArt diagram.

Database Access

Over the years, most spreadsheets have enabled users to work with simple flat database tables. Excel can work with databases that fall into two categories:

• Worksheet databases: The entire database is stored in a worksheet.

• External databases: The database 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 1-20). Table rows that don't meet the filter criteria are temporarily hidden.

Figure 1-20: 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 2013 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 before 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 1-21 shows an example of a web query.

Figure 1-21: 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, primarily statistical in nature. 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 1-22 shows a pivot table formatted as a report.

See Chapter 15 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.

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 website (http://office.microsoft.com), 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 19, creating your own add-ins is very easy.

Figure 1-22: Excel's pivot table feature can produce attractive reports.

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

File Format

A key consideration for Excel users and developers is file compatibility. Excel 97 through Excel 2003 all use the same file format, so file compatibility isn't a problem for these versions. Microsoft introduced a new file format with Excel 2007 that is used also in subsequent versions. 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 2007 and later, it can't handle features that were introduced in the later versions.

Refer to Chapter 3 for more information about Excel's file format, and read Chapter 24 for more information about compatibility issues for developers.

Excel's Help System

One of Excel's most important features is its Help system (see Figure 1-23). 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.

Figure 1-23: Excel's Help window.