tag:blogger.com,1999:blog-21730390089527162212026-07-24T11:31:04.595+05:30The knowledge of Microsoft Excel in your hand.Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.comBlogger47125tag:blogger.com,1999:blog-2173039008952716221.post-89903434048391286312011-08-04T11:30:00.004+05:302011-08-04T15:22:43.613+05:30

1. Select a cell in the Pivot Table report.2. Press Alt+jt+j+f or you select option than Formulas and then Calculated Field.3. In the Insert Calculated Field dialog box, type the formula name in the Name box.4. In the Fields list box, select the first field name to insert and click Insert Field. The field name is copied into the Formula box.5. Type / (in this example), repeat step 4 to insert

kamit.sonyhttp://www.blogger.com/profile/00736922690327628443noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-42727038003308655682011-07-17T23:09:00.003+05:302011-07-17T23:30:48.295+05:30

Use Excel Countif function when you want to see the count of a specific criteriaappear in a selected range.For Example :1.How many times does the name "jack"appear in a list of worker names?2.How many prices higher than 1000 are there in a given price list?see this video for reference

kamit.sonyhttp://www.blogger.com/profile/00736922690327628443noreply@blogger.com1tag:blogger.com,1999:blog-2173039008952716221.post-37807607272429148862011-07-16T23:34:00.010+05:302011-07-17T13:11:17.175+05:30

Hi friends this Post is Actually Posted by Mr. Amit Kumar. Specially thanks to him. Mr Amit is our new contributor and he is very happy to share his knowledge with us The function “OR” is used in a same manner like "AND" function, but its meaning is slightly different. Instead of checking if all its parts is met (as is the case of the “AND” function), it checks if at least one of the

kamit.sonyhttp://www.blogger.com/profile/00736922690327628443noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-88733944934598283542011-07-16T18:58:00.004+05:302011-07-17T13:13:37.417+05:30

Sometimes you need to check more than one criteria with the If statement. You can Use "AND" functions for that. For example: Only if the student has grades greater than 80 in HINDI, greater than 80 in English and greater than 80 in ,MATH only then write “good”, else write “Average”. Name HINDI ENGLISH MATH REMARKS A 81 81 81 GOOD B 80 81 81 AVERAGE The way to implement such a case is using

kamit.sonyhttp://www.blogger.com/profile/00736922690327628443noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-16870510211241524682011-07-16T18:01:00.006+05:302011-07-17T13:17:10.935+05:30

whenever you want to present some text according to a criteria: If the criteria is met then present "whatever want to present", in "True Place" other wise if the criteria does not match than it show second word "whatever you want" in "False Place". Here you see the Examples: _______________ Using if with Text: if a house value is higher than 10lac then you write "It is too expensive", else

kamit.sonyhttp://www.blogger.com/profile/00736922690327628443noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-71055674428106623972011-06-28T18:41:00.000+05:302011-06-28T23:37:00.807+05:30

The IF function, one of Excel's logical functions, tests to see if a certain condition in a spreadsheet is true or false. The syntax for the IF function is: =IF ( logical_test, value_if_true, value_if_false ) logical_test - a value or expression that is tested to see if it is true or false. value_if_true - the value that is displayed if logical_test is true. value_if_false - the value

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-9176073412962642302011-06-27T23:12:00.000+05:302011-06-27T23:12:23.842+05:30

Go To function :- Are you familiar with the Go To Special functionality in Excel? This is another chunk of functionality that many Excel users either don’t know exists or don’t take advantage of. Check it out in Excel; select Edit > Go To and then click the Special button at the bottom left corner of the Go To dialog box. If you haven’t used this yet, let me tell you—this handy little dialog

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-22629561972119469172011-06-25T20:37:00.000+05:302011-06-25T20:37:31.716+05:30

