Excel 2016 All-in-One For Dummies book cover

Excel 2016 All-in-One For Dummies

By: Greg Harvey Published: 11-16-2015

Your one-stop guide to all things Excel 2016

Excel 2016 All-in-One For Dummies, the most comprehensive Excel reference on the market, is completely updated to reflect Microsoft's changes in the popular spreadsheet tool. It offers you everything you need to grasp basic Excel functions, such as creating and editing worksheets, setting up formulas, importing data, performing statistical functions, editing macros with Visual Basic—and beyond. In no time, your Excel skills will go from 'meh' to excellent.

Written by expert Greg Harvey, who has sold more than 4.5 million copies of his previous books combined and has taught and trained extensively in Microsoft Excel, this all-encompassing guide offers everything you need to get started with Excel. From generating pivot tables and performing financial functions to performing error trapping and building and running macros—and everything in between—this hands-on, friendly guide makes working with Excel easier than ever before.

  • Serves as the ideal reference for solving common questions and Excel pain points quickly and easily
  • Helps to increase productivity and efficiency when working in Excel
  • Fully updated for the new version of Excel
  • Covers basic and more advanced Excel topics

If working in Excel occasionally makes you want to scream, this will be the dog-eared, dust-free reference you'll turn to again and again.

Articles From Excel 2016 All-in-One For Dummies

page 1
page 2
page 3
page 4
page 5
45 results
45 results
Sharing Excel 2016 Workbooks Saved on Your OneDrive

Article / Updated 03-26-2016

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. If you've not yet saved the workbook on your OneDrive, this Share task pane contains a Save to Cloud button that, when clicked, takes you backstage to the Save As screen where you can choose the OneDrive folder where you want it saved. Once the workbook's been saved to the Cloud, the Share task pane contains the Invite People option selected. Click the Invite People text box and then begin typing the e-mail address of the first person with whom you want to share the workbook. As you type, Excel matches the letters with the names and e-mail addresses entered in your Address book. When it finds possible matches, they are displayed in a drop-down menu, and you can select the person's e-mail address by clicking his or her name in the list. To find e-mail addresses in your Address list and add them to this text box, click the Search the Address Book for Contacts button (to the immediate left of the Can Edit drop-down list box) and then use the options in the Address Book: Global Address List dialog box. To share the workbook with multiple people, type a semicolon (;) after each e-mail address you add to this text box. (Optional) Click the Can Edit drop-down button and choose Can View option from the menu to prevent the people you invite from making any changes to the workbook you're sharing. By default, Excel 2016 allows the people with whom you share your workbooks to make editing changes to the workbook that are automatically saved on your OneDrive. If you want to restrict your recipients to reviewing the data in a read-only version without being able to make changes to the file, be sure to replace the Can Edit option with Can View before sharing the workbook. (Optional) Click the Include a Personal Message with the Invitation text box and type in any personal message that you want to incorporate as part of the e-mail with the generic invitation to share the file. By default, Excel creates a generic invitation. (Optional) Select the Require User to Sign-In before Accessing Document check box if you want the people with whom you share the workbook to have to log into a Windows Live account before they can open the workbook. Don't select this check box unless you're giving your log-in information to the recipient(s) of the e-mail invitation, and don't give this log-in information to anyone who isn't in your inner circle or isn't someone you trust completely. Click the Share button in the Share task pane. As soon as you click this Share button, Excel e-mails the invitation to share the workbook to each of the recipients entered in the Type Name or E-Mail Addresses text box. The program also adds the e-mail address and the editing status of each recipient (Can Edit or Can View) in the Shared With section at the bottom of the Share screen. All the people with whom you share a workbook receive an e-mail message containing a hyperlink to the workbook on your OneDrive. When they follow this link (and sign into the site if this is required), a copy of the workbook opens on a new page in their default web browser using the Excel Online web app. If you've given the user permission to edit the file, the web app contains an Edit Workbook drop-down button. When the user clicks this button in Excel Online, he or she has a choice between choosing the Edit in Excel or Edit in Excel Online option from its drop-down menu. When the user chooses Edit in Excel, the workbook is downloaded and opened in his version of Excel. When the user chooses Edit in Excel Online, the browser opens the workbook in a new version of the Excel Online, containing Home, Insert, Data, Review, and View tabs, each with a more limited set of command options than Excel 2016, which you can use in making any necessary changes and which are automatically saved to workbook on the OneDrive when you close Excel Online.

