Excel 2013 All-in-One For Dummies

By activating the Analysis ToolPak add-in with Excel 2013, you add a whole bunch of powerful financial functions to the Financial button’s drop-down menu on the Formulas tab of the Ribbon. The table shows all the financial functions that are added to the Insert Function dialog box when the Analysis ToolPak is activated.

Instead of assigning the password to open your Excel 2013 workbook at the time you save changes to it, you can do this as well from the Info screen in the Backstage view by following these simple steps:Choose File→Info or press Alt+FI.Excel opens the Info screen. Click the Protect Workbook button to open its drop-down menu and then choose Encrypt with Password.

A circular reference in an Excel 2013 formula is one that depends, directly or indirectly, on its own value. The most common type of circular reference occurs when you mistakenly refer in the formula to the cell in which you’re building the formula itself. For example, suppose that cell B10 is active when you build this formula: =A10+B10 As soon as you click the Enter button on the Formula bar or press Enter or an arrow key to insert this formula in cell B10 (assuming the program is in Automatic recalculation mode), Excel displays an Alert dialog box, stating that it cannot calculate the formula due to the circular reference.

PowerPivot in Excel 2013 makes it easy to perform sophisticated modeling with the data in your Excel pivot tables. To open the PowerPivot for Excel window, you click the Manage button in the Data Model group on the PowerPivot tab shown or press Alt+BM. If your workbook already contains a pivot table that uses a Data Model created with external data already imported in the worksheet when you select the Manage button, Excel opens a PowerPivot window similar to the one shown.

Excel’s Text to Speech feature can help you flag and then correct some errors that otherwise you would have to check and verify by comparing cell to cell. Usually, you do this by checking the columns and rows of data in a spreadsheet against the original documents from which you generated the spreadsheet. Excel’s Text to Speech feature reads aloud each entry that’s been made in a selected range of cells or data table within the worksheet.

When you choose Error Checking Options from the alert options drop-down menu attached to a cell with an error value or click the Options button in the Error Checking dialog box, Excel 2013 opens the Formulas tab of the Excel Options dialog box. This tab displays the Error Checking and Error Checking Rules options that are currently in effect in Excel.

Microsoft Excel 2013 gives you access to hot key sequences that keyboard enthusiasts can use to greatly speed up the process of selecting program commands. The first thing to know is that you activate all the Excel 2013 hot keys by pressing the Alt key before you type the various sequences of mnemonic (or not-so-mnemonic) letters.

The process of embedding and linking Excel 2013 worksheet data and charts in the slides of your Microsoft PowerPoint 2013 presentations is very similar to the techniques outlined for Word. To embed a cell selection or chart, drag the data or chart object from the Excel worksheet to the PowerPoint slide. If you prefer using the cut-and-paste method, copy the data or chart to the Clipboard (Ctrl+C), switch to PowerPoint, and choose the Paste Special option from the Paste button’s drop-down menu on the Home tab of the PowerPoint Ribbon (or press Alt+HVS).

Excel 2013 offers some very effective formula-auditing tools for tracking down a 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 (nondeductible, of course), depend on the results of the formulas.

Excel’s Print Titles enable 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 on the first page. Don’t confuse print titles with the header of a report.

Excel 2013 makes it easy to apply common formatting changes to a cell selection right within the Worksheet area thanks to its mini-toolbar feature — nicknamed the mini-bar. To display the mini-toolbar, select the cells that need formatting and then right-click somewhere in the cell selection. The mini-toolbar then appears immediately below or above the cell selection’s shortcut menu.

The formatting buttons that appear in the Font, Alignment, and Number groups on the Home tab in Excel 2013 enable you to accomplish targeted cell formatting. Some spreadsheet tables require a lighter touch than formatting as a table offers. For example, you may have a data table where the only emphasis you want to add is to make the column headings bold at the top of the table and to underline the row of totals at the bottom (done by drawing a borderline along the bottom of the cells).

Excel 2013’s Format as Table feature enables you to both define an entire range of data as a table and format all its data all in one operation. After you define a cell range as a table, you can completely modify its formatting simply by clicking a new style thumbnail in the Table Styles gallery. Excel also automatically extends this table definition — and consequently its table formatting — to all the new rows you insert within the table and add at the bottom as well as any new columns you insert within the table or add to either the table’s left or right end.

