excel tutorial

35 Excel Tips That Could Save You from Working All Night :


INTRODUCTORY NOTE :


35 EXCEL TIPS THAT COULD SAVE YOU FROM WORKING ALL NIGHT :


1. SPLIT WINDOWS AND FREEZE PANES :


2. HIDE AND UNHIDE COMMAND :


3. MOVING AROUND A SPREADSHEET WITH CTRL, SHIFT, AND ARROW KEYS :


4. NAME CELLS/RANGES :


5. SORT COMMAND :


5. SORT COMMAND (CONTINUED) :


6. TOGGLING AMONG RELATIONAL AND ABSOLUTE REFERENCES :


7. FILL DOWN AND FILL RIGHT COMMANDS :


8. IF FUNCTION : Cell shows 0 if condition is false IF(Comparison,TrueAction,””) ==> Cell shows blank if condition is false Create a “Mumbai” variable 1 if the store is in Mumbai 0 if the store is in other places 8. IF FUNCTION How you use this feature Exercise Why you need to know this]]>


9. AND AND OR FUNCTIONS :


10. SUM AND SUMIF FUNCTIONS :


11. SUBTOTALS AND TOTALS :


12. SUMPRODUCT FUNCTION :


13. NPV FUNCTION :


14. COUNT FUNCTIONS : count the number of cells containing numbers COUNTA(Range1,Range2,Value1,...) ==> count the number of non-empty cells COUNTBLANK(Range) ==> count the number of empty cells in the range COUNTIF(Range,”Criteria”) ==> count the number of cells in the Range containing the Criteria. NOTE: The “” signs must be used for the Criteria value Calculate the number of KFC stores in the dataset 14. COUNT FUNCTIONS How you use this feature Exercise Why you need to know this]]>


15. ROUND, ROUNDUP AND ROUNDDOWN FUNCTIONS : Round the number (or cell) to the specified number of digits If Digit = 0, then Number is rounded to nearest integer If Digit > 0, then Number is rounded to the specified number of decimal places If Digit < 0, then Number is rounded to the specified number of digits left of the decimal place ROUNDDOWN(Number,Digits) and ROUNDUP(Number,Digits) work the same way as ROUND, but the direction of rounding is specified by the function Calculate a rounded Avg Sale/Ticket variable, rounding to the nearest 10 Won 15. ROUND, ROUNDUP AND ROUNDDOWN FUNCTIONS How you use this feature Exercise Why you need to know this]]>


16. VLOOKUP AND HLOOKUP FUNCTIONS :


16. VLOOKUP AND HLOOKUP FUNCTIONS (CONTINUED) : look for a value in the row specified by SearchValue and the column specified by ColumnNumber SearchValue indicates the “match key” (i.e., find the row that contains the SearchValue in the first column) Range specifies the cells containing the data ColumnNumber specifies the column that contains the data element you want Error determines what happens when Excel does not find the exact SearchValue you want. FALSE leads Excel to display a #N/A when an exact match cannot be found. TRUE leads Excel to display the next smaller value than SearchValue HLOOKUP(SearchValue,Range,RowNumber,Error) ==> look for a value in the column specified by SearchValue and the row specified by RowNumber NOTE: The 1st column of data must be sorted in ascending order when using VLOOKUP, and the 1st row of data must be sorted if using HLOOKUP 16. VLOOKUP AND HLOOKUP FUNCTIONS (CONTINUED) How you use this feature]]>


16. VLOOKUP AND HLOOKUP FUNCTIONS (CONTINUED) :


16. VLOOKUP AND HLOOKUP FUNCTIONS (CONTINUED) :


17. INSERT FUNCTION COMMAND :


18. PASTE SPECIAL COMMAND :


19. AUDITING FEATURES :


20. GOAL SEEK ADD-IN :


21. SOLVER ADD-IN :


21. SOLVER ADD-IN (CONTINUED) :


22. DATA TABLES COMMAND :


22. DATA TABLES COMMAND (CONTINUED) :


23. SCENARIOS ADD-IN :


23. SCENARIOS ADD-IN (SIMPLE EXAMPLE) :


24. PIVOT TABLES :


24. PIVOT TABLES (CONTINUED) :


25. PROTECTING CELLS AND WORKSHEETS :


26. EDITING MULTIPLE WORKSHEETS SIMULTANEOUSLY :


27. CONDITIONAL FORMATTING :


28. AUTOFILTER COMMAND :


29. CUSTOMIZE TOOL BARS :


29. CUSTOMIZING YOUR TOOLBAR (CONTINUED) :


30. CHANGING DEFAULT WORKBOOK :


31. GROUP/UNGROUP PARTS OF SPREADSHEETS :


32. SWITCH OFF THE MICROSOFT ACTORS :


33. CLEAN UP TEXT :


33. CLEAN UP TEXT (CONTINUED) :


33. CLEAN UP TEXT (CONTINUED) :


34. KEYBOARD SHORTCUTS :


34. KEYBOARD SHORTCUTS (CONTINUED) :


34. KEYBOARD SHORTCUTS (CONTINUED) :


34. KEYBOARD SHORTCUTS (CONTINUED) :


34. KEYBOARD SHORTCUTS (CONTINUED) :


34. KEYBOARD SHORTCUTS (CONTINUED) :


35. FINAL THOUGHTS :