Remove Duplicates

Excel 2010 Remove Duplicates

Read More..

WEEKNUM

How to display what week number of a year a specific date is on in Excel 2010 WEEKNUM

1.      Go to the Formula tab> Select the Date & Time drop down list

2.      Click on WEEKNUM

 

3.      The function argument window will pop up

 

4.      In the Serial_number input box enter the required date with quotes (M/D/YYYY)

5.      In the return type enter 1 for Sunday as the first day of the week or 2 for Monday as the first day of the week

6.      Click on OK

7.      The result given for the 2^ndof August 2012 with Sunday being the first day of the week is

 

8.  So we are in week 31 of the year

Read More..

Else IF Statement

Excel 2010 Else IF Statement

=IF(A2="Hawl","Ha",IF(A2="Kental","Ken",IF(A2="Planet","Plan","Pli")))

Read More..

Format Painter

Excel Format Painter,

 

    Excel 2010 Format Painter YouTube Video

      1.    Select the cell with the color you want to use…e.g. select cell A2 with yellow

      2.      Go to the “Home” tab in the “Clipboard” section and select the “Format Painter”

3.      Then click on the cell you want to fill with that color, e.g. cell B2

   4.      The color in cell B2 will now be yellow as well which is reflected from cell A2

   5.      Repeat the steps for all required cells

You can also do it much more quickly by

   6.      Highlighting e.g. column A> then click on “Format Painter” in “Clipboard” group

   7.      Then select e.g. column B

   8.      The colors on column A will now be reflected in the same way on column B

   9.      That's it

Read More..

Concatenate Formula

Excel 2010 Concatenate Formula

Theory
=CONCATENATE(Field 1," ",Field 2)
Example:
=CONCATENATE(B2," ",C2)
The concatenate formula is ideal for merging two or more fields together. The concatenate formula is also ideal for creating unique identifier fields or unique ID columns without creating a completely new column for inputting. For example, merging or Concatenate("Full Name" + "D.O.B" + "Post Code") of a customer can be used as an ID column. Though the data will have to be complete and kept clean. Read More..

Dollar Formula

Excel 2010 Dollar Formula

=DOLLAR(Cell Reference, Decimal Places)
This excel tutorial will look at the dollar formula.
It displays the number selected in your local currency format.
The local currency outputted is based on your regional settings. Read More..

ACCRINT Formula

Excel 2010 ACCRINT Formula Displays the total interest gained from an investment product over a certain timeframe
1. Go to Formula tab
2. Click on Financial
3. Select ACCRINT

4. The function arguments window will pop up
5. In the issue input box enter the issue date for the security enclosed with quotes
6. In the first interest input box enter the first date that interest will be paid enclosed with quotes
7. In the settlement input box enter the settlement date for the product enclosed with quotes
8. In the Rate input box the annual rate of interest (percentage)
9. In the Par input box enter the value of the security

10. In the Frequency input box enter the number of annual interest payments
11. In the Basis input box enter 0 or leave blank if you want the average number of days to be 30 days per month

12. Click OK
13. As you can see from my inputs the outputted annual payments I will receive will be 12.50 pounds sterling

Keywords: Financial formula, Excel 2010 ACCRINT Formula , Excel 2010, ACCRINT Formula Read More..

WORKDAY Formula in Excel

How to display the end date of a project when the only information you have is the start date, the number of days the project should take, and the official holiday dates in Excel 2010 WORKDAY
1. Go to the Formula tab> Select the Date & Time drop down list> Click on WORKDAY

2. The function arguments window will pop up

3. In the Start_date input box enter the start date of the project enclosed in quotes
4. In the Days input box enter the total number of days for the project
5. In the Holidays input box enter the official holiday date
6. Click OK
7. The value will be displayed on the active cell in excel as a number
8. Right click on the cell and click format cells
9. Select the number tab

10. Select Date
11. Select the required date format
12. Click OK
13. The project end date is now displayed

Read More..

Pivot Table Calculated Field

Excel 2010 Pivot Table Calculated Field

The calculated field function within pivot tables is particularly useful for calculating percentages such as commissions.

Read More..

