Adding a new worksheet to your workbook

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 worksheet for each sales person. At the same have a consolidated worksheet with all information together.

The following are three ways to add a new worksheet to a workbook:
 

A. Press Shift+F11. This method inserts the new sheet before the active sheet.

B. Click the Insert Worksheet control, which is located to the right of the last sheet tab. This method inserts the new sheet after the last sheet in the workbook.

C. Right-click a sheet tab, choose Insert from the shortcut menu, and click the General tab of the Insert dialog box that appears. Then select the Worksheet icon and click OK. This method inserts the new sheet before the active sheet.
Step 1:

Step 2:

I know this is very easy for you guys who have gratuated in Microsoft Excel, but for amateurs this post will be helpful. HAPPY LEARNING :)

Count only text entries in any excel range

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 have used function COUNTIF. The formula used is E7: =COUNTIF(B5:B14,"*"). Based on the data above, it has given 5 as a result. Image below is the demonstration of the same.

Thank for your time and "HAPPY LEARNING" :)

Filtering Bold Data

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 Range

Set myRange = Application.InputBox(Prompt:="Please Select a Range", Title:="InputBox Method", Type:=8)
myRange.Select
Application.ScreenUpdating = False
For Each myRange In Selection
If myRange.Font.Bold = False Then
myRange.EntireRow.Hidden = True
End If
Next myRange
Application.ScreenUpdating = True
End Sub

With this post I am entering into the ocean of coding and will try to match pace with my other excel blogger friends.

"HAPPY LEARNING"

All about your LOAN in Excel

Are 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 loan.

Nper is the total of payments for the loan.

Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type is the number 0 (zero) or 1 and indicates when payments are due.

Instead of going into the details of this function, I would suggest you to download the file and make use of it. Click to Download the file from here.

"HAPPY LEARNING"

Create Dynamic Range for your Charts

Hi 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.  Don't worry I will tell you step by step process to create this chart and dynamic data input range for it.

Suppose I have a table like given in the image below and I want to create a chart for this table in such way that if I enter Budget and Actual sales in column C & D, these values will automatically get reflected in my chart.

Now first I will name my ranges to be plotted in charts. For that click Formulas > Name Manager > New

For Budget column name it as Budget_data or whatever you like and in refers to box at the bottom write the formula as =OFFSET(Sheet1!$C$5,0,0,COUNT(Sheet1!$C$5:$C$16)). Similarly for 2011 data name it as Actual_data and refers to =OFFSET(Sheet1!$D$5,0,0,COUNT(Sheet1!$D$5:$D$16))  have a look at the image below.

Before moving ahead I want to explain what the above formula is doing.  To put it in layman language, OFFSET is helping us to select the range starting from C5 and COUNT function is helping the formula to decide the height of range by counting the number of data entered in the table.

Once we're done naming ranges now we'll create charts and do edits required to make it dynamic.

Select your data to be plotted, here select B4:D15 and click Insert > Charts > Column > Clustered Column.  It will give you a chart appearing as in 1 of image below.

Now right click in your chart and select change series chart type as in 2 of the image.  Select Line chart type.

You can also change the layout of your chart and tittle as done in 3 of the image.

Now in 4 comes the most important part of this trick.  Here we will adjust the data source for our chart and define the two series shown in the chart.

As shown in 4 of the above image, when we click Select Data it will open Select Data Source Box where under Legend Entries (Series) we'll edit Series1 & Series2.  In the following image below we have made edits to Series1 by referring Series name to cell C3 & Series values to "Budget_Data" which we named above.  Similarly for Series2 we referred Series name to cell D3 & Series values to "Actual_Data".

What this will do is make our chart responsive to the data we enter in table range B4:D15.  When we enter figures for any month it will automatically plot the same in the corresponding chart.  And that exactly was our goal as well.

Hope you learned something out of this trick.  "HAPPY LEARNING"

Conditional Lookup using Multi Criteria

Hello 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 being big fan of functions I would like to provide you formula to find it as well.  You'll use a combination of INDEX & MATCH function to get your figure here. The formula in F8 will be as follows:

F8: {=INDEX(C2:C10,MATCH(F2&F5,A2:A10&B2:B10,0))}

This is an array formula and to make it works you'll have to press (Ctrl+Shift+Enter) CSE.

Download sample file: MULTI-CRITERIA LOOKUP

Are you above or below AVERAGE in Excel?

AVERAGE 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 AVERAGE excel skills".

Today I am trying to throw some light on AVERAGE function in excel but will also talk about AVERAGEA, AVERAGEIF & AVERAGEIFS.  So be with this post and by the time you finish reading, you'll be equipped with the power of four functions of excel in a matter of few minutes.

AVERAGE function as described in Excel Help is "Returns the average (arithmetic mean) of the arguments." For example, if the range A1:M1 contains numbers, the formula =AVERAGE(A1:M1) returns the average of those numbers.  See the image below explaining this situation:

Things one should know about AVERAGE function are provided arguments can either be numbers or names, ranges, or cell cell references that contains numbers.  Also if a range or cell reference contains text, logical values, or empty cells, those values are ignored; however, cells with negative or zero value are included for calculation of average.

Now what should one do if his data also has negative or zero values and he is looking to get average of only positive values.  Got stuck? Don't worry there is always a solution for every problem, and this riddle can also be solved.  You just need to know the way to approach it.  The formula created by using combination of SUMIF & COUNTIF as =SUMIF(A1:M1,">0")/COUNTIF(A1:M1,">0") can provide us a solution. If you've any other way to solve our problem, please share with our readers in comments at the end of the post.

Now I believe you guys know the AVERAGE function and are ready to go ahead and study other related fuctions I mentioned in the beginning.  First to go is AVERAGEA, this function also provides arithmetic mean but it doesn't ignore logical values and text in arguments.  Empty cell in the range is still ignored by this function as well.  For example in the below you see AVERAGEA(A1:M1) & AVERAGEA(A1:M1,N1) is giving us same result because blank cell N1 is ignored for calculation by function AVERAGEA.

It may also be possible that during your course of routine you would like to know or find average based on single or multiple criteria.  For example you want to calculate the average for numbers above 30 or average for values belonging to a particular category and are above 30 as well.  For situations like these excel has two powerful functions known as AVERAGEIF & AVERAGEIFS for single and multi criteria respectively.

In the image above AVERAGEIF function has been used to calculate average in the range A1:M2 provide that the values are greater than 30. At the same time AVERAGEIFS is being used to satisfy two conditions and calculate average.  Two conditions here are the number should belong to A2 category and should be greater than 30 as well.

So this way today you've added or refreshed functions related to averages in your knowledge base. Let me know about your past or present experiences of using these amazing functions in comments.  "HAPPY LEARNING"