Transcript Sometimes you have a large list that contains empty rows, and you need to remove these rows in order to clean up the list. You could delete the rows one by one, but that’s going to take a long time, especially if you have lots of blank rows. In today’s ExcelJet tip, we’ll show you a cool way to delete blank rows, even hundreds or thousands of blank rows, in record time....

Transcript In this lesson we’ll take a look at the Accounting format. Like the Currency format, the Accounting number format is designed for numbers with currency symbols. The main difference between Currency and Accounting formats is that Accounting aligns currency symbols to the left in each cell, and displays zero values with a hyphen. Let’s take a look. Let’s start off by copying a set of numbers in General format across our table....

Transcript Icon sets are a unique kind of conditional formatting in which cells in a selection can be labelled with icons based on certain conditions. Let’s take a look. Here we have the set of test scores we looked at previously. Let’s explore using conditional formatting with icon sets to highlight values in the table. Icon sets are another primary category in the Conditional Formatting menu. Here, you’ll see a large number of icons divided into four main area: Directional, shapes, indicators, and ratings....

Transcript In this video, we’ll look at how to use INDEX and MATCH with an Excel Table. Using INDEX and MATCH with an Excel Table is wonderfully straightforward. To illustrate, I’ll build INDEX and MATCH formulas that do the same thing as the VLOOKUP formulas already on this worksheet. First, to recap, these VLOOKUP formulas currently retrieve information from Table1 based on the ID provided in K4. Each formula uses the lookup_value from K4, the entire table for table_array , and the MATCH function to figure out what column number to return, based on column names in column J....

Transcript There are many situations where you need to enter a number in a special format; for example, a telephone number. Excel provides four special number formats out of the box that provide automatic number formatting: a format for zip codes, a format for zip codes + 4 digits, a telephone number format, and a format for social security numbers. Let’s take a look. Here we have a small list of numbers that need special formatting....

Explanation In this example, the goal is to calculate the correct grade for each name in column B using the score in column C and the table to the right. For convenience only, grade (G5:G9) and score (F5:F9) are named ranges . This is a classic “approximate-match” lookup problem because it is unlikely that a score will be found in the lookup table. Instead, the table acts to group scores into buckets....

Explanation Working from the inside out, the logical criteria used in this formula is this expression: --(names=G4) where names is the named range C4:E7. This generates a TRUE / FALSE result for every value in the data, and the double negative coerces the TRUE and FALSE values to 1 and 0 to yield an array like this: {0,0,0;1,0,0;0,0,0;0,0,0} This array is 4 rows by 3 columns, matching the structure of “names”....

Purpose Return value Syntax =INDEX(array,row_num,[col_num],[area_num]) array - A range of cells, or an array constant. row_num - The row position in the reference or array. col_num - [optional] The column position in the reference or array. area_num - [optional] The range in reference that should be used. Using the INDEX function The INDEX function returns the value at a given location in a range or array. INDEX is a powerful and versatile function....

Explanation Working from the inside out, the ISERROR function returns TRUE when a value is a recognized error, and FALSE if not. When given a range of cells (an array of cells) ISERROR function will return an array of TRUE/FALSE results. In the example, this resulting array looks like this: {FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE} Note that the 6th value (which corresponds to the 6th cell in the range) is TRUE, since cell B9 contains #N/A....

Explanation In this example, the goal is to retrieve the top 3 scores in column D that appear in a given group, entered as a variable in cell F5. If the group is changed, the formulas should calculate new results. The core of the solution is the LARGE function, which can be used to retrieve the “nth” largest value in a set of data. The challenge is that the LARGE function does not offer any built-in way to apply criteria before calculating a result....

Purpose Return value Syntax =PMT(rate,nper,pv,[fv],[type]) rate - The interest rate for the loan. nper - The total number of payments for the loan. pv - The present value, or total value of all loan payments now. fv - [optional] The future value, or a cash balance you want after the last payment is made. Defaults to 0 (zero). type - [optional] When payments are due. 0 = end of period. 1 = beginning of period....

