Excel 2016 All-in-One For Dummies

To help your user understand the purpose of your custom functions, 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.

Excel 2016 supports a type of information graphic called sparklines that represents trends or variations in collected data. Sparklines — invented by Edward Tufte — are tiny graphs (generally about the size of text that surrounds them). In Excel 2016, sparklines are the height of the worksheet cells whose data they represent and can be any one of following three chart types: Line that represents the selected worksheet data as a connected line showing whose vectors display their relative value Column that represents the selected worksheet data as tiny columns Win/Loss that represents the selected worksheet data as a win/loss chart whereby wins are represented by blue squares that appear above the red squares representing the losses To add sparklines to the cells of your worksheet, you follow these general steps: Select the cells in the worksheet with the data you want represented by a sparkline.

Excel's AutoSelect feature provides a particularly efficient way to select all or part of the cells in a large table of data. AutoSelect automatically extends a selection in a single direction from the active cell to the first nonblank cell that Excel encounters in that direction. You can use the AutoSelect feature with the mouse and a physical keyboard.

The options on the Formulas tab of the Excel 2016 Options dialog box (File→Options→Formulas or Alt+FTF) are divided into Calculation Options, Working with Formulas, Error Checking, and Error Checking Rules. The Formulas tab’s options enable you to change how formulas in the spreadsheet are recalculated. The Calculation options enable you to change when formulas in your workbook are recalculated and whether and how a formula that Excel cannot solve on the first try (such as one with a circular reference) is recalculated.

Excel 2016 allows you to consolidate data from different worksheets into a single worksheet. Using the program's Consolidate command button on the Data tab of the Ribbon, you can easily combine data from multiple spreadsheets. For example, you can use the Consolidate command to total all budget spreadsheets prepared by each department in the company or to create summary totals for income statements for a period of several years.

Power Map is the name of an exciting new visual analysis feature in Excel 2016 that enables you to use geographical, financial, and other types of data along with date and time fields in your Excel data model to create animated 3-D map tours. To create a new animation for the first tour in Power Map, you follow these general steps: Open the worksheet that contains the data for which you want to create the new Power Map animation.

Just as you can use Excel 2016's AutoFill 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.

Excel 2016 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 new Forecast Sheet feature in Excel 2016 makes it super easy to turn a worksheet containing historical financial data into a remarkable visual forecast worksheet. All you do is open the worksheet with your historical data, position the cell cursor in one its cells, and then click the Forecast Sheet button on the Data Tab of the Ribbon (Alt+AFC).

You don't have to live with just the predefined styles that Excel 2016 gives you on the Cell Styles gallery because you can readily create custom cell styles of your own. By far the easiest way to create a new custom cell style is by example. When you create a cell style by example, you choose a cell that already displays all the formatting attributes (applied separately using the techniques discussed previously in this chapter) that you want included in the new cell style.

Not all Excel 2016 worksheets come from templates. Many times, you need to create rather unique spreadsheets that aren't intended to function as standard models from which certain types of workbooks are generated. In fact, most of the spreadsheets that you create in Excel may be of this kind, especially if your business doesn't rely on the use of highly standardized financial statements and forms.

The Duplicate Values option on the Highlight Cell Rules continuation menu in Excel 2016 enables you to highlight duplicate values within a selected cell range. To highlight duplicate values in a cell range, follow these steps: Select the range of cells in the worksheet where you want duplicates formatted in a special way.

Microsoft offers Online versions of Word, Excel, PowerPoint, and OneNote as part of the Office 365 account that provides you with your OneDrive storage in the cloud. You can use Excel Online to edit worksheets saved on your OneDrive online right within your web browser. Excel Online is very useful when you need to make last-minute edits to a worksheet but don't have access to a device on which a copy of the Excel program is installed.

If you're running Excel 2016 on a touchscreen device that lacks any kind of physical keyboard, such as a Surface 3 tablet without the optional Type Cover, you need to open the Touch keyboard and use it to input your spreadsheet data. To open the Touch keyboard, simply tap the Touch Keyboard button that appears on the right side of the Windows 7,8, or 10 taskbar.

You can use Excel 2016's handy Quick Analysis tool to quickly 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).

For those times when you need to select a subset of an Excel 2016 data table as the range to be charted (as opposed to selecting a single cell within a data table), you can use the Quick Analysis tool to create your chart. Just follow these steps: Click the Quick Analysis tool that appears at the lower-right corner of the current cell selection.

