rowdy
Uploaded by
11 SLIDES
270 VUES
120LIKES

Essential Excel Tips and Tricks for Efficient Data Management

DESCRIPTION

Master Excel with these fundamental tips and tricks designed to boost your productivity. Learn how to navigate sheets effortlessly using Ctrl + PgUp/PgDn, create dynamic selections with named ranges, and efficiently manage your data with shortcuts. Enhance your workflow by utilizing the Quick Access Toolbar for macros, customizing your formatting, and effectively using formulas and functions. Discover how to handle charts, pivot tables, and automate tasks with VBA code, ensuring you get the most out of Excel’s powerful capabilities.

1 / 11

Télécharger la présentation

Essential Excel Tips and Tricks for Efficient Data Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 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

  3. 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

  4. 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

  5. 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 ++

  6. 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)

  7. 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

  8. 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)

  9. 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

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

More Related