Excel 2010 All-in-One For Dummies

In Excel 2010, you use the very familiar SUM function to total values in your worksheets. Use the SUMIF function when you want to total only the numbers in a range that meet criteria you specify. These functions are located with the Math & Trig functions on the Ribbon's Formulas tab or in the Select a Category list in the Insert Function dialog box.

The Page Break Preview feature in Excel 2010 helps you spot and fix page break problems in an instant, such as when the program wants to split onto different pages information that you know should always appear on the same page. Click the Page Break Preview button on the View tab. You also can click the Page Break Preview button on the Status bar.

You can use Excel 2010's database functions to calculate statistics, such as the total, average, maximum, minimum, and count in a particular database field when the criteria that you specify are met. For example, you could use the DSUM function in an Employee database to compute the sum of all the salaries for employees who were hired after January 1, 2000, or you could use the DCOUNT function to compute the number of records in the database for the Human Resources department.

The reference functions in Excel 2010, which are a part of the Lookup & Reference category, enable you to return specific information about particular cells or parts of the worksheet; create hyperlinks to different documents on your computer, network, or the Internet; and transpose ranges of vertical cells so that they run horizontally and vice versa.

Excel 2010's PMT function calculates the periodic payment for an annuity, assuming a stream of equal payments and a constant rate of interest. The PMT function uses the following syntax: =PMT(rate,nper,pv,[fv],[type]) As with the other common financial functions, rate is the interest rate per period, nper is the number of periods, pv is the present value or the amount the future payments are worth presently, fv is the future value or cash balance that you want after the last payment is made (Excel assumes a future value of zero when you omit this optional argument as you would when calculating loan payments), and type is the value 0 for payments made at the end of the period or the value 1 for payments made at the beginning of the period (if you omit the optional type argument, Excel assumes that the payment is made at the end of the period).

Excel 2010 offers an assortment of counting functions — including COUNT, COUNTA, COUNTBLANK, and COUNTIF — in the Statistical category that enable you to count the number of cells that contain numeric values, that are nonblank (and thus contain entries of any kind), or whose values meet the criteria that you specify.

Excel 2010 offers an assortment of counting functions — including COUNT, COUNTA, COUNTBLANK — in the Statistical category that enable you to count the number of cells that contain numeric values, that are nonblank (and thus contain entries of any kind), or whose values meet the criteria that you specify. You can find these functions on the Statistical continuation menu accessed from the More Functions button's drop-down menu on the Formulas tab.

When you can't find a ready-made Excel 2010 template that fits the bill, create your own templates from sample workbooks that you create or that your company has on hand. The easiest way to create your own template is to create an actual workbook prototype and then save the file using the template file format (.

Excel 2010's Statistical functions — including AVERAGE, MAX, MIN, and MEDIAN — are found on a continuation menu accessed from the More Functions command button's drop-down menu on the Formulas tab. Excel includes one of the most complete sets of statistical functions available outside a dedicated statistics software program.

The DAY, WEEKDAY, MONTH, and YEAR date functions in Excel 2010 all return parts of the date serial number that you specify as their argument. You can access these functions on the Date & Time button's drop-down menu. These functions use the following syntax: DAY(serial_number) returns the day of the month in the date as a number between 1 and 31.

Excel 2010 includes several time functions that you can use in your worksheets. Use the HOUR, MINUTE, and SECOND time functions to extract the hours, minutes, and seconds from a single time serial number. The HOUR, MINUTE, and SECOND functions let you extract specific parts of a time value in the worksheet. Each of these three time functions takes a single serial_number argument that contains the hour, minute, or second that you want to extract.

The most common financial functions in Excel 2010 — PV (Present Value) and FV (Future Value) — use the same arguments. The key to using these financial functions is to understand the terminology used by their arguments: PV is the present value, the principal amount of the annuity. FV is the future value, the principal plus interest on the annuity.

As an integral part of the Ribbon interface used by the major applications included in Microsoft Office 2010, Excel gives you access to hot keys that can help you select program commands more quickly. As soon as you press the Alt key, Excel displays the mnemonic letter choices on the various tabs and command buttons on the Ribbon.

