Excel 2016 For Dummies
Overview
Let your Excel skills sore to new heights with this bestselling guide
Updated to reflect the latest changes to the Microsoft Office suite, this new edition of Excel For Dummies quickly and painlessly gets you up to speed on mastering the world's most widely used spreadsheet tool. Written by bestselling author Greg Harvey, it has been completely revised and updated to offer you the freshest and most current information to make using the latest version of Excel easy and stress-free.
If the thought of looking at spreadsheet makes your head swell, you've come to the right place. Whether you've used older versions of this popular program or have never gotten a headache from looking at all those grids, this hands-on guide will get you up and running with the latest installment of the software, Microsoft Excel 2016. In no time, you'll begin creating and editing worksheets, formatting cells, entering formulas, creating and editing charts, inserting graphs, designing database forms, and more. Plus, you'll get easy-to-follow guidance on mastering more advanced skills, like adding hyperlinks to worksheets, saving worksheets as web pages, adding worksheet data to an existing web page, and so much more.
- Save spreadsheets in the Cloud to work on them anywhere
- Use Excel 2016 on a desktop, laptop, or tablet
- Share spreadsheets via email, online meetings, and social media sites
- Analyze data with PivotTables
If you're new to Excel and want to spend more time on your actual work than figuring out how to make it work for you, this new edition of Excel 2016 For Dummies sets you up for success.
Read More
About The Author
Greg Harvey, PhD is the President of Mind Over Media. Greg wrote his first computer book more than twenty years ago and since that time, he has amassed a long list of bestselling titles including Excel All-In-One For Dummies (all editions) and Excel Workbook For Dummies (all editions).
Sample Chapters
excel 2016 for dummies
CHEAT SHEET
At first glance, you might have trouble making sense of the many menus, tabs, columns, and rows of the Excel 2016 user interface. However, you can figure out what you're doing by using keystrokes to move the cell cursor to a new cell, following simple rules of data-entry etiquette, discovering common causes of some formula error values, and a reading a quick list of the best Excel 2016 features.
View Cheat Sheet
View Cheat Sheet
HAVE THIS BOOK?
View Book Companion Site
Access practice tests
Additional Book Resources
Articles from
the book
Excel
Excel 2016 makes creating a new pivot table a snap with the 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 table or list that you have open in an Excel worksheet, simply follow these steps:
Select the data (including the column headings) in your table or list as a cell range in the worksheet.
Excel
Excel 2016 makes it simple to create a new pivot table using a data list selected in your worksheet with its 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.
You can tell right away that an Excel 2016 formula has gone haywire because instead of a nice calculated value, you get a strange, incomprehensible message. This weirdness, in the parlance of Excel 2016 spreadsheets, is an error value. Its purpose is to let you know that some element — either in the formula itself or in a cell referred to by the formula — is preventing Excel from returning the anticipated calculated value.
To begin to work on a new Excel 2016 spreadsheet, you simply start entering information in the first sheet of the Book1 workbook window. Here are a few simple guidelines (a kind of data-entry etiquette) to keep in mind when you create an Excel spreadsheet in Sheet1 of a new workbook:
Whenever you can, organize your information in tables of data that use adjacent (neighboring) columns and rows.
At first glance, you might have trouble making sense of the many menus, tabs, columns, and rows of the Excel 2016 user interface. However, you can figure out what you're doing by using keystrokes to move the cell cursor to a new cell, following simple rules of data-entry etiquette, discovering common causes of some formula error values, and a reading a quick list of the best Excel 2016 features.
You activate all the Excel 2016 hot keys by pressing down the Alt key before you type the various sequences of mnemonic letters. All the hot key sequences for selecting the most common formula-related commands in Excel 2016x begin with the sequence Alt+M because the M in forMulas was the only mnemonic key still available.
Excel
Hyperlinks automate Excel 2016 worksheets by making the opening of other Office documents and Excel workbooks and worksheets just a mouse click away. It doesn't matter whether these documents are located on your hard drive, a server on your LAN (Local Area Network), or web pages on the Internet or a company's intranet.
In addition to online and local images, you can insert preset graphic shapes in your Excel 2016 chart or worksheet by selecting their thumbnails on the Shapes drop-down gallery on the Insert tab of the Ribbon.
Click the shape's thumbnail on the Shapes drop-down gallery and then drag the mouse pointer or Touch Pointer to draw it out in the chart or sheet.
After creating the field names and one record of the data list and formatting them as a table, you're ready to start entering the rest of its data as records in subsequent rows of the list. The most direct way to do this is to press the Tab key when the cell cursor is in the last cell of the first record.
Doing this causes Excel to add an extra row to the data list where you can enter the appropriate information for the next record.
Excel 2016 makes it a snap to insert additional worksheets in a workbook (up to 255 total) — simply click the Insert Worksheet button that appears to the immediate right of the last sheet tab.
For some of you, the single Excel 2016 worksheet automatically put into each new workbook that you start is as much as you would ever, ever need (or want) to use.
You can add text comments to particular cells in an Excel 2016 worksheet. Comments act kind of like electronic pop-up versions of sticky notes. To add a comment to a cell, follow these steps:
Move the cell pointer to or click the cell to which you want to add the comment.
Click the New Comment command button on the Ribbon's Review tab or press Alt+RC.
By assigning descriptive names to cell ranges, you can keep on top of the location of important information in an Excel 2016 worksheet. When assigning range names to a cell or cell range in Excel 2016, you need to follow a few guidelines:
Range names must begin with a letter of the alphabet, not a number.
For example, instead of 01Profit, use Profit01.
Excel
Excel 2016 automatically keeps a running list of the last 25 files you opened in the Recent Workbooks list on the Open screen when the Recent Workbooks option is selected under Places. If you want, you can have Excel display more or fewer files in this list.
To change the number of recently opened files that appear, follow these simple steps:
Choose File→Options→Advanced or press Alt+FTA to open the Advanced tab of the Excel Options dialog box.
Excel
In Excel 2016 you can use the View Side by Side command button (the one with the picture of two sheets side by side like tiny tablets of the Ten Commandments) on the Ribbon's View tab to quickly and easily do a side-by-side comparison of any two worksheet windows that you have open.
When you click this button (or press Alt+WB after opening two windows), Excel automatically tiles them horizontally (as though you had selected the Horizontal option in the Arrange Windows dialog box), as shown here.
Excel
If you just need to copy a single formula in Excel 2016, use the AutoFill feature or the Copy and Paste commands. This type of formula copy, although quite common, can't be done with drag and drop.
Don't forget the Totals option on the Quick Analysis tool. You can use it to create a row or a column of totals at the bottom or right edge of a data table in a flash.
After creating a pivot table in Excel 2016, you can create a pivot chart to display its summary values graphically by completing two simple steps:
Click the PivotChart command button in the Tools group on the Analyze tab under the PivotTable Tools contextual tab to open the Insert Chart dialog box.
Remember that the PivotTable Tools contextual tab with its two tabs — Analyze and Design — automatically appears whenever you click any cell in an existing pivot table.
Excel
Sometimes, none of the pivot tables that Excel 2016 suggests when creating a new table with the Quick Analysis tool or the Recommended PivotTables command button fit the type of data summary you have in mind.
In such cases, you can either select the suggested pivot table whose layout is closest to what you have in mind, or you can choose to create the pivot table from scratch (a process that isn't all that difficult or time consuming).
Excel
If creating a new pivot table with the Quick Analysis tool in Excel 2016 is too much work for you, you generate them in a snap with the Recommended Pivot Tables command button. To use this method, follow these easy steps:
Select a cell in the data list for which you want to create the new pivot table.
Provided that your data list has a row of column headings with contiguous rows of data, this can be any cell in the table.
Excel
Excel 2016 lets you create your own custom styles to add to the Tables Styles gallery and use in formatting your worksheet tables. Once created, a custom Table Style not only applies just the kind of formatting you want for your worksheet tables but can also be reused on tables of data in any worksheet you create or edit in the future.
Excel
Data tables enable you to enter a series of possible values that Excel 2016 then plugs into a single formula. A one-variable data table substitutes a series of possible values for a single input value in a formula.
The following figure shows a 2017 sales projections spreadsheet for which a one-variable data table is to be created.
Excel
In Excel 2016 a two-variable data table substitutes a series of possible values for two input values in a single formula. To create a two-variable data table, you enter two ranges of possible input values for a formula in the Data Table dialog box.
Enter a range of values for the Row Input Cell across the first row of the table and a range of values for the Column Input Cell down the first column of the table.
In Excel 2016 the Chart Elements button (with the plus sign icon) that appears to the right of an embedded chart when it's selected contains a list of the major chart elements that you can add to your chart. To add an element to your chart, click the Chart Elements button to display an alphabetical list of all the elements, Axes through Trendline.
Excel
In Excel 2016, you can use the command buttons on the Design tab of the Chart Tools contextual tab to make all kinds of changes to your new chart. The Design tab contains the following groups of buttons to use:
Chart Layouts: Click the Add Chart Element button to modify particular elements in the chart such as the titles, data labels, legend, and so on (note that most of the chart element options on this drop-down menu are duplicated on the chart elements palette that appears when you click the Chart Elements button in the worksheet to the right of a selected embedded chart).
Under certain circumstances, even the best formulas in Excel 2016 can appear to have freaked out after you get them in your worksheet. You can tell right away that a formula's gone haywire because instead of the nice calculated value you expected to see in the cell, you get a strange, incomprehensible message in all uppercase letters beginning with the number sign (#) and ending with an exclamation point (!
When Excel 2016 first adds titles to a new chart, it gives them generic names, such as Chart Title and Axis Title (for both the x- and y-axis title). To replace these generic titles with the actual chart titles, click the title in the chart or click the name of the title on the Chart Elements drop-down list.
(Chart Elements is the first drop-down button in the Current Selection group on the Format tab under Chart Tools.
Microsoft offers several Office Online Web apps for Word, Excel, PowerPoint, and OneNote as part of your Windows account and OneDrive storage in the cloud. You can use Excel Online to edit worksheets saved on your OneDrive online right within your web browser.
This comes in real handy for those occasions when you need to make last-minute edits to an Excel 2016 worksheet but don't have access to a device on which Excel 2016 is installed.
You can use the Insert Function button in Excel 2016 to edit formulas that contain functions right from the Formula bar. Select the cell with the formula and function to edit before you select the Insert Function button (the one sporting the fx that appears immediately in front of the current cell entry on the Formula bar).
Entering data into Excel 2016 worksheets is actually fairly simple. Begin by reciting (in unison) the basic rule of worksheet data entry. All together now:
To enter data in a worksheet, position the cell pointer in the cell where you want the data and then begin typing the entry.
Before you can position the cell pointer in the cell where you want the entry, Excel must be in Ready mode (look for Ready as the Program indicator at the beginning of the Status bar).
When building a new worksheet in Excel 2016, you'll probably spend a lot of your time entering numbers, representing all types of quantities from money that you made (or lost) to the percentage of the office budget that went to coffee and donuts. (You mean you don't get donuts?)
To enter a numeric value that represents a positive quantity, like the amount of money you made last year, just select a cell, type the numbers — for example, 459600 — and complete the entry in the cell by clicking the Enter button, pressing the Enter key, and so on.
When you create a new pivot table, you'll notice that Excel 2016 automatically adds drop-down buttons to the Report Filter field. These filter buttons enable you to filter all but certain entries in any of these fields.
Filtering the report
Perhaps the most important filter buttons in a pivot table are the ones added to the field(s) designated as the pivot table FILTERS.
Excel 2016's Filter feature makes it a breeze to hide everything in a data list except the records you want to see. To filter the data list to just those records that contain a particular value, you then click the appropriate field's AutoFilter button to display a drop-down list containing all the entries made in that field and select the one you want to use as a filter.
Excel
In a large data list in Excel 2016, trying to find a particular record by moving from record to record — or even moving ten records at a time with the scroll bar — can take all day. Rather than waste time trying to manually search for a record, you can use the Criteria button in the data form to look it up.
When you click the Criteria button, Excel clears all the field entries in the data form (and replaces the record number with the word Criteria) so that you can enter the criteria to search for in the blank text boxes.
When entering vast quantities of data in Excel 2016, it's easy for those nasty little typos to creep into your work. In your pursuit of the perfect spreadsheet, here are things you can do.
First, get Excel to correct certain data entry typos automatically when they happen with its AutoCorrect feature. Second, manually correct any disgusting little errors that get through, either while you're still in the process of making the entry in the cell or after the entry has gone in.
When an inserted picture is selected in the worksheet, Excel 2016 adds the Pictures Tools contextual tab to the Ribbon with its sole Format tab. The Format tab is divided into four groups: Adjust, Picture Styles, Arrange, and Size.
Clip art image ready for editing in the new worksheet.
The Adjust group contains the following important command buttons:
Remove Background opens the Background Removal tab and makes a best guess about what parts of the picture to remove.
Excel 2016 makes formatting a new pivot table you've added to a worksheet as quick and easy as formatting any other table of data or list of data. All you need to do is click a cell of the pivot table to add the PivotTable Tools contextual tab to the Ribbon and then click its Design tab to display its command buttons.
The Format as Table feature in Excel 2016 is so automatic that the cell cursor just has to be within the table of data prior to you clicking the Format as Table command button in the Styles group on the Home tab.
Clicking the Format as Table command button opens its rather extensive Table Styles gallery with the formatting thumbnails divided into three sections — Light, Medium, and Dark — each of which describes the intensity of the colors used by its various formats.
Panes are great for viewing different parts of the same Excel 2016 worksheet that normally can't be seen together. You can also use panes to freeze headings in the top rows and first columns so that the headings stay in view at all times, no matter how you scroll through the worksheet.
Frozen headings are especially helpful when you work with a table that contains information that extends beyond the rows and columns shown onscreen.
Excel
A funny thing about narrowing columns and rows in Excel 2016: You can get carried away and make a column so narrow or a row so short that it actually disappears from the worksheet! This can come in handy for those times when you don't want part of the worksheet visible.
For example, suppose you have a worksheet that contains a column listing employee salaries — you need these figures to calculate the departmental budget figures, but you would prefer to leave sensitive info off most printed reports.
Excel 2016 makes it easy to insert online graphic images into your worksheet. The Insert Pictures dialog box enables you to use Microsoft's Bing search engine to search the entire web for images to use. If that's not enough, you can also download images that you've saved in the cloud on your Windows OneDrive or your social media sites, such as Facebook or Flickr.
Excel
For those times in Excel 2016 when you need to select a subset of a 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 right below the lower-right corner of the current cell selection.
Excel
In Excel 2016 to the right of the Recommended Charts button in the Charts group of the Ribbon's Insert tab, you find particular command buttons with drop-down galleries for creating the following types and styles of charts:
Insert Column Chart to preview your data as a 2-D or 3-D vertical column chart
Insert Bar Chart to preview your data as a 2-D or 3-D horizontal bar chart
Insert Stock, Surface or Radar Chart to preview your data as a 2-D stock chart (using typical stock symbols), 2-D or 3-D surface chart, or 3-D radar chart
Insert Line Chart to preview your data as a 2-D or 3-D line chart
Insert Area Chart to preview your data as a 2-D or 3-D area chart
Insert Combo Chart to preview your data as a 2-D combo clustered column and line chart or clustered column and stacked area chart
Insert Pie or Doughnut Chart to preview your data as a 2-D or 3-D pie chart or 2-D doughnut chart
Insert Scatter (X,Y) or Bubble Chart to preview your data as a 2-D scatter (X,Y) or bubble chart
When using the galleries attached to these chart command buttons on the Insert tab to preview your data as a particular chart style, you can embed the chart in your worksheet by simply clicking its chart icon.
Although you can enter a function by typing it directly in a cell, Excel 2016 provides an Insert Function command button on the Formula bar you can use to select any of Excel's functions. When you select this button, Excel opens the Insert Function dialog box (shown in the figure) where you can select the function you want to use.
Excel
To make a web page query in Excel 2016, you click the From Web command button on the Data tab of the Ribbon or press Alt+AFW. Excel then opens the New Web Query dialog box containing the Home page for your computer's default web browser (Internet Explorer 10 in most cases). To select the web page containing the data you want to import into Excel, you can:
Type the URL web address in the Address text box at the top of the Home page in the New Web Query dialog box.
As entries go in Excel 2016, formulas are the real workhorses of the worksheet. If you set up a formula properly, it computes the correct answer when you enter the formula into a cell. From then on, the formula stays up to date, recalculating the results whenever you change any of the values that the formula uses.
Pivot tables are much more dynamic than standard Excel 2016 data tables because they remain so easy to manipulate and modify. To modify the fields used in your pivot table, first you display the PivotTable Field List by following these steps:
Click any of the pivot table's cells.
Excel adds the PivotTable Tools contextual tab with the Analyze and Design tabs to the Ribbon.
Excel
In some situations in Excel 2016, you need to move a particular worksheet or copy it from one workbook to another. To move or copy worksheets between workbooks, follow these steps:
Open both the workbook with the worksheet(s) that you want to move or copy and the workbook that is to contain the moved or copied worksheet(s).
Cell names are not only a great way to identify and find cells and cell ranges in your Excel 2016 spreadsheet, but they're also a great way to make out the purpose of your formulas.
For example, suppose that you have a simple formula in cell K3 that calculates the total due to you by multiplying the hours you work for a client (in cell I3) by the client's hourly rate (in cell J3).
Excel
As pivot implies, the fun of pivot tables in Excel 2016 is being able to restructure the table simply by rotating the column and row fields.
For example, suppose that after making the Dept field the column field and the Location field the row field in the example pivot table, you decide you want to see what the table looks like with the Location field as the column field and the Dept field as the row field.
Excel
After adding your scenarios to a table in a spreadsheet, you can have Excel 2016 produce a summary report like the one shown. This report displays the changing and resulting values for not only all the scenarios you've defined, but also the current values that are entered into the changing cells in the worksheet table at the time you generate the report.
After you more or less finalize an Excel 2016 worksheet by checking out its formulas and proofing its text, you often want to guard against any unplanned changes by protecting the document.
Each cell in the worksheet can be locked or unlocked. By default, Excel locks all the cells in a worksheet so that, when you follow these steps, Excel locks the whole thing up tighter than a drum:
Click the Protect Sheet command button in the Changes group on the Review tab on Ribbon or press Alt+RPS.
Excel
To make an external data query to an Access database table from Excel 2016, you click the From Access command button on the Ribbon's Data tab or press Alt+AFA. Excel opens the Select Data Source dialog box where you select the name of the Access database and then click Open.
The Select Table dialog box appears from which you can select the data table that you want to import into the worksheet.
Not only can you read comments left in an Excel 2016 workbook, you also can edit those comments. When you have an Excel 2016 workbook with sheets that contain a bunch of comments, you probably won't want to take the time to position the mouse pointer over each of its cells in order to read each one.
For those times, you need to click the Show All Comments command button on the Ribbon's Review tab (or press Alt+RA).
Excel
In really large Excel 2016 workbooks that contain many completed worksheets, you may want to switch to manual recalculation so that you can control when the formulas in the worksheet are calculated. You need this kind of control when you find that Excel's recalculation of formulas each time you enter or change information in cells has considerably slowed the program's response time to a crawl.
Excel
The Conditional Formatting button in the Styles group of the Home tab in Excel 2016 enables you to apply provisional formatting to a cell range based solely on the categories into which its current values fall. The cool thing about this kind of conditional formatting is that should you edit the numbers in the cell range so that their values fall into other categories, the program automatically changes their cell formatting to suit.
Excel 2016's Scenario Manager option on the What-If Analysis button's drop-down menu on the Data tab of the Ribbon enables you to create and save sets of different input values that produce different calculated results, named scenarios (such as Best Case, Worst Case, and Most Likely Case).
The key to creating the various scenarios for a table is to identify the various cells in the data whose values can vary in each scenario.
Creating a new data list in an Excel 2016 worksheet is much like creating a worksheet table except that it has only column headings and no row headings. To set up a new data list, follow these steps:
Click the blank cell where you want to start the new data list and then enter the column headings (technically known as field names in database parlance) that identify the different kinds of items you need to keep track of (such as First Name, Last Name, Street, City, State, and so on) in the columns to the right.
Before you can share your Excel 2016 workbook, you must save a copy of it in a folder on your OneDrive. The easiest way to do this is to first open the workbook to share in Excel and click the Share button that appears on the right side of the Ribbon.
Excel then opens a Share task pane in your worksheet with a Save to Cloud button.
Excel
If you have access to Skype IM (Instant Message) or have Skype for Business (also known as Lync 2016) software installed on the device running Excel 2016, you can share a workbook saved on your OneDrive by sending a link to a 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).
Excel
A basic printing technique that you may need occasionally is printing the formulas in an Excel 2016 worksheet instead of printing the calculated results of the formulas. You can check over a printout of the formulas in your worksheet to make sure that you haven't done anything stupid (like replace a formula with a number or use the wrong cell references in a formula) before you distribute the worksheet company-wide.
Excel
The Page Break preview feature in Excel 2016 enables you to spot and fix page break problems in an instant, such as when the program wants to split information across different pages that you know should always be on the same page.
This figure shows a worksheet in Page Break Preview with an example of a bad vertical page break that you can remedy by adjusting the location of the page break on Page 1 and Page 3.
Excel
Although zooming in and out on an Excel 2016 worksheet can help you get your bearings, it can't bring together two separate sections so that you can compare their data on the screen (at least not at a normal size where you can actually read the information).
To manage this kind of trick, split the Worksheet area into separate panes and then scroll the worksheet in each pane so that they display the parts you want to compare.
Excel 2016 runs only under Windows 7, 8, and, of course, the new Windows 10 operating system. This means that if your PC is running the old Vista or XP versions of Windows, you must upgrade before you can successfully install and run Excel 2016.
Starting Excel from the Windows 10 Start menu
Windows 10 brings back the good old Start menu that many of you remember from much earlier Windows versions.
Excel
You can create a summary worksheet in Excel 2016 that recaps or totals the values stored in a bunch of other worksheets in the workbook.
Here you create a summary worksheet titled Total Projected Income for the MGE – 2017 Projected Income workbook. This summary worksheet totals the projected revenue and expenses for all the companies that Mother Goose Enterprises operates.
Many of the worksheets that you create with Excel 2016 require the entry of a series of sequential dates or numbers. Excel's AutoFill feature makes short work of this kind of repetitive task. All you have to enter is the starting value for the series.
In most cases, AutoFill is smart enough to figure out how to fill out the series for you when you drag the fill handle to the right (to take the series across columns to the right) or down (to extend the series to the rows below).
For those times when Excel 2016 doesn't automatically adjust the width of your columns to your complete satisfaction, the program makes changing the column widths a breeze. The easiest way to adjust a column is to do a best-fit, using the AutoFit feature. With this method, Excel automatically determines how much to widen or narrow the column to fit the longest entry currently in the column.
The AutoSum tool is in the Excel 2016 Editing group on the Home tab of the Ribbon. Look for the Greek sigma symbol. This little tool is worth its weight in gold when it comes to entering functions.
In addition to entering the SUM, AVERAGE, COUNT, MAX, or MIN functions, it also selects the most likely range of cells in the current column or row that you want to use as the function's argument and then automatically enters them as the function's argument.
Excel 2016 add-in programs are small modules that extend the program's power by giving you access to a wide array of features and calculating functions not otherwise offered in the program. There are three types of add-ins:
Built-in add-ins available when you install Excel 2016
Add-ins that you can download for Excel 2016 from Microsoft's Office Online website
Add-ins developed by third-party vendors for Excel 2016 that often must be purchased
When you first install Excel 2016, the built-in add-in programs included with Excel are fully loaded and ready to use.
Excel 2016's 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 appears in the new column, literally in a flash (thus, its name, Flash Fill).
The second Excel 2016 detects a pattern in your initial data entries, the rest of the entries in that series immediately appear in blank cells in rows below that you can then enter with a single keystroke.
When entering data into Excel 2016, if you find that you need to enter a whole slew of numbers that use the same number of decimal places, you can turn on Excel's Fixed Decimal setting and have the program enter the decimals for you. This feature really comes in handy when you have to enter hundreds of financial figures that all use two decimal places (for example, for the number of cents).
Sometimes when doing what-if analysis in Excel 2016, you have a particular outcome in mind, such as a target sales amount or growth percentage. When you need to do this type of analysis, you use Excel's Goal Seek feature to find the input values needed to achieve the desired goal.
To use the Goal Seek feature located on the What-If Analysis button's drop-down menu, you need to select the cell containing the formula that will return the result you're seeking (referred to as the set cell in the Goal Seek dialog box).
For those of you who don't have the time or patience for adding totals to your Excel 2016 worksheet tables with AutoSum and AutoFill, Excel 2016's Totals feature on the Quick Analysis tool is just the thing. The Quick Analysis tool offers a bevy of features for doing anything from adding conditional formatting, charts, pivot tables, and sparklines to your worksheet tables.
Excel 2016 replaces the Research button on the Ribbon's Review tab from previous versions with a new Smart Lookup button. When you click the Smart Lookup button (or press Alt+RR), Excel opens an Insights task pane (similar to the one shown here) with information about the entry in the current cell of your worksheet under an Explore tab.
Excel 2016 SmartArt is a special type of graphic object that gives you the ability to construct fancy graphical lists and diagrams in your worksheet quickly and easily. SmartArt lists and diagrams come in a wide array of configurations (including a bunch of organizational charts and various process and flow diagrams) that enable you to combine your own text with the predefined graphic shapes.
Excel 2016 supports a special type of information graphic called a sparkline that represents trends or variations in collected data. Sparklines are tiny graphs generally about the size of the text that surrounds them. In Excel 2016, sparklines are the height of the worksheet cells whose data they represent and can be any of the following chart types:
Line that represents the relative value of the selected worksheet data
Column where the selected worksheet data is represented by tiny columns
Win/Loss where the selected worksheet data appears as a win/loss chart; wins are represented by blue squares that appear above red squares (representing the losses)
Sparklines via the Quick Analysis tool
In Excel 2016, you can use its Quick Analysis tool to quickly add sparklines to your data.
Through the use of its themes, Excel 2016 supports a way to format uniformly all the text and graphics you add to a worksheet. You can do this by simply clicking the thumbnail of the new theme you want to use in the Themes drop-down gallery opened by clicking the Themes button on the Page Layout tab of the Ribbon or by pressing Alt+PTH.
Excel
To save paper and your sanity, print your Excel 2016 worksheet directly from the Print screen in Excel's Backstage view by clicking File→Print (or simply pressing Ctrl+P or Ctrl+F2). As you see here, the Print screen shows you at-a-glance your current print settings along with a preview of the first page of the printout.
For those occasions when you feel the urge to format on the fly (so to speak) in Excel 2016, use the Format Painter button (the paintbrush icon) in the Clipboard group on the Home tab. This wonderful little tool enables you to take the formatting from a particular cell that you fancy up and apply its formatting to other cells in the worksheet simply by selecting those cells.
Excel
One of Excel 2016's niftiest new features is the Tell Me help feature available from the Tell Me What You Want to Do text box located to the immediate right of the last command tab above the Excel ribbon. As you enter a help topic into this text box, Excel displays a list of related Excel commands in a drop-down list.
Excel 2016 offers a wide variety of keystrokes for moving the cell cursor to a new cell. When you use one of these keystrokes, the program automatically scrolls a new part of the worksheet into view, if this is required to move the cell pointer.
The following table summarizes these keystrokes, including how far each one moves the cell pointer from its starting position.
Excel 2016 makes it easy for you to select cell ranges with a physical 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.
Excel 2016 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.
If you’re looking for a quick rundown on what’s cool in Excel 2016, look no further! Just a cursory glance down the first few items in this list tells you that the thrust of the features is being able to be productive with Excel 2016 anytime, anywhere!
Complete Cloud file support: The new Excel Save (File→Save) and Open (File→Open) screens make it a snap to add your OneDrive or company’s SharePoint team site as a place to store and edit your favorite workbooks.
Excel
You can activate the Excel 2016 hot keys by pressing the Alt key before you type the various sequences of mnemonic letters. The mnemonic letter is F (for File) for the commands on the Excel 2016 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.
Excel
Normally, unless you fool around with the Paste Options, Excel 2016 copies all the information in the range of cells you selected: formatting, as well the formulas, text, and other values you enter. You can use the Paste Special command to specify which entries and formatting to use in the current paste operation.
Excel
If you're not sure how to create a pivot table, you can have Excel create one for you using its recommendation, which is a great way to learn about pivot tables. This video shows how to select the data for a pivot table and select a table from Excel's list of recommendations, which you can then customize.
Excel
Load More Articles
Related Books
0:00
0:00
https://cdn.prod.website-files.com/6630d85d73068bc09c7c436c/69195ee32d5c606051d9f433_4.%20All%20For%20You.mp3
Frequently Asked Questions
No items found.
