From Excel Basics to Practice

2. Data display format

3. Smart Excel Auto-Fill

4. Making a Simple Calculation Table

5. Managing Sheets and Workbooks

6. Practical! Creating a Quotation Form

7. Drafting Expenditure Request

8. After filling out approval form, insert into expenditure proposal

11. [Handy Tips] Custom Format

12. [Useful to know] How to represent negative numbers

14. [Handy tips] Insert, Edit, Delete, and Display Comments in Cells

15. [Handy Tips!] Paste Special Paste Link

16. [Handy Tips] Paste Operations and Paste without Borders

18. Can the first row be fixed?

19. Sort data for easy viewing

20. [Handy Tips] Custom Sorting

21. [Useful to know] Sorting Merged Data

22. Fill Blank Cells

23. Remove hidden spaces

24. Convert Numbers Stored as Text to Number Format [Useful to Know]

26. Restricting Date Input Format

27. Displaying incorrect data

28. No need to press the [Han/Eng] key! - IME Mode

29. Organize Data According to Database Rules

30. Smartly printing large amounts of data

31. Seat Protection

32. Workbook Protection

33. Set Opening Password

35. Absolute Reference Method - Calculating Composition Ratio

36. [Handy Tip] Running Total

37. [Handy Tip] Defining Names: Using Aliases for Cell Addresses

38. Get Discount Amount with Mixed References

39. Need for Excel functions

40. Easily use functions with the AutoSum feature

41. RANK function for ranking

42. [Useful to know] LARGE and SMALL functions

43. Number Floor Function

44. Truncate to one decimal place

45. Count Functions - COUNT and COUNTA

46. COUNTIF and COUNTIFS Functions for Counting Based on Conditions

47. Count by age

48. Mission Problem Solving: Formulas & Basic Functions

50. Write results as formula

51. Nesting the AND function when writing conditional expressions in the IF function

52. Compare two sheets to get True/False results

53. [Handy Tip] Numbering Sequentially Excluding Blank Cells Using IF and COUNTA Functions

54. [Tips] Nested IF Usage

55. Multiple IF or IFS Function | Aha! If there are multiple conditions, I can use this function~ | New

56. Conditional Formatting when highlighting specific data | New

57. Conditional Formatting to see data matching conditions at a glance

58. Highlight cells that are above the average using Top/Bottom Rules

59. Highlight Entire Rows Meeting Two Conditions Using the AND Function

60. [Tip] Highlight Matching Data Between Two Sheets

61. VLOOKUP Function for Finding Data by Condition

62. Row/Column Swapping Function and TRANSPOSE Function

63. Catch the #N/A Error! - IFERROR Function

64. Finding matching values similar to VLOOKUP setup

65. Looking up a value from another sheet using the VLOOKUP function

66. INDEX, MATCH Functions Beyond VLOOKUP

67. [Handy to Know] Differentiating Usage of VLOOKUP and INDEX/MATCH Functions

68. [Helpful Tip] Importing Pictures from Another Sheet Using INDEX and MATCH Functions

70. [Handy to know] XLOOKUP function - When it's difficult to find with VLOOKUP function, find with XLOOKUP function

71. Creating a convenient quotation using the VLOOKUP function

75. [Useful to know] Using Text to Columns

76. LEFT, RIGHT, MID Text Functions

77. Show RRN suffix as *

78. Gender from RRN: 2 Ways

79. Marking Middle Name Chars with * (REPLACE)

80. Remove hyphens from RRN using SUBSTITUTE function

81. The LEN function for counting characters and the FIND function for finding position

82. [Useful to know] Removing unnecessary spaces from both ends of data using the TRIM function

84. How to use YEAR, MONTH, DAY functions and age calculation

85. Get Birthdate from Resident Number using DATE function

86. Calculating age using Resident Registration Number

87. DATEDIF function for calculating periods

88. WORKDAY function that finds dates excluding holidays

89. NETWORKDAYS function that calculates the number of days excluding holidays

90. WEEKDAY function to get the day of the week for a date

92. SUMIFS and AVERAGEIFS Functions: Sum and Average with Multiple Criteria

93. Aggregate Unpaid Amounts by Student Using the SUMIFS Function

94. [Handy Tip] Complete an Aggregate Table with the SUMIF Function

95. 3D formula

96. Calculate the average of multiple sheet items using the Consolidate function

97. Consolidating Branch-Specific Summaries from 6 Separate Workbook Files

98. [Handy Tip] Aggregate Massive Data Quickly and Easily Using Consolidation

99. All About the Subtotal Feature for Summarizing Data

100. Copy visible cells only

101. Filter - Show data by criteria : Text Filter

