آقای اکسل ال‌ایکس: جام مقدس نکات اکسل ۲۰۱۹
MrExcel LX The Holy Grail of Excel Tips 2019

1. Dedication

2. About the Author

3. About the Contributors

4. Foreword

5. #1 Double-Click the Fill Handle to Copy a Formula

6. #2 Break Apart Data

7. #3 Filter by Selection

8. #4 Bonus Tip: Total the Visible Rows

9. #5 The Fill Handle Does Know 1, 2, 3…

10. #6 Fast Worksheet Copy

11. #7 Use Default Settings for All Future Workbooks

12. #8 Recover Unsaved Workbooks

13. #9 Simultaneously Edit the Same Workbook in Office 365

14. #10 New Threaded Comments Allow Conversations

15. #11 Create Perfect One-Click Charts

16. #12 Paste New Data on a Chart

17. #13 Create Interactive Charts

18. #14 Show Two Different Orders of Magnitude on a Chart

19. #15 Create Waterfall Charts

20. #16 Create Funnel Charts in Office 365

21. #17 Create Filled Map Charts in Office 365

22. #18 Create a Bell Curve

23. #19 Plotting Employees on a Bell Curve

24. #20 Add Meaning to Reports Using Data Visualizations

25. #21 Sort East, Central, and West Using a Custom List

26. #22 Sort Left to Right

27. #23 Sort Subtotals

28. #24 Sort and Filter by Color or Icon

29. #25 Consolidate Quarterly Worksheets

30. #26 Get Ideas from Artificial Intelligence

31. #27 Create Your First Pivot Table

32. #28 Create a Year-over-Year Report in a Pivot Table

33. #29 Change the Calculation in a Pivot Table

34. #30 Find the True Top Five in a Pivot Table

35. #31 Specify Defaults for All Future Pivot Tables

36. #32 Make Pivot Tables Expandable Using Ctrl+T

37. #33 Replicate a Pivot Table for Each Rep

38. #34 Use a Pivot Table to Compare Lists

39. #35 Build Dashboards with Sparklines and Slicers

40. #36 See Why GETPIVOTDATA Might Not Be Entirely Evil

41. #37 Eliminate VLOOKUP with the Data Model

42. #38 Compare Budget Versus Actual via Power Pivot

43. #39 Slicers for Pivot Tables From Two Data Sets

44. #40 Use F4 for Absolute Reference or Repeating Commands

45. #41 Quickly Convert Formulas to Values

46. #42 See All Formulas at Once

47. #43 Audit a Worksheet With Spreadsheet Inquire

48. #44 Discover New Functions by Using fx

49. #45 Use Function Arguments for Nested Functions

50. #46 Calculate Nonstandard Work Weeks

51. #47 Turn Data Sideways with a Formula

52. #48 Handle Multiple Conditions in IF

53. #49 Troubleshoot VLOOKUP

54. #50 Use a Wildcard in VLOOKUP

55. #51 Replace Columns of VLOOKUP with a Single MATCH

56. #52 Use the Fuzzy Lookup Tool from Microsoft Labs

57. #53 Lookup to the Left with INDEX/MATCH

58. #54 Preview What Remove Duplicates Will Remove

59. #55 Replace Nested IFs with a Lookup Table

60. #56 Suppress Errors with IFERROR

61. #57 Handle Plural Conditions with SUMIFS

62. #58 Geography & Stock Data Types in Excel

63. #59 Dynamic Arrays Can Spill

64. #60 Sorting with a Formula

65. #61 Filter with a Formula

66. #62 Formula for Unique or Distinct

67. #63 Other Functions Can Now Accept Arrays as Arguments

68. #64 One Hit Wonders with UNIQUE

69. #65 SEQUENCE inside of other Functions such as IPMT

70. #66 Replace a Pivot Table with 3 Dynamic Arrays

71. #67 Dependent Validation using Dynamic Arrays

72. #68 Complex Validation Using a Formula

73. #69 Use A2:INDEX() as a Non-Volatile OFFSET

74. #70 Subscribe to Office 365 for Monthly Features

75. #72 Less CSV Nagging and Better AutoComplete

76. #73 Speed Up VLOOKUP

77. #74 Protect All Formula Cells

78. #75 Back into an Answer by Using Goal Seek

79. #76 Do 60 What-If Analyses with a Sensitivity Analysis

80. #77 Find Optimal Solutions with Solver

81. #78 Improve Your Macro Recording

82. #79 Clean Data with Power Query

83. #80 Render Excel Data on an iPad Dashboard Using Power BI

84. #81 Build a Pivot Table on a Map Using 3D Maps

85. #82 The Forecast Sheet Can Handle Some Seasonality

86. #83 Perform Sentiment Analysis in Excel

87. #84 Fill in a Flash

88. #85 Format as a Façade

89. #86 Word Cloud using Custom Visuals in Excel

90. #87 Surveys & Forms in Excel

91. #88 Use the Windows Magnifier

92. #90 Avoid Whiplash with Speak Cells

93. #91 Customize the Quick Access Toolbar

94. #92 Create Your Own QAT Routines Using VBA Macros

95. #93 Favorite Keyboard Shortcuts

96. #94 Ctrl+Click to Unselect Cells

97. #95 More Excel Tips

98. Appendix: Excel Stories

99. Index