View Article
Sorting on Multiple Fields in an Excel 2016 Data List

Article / Updated 03-26-2016

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.) The best and most common example of when you need more than one field is when sorting a large database alphabetically in last-name order. Say that you have a database that contains several people with the last name Smith, Jones, or Zastrow (as is the case when you work at Zastrow and Sons). If you specify the Last Name field as the only field to sort on (using the default ascending order), all the duplicate Smiths, Joneses, and Zastrows are placed in the order in which their records were originally entered. To better sort these duplicates, you can specify the First Name field as the second field to sort on (again using the default ascending order), making the second field the tie-breaker, so that Ian Smith's record precedes that of Sandra Smith, and Vladimir Zastrow's record comes after that of Mikhail Zastrow. To sort records in a data list using the Sort dialog box, follow these steps: Position the cell cursor in one of the cells in the data list table. Click the Sort button in the Sort & Filter group on the Data tab or press Alt+ASS. Excel selects all the records of the database (without including the first row of field names) and opens the Sort dialog box. Note that you can also open the Sort dialog box by selecting the Custom Sort option on the Sort & Filter drop-down button's menu or by pressing Alt+HSU. Select the name of the field you first want the records sorted by from the Sort By drop-down list. If you want the records arranged in descending order, remember also to select the descending sort option (Z to A, Smallest to Largest, or Oldest to Newest) from the Order drop-down list to the right. (Optional) If the first field contains duplicates and you want to specify how the records in this field are sorted, click the Add Level button to insert another sort level, select a second field to sort on from the Then By drop-down list, and select either the ascending or descending option from its Order drop-down list to its right. (Optional) If necessary, repeat Step 4, adding as many additional sort levels as required. Click OK or press Enter. Excel closes the Sort dialog box and sorts the records in the data list using the sorting fields in the order of their levels in this dialog box. If you see that you sorted the database on the wrong fields or in the wrong order, click the Undo button on the Quick Access toolbar or press Ctrl+Z to immediately restore the data list records to their previous order. By default, when you perform a sort operation, Excel assumes that you're sorting a data list that has a header row (with the field names) that is not to be reordered with the rest of the records in doing the sort. You can, however, use the Sort feature to sort a cell selection that doesn't have such a header row. In that case, you need to specify the sorting keys by column letter, and you need to be sure to deselect the My Data Has Headers check box to remove its check mark in the Sort dialog box.

View Article
Adding a Description to a User-Defined Function in Excel 2016

Article / Updated 03-26-2016

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. To add a description for your user-defined function, follow these steps: Open the Visual Basic Editor from Excel by clicking the Visual Basic button on the Developer tab of the Ribbon or pressing Alt+LV or Alt+F11. Now, you need to open the Object Browser. Choose View→Object Browser from the Visual Basic Editor menu bar or press F2. This action opens the Object Browser window, which obscures the Code window. Click the drop-down list box that currently contains the value and then select VBAProject from the drop-down list. When you select VBAProject from this drop-down list, the Object Browser then displays your user-defined function as one of the objects in one of the Classes in the pane on the left. Right-click the name of your user-defined function. This action selects the function and displays it in the Members pane on the right, while at the same time displaying the object’s shortcut menu. Click Properties on the shortcut menu. This action opens the Member Options dialog box for your user-defined function, where you can enter your description of this function. Type the text that you want to appear in the Insert Function and Function Arguments dialog box for the user-defined function in the Description text box and then click OK. Now, you can close the Object Browser and save your changes. Click the Close Window button to close the Object Browser and then choose the File→Save command.

