PowerPivot Free Add-on for Excel 2010

I would like to share with you a information on Microsoft Excel Add-on (supports only 2010).

PowerPivot is a free add-on for MS Excel 2010

How it is useful?

=> helps in increasing the data analysis capability of Excel
=> Increase the data holding capacity of Excel Sheet so we can store more data in single Excel sheet.

Features:

=> We can import data from Access, SQL Server also from other data base.
=> In Normal Excel pivot allow 1millions rows and data, In PowerPivot allow to use 100 millions of rows.
=> In Normal Excel we can import data only from single data sources whereas In PowerPivot we can import data from diverse
sources and combain these data into single model.
=> Save your data and perform analysis in the familiar Excel Environment and we can copy, move & re-use the data when we needed.
=> share your analysis online where your colleagues can continue to slice and dice your data.

for more details: http://www.powerpivot.com/
Video links: http://www.powerpivot.com/videos.

BIN2DEC

What Does It Do ?

This function converts a binary number to decimal. Negative numbers are represented using two's-complement notation.

Syntax =BIN2DEC(BinaryNumber) The binary number has a limit of ten characters.

Formatting No special formatting is needed.

CHAR

What Does It Do?

This function converts a normal number to the character it represent in the ANSI character set used by Windows.

Syntax

=CHAR(Number)

The Number must be between 1 and 255.

Formatting

The result will be a character with no special formatting.

Example

The following is a list of all 255 numbers and the characters they represent. Note that most Windows based program may not display some of the special characters, these will be displayed as a small box.

Note Number 32 does not show as it is the SPACEBAR character.

AVERAGE

What Does It Do ?

This function calculates the average from a list of numbers. If the cell is blank or contains text, the cell will not be used in the average calculation. If the cell contains zero 0, the cell will be included in the average calculation.

Syntax =AVERAGE(Range1,Range2,Range3... through to Range30)

Formatting No special formatting is needed.

Note To calculate the average of cells which contain text or blanks use =SUM() to get the total and then divide by the count of the entries using =COUNTA().

CHOOSE

What Does It Do?

This function picks from a list of options based upon an Index value given to by the user.

Syntax =CHOOSE(UserValue, Item1, Item2, Item3 through to Item29)

Formatting No special formatting is required.

Example The following table was used to calculate the medals for athletes taking part in a race. The Time for each athlete is entered. The =RANK() function calculates the finishing position of each athlete. The =CHOOSE() then allocates the correct medal. The =IF() has been used to filter out any positions above 3, as this would cause the error of #VALUE to appear, due to the fact the =CHOOSE() has only three items in it.

AND

What Does It Do?

This function tests two or more conditions to see if they are all true. It can be used to test that a series of numbers meet certain conditions. It can be used to test that a number or a date falls between an upper and lower limit. Normally the AND() function would be used in conjunction with a function such as =IF().

Syntax

=AND(Test1,Test2)

Note that there can be up to 30 possible tests.

Formatting

When used by itself it will show TRUE or FALSE.

Example 1

The following example shows a list of examination results. The teacher wants to find the pupils who scored above average in all three exams. The =AND() function has been used to test that each score is above the average. The result of TRUE is shown for pupils who have scored above average in all three exams.

=AND(C38>=AVERAGE($C$29:$C$38),D38>=AVERAGE($D$29:$D$38),E38>=AVERAGE($E$29:$E$38))

EXCEL SHORTCUTS

Data Entry / Copies selected cells / CTRL + C
Data Entry / Fill selected cell range with current entry / CTRL + ENTER
Data Entry / Copies value from the cell above / CTRL + SHIFT + '
Data Entry / Paste selected cells rows, column / CTRL + V
Data Entry / Copies formula from the cell above / CTRL+ '
Data Entry / Fill Down / CTRL+ D
Data Entry / Fill Right / CTRL+ R
Data Entry / Enters current time / CTRL+ SHIFT + COLON
Data Entry / Complete cell entry and select below cell / ENTER
Data Entry / Cancel a cell entry / ESC
Data Entry / Positions cursor at the end of the cell contents / F2
Data Entry / Pastes defined name into a formula / F3
Data Entry / Complete cell entry and select above cell / SHIFT + ENTER
Data Entry / Complete cell entry and select left cell / SHIFT + TAB
Data Entry / Complete cell entry and select right cell / TAB

Format / Start new line in same cell / ALT + ENTER

Format / Insert column / ALT + I , C

