Shantanu's Blog

Database Consultant

February 28, 2005


A quick calculation

To perform a quick calculation in the Formula Bar:
1. In the Formula Bar, type an equals (=) sign.
2. Type the numbers to multiply, for example, 9*5.
3. Press F9 to calculate and convert the formula to a value.
4. Press Enter to move down to the next cell. The value displayed in the cell is 45.

February 26, 2005


Text to column

There are a number of tasks those can be accomplished using "Text to column" option found under Data menu.

To separate first name and last names:
1. Select Column A or the range of cells containing the list of names.
2. From the Data menu, select Text to Columns.
3. In Step 1 of 3, select Delimited.
4. In Step 2 of 3, select the Space checkbox.
5. In Step 3 of 3, in the Destination box, select cell B1, and then click Finish.

To convert date values to text
1. Select Column A or the range of cells containing the list of dates.
2. From the Data menu, select Text to Columns.
3. In Step 1 of 3, select Delimited.
4. In Step 2 of 3, click on Next>
5. In Step 3 of 3, in the "Column data format", choose "Text". and then click Finish.

You can also use the same method to convert values from a text file to excel. If you have exported data from Oracle in text format, try the method mentioned above to import it in excel.

February 25, 2005


Power Outage Knocks Wikipedia Offline

Netcraft: Power Outage Knocks Wikipedia Offline: "The free online encyclopedia Wikipedia has been knocked offline by a power outage in its data center. While the servers hosting the site were down only a short time, much of the site's content remained offline as Wikimedia staff worked on properly restoring data from MySQL databases."


Flash Graph

Type a name to know if the name is popular. For e.g. If you type JOHNN you will get 2 options, johnny and johnnie click on any one of the name to how it has progressed over the decades. It's a fun tool but a module for business graphs to represent complex data.


Hotmail outage

Hotmail outage peeves some e-mail users | CNET "The outage has caused intermittent access to Hotmail accounts for some consumers over the last couple of weeks and, more alarmingly, appears to have erased new and saved messages from in-boxes, according to several people who contacted CNET about the problem."

February 20, 2005


Watermark in word documents

To create a watermark using Word 2002 and later,

1. From the Format menu, select Background and then Printed Watermark…
2. In the Printed Watermark dialog, select the radio button for Text watermark.
3. In the Text field, either type your value or select from the predefined list.
4. Select any other options and click the OK button.

February 17, 2005


More excel tips:

So you know what will the formula =sum(a1:a5) in excel will return. There are a few other functions like min and max or avg which you must have tried. But did you know what does =RANK(A1,$A$1:$A$5) do? It will display the ranking of the number in cell A1 within the range A1 to A5
If you want to print the row and column headings, go to File - Page setup... - Sheet tab and check Row and column headings. It will print 1,2,3 rows and A, B, C columns. But if you want to print only the row numbers without column headings, type the following formula in the column A
It will print the current row number in the cell.


Google Toolbar 3 beta

Google Toolbar 3 beta: What's new in the new version of google toolbar?
SpellCheck: Check your spelling whenever you type in web forms
WordTranslator: Translate English words into other languages
AutoLink (US only): Turn street addresses into links to online maps
Requires Internet Explorer 5.5+

February 14, 2005


Quickly Typing Dates into Cells

Typing a large amount of data into cells can be tiring, especially if it includes a series of dates. Type the day (serial number only) into cell A1 and add the following formula to insert the month and year into cell B1:
Type a full number in the cell and change it to a date by using the following formula:
For example, type 122203, the result is 12/22/03.


Limiting scroll area in Excel

To set the Scroll Area range, click the Properties icon from the Control Toolbox toolbar. In the Scroll Area text box, type the scroll area range. (To cancel the Scroll Area restricted range, clear the Scroll Area text box.) Note: You cannot add two Scroll Areas to the Scroll Area text box.

February 12, 2005


Amazon Prime Amazon Prime

* Free Two-Day Shipping on over a million in-stock items
* Overnight Shipping for only $3.99 per item—order as late as 6:30 PM


Newsburst RSS reader from C|NET

Newsburst : Help | CNET "Newsburst is a personalized RSS reader tool that tracks virtually any type of information on the Web: news, blogs, shopping lists, weather, search results, alerts, auctions and more."


Learn Linux - Shell Commands

Learn Linux - Shell Commands: "Using Linux - The Command Prompt

Linux Commands

