Oct 9, 2011 Learn in a Day - MS - Excel

Introduction

If you looking for easy to-use software which lets you create, maintain accounts and create reports you have found the assistant in excel. It is spread sheet program Microsoft Excel is a spreadsheet program that lets us organize our data into lists and then summarize, compare and present our data graphically

WORKBOOK

1. A Workbook contains collection of Worksheets.

2. There are 255 worksheets in single workbook

3. Worksheet is a combination of rows and columns.

4. ROW: A sequence of cells along a horizontal line.

5. There are 65536 rows in a worksheet.

6. COLUMN: A sequence of cells along a vertical line.

7. There are 256 columns in a worksheet

Save workspace:Saves a list of the open workbooks, their sizes, and their positions on the screen to a workspace file so that the screen will look the same the next time you open the workspace file

Page setupSets margins, paper source, paper size, page orientation, and other layout options for the active file.

Set print areaDefines the selected range as the print area, which is the only portion of the worksheet that will be printed

Print Preview Shows how a file will look when you print it.

Print (ctrl+P) Prints the active file or selected items. To select print options, on the File menu, click Print.

Properties Displays the property sheet for the active file.

Exit (Alt+F4) closes this program after prompting you to save any unsaved files.

Edit (alt+E)

Undo (ctrl+Z) Reverses the last command or deletes the last entry you typed.

Repeat (ctrl+Y) the Repeat command changes to Can't Repeat if you cannot repeat the last action.

Cut (Ctrl+X) Removes the selection from the active document and places it on the Clipboard.

Copy (ctrl+C) Copies the selection to the Clipboard.

Show Office Clipboard Displays the contents of the OfficeClipboard.

Paste (Ctrl+V) Inserts the contents of the Clipboard at the insertion point, and replaces any selection.

Paste Special Pastes, links, or embeds the Clipboard contents in the current file in the format you specify.

Fill

Down (ctrl+D) Copies the contents and formats of the topmost cells of a selected range into the cells below.

Right (ctrl+R) Copies the contents and formats of the leftmost cell or cells of a selected range into the cells to the right.

UpCopies the contents and formats of the bottom cell or cells of a selected range into the upper cells of the range.

LeftCopies the contents and formats of the rightmost cell or cells of a selected range into the cells to the left.

SeriesFills the selected range of cells with one or more series of numbers or dates. The contents of the first cell or cells in each row or column of the selection are used as the starting values for the series.

Justify Redistributes the text contents of cells to fill the selected range. Numbers or formulas cannot be filled and justified. Except for the left column, cells in the range should be blank.

Clear All Removes all cell contents and formatting, including comments and hyperlinks, from selected cells.

FormatsRemoves only the formatting from your selection; the content and comments are unchanged.

ContentsRemoves the cell contents (data and formulas) from selected cells without affecting cell formats or comments.

CommentsRemoves only the comments attached to selected cells; cell contents and formats are unchanged.

DeleteRemoves the selected object. In Outlook, removes the selected item from the view and moves it to the Deleted Items folder.

Delete SheetDeletes the selected sheets from the workbook. You cannot undo this command.

Move or Copy SheetMoves or copies the selected sheets to another workbook or to a different location within the same workbook.

Find (Ctrl+F)Searches selected cells or sheets for the characters you specify, and selects the first cell that contains those characters.

Replace (ctrl+H)Searches for and replaces the specified text and formatting.

Go To (Ctrl+G)Scrolls through the worksheet and selects the cell, range, or cells with special characteristics you specify.

View (alt+V)

NormalSwitches to normal view, which is the default view for most tasks in Microsoft Excel, such as entering data, filtering, charting, and formatting.

Page Break Preview Switches the active worksheet to page break preview, which is an editing view that displays your worksheet as it will print. In page break preview, you can move page breaks by dragging them left, right, up, or down.

Header and Footer Adds or changes the text that appears at the top and bottom of every page or slide.

Comments (shift+F2) Turns display of comments on the worksheet on or off.

Custom Views Creates different views of a worksheet. A view provides an easy way to see your data with different display options.

Full Screen Hides most screen elements so that you can view more of your document. To switch back to your previous view, click Full Screen or press ESC.

Zoom Controls how large or small the current file appears on the screen.

Insert (alt+I)

Insert Cells Inserts cells starting at the insertion point. You can choose to shift other cells in the table to the right or down. You can also insert an entire row or column.

Insert Rows and ColumnsInserts the number of cells, rows, or columns you select.

Worksheet (shift+f11)inserts a new worksheet to the left of the selected sheet.

