To hide columns, use Control + 0. You can also work with multiple columns at the same time. To unhide columns again, make a selection that spans the hidden columns, and use Control Shift 0. To hide rows, use Control + 9. Again, you’ll see a visual indication in row numbers to indicate that rows are hidden. To unhide rows again, make a selection that spans hidden rows, and use Control Shift 9....
The Format Task Pane was added in Excel 2013 and provides detailed controls for most chart elements. Previously, this sort of formatting was done with the Format dialog box. The main idea behind the Format Task pane is to provide a more complete set of controls to format things like charts without obscuring the worksheet below. The easiest way to display the Format Task Pane is to double-click on a chart....
where “xtable” is the named range E5:F10. Note: this is an array formula and must be entered with control + shift + enter. To parse the input string into an array or letters, we use MID, ROW, LEN and INDIRECT functions like this: LEN returns the length of the input text, which is concatenated to “1:” and handed off to INDIRECT as text. INDIRECT evaluates the text as a row reference, and the ROW function returns an array of numbers to MID: MID then extracts one character for at each starting position and we have: Essentially, we are asking VLOOKUP to find a match for “a”, “b”, and “c” at the same time....
which returns the 5 unique values in group A, as seen in E5:E9. Notice we are picking up the value “A” directly from the header in cell E4. Insider filter the expression C5:C16=E4 returns an array of TRUE FALSE values like this: This array is used to filter data, and the FILTER function returns another array as a result: This array is returned directly to the UNIQUE function as the array argument....
A basic understanding of fill handle in Excel could save you some time and make you more productive. What’s this Fill Handle in Excel? ‘Fill Handle’ is a tool that you can use to autocomplete lists in Excel. For example, if you have to enter numbers 1 to 20 in cell A1:A20, instead of manually entering each number, you can simply enter the first two numbers and use the fill handle to do the rest....
Data forms the backbone of any analysis that you do in Excel. And when it comes to data, there are tons of things that can go wrong – be it the structure, placement, formatting, extra spaces, and so on. In this blog post, I will show you 10 simple ways to clean data in Excel. #1 Get Rid of Extra Spaces Extra spaces are painfully difficult to spot. While you may somehow spot the extra spaces between words or numbers, trailing spaces are not even visible....
Solver is an Excel add-in that helps you perform what-if type analysis. You can find optimal outcomes subject to given constraints. For example, you could use Solver to find the maximum profit based on your financial models. The Solver add-in works by iterating through many scenarios until it finds the most optimal outcome. Solver is an indispensable tool for any sort of optimization problem in Excel. This post is going to show you how to get the Solver add-in for Excel....
Where “In” is the Excel Table on the left, “Out” is the table in the middle. The key to this approach is to use Excel Tables, because Table ranges automatically expand to handle changes in data. This means we can get a total of all incoming red items with: And a total of all outgoing red items with: In both cases, the SUMIFS function generates a total for all red items in each table....
rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: nper - the total number of payment periods for the loan, 60, from cell C8. pv - The present value, or total value of all payments now, 5000, from cell C5. start_period - the first period of interest, 1 in this case, since we are calculating principal across the entire loan term....
The results in column C will update automatically when values in column B change. To convert from Celsius to Fahrenheit, use a “C” for Celsius and an “F” for Fahrenheit like this: Note: unit strings are case sensitive. Both the “C” and “F” must be uppercase. Fahrenheit to Celsius To convert from Fahrenheit to Celsius, the formula would be: Other conversions You can use CONVERT to convert weight, distance, time, pressure, force, energy, power, magnetism, temperature, liquid, and volume....
where “group” is the named range B5:B14, and “data” is the named range C5:C14. Note: this is an array formula and must be entered with control + shift + enter. To apply criteria, we use the IF function inside MODE to filter values in a range. In this example, the IF function filters values by group with an expression like this: This compares each value in the named range “group” against the value in E5, which is “A”....
where data (O5:S9), vunits (N5:N9), and hunits (O4:S4) are named ranges, as explained below. This formula uses a lookup table with named ranges as shown below: Named ranges: data (O5:S9), vunits (N5:N9), and hunits (O4:S4). Introduction The goal is to convert an expense in one time unit, to an equivalent expense in other time units. For example, if we have a monthly expense of $30, we want to calculate an annual expense of $360, a weekly expense of $7....
If the number in column B is negative, MAX returns zero. Otherwise, MAX returns the original number. With the MAX function The MAX function provides an elegant solution: This formula takes advantage of the fact that the MAX function works fine with small sets of data — even two values. If the value in B5 is a positive number, MAX will return the number as-is, since positive numbers are always greater than zero....
In this case, we need a formula that returns FALSE as long as entries in C6:C9 sum to a total equal to or below 1000. We use the SUM function to sum a fixed range and then simply compare the result to 1000 using less than or equal to. Note the range C6:C9 is entered as an absolute reference to prevent the reference from changing automatically for each cell that data validation is applied to....
where “rate” is the named range H4. The EFFECT function calculates the effective annual interest rate based on the nominal annual interest rate, and the number of compounding periods per year. To demonstrate how this works, the table shown in the example is set up with various compounding periods in column C. The nominal interest rate is provided in cell H4, which is the named range “rate”. The formula in D5 is: Because named ranges behave like absolute references, this formula can simply be copied down the table....
Date configuration By default, ACCRINTM will calculate accrued interest from the issue date to the settlement date. If you want to calculate total interest from the issue date to the maturity date, supply maturity date instead of settlement date. Example In the example shown, we want to calculate accrued interest for a bond with a 5% coupon rate. The issue date is 5-Apr-2016, the settlement date is 1-Feb-2019, and maturity date is 15-Apr-2026....
The EXACT function takes two arguments, text1 and text2, which should be valid text strings. If these values are entered directly into the function, they should be enclosed in double quotes (""). Examples Below are two examples of the EXACT function used with hardcoded strings. In the first example, the strings are identical, in the second example, the only difference is the capital “A”: In the example shown, the formula in D6, copied down the column, is: You can also use a normal equals sign (=) in a formula, but the comparison is not case sensitive: To count cells that contain specific text, taking into account upper and lower case characters, you can combine EXACT together with the SUMPRODUCT function like this: Detailed explanation here....
To handle errors, Excel has provided a useful function – the IFERROR function. Before we get into the mechanics of using the IFERROR function in Excel, let’s first go through the different kinds of errors you can encounter when working with formulas. Types of Errors in Excel Knowing the errors in Excel will better equip you to identify the possible reason and the best way to handle these. Below are the types of errors you might find in Excel....
Example The IFNA function can be used to trap #N/A errors that may occur with the VLOOKUP function. In the example shown, the formula in F5, copied down, is: where xtable is the named range B5:C12. When the lookup value in column E is found in xtable, VLOOKUP returns the exchange range normally. When the lookup value is not found, VLOOKUP returns #N/A, and IFNA catches this error and returns “Not found”....
Another use for the Name Box is to navigate quickly to any range in a worksheet. If you type Z100 into the Name Box, the active cell will move to that address. If you type A1:A10 into the Name Box, that range will be selected. Finally, if you have one or more named ranges in a workbook, the Name Box behaves like a drop down menu. You can select and navigate quickly to any name....
