How to fill blank cells in Excel

Ok heres a quick tip on how to fill blank cells in Excel with zero or NA. Its simple find and replace function in Excel.

Copy the exact formula without shifting range

So you have just entered the SUM formula on 24th row on Excel, and wish to put an AVERAGE formula just below it. If you copy the formula for SUM (fig 1) which is just above it, the range will change automatically (fig 2.)

Sum of visible cells only in Excel

The SUM function in Excel provides the total SUM of all the cells mentioned in the range, be it hidden or not. Now, lets assume that it is the nth version of Excel working and you have hidden many rows, which you found to be irrelevant at that point in time. How will you find the sum of the visible cells only?

Resize your images before upload

I thought I had shared this tip earlier, seems i have missed out a lot of small little tips. I shall be covering them in the days to come. This tip is to reduce the size of bulky images without losing much on the quality, before making an upload to save your bandwidth.

Lesser known shortcuts in Excel

I have been using lots of shortcuts in Excel, and some of them are lesser known to people.
Here's a list, so that  it could improve your productivity more.

Note: Plus sign used here mean "and/ along with" and not the "+" operator

Separate first and last names in Excel

So you have got a list of full names, but require to sort it down by their first names. Excel has given a wonderful tool call "Text to columns", but it may not work all the time, if there a space or a dot or something else. Here is a simple solution to do that using formulas.

Get HTML color code of an image

Ever wondered how you can get the HTML color code of an image you loved ! There are many third-party tools where, there is an color picker to pick the color you like. This trick is to get the RGB codes of an image using MS Paint.

View GIF animations in Windows 7

Level: Intermediate

If you have been playing with Windows 7, you might have observed that animated GIF files does not play animations by default. On the contrary, GIF files will only play in Internet Explorer only. If you think that you set "Windows Photo Viewer" as the default program instead, it still wont work.

Note: Some third-party tools like Irfanview claims to be able to do so.

Calculate the total workdays between two dates in Excel

Level: Advanced

Ever came up with a task of calculating how many work days are there in the current year, but have no clue how to do it. One option is to count in manually, second option is using a function in Excel. If you want to know what a function is, read my earlier post here.

Using formulas, functions and charts in MS Excel

Level: Beginner

This is in continuation to the first introductory post on spreadsheets; if you have missed it, read it here.
The whole intention of writing the Excel tutorial is to enable the use of its functions to manipulate/ massage the data. So skipping all the steps on operating, formatting, templates, styles etc, we leap forward into the use of formulas and functions in Excel.