Excel 2016 makes it easy to download pictures from the web using the Bing Image search engine and insert them into your worksheets. To download an image with Bing Image Search, open the Insert Pictures dialog box (Alt+NF) and then select the Search Bing text box, where you type the keyword for the types of images you want to locate.

In addition to using the Excel 2016 Data Validation feature to restrict what kind of data can be entered into cell ranges of a worksheet, you can use it to mark all the data (by circling their cells) that are outside of expected or allowable parameters. To use the Data Validation feature in this way, you follow this general procedure: Select the cell range(s) in the worksheet that need to be validated and marked.

If the device running Excel 2016 also has Microsoft's Skype for Business 2016 online communication software installed on it, you can present your worksheets to the other attendees as part of any online meeting that you organize. To do this, first open the workbook you want to present at the online meeting in Excel 2016 before you select the Present Online option on the Share screen in the program's Share screen on the Backstage view (Alt+FHP).

Excel 2016 makes it possible to query data lists (tables) stored in external databases to which you have access and then extract the data that interests you into your worksheet for further manipulation and analysis. Excel 2016 also makes it easy to acquire data from a variety of different data sources, including Microsoft Access database files, web pages on the Internet, text files, and other data sources such as database tables on SQL Servers and Analysis Services, XML data files, and data tables from online connections to Microsoft Windows Azure DataMarket and OData Data feeds.

You use the Excel 2016 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.

In the course of creating and editing an Excel 2016 worksheet, 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.

Although you usually use the Go To feature in Excel 2016 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 similar to the one shown.

If you have access to Skype IM (Instant Message) or have Microsoft's Skype for Business software installed on the device running Excel 2016, you can share a workbook saved on your OneDrive by sending a link to co-worker or client via instant messaging. To do this, simply open the workbook saved on your OneDrive in Excel 2016 and then select the Send by Instant Message option on the Share screen in the Excel Backstage view (Alt+FHIM).

