Excel 2016: VLOOKUP, INDEX MATCH, and Other Advanced Lookups 4.0 (43 ratings). This course is recorded on a Windows computer, but the formulas and functions we use are also applicable to Excel for Mac; Students also bought Course content Expand.
Since the release of Excel 2016 for Mac, customers have given great feedback about its ease of use and the benefits of the new features. We want to highlight the Excel 2016 for Mac features that customers rave about and share some tips and tricks that could save you a ton of time using the most powerful spreadsheet and data analysis tool on the Mac platform. Recommended Charts Choosing a chart type to best represent your data is often challenging. Let Recommended Charts take some of the pain away. This feature allows you to see how selected data would be visualized on a variety of chart types before committing to one in particular.
To see a collection of suggested chart types, select a cell in the range of data you want to visualize, and then on the ribbon, under the Insert tab, click Recommended Charts. Formula Builder If you’ve ever had trouble remembering Excel functions or syntax, the new Formula Builder makes it easy. With the Formula Builder in Excel 2016 for Mac, building formulas just got simpler. It allows you to search and insert a function, add data to defined function arguments, and get help on any function. To access the Formula Builder, simply click the fx button on the Formula bar or press Shift+F3. Chart Formatting task pane Excel 2016 for Mac offers a rich set of features that make creating and customizing charts simpler and more intuitive.
One part of this fluid new experience is the Formatting task pane. The new Formatting task pane is the single source for formatting—all of the different styling options are consolidated in one place. With this single task pane, you can modify not only charts, but also shapes and text in Excel! To use the Formatting task pane, on the ribbon under the Format tab, click the Format Pane button or press Cmd+1 while a chart element is selected.
PivotTable slicers Slicers enable you to filter the data in a PivotTable report. It contains a set of buttons allowing you to find the items that you want to filter without the need to open drop-down lists. Creating a slicer is easy—just select the PivotTable you want to filter, and then on the ribbon, under the PivotTable Analyze tab, click the Insert Slicer button. To filter the PivotTable data, simply click one or more of the buttons in the slicer. Data Analysis ToolPak Still looking for the Data Analysis ToolPak in Mac Excel?
It’s finally here! Data Analysis ToolPak is an Excel add-in that helps develop complex statistical or engineering analyses. You provide the data and parameters for each analysis, and the tool uses the appropriate statistical or engineering macro functions to calculate and display the results in an output table.
Some tools generate charts in addition to output tables. To enable this add-in, under the Tools menu, click Add-Ins, select Data Analysis ToolPak and then click OK. The Data Analysis ToolPak is now on the ribbon under the Data tab. More (or new) keyboard shortcuts When building Office 2016 for Mac, one of our key objectives was to make it as easy as possible to transition from using Office for Windows to using Office for Mac and back again. That’s why you’ll notice an interface consistent with Office 2016 for Windows and why we added support for virtually all of the Windows Excel keyboard shortcuts. Windows users will rejoice that Windows key assignments like Ctrl+O for Open, Ctrl+F for Find and Ctrl+C for Copy now also work in the Mac version—no need to remember to press Cmd instead of Ctrl.
If you want to clear the content of the selected cell or range, just press the delete key. This even works on your Mac laptops, where delete is actually the backspace key. Some popular shortcut keys are listed below; a complete list can be found. Note that if a function key doesn’t work as you expect, then press the fn key in addition to the function key.
If you don’t want to press the fn key each time, you can change your Apple system preferences:. Go to Apple System Preferences Keyboard. On the Keyboard tab, select the Use all F1, F2, etc.
![Excel For Mac 2016 Vlookup Excel For Mac 2016 Vlookup](/uploads/1/2/5/3/125384496/403790363.png)
As standard function keys If you want to customize a keyboard shortcut, you can refer to the steps in this article:. New functions in Excel 2016 for Mac We worked hard to ensure your workbook is compatible and works seamlessly across platforms as often as possible. In Excel 2016 for Mac, we’ve added almost all from the Windows platform. Why not have a try on the Arabic function (for example, try =ARABIC(“LVII”)) and see what you get. Note that the WebService, EncodeURL and FilterXML functions are not available yet in Excel 2016 for Mac. Get data using SQL Server ODBC Say goodbye to having to use third party drivers before connecting to external data in Excel for Mac.
Excel 2016 for Mac comes with a pre-installed and integrated SQL Server ODBC driver that supports ODBC data connections with SQL Server and Azure SQL Database right out of the box. It also has a brand new Microsoft Query (MSQuery) and Connection Manager to make creating and managing all of your data connections easier and more consistent with Windows. For more details, check out this blog post:. What do you think? We just went through the basics of the new features available in Excel 2016 for Mac. Try them out for yourself and for other features and improvements you’d like us to change or improve in Excel 2016 for Mac.
Categories. Get started with Office 365 It’s the Office you know, plus tools to help you work better together, so you can get more done—anytime, anywhere. Sign up for updates Sign up now Microsoft may use my email to provide special Microsoft offers and information.
Move the Cell Cursor in Excel 2016 Spreadsheets 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. Keystroke Where the Cell Cursor Moves Right arrow or Tab Cell to the immediate right. Left arrow or Shift+Tab Cell to the immediate left. Up arrow Cell up one row.
Down arrow Cell down one row. Home Cell in Column A of the current row. Ctrl+Home First cell (A1) of the worksheet.
Ctrl+End or End, Home Cell in the worksheet at the intersection of the last column that has data in it and the last row that has data in it (that is, the last cell of the so-called active area of the worksheet). Page Up Cell one full screen up in the same column.
Page Down Cell one full screen down in the same column. Ctrl+Right arrow or End, Right arrow First occupied cell to the right in the same row that is either preceded or followed by a blank cell. If no cell is occupied, the pointer goes to the cell at the very end of the row. Ctrl+Left arrow or End, Left arrow First occupied cell to the left in the same row that is either preceded or followed by a blank cell.
If no cell is occupied, the pointer goes to the cell at the very beginning of the row. Ctrl+Up arrow or End, Up arrow First occupied cell above in the same column that is either preceded or followed by a blank cell. If no cell is occupied, the pointer goes to the cell at the very top of the column. Ctrl+Down arrow or End, Down arrow First occupied cell below in the same column that is either preceded or followed by a blank cell. If no cell is occupied, the pointer goes to the cell at the very bottom of the column. Ctrl+Page Down The cell pointer’s location in the next worksheet of that workbook. Ctrl+Page Up The cell pointer’s location in the previous worksheet of that workbook.
When moving the cell cursor by using the keystrokes listed in the table, keep the following helpful hints in mind:. In the case of those keystrokes that use arrow keys, you must either use the arrows on the cursor keypad or else have the Num Lock disengaged on the numeric keypad of your physical keyboard.
The keystrokes that combine the Ctrl or End key with an arrow key are among the most helpful for moving quickly from one edge to the other in large tables of cell entries or for moving from table to table in a section of a worksheet with many blocks of cells. When you use Ctrl and an arrow key on a physical keyboard to move from edge to edge in a table or between tables in a worksheet, you hold down Ctrl while you press one of the four arrow keys. When you do this with the Touch keyboard on a touchscreen device, you tap the Ctrl key and then the arrow key sequentially. When you use End and an arrow-key alternative, you must press and then release the End key before you press the arrow key. Pressing and releasing the End key causes the End Mode indicator to appear on the Status bar. This is your sign that Excel is ready for you to press one of the four arrow keys.
Excel 2016 Data-Entry Etiquette 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:.
![For For](/uploads/1/2/5/3/125384496/760026409.jpg)
Whenever you can, organize your information in tables of data that use adjacent (neighboring) columns and rows. Start the tables in the upper-left corner of the worksheet and work your way down the sheet, rather than across the sheet, whenever possible.
When it’s practical, separate each table by no more than a single column or row. When you set up these tables, don’t skip columns and rows just to “space out” the information. (To place white space between information in adjacent columns and rows, you can widen columns, heighten rows, and change the alignment.). Reserve a single column at the left edge of the table for the table’s row headings. Reserve a single row at the top of the table for the table’s column headings.
If your table requires a title, put the title in the row above the column headings. Put the title in the same column as the row headings. Decipher Error Values in Excel 2016 Formulas 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. The following table lists some Excel 2016 error values and their most common causes. What Shows Up in the Cell What’s Going On Here? Appears when the formula calls for division by a cell that either contains the value 0 or, as is more often the case, is empty. Division by zero is a no-no in mathematics. Appears when the formula refers to a range name that doesn’t exist in the worksheet. This error value appears when you type the wrong range name or fail to enclose in quotation marks some text used in the formula, causing Excel to think that the text refers to a range name.
Appears most often when you insert a space (where you should have used a comma) to separate cell references used as arguments for functions. Appears when Excel encounters a problem with a number in the formula, such as the wrong type of argument in an Excel function or a calculation that produces a number too large or too small to be represented in the worksheet. Appears when Excel encounters an invalid cell reference, such as when you delete a cell referred to in a formula or paste cells over the cells referred to in a formula.
Appears when you use the wrong type of argument or operator in a function, or when you call for a mathematical operation that refers to cells that contain text entries. Top 10 Features in Excel 2016 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.
By storing your Excel workbooks one of these places in the Cloud, you’re assured access to them on any device running Excel 2016 (which can include your Windows tablet and smartphone along with your desktop and laptop PC). Moreover, should you find yourself without a computing device running Excel 2016, as part of your Office 365 subscription you can still review and edit your workbooks using Excel Online in almost any major web browser. Painless File Share options: File sharing in Excel has only gotten better and easier than ever. The Share screen in the Excel Backstage makes it easier than ever to share your Excel workbooks online. Not only can you easily invite people to view and edit workbooks saved on your OneDrive in the Cloud, you can also present them in online Skype meetings and post them to your favorite Social media sites. Total Touchscreen support: Excel 2016 isn’t just the best spreadsheet program for your Windows desktop and laptop PC, it’s also the best on your Windows tablet and smartphone.
To make sure that the Excel 2016 touchscreen experience is as rich and rewarding as with a physical keyboard and mouse, Excel 2016 supports a special Touch mode that put more space between command buttons on the Ribbon making them easier to select with your finger or stylus along with all major touchscreen gestures. Integrated Data Model support: Excel 2016 supports true one-to-one and one-to-many relations between the data tables that you import into Excel from standalone database management programs as well as between the data lists that you create in Excel.
The relationships between the data tables and lists in the Data Model then enable you to use data from any of their columns in the Excel pivot tables and charts you create. Pivot table filtering with Slicers and Timelines: Excel 2016’s slicers make it possible to quickly filter the data in your pivot tables on a multiple of columns via onscreen graphic objects. Timelines enable you to graphically filter pivot table data using a timeline based on any date-type column included in the pivot table’s Data Model.
Recommended Charts: Not sure what type of chart will show off your data the best? Just position the cell pointer anywhere in the table of data and select Insert→Recommended Charts on the Ribbon. Excel then displays an Insert Chart dialog box where Live Preview shows how the table’s data will look in a variety of different types of charts. Once you find the chart best represents the data, you simply click the OK button to embed it in the table’s worksheet. Recommended Pivot Tables: If you’re a newbie at creating pivot tables for you’re the Excel data lists you create as well as data tables that you import from standalone database management programs, you can now get Excel to recommend and create one for you. All you have to do is locate the cell cursor in one of the cells of the data list and select Insert→Table→Recommended PivotTables on the Ribbon. Excel 2016 then opens the Recommended PivotTables dialog box showing you a whole list of different pivot tables that you can create on a new worksheet in the current Excel workbook simply by selecting the OK button.
Office Add-ins: Office Add-ins enable you to expand the power of Excel 2016 by installing all sorts of specialized little programs (also known as apps) that are available from the Office Store right within the program. To install and use an add-in, select Insert@@→My Add-ins@@→See All on the Ribbon and then select the STORE option in the Office Add-ins dialog box.
Free apps for Excel 2016 include the Bing Maps app to plot locations, the Merriam-Webster Dictionary app to look up words, and the Mini Calendar and Date Picker app to help you enter dates in your worksheet. Quick Analysis tool: The Quick Analysis tool appears at the lower-right corner of any selected table in an Excel 2016 worksheet. This tool contains options for applying conditional formats, creating a chart or pivot table, totaling values in rows or columns, or adding sparklines for the data in the selected table. And thanks to Excel’s Live Preview, you can see how your table data would appear using the various options before you apply any of them. Flash Fill: This nifty feature is literally a mind reader when it comes to dealing with multipart cell entries in a single column of the worksheet that contains discrete elements you could better use if they were entered all by themselves in separate columns of the sheet.
To separate discrete elements from longer entries in the column, all you have to do is manually enter the first element in the longer entry you want extracted into a cell in the same row in an empty column to the right terminated by pressing the down arrow. Then, the moment you type the first letter of the corresponding element in the second long entry in the empty cell in the row below, Excel 2016’s AutoCorrect feature not only suggests the rest of that second entry to make but all the rest of the corresponding entries for the entire column. To complete the AutoCorrect suggested entry and fill out the entire column, you simply select the Enter button on the Formula bar or press the Enter key.