Blog

Formula Errors in Excel

If you are a person who use Excel a lot, you might have encountered with these Error Codes like #DIV/0!, #NAME?, #N/A, etc. Each of these error codes tell us something. It gives us information on how to troubleshoot an incorrect formula. Following are the most common Error codes in Excel and this blog post…

Show Formulas in Excel

3 different methods to display formulas used in an Excel worksheet. Method 1 Go to the Formulas tab in the Excel ribbon, Click on Show Formulas Once you click on this button, every cell in the worksheet will display the formula used in that cell, instead of the resulting value. To turn off of this…

Convert Hijri to Gregorian

Excel supports Hijri and Umm-Al-Qura dates. This means we can display a Gregorian date in Hijri just by changing the Number Formatting of the corresponding cell. To display the Hijri equivalent of a Gregorian date, Right-click on the cell containing date > Format Cells > In the Format cells dialog, select Date from the options under Category…

Limit the Character length of a cell in Excel

Suppose, you want to restrict the number of characters that can be entered into an Excel cell. How will you solve this problem? The answer is Data Validation! Data Validation in Excel can be used to control the range, type & length of the data that can be entered into a cell. This blog post…

Overline text in Excel

This blog post is about 2 methods to create Overlined text in Excel. Method 1 Enter the text to be overlined into a cell. Here, I am using cell B3. Use the following formula to create the overlined version of the text in the cell B3. Make sure to turn on the Wrap Text option…

Square and Square Root of a Number in Excel

Calculate the Square of a Number The following are 3 different formulas to calculate the Square of the number in the cell B3 Formula 1 =B3*B3 Formula 2 =POWER(B3,2) Formula 3 =B3^2 Calculate the Square Root of a Number The following are 3 different formulas to calculate the Square Root of the number in the…

Excel Formula to Convert Numbers to Words

Have you ever thought of an Excel formula that will convert Numbers to Words? i.e. A formula that can convert the number 456,571 into Four Hundred and Fifty Six Thousand Five Hundred and Seventy One. In this blog post, I am sharing two Excel formulas that can convert Numbers in Words. Pete M’s Formula =CHOOSE(LEFT(TEXT(A1,”000000000″))+1,,”One”,”Two”,”Three”,”Four”,”Five”,”Six”,”Seven”,”Eight”,”Nine”)&IF(–LEFT(TEXT(A1,”000000000″))=0,,IF(AND(–MID(TEXT(A1,”000000000″),2,1)=0,–MID(TEXT(A1,”000000000″),3,1)=0),”…

Interpolation in Excel

You are given the X, Y Coordinates of 2 points, (99.5,39.5) and (201.1,45.7) and asked to find the Y coordinate of an intermediate point with an X coordinate of 115.7. How will you solve this problem? The answer is Interpolation! Interpolation is the method of estimating ‘unknown values’ that fall between ‘known values’. In this…

Pick a random value from a list in Excel

In this blog post, I will explain two Excel formulas that can be used to pick a random value from a List. Following is a list of 6 contestants for a particular game. For each round of the game, a player should be randomly selected from this list. Let’s see how to pick a random…

Remove Macros from an Excel Workbook

In this blog post, I will show you 3 different ways to remove Macros from an Excel workbook. Saving the Workbook as XLSX file This is the easiest way to remove macros from an Excel workbook! XLS, XLSM, and XLSB are the only Excel file formats that can hold VBA projects or Macros. If you save an Excel…

How to create a Dependent Drop-down list in Excel

A Drop-down list that depends on the selection made using another Drop-down list is called a Conditional or Dependent drop-down list. Following is an example of the same. As you can see, when we go for the degree B.sc in column C, the options are Physics, Mathematics & Chemistry. For B.A, the options are Malayalam,…