Blog Archive

Excel: Creating A Thermometer Style Chart

Category: Charts & Graphics | [Item URL]

Most people are familiar with "thermometer"-style graphs, which show the percentage of a project completed. It's easy to make such a display in Excel. The key is to create a chart that uses a single cell (containing a percentage value) as a data series.
The example below tracks daily progress toward a goal: 1000 new customers in a 15-day period.
Cell B18 contains the goal value. Cell B19 contains a simple sum formula:
Cell B21 contains the following formula, which calculates the percentage of the goal attained
As new data is entered in column B, the formulas display the current results.
To create the chart:
  1. Enter the formulas listed above, along with the worksheet's sample data.
  2. Select cell B21, and click the Chart Wizard button. Notice the blank row preceding cell B21. If you fail to include this blank row, Excel will use the entire data block--not just the single cell--to construct the chart. Since B21 is isolated from the other data, the Chart Wizard uses only the single cell.
  3. In step 1 of the Chart Wizard dialog, specify a Column chart and a Clustered Column subtype (the first choice).
  4. Click Next twice, and then in step 2 make additional adjustments: Add a Chart Title (Title tab), dump the Category (x) axis (Axes tab), delete the legend (Legend tab), and specify Show value (Data Labels tab). Click Finish to view the chart.
  5. Double-click the column to display the Format Data Series dialog box.
  6. Click the Options tab, and set the Gap width to 0 (this setting instructs the column to occupy the entire width of the plot area).
  7. To change the pattern used in the column, click the Patterns tab and make your selection. The example shown here uses a gradient fill effect.
  8. Double-click the vertical axis to bring up the Format Axis dialog. In the Scale tab of the Format Axis dialog, set Minimum to 0 and Maximum to 1.

Excel: Creating A Database Table From A Summary Table

Category: General | [Item URL]
Many users are familiar with Excel's pivot table feature, which creates a summary table from a database table. But what if you want to perform the opposite operation? This document describes how to create a database table from a simple two-variable summary table.
The worksheet below demonstrates. Range A1:E13 contains the original summary table, and columns G:I shows a 48-row database table derived from the summary table.

How to do it

The solution to creating this "reverse pivot table" is to use a pivot table! The steps below are specific to the example data shown, so you'll need to modify them slightly to work with your data.

Part 1: Creating a pivot table

  1. Activate any cell in your summary table
  2. Choose Data - PivotTable and PivotChart Report (the menu command may vary, depending on the version of Excel).
  3. In the PivotTable dialog box, select the Multiple consolidation ranges option, and click Next.
  4. In Step 2, choose the I will create the page fields option and click Next.
  5. In Step 2b specify your summary table range in the Range field (A1:E13 for the sample data) and click Add. Click Next.
  6. In Step 3, select a location for the pivot table, and click the Layout button.
  7. In the Layout dialog box, you will change the default layout in the diagram. Drag both the Column button and Row button away from the diagram. This will leave the diagram with only a data field: Sum of Value. The dialog box should look like the figure below.
  8. Click OK and then Finish to create the pivot table.

Part 2: Finishing up

At this point, you will have a small pivot table that shows only the sum of all values:
  1. Double-click the cell that contains the total (outlined in yellow, above). Excel will create a new sheet that displays the original data in the form of a database table (see the figure below).
  2. The column headings will display generic descriptions (Row, Column, and Value), so you'll probably want to change these headings to make them more descriptive.

A VBA Macro to do it

If you do this sort of thing on a regular basis, you may prefer to use a VBA macro. Just copy the VBA code to a VBA module. Then activate a cell in your summary table and execute the ReversePivotTable macro. This macro uses simple looping -- no fancy pivot table tricks.

Excel: Perform Two-Way Table Lookups

Category: Formulas | [Item URL]
All spreadsheets support lookup functions, tools that return a value from a table by looking up another value in the table. An income tax table is a good example. You can write a formula that uses the VLOOKUP function to determine the tax rate for a given income amount.
The lookup functions in Excel are only appropriate for one-way lookups, however. If you need to perform a two-way lookup, you'll need more than the standard functions. The figure below shows a simple example.