When you enter a command in Linux, you type a command at a prompt and then press enter. Commands can be more than one word--some require switches (which modify the command's behavior ) and/or file names (which tell the command what data to act on)."


Firefox gets Yahoo toolbar

Images: Firefox gets Yahoo toolbar | CNET
Yahoo said Wednesday, 9 Feb. 05 that it has released a test version of its toolbar for use with the Firefox browser.

February 11, 2005


Permutations and Combinations in Excel

These are two great functions in excel.
The syntax is =COMBIN(Number, Number_chosen). The number is how many things you have. Eg 3 sandwich spreads. The number chosen is how many you want to combine. For example how many different combinations of 2 sandwich spreads can you make when you have 4 spreads available. The formula is =COMBIN(4,2) and the answer is 6 different combinations of two sandwich spreads.

A permutation is any set or subset of objects or events where internal order is significant. Permutations are different from combinations, for which the internal order is not significant.
So the number total number of permutations you can make from 4 objects using 2 objects.
from this formuls =PERMUT(4,2) and the answer is 24. - Excel VBA: Remove Blank Rows - Excel VBA: Remove Blank Rows: "The obvious way to remove blank rows from a data set is to simply sort the data. This moves the blank rows to the bottom of the data and 'removes' them. But what if you want the blank rows removed, however you don't want the data sorted? VBA."


Count Used Cells With COUNTA() Function

Count Used Cells With COUNTA() Function:
Count Used Cells With COUNTA() Function

You can use the COUNTA() function in Excel to count non blank cells. In other words, use COUNTA() to get the total used cells in a Range.
Here's an example formula using COUNTA(), the following returns the count of non blank cells in the Range A1:A10
Also note: Empty cells with a space character in them are considered non blank or used cells and create undesired results, possibly caused by the spacebar delete.
To count the blank cells in a range use the COUNTBLANK() function.


Up and down arrows based on conditional formatting

When you look at the income statement, you can see the numbers but it's hard to tell what area is favorable or unfavorable. You see that net revenue and NOI are positive this year vs. last year, but when you get to the line item detail it's harder to see the differences.

1. Write a formula:
To help your reader quickly see the differences, combine font and conditional formatting to create a symbol.
The formula cell F3 is:
=IF(D3 [ E3,"q","p")
2. Apply color:
To color the letters, apply conditional formatting. In this case we want letters associated with unfavorable variances to be colored red and letters associated with favorable variances to be colored green.
3. Apply arrows:
Finally, the Wingdings 3 font is applied to the cells with formulas in column F.

The colored arrows make it much easier to quickly see what areas are favorable or unfavorable. There are literally hundreds of formatting symbols that you can use via the different font families that Excel provides.


Documentation tip

How can I document my worksheet showing row and column heading?

To document an Excel workbook you would like a picture in a Word file showing an area of a worksheet with the column and row headings as in the figure.
To achieve this:
1) Use Page SetUp/ Sheet to specify print with row and column headings,
2) select the range required,
3) Hold down SHIFT and select the Edit command from the menu bar; this now contains Copy Picture,
4) In the Copy Picture dialog box, use the As Printed option,
5) you now have an image ready to paste into another application.


Prompting Before a Save

There are times when you might want to add a prompt to a workbook that triggers a question or action before the workbook is saved. When added to the ThisWorkbook code module, this macro will prompt the user with a "Did you fill in cell A2" question when the user tries to save the file. If he answers "Yes", then the macro will exit and the save event continues. If he answers "No", the save event is canceled.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Did you fill in cell A2?"
Style = vbYesNo
Title = "Save Data Prompt"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
Exit Sub
Cancel = True
End If
End Sub

February 10, 2005


Wikipedia and you

My fellow WikiPedians of the world: ask not what the WikiPedia will do for you, but what together we can do for the knowledge of man.

February 09, 2005


Google Maps

To anyone who is working on maps, check this out:
Zoom somewhere and *drag* the map around. Search for anything and find it on the map (spelling corrections is working as usual...)
Only the USA for now, and only IE and Mozilla/Firefox.

February 05, 2005


Usability taken to the next level

Though acronym is one of the basic HTML tag supported since IE 2.0, it does not has any inherent effect on text display other than show the text in title attribute as yellow tool tip. You can spice up the concept by adding a different cursor shape. In the head section of you html page or in the external stylesheet, add the following code.