You can use Excel’s new Quick Analysis tool to format your data as a new table. Simply select all the cells in the table, including the cells in the first row with the column headings. As soon as you do, the Quick Analysis tool appears in the lower-right corner of the cell selection (the outlined button with the lightning bolt striking the selected data icon).

If you can remember that H stands for Home, you can remember that Alt+H starts Excel 2013's hot key sequence for commands on the Ribbon's Home tab. The remaining letters in the hot key sequences are not as easy to remember as you might like. 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, respectively).

Excel 2013 hot keys give you quick access to menu commands when you press the Alt key and then a sequence of letters. The mnemonic letter is F (for File) for the commands on the Excel 2013 File menu in the new Backstage View. Therefore, all you have to remember in the following table is the second letter in the File menu hot key sequence.

All the hot key sequences for selecting the most common formula-related commands in Excel 2013 begin with the sequence Alt+M — the M in forMulas was the only mnemonic still available. After you know to press Alt+M to access one of the commands on the Formula tab of the Ribbon, it's pretty easy sailing, if for no other reason than the commands on the Formula tab are pretty evenly and logically laid out.

The hot key combination for all view-related commands in Excel 2013 is Alt+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. After you know that, you'll find most of the subsequent letters fairly easy to deal with. Hot Key Exce

When you turn on change tracking for an Excel 2013 workbook, you can decide which changes to accept or reject by choosing the Accept/Reject Changes option from the Track Changes command button’s drop-down menu on the Ribbon’s Review tab (or pressing Alt+RGC). When you do this, Excel reviews all the highlighted changes made by you and others who’ve worked on the shared file, enabling you to accept or reject individual changes.

You can create your own Calculated Fields for a pivot table in Excel 2013. Calculated Fields are computed by a formula that you create by using existing numeric fields in the data source. To create a Calculated Field for your pivot table, follow these steps:Click any of the cells in the pivot table and then select the Calculated Field option from the Fields, Items, & Sets button’s drop-down list, or press Alt+JTJF.

Sometimes, instead of data labels that can easily obscure the data points in the chart, you’ll want Excel 2013 to draw a data table beneath the chart showing the worksheet data it represents in graphic form. To add a data table to your selected chart and position and format it, click the Chart Elements button next to the chart and then select the Data Table check box before you select one of the following options on its continuation menu: With Legend Keys to have Excel draw the table at the bottom of the chart, including the color keys used in the legend to differentiate the data series in the first column No Legend Keys to have Excel draw the table at the bottom of the chart without any legend More Options to open the Format Data Table task pane on the right side where you can use the options that appear when you select the Fill & Line, Effects, Size & Properties, and Table Options buttons under Table Options and the Text Fill & Outline, Text Effects, and Textbox buttons under Text Options in the task pane to customize almost any aspect of the data table The figure illustrates how a clustered column chart looks with a data table added to it.

You can use the InputBox function to add dialog boxes to your Excel 2013 macros. When you run the macro, this Visual Basic function causes Excel to display an Input dialog box where you can enter whatever title makes sense for the new worksheet. The macro then puts that text into the current cell and formats this text, if that’s what you’ve trained your macro to do next.

Linking formulas are formulas that transfer a constant or other formula to a new place in the same worksheet, same workbook, or even a different workbook without copying it to its new location. 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 WordArt gallery in Excel 2013, opened by clicking the WordArt command button in the Text group of the Insert tab of the Ribbon, makes it a snap to add really artsy text to the worksheet. The only thing to keep in mind when adding WordArt is that, just as its name implies, this text is really a graphic (art) object that behaves just like any other Excel graphic object although it contains only text!

Excel 2013 enables you to add digital signatures to the workbook files that you send out for review. After checking the spreadsheet and verifying its accuracy and readiness for distribution, you can (assuming that you have the authority within your company) digitally sign the workbook: To add a digital signature to your finalized workbook, you follow these steps:Inspect the worksheet data, save all final changes in the workbook file, and then position the cell pointer in a blank cell in the vicinity where you want the signature line graphic object to appear.

When you’re dealing with two graphic objects in Excel 2013, one on top of the other, and you want to align them with each other, you can use the options on the Align command button’s drop-down menu on the Format tab of the Drawing Tools or Picture Tools contextual menu after selecting both of them in the worksheet.

