About This Shortcut This shortcut will insert rows as long as at least one row is selected. In a table, this shortcut will insert a row above the active selection when only one cell is selected. If you use the shortcut without a row selected, you’ll see the Insert Cells dialog box displayed. With a laptop keyboard, use Control Shift + . With a full keyboard, use Control + Note: In Mac 2016, this shortcut was changed to Command Shift +, and now Control + I changes text to italics....

Purpose Return value Syntax =ISERROR(value) value - The value to check for any error. Using the ISERROR function The ISERROR function returns TRUE for any type of error, including #N/A , #VALUE! , #REF! , #DIV/0! , #NUM! , #NAME? , #NULL! , #CALC! , and #SPILL! errors. You can use ISERROR together with the IF function to test for errors and display a custom message, or run a different calculation when an error occurs....

Purpose Return value Syntax =ISEVEN(value) value - The numeric value to check. Using the ISEVEN function The ISEVEN function tests for even numbers. ISEVEN takes one argument , value , which should be a numeric value or a cell reference. When value is an even number, ISEVEN returns TRUE. When value is an odd number, ISEVEN returns FALSE. If value is not numeric, ISEVEN will return the #VALUE error. Only the integer portion of value is evaluated, decimal values are truncated....

Explanation In the image shown, the current date is August 19, 2019. Excel dates are serial numbers , so you can manipulate them with simple math operations. The TODAY function always returns the current date. Inside the AND function , the first logical test checks to see if the date in B5 is greater than or equal to today’s date minus 7 days: =B5>=(TODAY()-7)<TODAY()) The second logical test checks if the date is less than today:...

Purpose Return value Syntax =MAX(number1,[number2],...) number1 - Number, reference to numeric value, or range that contains numeric values. number2 - [optional] Number, reference to numeric value, or range that contains numeric values. Using the MAX function The MAX function returns the largest numeric value in the data provided. The MAX function can be used to return the largest value from any type of numeric data. For example, MAX can return the slowest time in a race, the latest date, the largest percentage, the highest temperature, or the top sales number....

About This Shortcut This keyboard shortcut will cycle the active cell through non-adjacent selections, starting at the right and moving to the left. About This Shortcut With add to selection mode enabled, you don’t need to hold down the control key to add non-adjacent cells to the current selection.

Transcript In this example, we’re going to analyze voting results with a pivot table. In the data, we have the fields: Name, Gender, Age, and Vote. And, you can see, that there are 3 voting options, A, B, and C. First, how many people voted? By counting Name, you can see that we have 300 votes total. Next, what was the winning vote? The winning vote is Option B, with 156 votes....

Here is the replay of our pivot table webinar, held November 14. Thanks to all of you who attended!

Explanation In this example, the goal is to perform a random sort of the data in B5:B16 with a formula. This can be done with the SORTBY function and the RANDARRAY function . SORTBY function The SORTBY function sorts provided values by one or more “sort by” arrays. The sort by arrays make it possible to sort data in a completely custom manner. The main requirement of the sort by array(s) is that they have dimensions that are compatible with the data being sorted....

Explanation Normally, the MATCH function receives a single lookup value, and returns a single match if any. In this case, however, we are giving MATCH an array for lookup value, so it will return an array of results, one per element in the lookup array. MATCH is configured for “exact match”. If a match isn’t found, MATCH will return the #N/A error. After match runs, it returns have something like this:...

Explanation Introduction A key limitation of VLOOKUP is it can only look up values to the right . In other words, the column with lookup values must be to the left of the values you want to retrieve with VLOOKUP. As a result, with standard configuration, there is no way to use VLOOKUP to “look left” and reverse the original lookup. From the standpoint of VLOOKUP, we can visualize the problem like this:...

Purpose Return value Syntax =ROUNDDOWN(number,num_digits) number - The number to round down. num_digits - The place at which number should be rounded. Using the ROUNDDOWN function The ROUNDDOWN function rounds numbers down . Unlike standard rounding, where only numbers less than 5 are rounded down, ROUNDDOWN rounds all numbers down . For example: =ROUNDDOWN(3.999,0) // returns 3 ROUNDDOWN takes two arguments, number and num_digits . Number is the number to be rounded, and num_digits is the place at which number should be rounded....

About This Shortcut Inside a cell, this shortcut will extend the selection by one character to the left each time the left arrow key is pressed. About This Shortcut Inside a cell, this shortcut will move the cursor by one word to the right each time the right arrow key is pressed.

Transcript In this video, we’ll look at shortcuts related to drag and drop in Excel. To drag and drop in Excel, just make a selection and hover the mouse over the edge of the selection. When the cursor changes, you can drag the selection to a new location. With this method, drag and drop is equivalent to cut and paste. The content of selected cells is moved, and the selection you drag will completely overwrite the destination cells....

Transcript Excel charts can exist in two states: as an embedded object, in a worksheet, floating above the cell grid, or, in a sheet all by themselves. In this video, we’ll look at how to move charts from one state to the other. Normally, when you choose a chart type on the ribbon, Excel creates an embedded chart on the same worksheet, which floats over the cell grid below. But you can also move a chart to a sheet all by itself....

There are over 200 Excel shortcuts for both Mac and PC ( you can download a PDF here ). This article explains about 50 of the most useful. – Dave Next worksheet / Previous worksheet Often, you’ll need to switch back and forth between different worksheets in the same workbook. If you are working in a large workbook with many sheets, this can be annoying to do with your mouse, because some sheets may not be visible....

Purpose Return value Syntax =TRANSLATE(text,[source_language],[target_language]) text - The text to translate as a string or cell reference. source_language - [optional] The source language code. Default = auto-detected.. target_language - [optional] The target language code. Default = system. Using the TRANSLATE function The TRANSLATE function translates text from one language to another in Excel. To perform a translation, TRANSLATE requires the text to translate and language codes to indicate the source and target language....

Transcript A tour of the Excel Ribbon Prominent at the top of the Excel window, the ribbon is a kind of super toolbar. The ribbon contains every command you’ll ever need in Excel, grouped by logical function into tabs. It’s a great way to explore what Excel can do. Let’s take a look. The ribbon is divided into tabs. Each tab contains logically grouped commands, further sub-divided into Groups. In some cases, there are more commands than available space, and you’ll see a small arrow in the lower right....

About This Shortcut Hold down the control key and click (or click and drag) elsewhere on the worksheet to add another range of calls to the current selection. About This Shortcut This keyboard shortcut will move the current selection to the next column right. We don’t know of any Windows equivalent; let us know if you find one.

Purpose Return value Syntax =CEILING.MATH(number,[significance],[mode]) number - The number that should be rounded. significance - [optional] Multiple to use when rounding. Default is 1. mode - [optional] Round negative numbers toward or away from zero. Default is 0. Using the CEILING.MATH function The Excel CEILING.MATH function rounds a number up to a given multiple, where multiple is provided as the significance argument. If the number is already an exact multiple, no rounding occurs and the original number is returned....