Send Email attachment

 1  Go to> File> Send> Email (attachment) 

  2   Outlook will open

 3     Enter email address and click send

Read More..

HLOOKUP

HLOOKUP
=HLOOKUP(F2,$B$2:$C$10,1,FALSE)

Read More..

Removing spaces

1.       Highlight column in question.  In this case I will highlight column A.

NB: Make sure to highlight the required column or all spaces in the other columns will be replaced as well.

2.       Select key CTRL + H...which is the keyboard shortcut for Find and Replace.  Or go to the Home tab> Then go to the Editing group and Click “Replace”.

3.       In the “Find What” text box, enter 1 single space by pressing the space bar

4.     Make sure there are no data and no spaces in the “Replace With” text box

5.       Click “Find Next”> Then click “Replace All”.  This will replace all the spaces in the column highlighted.

6.       In my case 10 spaces have replaces with nothing that is there are no longer any spaces in the highlighted column.

Read More..

Date

DATE for today,   

   1.       Go to> Formula> Date & Time> Date

      2.       In the Year text box enter Year(now())

3.       In the Month text box enter Month(now())

4.       In the Day text box enter Day(now())

5.       Click on OK

6.       You now have today’s date, 31-JUL-2012 as M/D/YYYY

Read More..

SECOND

How to display the seconds value in a specific time,

1.      Go to the Formula tab> Select the Date & Time drop down list> Click on SECOND

2.      The function argument window will popup

3.      In the Serial_number input box enter the required time including seconds

4.      I have entered "10:25:31"

5.      The resulting value of the seconds within the time is 31.

6.      Click on OK

7.      The result is now displayed in the active cell of excel

Read More..

Weekday

WEEKDAY function for today’s date,

1.       Go to “Formula” tab> Select “Date & Time” drop down list and select "Weekday"

2.       In the “Serial_number” text box> enter "now()" and click OK

3.       The weekday of Monday is now entered in excel which is the number 2 for Monday.  This is as the first day of the week is Sunday.

Read More..

VLOOKUP Exact Match

Exact Match, Excel VLOOKUP formula,

The theory for vlookups is as follows:

=VLOOKUP(cell reference, corresponding range of cells to link to, column number to output, false)

OR

=VLOOKUP(cell reference, corresponding range of cells to link to, column number to output, 0)

Note:

"False" means Exact Match 

OR

0 means Exact Match

Excel VLOOKUP Tutorial on YouTube

Read More..

AND/OR Formula

OR Formula

=OR(A2,B2)

If either cells adjacent in column A OR B are not equal to 0 then the answer will be True or if any of the adjacent cells are equal to 0 it will answer false.

AND Formula

=AND(A2,B2)

If both cells adjacent in column A AND B are not equal to 0 then the answer will be True or if any of the adjacent cells are equal to 0 it will answer false.

Read More..

Protecting Cells in Worksheet

 1.   Highlight entire worksheet by clicking immediately to the left of the column A header and immediately above row 1 header.

  2.   Right click on highlighted worksheet and then left click on format cells.

  3.   Click on the protection tab and uncheck Locked.  Then click OK.

   4.   Then select the cells that you are interested in protecting.

   5.   I have selected cells A1 to Q30

  6.   Right click on the highlighted area and left click on format cells.

  7.   Select the protection tab and tick the locked check box.  Then click OK.

  8.   Click on the Home tab> then click the format dropdown button in the cells section> then click protect sheet.

  9.   Select the options you want to allow such as Format Cells> Then enter a password> Then click OK.

 10.   Enter the password again for confirmation. Click OK.

 11.   Finally, save the workbook.

Read More..

Sort data

Read More..

Save as PDF File

Excel 2007 Save as PDF File,

1.       Click on “File”> Then click on “Save As”> PDF or XPS

2.       PDF should be selected by default, if not select “Save as type” of PDF

3.       Enter a “File Name”

4.       The Open file after publishing if checked will open the file after saving it

5.       Select whatever “Optimize for:” option you want...I will select “Standard (publishing online...)

6.   Click the “Options” button to publish the entire workbook, a selection or just the active worksheet(s)

7.       Click OK> Then Click “Publish”>

Read More..