You can also assign a graphic image to be used as the background for all the cells in an entire worksheet in Excel 2013. Just be aware that the background image must either be very light in color or use a greatly reduced opacity in order for your worksheet data to be read over the image. This probably makes most graphics that you have readily available unusable as worksheet background images.

To get an idea of how you build and use array formulas in an Excel 2013 worksheet, consider the sample worksheet. This worksheet is designed to compute the biweekly wages for each employee. It will do this by multiplying each employee’s hourly rate by the number of hours worked in each pay period. Instead of creating the following formula in cell R10, you copy down the cells R11 through R13: =A4*R4 You can create the following array formula in the array range: ={A4:A7*R4:R7} This array formula multiplies each of the hourly rates in the 4 x 1 array in the range A4:A7 with each of the hours worked in the 4 x 1 array in the range R4:R7.

The Future Value (FV) function in Excel 2013 is found on the Financial button’s drop-down menu on the Ribbon’s Formulas tab (Alt+MI). The FV function calculates the future value of an investment. The syntax of this function is =FV(rate,nper,pmt,[pv],[type]) The rate, nper, pmt, and type arguments are the same as those used by the PV function.

The Net Present Value (NPV) function in Excel 2013 calculates the net present value based on a series of cash flows. The syntax of this function is =NPV(<i>rate</i>,<i>value1</i>,[<i>value2</i>],[...]) where value1, value2, and so on are between 1 and 13 value arguments representing a series of payments (negative values) and income (positive values), each of which is equally spaced in time and occurs at the end of the period.

The PV (Present Value) function in Excel 2013 is found on the Financial button’s drop-down menu on the Ribbon’s Formulas tab (Alt+MI). The PV function returns the present value of an investment, which is the total amount that a series of future payments is worth presently. The syntax of the PV function is as follows: =PV(rate,nper,pmt,[fv],[type]) The fv and type arguments are optional arguments in the function (indicated by the square brackets).

To change the status of cells in an Excel 2010 worksheet from locked to unlocked or from unhidden to hidden, you use the Locked and Hidden check boxes found on the Protection tab of the Format Cells dialog box (Ctrl+1). To remove the Locked protection status from a cell range or nonadjacent selection, you follow these two steps: Select the range or ranges to be unlocked.

Normally, Excel 2013 recalculates your worksheet automatically as soon you change any entries, formulas, or names on which your formulas depend. This system works fine as long as the worksheet is not too large or doesn’t contain tables whose formulas depend on several values. When Excel does calculate your worksheet, the program recalculates only those cells that are affected by the change that you’ve made.

Just as you can use AutoFill in Excel 2013 to fill out a series with increments different from one unit, you can also get it to fill out custom lists of your own design. For example, suppose that you often have to enter a standard series of city locations as the column or row headings in new spreadsheets that you build.

Power View is a COM (Component Object Model) add-in that comes with most versions of Excel 2013. This add-in works with PowerPivot for Excel 2013 to enable you create visual reports for your Excel Data Model. To use the Power View add-in to create a visual report for the Data Model represented in your Excel pivot table, click the Power View button on the Insert tab of the Excel Ribbon or press Alt+NV.

You can create a custom cell style from scratch in Excel 2013 by defining each of its formatting characteristics in the Style dialog box as follows:Position the cell pointer in a cell that doesn’t have any formatting applied to it and then click the New Cell Style option at the bottom of the Cell Styles drop-down gallery (opened by clicking the Cell Styles button in the Styles group on the Ribbon’s Home tab).

Although Excel 2013 offers several stock headers and footers, you may want to insert information not available or in an arrangement that Excel doesn’t offer in the ready-made headers and footers. For those times, you need to use the command buttons that appear in the Header & Footer Elements group of the Design tab on the Header & Footer Tools contextual tab.

Excel 2013 has an AutoCorrect feature that automatically fixes common typos that you make in the text entries as soon as you complete them. In addition to the errors already recognized by AutoCorrect, you can add your own particular mistakes to the list of automatic replacements. You can use the AutoCorrect feature to automatically replace favorite abbreviations with full text, as well as to clean up all your personal typing mistakes.

To help your user understand the purpose of your custom functions in Excel 2013, you can add descriptions that appear in Insert Function and Function Arguments dialog boxes that help explain what the function does. To add this kind of description to your user-defined function, you use the Object Browser, a special window in the Visual Basic Editor that enables you to get information about particular objects available to the project that you have open.

