This blog is about tips and tricks that comes handy while dealing with your regular work related stuff. Stay connected, Stay ahead. HAPPY LEARNING is the Motto.noreply@blogger.com (Prakash Singh Gusain)Sun, 22 Jun 2025 01:30:04 +0530Blogger http://www.blogger.com27125http://prakashgusain.blogspot.com/en-ushttp://prakashgusain.blogspot.com/2014/06/multiply-using-pastespecial.htmlnoreply@blogger.com (Prakash Singh Gusain)Mon, 9 Jun 2014 18:25:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-1607896721009210897 Hello All, This post will help you learn a smarter way to multiply a range of data with a single number. For example below in Column A we have 5 products and column B contains their corresponding price. Now if you want to multiply the price by say 10%. You can do that smartly,  using the method I am going tell.  Enter 1.1 into any blank cell (like D1) Select the cell and 2http://prakashgusain.blogspot.com/2013/07/insert-pivot-table-in-5-easy-steps.htmlExcel 2010PivotTablenoreply@blogger.com (Prakash Singh Gusain)Sun, 7 Jul 2013 09:01:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-8472284198231443060 Do you want to learn pivot tables? Below are five steps to start the journey!! The post has images explaining each steps and assist you in the process. Step 1. Open the sheet that has data you want to summarize and select any cell. Step 2. On the Insert tab, click the PivotTable button in Tables group. Step 3. If required adjust the range of your data and provide location for0http://prakashgusain.blogspot.com/2013/04/monitoring-cells-with-watch-window.htmlnoreply@blogger.com (Prakash Singh Gusain)Sat, 6 Apr 2013 17:28:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-2200588032658183546 Some time you may want to monitor a value obtained from a formula in a cell.  As you work, you move around the worksheet and the cell value to be monitored disappear from view. To solve this issue/problem, a feature known as watch window comes to rescue. It makes the value always visible in a pop up window on top of worksheet. To activate this watch window, select Formulas > Formula 0http://prakashgusain.blogspot.com/2013/03/adding-new-worksheet-to-your-workbook.htmlExcel 2010Worksheetnoreply@blogger.com (Prakash Singh Gusain)Thu, 14 Mar 2013 19:23:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-3106616723299459389 Today you will be learning how to add worksheet in your workbook. You can organize a lot of information via worksheets. Instead of placing everything on a single worksheet; you can use additional worksheets in a workbook to separate various workbook elements logically. Example: Let say you have 10 sales person, and you want to track their activities daily.  You can create individual 0http://prakashgusain.blogspot.com/2012/06/count-only-text-enteries-in-any-excel.htmlCOUNTIFFUNCTIONIF FUNCTIONTEXTnoreply@blogger.com (Prakash Singh Gusain)Sun, 10 Jun 2012 16:39:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-7223122136423211806 It's been a long time since, I've written anything for this blog. Being free on Sunday, here we go with this little post. Problem in Hand: We want to count only the text entries in the list under consideration. Meaning we want to ignore all possible entries under sun in an excel cell except "Text". Image below will help you understand problem visually. To solve this problem I 2http://prakashgusain.blogspot.com/2011/10/filtering-bold-data.htmlFilter Bold CellsVBAnoreply@blogger.com (Prakash Singh Gusain)Thu, 13 Oct 2011 02:10:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-5263192706378459222 Dear readers today I came to know about a situation where the user was looking to filter data in a range, but he wanted to filter only those cells in range which has BOLD formatting. I thought of doing it using VBA as there was no option that came to my mind that time.  The below code worked for me. Sub FilterBold() Dim myRange As RangeSet myRange = Application.InputBox(Prompt:="2http://prakashgusain.blogspot.com/2011/08/all-about-your-loan-in-excel.htmlFUNCTIONPMTnoreply@blogger.com (Prakash Singh Gusain)Wed, 31 Aug 2011 00:51:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-3959915219578988131Are you planning to borrow money?  If yes, give my EMI Calculator a look and you'll know a lot about it. It helps you to know how much EMI you'll be paying, during repayment period how much amount you'll have to pay extra and other things. I have used PMT function to get the value of EMI.  The syntax goes like this: PMT(rate,nper,pv,fv,type) Rate is the interest rate for the 0http://prakashgusain.blogspot.com/2011/08/create-dynamic-range-for-your-charts.htmlChartDYNAMIC RANGEINDEXMATCHnoreply@blogger.com (Prakash Singh Gusain)Tue, 23 Aug 2011 02:55:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-7849861342468885971Hi guys recently I got an email where the sender has requested to create chart in such a manner that graph adjusts itself to the changing figures in the table or data. I provide him a solution which look something like the below image.Download the file from here: DYNAMIC RANGE Now the real question here comes is, how this was done? For that I have used Name Manager from Formulas tab.&3http://prakashgusain.blogspot.com/2011/08/conditional-lookup-using-multi-criteria.htmlINDEXLOOKUPMATCHnoreply@blogger.com (Prakash Singh Gusain)Mon, 22 Aug 2011 22:01:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-5274554038950240850Hello readers today's post will be short but I assure you it will help you tackle problem which you may be facing or might face in future. Suppose you want to look up any value based on two criteria, for example in the below image you want to know the price in F8 based on Make & Color in F2 & F5 respectively. I know the solution to this can be found using filter feature, but 0http://prakashgusain.blogspot.com/2011/08/are-you-above-or-below-average-in-excel.htmlAVERAGEFUNCTIONnoreply@blogger.com (Prakash Singh Gusain)Mon, 8 Aug 2011 19:36:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-8121664055238238640AVERAGE this word is no new to any of us and it's meaning is known to all of us.  But what does it exactly means? The literal meaning is "a quantity, rating, or the like that represents or approximates an arithmetic mean".  For statistician it has always meant Arithmetic Mean and when used as an adjective it means typical; common; or ordinary. For example "Prakash has 7http://prakashgusain.blogspot.com/2011/08/conditional-sum-using-multiple-criteria.htmlnoreply@blogger.com (Unknown)Fri, 5 Aug 2011 22:57:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-1494658880734847653SUMPRODUCT Function is used to sum a range when multiple conditions are met.. The first argument of sumproduct is the range which is to be summed. Suppose you want to get a sum of a product range which falls between two Dates, In other words, the value in the amount range is summed only if both of the following criteria are met: First condition: The Product should be Samsung.. Second condition: 0http://prakashgusain.blogspot.com/2011/08/insert-blank-row-after-each-record.htmlBlank Rownoreply@blogger.com (Prakash Singh Gusain)Tue, 2 Aug 2011 15:16:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-9028748526248260660Hello friends I am back again with a new excel trick for you to learn.  Today I am trying to explain a very simple way to add a blank row after each record in your data.  This trick is very special because without VBA it is the only method to insert a blank row to your data except doing it manually. Let me first tell you a little bit about the objective that I am planning to 37http://prakashgusain.blogspot.com/2011/07/dynamic-charts-with-index-match.htmlChartINDEXMATCHnoreply@blogger.com (Prakash Singh Gusain)Mon, 25 Jul 2011 22:56:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-265914634809868558Have you ever created a dynamic chart in excel? If yes, Kudos to you!  If not don't worry today you can master that skill in couple of minutes.  I would like to tell you that this is my first post on charts and I am starting a series on charting with this.  I will keep adding more to it time and again for sure.  In this post I'll be talking about how to 4http://prakashgusain.blogspot.com/2011/07/sort-data-from-left-to-right-sort-data.htmlExcel 2007SORT IN ROWSnoreply@blogger.com (Prakash Singh Gusain)Thu, 14 Jul 2011 03:36:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-7198197655255254528Dear readers & followers it's been a long time since my last post.  Now I have finally got some time to write something interesting and worth sharing. Today you're gonna learn how to sort data in rows. What I mean by that is sorting your data from left to right instead of sorting from top to bottom in column. If you look at the image below it has few dates mentioned in column B to F6http://prakashgusain.blogspot.com/2011/04/finding-top-5-performers-in-excel.htmlExcelFUNCTIONTop 5noreply@blogger.com (Prakash Singh Gusain)Thu, 21 Apr 2011 04:00:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-7431207698778695146I recently came across with a problem asked by one of my friend.  He wanted me to create a function in Excel to know the Top 5 sales performer in his team.  I suggested him a easy way of sorting the data in descending order based on sales figures but he wanted to keep the data intact and know the result. See the image below to visualize what he was looking for:To provide him with an 3http://prakashgusain.blogspot.com/2011/04/ms-excel-subtotal-function-and-filter.htmlExcel 2007FilterFUNCTIONMicrosoft ExcelMicrosoft Excel FUNCTIONSUBTOTALnoreply@blogger.com (Prakash Singh Gusain)Fri, 15 Apr 2011 04:45:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-6491862505922472926The Subtotal function provide the subtotal of the numbers in a column.  It allows us to perform a calculation in the worksheet, and the same calculation can also be performed on the subsets of the data by applying filters. The magic of this function is that it ignores values in rows hidden by a filter. The syntax for the Subtotal function is:         1http://prakashgusain.blogspot.com/2011/02/vlookup-to-fetch-2nd-3rd-or-4th-value.htmlExcel 2007FUNCTIONLOOKUPMicrosoft ExcelMicrosoft Excel FUNCTIONnoreply@blogger.com (Prakash Singh Gusain)Mon, 28 Feb 2011 14:40:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-5654914677499286891We all use VLOOKUP and must have observed a shortcoming that it only provides you with the first matching result. I mean if we have data where the lookup_value is coming twice or more times, VLOOKUP will give us the result for the first occurrence only. In the data below if your boss is interested in knowing the commission paid to "Mavericks Reality" for their second 3http://prakashgusain.blogspot.com/2011/02/filter-asterisk-character.htmlExcelFilterTipsWildcardnoreply@blogger.com (Prakash Singh Gusain)Tue, 22 Feb 2011 00:30:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-7776036304125306922 Hello friends I hope you are keeping track of our Excel tips and learning new things everyday. Now it's time to get ready for another Excel tips. Today's post will be short and but very unique. Today we are going to learn, how to filter the asterisk character ("*"). Suppose you have a data which contains few asterisk character ("*") and you want to filter rows containing asterisk 15http://prakashgusain.blogspot.com/2011/02/and-function-test-mathematical.htmlAND FuctionExcel 2007Microsoft ExcelMicrosoft Excel FUNCTIONnoreply@blogger.com (Prakash Singh Gusain)Sun, 20 Feb 2011 20:54:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-7156319895711081812Moving ahead with our series of logical function, lets evaluate the AND function AND function provides result in TRUE or FALSE as output based on one of more condition. AND function evaluates at least one mathematical expression located in another cell in the spreadsheet. The syntax it uses is as follow: AND(logical1,logical2, ...) Logical1, logical2, ...   are 1 to 255 conditions 0http://prakashgusain.blogspot.com/2011/02/explaining-array-formulas-in-excel.htmlArray FormulaExcel 2007Microsoft Excelnoreply@blogger.com (Prakash Singh Gusain)Thu, 17 Feb 2011 04:08:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-1274988437268189825There is special class of formulas available in Excel know as Array formulas or CSE b’coz they are entered by hitting three keys at a time Ctrl+Shift+Enter. There is no doubt this is one of the most powerful feature provided by Microsoft Excel, but this is one of least used feature as well. Array formulas empower you to calculate that you can’t perform with standard Excel function.   The "0http://prakashgusain.blogspot.com/2011/02/two-ways-lookup-formula-to-extract.htmlExcel 2007INDEXLOOKUPMATCHMicrosoft Excelnoreply@blogger.com (Prakash Singh Gusain)Mon, 14 Feb 2011 23:43:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-1318333079620605193I am sure if you’re using Excel at work, then you must have used lookup function (LOOKUP, VLOOKUP & HLOOKUP). There is no doubt that these are most useful & beautiful functions provided in the excel package. But if you haven’t used these function yet, let me take the honor of telling you that a lookup function return a value from a given table by looking up another value in the 3http://prakashgusain.blogspot.com/2011/02/create-table-in-excel-2007.htmlExcel 2007Excel 2007 TableInsert TableMicrosoft ExcelRibbonTablenoreply@blogger.com (Prakash Singh Gusain)Thu, 10 Feb 2011 02:22:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-5933355345318486882With the launch of Office 2007, Microsoft has introduced a rich concept to Excel know as "Table". Table in Excel 2007 help you to manage and analyze your data. This feature has less functionality in terms of calculation but it is very useful in storing lots of information consistently. Formatting, sorting and filtering are very easy with Table. Typically, an Excel table has only column headings 0http://prakashgusain.blogspot.com/2011/02/excel-function-if-logicaltest.htmlExcel 2007FUNCTIONIF FUNCTIONMicrosoft ExcelMicrosoft Excel FUNCTIONnoreply@blogger.com (Prakash Singh Gusain)Mon, 7 Feb 2011 20:11:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-1815932210728625495Now in coming two weeks I have decided to discuss functions available in the stable of Microsoft Excel.  I have taken Logical function to start with, as I found them most useful will working data (this is my personal view :) though). To start with I am gonna explain IF function with an example. The IF function is one of Excel's logical functions and it tests to see if a certain condition in2http://prakashgusain.blogspot.com/2011/02/create-charts-directly-in-range.htmlChartExcel 2007In-Cell ChartMicrosoft ExcelTipsnoreply@blogger.com (Prakash Singh Gusain)Tue, 1 Feb 2011 02:37:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-9063239975163377053This post will be talking about how to create non-graphic chart without using the default chart templates in Excel.  This method use combination of functions to display bars in the call corresponding to the data.In the screen-shot above, I have data containing targeted (column B) and actual (column C) number of calls made by agents  in an assessment period. Column D represents, the 2http://prakashgusain.blogspot.com/2011/01/excel-function-mround-rounding-value-to.htmlExcel 2007FUNCTIONMicrosoft ExcelMROUNDnoreply@blogger.com (Prakash Singh Gusain)Mon, 24 Jan 2011 20:54:00 +0530tag:blogger.com,1999:blog-2392561432908973850.post-1596571609488167487Readers if you want to round a value to some multiple of a whole number, you need to know MROUND function.  It works wonders when it comes to round a value with respect to a given multiple. The MROUND function can be used to round a number up or down to a specified multiple. The syntax for the MROUND function is as follows: =MROUND (Number, Multiple)  Number: the number which you want 0