View Article
Inserting Online Images into Your Excel 2016 Worksheets

Article / Updated 03-26-2016

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. After you press Enter or click the Search button (with the magnifying glass icon), the Insert Pictures dialog box displays a scrollable list of thumbnails for images matching your keyword. You can then click a thumbnail in the list to display a short description plus the size (in pixels) of the image in the lower-left corner of the Insert Pictures dialog box. Below the top row of thumbnail images, the Insert Pictures dialog box also displays a disclaimer informing you that the online images that Bing has returned in the search use what's called Creative Commons. This licensing grants free distribution of what is otherwise copyrighted material under certain conditions (often noncommercial or educational use). The disclaimer then goes on to urge you to review license for any image you insert into your worksheet so that you may be certain that you are in compliance with these conditions (always a good idea). After reading this disclaimer, you can close its text box by clicking its Close button with the x in it. When you click one of the displayed thumbnail images, a short description plus the size (in pixels) for that image is displayed in the lower-left corner of the Insert Pictures dialog box along with a hyperlink to source website (which you can click to visit this site to review the free use conditions of its Creative Commons license) To get a better view of a particular image whose thumbnail is highlighted or selected in the list, click the View Larger button that appears in the thumbnail's lower-right corner (with the magnifying glass with a plus sign in its icon). Excel then displays a slightly larger version of the thumbnail in the center of the dialog box while at the same time blurring out all the other thumbnails in the background. To insert one of the located web images into the current worksheet, double-click its thumbnail if it's not already selected in the list. If the thumbnail is selected, you can insert the image by selecting the Insert button or by pressing Enter. If you use a social media site such as Facebook or the photo-sharing site Flickr on your Windows device, you can add these locations as options to your Insert Pictures dialog box. Simply select the app's icon that appears at the bottom of the Insert Pictures dialog box to connect Office with the particular app. You can then insert images from one of these sites after selecting its option in the Insert Pictures dialog box. You can also download pictures saved in folders on your OneDrive by clicking the Browse button to the right of the OneDrive heading at the bottom of the Insert Pictures dialog box.

View Article
How to Format Tables with the Quick Analysis Tool in Excel 2016

