31 Aug 2014

Find and remove sensitive and hidden data in Excel

In Excel, there may be sensitive information, hidden cells, hidden rows or columns that needs to be deleted before sharing with someone. If you use Excel 2007, 2010 or Excel 2013, it is easily for you to remove the unwanted hidden rows or columns.

How to hide cells contents in Excel

This small trick will only change the formatting of the Excel cells and will not change the content. They contents are still there and shall be accessible for formulas, charts.

Calculate difference between two dates

I recently came up with a hidden function in Excel called DATEDIF. As the name suggests the job of this function is to calculate the difference between two given dates (DATE DIF).
I consider this function as hidden because, for some reason Microsoft has decided not to document this function. And because of this you won’t find this function in the Formula Tab. To verify this, try and type =DATE in any cell. You will see, Excel enlists all the functions that start with the word “date” but it doesn’t shows DATEDIF.


Using HLOOKUP in Excel

Many of the Excel users are known to have skills if they are able to use VLOOKUP formula.
Alternatively there is a little complex formula called HLOOKUP where H stands for “Horizontal” and hence it is often called as Horizontal Lookup. It is a little confusion to most of the users how to use it. The basic task of HLOOKUP function is to search for a value in the topmost row of a table, and then return a corresponding value in the same column from a row you specify.

30 Aug 2014

Create a custom list to sort data

It has been quite some time i have written a post. If you like to sort information differently as per your choice, and not in the classic fashion of ascending or descending sort order in Microsoft ® Excel ®, you can easily customize it. Lets get to it directly.

22 Aug 2014

Few time saving Excel tips and tricks

Everyone wants data at the snap of the finger. Even a small mistake can take one to spend hours for resolution. I am listing down a few tips that will save lot of time.

Quickly find merged cells in Excel

You are in a hurry and want to prepare some data in a minute, but unable to sort, filter or use pivot table. You are left wondering what and where went wrong, until you see the error.



Imagine you have a thousands of rows, it would be a huge tasks to find out where the meged cell is.
Here is a simple tip to find it immediately.

1) Open Find option by pressing Ctrl+F


2) Click Format. You wiill see Formatting options.
3)  Go to Alignment tab and select "Merge Cells"

4) Click Ok to close the option. Go to the Find option and click "Find all"

5) You will see all the merged cells listed.


Disqus

comments powered by Disqus