Conditionally Formatting Data: Determine yhe top two students in the class

MS Excel:- Determine yhe top two students in the class

Conditionally Formatting Data: Identify top, bottom, and above average values in a recent tour report

MS Excel:- Identify top, bottom, and above average values in a recent tour report

Create a pivot table in Excel

In this example, the data for the pivot table resides on Sheet1.

Highlight the cell where you'd like to see the pivot table. In this example, we've selected cell A1 on Sheet2.
Next, select the Insert tab from the toolbar at the top of the screen. In the Tables group, click on the arrow under the PivotTable button and select PivotTable from the popup menu.

A Create PivotTable window should appear. Select the range of data for the pivot table and click on the OK button. In this example, we've chosen cells A1 to D13 in Sheet1.

Your pivot table should now appear as follows:

Conditionally Formatting Data: Identify a dynamically changes number or text value in a list of products

MS Excel :- Identify a dynamically changes number or text value in a list of products

Conditionally Formatting Data: Identify specific numbers, date, and text in a list of products

MS Excel :- Identify specific numbers, date, and text in a list of products

Adding Pop-Up Documentation to a Cell

> Select the cell for which you want the pop-up to appear.
> Choose Validation from the Data menu.
> Make sure the Input Message tab is displayed. (Click here to see a related figure.)
> Make sure the Show Input Message When Cell is Selected check box is selected.
> In the Title box, enter a title for the pop-up window. (This title appears in bold at the top of the pop-up window.)
> In the Input Message box, enter the text of the documentation you want to appear in the pop-up window.
> Click OK.

If you are using Excel 2007 then you should follow these steps:
> Select the cell for which you want the pop-up to appear.
> Display the Data tab of the ribbon.
> In the Data Tools group, click the Data Validation tool. Excel displays the Data Validation dialog box.
> Make sure the Input Message tab is displayed.
> Make sure the Show Input Message When Cell is Selected check box is selected.
> In the Title box, enter a title for the pop-up window. (This title appears in bold at the top of the pop-up window.)
> In the Input Message box, enter the text of the documentation you want to appear in the pop-up window.
> Click OK.

Basic Formulas & Excel Tips

Jaydeep 's Excel Tips are for beginners to intermediate users. The secret to a great spreadsheet is planning. Most people just jump in and start creating the spreadsheet without thinking about future enhancements. In my personal opinion a good spreadsheet is not static. It is fluid and dynamic, making it more efficient and more powerful. Excel is a powerful tool in the hands of someone who plans & utilizes Excel's full potential power. Here I hope to share some useful tips for BEGINNERS when using Excel 2007.

Basic Formulas
Totals

Every body uses totals right? See the example on the right. Most users would create the total on row 3. If they need to expand to insert more rows, generally they would tend to insert above row 3. This may resultthe total excluding the new rows(unless Excel XP is used). To overcome this problem,always create the total in row 4, leaving a blank row above the total. Resize the row height as shown above and if you need to insert more rows, insert above the blank row (ie row 3). By the way the formula for the total would be =sum(a1:a3) Simple huh?

More Totals

Lets say you need to total numbers that are not in a range, example a1, a5, a10. Well normally the formula would be =a1+a5+a10. If you have 10 cells to total up that would be slow coz you need to type in "+". Next time try using type =sum( first, then hold down the control key, use the mouse & click on a1, a5, a10, then release the control key and type ) and you're done! The formula would be =sum(a1,a5,a10) Fast wasn't it?
Dates

Excel recognizes dates whether you use slash ("/") or hyphen ("-"). However if you use full stop (".") Excel would assume the date as a text. Therefore you will not be able to customize the date styles or even perform calculations with the date. So dates should be 25/12/2004 or 25-12-2004 and not 25.12.2004 (a pretty common mistake).
More Dates

Do you want excel to use the system date? Use this formula =today() and presto you can see the system date. Cool huh? But if you just want the system date without using the formula just press ctrl : and Excel will insert the system date. If you want the date & time use =now() and the date & time will appear.
Text / Strings

There are times you need include numbers or dates together with your text. Typing it in doesn't do much if the numbers keep on changing because it's a formula and you want it linked to your text. Example you need your text to say"Print date : 31-Dec-2004" but you want the date to be linked to a cell with formula so that whenever you print your report, the date in the text is always changing according to your system date. Do do that you will need a formula. Let’s say cell "A1" contains the date (formula =today() remember?). Type this formula anywhere you want the text to appear ="Print date : "&TEXT(A1,"dd-mmm-yyyy") and you're done. Each time you print your report, the date automatically changes. Similarly if you need a number or value to be linked, you could change the number format to 2 decimals this way (assuming "A1" contains the value)="Profit for the month is $ "&TEXT(A1,"0,000.00")
File Name

It is always a good idea to insert the file name & path and include it in the print range so that you will know the filename & it's location when you need to find the file. Just type in this formula =cell("filename") Note that the word "filename" is typed and not the actual filename. And lo & behold, the filename appears in the cell.

It is always a good idea to insert the file name & path and include it in the print range so that you will know the filename & it's location when you need to find the file. Just type in this formula =cell("filename") Note that the word "filename" is typed and not the actual filename. And lo & behold, the filename appears in the cell.