VBA is based on Visual Basic, a programming language derived from BASIC. BASIC stands for Beginner’s All-Purpose Symbolic Instruction Code. BASIC is designed to be user-friendly, because it employs recognizable English words (or quasi-recognizable variations on them) rather than abstruse and incomprehensible programming terms. In addition to its English-like diction, BASIC’s designers endeavored

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-27446746484789883482011-06-24T15:50:00.001+05:302011-06-24T15:54:49.430+05:30

Excel conditional formatting is a hidden and powerful gem that when used well, can change the outlook of your project report / sales budget / project plan or analytical outputs from bunch of raw data in default fonts to something truly professional and good looking. Better still, you dont even need to be a guru or excel pro to achieve dramatic results. All you need is some coffee and this post to

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-26762839954133175932011-06-23T22:38:00.001+05:302011-06-23T22:39:38.289+05:30

Shortcut Keys On most of Excels menu items you will see the shortcut key associated with it. To see a complete list push F1 and type "Shortcut Keys". Quick Help To get quick help on any menu item push Shift+F1 and click the menu item Insert Today's Date To insert Today's date push Ctrl+; (semicolon) Insert Current Time To insert the current time push Ctrl+Shift+: (Colon) Show the Paste

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-72106241773071393762009-12-17T18:58:00.001+05:302009-12-17T19:07:39.519+05:30

Select Case is similar to if....Then .....Else ,except that you can choose from several condition values. The general form is Select Case expression Case Value1 one or more VB statements for action1 Case Value2 one or more VB statements for action2 ..... ..... Case Valuem one or more VB statements for actionm [case else one or more statements for action otherwise] End Select You can have as

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-83309432752125155562009-12-17T18:18:00.001+05:302009-12-17T19:06:05.294+05:30

If statement is basically a single condition base statement. If statement is basically known as If....then statement Syntax: If condition Then statement1 [Else statement2] If the condition is satisfied (true then statement1 is carried out otherwise control passes to statement2. Usually you will need to use the block statement. If.....then ....else which does not restrict you to one line of

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-70833129744583391592009-11-16T18:55:00.000+05:302009-11-16T18:55:06.360+05:30

When you recorded a macro, Excel created a module, added it to your workbook and wrote your recorded actions in a procedure belonging to that module. When you want to write your own code in a new workbook you have to add a module to the workbook. The interface for macro development is called the Visual Basic Integrated Development Environment (IDE). Macro modules are displayed in the IDE instead

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-33627123529955865392009-11-15T18:03:00.008+05:302009-11-16T13:01:09.460+05:30

You already know how to get input from the user through the use of the Value property of a spreadsheet cell. Apart from that, you can also generate output for the user through the spreadsheet. However there may be times when you want something more dynamic and dramatic way to interact with the user than using a spreadsheet cell. The most common method for gathering input from the user and sending

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-4299586176888446602009-11-15T10:51:00.010+05:302009-11-15T18:02:11.233+05:30

Today we are going to discuss about few more important things............VariablesJust as in other programming languages. You can use variables. You do not have to declare variables. Visual Basic will automatically create storage for a variable the first time you use it.Automatically created variables are of type Variant and can contain any type of data---strings, numbers, boolen values, errors,

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-21050427030740723892009-11-14T18:02:00.006+05:302009-11-15T08:21:10.724+05:30

Today we are going to discuss about Visual Basic Grammar. I called it VBA Grammar.Objects:-Visual Basic is an object-oriented language. This means that all the items in Excel are thought of as objects. There are more than a hundred of them.Few Examples of objects are:The Excel application(the largest object)A WorkbookA worksheetA RangeA ChartA object is just like as noun. A object can contain

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-19556437616366039282009-11-14T11:29:00.003+05:302009-11-14T11:45:20.439+05:30

In Excel, you can automate tasks by using macros. A macro is a set of instructions that tells Excel what to do. These commands are written in a computer programming language called Visual Basic for Applications (VBA). Macros are basically of two types.1) Recording Macros2) Creating MacrosMeans who don't know anything about Visual Basic he also can record macros for there daily use.And also who

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-69683209376787358412009-11-13T10:49:00.004+05:302009-11-14T11:21:06.975+05:30