Bar and column charts are great for comparing things, because it’s easy to see how bar lengths differ. This chart is an example of a clustered column chart showing product units sold this year versus last year. The data used for this chart looks like this on the worksheet: Note: you can see the same data in a clustered bar chart . How to make this chart Select the data and insert a bar char on the ribbon: Insert the first 2D bar chart option: Right-click each data series and use fill tool to change color: After changing colors: Select chart data: Chart tools > Design > Select data Move this year series up: Move legend to top: Select data series and set overlap to zero and bar width to 60%: Final chart: Bar charts are great for comparing things, because it’s easy to see how bar lengths differ....

About This Shortcut On Windows, this shortcut works in the Font tab of the Format Cells dialog box and removes all cell borders except diagonal borders. Conflicts with Mac Universal Access Zoom. System preferences > Keyboard > Universal Access > Zoom out About This Shortcut While editing a formula, this shortcut toggles cell references from relative to absolute, to partially absolute, back to relative again: A1 –> $A$1 –> A$1– > $A1– > A1...

Purpose Return value Syntax =RRI(nper,pv,fv) nper - Number of periods. pv - Present value of investment. fv - Future value of investment. Using the RRI function The RRI function returns an equivalent interest rate for the growth of an investment. For example, to use RRI to calculate equivalent annual compound interest for a 1000 investment worth 1200 after five years you can use a formula like this: =RRI(5,1000,1200) // returns 0....

Explanation The range B7:B9 contains the sheet names we want to include in the search. These are just text strings, and we need to do some work to get them to be recognized as valid sheet references. Working from the inside out, this expression is used to build a full sheet reference: "'"&B7&"'!"&"1:1048576" The single quotes are added to allow sheet names with spaces, and the exclamation mark is a standard syntax for ranges that include a sheet name....

Explanation The SEQUENCE function is a dynamic array function that can generate multiple results. When used by itself on the worksheet, SEQUENCE outputs an array of results that " spill " onto the worksheet in a " spill range “. In the example shown, we want to generate 12 times, one hour apart, starting at 7:00 AM (the value in B5). To do this, we use the TIME function, which can create a valid Excel time with hours, minutes, and seconds given as decimal values....

Transcript In this video, we’ll look at how to generate a sequence of times with the SEQUENCE function . The SEQUENCE function can be used to generate numeric sequences of all kinds. Since Excel times are just numbers, SEQUENCE works well for generating times. In the first worksheet, let’s generate 9 times, one hour apart, starting at 9:00 AM. For start I’ll use 9:00 AM. For times , I use 9....

Transcript In this video, we’ll look at a core idea in dynamic array behavior, the spill range . When a dynamic array formula outputs multiple values, it is said to “spill” these values onto the worksheet. For example, if I use the UNIQUE function on this list of colors, UNIQUE spills 3 values - red, blue, and green. The rectangle that encloses these values is called the “spill range”. Notice when I select any cell in a spill range, the rectangle will appear with special highlighting....

About This Shortcut Unhide any hidden rows that intersect the current selection. About This Shortcut Unhide any hidden columns that intersect the current selection. Notes: (1) May not work in Excel 2010 on Vista or Windows 7. Discussion and fix is here: http://superuser.com/questions/183197/whats-the-keyboard-shortcut-to-unhide-a-column-in-excel-2010 (2) May not work in Excel 2007. See http://www.techrepublic.com/blog/microsoft-office/office-solution-keyboard-shortcuts-for-hiding-and-unhiding-columns-and-rows/ (3) In Windows 10, there is a conflict with a keyboard-switching shortcut. You can resolve this by unassigning the keyboard layout hot key....

Purpose Return value Syntax =WEEKNUM(serial_num,[return_type]) serial_num - A valid Excel date in serial number format. return_type - [optional] The day the week begins. Default is 1. Using the WEEKNUM function The WEEKNUM function takes a date and returns a number between 1 and 54 that corresponds to the week of the year. By default, the WEEKNUM function starts counting on the week that contains January 1 and increments week numbers on Sunday....