The formula in cell H4 looks up the entries in cells H2 and H3 and then returns the corresponding value from the table. The formula in H4 is:
=INDEX(A1:E14, MATCH(H2,A1:A14,0), MATCH(H3,A1:E1,0)).
The formula uses the INDEX function, with three arguments. The first is the entire table range (A1:A14). The second uses the MATCH function to return the offset of the desired month in column A. The third argument uses the MATCH function to return the offset of the desired product in row 1.
You may prefer to take advantage of Excel's natural-language formulas. For example, enter the following formula to return Sprocket sales for June:
=June Sprockets 
If natural-language formulas aren't working, select Tools, Options, click the Calculation tab, and place a check mark next to "Accept labels in formulas." Be aware that using natural language formulas is not 100% reliable!

Excel: Calculate The Number Of Days In A Month

Category: Formulas | [Item URL]

Excel lacks a function for calculating the number of days in a particular month, so you'll need to construct your own formula.
If cell A1 contains a date, this formula will return the number of days in the month:

Excel: Navigating Excel’s Sheets

Category: General | [Item URL]
Every Excel user knows that you can activate a different sheet in a workbook by clicking its sheet tab. Most users also know that you can press Ctrl-Page Up to activate the previous sheet, and Ctrl-Page Down to activate the next sheet.
But if your workbook contains many sheets, and not all of the sheets' tabs fit on the screen, you may find it tedious to scroll through the tabs or use the keyboard to activate a distant sheet.
Here's a little-known trick that will allow you to bypass repeated scrolling or typing: Display a pop-up list of sheet names by right-clicking one of the arrows to the left of the sheet tabs that are located at the bottom of the worksheet window (see the figure below). Select a sheet from the list, and you're there in a flash.

Excel: Create A Drop-Down List Of Possible Input Values

Create A Drop-Down List Of Possible Input Values

Category: General | [Item URL]
If you're creating a worksheet that will require user input and you want to minimize data entry errors, use Excel's data validation feature to add a drop-down list. The best part about it is that you don't have to write any macros.
Data validation is an excellent way to ensure that a cell entry is of the proper data type (text, number, or date) and within the proper numeric range. The drop-down list produced with the feature appears when a user clicks the cell.
Here's how to create a drop-down list:
  1. Type the list of valid entries in a single column. If you like, you can hide this column (select Format, Column, Hide).
  2. Select the cell or cells that will display the list of entries.
  3. Choose Data, Validation, and select the Settings tab.
  4. From the Allow drop-down list, select List.
  5. In the Source box, enter a range address or a reference to the items that you entered in step 1.
  6. Make sure the 'In-cell dropdown' box is selected.
  7. Click OK.
If your list is short, you can skip step 1 and type the list entries directly in the Source box in step 5, separating items with a comma.
The Data Validation dialog box has two other tabs. Click Input Message to add a prompt that will appear when a user selects a cell. Click Error Alert to specify a custom error message if the user's entry is invalid.
The handy data validation feature suffers from one serious flaw. If you paste an entry into a cell that uses data validation, the validation isn't performed. And if you select that cell again, the drop-down list no longer appears. Fortunately, you can circumvent this problem by protecting the worksheet: Select Tools, Protection, Protect Sheet.


Sending Personalized Email From Excel

Sending Personalized Email From Excel

Category: General VBA | [Item URL]
If you use Microsoft Outlook, it's fairly easy to create a macro to send personalized email from Excel. Just create an Outlook object and manipulate Outlook's object model using data from your worksheet.
But what if you don't use Outlook? This tip presents a technique for sending personalized email from Excel. I developed and tested the code using Outlook Express (which does not support VBA). It may or may not work with other email clients.

An Example

Consider the simple worksheet shown in the figure below. The goal is to send a personalized email to each of the three people listed in the workbook, informing them of their annual bonus amount.
For example, the first email message would read:
Dear John Jones,

I am pleased to inform you that your annual bonus is $2,000.

William Rose

The VBA Code

The VBA code to create and send these email messages is listed below.
Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
  ByVal nShowCmd As Long) As Long
Sub SendEMail()
    Dim Email As String, Subj As String
    Dim Msg As String, URL As String
    Dim r As Integer, x As Double
    For r = 2 To 4 'data in rows 2-4