Anytime you launch Excel (without also opening an existing workbook file), the Excel screen in the Backstage view presents you with a choice between opening a new workbook or opening a new workbook based on the design in an existing template. Spreadsheet templates are the way to go if you can find one that uses the design of the spreadsheet that you want to build.

After you’ve created a macro in Excel 2013, you don’t necessarily have to re-record it to change the way it behaves. In many cases, you will find it more expedient to change its behavior by simply editing its contents in the Visual Basic Editor. Note that if the macro you want to edit is stored in your Personal Macro Workbook, you must unhide this workbook before you edit it in the Visual Basic Editor.

You can use Excel’s Eliminate Duplicates feature to quickly find and remove duplicate records from a list (or rows from a table). This is a great feature especially when you’re dealing with a really large data list in which several different people do the data entry and which should not have any duplicate records (such as client lists, personnel files, and the like).

Of all the Office 2013 programs (besides Excel 2013), Microsoft Word 2013 is the one that you are most apt to use. From time to time, you may need to bring some worksheet data or charts that you’ve created in your Excel workbooks into a Word document that you’re creating. As with all the other Office programs, you have two choices when bringing Excel data (worksheet cell data or charts) into a Word document: You can embed the data in the Word document, or you can link the data that you bring into Word to its original Excel worksheet.

You can use the Allow Users to Edit Ranges command button in the Changes group on the Review tab of the Excel 2013 Ribbon to enable the editing of particular ranges in the protected worksheet by certain users. When you use this feature, you give certain users permission to edit particular cell ranges, provided that they can correctly provide the password you assign to that range.

The Top Ten option on the Number Filters option’s submenu in Excel 2013 enables you to filter out all records except those whose entries in that field are at the top or bottom of the list by a certain number (10 by default) or in a certain top or bottom percent (10 by default). Of course, you can only use the Top Ten item in numerical fields and date fields; this kind of filtering doesn’t make any sense when you’re dealing with entries in a text field.

You can use the Find feature in the Visual Basic Editor to quickly locate the statements or properties that need editing in your Excel 2013 macro. You open the Find dialog box by choosing Edit→Find on the menu bar, clicking the Find button on the Standard toolbar, or by pressing Ctrl+F. This dialog box is very similar to the one you use when finding entries in your Excel spreadsheet.

The CELL function in Excel 2013 is the basic information function for getting all sorts of data about the current contents and formatting of a cell. The syntax of the CELL function is CELL(info_type,[reference]) The info_type argument is a text value that specifies the type of cell information you want returned.

If you’re running Excel 2013 with a computer connected to a digital tablet or on a touchscreen, you can mark up your worksheets with digital ink. Excel 2013 running on a computer equipped with a digital tablet or running on a touchscreen device contains a Start Inking command button located at the very end of the Ribbon’s Review tab.

The Normal margin settings that Excel 2013 applies to a new report use standard top and bottom margins of 0.75 inch (3/4 inch) and left and right margins of 0.7 inch with just over a 1/4 inch separating the header and footer from the top and bottom margins, respectively. In addition to the Normal margin settings, the program enables you to choose two other standard margins from the Margins button’s drop-down menu in the Print screen (Ctrl+P): Wide margins with 1-inch top, bottom, left, and right margins and 1/2 inch separating the header and footer from the top and bottom margins, respectively.

At some point after sharing an Excel 2013 workbook, you’ll want to update the workbook to incorporate the changes made by different users. When merging changes, you may also have to deal with conflicting changes made to the same cells and decide which changes to accept and which to reject. After you’ve merged all the input and decided how to deal with all the conflicting changes, you may even want to turn off file sharing to prevent users from doing any further editing.

If you’ve created custom styles in another workbook in Excel 2013 that you want to use in a new workbook or in an existing one that you’ve opened for editing, you have to merge them into that workbook as follows:Open the workbook file containing the custom styles that you want to copy and use.You must have the workbook containing the custom styles to merge open, along with the workbook into which these custom styles will be copied.

All graphic objects that you add to an Excel 2013 worksheet lay on different invisible layers that reside on top of the worksheet and over the worksheet data in the cells below. This means that if you move a graphic object over a cell that contains an entry, the graphic hides the data beneath it. Likewise, if you draw a shape or add an image and then position it on top of another graphic object (such as an embedded chart or other shape or picture), it also covers up the graphic below.

