Sunday, 2 March 2014

Short Cuts 8

Ctrl + Down Arrow Moves to the last row in the current region


Ctrl + Left Arrow Moves to the first column in the current region


Ctrl + Right Arrow Moves to the last column in the current region


Ctrl + Page Up Moves to the previous worksheet in the workbook


Ctrl + Page Down Moves to the next worksheet in the workbook


Ctrl + Shift + Tab Moves to the previous open workbook or window


Scroll Lock + Arrow Keys Moves the workbook or window one cell the corresponding direction


Scroll Lock + End Moves to the last cell in the current window


Scroll Lock + Home Moves to the first cell in the current window


Scroll Lock + Page Down Moves you down one screen (current selection unchanged


Scroll Lock + Page Up Moves you up one screen (current selection unchanged)


Enter Moves from top to bottom within a selection


Tab Moves from left to right within a selection


Ctrl + . Moves clockwise to the next corner within a selection


Shift + Tab Moves from right to left within a selection (opposite direction to Tab)


Alt + Ctrl + Left Arrow Moves to the left between non adjacent cells in a selection


Alt + Ctrl + Right Arrow Moves to the right between non adjacent cells in a selection 

= Starts a Formula


Ctrl + ` Toggles between the value layer and the formula layer


Ctrl + Insert Copies the current selection to the clipboard


Ctrl + Shift + ( Unhides any hidden rows within the selection


Ctrl + Shift + ) Unhides any hidden columns within the selection


Ctrl + Shift + \ Select unequal cells


Alt Toggles the activation of the old Menu Bar


Alt + Shift + Left Arrow Displays the (Data > Group and Outline > UnGroup) dialog box


Alt + Shift + Right Arrow Displays the (Data > Group and Outline > Group) dialog box


Alt + Spacebar Activates the Control Box in the top left hand corner


End Toggles between switching End Mode on or off


Scroll Lock Toggles between switching Scroll Lock on or off


Shift + Insert Pastes the entry from the clipboard


Short Cuts 7

Ctrl + Shift + (1 or !) Applies the Comma separated format "#,##0.00" to the selection


Ctrl + Shift + (4 or $) Applies the Currency format "£#,##0.00" to the selection


Ctrl + Shift + (5 or %) Applies the Percentage format "0%" to the selection


Ctrl + Shift + (6 or ^) Applies the Exponential format "#,##E+02" to the selection


Ctrl + Shift + (# or ~) Applies the General format to the selection


Ctrl + (# or ~) Applies the Custom Date format "dd-mmm-yy" to the selection


Ctrl + Shift + (7 or &) Applies the outline border to the selection


Ctrl + Shift + (- or _ ) Removes all the borders from the selection 

Arrow Keys Moves to the next cell in that direction

Ctrl + Tab Moves to the next open workbook or window


Alt + Tab Moves to the next application open on your computer


Alt + Shift + Tab Moves to the previous application open on your computer


Enter Moves to the cell directly below


Tab Moves to the next cell on the right (or unprotected cell)


Home Moves to the first column in the current row


End + Arrow Keys Moves to the next non empty cell in that direction


End + Enter Moves to the last cell in the current row that is not blank


End + Home Moves to the last used cell on the active worksheet*


End + Home Moves to the last used cell on the active worksheet*


Page Down Moves to the next screen of rows down


Page Up Moves to the previous screen of rows up


Shift + Enter Moves to the cell directly above (opposite direction to Enter)


Shift + Tab Moves to the cell directly to the left (opposite direction to Tab)


Alt + Page Down Moves you one screen of columns to the right


Alt + Page Up Moves you one screen of columns to the left


Ctrl + Home Moves to cell "A1" on the active sheet


Ctrl + End Moves to the last used cell on the active worksheet*


Ctrl + Up Arrow Moves to the first row in the current region



Short Cuts 6

Ctrl + Shift + \ Selects the cells in a selected column that do not match the value in the active cell


Ctrl + / Selects the array containing the active cell ??


Alt + ; Selects the visible cells in the current selection


Ctrl + Shift + (8 or *) Selects the current region (surrounded by blank rows and columns)


Ctrl + * Selects the current region (using the * on the number keyboard)

Ctrl + [ Selects all the cells that are directly referred to by the formula in the active cell (precedents)


Ctrl + Shift + [ Selects all the cells that are directly (or indirectly) referred to by the formula in the active cell


Ctrl + ] Selects all the cells that directly refer to the active cell (dependents)


Ctrl + Shift + ] Selects all the cells that directly (or indirectly) refer to the active cell


Ctrl + Shift + Page Down Selects the active worksheet and the one after it


Ctrl + Shift + Page Up Selects the active worksheet and the one before it


Ctrl + Shift + Spacebar Selects all the objects on the worksheet when an object is selected or selects the whole 
worksheet


Ctrl + Spacebar Selects the current column


Shift + Arrow keys Selects the active cell and the cell in the given direction


Shift + Backspace Selects the active cell when multiple cells are selected


Shift + Spacebar Selects the current row


Ctrl + Shift + Arrow Key Extends the selection to the next cell adjacent to a blank cell in that direction


Ctrl + Shift + End Extends the selection to the last used cell on the worksheet


Ctrl + Shift + Home Extends the selection to the beginning of the worksheet


Shift + Arrow Keys Extends the selection by one cell in that direction


Shift + Home Extends the selection to the first column


Shift + Page Down Extends the selection down one screen


Shift + Page Up Extends the selection up one screen


End + Shift + Arrow Keys Extends the selection to the next non-blank cell in that direction


End + Shift + End Extends the selection to the last cell in the current row*


End + Shift + Home Extends the selection to last used cell on the worksheet


Alt + ' Displays the Style dialog box


Ctrl + Shift + ( ' or ) Applies the Time format "hh:mm" to the selection



Short Cuts 4

Ctrl + Shift + F Activates the Font Tab of the format cells


Ctrl + Shift + O Selects all the cells with comments


Ctrl + Shift + P Activates the Font Size tab of the format cells


Enter Enters the contents of the active cell and moves to the cell below (by default)


Shift + Enter Enters the contents of the active cell and moves to the cell above (by default)


Tab Enters the contents of the active cell and moves one cell to the right


Shift + Tab Enters the contents of the active cell and moves one cell to the left


Alt + = Enters the SUM() function (AutoSum) to sum the adjacent block of cells


Alt + 0128 Enters the euro symbol (€) (using Number keypad)


Alt + 0162 Enters the cent symbol (¢) (using Number keypad)


Alt + 0163 Enters the pound sign symbol (£) (using Number keypad)


Alt + Enter Enters a new line (or carriage return) into a cell


Ctrl + ' Enters the contents from the cell directly above into the active cell


Ctrl + Shift + 2 Enters the value from the cell directly above into the active cell

Ctrl + ; Enters the current date into the active cell


Ctrl + Shift + ; Enters the current time into the active cell


Ctrl + Shift + Enter Enters the formula as an Array Formula


Shift + Insert Enters the data from the clipboard


Alt + Down Arrow Displays the Pick From List drop-down list Esc Cancels the cell entry and restores the original 
contents


Delete Deletes the selection or one character to the right


Backspace Deletes the selection or one character to the left Shift + Delete Cuts the selection to the clipboard


Ctrl + Delete Deletes text to the end of the line Ctrl + - Displays the Delete dialog box


Ctrl + Shift + = Displays the Cells dialog box


Ctrl + \ Selects the cells in a selected row that do not match the value in the active cell



Short Cuts 3

Ctrl + 3 Toggles italics on the current selection


Ctrl + 4 Toggles underlining on the current selection


Ctrl + 5 Toggles the strikethrough of text on the current selection


Ctrl + 6 Toggles between hiding, displaying


Ctrl + 8 Toggles the display of Outline symbols on the active worksheet


Ctrl + 9 Hides the rows in the current selection (Format > Row > Hide)


Ctrl + Shift + 0 Unhides the columns in the current selection


Ctrl + Shift + 2 Enters the value from the cell directly above into the active cell


Ctrl + Shift + 8 Selects the current region (surrounded by blank rows and columns)


Ctrl + Shift + 9 Unhides the rows in the current selection


Ctrl + A Displays formula palette given a function name or selects the whole worksheet or current date


Ctrl + B Toggles bold on the current selection


Ctrl + C Copies the current selection to the clipboard


Ctrl + D Copies the first cell in the selection downwards


Ctrl + F Displays the Find dialog box


Ctrl + G Displays the GoTo dialog box


Ctrl + H Displays the Replace dialog box


Ctrl + I Toggles italics on the current selection


Ctrl + K Displays the Insert Hyperlink dialog box


Ctrl + L Displays the Create Table dialog box


Ctrl + N Creates a new workbook


Ctrl + O Displays the Open dialog box


Ctrl + P Displays the Print dialog box


Ctrl + R Copies the leftmost cell in the selection to the right


Ctrl + S Saves, Displays the Save As dialog box if a new workbook


Ctrl + U Toggles underlining on the current selection


Ctrl + V Pastes the entry from the clipboard


Ctrl + W Closes the active workbook or window


Ctrl + X Cuts the current selection to the clipboard


Ctrl + Y Repeats the last workbook action Ctrl + Z Undo the last workbook action

Short Cuts 2

Ctrl + F5 Restores the size of the active workbook or window


Ctrl + F6 Moves to the next open workbook or window


Ctrl + F7 Activates the Move window command


Ctrl + F8 Activates the Resize window command


Ctrl + F9 Minimises the size of the active workbook or window


Ctrl + F10 Maximises the size of the active workbook or window


Ctrl + F12 Displays the (File > Open) dialog box


Alt + F1 Creates a chart (on a chart sheet) using the highlighted range


Alt + F2 Displays the (File > Save As) dialog box


Alt + F4 Closes all the workbooks (saving first) and exits Excel (File > Exit)


Alt + F8 Displays the (Tools > Macro > Macros) dialog box


Alt + F11 Toggles between the Visual Basic Editor window and and the Excel window


Alt + Ctrl + F9 Calculates All cells on All worksheets in All workbooks


Alt + Shift + F1 Inserts a new worksheet (before the active sheet) into the active workbook (Insert > Worksheet)


Alt + Shift + F2 Displays the (File > Save As) dialog box


Alt + Shift + F4 Closes all the workbooks (saving first) and exits Excel (File > Exit)


Alt + Shift + F10 Displays the drop-down menu for the corresponding smart tag


Alt + Shift + F11 Activates the Microsoft Script Editor window


Ctrl + Shift + F3 Displays the (Insert > Name > Create) dialog box


Ctrl + Shift + F6 Moves to the previous open workbook or window


Ctrl + Shift + F10 Activates the Menu Bar or Shortcut Characters in 2007


Ctrl + Shift + F12 Displays the (File > Print) dialog box


Ctrl + 0 Hides the columns in the current selection


Ctrl + 1 Displays the formatting dialog box


Ctrl + 2 Toggles bold on the current selection



Thursday, 27 February 2014

Tips and Tricks for Excel

Excel Tip No. 1: Automatically SUM() with ALT + =  Quickly add an entire column or row by clicking in the first empty cell in the column. Then enter ALT + ‘=' (equals key) to add up the numbers in every cell above.

Excel Tip No. 2: Logic for Number Formatting Keyboard Shortcuts At times keyboard shortcuts seem random, but there is logic behind them. Let's break an example down. To format a number as a currency the shortcut is CRTL + SHIFT + 4. Both the SHIFT and 4 keys seem random, but they're intentionally used because SHIFT + 4 is the dollar sign ($). Therefore if we want to format as a currency, it's simply: CTRL + ‘$' (where the dollar sign is SHIFT + 4). The same is true for formatting a number as a percent.


Excel Tip No. 3: Display Formulas with CTRL + ` When you're troubleshooting misbehaving numbers first look at the formulas. Display the formula used in a cell by hitting just two keys: Ctrl + ` (known as the acute accent key) – this key is furthest to the left on the row with the number keys. When shifted it is the tilde (~).


Excel Tip No. 4: Jump to the Start or End of a Column Keyboard Shortcut You are thousands of rows deep into your data set and need to get to the first or last cell. Scrolling is OK but the quickest way is to use the keyboard shortcut CTRL + ↑ to jump to the top cell, or CTRL + ↓ to drop to the last cell before an empty cell. When you combine this shortcut with the SHIFT key, you'll select a continuous block of cells from your original starting point.


Excel Tip No. 5: Repeat a Formula to Multiple Cells Never type out the same formula over and over in new cells again. This trick populates all of the cells in a column with the same formula, but adjusts to use the data specific to each row. Create the formula you need in the first cell. Then move your cursor to the lower right corner of that cell and, when it turns into a plus sign, double click to copy that formula into the rest of the cells in that column. Each cell in the column will show the results of the formula using the data in that row.

Excel Tip No. 6: Add or Delete Columns Keyboard Shortcut Managing columns and rows in your preadsheet is an all-day task. Whether adding or deleting, you can save a little time when you use this keyboard shortcut. CTRL + ‘-‘ (minus key) will delete the column your cursor is in and CTRL + SHIFT + ‘=' (equal key) will add a new column. From an earlier tip, think about CTRL + ‘+' (plus sign).

Excel Tip No. 7: Adjust Width of One or Multiple Columns It's easy to adjust a column to the width of its content and get rid of those useless ##### entries. Click on the column's header, move your cursor to the right side of the header and double click when it turns into a plus sign.

Excel Tip No. 8: Copy a Pattern of Numbers or Even Dates Another amazing feature built into Excel is its ability to recognize a pattern in your data,and allow you to automatically copy it to other cells. Simply enter information in two rows which establish the pattern, highlight those rows and drag down for as many cells as you want to populate. This works with numbers, days of the week or months!

Excel Tip No. 9: Tab Between Worksheets Jumping from worksheet to worksheet doesn't mean you have to move your hand off the keyboard with this cool shortcut. To change to the next worksheet to the right enter CTRL + PGDN. And conversely change to the worksheet to the left by entering CTRL + PGUP.

Excel Tip No. 10: Double Click Format Painter Format Painter is a great tool which lets you duplicate a format in other cells with no more effort than a mouse click. Many Excel users (Outlook, Word and PowerPoint too) use this handy feature, but did you know you can double-click Format Painter to copy the format into multiple cells? It's quite a time-saver.