Excel Tricks and tips

1. Excel Tricks and tips Danny Puype Trainer danny@puype.com

3. Nav/Selection • Right click on sheet tabs + Ctrl PgUp/PgDn • Use range name for big selection with F5 • Make selections with Ctrl/End and arrows • Ctrl + * • Change Enter behavior • Combine Tab & Enter in a list • Insert Multiple new lines • Put macro on toolbar – quick access toolbar • Use of F5 for special selections • Double click border of cells to move cursor • Select columnar data with blanks

4. Copy/Cut - Paste • Paste special – special features like • Calculations – Transpose - Skip blanks • Quickly copy sheets, cells etc with Ctrl + drag • Quickly propagate a structure with group sheets • Use visible cells only with subtotals / filtered data • Move columns or cells between other columns or cells • Filling out data (numbers, dates…) with fixed intervals & series • Fill Blanks with F5 - Goto blanks and Ctrl-Enter • Ctrl-D to fill values fromrowjustabove • Double click format painter to copy format multiple times • Create custom lists • Ctrl+space: highlight col, then Ctrl + or Ctrl – • use ALT instead of / • /IR - /IC - Insert Row/Column • /EDR - /EDC - Delete Row/Column • /ORH - /OCH - Hide Row/Column • /ORU - /OCU - Unhide Row/Column

5. Formatting • Use styles and customize styles • Use table styles • Reset columns & row to autofit properly • Custom formats with 4 arguments • Alt + Enter to have text on multiple lines • Find cells formatted with Find and then reformat • Use of theme colors • Time format beyond 24h - [h]:mm:ss

6. Formulas • Nest formulas the easy way • Use simple references to avoid repetition • Statistics in status bar • Unknown operators like & and ^ • Calculating time • Set up 3D formula • Intersection operator: a space • F9 to see values behind formula • Double click formula to debug • Watch window • Auto-completed function names and use of tab • Creating dynamic ranges with offset • Range names and dynamic ranges with Tables • Use of wildcard * in formulas: =vlookup(”abc*”;table;col nr; 0) • Transform conditional outcomes with ++

7. Data • Autofilter by sample • Sort on colour • Use subtotals in filtered data & use of tables • Insert Currencies from 'Existing Connections' • Get rid of spaces with text to columns • Repeat rows when printing • Compare columns or rowswith F5 • Dynamic data with tables • Alt + Down arrow for a drop down list • Remove duplicates • Fill blancs in pivot tables • Simple reference to cells in pivot tables • Put Show All records on toolbar (<2007)

8. Charts • Make a custom chart • Right click on any of the chart areas • Create default or custom chart and call it with Alt-F1 • Add data to graphs with Ctrl-C and Ctrl-V • Move a chart and align it with cells with ALT • Quickly change markers in a line chartwithanyshapewith Ctrl-V • Makemicrochartswithrept() • Quick chartwithF11 or Alt-F1 on samesheet • Add a secondary axis

9. Various • Create button with print area • Text to speech • VBA code for a sheet: right click sheet tab and View Code • Pin oftenused files to recently files menu • Camera tool (eg to create micro chart) • Hide certain error/warning messages • Hide all columns and rows not in use • Remove icon on toolbar with Alt – drag (<2007)

10. Shortcuts • Insert Time / Date - Ctrl + ; / : • Use of F2 - F3 - F4 – F5 - F11 • Use edit clear all / format on QAT • Ctrl-pgup –pgdn to nav between sheets • Ctrl F1 removes ribbon • Ctrl-Shft +1 = Format Cells • Alt + = autosum • Ctrl+Alt+V = Paste Special • Ctrl when dragging fill handle – for dates: right mouse on fill handle • Ctrl-spacebar/Shft-spacebar : select full column/row

11. Links • http://www.2013.net/blog/index.php?/site/excel_links/