Transcript In this lesson we’ll look at how to use borders and fills together to create an interesting table design. Let’s take a look. This is a pretty basic table with a simple gray border. Let’s upgrade this design to something with a little more polish. First, let’s remove all borders. For this, we can use the preset in the border menu on the ribbon. Now let’s increase the row height to 30 for all the rows in the table....
Transcript In this video, we’ll look at how to use the HLOOKUP function. HLOOKUP works just like VLOOKUP , but instead of getting the value of an item from a certain column in a table, HLOOKUP gets a value from a certain row in a table. Let’s take a look. Here’s the VLOOKUP commission example we’ve looked at previously. Let me quickly run through the example again to recap. To look up the correct commission value in this table with VLOOKUP, we give VLOOKUP the value to look up and the table array to use....
Purpose Return value Syntax =IF(logical_test,[value_if_true],[value_if_false]) logical_test - A value or logical expression that can be evaluated as TRUE or FALSE. value_if_true - [optional] The value to return when logical_test evaluates to TRUE. value_if_false - [optional] The value to return when logical_test evaluates to FALSE. Using the IF function The IF function is one of the most widely used functions in Excel. At the core, IF runs a test and returns one value when the result is TRUE, and another value when the result is FALSE....
Purpose Return value Syntax =IFERROR(value,value_if_error) value - The value, reference, or formula to check for an error. value_if_error - The value to return if an error is found. Using the IFERROR function The IFERROR function returns a custom result when a formula returns an error and a normal result when a formula calculates without an error. The typical syntax for the IFERROR function looks like this: =IFERROR(formula,custom) In the example above, “formula” represents a formula that might return an error, and “custom” represents the value that should be returned if the formula returns an error....
Explanation In this example, the goal is to calculate the total income tax owed in a progressive tax system with multiple tax brackets, as shown in the worksheet. The article below first reviews how taxes are computed in a progressive system. Next, it shows how to accomplish this task in Excel using two different methods: A modern approach that leverages the latest Excel formulas and functions A traditional approach that works in older versions of Excel....
Explanation The N function takes a value and returns a number. When given a text value, the N function returns zero. In this case, the primary formula the SUM function, and the N function simply evaluates the comment text and returns zero: =SUM(F5:F8)+N("Q4 numbers are estimates") =SUM(F5:F8) + 0 =410,750 The comment is visible in the formula bar when the cell is selected. Explanation This formula relies on concatenation to assemble a valid location for the HYPERLINK function....
About This Shortcut Move to the previous pane in a worksheet that has been split. About This Shortcut Move to the next workbook window. This shortcut “cycles” through open Excel workbooks in a “forward” direction. On Windows, you can also use Ctrl + F6 to move to the next workbook and Shift + Ctrl + F6 to move to the previous open workbook. On a Mac, the shortcut for cycling in a forward direction between open windows in an application is Command + Shift + ....
Purpose Return value Syntax =OCT2DEC(number) number - The octal number you want to convert. Using the OCT2DEC function The OCT2DEC function is used to convert octal numbers (base 8) to decimal numbers (base 10). This is useful when working with different number systems, especially in engineering and computer science applications. Key features Converts octal numbers to decimal numbers Handles both positive and negative octal numbers (using two’s-complement for negatives) Returns a decimal number (as a number, not text) To get the octal representation of a decimal number, use the DEC2OCT function....
Purpose Return value Syntax =PEARSON(array1,array2) array1 - The first set of data values. array2 - The second set of data values. Using the PEARSON function The PEARSON function computes the Pearson product-moment correlation coefficient for two arrays of numbers. It quantifies both the strength and direction of a linear relationship, with results ranging from -1 (perfect negative correlation) to 1 (perfect positive correlation). Key features Returns values between -1 and 1 (inclusive) Positive values close to 1 indicate a strong positive correlation Negative values close to -1 indicate a strong negative correlation Values near zero indicate weak or no linear correlation Standardized and unit-independent Both arrays must have the same number of data points Ignores text and logical values; works with numbers only Note: The PEARSON function is functionally identical to CORREL ....
Purpose Return value Syntax =PERMUT(number,number_chosen) number - The total number of items. number_chosen - The number of items in each combination. Using the PERMUT function The PERMUT function returns the number of permutations for a given number of items. A permutation is a combination where order matters. In other words, a permutation is an ordered combination. There are two types of permutations: Permutations where repetition is not allowed (i.e. 123) Permutations where repetition is allowed (i....
Standard Pivot Tables have a simple feature for creating calculated items. You can think of a calculated item as “virtual rows” in the source data. A calculated item will not appear in the field list window. Instead, it will appear as an item in the field for which it is defined. In the example shown, a calculated item called “Southeast” has been created with a formula that adds South to East....
Explanation The SUBSTITUTE function lets you replace text by matching content. In this case, we want to remove hyphens from telephone numbers. The SUBSTITUTE function can handle this easily — we just need to provide a cell reference (B6), the text to remove ("-"), and the an empty string ("") for replacement text. SUBSTITUTE will replace all instances of “-” with nothing. Note that SUBSTITUTE is a case-sensitive function. Removing more than one thing If you need to remove more than one thing, you can nest multiple SUBSTITUTE functions....
Explanation In this example, the goal is to “reverse” the items in the range B5:B14, so that the first item appears last, the second item appears second to last, and so on. When you first encounter a problem like this in Excel, your first instinct might be to sort the values in some way using the SORT function or Excel’s built-in sort feature. If the items happen to be sorted in alphabetical order, A-Z, you can indeed sort in descending order, Z-A....
Purpose Return value Syntax =ROMAN(number,[form]) number - Number (in Arabic numeral) you want to convert to Roman numeral. form - [optional] The type of Roman numeral you want. Using the ROMAN function The ROMAN function converts a number to a Roman numeral. For example: =ROMAN(4) // returns "IV" =ROMAN(9) // returns "IX" =ROMAN(99) // returns "XCIX" =ROMAN(100) // returns "C" ROMAN takes two arguments : number and form . Number should be a whole number between 1 and 3999....
Explanation In the example shown, the goal is to round a price to the nearest value ending in .99. So, for example, if a price is currently $5.31, the result should be $4.99. The best way to think about the problem is to restate it as “round a price to the nearest whole dollar, less 1 penny”. In other words, the solution works in two parts: (1) round and (2) subtract....
Purpose Return value Syntax =ROUNDUP(number,num_digits) number - The number to round up. num_digits - The place at which number should be rounded. Using the ROUNDUP function The ROUNDUP function rounds numbers up . Unlike standard rounding, where only numbers less than 5 are rounded down, ROUNDUP rounds all numbers up . For example: =ROUNDUP(3.001,0) // returns 4 ROUNDUP takes two arguments, number and num_digits . Number is the number to be rounded, and num_digits is the place at which the number should be rounded....
About This Shortcut This shortcut will select all cells that don’t match the value in the active cell in a column, or selection within a column. Note that this shortcut depends on the location of the active cell in a selection. You may want to start your selection with the active cell or move the active cell after the selection is made. About This Shortcut When you select a formula and use this shortcut, Excel will select cells that are directly referred to in the selected formula....
About This Shortcut Use this keyboard shortcut to select additional non-adjacent worksheets. When you hold the Control key down (Command on the Mac), and click another worksheet tab the active worksheet and clicked worksheet will be selected. You can select as many additional worksheets as you like. About This Shortcut This shortcut toggles Scroll Lock on and off. Note: the Scroll Lock status message may or may not be visible on the left side of the status bar, since it can be disabled....
About This Shortcut Inside a cell, this shortcut will select from the cursor to the end of the text. About This Shortcut Inside a cell, this shortcut delete text starting from the cursor to the end of the current line of text.
About This Shortcut This shortcut will select only visible cells in the current selection. This means hidden cells in the current selection will not be selected. Watch our video tip on this shortcut for a demonstration. In Mac Excel 2016, you can use the same shortcut as Windows, Alt ; About This Shortcut This shortcut enters cell edit mode with the cursor at the end of the last line of text in the cell....
