tag:blogger.com,1999:blog-84595523409120058162011-09-22T11:22:06.643+05:30This web log contains tips that i want to share with all of you. Some of them are my own where as many from internet or from other sources. This blog is for those who knows basics of MS Excel and wanted to go further.Rajendra Shewadehttp://www.blogger.com/profile/16074520042701675079noreply@blogger.comBlogger101100tag:blogger.com,1999:blog-8459552340912005816.post-17466803870739680662008-01-19T10:51:00.000+05:302008-01-19T10:53:47.065+05:30

This type of formatting of table is easily possible with Excel 2007, but before that it was a pain.Let’s see how we can use conditional formatting to our advantage in this area.‘Zebra Stripes’ is basically coloring your table’s each alternate row, this will increase the readability of the table.Let’s see how to achieve this in step by step mode.1. Select the entire table.2. Goto Format -

Rajendra Shewadehttp://www.blogger.com/profile/16074520042701675079noreply@blogger.com2tag:blogger.com,1999:blog-8459552340912005816.post-43724518716691664902007-11-10T12:52:00.000+05:302007-11-10T12:53:32.558+05:30

This is one of the rarely used functions in excel as many use it’s alternative Max().Let’s first understand the difference between them so that we will come to know what are the situations where we can use Large instead of Max.Max function returns the maximum value from the given range, where as Large function 1st sorts the given range internally and returns the i’th from the top.What i mean is

Rajendra Shewadehttp://www.blogger.com/profile/16074520042701675079noreply@blogger.com2tag:blogger.com,1999:blog-8459552340912005816.post-59483419609094124392007-11-09T12:26:00.000+05:302007-11-09T12:28:26.788+05:30

I have a column of dates (column G) in date format and I’d like another column to display the fiscal or academic year for that date. I’d basically like a formula that will look at the date in column G and tell me if it is from 2001-02 or 2002-03, etc using July 1-June 30 as the fiscal or academic year. How do I do this?Formula :=IF(G3>DATEVALUE(”06/30/” & YEAR(G3)),”Academic Year ” & YEAR(G3) & ”

Rajendra Shewadehttp://www.blogger.com/profile/16074520042701675079noreply@blogger.com0tag:blogger.com,1999:blog-8459552340912005816.post-24690520922682490072007-10-07T12:52:00.000+05:302007-10-07T12:56:04.851+05:30

Many a times you have a situation where in you have to type in again the same thing you have typed some time before. What you do? go select that cell again and copy paste....Here is a way out to quick select list, just use (Alt + down arrow) and the entire list will appear just like auto filter in place. Then you can select whatever option you want.

Rajendra Shewadehttp://www.blogger.com/profile/16074520042701675079noreply@blogger.com0tag:blogger.com,1999:blog-8459552340912005816.post-52008350977816394792007-10-07T02:02:00.000+05:302007-10-07T02:10:13.225+05:30

Quick sheet add (Shift + F11)Quick chart add (Select source data and press F11)Quick copy and edit value of the cell in cell below (Ctrl + Shift + ')Quick copy and edit formula of the cell in cell below (Ctrl + ')Edit or create a comment in the current cell (Shift + F2)

Rajendra Shewadehttp://www.blogger.com/profile/16074520042701675079noreply@blogger.com0tag:blogger.com,1999:blog-8459552340912005816.post-3879156930426097132007-09-29T19:02:00.000+05:302007-09-29T19:09:47.735+05:30

'Ctrl + A'Use this key immediately after typing the function name e.g. =Sum, It will open up the function argument window, which provides details about input parameters of that function.'Ctrl+shift+A'Use this key after typing the function name and '(' e.g. =Sum(, It shows the syntax for the function in-place, this proves useful many times.

Rajendra Shewadehttp://www.blogger.com/profile/16074520042701675079noreply@blogger.com0tag:blogger.com,1999:blog-8459552340912005816.post-54490835048186469422007-09-10T23:38:00.000+05:302007-09-10T23:39:04.687+05:30

Many of the times there are situations where you wanted to have one confidential worksheet where you can keep your secrete data and which you refer on other sheets, or may be only some hidden worksheet whereby only specific person knows about it. And you wonder how to do that in Excel. Yes there is a way in Excel, no I am not talking about sheet ->hide, all knows about it. It hides sheets from

Rajendra Shewadehttp://www.blogger.com/profile/16074520042701675079noreply@blogger.com0tag:blogger.com,1999:blog-8459552340912005816.post-259090824215716782007-07-08T22:22:00.000+05:302007-07-08T22:39:46.671+05:30

I wanted to write some thing of my own but when I found many things related to this on Internet, so I am posting one of the links here. Please read it and understand it deeply, if you really want to create dynamic pivot tables, charts, data validations etc.The link below have the information about how to create and make use of 'Named Ranges', I like it among all as it is simple with out much

Rajendra Shewadehttp://www.blogger.com/profile/16074520042701675079noreply@blogger.com1tag:blogger.com,1999:blog-8459552340912005816.post-21630453800400988642007-07-01T19:13:00.000+05:302007-07-08T22:39:46.671+05:30

I have come across many times with this question, and some people even say pivot table is not useful because they do not support additional data in the main data table. But as many other things in excel are not yet fully explored, the same is with this function. Most of the time, we do not get answer to our problems because we always look at it from the same angle. Try to look at any problem

Rajendra Shewadehttp://www.blogger.com/profile/16074520042701675079noreply@blogger.com0tag:blogger.com,1999:blog-8459552340912005816.post-3272151189193655822007-01-22T00:47:00.001+05:302007-01-22T00:49:45.710+05:30

Before starting this blog I just wanted to make one thing clear, this blog is for those who knows basics of MS Excel and wanted to go further. Please do not read further if you want to have some thing like 'Getting started .... with MS Excel". Lot's of material is available on internet read that.

Rajendra Shewadehttp://www.blogger.com/profile/16074520042701675079noreply@blogger.com