Saturday, July 7, 2012

Excelsheet for Beginners Day 2 (two)

 Next we will learn Creating Cell Data -Day 2

Making Cell Entries
Excel recognizes only two types of cell entries: text (label) and number (or value).

Numeric cell entries are those that consist solely of numbers or calculable formulas.

Text entries are those that consist of all letters or a combination of letters, numbers, and punctuation on which Excel can perform no sort of calculation.

Excel Worksheet automatically left aligns all text entries and right-aligns all numeric ones, you can often tell immediately how your entry has been classified by noting how it’s aligned in its cell.

Entering data in a cell range
To make the same entry in several different cells in the same worksheet, select all the cells and cell ranges and then press Ctrl+Enter to complete the entry you make in the active cell and simultaneously insert it into all the other selected cells.

Pasting Data
The cut, copy, and paste features are used by virtually everyone who uses Excel. However, Excel offers additional advanced techniques for pasting that a great many Excel users are unaware of or rarely use, even though they allow the user to do some very powerful data manipulation.

Using the Paste Special feature, you copy values using various options without copying formula from the cells.

Filling in a data series with the Fill handle
The tiny black square in the lower-right corner of the cell cursor is known as the Fill handle. The Fill handle is your key to the AutoFill feature that makes it easy to fill in a continuous range with the same entry or with data series.

To create a sequential series that increments by one unit (day, hour, month, number), you enter the first entry in the series in a blank cell and then drag the Fill handle in the direction you want the series to appear (down or to the right are the most common directions).

To create series that increment by other units (every other day, every third number, every fourth hour), you enter the first two entries in the series (that serves as an example of the increment to be used) in two adjacent blank cells and then, after selecting them both, drag the Fill handle in the appropriate

Applying Auto Fill for a Data Series
You can quickly fill adjacent cells with data that continues a formula or a series of numbers, days, or dates, either manually from the Fill menu, or automatically by dragging the fill handle. When copying or filling data by using the Fill menu commands, you can set specific options for the pattern of the data sequence you want to create.

Go to Edit menu > fill menu > Series option> click AutoFill (day, weekdays, months, years.)

Copying a formula with the Fill handle
AutoFill is not only useful for filling in a data series or copying a data entry into a continuous cell range, but also for copying a formula across a row or down a column of a data table. When you copy a formula, Excel automatically adjusts the column and row references.

Excel automatically uses the relative column and row cell references in all formulas you create.
If you ever need to override/avoid this so that all or part of a cell reference is not adjusted in the copied formulas, you enter a $ (dollar sign) (it locks reference cell) before the cell’s column letter or row number (you can have Excel do it for you by pressing F4 while building the formula on the Formula bar).

Formulas are entries that have an equation that calculates the value to display. When creating any formula, Excel always follows the mathematical(algebraic) order of operations.

Formulas OR Functions always BEGIN with an equal sign ( = ) , in a formula, constants(numbers like 1,2,3), functions and cell reference may be used.

when making/evaluating a formula, the order is/should as follow (from highest to lowest priority):
1.parenthesis ()
2.Negation(ex. -1)
3.Percentage (%)
4.Exponentiation (^ 'caret sign' is symbol for square,its written as 2^2)
5.Multiplication and Division (* for multiplication and / for division)
6.Addition and Subtraction (+ for addition and - for subtraction)

Probably the most popular function in any spreadsheet is the 'SUM' function.
The 'Sum' function takes all of the values in each of the specified cells and totals their values to give their total value.
=SUM(first value, second value, etc)

Formula Condition:
Text cells can not be added to a number and will produce an error.

The 'Average' function finds the average of the specified data.
=Average (first value, second value, etc.)

The 'Max' (which stand for Maximum) Function, will return the largest (Maximum / Highest) value in the selected range of cells.

'Min' (which stands for minimum) Function will return the smallest (Minimum / Lowest) value in the selected range of cells.
Conditions for Max-Min Functions:
Blank entries/cells are not included in the calculations of the Max/Min Function.
Text entries in cells are not included in the calculations of the Max/Min Function.

The 'IF' function will check the logical condition of a statement and return one value if true and a different value if false.

 The formula condition is:    =IF (condition, value-if-true, value-if-false)