You can activate the Excel 2010 hot keys by pressing the Alt key before you type the mnemonic letters for a particular task. The mnemonic letter for all view-related commands in Excel 2010 is W (the last letter you see in vieW). Therefore, all the hot keys for switching the Excel worksheet into a new view begin with Alt+W.

To activate the Excel 2010 hot keys, you press the Alt key before typing the mnemonic letters for a particular task. The mnemonic letter is F (for File) for the commands on the File menu in the new Backstage View. All you have to remember, then, is the second letter in the sequence; unfortunately, not all of the second letters are intuitive, as you can see in the following table.

The typical chart (or graph) in Excel 2010 comprises several distinct parts, including the chart area, data series, axes, legend, plot area, gridlines, data markers, and more. The following list summarizes the parts of a typical Excel chart, some of which appear in the illustration. A typical column chart containing a variety of standard chart elements.

You activate Excel 2010 hot keys by pressing the Alt key before you type the mnemonic letter for a particular task. Hot key sequences for the most common formula-related commands in Excel 2010 begin with the sequence Alt+M because the M in forMulas was the only mnemonic key still available (F is assigned to the File menu commands).

To activate Excel 2010 hot keys, you press the Alt key before you type the mnemonic letters for a particular task. The mnemonic letter is H (Home) for the editing commands because these commands are located on the Home tab. The remaining letters in the hot key sequences are not so intuitive. Fortunately, the most common editing commands (Cut, Copy, and Paste) still respond to the old Ctrl+key sequences (Ctrl+X, Ctrl+C, and Ctrl+V), and you may find them quicker to use.

You can add a graphic image to the background of cells in an Excel 2010 worksheet. The background image (sometimes called a watermark) should be very light in color or use a greatly reduced opacity in order for your worksheet data to be read over the image. This feature can be quite effective if you have a special corporate watermark graphic that adds just a hint of a background without obscuring the data being presented in its cells.

An array formula is a special formula that operates on a range of values in Excel 2010. When you build an array formula in a worksheet, you press Ctrl+Shift+Enter to insert an array formula in the array range. To get an idea of how you build and use array formulas in a worksheet, consider the example below. This worksheet is designed to compute the biweekly wages for each employee.

In Excel 2010, you select portrait or landscape page orientation, which affects the layout of the printed page. You also can adjust the Size setting based on the size of the paper you plan to use to print your worksheet. In addition to the Ribbon methods described below, you also can change the paper orientation and paper size from the Settings section of the Print panel in Backstage view (press Ctrl+P).

Print margins indicate how much white space Excel 2010 places between the worksheet data and the edge of the page. The Normal margin settings that Excel applies to a new workbook have top and bottom margins of 0.75 inches and left and right margins of 0.7 inches. You also can specify margins for the header and footer and center your worksheet horizontally or vertically on the page to improve the appearance of a printed report.

When you display your data visually in Excel 2010, choosing the right type of chart is just as important as deciding to use a chart at all. Different charts display the data in very different ways. Using the best chart type and format will help you display your data visually in the most meaningful way. The Insert Chart dialog box provides access to all the available chart types and subtypes.

In Excel 2010, you can create custom dictionaries to use when spell checking your worksheets. You use the Add to Dictionary button in the Spelling dialog box to add unknown words to a custom dictionary. By default, Excel adds these words to a custom dictionary file named CUSTOM.DIC, but you can create a new custom dictionary to use as the default, if you prefer.

In Microsoft Excel 2010, you can customize the Ribbon by adding brand new tabs and creating custom groups of command buttons within the displayed Ribbon tabs. Use the Customize Ribbon tab of the Excel Options dialog box to add new tabs to the Ribbon and customize the existing Ribbon tabs. You also can change the order in which the tabs appear in the Ribbon and which groups of command buttons appear on each of these displayed tabs.

After creating a chart in Excel 2010, you may need to change the range of the source data that is the basis for the chart. The Select Data Source dialog box enables you to choose a different source range for an existing chart. You also can use this dialog box to switch the row and column values, change the order of the data series used in the chart, and indicate how to deal with hidden and empty cells in the data range being charted.

