Excel – All You Need To Know

Excel – All You Need To Know

What do you need to know? What gets actually used at work, in college, and in research? What can you be tested for?

  1. Absolute and Relative cell references.
  2. Using keyboard shortcuts. Windows. Not Mac. (Cntrl Shift Up/Down, Cntrl D + Cntrl R, etc).
  3. IF, Nested IF function. Alternative – combining AND, OR, NOT functions.
  4. LOOKUP functions (LookUp, VLookUp, HLookUp, XLookUp).
  5. Alternative to lookup functions – Index Match.
  6. SUMIF, COUNTIF, MAX, MIN, SUMPRODUCT, etc.
  7. STDEV, CORREL, SLOPE, etc.
  8. Time value of money functions (FV, PV, PMT, etc) – You don’t have to use these if you prefer to apply the underlying formula directly.
  9. Pivot Tables.
  10. Freeze Panes.
  11. Remove Duplicates.
  12. Copy Paste (as values, formulas, format and formulas both (twice, one after the other).
  13. Solver.
  14. Goal Seek. What-if analysis. Data validation. Buttons.
  15. Data Analysis Tool Pack (Regression, Histogram, Correlation).
  16. Googling VBA code (Yes, you read that right. Available online. Copy paste, and adjust). You can also use the “Record Macro” button. Use VBA only when necessary to run iterative procedures that basic excel functions can’t take care of. You don’t need macros corrupting, bloating, and crashing your files).
  17. Conditional formatting, sort, filter (remove blanks or #NA), consistent formatting (alignment, font, data type, color).
  18. Charting and chart formatting.
  19. String manipulation (Concatenate, left, right, mid) and Date Functions (Workday).
  20. Working with connections (API, SQL Database).
  21. Hygiene (comments, naming sheets, labels, naming cells, and ranges).
  22. Printing file into pdf.
  23. Hiding sheets, cells. Password protection.
  24. Evaluate formula button.

Leave a Reply

Your email address will not be published.

Close
Menu
Social profiles