You may have a table in Excel 2013 in which you need to perform a two-way lookup, whereby a piece of data is retrieved from the Lookup table based on looking up a value in the top row (with the table’s column headings) and a value in the first column (with the table’s row headings). The figure illustrates a situation in which you would use two values, the production date and the part number, to look up the expected production.

The Info screen in the Excel 2013 Backstage view (Alt+FI) enables you to prepare your workbook for distribution by inspecting the properties of your workbook. To do this, click the Check for Issues button in the Info screen and then select any of the following options: Inspect Document to open the Document Inspector, which checks your documents for hidden content and metadata (data about the document).

The Excel 2013 Backstage view contains a Print screen opened by choosing File→Print or pressing Ctrl+P. This Print screen enables you to do any of the following: Change the number of spreadsheet report copies to be printed (1 copy is the default) by entering a new value in the Copies combo box. Select a new printer to use in printing the spreadsheet report from the Printer drop-down list box.

You can use the Summary button in the Scenario Manager dialog box of Excel 2013 to create a summary report that shows the changing values used in each scenario and, if you want, key resulting values that each produces. When you click the Summary button, Excel opens a Scenario Summary dialog box, where you may designate a cell selection of result cells in the Result Cells text box to be included in the report.

If you’re dealing with an Excel 2013 spreadsheet whose data is of a sensitive nature and should not be modified by anyone who’s authorized to open it, you need to set both a password for opening and a password for modifying the workbook file. You assign either one or both of these types of passwords to a workbook file at the time you save it with the File→Save As command (Alt+FA).

Many times you will want to protect an Excel 2013 workbook that you intend to share on a network. That way, you can allow simultaneous editing of the contents of its worksheets (assuming that you don’t also protect individual sheets), while at the same time preventing anybody but you from removing the Change tracking (and thus deleting the Change History log).

As long as you want to use Excel’s default print settings to print all the cells in the current worksheet, printing in Excel 2013 is a breeze. Simply add the Quick Print button to the Quick Access toolbar by clicking the Customize Quick Access Toolbar button and then choosing the Quick Print item from its drop-down menu.

In Excel 2013 you use the ROUND function found on the Math & Trig command button’s drop-down menu to round up or down fractional values in the worksheet as you might when working with financial spreadsheets that need to show monetary values only to the nearest dollar. Unlike when applying a number format to a cell, which affects only the number’s display, the ROUND function actually changes the way Excel stores the number in the cell that contains the function.

Save customization to any Excel 2013 templates you download to make the workbooks you create from them easier to use and quicker to fill out. For example, you can make your own custom Quarterly Sales Report template from one generated by the downloaded template by filling in your company name and address in the top section and your billing terms and a thank-you message in the bottom sections.

In the course of creating and editing a worksheet in Excel 2013, you may find that you need to modify the worksheet display many times as you work with the document. Excel’s Custom Views feature enables you to save any of these types of changes to the worksheet display. This way, instead of taking the time to manually set up the worksheet display that you want, you can have Excel re-create it for you simply by selecting the view.

If converting your Excel 2013 worksheets to PDF or XPS files is way too complex for your needs, you can save your worksheets as good old HTML files for viewing and printing in anybody’s web browser (as well as for publishing to your website).Choose File→Save As or press Alt+FA to open the Save As screen in the Backstage view.

After going through extensive editing and formatting of one of Excel’s basic chart types, you may want to save your work of art as a custom chart type that you can then use again with different data without having to go through all the painstaking steps to get the chart looking just the way you want it. Excel makes it easy to save any modified chart that you want to use again as a custom chart type.

Excel 2013 makes it easy for you to select cell ranges with a physical or Touch keyboard by using a technique known as extending a selection. To use this technique, you move the cell cursor to the active cell of the range; then press F8 to turn on Extend Selection mode (indicated by Extend Selection on the Status bar) and use the direction keys to move the pointer to the last cell in the range.

Although you usually use the Go To feature in Excel 2013 to move the cell cursor to a new cell in the worksheet, you can also use this feature to select a range of cells. When you choose the Go To option from the Find & Select button’s drop-down menu on the Home tab of the Ribbon (or press Ctrl+G or F5), Excel displays a Go To dialog box.

