Category Archives: Excel

Amount to Words macro

Here is a new year gift for the Indian customers. Converting Amount to Words is always a problem. Many application require it and that code is always written manually, within the application. What is really needed is a comprehensive Excel function. Many people have written lots of Excel functions. But neither of them is specific to India, nor is it comprehensive.

We (Raj and me) had created it in 2005, but I had forgotten to publish it on the blog. We have done good amount of testing – but still you have to use it at your own risk. No guarantees. Read on to download, install and use the Excel function.

Amount to Words banner

Continue reading Amount to Words macro

Excel Table AutoFill Formulas not working

Table has many benefits. One of them is Table AutoFill Formulas. If you add a formula in any table column, it copies the formula automatically. Further editing of the formula also is automatically copied to all other cells in the column. Sometimes, the AutoFill stops working. How to re-enable this feature? Here are the instructions
(Reading time 3 min)

Continue reading Excel Table AutoFill Formulas not working

Excel IF function – Dos and Don’ts

imageSo far, I have not written an article about the humble IF function. But in the last few months I came across many customers who asked me to explain how the IF function works. Surprised? The reason is that many people use the Excel IF function, but they do not understand its nuances.

Usually someone has created a file and is handed over from person to person. Worse still, those who know how to use the IF function misuse it! So here it is a simple, short and practical description of how to use the IF function.

Continue reading Excel IF function – Dos and Don’ts

How to Filter on Blanks quickly

A simple but very useful method. All of us use AutoFilter in Excel. The dropdown shows unique values in the column. Sometimes we just want to see the rows with blank cells. Unfortunately, the (blank) item appears at the end of the list. If the data contains many unique values, you have to waste time and scroll manually to the bottom of the long list. Often, after reaching the list we realize that we should actually have unselected all the items first. Then you go up again! Pathetic. Here is a smart way to Filter on Blanks instantly.

Continue reading How to Filter on Blanks quickly

VLOOKUP based text grouping

You must read this article first: Grouping Text in Pivot Tables. This article explains how to combine multiple text items into a single group. This is used for classifying or for correcting spelling mistakes. If new items appear, they remain ungrouped. That way, you have to keep doing the Grouping update repetitively. An alternative is to use VLOOKUP based text grouping. Read on to find out how this is done.

Continue reading VLOOKUP based text grouping

How to use FormulaText function in Excel

FormulaText function in Excel was introduced in version 2013. It requires just one parameter – the cell from which you want to display the formula.  Even if you select a range, it will return the formula in the top left cell.

image

Detailed syntax.

When to use the FormulaText function in Excel

Here are three practical scenarios.

  1. It is very helpful in documenting important formulas.
  2. It can also be used as a part of an audit process.
  3. Trainers can use it to teach formulas more effectively.
  4. I find it useful for creating more readable screenshots while creating learning content for Excel.