Tag Archives: Excel

Consolidate Cross-Tab Data in minutes

Cross-tab data is where you have row headings as well as column headings. Consolidating (combining) multiple blocks of such data is a laborious process. Usually the rows and columns do not match across data blocks. You need to do a lot of copy-paste to bring them in the correct order before you can add a formula. Using a 20 year old unknown but powerful feature of Excel you can Consolidate Cross-Tab Data in minutes. You just select the data blocks and let Excel do the dirty work of matching row and column names.

Additional Resources : Consolidate Cross-Tab Data

I have already covered this topic as a step-by-step article.  Now I have created a video (9 min). You can download the sample file used in the video and try it yourself.

Continue reading Consolidate Cross-Tab Data in minutes

Pivot Report Sharing

Here is a fast paced, 8 minute video which will revolutionize Pivot Report Sharing. Using OneDrive or SharePoint, control the visibility and provide full interactivity. Reports render instantly even with large files and low bandwidth.

Pivot Report Sharing articles

New, Improved way of delivering Excel reports: Part 1
New, Improved way of delivering Excel reports: Part 2
New, Improved way of publishing Excel reports: Part 3

Excel risk Mitigation

This article is for CFOs, CEOs and other members of Senior Leadership Team. It requires just 10 minutes to read. But it will reduce your personal as well as organizational Operational Risk significantly.  Notice the green mark  below.
This is the biggest Excel risk.

Excel Risk - Demo

If you see even a single green mark, you should send the file back.
Ask the sender to handle all the green marks and send the revised file to you.
Of course, they may not know what you are talking about.
Ask them to read these 5 articles: Knowledge Pack: Green Marks in Excel

The rule is, Excel sheet with Green Marks should not be sent to anybody. It is the sender’s responsibility to remove all errors – not the recipient’s.

Continue reading Excel risk Mitigation

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