Excel 2013 isn’t set up to automatically recognize European date formats in which the number of the day precedes the number of the month and year. For example, you may want 6/11/2014 to represent November 6, 1969, rather than June 11, 2014. If you’re working with a spreadsheet that uses this type of European date system, you have to customize the Windows Regional settings for the United States so that the Short Date format in Windows programs, such as Excel and Word 2013, use the D/m/yyyy (day, month, year) format rather than the default M/d/yyyy (month, day, year) format.

You can share files in Excel 2013 in a variety of ways. Keep in mind when making any last-minute changes in a shared workbook, that some of Excel’s editing features become unavailable to you and any others working in the file. One way to share a workbook is by turning on file sharing as follows:Open the workbook to be shared and then make any last-minute edits to the file, especially those that are not supported in a shared workbook.

The Page Break Preview feature in Excel 2013 enables you to spot page break problems in an instant as well as fix them, such as when the program wants to split onto different pages information that you know should always appear on the same page. Here's how you can correct these two bad page breaks in Page Break Preview mode:Click the Page Break Preview button (the third one in the cluster of three to the left of the Zoom slider) on the status bar, or choose View→Page Break Preview on the Ribbon, or press Alt+WI.

You can use the column sorting capability in Excel 2013 to change the order of the fields in a data list without having to resort to cutting and pasting various columns. When you sort the fields in a data list, you add a row at the top of the list that you define as the primary sorting level. The cells in this row contain numbers (from 1 to the number of the last field in the data list) that indicate the new order of the fields.