Chart WizardStarts the Chart Wizard, which guides you through the steps for creating an embedded chart on a worksheet or modifying an existing chart.

Symbol Inserts symbols and special characters from the fonts that are installed on your computer.

Page Break Inserts a page break above a selected cell. This command changes to Remove Page Break if you have a cell selected that is adjacent to a manually inserted page break.

Insert Function(shift+F3) Displays a list of functions and the formats and allows you to set values for arguments.

1. Date functions

=DATE (2007, 6, 20) will return 6/20/2007

=DATEVALUE ("06/20/2007") will return 39253

=day (39253) will return 20

=month (39253) will return 06

=year (39253) will return 2007

=today () will return Current date

=now () will return Current date and time

2. Text Functions

=CHAR (97) will return "a"

=code ("a") will return 97

=CONCATENATE ("ram","krish") will return "ramkrish"

=EXACT ("hai","Hai") will return false

=EXACT ("hai","hai") will return true

=FIND ("m","computer") will return 3

=LEFT ("computerworld", 8) willreturn "Computer"

=RIGHT ("computer world", 5) will return "world"

=LEN ("Good") will return 4

=LOWER ("GOOD") will return "good"

=UPPER ("good") will return "GOOD"

=MID ("Computers", 4,5) will return "puter"

=PROPER ("kumar") will return "Kumar"

=REPLACE ("computer", 5,4,"lete") will return "complete"

=REPT ("cars ", 2) will return "cars cars"

3. Math and Trig Functions

=ABS (-12) will return 12

=COS (60) will return -0.95241298

=sin (90) will return 0.893996664

=tan (45) will return 1.619775191

=FACT (5) will return 120

=LN (6) will return 1.791759469

=MOD (100, 3) will return 1

=PI () will return 3.14

=POWER (5, 3) will return 125

=SQRT (256) will return 16

Name

Define (Ctrl+F3)Creates a name for a cell, range, or constant or computed value that you can use to refer to the cell, range, or value.

Paste Inserts the selected name into the formula bar. If the formula bar is active and you begin a formula by typing an equal sign (=), clicking Paste pastes the selected name at the insertion point.

Create Creates names by using labels in a selected range.

Apply Searches formulas in the selected cells and replaces references with names defined for them, if they exist.

Label Uses predefined label ranges so that Microsoft Excel can automatically apply text labels to your formulas.

Comment Inserts a comment at the insertion point.

Picture

Clip ArtOpens the Clip Gallery where you can select the clip art image you want to insert in your file or update your clip art collection. In PowerPoint, this command is available only in slide and notes views.

From File Inserts an existing picture in the active file at the insertion point.

Organization Chart Inserts a Microsoft Organization Chart object into your presentation, document, or worksheet.

AutoShapes Displays the AutoShape categories you can insert.

WordArt Creates text effects by inserting a Microsoft Office drawing object.

Object Inserts an object ¾ such as a drawing, WordArt text effect, or an equation ¾ at the insertion point.

Hyperlink(ctrl+K) Inserts a new hyperlink or edits the selected hyperlink.

FORMAT (ALT +O)

Cells(ctrl+1)Applies formats to the selected cells. This command might not available if the sheet is protected

Row

Height Changes the height of the selected rows. You need to select only one cell in a row to change the height for the entire row.

AutoFit Adjusts the row height to the minimum necessary to display the height of the tallest cell in the selection.

Hide Hides the selected rows or columns. Hiding rows or columns does not delete them from the worksheet

Unhide Displays rows or columns in the current selection that were previously hidden.

Column

Width Changes the width of the selected columns. You need to select only one cell in a column to change the width for the entire column

AutoFit Selection Adjusts the column width to the minimum necessary to display the contents of the selected cells

Hide Hides the selected rows or columns. Hiding rows or columns does not delete them from the worksheet.

Unhide Displays rows or columns in the current selection that were previously hidden.

Standard Width Changes the standard width of columns on a worksheet. This command is not available if the sheet is protected.

Sheet

RenameRenames the active sheet.

HideHides the active sheet. The sheet remains open and accessible to other sheets, but it is not visible.

BackgroundInserts a tiled graphic image in the worksheet background, based on the bitmap you select.

AutoFormatApplies a built-in combination of formats, called an auto format, to a cell range or a PivotTable report

Conditional FormattingTo find cells that have conditional formatting settings identical to the settings of a specific cell, click the specific cell

StyleDefines or applies to the selection a combination of formats, called a style.

TOOLS (ALT+T)

SpellingChecks spelling in the active document, file, workbook, or item.

