Blog

A Blog with Resources ....





How to add the Developer Tab in Excel 2010

Posted by on Jan 13, 2013 in MS Excel | 0 comments

Adding the Developer Tab in Excel 2010

  •     Click on the File tab to open the file menu. – its the first option in the menu.
  •     Click on Options in the menu
  •     Click on the Customize Ribbon option.
  •     See the drop down list under the label” Choose Commands from” and select     Main Tabs
  •    Under the Main Tabs section – you will see Developer option. Select it and click on “ADD” button  thats in the middle of the two box. as show in the picture.
  •     Click OK .

The Developer tab should now be visible in the main menu in Excel 2010.

 

 


Remove Duplicates in Excel Records

Posted by on Jan 13, 2013 in MS Excel | 0 comments

Excel – 2010 or 2007



You can Catch  Duplicates by the feature that is available in Excel 2007 or 2010 version.

Under Home tab, Under Style section – click on Conditional Formatting,

Select “Highlight Cells Rules” and click on Duplicate Values . The window will popup as shown below. Select the colour from the list and click OK.  The duplicate values will be highlighted and it will be easily visible to you if you want to spot any duplicate records! 🙂


 

How to Send and Receive Emails Automatically in Outlook 2010

Posted by on Jan 13, 2013 in MS Outlook | 0 comments


Here’s how to set your emails to come in automatically as often as you want:

  1. Click on the Send/Receive tab on the ribbon at the top of your screen.
  2. In the “Send & Receive” section on the left, click on “Send/Receive Groups.”
  3. Click on “Define Send/Receive Groups” in the drop down menu, you will see the popup window as shown below in the first screenshot.
  4. In the popup window “Send/Receive Groups, check the box next to “Schedule an automatic send/receive every __ minutes.”  – enter how ever minute you like for the outlook to send/receive emails.!
  5. Click on the Close button.


Concatenate

Posted by on Dec 31, 2012 in MS Excel | 0 comments


Concatenate

The concatenate function strings together the contents of a series of cells (text1, text2). The order that you select the cells is the order that they are combined into the resulting cell.

Syntax: CONCATENATE(text1,text2,…)

Shortcut: The symbol “&” can also be used instead of the concatenate function (=A2&B2).

Example

Image:Xls2 concatenate.jpg


Retrieving Random Rows

Posted by on Dec 31, 2012 in SQL | 0 comments


You can run script below and it will give random rows every single time you run it:

 

USE AdventureWorks2012
GO

-- Method 1
SELECT TOP 100*
FROM SalesOrderDetail
ORDER BY NEWID()
GO


-- Method 2
SELECT TOP 100 *
FROM SalesOrderDetail
ORDER BY CHECKSUM(NEWID())
GO


Removing Leading Zeros

Posted by on Dec 31, 2012 in SQL | 0 comments

Removing Leading Zeros From Column in Table

 

USE tempdb
GO
-- Create sample table
CREATE TABLE Table1 (Col1 VARCHAR(100))
INSERT INTO Table1 (Col1)
SELECT '000006'
UNION ALL
SELECT '00501007'
UNION ALL
SELECT '50401003'
UNION ALL
SELECT '0100 000 001'
UNION ALL
SELECT '00.0001'
UNION ALL
SELECT '07.001'
GO

-- Display the data from the new table

SELECT *
FROM Table1
GO

-- Remove leading zeros
SELECT
SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))
FROM Table1
GO


-- Clean up
DROP TABLE Table1
GO

 

Compare Two documents in Word

Posted by on Dec 30, 2012 in MS Word | 0 comments

    1. Open Microsoft Word and go to Review > Compare.

    1. It will open the Compare Document dialog box where you can browse the documents you want to compare. In the Original document field, select the original document and from the Revised document field, select the document in which you made changes.

    1. Click on More to view more comparison settings and check what data in the documents you want to compare. Check the fields and once you are satisfied, click OK.

    1. This will open a window that displays the comparison results. It will have a series of columns and panes. The ones to focus on are Compared Document, Original Document and Revised Document.

    1. In the Compared Document field, the changes between the two documents will be highlighted for you.

  1. This way you can compare two documents and can find all of the differences.

Need Random text in word?

Posted by on Dec 30, 2012 in MS Word | 0 comments

Type the following:  in any case

=rand()

USEFUL SQL TIPS

Posted by on Dec 30, 2012 in SQL RESOURCES | 0 comments

This is a whitepaper of Top Ten SQL Performance Tips

I found this to be a very useful link:

http://www.quest.com/whitepapers/10_SQL_Tips.pdf

Excel Error message

Posted by on Dec 30, 2012 in MS Excel | 0 comments

Excel Error Messages to Get to Know

Excel gives you an error message if you create a formula in Excel that contains an error or circular reference. It also gives you a brief description about the error. Understanding the error and their meaning helps correct the problem .

Error

Meaning

#DIV/0! Trying to divide by 0
#N/A! A formula or a function inside a formula cannot find the referenced data
#NAME? Text in the formula is not recognized
#NULL! A space was used in formulas that reference multiple ranges; a comma separates range references
#NUM! A formula has invalid numeric data for the type of operation
#REF! A reference is invalid
#VALUE! The wrong type of operand or function argument is used

Absolute Cell References vs Relative Cell References

Posted by on Dec 30, 2012 in MS Excel | 0 comments

When you copy and paste a formula in Excel, Excel looks at how you create the references within the formula – depending on how you have created the references , the values are displayed. This is where Relative and Absolute Cell reference matter! The formula can completely give you different results  – depending on either change the references relative to the cell where you’re pasting it (relative reference), or it can always refer to a specific cell. You can also mix relative and absolute references so that, when you move or copy a formula, the row changes but the column does not, or vice versa.

Preceding the row and/or column designators with a dollar sign ($) specifies an absolute reference in Excel.

Example

Comment

=A1 Complete relative reference
=$A1 The column is absolute; the row is relative
=A$1 The column is relative; the row is absolute
=$A$1 Complete absolute reference

 

Excel Functions

Posted by on Dec 30, 2012 in MS Excel | 0 comments

MOST COMMONLY USED FUNCTIONS IN EXCEL:

SUM Calculates the sum of a group of values
AVERAGE Calculates the mean of a group of values
COUNT Counts the number of cells in a range that contains numbers
INT Removes the decimal portion of a number, leaving just the integer portion
ROUND Rounds a number to a specified number of decimal places or digit positions
IF Tests for a true or false condition and then returns one value or another
NOW Returns the system date and time
TODAY Returns the system date, without the time
SUMIF Calculates a sum from a group of values, but just of values that are included because a condition is met
COUNTIF Counts the number of cells in a range that match a criteria
LEFT Extracts one or more characters from the left side of a text string
RIGHT Extracts one or more characters from the right side of a text string
MID Extracts characters from the middle of a text string; you specify which character position to start from and how many characters to include
CONCATENATE Assembles two or more text strings into one
REPLACE Replaces part of a text string with other text
LOWER Converts a text string to all lowercase
UPPER Converts a text string to all uppercase
PROPER Converts a text string to proper case
LEN Returns a text string’s length (number of characters)