Format / Insert row / ALT + I , R
Format / Insert worksheet / ALT + SHIFT + F1
Format / Move or copy worksheet / ALT+ E, M
Format / Rename worksheet / ALT+ O, H, R
Format / Hide column / CTRL + 0
Format / Hide row / CTRL + 9
Format / Define a name / CTRL + F3
Format / Delete selected row or column / CTRL + MINUS SIGN
Format / Applies Number format / CTRL + SHIFT + 1
Format / Applies Time format / CTRL + SHIFT + 2
Format / Applies Date format / CTRL + SHIFT + 3
Format / Applies Currency format / CTRL + SHIFT + 4
Format / Applies Percentage format / CTRL + SHIFT + 5
Format / Applies Exponential number format / CTRL + SHIFT + 6
Format / Applies outline border to selected cells / CTRL + SHIFT + 7
Format / Removes outline border to selected cells / CTRL + SHIFT + MINUS SIGN
Format / Insert selected row / CTRL + SHIFT + PLUS SIGN
Format / Insert selected column / CTRL + SHIFT + PLUS SIGN
Format / Cut selected cells, rows, columns / CTRL + X
Format / Applies or removes bold formatting / CTRL+ 2
Format / Applies or removes italic formatting / CTRL+ 3
Format / Applies or removes underlining / CTRL+ 4
Format / Applies or removes strikethrough / CTRL+ 5
Format / Applies or removes bold formatting / CTRL+ B
Format / Applies or removes italic formatting / CTRL+ I
Format / Unhide selected hidden columns / CTRL+ SHIFT + 0
Format / Unhide selected hidden rows / CTRL+ SHIFT + 9
Format / Applies or removes underlining / CTRL+ U
Format / Delete comment / SHIFT + F10, M
Format / Insert worksheet / SHIFT + F11
Format / Insert or edit cell comment / SHIFT + F2

Function / Display Style dialog box / ALT + '
Function / Display drop down list / ALT + DOWN ARROW
Function / Delete worksheet / ALT + E, L
Function / Creates chart of data in current range / ALT + F1
Function / Opens Visual Basic (Macros) Editor / ALT + F11
Function / Displays Macro dialog box / ALT + F8
Function / Displays smart tag menu/message / ALT + SHIFT + F10
Function / Opens Microsoft Script Editor / ALT + SHIFT + F11
Function / Calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation / CTRL + ALT + F9
Function / Rechecks dependent formulas and calculates all cells in all open workbooks / CTRL + ALT + SHIFT + F9
Function / Closes and reopens current task pane / CTRL + F1
Function / Close selected workbook / CTRL + F4
Function / Inserts the argument names and parentheses when the insertion point is to the right of a function name in a formula / CTRL + SHIFT + A
Function / Create names / CTRL + SHIFT + F3
Function / Undo or Redo command to reverse or restore the last automatic correction when AutoCorrect Smart Tags are displayed / CTRL + SHIFT + Z
Function / Close selected workbook / CTRL + W
Function / Repeat last action / CTRL + Y
Function / Repeats last command or action / CTRL + Y
Function / Undo last action / CTRL + Z
Function / Displays Format Cells dialog box / CTRL+ 1
Function / Displays or hides the Standard toolbar / CTRL+ 7
Function / Displays or hides the outline symbols / CTRL+ 8
Function / Displays Microsoft Office Clipboard / CTRL+ C, C
Function / Enters current date / CTRL+ COLON
Function / Display the Windows Start menu / CTRL+ ESC
Function / Displays Find dialog box / CTRL+ F
Function / Displays Go To dialog box / CTRL+ G
Function / Displays Find/Replace dialog box / CTRL+ H
Function / Displays Insert/Edit Hyperlink dialog box / CTRL+ K
Function / Displays Create List dialog box / CTRL+ L
Function / Displays Delete dialog box / CTRL+ MINUS SIGN
Function / Creates new, blank file / CTRL+ N
Function / Displays Open dialog box / CTRL+ O
Function / Displays Print dialog box / CTRL+ P
Function / Saves active file / CTRL+ S
Function / Displays Insert dialog box / CTRL+ SHIFT + PLUS SIGN
Function / Displays Help task pane / F1
Function / Selects or closes menu bar / F10
Function / Creates chart of data in current range / F11
Function / Displays Save As dialog box / F12
Function / Repeat last action / F4
Function / Display Go To dialog box / F5
Function / Displays Spelling Check dialog box for active cell / F7
Function / Calculates all worksheets in all open workbooks / F9
Function / Calculates selected portion of formula and replaces the selected portion with the calculated value / F9 followed by ENTER (or followed by CTRL + SHIFT + ENTER for array formulas)
Function / Shortcut menu / SHIFT + F10
Function / Displays Insert Function dialog box / SHIFT + F3
Function / Repeat last Find action / SHIFT + F4
Function / Display Find dialog box / SHIFT + F5
Function / Calculates active worksheet / SHIFT + F9