abbr, acronym {
border-bottom: 1px solid #AAA;
cursor: help;

Now use the acronym tag anywhere on the page. for e.g.
Don't post any PNG images on here.

Now the acronym will not only have the yellow tool tip, but also the help shaped curser instead of regular arrow.

February 04, 2005


PHP: Alternative syntax for control structures

PHP: Alternative syntax for control structures - Manual: Alternative syntax for control structures

PHP offers an alternative syntax for some of its control structures; namely, if, while, for, foreach, and switch. In each case, the basic form of the alternate syntax is to change the opening brace to a colon (:) and the closing brace to endif;, endwhile;, endfor;, endforeach;, or endswitch;, respectively.

?php if ($a == 5): ?
A is equal to 5
?php endif; ?

In the above example, the HTML block 'A is equal to 5' is nested within an if statement written in the alternative syntax. The HTML block would be displayed only if $a is equal to 5.

February 03, 2005


Adding RSS Feeds to MY Yahoo

Sometimes a name can go against you and I think this may be the case with RSS. One common translation of the abbreviation is "really simple syndication". As one friend asked me, "If it's so simple, how come I don't get it and I'm not using it?" The question has merit as the simplicity may reside more with the publisher. Fortunately, MY Yahoo! offers a simple way of adding RSS to your pages. Check out the 4 minute flash tutorial.


PowerPoint Slide Show Settings: Kiosk Setup

PowerPoint - Kiosk Setup
Setting up an information booth or kiosk is very simple. Any PowerPoint slide show can be made to run automatically and to loop continuously if desired.
1. Set timings for all slides, this can be done from the Slide Sorter View by selecting Slide Transition from the Slide Show menu.
Alternatively, click the Rehearse Timings icon from the Slide Sorter View to run the slide show, click with the mouse to advance through the slides at the desired pace. At the end of the show PowerPoint will give a summary of the time taken for the entire show and register these timings for each slide.
To get rid of these Rehearsed Timings, Select "Set Up Show" from Slide Show Menu and check "Manually" inside the Advance Slides box.
2. Select Set Up Show from the Slide Show menu.
3. Select Browsed At A Kiosk, in the Set Up Show window under Show Type and under Advance Slides select Using Timings, If Present.
Note that it may be a good idea to also unplug the keyboard from the computer, as the [Esc] key will stop the show.


Firefox extension wishlist

Wouldn't it be nice if for every site we visit, we would automatically see a display of how many pages the domain at large contains in Google? This could be made into a nice Firefox extension. E.g. you visit "", then Google should be queried for "", and the resulting page count should be displayed in the Firefox status bar at the bottom.


Gmail labels explained

A shortcut to search for all messages having a specific label is to type the Label prefixed with the "label:" query word in any simple search field at the top of any Gmail page:
If you want to view all messages that have selected multiple Labels, for example messages having both 'work' and 'client', enter the following into the simple search field at the top of any Gmail page:
label:work label:client
To see all messages with either 'work' or 'home', you can enter:
label:work OR label:client
Note: the specific label names are NOT case sensitive, but the "OR" operator is case sensitive.

This type of searching goes way beyond this by letting you search using more complex criteria. For example, building on our example above, say you want to search for messages containing attachments from your family sent before May 21, 2004? You would simply enter the following advanced search criteria:
label:family has:attachment before:2004/5/21
Yes, this could actually be done in the Search Options pane, but in addition to the available search criteria fields, query words not only let you search using criteria not included in the Search Options pane, (like "cc:" and "bcc:") but you can do "compound" searches otherwise not available in the Search Options pane. For example:
label:doctors label:statements has:attachment before:2004/5/21 in:anywhere

Here are some of the keywords other than lable: mentioned above.
from: to: subject: cc: bcc: label: has:attachment filename: after: before:
You can also use the "in" keyword.
in:anywhere in:inbox in:trash in:spam in:starred in:unread in:read
And the comparison is done using OR, - (minus sign to exclude a word), "" (Double inverted comma) and () (Brackets)

February 02, 2005


Technology - Why There's No Escaping the Blog - FORTUNE

According to blog search-engine and measurement firm Technorati, 23,000 new weblogs are created every day—or about one every three seconds. Each blog adds to an inescapable trend fueled by the Internet: the democratization of power and opinion. Blogs are just the latest tool that makes it harder for corporations and other institutions to control and dictate their message. An amateur media is springing up, and the smart are adapting. Says Richard Edelman, CEO of Edelman Public Relations: "Now you've got to pitch the bloggers too. You can't just pitch to conventional media."
Of course, it's difficult to take the phenomenon seriously when most blogs involve kids talking about their dates, people posting pictures of their cats, or lefties raging about the right (and vice versa). But whatever the topic, the discussion of business isn't usually too far behind: from bad experiences with a product to good customer service somewhere else. Suddenly everyone's a publisher and everyone's a critic.
says Marissa Mayer, who oversees the search site and all of Google's consumer web products. 'Our legal department loves the blogs, because it basically is a written-down, backed-up, permanent time-stamped version of the scientist's notebook. When you want to file a patent, you can now show in blogs where this idea happened.'


encyclopedia, not an experiment

Wikipedia is not primarily an experiment in Internet democracy. It's a project to write an encyclopedia. Wikipedia *is* a grand social experiment of course. But not _primarily_.


Data Definition Language, Part 1

Studying for the MySQL Certification exam? This article, the first of two parts, covers roughly 10 percent of the material that will appear on the exam, including general database and table properties, storage engines and table types, and more. It is excerpted from chapter four of the book MySQL Certification Guide written by Paul Dubois


Creating Pop-Up Notes with CSS and JavaScript Part I

A pop-up note contains information and can be made to appear when a visitor to your website moves the mouse over the appropriate area. Alejandro Gervasio explains how to create several different kinds of pop-up notes using CSS and JavaScript.

February 01, 2005


Removing Icons Performing Two Different Tasks

Some of the icons on the Standard and Formatting toolbars perform two different (but related) tasks. To toggle between the two tasks, press Shift while clicking the icon. The icons that perform different tasks are:

In the Standard toolbar:

*Print Print Preview
*Sort Ascending Sort Descending
*Open Save
*In the Formatting toolbar:
*Increase Indent Decrease Indent
*Underline Double Underline
*Center Merge and Center
*Increase Decimal Decrease Decimal

Example: The Open icon changed to Save when pressing the Shift key.

To remove the double-task icons:
Drag the icon off the toolbar while holding the Alt key.


June 2001   July 2001   January 2003   May 2003   September 2003   October 2003   December 2003   January 2004   February 2004   March 2004   April 2004   May 2004   June 2004   July 2004   August 2004   September 2004   October 2004   November 2004   December 2004   January 2005   February 2005   March 2005   April 2005   May 2005   June 2005   July 2005   August 2005   September 2005   October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   May 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006   December 2006   January 2007   February 2007   March 2007   April 2007   June 2007   July 2007   August 2007   September 2007   October 2007   November 2007   December 2007   January 2008   February 2008   March 2008   April 2008   July 2008   August 2008   September 2008   October 2008   November 2008   December 2008   January 2009   February 2009   March 2009   April 2009   May 2009   June 2009   July 2009   August 2009   September 2009   October 2009   November 2009   December 2009   January 2010   February 2010   March 2010   April 2010   May 2010   June 2010   July 2010   August 2010   September 2010   October 2010   November 2010   December 2010   January 2011   February 2011   March 2011   April 2011   May 2011   June 2011   July 2011   August 2011   September 2011   October 2011   November 2011   December 2011   January 2012   February 2012   March 2012   April 2012   May 2012   June 2012   July 2012   August 2012   October 2012   November 2012   December 2012   January 2013   February 2013   March 2013   April 2013   May 2013   June 2013   July 2013   September 2013   October 2013   January 2014   March 2014   April 2014   May 2014   July 2014   August 2014   September 2014   October 2014   November 2014   December 2014   January 2015   February 2015   March 2015   April 2015   May 2015   June 2015   July 2015   August 2015   September 2015   January 2016   February 2016   March 2016   April 2016   May 2016   June 2016   July 2016   August 2016   September 2016   October 2016   November 2016   December 2016   January 2017   February 2017   April 2017   May 2017   June 2017   July 2017   August 2017   September 2017   October 2017   November 2017   December 2017   February 2018   March 2018   April 2018   May 2018   June 2018   July 2018   August 2018   September 2018   October 2018   November 2018   December 2018   January 2019   February 2019   March 2019   April 2019   May 2019   July 2019   August 2019   September 2019   October 2019   November 2019   December 2019   January 2020   February 2020   March 2020   April 2020   May 2020   July 2020   August 2020   September 2020   October 2020   December 2020   January 2021   April 2021   May 2021   July 2021   September 2021   March 2022   October 2022   November 2022   March 2023   April 2023   July 2023   September 2023   October 2023   November 2023   April 2024   May 2024   June 2024  

This page is powered by Blogger. Isn't yours?