Monitoring cells with a Watch Window

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 Auditing > Watch Window.  In order to add a cell to watch, click Add Watch and specify the cell that you want to watch. The Watch Window displays the value in that cell. You can add as many cells to the Watch Window, and you can also move the window anywhere in worksheet. Image below shows the Watch Window monitoring two cells.

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