Wednesday, July 30, 2014

customize keyboard shortcuts excel 2013

Is my old shortcuts can still be used?


Keyboard shortcuts that start with Ctrl still can be used in Microsoft Excel 2013, for example, Ctrl + C to copy to clipboard fixed, and Ctrl + V can still paste from the clipboard Shortcut key combination Ctrl.

key                                                    Description
Ctrl + PgDn  Move from one worksheet tab to another worksheet tabs, from left to right.
Ctrl + PgUp  Switch between worksheet tabs, from right to left.
Ctrl + Shift + & Applying to the frame boundary cells of choice.
Ctrl + Delete Shift_  frame boundaries of cells of choice.
Ctrl + Shift + ~  Apply the General number format.
Ctrl + Shift + $ Currency format Applying to two decimal places (negative numbers in parentheses).
Ctrl + Shift +% Apply the Percentage format with no decimal places.
Ctrl + Shift + ^ Applying the Scientific number format with two decimal places.
Ctrl + Shift + # Apply the Date format with the day, month, and year.
Ctrl + Shift + @ Apply Time format with the hour and minute, and AM or PM.
Ctrl + Shift +! Applying Numbers format with two decimal places, thousands separator, and minus sign (-) for negative values​​.
Ctrl + Shift +: Enters the current time.
Ctrl + Shift + "Copies the value from the cell above the active cell into the cell or formula bar.
Ctrl + Shift + Plus (+) Displays the Insert dialog box to insert blank cells.
Ctrl + Minus (-) Displays the Delete dialog box to delete cell option.
Ctrl +; Enter the current date.
Ctrl + `Toggle display cell values ​​and displaying formulas in the worksheet.
Ctrl + 'Copy the formula from the cell above the active cell into the cell or formula bar.
Ctrl + 1 Display Format dialog box Sel.
Ctrl + 2 Apply or remove bold formatting fonts.
Ctrl + 3 Apply or remove italic font formatting.
Ctrl + 4 Apply or remove the bottom line.
Ctrl + 5 Applying or removing graffiti.
Ctrl + 6 Alternating between hiding and displaying objects.
Ctrl + 8 Show or hide the frame symbol.
Ctrl + 9 Hides the selected rows.
Ctrl + 0 Hides the selected columns.
Ctrl + A Select the entire worksheet.
Function Arguments dialog box displays when the insertion point is located to the right of a function name in a formula.
Ctrl + Shift + A inserts the argument names and parentheses when the insertion point is located to the right of a function name in a formula.
Ctrl + B Apply or remove bold formatting.
Ctrl + C Copy the selected cells.
Using Ctrl + D Fill Down command to copy the content and format of the topmost cell of a selected range into the cells below it.
Ctrl + E Adding more value to the active field by using the data that surrounds the column.
Ctrl + F Displays the Find and Replace dialog box with the Find tab selected.
Shift + F5 also displays this tab, while Shift + F4 repeats the last Find action.
Ctrl + Shift + F opens the Format Cells dialog box with the Font tab selected.
Ctrl + G Displays the Open dialog box.
F5 also displays this dialog box.
Ctrl + H Displays the Find and Replace dialog box with the Replace tab selected.
Ctrl + I Apply or remove italic formatting.
Ctrl + K Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing hyperlinks.
Ctrl + L Displays the Create Table dialog box.
Ctrl + N Create a new blank workbook.
Ctrl + O Displays the Open dialog box to open or find a file.
Ctrl + Shift + O selects all cells that contain comments.
Ctrl + P Displays the Print tab in Microsoft Office Backstage view.
Ctrl + Shift + P opens the Format Cells dialog box with the Font tab selected.
Ctrl + Q Display Quick Analysis option for your data when you have a cell that contains the data that is selected.
Using Ctrl + R Fill Down command to copy the content and format of the leftmost cell of a selected range into the cells in her right.
Ctrl + S Saves the active file with the name, location, and file format at this time.
Ctrl + T Displays the Create Table dialog box.
Ctrl + U Apply or remove the bottom line.
Ctrl + Shift + U switches between expanding or shrinking formula bar.
Ctrl + V Pastes the clipboard content at the insertion point and replace any choice. Available only after you cut or copy an object, text, or cell content.
Ctrl + Alt + V displays the Paste Special dialog box. Available only after you cut or copy an object, text, or content of cells in a spreadsheet or other program.
Ctrl + W Closes the selected workbook window.
Ctrl + X Cuts the selected cells.
Ctrl + Y Repeats last command or action, if possible.
Using Ctrl + Z Undo command to reverse the last command or delete the last entry you typed.
F1 Displays the Excel Help task pane.
Ctrl + F1 displays or hides the ribbon.
Alt + F1 to make a chart of data that are embedded in the current range.
Alt + Shift + F1 inserts a new worksheet.
F2 Edit the active cell and position the insertion point at the end of the cell content. These keys also move the insertion point to
      the formula bar when editing in a cell is turned off.
Shift + F2 to add or edit a cell comment.
Ctrl + F2 displays the print preview area on the Print tab in Backstage view.
F3 Displays the Paste Name dialog box. Only available when the name specified in the workbook (Formulas tab, the group name
      is defined, Define Name).
Shift + F3 displays the Insert Function dialog box.
F4 Repeat last command or action, if possible.
Ctrl + F4 closes the selected workbook window.
Alt + F4 closes Excel.
F5 Displays the Open dialog box.
Ctrl + F5 restores the window size of the selected workbook window.
F6 Switch between the worksheet, ribbon, task pane, and Zoom controls. In a worksheet that has been split (View menu, Manage
      This Window, Freeze Panel, Split Window command), F6 includes the separator panel when switching between panels and
      ribbon area.
Shift + F6 switches between the worksheet, Zoom controls, task pane, and ribbon.
Ctrl + F6 switches to the next workbook window when more than one workbook window is open.
F7 Displays the Spelling dialog box to check spelling in the active worksheet or a range of options.
Ctrl + F7 perform the Move command on the workbook window when not maximized. Use the arrow keys to move the window,
                and when finished, press Enter, or Esc to cancel.
F8 Enable or disable expansion mode. In expansion mode, Option, Extended will appear on the status line, and the arrow keys
     to extend the selection.
Shift + F8 enables you to add non adjacent cell or range to a selection of cells by using the arrow keys.
Ctrl + F8 to run the Size command (on the Control menu for the workbook window) when a workbook is not maximized.
Alt + F8 displays the Macro dialog box to create, edit, or delete a macro.
F9 Calculates all worksheets in all open workbooks.
Shift + F9 calculates the active worksheet.
Ctrl + Alt + F9 calculates all worksheets in all open workbooks, regardless of whether there is any change since the last calculation.
Ctrl + Alt + Shift + F9 recheck dependent formulas, and then calculates all cells in all open workbooks, including cells
                                     not marked as need be calculated.
Ctrl + F9 minimizes a workbook window into an icon.
Enable or disable the F10 key tip. (Pressing Alt will run the same thing.)
Shift + F10 displays the shortcut menu for the selected item.
Alt + Shift + F10 displays the menu or message for Error Checking button.
Ctrl + F10 to maximize or restore the selected workbook window.
F11 Create chart of the data in the current range in a separate chart sheet.
Shift + F11 inserts a new worksheet.
Alt + F11 opens the Microsoft Visual Basic For Applications Editor, where you can create a macro by using Visual Basic
                 for Applications (VBA).
F12 Displays the Save As dialog box.
Tip Displaying Alt key (new shortcut) on the tape.
        For example,
Alt, W, P divert worksheet to display Page Layout.
Alt, W, L divert worksheet to Normal view.
Alt, W, I switch the worksheet to display Preview Page.
Arrows
Move one cell up, down, left, or right in a worksheet.
Ctrl + Arrow Keys to move to the edge of the current data region in a worksheet.
Shift + Arrow keys extend the cell selection by one cell.
Ctrl + Shift + Arrow Keys expand the choice of cells to the last non-empty cell in the column or the same row as the active cell,
                       or if the next cell is blank, extends the selection to the next empty cell.
Left Arrow or Right Arrow to select the tab to the left or right of the current selected band. When a submenu is open or selected, these arrow buttons switches between the main menu and the submenu. When the ribbon tab is selected, these keys navigate the tab buttons.
Down Arrow or Up Arrow choose the next or previous command when a menu or submenu is open. When the ribbon tab is selected, these keys navigate up or down the tab group.
In the dialog box, arrow keys move between options in an open drop-down list below, or among the various options in a group of options.
Down Arrow or Alt + Down Arrow opens a selected drop-down list below.
Backspace
Delete one character to the left of the formula bar.
Also remove the active cell content.
In cell editing mode, this button removes the character to the left of the insertion point.
Remove
Removing the cell content (data and formulas) from selected cells without affecting cell formats or comments.
In cell editing mode, this button removes the character to the right of the insertion point.
End
End End mode on or off. In End mode, you can press the arrow keys to move to the next non-empty cell in the column or the same row as the active cell. End mode automatically switches off after the arrow key is pressed. Make sure you press End again before pressing the next arrow. End mode is displayed on the status bar when active.
If the cell is empty, press End followed the arrow keys to move to the last cell in the row or column.
End also choose the last command on the menu when a menu or submenu is visible.
Ctrl + End to move to the last cell on the worksheet, the bottom line to that used in the rightmost column are used.
                  If the cursor is in the formula bar, Ctrl + End moves the cursor to the end of the text.
Ctrl + Shift + End expand the choice of cells to the last used cell on the worksheet (lower right corner) If the cursor is in the formula bar, Ctrl + Shift + End selects all the text in the formula bar from the cursor position to the end-this does not affect the high formula bar.
Enter
Ending a cell entry from the cell or formula bar, and select the cell below (by default).
In a data form, Enter to move to the first field in the next record.
Opens a selected menu (press F10 to activate the menu bar) or performs the action for the selected command.
In the dialog box, this button to run the action for the default command button in the dialog box (the button with a thick frame, often of an OK button).
Alt + Enter to start a new line in the same cell.
Ctrl + Enter fulfill the selected cell range with the current entry.
Shift + Enter end a cell entry and selects the cell above it.
Esc
Cancel the entry in the cell or formula bar.
Closes an open menu or submenu, dialog box, or message window.
This button also closes full screen mode when this mode is applied, and returns to the normal display mode to display the ribbon and status bar again.
Home
Move to the beginning of the line in the worksheet.
Move to the cell in the upper left corner of the window when Scroll Lock is activated.
Choosing the first command on the menu when a menu or submenu is visible.
Ctrl + Home to move to the beginning of the worksheet.
Ctrl + Shift + Home expand the choice of cell to cell at the beginning of the worksheet.
Page Down
Move one screen down in a worksheet.
Alt + Page Down to move the screen to the right of the worksheet.
Ctrl + Page Down to move to the next sheet in the workbook.
Ctrl + Shift + Page Down choosing the current and next sheet in the workbook.
Page Up
Move one screen up in a worksheet.
Alt + Page Up moves one screen to the left of the worksheet.
Ctrl + Page Up to move to the previous sheet in the workbook.
Ctrl + Shift + Page Up choose the current and previous sheet in the workbook.
blade Spacing
In the dialog box, this button to run the action for the selected button, or select or clear the check box.
Ctrl + Space bar selecting an entire column in a worksheet.
Shift + Space bar to choose one full row in a worksheet.
Ctrl + Shift + Space bar to select the entire worksheet.
Alt + Space bar displays the Control menu for the Excel window.
Tab
Move one cell to the right in the sheet work. between unlocked cells on a protected worksheet. Move to the next option or option group in a dialog box.
Shift + Tab to move to the previous cell in a worksheet or the previous option in a dialog box.
Ctrl + Tab to switch to the next tab in a dialog box.
Ctrl + Shift + Tab to switch to the previous tab in a dialog box.
Saturday, July 26, 2014

Microsoft Excel 2013 Formulas | Formula Examples and Explanations Microsoft Excel

Complete Microsoft Excel Formulas, These are:

                    VALUE
To change the value of the value to the value of the text, writing:
        = VALUE (text)

                   FIND
To produce a substring from a string position or a number that is sought, writing:
        = FIND (search text, the text, start number)

                    MID
  To take a particular character from a series of characters, writing:
        = MID (text, start position, number of characters)

                  REPLACE
To replace a substring with another substring in a string (a series of characters or character), writing:
        = REPLACE (...)

                 CONCATENATE
To combine strings into one sentence maximum of 30 strings,writing:
= CONCATENATE (text1, text2, ...)

                        MAX
To find the highest value from a set of data, writing:
        = MAX (number1, number2, ...)

                      STEDEV
To determine the standard deviation of a range,
writing: = STEDEV (number1, number2, ...)

                       VAR
To determine the value of a range varience,
writing: = VAR (number1, number2, ...)

                      LEFT
  to get the most left few letters. Text here refers to the text that will be taken. Value can be directly typed text a cell, but by adding two quotes (") at the beginning and at the end of the text. The formula is very simple.
= LEFT (text)

                    RIGHT
The opposite of LEFT, which took some characters starting from the right, ie:
= RIGHT (text)

                     LEN
To calculate the number of characters in a text. The formula is:
= LEN (text)

                    UPPER
Make all the text that is in the cell to uppercase all. writing:
= UPPER (text)

                    LOWER
  The opposite of UPPER, which is made ​​lowercase. writing:
= LOWER (text)

  PROPER
Making text to uppercase in the first character in each word. writing:
= PROPER (text)

  BAHTTEXT
        Changing numbers into text form. But only a few can be in the fox.
        writing:
        = TTEXT (...)

  FACT
        As the factorial of a number. writing:
        = FACT (...)
  GCD
        As the greatest common divisor. writing:
        = GCD (number1, number2, ...)

  LN
        As the natural logarithm of a number. writing:
        = LN (number)

  RAND
        As a random real number uniformly greater than or equal to 0 and less than 1.
A new real number at random will be shown each time the worksheet is calculated. writing:
        = RAND (...)

  CEILING
        Stating the amount of rounding, away from zero, to the nearest multiples. writing:
        = CEILING (...)

  SQRT
        To calculate the root of a positive number can not be negative. writing:
        = SQRT (...)

  INT (number)
        For rounding-down of an integer value / round. writing:
        = INT (number)

  CHAR
        To declare a character that has been determined using a numeric code. writing:
        syntax
        = CHAR (number)

   SUM:
To sum ​​up a set of data on the range, writing: = SUM (number1, number2, ..)

  AVERAGE:
To find the average value, writing:
= AVERAGE (number1, number2, ...)

   MIN:
To find the lowest value of a set of data, writing: = MAX (number1, number2, ...)

   HLOOKUP:
To read a table / range horizontally, writing:
= HLOOKUP (lookup_value, table_array, row_index_num, ...)

   / COUNTIF Function:
To count the number of cells within a range with a typical / specific criteria. Writing: = COUNTIF (range; criteria).

   AND:
  AND generate value TRUE if all arguments are TRUE valuable in testing.

   NOT:
  not the opposite of the AND, which returns TRUE if the conditions tested FALSE and FALSE if TRUE test conditions in
Writing: = NOT (arguments)
  LEN
To calculate the number of characters in a text. The formula is:
= LEN (text)

  UPPER
Make all the text that is in the cell to uppercase all. writing:
= UPPER (text)

  LOWER
  The opposite of UPPER, which is made ​​lowercase. writing:
= LOWER (text)

   PROPER
Making text to uppercase in the first character in each word. writing:
= PROPER (text)

   BAHTTEXT
        Changing numbers into text form. But only a few can be in the fox.
        writing:
        = TTEXT (...)

   FACT
        As the factorial of a number. writing:
        = FACT (...)

   GCD
        As the greatest common divisor. writing:
        = GCD (number1, number2, ...)

      LN
        As the natural logarithm of a number. writing:
        = LN (number)

    RAND
        As a random real number uniformly greater than or equal to 0 and less than 1. A new real number at random will be shown each time the worksheet is calculated. writing:
        = RAND (...)

   CEILING
        Stating the amount of rounding, away from zero, to the nearest multiples. writing:
        = CEILING (...)

    SQRT
        To calculate the root of a positive number can not be negative. writing:
        = SQRT (...)

   INT (number)
        For rounding-down of an integer value / round. writing:
        = INT (number)

   CHAR
        To declare a character that has been determined using a numeric code. writing:
        syntax
        = CHAR (number)

   SUM:
To sum ​​up a set of data on the range, writing: = SUM (number1, number2, ..)

  AVERAGE:
To find the average value, writing:
= AVERAGE (number1, number2, ...)

   MIN:
To find the lowest value of a set of data, writing: = MAX (number1, number2, ...)

   HLOOKUP:
To read a table / range horizontally, writing:
= HLOOKUP (lookup_value, table_array, row_index_num, ...)

   VLOOKUP:
To read the table / vertical range (VLOOKUP), writing:
= VLOOKUP (lookup_value, table_array, row_index_num, ...)

   / COUNTIF Function:
To count the number of cells within a range with a typical / specific criteria. Writing: = COUNTIF (range; criteria).

   AND:
  AND generate value TRUE if all arguments are TRUE valuable in testing.

   NOT:
  not the opposite of the AND, which returns TRUE if the conditions tested FALSE and FALSE if TRUE test conditions in
Writing: = NOT (arguments)