Category Archives: Excel

How to hide cell contents in Excel

This is a short article. We know how to hide rows, columns and sheets. Did you know you can hide cell contents as well in Excel?

When we want to hide cell contents, the obvious thought is to change the font color to white. It works, for some time. But the moment you select the cell, it will show the value as the background color changes. So here is the right way.

Continue reading How to hide cell contents in Excel

How to Really Remove Duplicate Rows in Excel

Watch this five minute video to understand what we “really” mean by Remove Duplicate Rows in Excel. The default option removes the duplicate rows automatically. Most of us want to “see” the duplication. That is what leads to the confusion. Not to worry. Learn the right method now.

Remove duplicates QRScan this code and watch the video on your mobile phone and try it out on your laptop.
Download the sample file if you want to try it out yourself.
Continue reading How to Really Remove Duplicate Rows in Excel

How to calculate YOY growth in Excel Pivot Table

Estimated reading time 3 min. Works with ALL versions of Excel.
The data is a list of sales transactions, two columns – amount and date.
We have 5000 transactions over many years. We want to know how the business grew year on year. Here are the steps…

SNAGHTML48223f                  image

Continue reading How to calculate YOY growth in Excel Pivot Table

Excel: How to copy only Visible Cells

This is a frequent frustration. You copy some data which contains hidden rows and columns. You blindly assume only the visible cells will be pasted.  But when you paste it, even the hidden cells appear. This is non-intuitive and disturbing. Fortunately, there is an easy solution available.  Read on to find out how (Estimated reading time 3 min)

SNAGHTML158f9fcd

Continue reading Excel: How to copy only Visible Cells

How to work on Excel when it is busy

This is a common requirement. We have started some time consuming operation like adding a field to a large pivot table, or fetching data from a database, or refreshing some complex calculations. In such cases Excel seems to be unresponsive. Not only the current file is unusable, other files opened are also not editable. Things just freeze till the time and resource consuming activity finishes.

Of course, that is a total waste of time. You want to do something else on another Excel file during this time. The solution is to create another instance of Excel.

  • When you are about to start a time consuming operation, make sure no other Excel files are open. Start the operation on the file and leave it alone.
  • Now right click on the Excel icon in the Taskbar and choose Excel (whichever version) again. This way you open an independent copy of Excel. This copy is not busy at all.Open an independent copy of Excel when it is busy processing another copy
  • Now you open other Excel files here and continue working while the other copy is executing the time consuming job. That’s it.

This concept is not applicable just to Excel. The same logic applies to any application which can get busy and block you from working on other files.

How to generate random data based upon known percentage distribution using Excel

Let us say I want to create sample data for my analytics class for a customer. I dont have access to the actual customer data. But I want the data to look familiar to the participants. I want to use the names of their products in sample data containing 1000 rows. I could use Randbetween() with Vlookup but that gives me almost equal distribution for all products. I want to make the data look realistic. I want to use the market share values of their products to generate the sample data. Here is how you do it. 3.5 minute video.