If you get stuck and need help with Excel 2010 features, you can use the Excel Help window to quickly find the answers. The Excel Help button — the blue circle with a question mark, located to the right of the last Ribbon tab — provides quick access to Help. Certain commands on the Ribbon display a ScreenTip that says Press F1 for Help when you hover the mouse cursor over them.

You can use Excel 2010's Subtotals feature to subtotal data in a sorted list. To subtotal a list, you first sort the list on the field for which you want the subtotals, and then you designate the field that contains the values you want summed — these don't have to be the same fields in the list. Excel does not allow you to subtotal a list formatted as a table.

The Analysis ToolPak is an Excel 2010 add-in program that adds extra financial, statistical, and engineering functions to Excel's pool of built-in functions. The tools included in the Analysis ToolPak enable you to analyze worksheet data by using ANOVA, F-Test, rank and percentile, t-Test, and Fourier analysis.

In Excel 2010, you can use the ampersand (&) operator to concatenate (or join) separate text strings together. For example, in a client list worksheet that includes the last names in column A and the first names in column B, you could use this operator to join together the first and last names into a single entry (with the first and last names separated by a space) in column C.

The Print panel in Excel 2010's Backstage view includes commands that allow you to specify which parts of a workbook you want to print. You also can specify a print area from the Page Layout tab of the Ribbon. To display the Print panel, click the File tab and choose Print (or press Ctrl+P). Specifying print areas in Backstage view You can specify print areas in the Settings section of the Print panel in Excel's Backstage view.

Printing a chart in Excel 2010 is a breeze, but the method you choose for printing the chart depends on whether you want to print an embedded chart (with or without the surrounding worksheet data) or a separate chart sheet: To print an embedded chart as part of the data on the worksheet, you simply print the worksheet (from the Print panel in Backstage view by pressing Ctrl+P).

Excel 2010's Print Titles command enables you to print particular row and column headings on each page of the report. Print titles are important in multi-page reports where the columns and rows of related data spill over to other pages that no longer show the row and column headings. Don't confuse print titles with the header of a report.

After you create an Excel 2010 macro, either by using the macro recorder or by creating it in Visual Basic for Applications (VBA), you run the macro to have it carry out the commands and keystrokes that are saved as part of the macro. The macro commands in Excel 2010 are located on the Developer tab, which doesn't appear on the Ribbon by default.

Windows allows you to install more than one printer for use with Microsoft applications. If you've installed multiple printers, the first one installed becomes the default printer, which is used by all Windows applications, including Excel 2010. If you get a new printer, you must first install it from the Windows Control Panel before you can select and use the printer in Excel.

Excel 2010 introduced sparklines, a new type of information graphic that represents trends or variations in collected worksheet data. Sparklines are tiny graphs (miniature charts) that fit within a single cell in the worksheet. Sparklines can be any one of the following three chart types: Line that represents the relative value of the selected data.

Microsoft Office Excel 2010 provides access to translation tools on the Research task pane that enable you to translate words or phrases using bilingual dictionaries. To quickly look up a word or phrase that is located in a worksheet cell, press and hold the Alt key and click the cell. The Research task pane opens with the translation displayed in the list box.

Excel 2010 uses seven logical functions — AND, FALSE, IF, IFERROR, NOT, OR, and TRUE — which appear on the Logical command button's drop-down menu on the Formulas tab of the Ribbon. All the logical functions return either the logical TRUE or logical FALSE when their functions are evaluated. Here are the names of the logical functions along with their argument syntax: AND(logical1,logical2,.

Excel 2010 provides templates, ready-made worksheets for common uses. These predesigned templates contain standard or boilerplate text, but you can modify them to fit your needs. You can use the templates automatically installed with Excel 2010 or you can download free templates from Office.com, the Microsoft Office Web site.

The most popular of the lookup functions in Excel 2010 are HLOOKUP (for Horizontal Lookup) and VLOOKUP (for Vertical Lookup). These functions are located in the Lookup & Reference category on the Formulas tab of the Ribbon and in the Insert Function dialog box. They are part of a powerful group of functions that can return values by looking them up in data tables.

