Key Takeaways

Editing Takeaways

  • Learn Excel's keyboard shortcuts to enhance your productivity when editing data. Essential shortcuts include:
    • Cut (CTRL+X)
    • Copy (CTRL+C)
    • Paste (CTRL+V)
    • Paste Special (CTRL+ALT+V)
    • Undo (CTRL+Z)
    • Redo (CTRL+Y)
    • Shortcuts for navigating and selecting cells (CTRL+ARROW, SHIFT+ARROW, CTRL+SHIFT+ARROW, etc.)
  • Use data validation to help maintain data consistency within your files

Formatting Takeaways

  • Learning Excel's shortcuts for formatting can also significantly enhance your efficiency. Key shortcuts include those for:
    • Highlight cells (ALT, H, H, ...)
    • Add cell borders (ALT, H, B, ...)
    • Change font characteristics (ALT, H, F, ...)
  • Get your data in a tabular format, and use tables - this cannot be overemphasized! The benefits of using tables are numerous and significant
  • When necessary use colors to help organize your data
  • Do not overuse formatting; keep your files simple and clean by only formatting what is necessary
  • Avoid merging cells!
  • Minimize the use of conditional formatting, particularly in ranges that need to be sorted, filtered, etc.
  • Recognize how your data are formatted, including:
    • Numbers formatted as text
    • Not recognizing the formatting used can really mess up formula results, if you are not careful!
    • The presence or absence of blank spaces (which could impact certain formulas, such as COUNTBLANK, LEN, etc.)

Formulas (and Functions) Takeaways

  • Know how to use absolute (e.g. $A$1), mixed (e.g. A$1 and $A1), and relative (e.g. A1) cell references
  • Minimize hard-coding in formulas and use cell referencing instead
    • This is key to having flexible formulas!
  • Try to construct formulas that are as simple as possible
    • This requires knowing and leveraging the best functions to use
  • When using tables:
    • Use a single formula for each field (so that only one formula needs to be written for that field)
    • Use structured referencing (=PRODUCT([@Balance],[@Rate])) rather than cell references (=PRODUCT(B4, C4)) for better clarity

Analysis Takeaways

  • Mentally separate your raw data (inputs) from your analysis (outputs) - do not try to force them to be one concept!
  • If PivotTables can be used to analyze your data, use them instead of trying to create ad-hoc analyses
    • While ad-hoc analyzes are more flexible versus PivotTables, they are much more error prone (you will have to worry more about double-checking your calculations)
  • Once again, having your raw data in tabular formats in tables will make your analyses much easier
  • Ensure that the results of your analyses are clear and understandable to the end consumer

Document Design Takeaways

  • Keep tables and PivotTables in separate sheets so they can change dimensions (rows and columns) without issues
  • If you need additional information in a sheet that contains a table or PivotTable, place that information at the top of the sheet (above the table or PivotTable)
  • Clearly label your data (including tables and PivotTables), once again keeping in mind the end consumer

Other Takeaways

  • Use Excel only when it is necessary to do so!
    • If you are doing all of your work in Excel, there may be better alternatives that can handle your data management needs
    • Typically, some sort of database is used as your key data repository, and Excel is only used in areas that your other systems cannot handle well
    • Excel is best used when you need a lot of flexibility in examining data (e.g. sensitivity and scenario analyses)
  • Use VBA only when it is necessary (i.e. for repetitive and predictable tasks)
    • Use of VBA is not necessarily a sign of being sophisticated in Excel
    • Rather, it could mean that you are not leveraging Excel's existing functionalities optimally, or that Excel is not the best tool to use for the task at hand

All Shortcuts

  • A list of all shortcuts used throught this course is downloadable (above)