Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Category: Others

Excel Tutorials for beginners, Intermediates and experts.

Convert column letter to number in Excel

To convert a column letter to an regular number (e.g. 1, 10, 26, etc.) you can use a formula based on the INDIRECT and COLUMN functions. See example below: Formula =COLUMN(INDIRECT(letter&”1″)) Explanation In the example shown, the formula in C5 is: =COLUMN(INDIRECT(B5&”1″)) How this formula works The first step is to construct a standard “A1” style reference using the column…

Convert column number to letter in Excel

To convert a column number to an Excel column letter (e.g. A, B, C, etc.) you can use a formula based on the ADDRESS and SUBSTITUTE functions. See example below: Formula =SUBSTITUTE(ADDRESS(1,col_number,4),”1″,””) Explanation In the example shown, the formula in C5 is: =SUBSTITUTE(ADDRESS(1,B5,4),”1″,””) The first step is to construct an address that contains the column number. We do this with…

Basic numeric sort formula in Excel

To dynamically sort data that contains only numeric values, you can use a helper column and a formula created with the RANK and COUNTIF functions. Formula =RANK(A1,values)+COUNTIF(exp_rng,A1)-1 Note: this formula is the set-up for a formula that can extract and display data using a predefined sort order in a helper column. One example here. Explanation In the example shown, the formula in D5 is: =RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1…

Basic text sort formula in Excel

To dynamically sort text values in alphabetical order, you can use use a formula based on the COUNTIF function. Formula =COUNTIF(range,”<=”&A1) Explanation In the example shown, the formula in C5 is: =COUNTIF(countries,”<=”&B5) where “countries” is the named range B4:B13 How this formula works This formula uses the “greater than or equal to” operator with text, something you might not have tried before.…

Basic error trapping example in Excel

To catch errors that a formula might trigger in a worksheet, you can use the IFERROR function to display a custom message, or nothing at all. See example below: Formula =IFERROR(formula,value_if_error) Explanation In the example shown, the formula in E5 is: =IFERROR(C5/D5,””) How this formula works In this example, the IFERROR function is used to trap and suppress the #DIV/0!…

Check if multiple cells have same value in Excel

To confirm that a range of cells all have the same value, you can use a formula based on the COUNTIF function. Formula =COUNTIF(range,”<>value”)=0 Explanation In the example shown, the formula in C9 is: =COUNTIF(C5:C8,”<>ok”)=0 Note: this formula is not case-sensitive, you can find a case-sensitive formula here. How this formula works This formula relies on the standard behavior of the…

How to count total number of cells in a rectangular range in Excel

If you need to count the total number of cells in a rectangular range, you can do so with the ROWS and COLUMNS functions. Formula =ROWS(range)*COLUMNS(range) Note that this formula only works for a single rectangular range of cells. Explanation In the example, the active cell contains this formula: =ROWS(B4:C9)*COLUMNS(B4:C9) How this formula works There is no built-in function for…

How to count total columns in range in Excel

If you need to count the number of columns in a range, use the COLUMNS function. COLUMNS counts the number of columns in and returns the result as a number. Formula =COLUMNS(range) Explanation In the example, the active cell contains this formula: =COLUMNS(B4:C9)

How to test a range for numbers in Excel

To test a range for numbers, you can use a formula based on the ISNUMBER and SUMPRODUCT functions. See example below: Formula =SUMPRODUCT(–ISNUMBER(range))>0 Explanation In the example shown, the formula in xxxx is: =SUMPRODUCT(–ISNUMBER(C5:C9))>0 How this formula works Working from the inside out, the ISNUMBER function will return TRUE when given a number and FALSE if not. When you supply a…

Check if multiple cells have same value with case sensitive in Excel

To verify that multiple cells have the same value with a case-sensitive formula, you can use a simple array formula based on the EXACT and AND functions. See example below: Formula {=AND(EXACT(range,value))} Explanation In the example shown, the formula in G5 is: =AND(EXACT(B5:F5,B5)) This is an array formula and must be entered with control + shift + enter How this…

Check if range contains a value not in another range in Excel

To test if a range contains any values (i.e. at least one value) not in another range, you can use the SUMPRODUCT function with MATCH and ISNA. The MATCH function receives a single lookup value, and returns a single match if any. In this case, however, we are giving MATCH an array for lookup value, so it will return an…

How to add sequential row numbers to a set of data in Excel

To add sequential row numbers to a set of data with a formula, you can use the ROW function. See example below: Formula =ROW()-offset Explanation In the example shown, the formula in B5 is: =ROW()-4 How this formula works When not given a reference, the ROW function returns the row number of the current row. In cell B5, ROW returns…

How to create dynamic named range with INDEX in Excel

This tutorials show examples one and two dynamic named ranges created. The first is created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand and contract when data is added or removed. Formula =$A$1:INDEX($A:$A,lastrow) Explanation This page shows an example of a dynamic named range created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand…

How to fill cell ranges with random number from fixed set of options in Excel

