This is a picture blog. Self-explanatory! Learn how to Sum visible cells only using the Aggregate function.
Works only in vertical direction (not horizontal). It is best to use it with Excel Tables as shown above. That way, adding more data will automatically update the formula results.
Works from Office 2010 onwards.
This is the continuation of the earlier article about Validations. Data Validation using Formula is not very well understood. Hence this article. Try it out and you will immediately know where you can use it. Reading time 9 min.
Continue reading Data Validation using Formula
This is a very old feature. But it is still very useful. Spend 10 minutes to learn where you should use and not use Range Names in Excel.
Continue reading Where and when to use Range Names 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
The more I work with customers, the more I realize that data quality is still a very big problem. Data Validation in Excel is an effective solution to this problem. Here is a short guide for you. Reading time 12 min.
Continue reading Prevent data-entry mistakes using Data Validation 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.
Scan 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
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…
Continue reading How to calculate YOY growth in Excel Pivot Table
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)
Continue reading Excel: How to copy only Visible Cells
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.
- 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.
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.