Multiply using PasteSpecial

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 choose Edit, Copy.

Select the range of values and choose Edit, Paste Special.

Choose the Multiply option and click OK.

Delete the cell that contains the 1.1. 

Your final result will look like this. 

HAPPY LEARNING :) 

Insert Pivot Table in 5 easy steps

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 for pivot table. I am using default "New Worksheet" in this illustration.

Step 4. You will see your pivot table like below image. As it says "To build a report, choose fields from the PivotTable Field List" that will be your final step.

Step 5. Assign fields from PivotTable Field List to various part of table. You can do this by dragging any field name from Choose fields to add to report box and dropping it in any one of the four areas, also know as drop zones.

Keep dragging and dropping till the time you get the desired report/result. "HAPPY LEARNING"

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"