To quickly fill a range of cells with random numbers from a set of fixed options (i.e. 25,50,75,100), you can use a formula based on the CHOOSE and RANDBETWEEN functions. Formula =CHOOSE(RANDBETWEEN(1,4),num1,num2,num3,num4) Explanation In the example shown, the formula in B4 is: =CHOOSE(RANDBETWEEN(1,4),25,50,75,100) Which returns a random number from the numbers provided. How this formula works The CHOOSE function does…

How to generate random number between two numbers in Excel

To generate random integers between two numbers, you can use the RANDBETWEEN function. Formula =RANDBETWEEN(lower,upper) Explanation In the example shown, the formula in B5 is: =RANDBETWEEN(1,100) This formula is then copied down from B5 to B11. The result is random numbers between 1-100. Note that the RANDBETWEEN function will generate new numbers whenever a change is triggered on the worksheet.…

How to fill cell ranges with random text values in Excel

To quickly fill a range of cells with random text values, you can use a formula based on the CHOOSE and RANDBETWEEN functions. Formula =CHOOSE(RANDBETWEEN(1,3),”Value1″,”Value2″,”Value3″) Note that RANDBETWEEN will calculate a new value whenever the worksheet is changed. Once you have values in the range, you may want to replace the formulas with values to prevent further changes. Explanation In…

How to get random value from list or table in Excel

To get a random value from a table or list in Excel, you can use the INDEX function with help from the RANDBETWEEN and ROWS functions. Formula =INDEX(data,RANDBETWEEN(1,ROWS(data)),1) Note: this formula uses the named range “data” (B5:E104) for readability and convenience. If you don’t want to use a named range, substitute $B$5:$E$104 instead. Explanation In the example shown, the formula…

How to generate random times at specific intervals in Excel

To generate random times in at specific intervals you can use the RAND function with the FLOOR function. See example below: Formula =FLOOR(RAND(),”0:15″) Explanation In the example shown, the formula in B6 is: =FLOOR(RAND(),”0:15″) which generates a random time at a 15-minute interval. How this formula works The RAND function generates a decimal number between zero and 1. So, you…

How to generate random number weighted probability in Excel

To generated a random number, weighted with a given probability, you can use a helper table together with a formula based on the RAND and MATCH functions. Formula =MATCH(RAND(),cumulative_probability) Explanation In the example shown, the formula in F5 is: =MATCH(RAND(),D$5:D$10) How this formula works This formula relies on the helper table visible in the range B4:D10. Column B contains the…

How to generate random date between two dates in Excel