Today we are going to discuss about the drawbacks of Microsoft 1900 date system.Microsoft Excel uses two different date systems: the 1900 date system and the 1904 date system. By default Microsoft Excel use the 1900 date system. Microsoft use 1900 date system because the 1900 date system allows greater compatibility between Microsoft Excel and other spreadsheets program such as Lotus 1-2-3.

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-58348279724034138982009-11-12T17:28:00.005+05:302009-11-13T12:36:43.257+05:30

Do you know how excel understand date and time system..........It is very easy and simple process.By default excel use the 1900 date system. excel does not calculate any date before the 1900 date system. okay now what is the process. Excel count the date difference from 1st Jan 1900. That means 1st Jan 1900 is the first day according to excel and 2nd Jan 1900 is the 2nd day and so on.Example:-

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-42557391583803553362009-11-09T16:47:00.003+05:302009-11-09T17:17:14.476+05:30

Hi..............friends...................Today I am going to share few links with u.All those links included examples of formulas.........Every link is important according to my view.....The more you learn the more you get..........So the links are bellow..........http://office.microsoft.com/en-us/excel/HP052001271033.aspxhttp://www.cpearson.com/excel/excelF.htmhttp://

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-60710284902022392802008-11-26T13:26:00.004+05:302008-11-26T14:38:45.455+05:30

Hi All,Today we are know about how to reverse a cell value. Means if you input "RAM" in cell A1 then it returns you "MAR" in other cell.For doing this we make a custom function. We called the function rvrsstring.Now, Open a new excel file then open visual basic editor by press Alt+F11 or by the following path,Tools>macro>visual basic editor.Now insert a new module, by Insert>module. Now paste

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-62930758899031030002008-11-25T12:13:00.003+05:302008-11-25T14:49:44.486+05:30

Hi all,Today we are discussed about how to make a non printable excel file through vba macro.Open a new excel sheet and then open Visual basic editor by pressing alt+F11 or you also can open visual basic editor by the following path Tools>Macro>visual basic editor.Now open Thisworkbook and copy and paste the following code on it.Private Sub Workbook_Activate()Dim loectrl As

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-617076984422630712008-11-20T11:33:00.004+05:302008-11-25T12:05:28.194+05:30

Hi Friends,Today we are discussed about how to renaming the excel columns tab label.Actually you cant rename a columns tab in excel because excel is a spreadsheet program and you cant change the columns label.We find a lot of questions from people how to rename a columns tab label in excel like A to "my name" column B to "my address" extra.But due to excel is a spread sheet program you cant

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com2tag:blogger.com,1999:blog-2173039008952716221.post-75032601520645325932008-10-30T14:50:00.003+05:302008-11-20T11:33:04.872+05:30

HI friends,We are all ready know about the powerful vlookup formula in excel. I am do some test on this vlookup formula for make a master vlookup formula to lookup on the left most column from a master data sheet. I want to share the master formula with all of you.Here is some limitation of the formula which are following:1) In A1 cell of formula sheet put the value of A1 cell of data sheet2) if

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0tag:blogger.com,1999:blog-2173039008952716221.post-72254503955599906432008-10-28T19:52:00.004+05:302008-11-20T11:33:40.885+05:30

Hi All,First of all HAPPY DIWALI to you.Today we are learned about two more powerful function of macro in excel.1) Auto_Open2)Auto_Close1)Auto_Open: auto_open is a function in macro of excel. This function call your desired vb code before a excel workbook is open.2)Auto_Close: auto_close is a function in macro of excel. This function call your desired vb code before a excel workbook closed.Here

Sudipto (RED DEVIL)http://www.blogger.com/profile/11446457437980074768noreply@blogger.com0