Article / Updated 03-26-2016

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). When you click this tool, the Quick Analysis options palette appears with five tabs (Formatting, Charts, Totals, Tables, and Sparklines). Click the Tables tab in the Quick Analysis tool's option palette to display its Table and Pivot Table buttons. When you highlight the Table button on the Tables tab, Excel's Live Preview shows you how the selected data will appear formatted as a table. (See the following figure.) To apply this previewed formatting and format the selected cell range as a table, you have only to click the Table button. Previewing the selected data formatted as a table with the Quick Analysis tool. As soon as you click the Table button, the Quick Analysis options palette disappears and the Design contextual table appears on the Ribbon. You can then use its Table Styles drop-down gallery to select a different formatting style for your table. (The Tables button on the Quick Analysis tool's Tables tab offers only the one blue medium style shown in the Live Preview.)

View Article
How to Add Sparkline Graphics to a Worksheet in Excel 2016

Article / Updated 03-26-2016

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. Click the type of chart you want for your sparkline (Line, Column, or Win/Loss) in the Sparklines group of the Insert tab or press Alt+NSL for Line, Alt+NSO for Column, or Alt+NSW for Win/Loss. Excel opens the Create Sparklines dialog box, which contains two text boxes: Data Range, which shows the cells you selected with the data you want graphed, and Location Range, where you designate the cell or cell range where you want the sparkline graphic to appear. Select the cell or range of cells where you want your sparkline to appear in the Location Range text box and then click OK. When creating a sparkline that spans more than a single cell, the Location Range must match the Data Range in terms of the same amount of rows and columns. (In other words, they need to be arrays of equal size and shape.) Because sparklines are so small, you can easily add them to the cells in the final column of a table of data. That way, the sparklines can depict the data visually and enhance their meaning while remaining an integral part of the table whose data they epitomize. The figure shows you a worksheet data table after adding sparklines to the table's final column. These sparklines depict the variation in the sales over four quarters as tiny line graphs. As you can see in this figure, when you add sparklines to your worksheet, Excel 2016 adds a Design tab to the Ribbon under Sparkline Tools. Sparklines graphics representing the variation in the data in a worksheet table as tiny Line charts. This Design tab contains buttons that you can use to edit the type, style, and format of the sparklines. The final group (called Group) on this Design tab enables you to band together a range of sparklines into a single group that can share the same axis and/or minimum or maximum values (selected using the options on its Axis drop-down button). This is very useful when you want a collection of different sparklines to all share the same charting parameters so that they equally represent the trends in the data.

View Article
How to Change Common Calculation Options on the Formulas Tab in Excel 2016

Article / Updated 03-26-2016

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. Choose from the following items: Automatic option button (the default) to have Excel recalculate all formulas immediately after you modify any of the values on which their calculation depends. Automatic Except for Data Tables option button to have Excel automatically recalculate all formulas except for those entered into what-if data tables you create. To update these formulas, you must click the Calculate Now (F9) or the Calculate Sheet (Shift+F9) command button on the Formulas tab of the Ribbon. Manual option button to switch to total manual recalculation, whereby formulas that need updating are recalculated only when you click the Calculate Now (F9) or the Calculate Sheet (Shift+F9) command button on the Formulas tab of the Ribbon. Enable Iterative Calculation check box to enable or disable iterative calculations for formulas that Excel finds that it cannot solve on the first try. Maximum Iterations text box to change the number of times (100 is the default) that Excel recalculates a seemingly insolvable formula when the Enable Iterative Calculation check box contains a check mark by entering a number between 1 and 32767 in the text box or by clicking the spinner buttons. Maximum Change text box to change the amount by which Excel increments the guess value it applies each time the program recalculates the formula in an attempt to solve it by entering the new increment value in the text box. The Working with Formulas section contains four check box options that determine a variety of formula-related options: R1C1 Reference Style check box (unchecked by default) to enable or disable the R1C1 cell reference system whereby both columns and rows are numbered as in R45C2 for cell B45. Formula AutoComplete check box (checked by default) to disable or re-enable the Formula AutoComplete feature whereby Excel attempts to complete the formula or function you’re manually building in the current cell. Use Table Names in Formulas check box (checked by default) to disable and reenable the feature whereby Excel automatically applies all range names you’ve created in a table of data to all formulas that refer to their cells. Use GetPivotData Functions for PivotTable References check box (checked by default) to disable and reenable the GetPivotTable function that Excel uses to extract data from various fields in a data source when placing them in various fields of a pivot table summary report you’re creating. The remaining options on the Formulas tab of the Excel Options dialog box enable you to control error-checking for formulas. In the Error Checking section, the sole check box, Enable Background Error Checking, which enables error-checking in the background while you’re working in Excel, is checked. In the Error Checking Rules, all of the check boxes are checked, with the exception of the Formulas Referring to Empty Cells check box, which indicates a formula error when a formula refers to a blank cell. To disable background error checking, click the Enable Background Error Checking check box in the Error Checking section to remove its check mark. To change the color used to indicate formula errors in cells of the worksheet (when background error checking is engaged), click the Indicate Errors Using This Color drop-down button and click a new color square on its drop-down color palette. To remove the color from all cells in the worksheet where formula errors are currently indicated, click the Reset Ignore Errors button. To disable other error-checking rules, click their check boxes to remove the check marks.

View Article
How to Consolidate Worksheets in Excel 2016

Article / Updated 03-26-2016

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. If you used a template to create each worksheet you're consolidating, or an identical layout, Excel can quickly consolidate the values by virtue of their common position in their respective worksheets. However, even when the data entries are laid out differently in each spreadsheet, Excel can still consolidate them provided that you've used the same labels to describe the data entries in their respective worksheets. Most of the time, you want to total the data that you're consolidating from the various worksheets. By default, Excel uses the SUM function to total all the cells in the worksheets that share the same cell references (when you consolidate by position) or that use the same labels (when you consolidate by category). You can, however, have Excel use any of other following statistical functions when doing a consolidation: AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, VAR, or VARP. To begin consolidating the sheets in the same workbook, you select a new worksheet to hold the consolidated data. (If need be, insert a new sheet in the workbook by clicking the Insert Worksheet button.) To begin consolidating sheets in different workbooks, open a new workbook. If the sheets in the various workbooks are generated from a template, open the new workbook for the consolidated data from that template. Before you begin the consolidation process on the new worksheet, you choose the cell or cell range in this worksheet where the consolidated data is to appear. (This range is called the destination area.) If you select a single cell, Excel expands the destination area to columns to the right and rows below as needed to accommodate the consolidated data. If you select a single row, the program expands the destination area down subsequent rows of the worksheet, if required to accommodate the data. If you select a single column, Excel expands the destination area across columns to the right, if required to accommodate the data. If, however, you select a multi-cell range as the destination area, the program does not expand the destination area and restricts the consolidated data just to the cell selection. If you want Excel to use a particular range in the worksheet for all consolidations you perform in a worksheet, assign the range name Consolidate_Area to this cell range. Excel then consolidates data into this range whenever you use the Consolidate command. When consolidating data, you can select data in sheets in workbooks that you've opened in Excel or in sheets in unopened workbooks stored on disk. The cells that you specify for consolidation are referred to as the source area, and the worksheets that contain the source areas are known as the source worksheets. If the source worksheets are open in Excel, you can specify the references of the source areas by pointing to the cell references (even when the Consolidate dialog box is open, Excel will allow you to activate different worksheets and scroll through them as you select the cell references for the source area). If the source worksheets are not open in Excel, you must type in the cell references as external references, following the same guidelines you use when typing a linking formula with an external reference (except that you don't type =). For example, to specify the data in range B4:R21 on Sheet1 in a workbook named CG Music - 2014 Sales.xlsx as a source area, you enter the following external reference: '[CG Music – 2014 Sales.xlsx]Sheet1'!$b$4:$r$21 Note that if you want to consolidate the same data range in all the worksheets that use a similar filename (for example, CG Music - 2012 Sales, CG Music - 2013 Sales, CG Music - 2014 Sales, and so on), you can use the asterisk (*) or the question mark (?) as wildcard characters to stand for missing characters as in '[CG Music - 20?? Sales.xlsx]Sheet1'!$B$4:$R$21 In this example, Excel consolidates the range A2:R21 in Sheet1 of all versions of the workbooks that use "CG - Music - 20" in the main file when this name is followed by another two characters (be they 12, 13, 14, 15, and so on). When you consolidate data, Excel uses only the cells in the source areas that contain values. If the cells contain formulas, Excel uses their calculated values, but if the cells contain text, Excel ignores them and treats them as though they were blank (except in the case of category labels when you're consolidating your data by category).

View Article
How to Create 3-D Power Maps in Excel 2016

Article / Updated 03-26-2016

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. Position the cell cursor in one of the cells in the data list and then click Insert→Map→Open Power Map (Alt+NSMO) on the Excel Ribbon. Excel opens a Power Map window with a new Tour (named Tour 1) with its own Ribbon with a single Home tab similar to the one shown here. Power Map window with new Clustered Column 3-D animation for visualizing the quantities ordered of various Northwind items between July1996 and May 1998 at various locations in western Canada and United States. This window is divided into three panes. The Layer pane on the right contains an outline of the default Layer 1 with three areas: Data, Filters, and Layer Options. The Data area in the Layer Pane is automatically expanded to display a Location, Height, Category, and Time list box. The central pane contains a 3-D globe on which your data will be mapped. A floating Field List containing fields in the selected Excel data model initially appears over this 3-D globe. The left Tour Editor pane contains thumbnails of all the tours and their scenes animated for your data model in Power Map (by default, there is just one scene marked Scene 1 when you create your first tour). Drag fields from the floating Field List to the Location, Height, Category, and Time list boxes in the Layer Pane to build your map. Drag the geographical fields whose location data are to be represented visually on the globe map and drop them into the Location list box in the Layer Pane. Power Map displays data points for each location field for your animation on the 3-D globe as you drop it into the Location list box. The program associates the selected location field with a geographical type in the drop-down list box to the right of the field name in the Location list box in the Layer pane. You can modify the type by selecting its drop-down button, if necessary. Just keep in mind that each location field needs to have a unique geographical type. You also add fields from the floating Field List that you want depicted in the animation to the Height, Size, or Value list boxes (depending upon the type of visualization selected). Select the type of visualization by clicking its icon under the Data heading in the Layer Pane: Stacked Column (default), Clustered Column, Bubble, Heat Map, or Region. Power Map now displays data points for your Height, Size, or Value data on the 3-D globe appropriate to the type of visualization selected along with a floating legend for the data values (organized by any fields used as categories) in the center pane of the Power Map window. At the bottom of the map, you see a Time Line control with a play button that enables you to play and control the animation. (Optional) Click the Map Labels button on the Ribbon to add country and city names to the maps on your 3-D globe. (Optional) Click the Close the Layer Pane button and Close the Tour Editor button to hide the display of Layer and Tour Editor panes, respectively. Now, your 3-D globe with the Layer 1 legend on the right side and animation timeline below fill the entire window below the Power Map Ribbon. Note that you can redisplay the Layer pane and the Tour Editor pane in the Power Map window at any time by clicking the Layer Pane or Tour Editor Ribbon buttons, respectively. (Optional) Drag the Layer 1 legend so that it's not obstructing your 3-D globe. You can also resize the legend by selecting it and then dragging its sizing handles. If the Time Line animation control is obstructing key areas of the globe, you can hide it by clicking its Close button. You can redisplay the Time Line control at any time by clicking the Time Line button in the Time group on the Power Map Ribbon. Note that you can't reposition or resize the Time Line control when it is displayed and that you can play your animation by clicking the Play Tour button on the Ribbon when the Time Line control is hidden. (Optional) Drag the globe to display the area of the world with the locations you want to watch when you play your animation or use the Rotate Left, Rotate Right, Tilt Up, or Tilt Down buttons to bring this area into view. Then, click the Zoom In (Shift+ +) or Zoom Out (Shift + –) to bring the area closer into view or further away. Once you have the viewing window beneath the Power Map Ribbon positioned the way you want it when viewing your animation, you are ready to play the 3-D map tour you've created. Click the Play Tour button on the Ribbon or the Play button on the Time Line control (if it's still visible). When you click the Play Tour button on the Ribbon, Power Map automatically hides the Tour Editor and Layer pane along with the Time Line control if they are still visible at that time. You can pause the animation by clicking the Pause button that appears in a cluster of controllers in a bar at the bottom of the screen or by pressing the spacebar on your keyboard. When you're finished watching the animation, return to the regular editing view of Power Map by clicking the Go Back to Edit View button (the one with the arrow pointing left at the very beginning of the bar at the bottom of the screen) or press the Esc key on your keyboard. Click the Close button in the far right corner of the Power Map title bar to close Power Map and return to your Excel worksheet and then save the workbook (Ctrl+S) to save your Power Map tour as part of the workbook file. After you create your initial animation tour for the data model in your Excel workbook, you can always replay it simply by reopening it in Power Map and clicking Play Tour on its Ribbon. To reopen the tour when the workbook with the data model is open in Excel, select Insert→Map→Open Power Map on the Excel Ribbon (Alt+NSMO) and then click the Tour 1 button at the top of the Launch Power Map dialog box.