'       Get the email address
          Email = Cells(r, 2)
'       Message subject        Subj = "Your Annual Bonus"

'       Compose the message        Msg = ""
          Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf
        Msg = Msg & "I am pleased to inform you that your annual bonus is "

        Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf
          Msg = Msg & "William Rose" & vbCrLf
        Msg = Msg & "President"
'       Replace spaces with %20 (hex)        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
          Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
'       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")        
'       Create the URL        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg        

'       Execute the URL (start the email client)
          ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

'       Wait two seconds before sending keystrokes        Application.Wait (Now + TimeValue("0:00:02"))
          Application.SendKeys "%s"
    Next r
End Sub

How it Works

The first part of the macro is straightforward. It loops through the rows, picks up the email address and composes the custom message. Notice the use of Excel's Substitute worksheet function. This is used to replace space characters and carriage return/line feed sequences with their hex code equivalent (the standard characters are not allowed in a URL). A URL is created, and stored in the variable named URL. It then uses the ShellExecute API function to execute associated application.
At this point, the compose window for Outlook Express is displayed and ready to be sent. It uses the SendKeys message to send an Alt+S command (the "send" command for Outlook Express). Notice that there is a two second delay before the keystroke is sent. This can be adjusted as needed (one second may work fine).
I only use SendKeys as a last resort -- and this qualifies. Because of the pause, this macro is not very fast.
Note: The size of the email message is limited to (I think) 255 characters.
Note: My first approach was to use the FollowHyperlink method of the Workbook object. However, I was not able to figure out how to insert blank lines in the email body. Ron de Bruin solved that problem (see below).


"Bugs" suggested a way to insert a block of static text into the message, thereby negating the 255-character limit. The solution? Copy your text to the Clipboard. Then add the following statement before the Application.SendKeys statement:
 Application.SendKeys "{Tab}{Tab}{Tab}{Tab}{Tab}^{End}{Return}{Return}^v"
This statement tabs through the email window headers, jumps to the end of the message text, inserts two carriage returns, and pastes the contents of the clipboard. You may need to experiment with the number of Tabs sent.
For more information on sending email from Excel, visit Ron de Bruin's site.


Heart Care FAQ