If you use Microsoft Excel 2010 often, you may want to make its program option a permanent part of the Windows Start menu or taskbar. This enables you to start the Excel 2010 program with just a click or two. The commands to pin (or unpin) Excel 2010 to the Start menu or taskbar apply to computers with Windows 7 installed.

Excel 2010 contains a number of built-in date functions that you can use in your worksheets. Three common date functions are TODAY, DATE, and DATEVALUE, and they can come in very handy when you're trying to create a schedule in Excel. TODAY The easiest and most commonly used date function has to be TODAY. This function takes no arguments and is always entered as follows: =TODAY() When you enter the TODAY function in a cell, Excel returns the current date by using the following Date format: 7/23/2011 Keep in mind that the date inserted into a cell with the TODAY function is not static.

Excel 2010's time functions enable you to convert text entries representing times of day into time serial numbers so that you can use them in calculations. Three common time functions are NOW, TIME, and TIMEVALUE. NOW The NOW function gives you the current date and time based on your computer's internal clock.

Excel 2010 includes the Live Preview feature, which enables you to see how a new font, font size, table style, cell style, or number format would look on your selected data before you actually apply it. This feature saves tons of time that would otherwise be wasted applying format after format until you finally select the right one.

The Print panel of Excel 2010's new Backstage view enables you to see how your worksheet data will be paged when printed. Save wasted paper and your sanity by previewing your data before you print any worksheet, section of a worksheet, or entire workbook. Because of the peculiarities in paging worksheet data, check the page breaks for any report that requires more than one page.

Linking formulas are formulas in Excel 2010 that reference a location in the same worksheet, same workbook, or even a different workbook. When you create a linking formula, it brings forward the constant or original formula to a new location so that the result in the linking formula remains dynamically tied to the original.

The mathematical functions ROUND, ROUNDUP, and ROUNDDOWN are included in the Math & Trig category in Excel 2010. You'll find these by clicking the Math & Trig button on the Ribbon's Formulas tab or in the Select a Category list in the Insert Function dialog box. ROUND You use the ROUND function to round up or down fractional values in the worksheet as you might when working with financial worksheets that only need to show monetary values to the nearest dollar.

Add graphic objects such as shapes, pictures, clip art, text boxes, WordArt, and SmartArt diagrams to create visual interest to Excel 2010 charts and worksheets. Excel supports two types of graphic objects: those that you create yourself from the Shapes gallery or with the SmartArt, Text Box, and WordArt command buttons on the Insert tab of the Ribbon, and those created by others that you import with the Picture and Clip Art command buttons.

Excel 2010 offers some effective formula-auditing tools for tracking down the cell that's causing your error woes by tracing the relationships between the formulas in the cells of your worksheet. By tracing the relationships, you can test formulas to see which cells, called direct precedents in spreadsheet jargon, directly feed the formulas, and which cells, called dependents, depend on the results of the formulas.

If Excel 2010 can't properly calculate a formula that you enter in a cell, the program displays an error value in the cell as soon as you complete the formula entry. Excel uses several error values, all of which begin with the number sign (#). Excel's error values The following table shows Excel's error values along with the meaning and the most probable cause for its appearance.

You can use the AutoSum button in Excel 2010 to quickly sum a row or a column of values in an Excel worksheet. The AutoSum button is located in the Editing group of the Home tab. When you click this button, Excel inserts the built-in SUM function into the active cell and simultaneously selects what the program thinks is the most likely range of numbers that you want added together.

Excel 2010's IS information functions (as in ISBLANK, ISERR, ISNA, ISNUMBER, ISTEXT, and so on) are a large group of functions that perform essentially the same task. They evaluate a value or cell reference and return the logical TRUE or FALSE, depending on whether the value is or isn't the type for which the IS function tests.

Excel 2010's Page Layout view gives you an instant view of how printed Excel pages will look. You activate this view by clicking either the Page Layout View button (the center one) to the immediate left of the Zoom slider on the Status bar, or the Page Layout View command button on the View tab of the Ribbon. When you switch to Page Layout view, Excel adds horizontal and vertical rulers to the column letter and row number headings.