If you have a lot of decimal numbers to enter into an Excel 2016 spreadsheet (suppose that you're building a financial spreadsheet with loads of dollars and cents entries), you may want to use Excel's Fixed Decimal Places feature so that Excel places a decimal point in all the numbers that you enter in the worksheet.

To fill out a data series using your finger or stylus when using Excel 2016 on a touchscreen tablet without access to a mouse or touchpad, you use the AutoFill button that appears on the touchscreen mini-toolbar as follows: Tap the cell containing the initial value in the series you want AutoFill to extend. Excel selects the cell and displays selection handles (with circles) in the upper-left and lower-right corners.

Excel 2016 contains a number of built-in Date functions that you can use in your spreadsheets. When you install and activate the Analysis ToolPak add-in, you have access to a number of additional Date functions — many of which are specially designed to deal with the normal Monday through Friday, five-day workweek (excluding, of course, your precious weekend days from the calculations).

Excel 2016 lets you choose from four different depreciation functions, each of which uses a slightly different method for depreciating an asset over time. These built-in depreciation functions found on the Financial button's drop-down menu on the Formulas tab of the Ribbon include the following: SLN(cost,salvage,life) to calculate straight-line depreciation SYD(cost,salvage,life,per) to calculate sum-of-years-digits depreciation DB(cost,salvage,life,period,[month]) to calculate declining balance depreciation DDB(cost,salvage,life,period,[factor]) to calculate double-declining balance depreciation As you can see, with the exception of the optional month argument in the DB function and the optional factor argument in the DDB function, all the depreciation functions require the cost, salvage, and life arguments, and all but the SLN function require a period argument as well: Cost is the initial cost of the asset that you're depreciating.

Office Add-ins aren't the only ones that you can use to extend Excel 2016's built-in features in some way. You can also use built-in add-ins created by Microsoft or third-party Excel add-ins that you can purchase from a wide variety of vendors. Before you can use any Excel add-in program, the add-in must be installed in the proper folder on your hard drive, and then you must select the add-in in the Add-Ins dialog box.

Excel 2016's handy 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.

The IS information functions in Excel 2016 (as in ISBLANK, ISERR, 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. For example, if you use the ISBLANK function to test the contents of cell A1 as in =ISBLANK(A1) Excel returns TRUE to the cell containing the formula when A1 is empty and FALSE when it's occupied by any type of entry.

The Number Format section near the bottom of the Cell Styles gallery in Excel 2016 (see the figure) contains the following five predefined styles that you can use to format the values entered into the cell selection as follows: Comma sets the number format to the Comma Style (same as clicking the Comma Style command button in the Number group of the Home tab).

Excel 2016 supports the use of Office Add-ins to help you build your worksheets. Office Add-ins are small application programs that run within specific Office 2016 programs, such as Excel, and increase particular functionality to promote greater productivity. There are Office Add-ins to help you learn about Excel's features, look up words in the Merriam-Webster dictionary, and even enter dates into your spreadsheet by selecting them on a calendar.

You can use the options on the Excel 2016's Paste button's drop-down menu or use the options in the Paste Special dialog box (by choosing Paste Special from this drop-down menu or pressing Alt+HVS) to control what information is pasted into the paste range. Normally, when you paste worksheet data from the Clipboard, Excel 2016 pastes all the information (entries, formatting, and comments) from the cell selection into the designated paste area, thus replacing any existing entries in the cells that are overlaid.

The Excel 2016 reference functions on the Lookup & Reference command button's drop-down list on the Formulas tab of the Ribbon are designed to deal specifically with different aspects of cell references in the worksheet. This group of functions includes: ADDRESS to return a cell reference as a text entry in a cell of the worksheet AREAS to return the number of areas in a list of values (areas are defined as a range of contiguous cells or a single cell in the cell reference) COLUMN to return the number representing the column position of a cell reference COLUMNS to return the number of columns in a reference FORMULATEXT to return the formula referenced as a text string GETPIVOTDATA to return data stored in an Excel pivot table HYPERLINK to create a link that opens another document stored on your computer, network, or the Internet (you can also do this with the Insert→Hyperlink command) INDIRECT to return a cell reference specified by a text string and bring the contents in the cell to which it refers to that cell LOOKUP to return a value from an array OFFSET to return a reference to a cell range that's specified by the number of rows and columns from a cell or a cell range ROW to return the row number of a cell reference ROWS to return the number of rows in a cell range or array RTD to return real-time data from a server running a program that supports COM (Component Object Model) automation TRANSPOSE to return a vertical array as a horizontal array and vice versa Get the skinny on columns and rows The COLUMNS and ROWS functions return the number of columns and rows in a particular cell range or array.

Excel 2016 enables you to create and save sets of input values that produce different results as scenarios with the Scenario Manager option on the What-If Analysis button's drop-down menu on the Data tab of the Ribbon. A scenario consists of a group of input values in a worksheet to which you assign a name, such as Best Case, Worst Case, Most Likely Case, and so on.

Excel 2016 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.

The most popular of the Excel 2016 lookup functions 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.

The AVERAGE, MAX (for maximum), and MIN (for minimum) functions in Excel 2016 are the most commonly used of the statistical functions because they are of use to both the average number cruncher as well as the dedicated statistician. All three functions follow the same syntax as the good old SUM function. For example, the syntax of the AVERAGE function uses the following arguments just as the SUM, MAX, and MIN functions do: AVERAGE(<i>number1</i>,[<i>number2</i>],[.

By activating the Excel 2016 Analysis ToolPak add-in, 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.

The Excel 2016 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.

The PV (Present Value), NPV (Net Present Value), and FV (Future Value) functions in Excel 2016 all found on the Financial button's drop-down menu on the Ribbon's Formulas tab (Alt+MI) enable you to determine the profitability of an investment. Calculating the Present Value The PV, or Present Value, function returns the present value of an investment, which is the total amount that a series of future payments is worth presently.

Excel 2016 makes it easy to download pictures from the web using the Bing Image search engine and insert those images into any of your worksheets. To download an image with Bing Image Search, open the Insert Pictures dialog box (Alt+NF) and then select the Search Bing text box, where you type the keyword for the types of images you want to locate.

You can share any of your Excel 2016 workbooks via OneDrive in the Cloud. To share Excel 2016 workbooks from your OneDrive, you follow these steps: Open the workbook file you want to share in Excel 2016 and then click the Share button at the far right of the row with the Ribbon. Excel opens the Share task pane on the right side of the worksheet area.

When you need to sort a data list on more than one field in Excel 2016, 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.