Explanation In this example, the goal is to create an “locked” reference that won’t change when columns or rows are added or deleted in a worksheet, or during a copy / paste / cut operation. The INDIRECT function accepts text, and evaluates that text as a reference. As a result, the text is not susceptible to changes, like a normal cell reference. It will continue to evaluate to the same location regardless of changes to the worksheet....
Explanation The goal is to retrieve the nth matching record in a set of data, after filtering on a specific product. In the worksheet shown, the product in H4 and the value for n in H5 are inputs that can be changed at any time. For instance, if the product in H4 is “A” and the value in H5 is 3, the formula should return the 3rd record in the table where the product is “A”, as shown in the screen above....
Explanation In this example, the goal is to convert each character in a text string into its corresponding Unicode code point and display the results as a space-separated sequence. This problem can be solved using several Excel functions working together to extract, convert, and format the Unicode values. The formula explained The formula processes the input string through several steps: =TEXTJOIN(" ",,BASE(UNICODE(REGEXEXTRACT(B6,".",1)),16,4)) Working from the inside out: REGEXEXTRACT(B6,".",1) - Extracts each character individually {“a”;“p”;“p”;“l”;“e”} UNICODE(REGEXEXTRACT(…)) - Converts each character to its Unicode code point as decimal values {97;112;112;108;101} BASE(UNICODE(…),16,4) - Converts decimal numbers to hexadecimal (hex) with 4-digit padding {0061;0070;0070;006C;0065} TEXTJOIN(" “,, …) - Joins all values with spaces as separators The advantage of this formula over the standard UNICODE function is that it processes the entire string and returns the complete Unicode sequence for all characters....
Transcript In this video I’m going to show you how you can use multiple Excel functions to split, manipulate, and rejoin values inside a single formula. Here we have some sample data and, in column B, we have text values with a number at the end. What we want to do is increment these numbers using the value in column C. Now if I try to do this directly, with a formula that adds C5 to B5, I’ll get an error because the value in B is text, and Excel won’t let you add numbers and text....
Transcript In this lesson, we’ll look at how to hide and unhide columns and rows. Hiding rows or columns is a good way to visually simplify a worksheet without removing important information. Let’s take a look. One way to hide a column is to choose Hide Columns from the Format menu on the home ribbon. Notice that when a column is hidden, its heading is also hidden, so that there is a gap in the column letters that run across the top of the worksheet....
Transcript In this video, we’ll look at how to perform a random sort with the SORTBY function , with help from the RANDARRAY function . In this worksheet, we have the first 10 letters in the alphabet in the range B5:B14. How can we sort this data in random order? One way to do this is to add a helper column and use RAND function to generate random values. Then we can use the SORT function to sort data by the helper column....
Transcript In this video we’ll look at how to rank values in ascending or descending order using the RANK function . Here we have a table that contains five test scores for a group of students and an average score in Column I. How can we rank these students from highest to lowest scores? Well, one option is to sort the students by average score in descending order. Next, you can enter a “1” for the rank of the first student, “2” for the rank of the second student, and then just double-click the fill handle to copy that down....
Transcript In this lesson we’ll look at how to remove existing borders and fills. Excel makes it easy to clear both borders and fills so that you can get a clean start. Let’s take a look. You might inherit a worksheet that comes from someone else that contains a lot of existing formatting, including heavy use of borders and fills. Rather than try to work with what’s there, piece by piece, it’s often easier and faster to just clear all borders and fills and start fresh....
Purpose Return value Syntax =IMTAN(complex_num) complex_num - The complex number in the form “x+yi”. Using the IMTAN function The Excel IMTAN function returns the tangent of a complex number. For example, given the complex number “3+4i” as input, the function returns “-0.000187346+0.999355987i” =IMTAN("3+4i") // returns -0.000187346+0.999355987i Explanation In math, the tangent of a complex number is defined in terms of complex sine and cosine functions. In Excel, the tangent of a complex number is equivalent to the following formula....
This chart shows actuals so far in a solid line with forecast numbers in a dashed line. The chart type is set to line chart, and actual and forecast values are plotted as two data series. The data used for the chart is set up as shown below: How to make this chart Select the data and insert a line chart: Choose the first option, a basic line chart: Chart as inserted: Select Forecast data series and apply dashed line: After setting dashed line on Forecast data series: From this point, you can set the chart title and move the legend....
About This Shortcut Moves current selection one screen below the active selection. The distance covered depends on the size of the window. If there are 25 rows visible (of the same height), the active selection will move 25 rows down. About This Shortcut If the active cell is empty, Excel will stop on the first non-empty cell to the right. If the active cell is non-empty, Excel will stop on the last non-empty cell to the right....
About This Shortcut This keyboard shortcut will cycle the active cell through non-adjacent selections, starting at the left and moving to the right. About This Shortcut This keyboard shortcut will cycle the active cell through non-adjacent selections, starting at the right and moving to the left.
One of the big changes to Excel in the last year was the introduction of a native checkbox in Excel. Checkboxes might seem like a small thing, but they’re very useful for organizing information, tracking progress, and creating interactive spreadsheets. There’s something uniquely satisfying about ticking a box to finish off a task! Unlike the clunky solutions of the past, this new checkbox sits happily in a cell and is very easy to set up....
Explanation The goal is to assign a grade to each score in column C according to the rules in the table in the range F4:G9. One way to do this in Excel is to use a series of nested IF functions. Generally, nested IFs formulas are used to test more than one condition and return a different result for each condition. Testing more than one condition If you need to test for more than one condition, then take one of several actions, depending on the result of the tests, one option is to nest multiple IF statements together in one formula....
Explanation The TEXT function can apply number formats of any kind, including currency, date, percentage, etc. By applying a number format like “00”, “000”, “0000”, you can “pad” numbers with as many zeros as you like. Zeros will only be added where needed. Number format only The TEXT function converts numbers to text as a normal step in applying the number format. If you are concatenating (joining) the result to another text string, this is fine, but if you just want to apply visual padding to a free-standing number set of numbers, you can apply a custom number format without using a formula....
The term “pairwise lifting” in Excel refers to a special case of “lifting” – a built in calculation behavior whereby functions that don’t handle arrays natively are “lifted” and called once for each value in an array, returning in turn an array containing multiple results. Pairwise lifting occurs when values in two or more arrays are combined pairwise to produce a different array holding the result of this operation. Example The example shown illustrates what happens if you call the SQRT function like this:...
Pivot tables can group dates by year, month, and quarter. This comes in handy if you want to count birthdays that occur in each month, while ignoring the year. In the example shown, there are 100 names and birthdays in the source data. A pivot table is used to count the birthdays that occur in each month of the year. Fields The source data contains two fields: Name , and Birthdate ....
Explanation In this formula, the goal is to return the numeric position of the most expensive property in the list. The formula in cell I5 is: =MATCH(MAX(C3:C11),C3:C11,0) The MAX function extracts the maximum value from the range C3:C11. In this case, that value is 849900. This number is then supplied to the MATCH function as the lookup value. The lookup_array is the same range (C3:C11), and the match_type is set to “exact” with 0....
Transcript In this video, we’ll look at a shortcut recipe that lets you easily remove hundreds or thousands of blank rows in a worksheet. In this first example, we have a big set of data that contains a lot of blank rows. If I move to the last cell, you can see that there’s over 36,000 rows of data, and, looking at the count, just over 33,000 rows actually contain data, which means over 3000 rows are blank....
The status bar is a name for the lower edge of the worksheet window, which displays various information about an Excel worksheet. The status bar can be configured to display things like sum, count, and average of the currently selected cells. It also displays information about the current status of the worksheet, whether scroll lock or end mode are enabled, etc. Right click the status bar to control what kind of information is displayed....
