Microsoft Office Excel (.xls, .xlsx)
Microsoft Excel is a spreadsheet developed by Microsoft. This application is available in Microsoft Office Software.
It has two types of Extension .xls and .xlsx. In old version, up to 2003 is .xls and now is .xlsx up to 2013.
An excel file is called Workbook, one workbook have many worksheet. Sheet also called electronic spread sheet (Maximum sheet 256) and there have many cells, which is combined by column and row.
Electronic Spreadsheet: An electronic spreadsheet application accepts data values and relationships between the data values in a tabular format consisting of rows and columns. It allows user to perform calculation on these values. If any information is changed the worksheet program automatically recalculates all results.
Excel specifications and limits:
- First Column Name: A
- First Row Number: 1
- Last Column Name: XFD
- Last Row Number: 1048576
- First Cell Name: A1
- Last Cell Name: XFD1048576
- Total Column Number: A to XFD=16384
- Total Row Number: 1048576
- Total Cell Number: XFD1048576=16384X1048576=17179869184
Cell name like “A1”, ‘A’ is column and ‘1’ is row number, is used in a formula, then cell name/address is referred as cell reference.
Cell Reference: A reference that identifies a cell or a range of a cells on a worksheet that user want to use in a formula. There are three types of cell reference are as follows.
1. Relative Cell Reference: When user copy formula from one cell to another cell, the cell address is changed accordingly. For example:-
2. Mixed Cell Reference: For fixed a column or row in a formula user should to fixed ($) column or row. When user copy formula from one cell to another cell, then one part of cell is fixed and other is relative. For example:-
3. Absolute Cell Reference: For fixed a cell value in a formula user should to fixed ($) column as well as row. When user copy formula from one cell to another cell, then cell address will not be change accordingly. For example:-
To create an excel file: Goto Computer->Local Disk (D:) or Desktop
Symbol refer as
|$||Use to fixed column or row||$A$1|
- ##### :: Column is not wide enough to display the numbers in the cell. Solution is Increase the cell width or height.
- #DIV/0! :: Trying to divide a value by zero (0), this is mathematically impossible. Solution is to change the value where is zero (0).
- #VALUE! :: Something in the formula is not a value and therefore a calculation can’t be made. Solution is to correct the value where is present as text.
- #NAME? :: Text is found in a formula that can’t be matched to either a legitimate function or range name. Solution is to correct the function spelling in formula.
Sheet: Insert, Move or Copy, Duplicate, Delete, Rename, Hide, Unhide, Tab Color, Protect Sheet.
File: Save, Save As, Print, Print Preview.
Save As: Excel 97-2003 Workbook (Low version)
Save As: PDF
Column or Row: Hide, Unhide, Resize, Insert, Delete
Home: Border Color, Text Orientation, Wrap Text, Shrink to fit, Merge & Center, Format Cell (Number-000, 0.00, #,##0.00, “Rs. ”0.00“/-”, Date-mm/dd/yy, dd/mm/yy, dd/mmm/yy, Text), Conditional Formatting (format cell color Red, Bold and fill Blue color where Number greater than equal to 75 or Duplicate, Data bars, Color Scales, Icon Sets P or O, Manage Rules, Cell Styles,
Insert: PivotTable, Shapes, Screenshot, Chart – Column, Pie, Line, etc. (Switch Row/Column, Select Data, Chart Layout, Move Chart, Chart Title, Axis Titles, Legend, Data Labels, Data Tables, etc.), Sparklines, Hyperlink, Header & Footer, Symbol.
Page Layout: Page Setup (Margins, Orientation, Size, Print Titles, etc.), Scale, Gridlines
Formulas: Defined Names, Show Formulas, Error Checking
Function: SUM(), AVERAGE(), PRODUCT(), IF(), IF(AND(), IF(OR(), INDEX(), COUNT(), COUNTA(), LOWER(), UPPER(), PROPER(), MATCH(), MIN(), MAX(), TODAY(), DATE(), TIME(), NOW(), MOD(), COUNTIF(), COUNTIFS(), SUMIF(), AVERAGEIF(), SUBTOTAL(), SUMPRODUCT()
Data: Sort, Auto Filter, Advance Filter, Text to Columns, Remove duplicates, Data Validation (List, Error message), Goal Seek, Scenario Manager, Subtotal,
Review: New Comment, Edit Comment, Delete Comment, Protect Sheet, Allow Users to Edit Ranges
View: Normal, Page Layout, Page Break Preview, On/Off (Ruler, Formula Bar, Gridlines), Freeze Panes, Macros,
Comment: A comment is a note that attach to a cell and separate from other cell content. Comments are useful as remainders.
Filtering Record: Filtering is a quick and easy way to find and work with a subset of data in a range. It displays only the rows that meet the criteria that user specify for a column. There are two types of filtering: a)Auto Filter and b)Advance Filter.
Chart: Chart is a Pictorial representation of data in the worksheet. It give user to represent data in the most effect manner by using any type of chart. There are 11 type of chart in Excel 2007.
Goal Seek: Goal Seek is the part of a suite of commands sometimes called what-if analysis tools. Microsoft Excel use the value in one specific cell until a formula that’s dependent on that cell for return the result which user want.