When you need to sort a data list on more than one field in Excel 2013, you use the Sort dialog box. And you need to sort on more than one field when the first field contains duplicate values and you want to determine how the records with duplicates are arranged. (If you don’t specify another field to sort on, Excel just puts the records in the order in which you entered them.

Excel 2013 enables you to split the active worksheet window into two or four panes. After splitting up the window into panes, you can use the Excel workbook’s horizontal and vertical scroll bars to bring different parts of the same worksheet into view. This is great for comparing the data in different sections of a table that would otherwise not be legible if you zoomed out far enough to have both sections displayed in the worksheet window.

You can use Excel’s Subtotals feature to subtotal data in a sorted list. To subtotal a data 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 data list. When you use the Subtotals feature, you aren’t restricted to having the values in the designated field added together with the SUM function.

The TRANSPOSE function in Excel 2013enables you to change the orientation of a cell range (or an array). You can use this function to transpose a vertical cell range where the data runs down the rows of adjacent columns to one where the data runs across the columns of adjacent rows and vice versa. To successfully use the TRANSPOSE function, not only must you select a range that has an opposite number of columns and rows, but you must also enter it as an array formula.

One way you can share a workbook is by turning on change tracking. When you do this, Excel tracks all changes you make to the contents of the cells in the shared workbook by highlighting their cells and adding comments that summarize the type of change you make. When you turn on change tracking, Excel automatically turns on file sharing along with the workbook’s Change History log.

When you use advanced filtering in Excel 2013, you don’t use the field’s AutoFilter buttons and associated drop-down menu options. Instead, you create a so-called Criteria Range somewhere on the worksheet containing the data list to be filtered before opening the Advanced Filter dialog box. If you use the Advanced Filter feature to do a query, you extract copies of the records that match your criteria by creating a subset of the data list.

The SUM function in Excel 2013 is perfect when you want to get the totals for all the numbers in a particular range or set of ranges. But what about those times when you only want the total of certain items within a cell range? For those situations, you can use the SUMIF or SUMIFS function on the Math & Trig command button’s drop-down menu.

DAX stands for Data Analysis Expression and is the name of the language that PowerPivot for Excel 2013 uses to create calculations between the columns (fields) in your Excel Data Model. Fortunately, creating a calculation with DAX is more like creating an Excel formula that uses a built-in function than it is like using a programming language such as VBA or HTML.

Excel 2013 supports a wide variety of built-in functions that you can use when building formulas. Of course, the most popular built-in function is by far the SUM function, which is automatically inserted when you click the Sum command button on the Home tab of the Ribbon. (Keep in mind that you can also use this drop-down button attached to the Sum button to insert the AVERAGE, COUNT, MAX, and MIN functions.

Excel 2013’s great new Flash Fill feature gives you the ability to take a part of the data entered into one column of a worksheet table and enter just that data in a new table column using only a few keystrokes. The series of entries appear in the new column, literally in a flash (thus, the name Flash Fill), the moment Excel detects a pattern in your initial data entry that enables it to figure out the data you want to copy.

After you assign a name to a cell or cell range in your Excel 2013 worksheet, you can then choose the range name from the Use in Formula button’s drop-down menu on the Ribbon’s Formulas tab to paste it into the formulas that you build (Alt+MS). For example, in the sample Sales Price table shown, after assigning the discount rate of 15% to the range name, discount_rate, you can create the formulas that calculate the amount of the sale discount.

Text functions in Excel 2013 are found on the Text command button’s drop-down menu on the Ribbon’s Formulas tab (Alt+MT). There are two types of text functions: functions such as VALUE, TEXT, and DOLLAR that convert numeric text entries into numbers and numeric entries into text, and functions such as UPPER, LOWER, and PROPER that manipulate the strings of text themselves.

Excel 2013 includes a number of database functions that you can use to calculate statistics, such as the total, average, maximum, minimum, and count in a particular field of the data list only when the criteria that you specify are met. For example, you could use the DSUM function in an Employee data list 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 data list for the Human Resources department.

The Format Painter button (with paintbrush icon) in the Clipboard group of the Home tab of Excel 2013 takes formatting from the current cell and applies it to cells that you “paint” by dragging its special thick-white cross-plus-paintbrush mouse pointer through them. This tool, therefore, provides a quick-and-easy way to take a bunch of different formats (such as a new font, font size, bold, and italics) that you applied individually to a cell in the spreadsheet and then turn around and use them as the guide for formatting a new range of cells.

Excel 2013 makes it simple to create a new pivot table using a data list selected in your worksheet with its new Quick Analysis tool. To preview various types of pivot tables that Excel can create for you on the spot using the entries in a data list that you have open in an Excel worksheet, simply follow these steps:Select all the data (including the column headings) in your data list as a cell range in the worksheet.

The Data Validation feature in Excel 2013 can be a real timesaver when you’re doing repetitive data entry, and can also go a long way in preventing incorrect entries in your spreadsheets. When you use Data Validation in a cell, you indicate what type of data entry is allowed in the cell. As part of restricting a data entry to a number (which can be a whole number, decimal, date, or time), you also specify the permissible values for that type of number (a whole number between 10 and 100 or a date between January 1, 2012, and December 31, 2012, for example).

The most popular of the lookup functions in Excel 2013 are HLOOKUP (for Horizontal Lookup) and VLOOKUP (for Vertical Lookup) functions. These functions are located on the Lookup & Reference drop-down menu on the Formulas tab of the Ribbon as well as in the Lookup & Reference category in the Insert Function dialog box.

Excel 2013 uses a system called Microsoft Authenticode that enables developers to authenticate their macro projects or add-ins created with Visual Basic for Applications by a process referred to as digital signing. When you run a macro in your worksheet that’s not saved in the trusted locations on your computer, such as the Templates and XLSTART folder in your user area on the computer, Excel checks to see whether the macro is digitally signed and that the signature is both valid and current.

Many of the simpler formulas that you build require the sole use of Excel’s operators, which are the symbols that indicate the type of computation that is to take place between the cells and/or constants interspersed between them. Excel uses four different types of operators: arithmetic, comparison, text, and reference.

To be able to omit the filename from the custom functions that you create in Excel 2013 when you enter them directly into a cell, you need to save the workbook file that contains them as a special add-in file. The only limitation to the user-defined functions that you save as part of a regular workbook file or the Personal Macro Workbook file is that when you enter them directly into a cell (without the use of the Insert Function dialog box), you must preface their function names with their filenames.

The first step in setting up a problem in Excel 2013 for the Solver add-in utility to work on is to create the worksheet model for which you will define the objective cell, variables cells, and the constraint cells. To define and solve a problem with the Solver add-in after you’ve loaded the add-in and have created your worksheet model, you follow these steps:Click the Solver command button in the Analysis group at the end of the Ribbon’s Data tab.

Excel 2013 makes it easy to share your spreadsheets with trusted clients and co-workers. If you save your workbook files in the cloud on your SkyDrive, you can easily share their worksheets by inviting co-workers and clients to open them in Excel on their own devices or, if they don’t have Excel, in their web browsers with the Excel web app.

In Excel you normally sort the records of a data list or rows of a table on the values (entries) contained in one or more columns of the list or table; however, Excel 2013 also enables you to sort on the font or fill color or cell icons that you assign to them. These colors and icons are assigned by using the Conditional Formatting feature to mark those values in the columns of a data list or table that are within or outside certain parameters with a distinctive font or fill colors or cell icon.

Excel 2013 includes other special Date functions in the Date and Time category in the Insert Function dialog box. These particular Date functions expand your abilities to do date calculations in the worksheet — especially those that work only with normal workdays, Monday through Friday. EDATE function The EDATE (for Elapsed Date) function calculates a future or past date that is so many months ahead or behind the date that you specify as its start_date argument.

Diagram View is among the most useful features for data modeling offered by the Excel 2013 PowerPivot add-in. When you switch from the default Data View to Diagram View either by clicking the Diagram View button on the Ribbon or the Diagram button in the lower-right corner, all the data tables used in the Data Model are graphically displayed in the PowerPivot window.

Excel 2013 offers a document recovery feature that can help you in the event of a computer crash because of a power failure or some sort of operating system freeze or shutdown. The AutoRecover feature saves your workbooks at regular intervals. In the event of a computer crash, Excel displays a Document Recovery Task pane the next time you start Excel after rebooting the computer.

The Excel 2013 PMT function on the Financial button’s drop-down menu on the Formulas tab of the Ribbon 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.

In Excel 2013, the PowerPivot add-in, introduced in Excel 2010, that enables you to efficiently work with and analyze large datasets (such as those with hundreds of thousands or even millions of records) has been made a much more integral part the program. In fact, the PowerPivot technology that makes it possible for Excel to easily manage massive amounts of data from many related data tables is now part and parcel of Excel 2013 in the form of its Data Model feature.

In Excel 2013, when you click the Select Data command button on the Design tab of the Chart Tools contextual tab (or press Alt+JCE), Excel opens a Select Data Source dialog box. The controls in this dialog box enable you to make the following changes to the source data: Modify the range of data being graphed in the chart by clicking the Chart Data Range text box and then making a new cell selection in the worksheet or typing in its range address.

When dealing with complex problems in Excel 2013, you might need to use the Solver add-in. For example, use the Solver to find the best solution when you need to change multiple input values in your model and you need to impose constraints on these values and/or the output value. The Solver add-in works by applying iterative methods to find the “best” solution given the inputs, desired solution, and the constraints that you impose.

Excel 2013 offers far fewer Time functions when compared with the wide array of Date functions. Like the Date functions, however, the Time functions enable you to convert text entries representing times of day into time serial numbers so that you can use them in calculations. The Time functions also include functions for combining different parts of a time into a single serial time number, as well as those for extracting the hours, minutes, and seconds from a single time serial number.

Instead of copying cell ranges from one workbook to another, you can move (or copy) entire worksheets between workbooks in Excel 2013. You can do this with drag-and-drop or by choosing the Move or Copy Sheet option from the Format command button’s drop-down menu on the Ribbon’s Home tab. To use drag-and-drop to move a sheet between open windows, you simply drag its sheet tab from its window to the place on the sheet tabs in the other window where the sheet is to be moved to.

An array formula in Excel 2013 (and other spreadsheets) is a special formula that operates on a range of values. If a cell range supplies this range (as is often the case), it is referred to as an array range. If this range is supplied by a list of numerical values, they are known as an array constant. Many spreadsheet tables use an original formula that you copy to adjacent cells by using relative cell references (sometimes referred to as a one-to-many copy).

Where the heck did they stick this Visual Basic Editor that you’ve heard so much about? Actually, the Visual Basic Editor in Excel 2013 is always ready to step forward whenever you press Alt+F11 or click the Visual Basic command button on the Developer tab or press Alt+LV when this optional tab is displayed on the Ribbon.

Instead of creating logical formulas in Excel 2013 to suppress the display of potential error values, you can use Conditional Formatting to deal with them. All you have to do is create a new conditional formatting rule that displays all potential error values in a white font (essentially, rendering them invisible in the cells of your worksheet).

After you have the skill in the VBA language, you can write new macros from scratch in the Visual Basic Editor instead of just editing ones that you’ve previously recorded in your spreadsheet by using Excel’s macro recorder. When creating a macro from scratch in the Visual Basic Editor, you need to follow these general steps: Click the name of the VBA project in the Project Explorer window where you want to add the new macro.