value returned may be either a number or text,if value returned is text, it must be in quotes like this "HELLO".

End of day 2.. to continue...

Wednesday, July 4, 2012

Excelsheet for Beginners Day 1 (One)

MS Excel has been referred to as the world’s most popular database program because you can store vast amounts of data within a single workbook or worksheet. This MSEXCEL is meant much more than just crunching numbers.

This was not, however, the purpose for which Excel sheet was created. By using the many functions built in to Excel, you can build formulas that perform complex data calculations.

For millions of users the world over, Excel is also their number-one interface to the corporate database,as well as their premier charting program. Given Excel’s versatility, it should come as no surprise that mastering the basics of the program, is no small task.

Opening a New Workbook
Each time you launch MS Excel (using any method other than double-clicking an Excel workbook file icon), a new workbook containing three blank worksheets opens. You can build your new spreadsheet in this workbook, using any of its sheet pages.

The blank workbook that opens with Excel is given a temporary filename such as Book1, Book2, and so on, that appears after the application’s name on the program window’s title bar.

When Excel opens a blank workbook, the new workbook follows the Blank Workbook template (which controls the formatting applied to all its blank cells). You can also open new workbooks from other, specialized templates or from a workbook that you’ve already created.

Excelsheet workbook

Cell: A rectangle within a workbook or worksheet, it may contain text values or formulas that return a value.

Formula: A equation that performs various operations typically mathematical calculations within a worksheet.

Worksheet: A 2-D page of cells (256 columns wide and 65,536 rows long) that is contained within a workbook.

Workbook: A file that contains one or more worksheets.

Q. How do you create templates out of your own Excel workbook files?
A. Build a spreadsheet in a new or existing workbook file. To this spreadsheet add all the stock text and data, calculating formulas, and formatting required in all the files you will generate from its ensuing template and then save this file with the Office Button | Save As command.

In the Save As dialog box, select Excel Template (*.xltx) in the Save As Type drop-down list box and edit the dummy filename (without removing the .xltx filename extension) before you click the Save button.

To do open work/learn Excel, click Office Button New or press Ctrl+N or Alt+FN. Excel opens the New Workbook, single workbook is called as Excel Sheet, Many Excelsheet are called Excel Workbook.

To Navigate Through a Worksheet

To move by one cell
Press the Up Arrow key to move one cell up.
Press the Down Arrow key to move one cell down.
Press the Left Arrow key or the Tab key to move one cell to the right.
Press the Right Arrow key to move one cell to the left.

To move by one screen (screen resolution its 1280x800)(moving 1280x800 UP/Down fast)
Press Page Up or Page Down to move up or down.
Press Alt+Page Up or Alt+Page Down to move to the left or right.

To move to the edge of the current data entered region
Press Ctrl+Up Arrow to go extreme up(Starting of the data)
Ctrl+Down Arrow to go extreme down(end of the data)
Ctrl+Left Arrow, to go extreme left(Starting of the data)
Ctrl+Right go extreme right(end of the data)

To move to the extreme far end cells
Press End, and then press the Up Arrow, Down Arrow, Left Arrow, or Right Arrow key.

To move to the beginning of the current row
Press Home.

To move to the beginning of a worksheet
Press Ctrl+Home.

To move to the last populated cell on a worksheet
Press Ctrl+End.

Moving to a different sheet in the workbook

The easy way to select a new worksheet is to click its sheet tab, although you can also use the shortcut keys Ctrl+PageDown to select the next sheet and Ctrl+Page Up to select the previous sheet.

Selecting Commands on the Office Menu
Press the Esc (Escape) key on your keyboard to close the Save As dialog box.
click Office Button, Exit Excel or press Alt+FX to close the program.

Selecting Commands from the Ribbon
If you prefer selecting Excel commands from the keyboard, you’ll probably want to
memorize the following access keys for selecting the seven tabs:
Home tab: Alt+H
Insert tab: Alt+N
Page Layout tab: Alt+P
Formulas tab: Alt+M
Data tab: Alt+A
Review tab: Alt+R
View tab: Alt+W

End of  Day 1 (One) .. to continue...