102. Number Filter : Display only matching data

103. Date filter displaying only data for the relevant period

106. Calculating Sum Changing with Filtered Results Using SUBTOTAL

107. [Useful Tip] Using SUBTOTAL and VLOOKUP Functions, and Filter to Calculate Subtotals

108. Filtering List of Applicants for Two or More Courses Using Advanced Filter

109. Filtering Match/Mismatch Data Comparing Two Sheets

110. Handy Tips] Basic Usage of Advanced Filter

111. Useful to know] Extracting duplicate data using Advanced Filter

113. Make Horizontal Bar Chart

114. Creating a line chart for easy viewing of trends

115. Good pie charts for composition

116. Combo chart for large deviations in value range

117. Modify Combo Chart

118. [Handy to Know] Daily Column Chart

120. Field Grouping

121. Refreshing PivotTable Data and Changing Source Data

122. [Handy Tips] Reviewing Pivot Tables

123. [Handy Tips] Creating Custom Group Fields in PivotTables

124. Insert Recommended PivotTable and Slicer

125. Pivot Chart

126. Creating charts for reports using pivot tables

127.

128. Write formulas for a transaction statement using the VLOOKUP function and complete the report with a PivotTable and PivotChart.

129. 4 Steps to Use Power Query | Will blow away the prejudice that it's difficult 👍 | New

131. Combine data scattered across multiple sheets or files into one to create a PivotTable (No VBA needed! 👍)

133. Try Power Query instead of INDEX, MATCH functions. Easily get the desired results! 👍

134. When finding values matching two or more conditions: VLOOKUP (×) Power Query (○) | You can get the desired results so easily! Highly Recommended 👍

135. Extracting Duplicate Data Using Practical Examples | Guaranteed to finish work on time! 👍👍👍

136. Compare Two Sheets to Extract Matching or Non-Matching Data | Power Query for Getting Off Work on Time

137. Unpivot Columns | Data Preventing Pivot Tables~ Solution Revealed | New

139. Utilizing a dynamic range defined by name in a function

140. Dynamic Charts Easily Created with Table Formatting

141. Setting a dynamic range using the OFFSET function

142. Creating an Auto-Updating Chart with OFFSET (Data Add/Delete)

144. Basic method for writing array formulas

145. Using Array Formulas

146. COUNTIF cannot count people by condition

147. Calculating the Sum of Even and Odd Rows - Using SUMIF Function and Array Formulas

148. Finding a Value Matching Two Criteria That VLOOKUP Cannot Find - Using INDEX and MATCH Array Formulas

149. Counting the number of cells containing a specific word by writing an array formula using COUNT and FIND functions

153. Using Table Formatting for a Dynamic Range Database

154. Entering a formula in non-contiguous cells at once

155. How do I sort the subtotaled results?

156. Protect Sheet to Prevent Deleting Formulas in Cells

157. Write a formula using IF and IFERROR functions for automatic data numbering.

158. Distinguish Even and Odd Numbers using ISODD, ISEVEN, MOD, IF Functions

159. Using IF, ISODD, MID functions to determine gender from resident registration number

160. Using IF, LEN, REPLACE, REPT functions to mask middle characters of a name with *

161. Color Fill Even Rows - Using ISEVEN, MOD Functions

162. Color every fifth row - Use MOD, ROW functions

163. Using the TEXT Function - How to Apply Formatting to Data Where Display Format Cannot Be Applied

164. Creating a Double Validation List - Setting Dynamic Range using the INDIRECT Function and Table Formatting

165. Look up a value in a specific sheet using the INDIRECT function

166. If you are debating between Pivot Table and SUMIFS function when creating aggregation tables?

167. Calculate the count per interval using the FREQUENCY function or Pivot Table

168. When the COUNTIF function for finding duplicate data meets Excel features - Conditional Formatting, Advanced Filter, Data Validation

172. Add frequently used features to the Quick Access Toolbar and use them as shortcuts

173. Remove page breaks shown after print preview

175. Various Ways to Enter Special Characters and the CODE Function to Get ASCII Code Values

176. Which should I use: display format or the TEXT function?

177. Paste Special: Keep Column Widths or Exclude Borders

182. Selecting Event Entry Winners Using Random Number Functions (RAND, RANDBETWEEN, RANDARRAY Functions)

187. VBA enhancing macros completed

188. Advanced Filter Macro - Setting up a macro to automatically display the result value whenever the condition is changed

189. VBA to view and modify contents of multiple sheets

190. Relative reference macro: Split monthly data into sheets

191. How to Highlight Rows/Columns When Selecting a Cell 👍 | Use it directly in practice 📢