To generate random dates between two dates, you can use the RANDBETWEEN function, together with the DATE function. See example below: Formula =RANDBETWEEN(date1,date2) Explanation In the example shown, the formula in B5 is: =RANDBETWEEN(DATE(2016,1,1),DATE(2016,12,31)) This formula is then copied down from B5 to B11. The result is random dates between Jan 1, 2016 and Dec 31, 2016 (random dates in…

How to calculate percent variance in Excel

You can calculate a percent variance by subtracting the original number from the new number, then dividing that result by the original. For example, if the baseline number is 100, and the new number is 110: =(110-100)/100 This formula can be used to calculate things like variance between this year and last year, variance between a budgeted and actual values,…

How to calculate decrease by percentage in Excel

If you need to decrease a number by a certain percentage, you can use a simple formula that multiplies the number times the percent – 1. Formula =number*(1-percent) Explanation In the example, the active cell contains this formula: =C6*(1-D6) In this case, Excel first calculates the result of 1 – the value in D6 (.2) to get 0.8 which it…

How to get amount with percentage in Excel

If you have a total and a percentage that presents some amount of the total, you can calculate the amount represented by the percentage with a simple formula that multiples the total by the percentage. Formula = total * percent Explanation In the example, the active cell contains this formula: =C6*D6 To calculate the amount, Excel simply multiples the value…

How to get original number from percent change in Excel

To calculate the original number based on current value and known percentage change, you can use a simple formula that divides the current value by the percent plus 1. Formula =current/(percent+1) Explanation In the example, the active cell contains this formula: =C6/(B6+1) In this case, Excel first calculates the result of B6 + 1, then divides C6 by the result: =C6/(B6+1) =200/1.15 =173.913 Rounded result…

How to get original price from percentage discount in Excel

If you have a discounted price and know the discount percentage, you can calculate the original price with a simple formula that divides the discounted price by the result of 1 minus the discount percentage. See example below: Formula =price/(1-discount) Explanation How the formula works In the example, the active cell contains this formula: =C6/(1-D6) In this case, Excel first…

How to calculate percent change in Excel

If you need to calculate the percentage change between two values in Excel, you can use a formula that divides the change itself by the “old” value. A classic example would be to calculate the percentage between sales last month and sales this month. Formula =(new_value-old_value)/old_value Explanation In the example, the active cell contains this formula: =(D6-C6)/C6 In this case,…

How to calculate percentage discount in Excel

If you have a discounted price and an original price, and you want to know the discount as a percentage, you can calculate the percentage discount using a formula that divides the discounted price by the original price and then subtracts the result from one. Formula = 1-(discount_price/original_price) Explanation In the example, the active cell contains this formula: =1-(D5/C5) In…

How to calculate percentage of total in Excel

To calculate percent of a total (i.e. calculate a percent distribution), you can use a formula that simply divides an amount by the total. Formula =amount/total Explanation In the example shown, the formula in D6 is: =C6/$C$11 Note: the result is formatted with Percentage number format to show 25%, 10%, etc. How the formula works In the example, D6 contains the formula…

How to calculate profit margin percentage in Excel

If you need to calculate a profit margin, you can easily do so with a simple formula that uses the sale price and the cost. Formula =(price-cost)/price Explanation In the example shown, the first formula looks like this: =(B4-C4)/B4 Make sure you use parentheses to control the order of operations. Note that the result will be a decimal like 0.40,…

How to calculate total from percentage in Excel

If you have an amount and the percentage that the amount represents of a total, you can calculate the total with a formula that simply divides the amount by the percentage. Formula = amount / percent Explanation In the example, the active cell contains this formula: =C6/D6 Excel simply divides the value in cell C6 by the percentage value in…

How to increase by percentage in Excel

If you need to increase a number by a certain percentage, you can use a simple formula that multiplies the number times the percent + 1. Formula =number*(1+percent) Explanation How this formula works In the example, the active cell contains this formula: =C6*(1+D6) In this case, Excel first calculates the result of 1 + the value in D6 (.2) to…

How to calculate percent of goal in Excel

If you need to calculate percent of goal, you can do so with a simple formula that divides the actual by the goal amount, with the result formatted using the percentage number format. This same formula can be used to calculate things like percent of target, percent of budget, percent of forecast, and so on. Formula =actual/goal Explanation In the…

How to calculate percent of students absent in Excel

To calculate percent of students attending a class, given a total class size and the number of students attending, you can use a simple formula that divides students absent (calculated by subtracting attending from total) by the total. The result must be formatted using the percentage number format. Formula =(total-attended)/total Note: when working manually, percentage results are calculated by dividing one…

How to calculate percent sold in Excel

To calculate percentage sold, you can use a simple formula that divides sold amount by the total amount. Formula =sold/total Note: when working manually, percentage results are calculated by dividing one number by another, then multiplying by 100 to express as a percentage. However, because Excel can display decimal or fractional values automatically as a percentage by applying the Percentage number format, there…

How to calculate project complete percentage in Excel

To calculate the percentage complete for a project with a list of tasks, you can use a simple formula based on the COUNTA function. Formula =COUNTA(range1)/COUNTA(range2) Explanation In the example shown, the formula in F6 is: =COUNTA(C5:C11)/COUNTA(B5:B11) How this formula works At the core, this formula simply divides tasks complete by the total task count: =complete/total which is then formatted…

List worksheet index numbers in Excel

To list the index numbers of sheets in an Excel workbook, you can enter the sheet names, then use a formula based on the SHEET and INDIRECT functions. The SHEET function then returns the current index for each sheet as listed. Formula =SHEET(INDIRECT(name&”!A1″)) Explanation In the example shown, the formula in C5 is: =SHEET(INDIRECT(B5&”!A1″)) How this formula works The INDIRECT…

List sheet names with formula in Excel

To list worksheets in an Excel workbook, you can use a 2-step approach: (1) define a named range called “sheetnames” with an old macro command and (2) use an INDEX formula to retrieve sheet names using the named range. Formula =GET.WORKBOOK(1)&T(NOW()) Note: because this formula relies on a macro command, you’ll need to save as a macro-enabled workbook if you want the formula…

How to get Excel workbook path only

If you want to get the workbook path (directory) only, you can do so with a formula that uses the LEFT and the FIND function. The result will look something like this: Drive:\path\to\file\ //Windows Drive:path:to:file: //Mac Formula =LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))-1) Explanation How the formula works The cell function is used to get the full file name and path: CELL(“filename”,A1) The result looks…

How to create dynamic worksheet reference in Excel

To create a formula with a dynamic sheet name you can use the INDIRECT function. Note: The point of this approach is it lets you to build a formula where the sheet name is a dynamic variable. So, for example, you could change a sheet name (perhaps with a drop down menu) and pull in information from different worksheet. Formula =INDIRECT(sheet_name&”!A1″)…

Create dynamic workbook reference to another workbook in Excel

To build a dynamic worksheet reference – a reference to another workbook that is created with a formula based on variables that may change – you can use a formula based on the INDIRECT function. See example below: Formula =INDIRECT(“‘[“&workbook&”]”&sheet&”‘!”&ref) Explanation In the example shown, the formula in E6 is: =INDIRECT(“‘[“&B6&”]”&C6&”‘!”&D6) How this formula works The gist of this formula…