Posts

Purpose Return value Syntax =UNICODE(text) text - Unicode character to convert to number. Using the UNICODE function The Excel UNICODE function returns a number (code point) corresponding to a Unicode character given as text. The result is a number in decimal notation. For example, the Euro symbol (€) is code point 8364 in decimal notation, so UNICODE returns 8364: =UNICODE("€") // returns 8364 To get the hexadecimal value, which is usually how unicode code points are expressed, you can use the DEC2HEX function like this:...

Purpose Return value Syntax =XNPV(rate,values,dates) rate - Discount rate to apply to the cash flows. values - Values representing cash flows. dates - Dates that correspond to cash flows, in any order. Using the XNPV function The XNPV function returns the net present value (NPV) of an investment based on a discount rate and a series of cash flows that occur at irregular intervals. Values represent cash flows and be correspond to dates ....

About This Shortcut On Windows, this shortcut only works within the Format Cells dialog box, on the Borders tab. If you use this shortcut in Format Cells, you’ll see a border added or removed from the border preview area. On the Mac, there is no equivalent shortcut. About This Shortcut On Windows, this shortcut only works within the Format Cells dialog box, on the Borders tab. If you use this shortcut in Format Cells, you’ll see a horizontal interior border added or removed from the border preview area....

Explanation In this example, the goal is to calculate the variance between a Forecast (column C) and Actual (column D) as a percentage. For example, with a Forecast value of 100,000 and an Actual value of 112,000, we want to return a variance of 12%. The concept of variance requires a baseline value and a “new” value. The baseline value is subtracted from the new value and the result is divided by the baseline value....

Explanation In this example, the goal is to convert the Celsius temperatures shown in column B to Fahrenheit temperatures in column C. The solution shown in the worksheet above relies on the CONVERT function, which can convert a number in one measurement system to another. CONVERT is fully automatic and based on “from” and “to” unit strings. As long as the units valid options in the same category (weight, distance, temperature, etc....

Purpose Return value Syntax =CHOOSEROWS(array,row_num1,[row_num2],...) array - The array to extract rows from. row_num1 - The numeric index of the first row to return. row_num2 - [optional] The numeric index of the second row to return. Using the CHOOSEROWS function The Excel CHOOSEROWS function returns specific rows from an array or range . The rows to return are provided as numbers in separate arguments. Each number corresponds to the numeric index of a row in the source array....

Explanation The first step is to construct a standard “A1” style reference using the column letter, by adding a “1” with concatenation: B5&"1" This results in a text string like “A1” which is passed into the INDIRECT function. Next, the INDIRECT function transforms the text into a proper Excel reference and hands the result off to the COLUMN function. Finally, the COLUMN function evaluates the reference and returns the column number for the reference....

Explanation Data validation rules are triggered when a user adds or changes a cell value. This custom validation formula simply checks the year of any date against a hard-coded year value using the YEAR function. When a user enters a value, the YEAR function extracts and compares the year to 2016: =YEAR(C5)=2016 When the years match, the expression returns TRUE and validation succeeds. If the years don’t match, or if the YEAR function is not able to extract a year, validation fails....

Explanation In this example, the goal is to create a reference to an external workbook with variable information. The easiest way to do this is to assemble the reference to a range or cell in another workbook as a text value , then use the INDIRECT function to convert the text to an actual reference. In Excel, a full reference to an external worksheet looks like this: '[sample data.xlsx]Sheet1'!A1 Note the square brackets ([ ]) around workbook name, single quotes (’ ‘) around the worksheet + sheet, and the exclamation mark (!...

Shortcuts turbocharge your productivity in Excel: they help you work faster & more accurately, they let you focus on your work (not Excel’s interface), and they unlock Excel’s most powerful features. But shortcuts require practice to master. This video course covers over 200 Excel shortcuts, and includes a full set of practice worksheets. Start using more Excel shortcuts today!

Purpose Return value Syntax =EXPAND(array,[rows],[columns],[pad_with]) array - The array to expand. rows - [optional] The final number of rows. Default is total rows. columns - [optional] The final number of columns. Default is total columns. pad_with - [optional] Value to use for new cells. Default is #N/A. Using the EXPAND function The EXPAND function expands an array by adding rows and columns, which are supplied as separate arguments. The values given for rows and columns represent the dimensions of the final array, not the number of rows or columns to add....

Explanation In this example, the goal is to list values in a given group that are within a given tolerance. The group is set in cell G4, and the target value is entered in cell G5. The allowed tolerance is entered in cell G6. The data comes from an Excel Table called data in the range B5:D16. The solution is built on the FILTER function which can be used to extract and list data that meets multiple criteria....

Transcript In this video, we’ll build a dropdown list using dynamic arrays to filter data. Here we have data in an Excel Table called “data”. In cell J2, I’ll set up a dropdown list we can use to filter data by color. First, I’ll type “Red” in J2, so we have something to filter on. Next, I’ll enter the FILTER function in cell I5. For array , we want the full table....

The Formula Bar in Excel sits directly above the worksheet area, to the right of the Name Box . The formula bar can be used to edit the content of any cell and can be expanded to show multiple lines for the same formula ( example , shortcut for toggling ). For an example of editing a long formula in an expanded formula bar, watch this video . Excel supports both full column, and full row references....

The function screen tip is a small window that appears when you click into a function in the formula bar. Although this window looks quite simple, it is actually a very useful tool when editing and debugging Excel formulas. When the function screen tip is visible, you can use it to precisely navigate the function arguments in a formula. The window is context-sensitive and its contents depend on the function the cursor resides in....

Explanation Working from the inside out, the EOMONTH function gets the last day of month of any date. To this result, we add 1, which results in the first day of the next month. This date goes into WORKDAY function as the “start date”, along with -1 for “days”. The WORKDAY function automatically steps back 1 day, taking into account any weekends. The result is the last workday of the month....

Explanation The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. The DATE function creates a proper Excel date with given year, month, and day values. Because the reference to B4 is fully relative, it will update as the rule is applied to each cell in the range, and any dates that are both greater than 8/1/2015 and less than 11/1/2015 will be highlighted. Use other cells for input You don’t need to hard-code the dates into the rule....

Explanation When you use a formula to apply conditional formatting, the formula is evaluated for each cell in the range, relative to the active cell in the selection at the time the rule is created. So, in this case, if you apply the rule to B4:G11, with B4 as the active cell, the rule is evaluated for each of the 40 cells in B4:G11 because B4 is entered as a fully relative address....

Transcript In this video, we’ll look at an example of how Excel plots dates on a horizontal axis. When you create a chart using valid dates on a horizontal axis, Excel automatically sets the axis type to date. For example, this stock price data is spaced out over a period of more than 10 years, in random intervals. If plot this stock price data as a line chart, the horizontal axis is automatically set up as a category axis with a type of “date”....

Transcript In this lesson we’ll look at how to apply the basic styles of bold, italic, and underlining. All of these styles can be applied individually or together. Let’s take a look. Let’s pick up where we left off and work with the drink size headings in our sample coffee menu. Holding down the Control key, we can select all of these headings at once and work with them at the same time....