Useful Tips in Excel

Instant charts in Excel.

Select any of the cells in the table of numbers and press F11

Headers and Footers in Excel.

Click on the File menu and select Page Setup. Now select the Header/Footer tab. Try experimenting with the Custom Header and Custom Footer options as well.

Displaying the serial number behind Date format in Microsoft Excel

Select a cell and enter today date into the cell by pressing Ctrl + ; than press Ctrl+~.

Press Ctrl+~ again to restore the sheet to its normal appearance.

To transpose cells:

1. Copy a range of cells (see range A1:A6 below).
2. Select a cell.
3. Press Shift+F10.
OR
From the shortcut menu, select Paste Special.
4. Select the Transpose checkbox, and click OK. The list is now presented in horizontal cells.

To delete empty rows between data:

1. Select all columns containing data.
2. Click the Sort icon (either Ascending or Descending).

Checking For Duplicate Values Within A Range

Problem:

Columns A & B contain two lists of values.
We want to create a formula that will check whether there is any duplication of values within either list (blank cells are to be ignored).

Solution:

Use the COUNTA, SUMPRODUCT, and COUNTIF functions as shown in the following formula:
=IF(COUNTA(A2:A7)=SUMPRODUCT((A2:A7<>”")/COUNTIF(A2:A7,A2:A7&”")),”No Duplicates”,”Duplicates”)
The formula will return “Duplicates” if the list contains duplicate values, otherwise it will return “No Duplicates”.

List1____List2
1________1
2
3________B
A________2
________3
1________4

To manually wrap text:

1. Type the following text into a cell: “F1 Get the Most out of Adsense! The Ultimate way to generate Revenue”.
2. In the Formula Bar, place the cursor after the word “Adsense!”.
3. Press Alt+ Enter.

To cancel manual text wrapping:

1. In the Formula Bar, place the cursor where you caused the text to wrap, that is, before the word “The”.
2. Press Delete.

To filter a range into a List of unique records:

1. Select a cell the range .
2. From the Data menu, select Filter, and then Advanced Filter.
3. Select Copy to another location.
4. In the Copy to box, insert a cell address.
5. Select the Unique records only checkbox, and then click OK.

Automatic list numbering

Column A consists of formulas that refer to column B. The formula in cell A1 is:

=IF(B1<>”",COUNTA($B$1:B1)&”.”,”")

This formula, which is copied down to the other cells in column A, displays the next consecutive item number if the corresponding cell in column B is not empty. If the cell in column B is empty, the formula displays nothing.

As items are added or deleted from column B, the numbering updates automatically.

Background Image in your spreadsheet:

To customize your spreadsheet, you might want to add a background image Choose Format > Sheet > Background. Select your picture file.

Related posts:

1. Adding Drop-Down List in Excel
2. Quickly Access to other functionality of Microsoft Excel with Cells Assistant for Excel Plugin
3. Power of CTRL key in Excel
4. Update Charts Automatically
5. Moving Between Precedent and Dependent Cells
6. Protecting cells, sheets, workbooks, and files
7. 20+ Microsoft Excel Printing Tips
8. Copying Excel into Powerpoint
9. Combining Text and Values in one cell
10. Conditional Formatting in Excel