tag:blogger.com,1999:blog-2173039008952716221Fri, 25 Oct 2024 02:44:50 +0000Learn office ExcelVBAmacro codemacroFree Excel Learninglearn excelGot Power of Excel in your handWorking in a workbookBasic excel learningDate serialExcel DateIf StatementTips and TricksVba Grammarexamplesexamples of formulasif function1900 date systemAND functionApplication WindowArraysAuto Correct option in ExcelClosing a WorkbookConstantConstantsCount commentsCount comments in excelData typesData validationDate ValueDate and Time in ExcelEntering and Editing Data in excelGO TOGO TO in excelHow links cells of two different Excel files.IF AND functionIF function examplesIf Or functionIf then else statementInputboxIntroduction with macroMake a Task scheduler diaryMenu barMoving and Copying Data and formulas in ExcelNon printable fileOpen a WorkbookSelect Case Statement. Select CaseShortcutStop printTitle BarUsing SetVariablesVisual Basic for applicationVlookup formula tricks.World of Excelauto_closeauto_openbasic of barchartingconditional formattingdesign exceldiffrence between vb and vbadisable print commandexcel formulaformulas in Excelinputlearn macrolearn office excel.more knowledgemsgboxoutputrename excel columns tab labelrename the excel columns namereverse cellreverse cell in excelreverse cell valuereverse string in excelshortcut key; excel shortcut keys.special cellsspeciallcells in excelwriting your own macroHere you can find 1000 of examples, with lot of tricks and many more about excel and vba macro.http://learnofficeexcel.blogspot.com/noreply@blogger.com (Sudipto (RED DEVIL))Blogger47125en-usnoThe knowledge of Microsoft Excel in your hand.noreply@blogger.comtag:blogger.com,1999:blog-2173039008952716221.post-8990343404839128631Thu, 04 Aug 2011 06:00:00 +00002011-08-04T15:22:43.613+05:301. 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 http://learnofficeexcel.blogspot.com/2011/08/how-to-use-formula-in-pivot-table.htmlnoreply@blogger.com (kamit.sony)0nonoreply@blogger.com (kamit.sony)tag:blogger.com,1999:blog-2173039008952716221.post-4272703800330865568Sun, 17 Jul 2011 17:39:00 +00002011-07-17T23:30:48.295+05:30Use 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 referencehttp://learnofficeexcel.blogspot.com/2011/07/use-excel-countif-function.htmlnoreply@blogger.com (kamit.sony)1nonoreply@blogger.com (kamit.sony)tag:blogger.com,1999:blog-2173039008952716221.post-3780760727242914886Sat, 16 Jul 2011 18:04:00 +00002011-07-17T13:11:17.175+05:30If Or functionlearn excelHi 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 http://learnofficeexcel.blogspot.com/2011/07/using-multiple-criteria-with-or.htmlnoreply@blogger.com (kamit.sony)0nonoreply@blogger.com (kamit.sony)If Or function, learn exceltag:blogger.com,1999:blog-2173039008952716221.post-8873394493459828354Sat, 16 Jul 2011 13:28:00 +00002011-07-17T13:13:37.417+05:30AND functionIF AND functionif functionlearn excel 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 usinghttp://learnofficeexcel.blogspot.com/2011/07/using-multiple-criteria-with-and.htmlnoreply@blogger.com (kamit.sony)0tag:blogger.com,1999:blog-2173039008952716221.post-1687051021124152468Sat, 16 Jul 2011 12:31:00 +00002011-07-17T13:17:10.935+05:30examplesexamples of formulasIF function exampleslearn excelwhenever 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 http://learnofficeexcel.blogspot.com/2011/07/if-statement-examples.htmlnoreply@blogger.com (kamit.sony)0tag:blogger.com,1999:blog-2173039008952716221.post-7105567442810662397Tue, 28 Jun 2011 13:11:00 +00002011-06-28T23:37:00.807+05:30excel formulaif functionIf StatementLearn office ExcelThe 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 http://learnofficeexcel.blogspot.com/2011/06/if-function-one-of-excels-logical.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-917607341296264230Mon, 27 Jun 2011 17:42:00 +00002011-06-27T23:12:23.842+05:30GO TOGO TO in excelLearn office Excelspecial cellsspeciallcells in excelVBAGo 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 http://learnofficeexcel.blogspot.com/2011/06/go-to-function-and-special-cells-method.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-2262956197211946917Sat, 25 Jun 2011 15:07:00 +00002011-06-25T20:37:31.716+05:30diffrence between vb and vbalearn excelVBAVisual Basic for applicationVBA 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 http://learnofficeexcel.blogspot.com/2011/06/diffrence-between-vb-and-vba-visual.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-2744674648478988348Fri, 24 Jun 2011 10:20:00 +00002011-06-24T15:54:49.430+05:30chartingconditional formattingdesign excellearn excellearn macroLearn office ExcelExcel 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 tohttp://learnofficeexcel.blogspot.com/2011/06/be-rockstar-on-conditional-formatting.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0nonoreply@blogger.com (Sudipto (RED DEVIL))charting, conditional formatting, design excel, learn excel, learn macro, Learn office Exceltag:blogger.com,1999:blog-2173039008952716221.post-2676283995413317593Thu, 23 Jun 2011 17:08:00 +00002011-06-23T22:39:38.289+05:30learn office excel.shortcut key; excel shortcut keys.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 http://learnofficeexcel.blogspot.com/2011/06/shortcut-keys-for-excel.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-7210624177307139376Thu, 17 Dec 2009 13:28:00 +00002009-12-17T19:07:39.519+05:30macromacro codeSelect Case Statement. Select CaseVBASelect 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 http://learnofficeexcel.blogspot.com/2009/12/select-case-vba.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-8330943275212515556Thu, 17 Dec 2009 12:48:00 +00002009-12-17T19:06:05.294+05:30If StatementIf then else statementmacromacro codeVBAIf 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 http://learnofficeexcel.blogspot.com/2009/12/if-statement-in-vba.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-7083312974458339159Mon, 16 Nov 2009 13:25:00 +00002009-11-16T18:55:06.360+05:30macromacro codeVBAwriting your own macroWhen 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 insteadhttp://learnofficeexcel.blogspot.com/2009/11/writing-your-own-macros.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-3362712352995586539Sun, 15 Nov 2009 12:33:00 +00002009-11-16T13:01:09.460+05:30inputInputboxmacromacro codemsgboxoutputVBAYou 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 sendinghttp://learnofficeexcel.blogspot.com/2009/11/simple-input-and-output-with-vba.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-429958617688844660Sun, 15 Nov 2009 05:21:00 +00002009-11-15T18:02:11.233+05:30ArraysConstantConstantsData typesmacromacro codeUsing SetVariablesVBAVba GrammarToday 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, http://learnofficeexcel.blogspot.com/2009/11/vba-grammarexcel-macro-part-ii.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-2105042703074072389Sat, 14 Nov 2009 12:32:00 +00002009-11-15T08:21:10.724+05:30macromacro codeVBAVba GrammarToday 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 http://learnofficeexcel.blogspot.com/2009/11/vba-grammarexcel-macro-part-i.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-1955643761636603928Sat, 14 Nov 2009 05:59:00 +00002009-11-14T11:45:20.439+05:30Introduction with macromacromacro codeVBAIn 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 http://learnofficeexcel.blogspot.com/2009/11/indroduction-with-macro-in-excel-vba.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-6968320937678735841Fri, 13 Nov 2009 05:19:00 +00002009-11-14T11:21:06.975+05:301900 date systemDate serialExcel DateToday 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.http://learnofficeexcel.blogspot.com/2009/11/drawback-of-microsoft-1900-date-system.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-5834827972403413898Thu, 12 Nov 2009 11:58:00 +00002009-11-13T12:36:43.257+05:30Date serialDate ValueExcel DateDo 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:- http://learnofficeexcel.blogspot.com/2009/11/how-excel-calculate-date-and-time.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-4255739158380355336Mon, 09 Nov 2009 11:17:00 +00002009-11-09T17:17:14.476+05:30examplesexamples of formulasformulas in ExcelHi..............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://http://learnofficeexcel.blogspot.com/2009/11/examples-of-formulas.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-6071028490202239280Wed, 26 Nov 2008 07:56:00 +00002008-11-26T14:38:45.455+05:30reverse cellreverse cell in excelreverse cell valuereverse string in excel 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 http://learnofficeexcel.blogspot.com/2008/11/reverse-cell-text-or-cell-value.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-6293075889903103000Tue, 25 Nov 2008 06:43:00 +00002008-11-25T14:49:44.486+05:30disable print commandNon printable fileStop printHi 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 http://learnofficeexcel.blogspot.com/2008/11/make-non-printable-excel-file-through.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-61707698442263071Thu, 20 Nov 2008 06:03:00 +00002008-11-25T12:05:28.194+05:30rename excel columns tab labelrename the excel columns nameHi 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 http://learnofficeexcel.blogspot.com/2008/11/renaming-excel-columns-tab.htmlnoreply@blogger.com (Sudipto (RED DEVIL))2tag:blogger.com,1999:blog-2173039008952716221.post-7503260152064532593Thu, 30 Oct 2008 09:20:00 +00002008-11-20T11:33:04.872+05:30Vlookup formula tricks.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 http://learnofficeexcel.blogspot.com/2008/10/tricky-vlookup-formulae.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0tag:blogger.com,1999:blog-2173039008952716221.post-7225450395559990643Tue, 28 Oct 2008 14:22:00 +00002008-11-20T11:33:40.885+05:30auto_closeauto_openmacro codeHi 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 http://learnofficeexcel.blogspot.com/2008/10/using-autoopen-and-autoclose-function.htmlnoreply@blogger.com (Sudipto (RED DEVIL))0