Excel-Basic-Formula-and-Shortcut-Keys (1).pptx

Short cuts for excel

1. Excel Basic Formula

2. IF function =IF( logical_test , value_if_true , value_if_false ) Conditional logic. Example: =IF(A1>10, "Pass", "Fail")

3. IFERROR function =IFERROR(value, value_if_error ) Returns custom value if formula errors. Example : =IFERROR(A1/B1, "Error")

4. ISBLANK function =ISBLANK(value) Checks if a cell is empty. Example: =ISBLANK(A1)

5. LEFT function =LEFT(text, num_chars ) Extracts characters from the start. Example: =LEFT(A1, 3)

6. RIGHT function =RIGHT (text, num_chars ) Extracts characters from the end. Example: =RIGHT(A1, 3)

7. TRIM function =TRIM(text) Removes extra spaces. Example: =TRIM(A1)

8. PROPER function =PROPER(text) Capitalizes the first letter of each word. Example: =PROPER(A1)

9. UPPER function =UPPER(text) Converts to uppercase. Example: =UPPER(A1)

10. LOWER function =LOWER (text) Converts to lowercase. Example: =LOWER(A1)

11. TODAY function =TODAY() Returns current date. Example: =TODAY()

12. NOW function =NOW() Returns current date and time. Example: =NOW()

13. TEXT function =TEXT(value, format_text ) Formats a number/date. Example: =TEXT(A1, "mm/dd/ yyyy ")

14. DATEDIF function =DATEDIF( start_date , end_date , unit) Calculates difference between dates. Example: =DATEDIF(A1, B1, "d") → days

15. NETWORKDAYS function =NETWORKDAYS( start_date , end_date , [holidays]) Returns number of workdays. Example: =NETWORKDAYS(A1, B1)

16. EOMONTH function =EOMONTH( start_date , months) Gets end of month date. Example: =EOMONTH(A1, 1) → end of next month

17. ROUND function =ROUND(number, num_digits ) Rounds a number to specified decimal places. Example: =ROUND(A1, 2)

18. ROUND function =ROUNDUP(number, num_digits ) / ROUNDDOWN(number, num_digits ) Example: =ROUNDUP(A1, 0) Example: =ROUNDDOWN(A1, 0)

19. INT function =INT(number) Rounds number down to nearest integer. Example: =INT(4.75) → 4

20. COUNT function =COUNT(range) Counts the number of numeric values in a range. Example: =COUNT(A1:A10)

21. COUNTA function = COUNTA(range) Counts all non-empty cells. Example: =COUNTA(A1:A10)

22. COUNTIF function = COUNTIF(range, criteria) Counts cells that meet a single condition. Example: =COUNTIF(A1:A10, ">5")

23. COUNTIFS function = COUNTIFS(range, criteria) Counts cells that meet a single condition. Example: =COUNTIF(A1:A10, ">5")

24. SUM function = SUM(range) Adds all numbers in a range. Example: =SUM(B1:B10)

25. SUMIF function =SUMIF(range, criteria, [ sum_range ]) Sums values based on a condition. Example: =SUMIF(A1:A10, ">5", B1:B10)

26. SUMIFS function =SUMIFS( sum_range , criteria_range1, criteria1, ...) Sums values based on multiple conditions. Example: =SUMIFS(B1:B10, A1:A10, ">5", C1:C10, "<100")

27. AVERAGE function =AVERAGE(range) Calculates the mean of numbers. Example: =AVERAGE(C1:C10)

28. AVERAGEIF function =AVERAGEIF(range, criteria, [ average_range ]) Averages values that meet a condition. Example: =AVERAGEIF(A1:A10, ">5", B1:B10)

29. SUBTOTAL function =SUBTOTAL( function_num , ref1, [ref2], …) Performs calculations like sum, average, etc. Example: =SUBTOTAL(9, A1:A10) → SUM

30. FILTER function =FILTER(array, include, [ if_empty ]) Filters a range based on a condition. Example: =FILTER(A2:B10, B2:B10>50)

31. UNIQUE function =UNIQUE(array) Returns unique values from a range. Example: =UNIQUE(A1:A10)

32. HLOOKUP function =HLOOKUP( lookup_value , table_array , row_index , [ range_lookup ]) Looks up a value horizontally. Example: =HLOOKUP("Math", A1:Z2, 2, FALSE)

33. VLOOKUP function =VLOOKUP( lookup_value , table_array , col_index , [ range_lookup ]) Looks up a value vertically. Example: =VLOOKUP(101, A2:C100, 3, FALSE)

34. XLOOKUP function =XLOOKUP( lookup_value , lookup_array , return_array , [ if_not_found ], [ match_mode ], [ search_mode ]) Replaces VLOOKUP and HLOOKUP. Example: =XLOOKUP(101, A2:A100, B2:B100, "Not found")

35. DGET function =DGET(database, field, criteria) Extracts a single value from a database. Example: =DGET(A1:C100, "Salary", E1:F2)

36. SORT function =SORT(array, [ sort_index ], [ sort_order ], [ by_col ]) Sorts an array. Example: =SORT(A2:B10, 2, -1) (Sort by 2nd column descending)

37. SORT function =RANK(number, ref, [order]) Ranks a number in a list. Example: =RANK(A1, A1:A10)

38. CONCATENATE function =CONCATENATE(text1, [text2], ...) Joins multiple strings. Example: =CONCATENATE(A1, " ", B1)

39. TEXTJOIN function =TEXTJOIN(delimiter, ignore_empty , text1, [text2], ...) Joins text with a delimiter. Example: =TEXTJOIN(", ", TRUE, A1:A5)

40. Excel Shortcut Keys

41. ALT + H + W – Set Column Width ALT + H + H + N – Fill Cell with No Color ALT + H + B + U – Apply Bottom Border ALT + H + S + F – Sort Ascending (Smallest to Largest) CTRL + ARROW KEY – Move to Edge of Data Region

42. SHIFT + SPACE – Select Entire Row CTRL + SPACE – Select Entire Column ALT + H + W – (Repeated: Set Column Width) ALT + H + M + C – Merge and Center ALT + H + M + M – Merge Cells Only

43. ALT + H + S + C – Sort Descending (Largest to Smallest) SHIFT + ALT + RIGHT ARROW – Group Rows or Columns SHIFT + ALT + LEFT ARROW – Ungroup Rows or Columns Go To Special (Ctrl + G → Alt + S) ALT + E + S + U – Paste Values

44. ALT + E + S + V – Paste Formats ALT + E + S + E – Transpose the copied text ALT + H + O + I – AutoFit Column Width ALT + H + O + A – AutoFit Row Height CTRL + SHIFT + "+" – Insert Cells, Rows, or Columns CTRL + "-" – Delete Cells, Rows, or Columns