Qn: What are the thumb rules for a layman to take care of his heart?
1. Diet - Less of carbohydrate, more of protein, less oil
2. Exercise - Half an hour's walk, at least five days a week; avoid lifts and avoid sitting for a longtime
3. Quit smoking
4. Control weight
5. Control blood pressure and sugar
Qn: Is eating non-veg food (fish) good for the heart?
Ans: No
Qn: It's still a grave shock to hear that some apparently healthy person gets a cardiac arrest. How do we understand it in perspective?
Ans: This is called silent attack; that is why we recommend everyone past the age of 30 to undergo routine health checkups.
Qn: Are heart diseases hereditary?
Ans: Yes
Qn: What are the ways in which the heart is stressed? What practices do you suggest to de-stress?
Ans: Change your attitude towards life. Do not look for perfection in everything in life.
Qn: Is walking better than jogging or is more intensive exercise required to keep a healthy heart?
Ans: Walking is better than jogging since jogging leads to early fatigue and injury to joints
Qn: You have done so much for the poor and needy. What has inspired you to do so?
Ans: Mother Theresa , who was my patient
Qn: Can people with low blood pressure suffer heart diseases?
Ans: Extremely rare
Qn: Does cholesterol accumulates right from an early age (I'm currently only 22) or do you have to worry about it only after you are above 30 years of age?
Ans: Cholesterol accumulates from childhood.
Qn: How do irregular eating habits affect the heart ?
Ans: You tend to eat junk food when the habits are irregular and your body's enzyme release for digestion gets confused.
Qn: How can I control cholesterol content without using medicines?
Ans: Control diet, walk and eat walnut.
Qn: Can yoga prevent heart ailments?
Ans: Yoga helps.
Qn: Which is the best and worst food for the heart?
Ans: Fruits and vegetables are the best and the worst is oil.
Qn: Which oil is better - groundnut, sunflower, olive?
Ans: All oils are bad.
Qn: What is the routine checkup one should go through? Is there any specific test?
Ans: Routine blood test to ensure sugar, cholesterol is ok. Check BP, Treadmill test after an echo.
Qn: What are the first aid steps to be taken on a heart attack?
Ans: Help the person into a sleeping position , place an aspirin tablet under the tongue with a sorbitrate tablet if available, and rush him to a coronary care unit since the maximum casualty takes place within the first hour.
Qn: How do you differentiate between pain caused by a heart attack and that caused due to gastric trouble?
Ans: Extremely difficult without ECG.
Qn: What is the main cause of a steep increase in heart problems amongst youngsters? I see people of about 30-40 yrs of age having heart attacks and serious heart problems.
Ans: Increased awareness has increased incidents. Also, s edentary lifestyles, smoking, junk food, lack of exercise in a country where people are genetically three times more vulnerable for heart attacks than Europeans and Americans.
Qn: Is it possible for a person to have BP outside the normal range of 120/80 and yet be perfectly healthy?
Ans: Yes.
Qn: Marriages within close relatives can lead to heart problems for the child. Is it true?
Ans : Yes, co-sanguinity leads to congenital abnormalities and you may not have a software engineer as a child
Qn: Many of us have an irregular daily routine and many a times we have to stay late nights in office. Does this affect our heart ? What precautions would you recommend?
Ans : When you are young, nature protects you against all these irregularities. However, as you grow older, respect the biological clock.
Qn: Will taking anti-hypertensive drugs cause some other complications (short / long term)?
Ans : Yes, most drugs have some side effects. However, modern anti-hypertensive drugs are extremely safe.
Qn: Will consuming more coffee/tea lead to heart attacks?
Ans : No.
Qn: Are asthma patients more prone to heart disease?
Ans : No.
Qn: How would you define junk food?
Ans : Fried food like Kentucky , McDonalds , samosas, and even masala dosas.
Qn: You mentioned that Indians are three times more vulnerable. What is the reason for this, as Europeans and Americans also eat a lot of junk food?
Ans: Every race is vulnerable to some disease and unfortunately, Indians are vulnerable for the most expensive disease.
Qn: Does consuming bananas help reduce hypertension?
Ans : No.
Qn: Can a person help himself during a heart attack (Because we see a lot of forwarded emails on this)?
Ans : Yes. Lie down comfortably and put an aspirin tablet of any description under the tongue and ask someone to take you to the nearest coronary care unit without any delay and do not wait for the ambulance since most of the time, the ambulance does not turn up.
Qn: Do, in any way, low white blood cells and low hemoglobin count lead to heart problems?
Ans : No. But it is ideal to have normal hemoglobin level to increase your exercise capacity.
Qn: Sometimes, due to the hectic schedule we are not able to exercise. So, does walking while doing daily chores at home or climbing the stairs in the house, work as a substitute for exercise?
Ans : Certainly. Avoid sitting continuously for more than half an hour and even the act of getting out of the chair and going to another chair and sitting helps a lot.
Qn: Is there a relation between heart problems and blood sugar?
Ans: Yes. A strong relationship since diabetics are more vulnerable to heart attacks than non-diabetics.
Qn: What are the things one needs to take care of after a heart operation?
Ans : Diet, exercise, drugs on time , Control cholesterol, BP, weight.
Qn: Are people working on night shifts more vulnerable to heart disease when compared to day shift workers?
Ans : No.
Qn: What are the modern anti-hypertensive drugs?
Ans : There are hundreds of drugs and your doctor will chose the right combination for your problem, but my suggestion is to avoid the drugs and go for natural ways of controlling blood pressure by walk, diet to
reduce weight and changing attitudes towards lifestyles.
Qn: Does dispirin or similar headache pills increase the risk of heart attacks?
Ans : No.
Qn: Why is the rate of heart attacks more in men than in women?
Ans : Nature protects women till the age of 45. (Present Global census show that the Percentage of heart disease in women has increased than in men )
Qn: How can one keep the heart in a good condition?
Ans : Eat a healthy diet, avoid junk food, exercise every day, do not smoke and, go for health checkup s if you are past the age of 30 ( once in six months recommended)....

 Send it to all your friends....... They might be benefitted……