View Article
How to Create Custom AutoFill Lists in Excel 2016

Article / Updated 03-26-2016

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. Instead of copying the list of cities from one workbook to another, you can create a custom list containing all the cities in the order in which they normally appear in your spreadsheets. After you create a custom list in Excel, you can then enter all or part of the entries in the series simply by entering the first item in a cell and then using the Fill handle to extend out the series either down a column or across a row. To create a custom series, you can either enter the list of entries in the custom series in successive cells of a worksheet before you open the Custom Lists dialog box, or you can type the sequence of entries for the custom series in the List Entries list box located on the right side of the Custom Lists tab in this dialog box, as shown. Creating a custom list of cities for AutoFill. If you already have the data series for your custom list entered in a range of cells somewhere in a worksheet, follow these steps to create the custom list: Click the cell with the first entry in the custom series and then drag the mouse or Touch pointer through the range until all the cells with entries are selected. The expanded cell cursor should now include all the cells with entries for the custom list. Select File→Options→Advanced (Alt+FTA) and then scroll down and click the Edit Custom Lists button located in the General section. The Custom Lists dialog box opens with its Custom Lists tab, where you now should check the accuracy of the cell range listed in the Import List from Cells text box. (The range in this box lists the first cell and last cell in the current selected range separated by a colon — you can ignore the dollar signs following each part of the cell address.) To check that the cell range listed in the Import List from Cells text box includes all the entries for the custom list, click the Collapse Dialog Box button, located to the right of the Import List from Cells text box. When you click this button, Excel collapses the Custom Lists dialog box down to the Import List from Cells text box and puts a marquee (the so-called marching ants) around the cell range. If this marquee includes all the entries for your custom list, you can expand the Custom Lists dialog box by clicking the Expand Dialog box button (which replaces the Collapse Dialog Box button) and proceed to Step 3. If this marquee doesn't include all the entries, click the cell with the first entry and then drag through until all the other cells are enclosed in the marquee. Then, click the Expand Dialog box button and go to Step 3. Click the Import button to add the entries in the selected cell range to the List Entries box on the right and to the Custom Lists box on the left side of the Custom Lists tab. As soon as you click the Import button, Excel adds the data entries in the selected cell range to both the List Entries and the Custom Lists boxes. Select the OK button twice, the first time to close the Custom Lists dialog box and the second to close the Excel Options dialog box. If you don't have the entries for your custom list entered anywhere in the worksheet, you have to follow the second and third steps listed previously and then take these three additional steps instead: Click the List Entries box and then type each of the entries for the custom list in the order in which they are to be entered in successive cells of a worksheet. Press the Enter key after typing each entry for the custom list so that each entry appears on its own line in the List Entries box, or separate each entry with a comma. Click the Add button to add the entries that you've typed into the List Entries box on the right to the Custom Lists box, located on the left side of the Custom Lists tab. Note that when Excel adds the custom list that you just typed to the Custom Lists box, it automatically adds commas between each entry in the list — even if you pressed the Enter key after making each entry. It also automatically separates each entry on a separate line in the List Entries box — even if you separated them with commas instead of carriage returns. Click the OK button twice to close both the Custom Lists box and Excel Options dialog box. After you've created a custom list by using one of these two methods, you can fill in the entire data series by entering the first entry of the list in a cell and then dragging the Fill handle to fill in the rest of the entries. If you ever decide that you no longer need a custom list that you've created, you can delete it by clicking the list in the Custom Lists box in the Custom Lists dialog box and then clicking the Delete button. Excel then displays an alert box indicating that the list will be permanently deleted when you click OK. Note that you can't delete any of the built-in lists that appear in this list box when you first open the Custom Lists dialog box. Keep in mind that you can also fill in any part of the series by simply entering any one of the entries in the custom list and then dragging the Fill handle in the appropriate direction (down and to the right to enter succeeding entries in the list or up and to the left to enter preceding entries).

View Article
page 1
page 2
page 3
page 4
page 5