Selection / Select visible cells in current selection / ALT + ;
Selection / Select array / CTRL + /
Selection / Select all cells directly referenced by formulas in selection / CTRL + [
Selection / Select cells that don't match formula or static value in active cell / CTRL + \
Selection / Select cells that contain formulas that directly reference active cell / CTRL + ]
Selection / Select populated cells / CTRL + A
Selection / Select all cells / CTRL + A, A
Selection / Select all cells directly or indirectly referenced by formulas in selection / CTRL + SHIFT + {
Selection / Select the cells that don't match formula or static value in active cell / CTRL + SHIFT + |
Selection / Select cells that contain formulas that directly or indirectly reference active cell / CTRL + SHIFT + }
Selection / Extend selection to last nonblank cell / CTRL + SHIFT + ARROW KEY
Selection / Extend selection to last used lower-right cell / CTRL + SHIFT + END
Selection / Extend selection to beginning of worksheet / CTRL + SHIFT + HOME
Selection / Select populated cells / CTRL + SHIFT + SPACE
Selection / Select all cells / CTRL + SHIFT + SPACE, SPACE
Selection / Select column / CTRL + SPACE
Selection / Selects current region around active cell / CTRL+ SHIFT + 8
Selection / Selects all cells that contain comments / CTRL+ SHIFT+ O
Selection / Select current and next or previous sheet / CTRL+ SHIFT+ PAGE UP, PAGE DOWN
Selection / Extend selection to last nonblank cell in the same column or row as active cell / END + SHIFT + ARROW KEY
Selection / Extend selection to last cell in current row / END + SHIFT + ENTER
Selection / Extend selection to last used cell on worksheet (lower-right corner) / END + SHIFT + HOME
Selection / Turn extend selection mode on or off / F8
Selection / Extend selection to the cell in lower-right corner of window / SCROLL LOCK + SHIFT + END
Selection / Extend selection to cell in the upper-left corner of window / SCROLL LOCK + SHIFT + HOME
Selection / Extend selection by one cell / SHIFT + ARROW KEY
Selection / Select only active cell of multiple selection / SHIFT + BACKSPACE
Selection / Add another range of cells to selection / SHIFT + F8
Selection / Extend selection to beginning of row / SHIFT + HOME
Selection / Extend selection down one screen / SHIFT + PAGE DOWN
Selection / Extend selection up one screen / SHIFT + PAGE UP
Selection / Select row / SHIFT + SPACE

View / Move one screen to right / ALT + PAGE DOWN
View / Move one screen to left / ALT + PAGE UP
View / Switch to previous program / ALT+ SHIFT+ TAB
View / Switch next program / ALT+ TAB
View / Move one character up, down, left, or right / Arrow keys
View / Alternate between hiding and displaying objects / CTRL + 6
View / Move to edge of current data region / CTRL + ARROW KEY
View / Scroll to display active cell / CTRL + BACKSPACE
View / Move bottom most last used cell / CTRL + END
View / Maximize window size of worksheet / CTRL + F10
View / Minimize window size of worksheet / CTRL + F5
View / Switches to next workbook window / CTRL + F6
View / Moves workbook window when not maximized / CTRL + F7
View / Sizes workbook when not maximized / CTRL + F8
View / Move to beginning of worksheet / CTRL + HOME
View / Move to beginning of worksheet / CTRL + HOME
View / Switch to previous workbook window / CTRL + SHIFT + F6
View / Alternates between displaying cell values and displaying formulas in the worksheet / CTRL+ `
View / Alternates between hiding objects, displaying objects, and displaying placeholders for objects / CTRL+ 6
View / Move to next worksheet / CTRL+ PAGE DOWN
View / Move to previous worksheet / CTRL+ PAGE UP
View / Switch to next pane in split worksheet / F6
View / Move to beginning of row / HOME
View / Move down one screen / PAGE DOWN
View / Move up one screen / PAGE UP
View / Switches to previous pane in split worksheet / SHIFT + F6

CONCATENATE

What Does It Do?

This function joins separate pieces of text into one item.

Syntax

=CONCATENATE(Text1,Text2,Text3...Text30) Up to thirty pieces of text can be joined.

Formatting

No special formatting is needed, the result will be shown as normal text.

Note: You can achieve the same result by using the "&" operator.