Friday, 12 October 2012

Some Tricks in Excel - MS Office

* Generate Random NumbersNeed to create random numbers? You can do it in Excel. 
To generate a number between 0 and 1, type =RAND() in a cell.
To generate a number between 1 and 100, type =RAND()*100.
After entering, use the fill handle to quickly populate as many cells with random numbers as needed. To use the fill handle, click the cell, move your pointer over the lower-right corner of the cell until it turns into a black plus sign, and drag it horizontally or vertically across the cells you wish to populate. The cells can then be formatted as desired.

* No Formula, PleaseWhen copying and pasting a cell that contains a formula, use the Paste Special feature. First, copy the cell (Edit...Copy). Next click in the desired location and click Edit...Paste Special. Choose Values to copy the number only and not the formula. 

* Insert Time/Date in ExcelTry these keyboard shortcuts to insert the time/date in an Excel spreadsheet:
Current date: Press CTRL+SEMICOLON
Current time: Press CTRL+SHIFT+ SEMICOLON
Current date and time: Press CTRL+ SEMICOLON then SPACE then CTRL+SHIFT+ SEMICOLON

* Hide Worksheets in Excel 2002To hide Excel worksheets to prevent unwanted changes, Select the worksheet, click Format...Sheet...Hide.

* Color-Coding Excel Sheet Tabs in Excel 2002In Excel 2002, color-code sheet tabs for easier identification or grouping. 
Select the sheet(s) by holding down the CTRL key and clicking the tabs.
Click Format...Sheet...Tab Color. You can also right-click the sheet tab and choose
click Tab Color.

* Pasting an Excel Table and Its Formatting into WordIn Excel, select the table and click Edit..Copy. Switch to Word, and click where the table will be located. Click Edit...Paste. Using the Paste Options smart tag, select one of the following options: To keep the formatting, select Keep Source Formatting. To automatically keep data updated as it is updated in Excel, select Keep Source Formatting and Link to Excel. To match the style of another table in the Word document, select Match Destination Table Style. To link the table instead of copying it, select Match Destination Table Style and Link to Excel.

* Go ToTo search for specific cells, such as ones that have formulas or ones that just contain values, use the Go To feature. Click Edit...Go to...and choose the desired feature.

* Shortcut KeysTo see a complete list of shortcut keys in Excel, press F1 on the keyboard and type shortcut keys in the search box. 

* Customizing ToolbarsRight-click on any toolbars and click the customize the toolbar. Click the Command tab, select the desired category, and click and drag new features from the right command box to the toolbar.

* Quick GraphsWant to create a quick graph? Click anyway in the Excel data on the spreadsheet, press F11 key and presto! Right-click in the graph border to change the type, location, or data.

* Basic Keyboard ShortcutsF1 Help
F2 Edit current Cell
F5 Goto
F7 Spell Check
F12 Save file as
CTRL + A Select entire worksheet.
CTRL + B Toggle Bold Text.
CTRL + C Copies the item or items selected to the Clipboard and can be pasted using CTRL + V.
CTRL + F Displays the Find dialog box.
CTRL + H Displays the Replace dialog box.