Resources » Technical Tips » Excel
Microsoft Excel tutorial or working with Excel
Microsoft excel is an application program developed by Microsoft.Excel is what is known as a spreadsheet application. Microsoft excel allows you to create professional spreadsheets including large no. of rows & columns and charts. It includes three components- worksheet, graph and DBMS. It performs numerous functions and formulas.
Starting With Ms Excel
There are three way to start excel
1. Double Click on icon.
2.Click on Start Button ->Programs-> Microsoft Office-> MS-Excel
3. Start with run command box.
Follow these step.
Enter or ok button.
Excel provides some items with buttons
Formatting tool bar
Standard tool bar
Sheet Tab Scroll Button
File Menu: Includes opening a new or existing file, to save or to save with different names, to change default, setting of page margin, size & orientation & to print a document.
New: Excel will open a new untitled Workbook. To create a Workbook, click at the new option in the file menu or new button in the toolbar or use ctrl+ n keyboard shortcut key
Open: With Excel running, if you wish to open a pre-existing/loaded Workbook, choose open command on the file pull down menu or use ctrl+ o keyboard shortcut key.
Close: Close current file. (Ctrl+W)
Save: Uses the save command on the file pull down menu or use ctrl+ s keyboard shortcut.
Save as: Save the current file with another name. (F12)
Save as web page: You can save the current file as web page.
Save Workspace: Saves a list of the open workbooks, their sizes, and their positions on the screen store in one workspace file so that the next screen will look the same the next time you open the workspace file then all files are opened at a time.
File search: Search any file in computer system.
Web Page Preview: In Web page preview, you can see how your Workbook will look in a Web browser.
Page setup: Set the page setting in the file like change the margins (Top, Bottom, Left, Right & Gutter margin ) set the margin apply in whole Workbook and this point forward. Paper size (letter, A4, legal etc)
Print Area: Click the print area box to select a worksheet range to print and then drag through the worksheet areas that you want to print.
Print preview: Use print preview to display a Workbook exactly as it will be printed means view the Workbook will look how when you’ll print it.
Print: Print the current file & current page & print the selected area and print the selected pages as like 2, 4,6,9,12,15 etc.
Send to: To send current file matter to another application program.
Properties: Display the property sheet of the active file statistics information means display total pages, paragraph, lines, words, characters, characters with space in the file.
Exit: Exit from file.
Edit Menu: Includes new, cut, copy, delete or paste the selected text, object search and replace the value & pareses automatically.
Undo: Use undo to cancel the last command.
Redo: Its use redoes the last action.
Cut: Cut the selected text temporarily and paste another location.
Copy: Copy the selected text in the memory.
Office clipboard: Office Clipboard works with the standard Copy and Paste commands. Just copy an item to the Office Clipboard to add it to your collection, and then paste it from the Office Clipboard into any Office Workbook at any time. The collected items stay on the Office Clipboard until you exit Office.
Paste: Paste the text through memory in the current cursor position.
Paste special: Paste text without formatting or paste link the text through memory in the current position.
Paste as hyperlink: when you click on the text with the combination of ctrl. Cursor automatically move on the position where the data to be copied.
Clear: Delete the selected text formatting & delete text permanently.
Select all: Select all the objects, text and value in the current file.
Delete: Delete the selected range
Delete Sheet: To Delete the Current Working Sheet.
Find: A powerful and frequently used excel processing feature is of searching for a specified word and value.
Replace: Replace the particular text to another text and value.
Go to: This is one of their easiest and quickest ways to move in a big Workbook to go to the particular cell.
Links: Link two excel workbook, or link a document created with another application to a excel workbook.
Object: Edit an object embedded in the word document.
View Menu: Includes looking at the active document in different ways. To select the toolbar, rules, header & footer & so on.
Normal: This view is used only for creating and editing document. It does not show side-by-side headers, footers, vertical ruler line, and margins. Automatic page breaks are shown as dotted line.
Page Break Preview: before printing, make sure the page breaks appear where you want them
Task Pane: Show or hide the task pane.
Toolbar: Show & hide the toolbar.
Formula Bar: Remove or display the bar which displays cell address and data entered into the active cell.
Header and Footer: Work in the header and footer areas by clicking Header and Footer in View menu.
Comments: Display or hide the comments on the screen.
Full screen: Show or hide the current page matter in the full screen.
Zoom: It lets you zoom in and out to see bigger or smaller on-screen representation.
Insert Menu: Includes creating page, to add page number, date & time, are to text, comment, captions to active document or add object such as picture text box. Hyperlink to an active document.
Cells: It is used to insert the cell in worksheet.
Rows: It is used to insert the row in worksheet.
Columns: It is used to insert the column in worksheet.
Worksheet: By default, an Excel workbook is made up of three worksheets. You may insert as many additional sheets as you require. Sheets are inserted in front of the current worksheet.
Chart: This adds a chart of the selected data or of the entire worksheet if you have no data selected.
Symbol: Insert symbol to add a symbol or special character to a document.
Page Break: Inserts page breaks above and to the left of the active cell. To avoid adding a page break to the left, make sure a cell in Column A is selected before inserting the break.
Function: It is used to insert the function in worksheet. all function must start with = operator.
Name: It is used to assign the name to a cell or group of cell that can be used with in any function as an argument.
Hyper link: It is used to relate the any documents. (Ctrl+K) Cells – Use this command to insert a cell. A pop-up window allows you to move existing data down or to the right. You can also insert rows or columns with this window.
Comments: Add comments to a document to a document with insert comment.
1.Click where you want to insert the picture.
2.On the Insert menu, point to Picture, and then click From File.
3.Locate the picture you want to insert.
4.Double-click the picture you want to insert.
Diagram: -On the Drawing toolbar, click Diagram or Organizational Chart.
Click one of the following diagram types:
Do one or more of the following:
If you want to add text to an element in the diagram, right-click the element, click Edit Text, and then type the text. For cycle and target diagrams, you can only add text to the text placeholders that appear when you insert the diagram or diagram element.
If you want to add an element, click Insert Shape on the Diagram toolbar.
If you want to add a preset design scheme, click AutoFormat on the Diagram toolbar, and select a style from the Diagram Style Gallery.
Click outside the drawing when you are finished.
Object: Create and insert object in your current file
Select word you want to create link.
On the inset menu, click Hyperlink.
In the File name box, enter the name of the file you want to make hyperlink and then click ok.
Format Menu: Includes to change or set the font, font size, style, alignment to change or set the paragraph alignment, indents & spacing to add bullet & numbering border & shading, to set the color for the text before background, to set tabs or drop tabs & change the cases.
Cell: In this option to change the fonts, font styles (Regular, Italic, Bold, Bold italic), point size (1 to 1638), font color, underline & underline color, effects means strikethrough, superscript, subscript, hidden, small caps, all caps, outline, shadow, engrave, embosses etc. & you also set the character animation of sees types ( blinking background, lass Vegas, marching black ants, marching red ants, sparkle text).
Cells – Format the way a number is displayed; alignment of data in the cell(s), font (size, color, style, etc.), borders and colors for the selected cells, and you may also lock the contents of a cell here.
Row: Specify a row height, choose auto-fit, and hide or unhide the selected row.
Column: Specify a column width, choose auto-fit, hide or unhide the selected column, or choose the standard width for a column.
Sheet: Here you can rename the sheet if sheet 1 is not descriptive enough (and it's not), you can hide or unhide a sheet, or you can tile an image in the background of the entire sheet.
Auto Format: There are sixteen predestined formats to change the look of your spreadsheet. You may apply the format to the entire sheet or only to selected cells.
Worksheet: It is used to formatting the worksheet.
Auto format: It is used to select the style of the data.
Conditional format: It is used to format the cell according to condition like if the result is paas then the background color is green otherwise red.
Conditional Formatting: Applies formats to selected cells that meet specific criteria based on values or formulas you specify. Maximum Three conditions can be set in the range.
Styles: Styles are named groups of formatting command. Used to present your document with consistent formatting. When you apply a style, each format in the style is applied simultaneously to selected text.
Tool Menu: Includes checking grammar & spelling, to select auto summarize & auto correct facility, document protection, create envelope & tables or labels, macros, mail merging the document. It also includes the option button which includes all the main settings of MS-Word such as security, File location, compatibility etc.
Spelling and Grammar: Spell check is run using either the tools/ spelling menu selection or by clicking the spelling button. You can check an entire document or a selected word or passage.
Language: You can change the language or omit proofing for a document or for selected text in a document. If you change the language, you must have the dictionary of the language or a related to check the spelling.
Thesaurus: Electronic thesaurus lets you look up and select synonyms for words in the text of you WordStar document at any time during editing.
Share Workbook: Choose this option if you want several users to work on the data in the same workbook simultaneously. Make it available on your network and anyone with access can make changes.
Protection: You can password protect a single sheet of a workbook, or the entire workbook. One obvious application would be to protect a worksheet which you are using as your grade book.
Goal Seek: The Goal seeks Command adjust a Numeric entry that a formula depends on, to achieve a target result from the formula itself.
Scenarios: Creates and saves scenarios, which are sets of data you can use to view the results of what-if analyses. The scenario Manager is especially useful on a worksheet that is organized into distinct “Input” and “Output” areas.
Formula Auditing: The command in the Auditing submenu allows you to trace precedents and dependents on a worksheet. A precedent is a cell that is referenced in the formula of the active cell. A dependent is a cell containing a formula that refers to the active cell.
Macro: A macro is a series of commands that you can group together as a signal command to make everyday task simpler and easier. A macro is first recorded and saves with a name and a shortcut key. When you record the macro, you perform a series of steps. When you run the macro, these steps are performed exactly as you recorded them.
Auto correct: Control the capitalization of some text when you are inputting a string of artistic text or block of paragraph text. It also allows you to build quick shortcut words when entering repetitive information.
Customize: Create a custom toolbar
1.On the Tools menu, click Customize.
2.Click the Toolbars tab.
4.In the Toolbar name box, type the name you want.
5.Click the Commands tab.
6.Do one of the following:
Add a button to the toolbar
1.Click a category in the Categories box.
2.Drag the command you want from the Commands box to the displayed toolbar.
3.In the Categories box, click Built-in Menus.
4.Drag the menu you want from the Commands box to the displayed toolbar.
Sort: Sort command rearranges a range of worksheet data alphabetically or numeric in order the sort command is suited for rearranging the rows of a list or database, you can also rearrange columns of data. You can define as three keys of each sort operation in Ascending or Descending Order.
Filter: Filter allow you to work with selected rows of information in list, including a list that you have organized as a database. Excel gives you two effective ways to filter a list or database. The simpler of the two techniques is known as AutoFilter. When you select this feature, Excel provides drop-down lists at the top of every columns in your database.
Forms: Displays the column labels that correspond to each column in your list. In the box to right of the column labels, you can enter new records at the end of the list, edit existing records or find records in your list based on criteria you specify. If you click the restore button then restores edited fields in the displayed record removing your changes.
Sub-total: Calculate a subtotal for the columns you select and inserts subtotal rows in the current list. Microsoft Excel inserts a subtotal row at each change in the column you select and a Grand Total row at the bottom of the list.
At each change in: Select a label from the each change in box to specify the column the items or groups by which you want to subtotal values in other columns.
Use Function: You can also select a function to use for the subtotal calculation; Sum is the most common choice, but other function are also available, such as Count, Average, Max, Min, Product, Count Num etc.
Add subtotal to: Select one or more check boxes under add subtotal to specify the columns that contain value you want to subtotal.
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 limits on the valid entries.
Text to columns: Separates text in one cell on a worksheet into columns by using the convert text to columns wizard.
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: This Command is used to merge two or more files (Table) with functions.
Pivot Table: A Pivot Table is dynamic, customizable tool designed to help you rearrange, summarize and explore information from a database or list. The pivot table wizard provides a simple four-step graphical approach. First Choose the Microsoft Excel list or database, External Data source, Multiple Consolidate Range, Another Pivot Table.
Second select the pivot table in new sheet or select the cell on the worksheet, or type a cell reference in the existing worksheet box to specify the upper-left cell of the range on the worksheet where you want the pivot table to be placed.
Window Menu : Open new window just above the opened document to switch over from opened window to window by & splitting or to arrange the opened document window.
New Window: Create a duplicate window according to the current windows.
Arrange All: Arrange all windows on the screen in tile wise.
Hide: Hides the active workbook window or makes a hidden window visible. A hidden window remains open.
Unhide: Display hidden workbook windows.
Split/Remove Split: Split the window in the any point.
Freeze Panes: Freeze Panes Command Freezes the top pane, the left pane, or both, on the active worksheet. The Freeze Panes button is in the utility category.
Did you like this resource? Share it with your friends and show your love!
|Guest Author: Rushal 29 Jan 2013|
|Please send me a link that includes advanced portion of Excel like macros etc.|
Return to Article Index
Active MembersTodayLast 7 Daysmore...
Talk to Webmaster Tony John