Error CheckingFormula errors can result in error values as well as cause unintended results. Below are some tools to help find and investigate errors.

Shared WorkbooksSwitches to shared workbook mode, which allows you and other users on your network to edit and save changes to the same workbook.

TRACK CHANGES

Highlight Changes Highlights changes to cell contents in a shared workbook, including moved and pasted contents and inserted and deleted rows and columns.

Track Changes Accept or Reject Changes Finds and selects each tracked change in a document so that you can review, accept, or reject the change.

Merge WorkbooksCombines changes from multiple copies of a shared workbook into one workbook

Protection

Protect Sheet Prevents changes to cells on worksheets, items in a chart, graphic objects on a worksheet or chart sheet, or code in a Visual Basic Editor form.

Goal Seek Adjusts the value in a specified cell until a formula that is dependent on that cell reaches a target value

Scenarios Creates and saves scenarios, which are sets of data you can use to view the results of what-if analyses.

Formula Auditing

Trace Precedents Draws tracer arrows from the cells that supply values directly to the formula in the active cell (precedents).

Trace Dependents Draws a tracer arrow to the active cell from formulas that depend on the value in the active cell.

Trace Error If the active cell contains an error value such as #VALUE or #DIV/0, draws tracer arrows to the active cell from the cells that cause the error value

Remove All Arrows Removes all tracer arrows from the worksheet.

Evaluate Formula Evaluate the formula one step at a time.

Show Watch Window Display or hide a watch window to keep track of the results of cells in the spreadsheet when it recalculates.

Formula Auditing Mode Toggle the display of the worksheet to show formulas in your worksheet.

Macros Opens the Macros dialog box, where you can run, edit, or delete a macro. Use Record New Macro to record a series of actions as a macro, or click Visual Basic Editor to write a macro.

Customize Customizes toolbar buttons, menu commands, and shortcut key assignments.

Options Modifies settings for Microsoft Office programs such as screen appearance, printing, editing, spelling, and other options.

DATA (ALT +D)

Sort Arranges the information in selected rows or lists alphabetically, numerically, or by date.

FILTER Filtering is a quick and easy way to find and work with a subset of data in a list. A filtered list displays only the rows that meet the criteria you specify for a column. Microsoft Excel provides two commands for filtering lists:

§ AutoFilter, which includes filter by selection, for simple criteria

§ Advanced Filter, for more complex criteria

Form Displays a data form in a dialog box. You can use the data form to see, change, add, delete, and find records in a list or database.

Subtotals Calculates subtotal and grand total values for the labeled columns you select. Microsoft Excel automatically inserts and labels the total rows and outlines the list.

Validation Defines what data is valid for individual cells or cell ranges; restricts the data entry to a particular type, such as whole numbers, decimal numbers, or text; and sets limits on the valid entries.

Table Creates a data table based on input values and formulas you define. Data tables can be used to show the results of changing values in your formulas

Consolidate These suggestions apply only to consolidations that were created by using the Consolidate command on the Data menu. They do not apply to consolidations created by using 3-D references in formulas.

Group Defines the selected rows or columns of detail data as a group in an outline, so you can summarize the data

Ungroup Removes selected rows or columns from a group on an outlined worksheet. In a pivot Table or Pivot Chart report

Auto Outline Automatically outlines a selected range of cells or the entire worksheet, based on formulas and the direction of references

Clear Outline Removes the outline from the selected group of data. If the selection is not a specific group within the outline, the outline is removed from the worksheet

Pivot Table and PivotChart Report Starts the Pivot Table and Pivot Chart Wizard, which guides you through creating or modifying a PivotTable or PivotChart report.

Import Data Import data from a selected data source.

New Web Query Creates a new Web query to retrieve text or data that is in tables or preformatted areas on a Web page. The retrieved data does not include pictures, such as .gif images, and does not include the contents of scripts

New Database Query Brings data from external sources into Microsoft Excel, by using either the Query Wizard to create a simple query, or Microsoft Query to create a more complex query.

WINDOW (ALT+W)

New Window Opens a new window with the same contents as the active window so you can view different parts of a file at the same time.

Arrange Displays all open files in separate windows on the screen. The Arrange command makes it easier to drag between files.

Hide Hides the active workbook window. A hidden window remains open.

Split Splits the active window into panes, or removes the split from the active window.

Freeze Panes Freezes the top pane, the left pane, or both on the active worksheet. Use the Freeze Panes button to keep column or row titles in view while you're scrolling through a worksheet. Freezing titles on a worksheet does not affect printing.

Source : http://www.livetolearn.in/site/blog