2015-2021 Excel Tips Weekly Updated 09 02 2021

1. 001 - Hide and unhide worksheets securely.mp417.01MB
2. 002 - Resizing, moving, copying, and manipulating charts and other objects.mp427.85MB
3. 003 - Using list boxes and combo boxes to facilitate forms creation.mp421.48MB
4. 004 - Using the FORMULATEXT and N functions for worksheet and formula documentation.mp416.4MB
5. 005 - Using the ISBLANK and COUNTBLANK functions to check for and tabulate blank cells.mp423.75MB
6. 006 - Summarizing data from diverse ranges with Data - Consolidate.mp429.4MB
7. 007 - Using check boxes and conditional formatting together for dynamic displays.mp440.45MB
8. 008 - Becoming more productive with these 10 tiny tips you'll use often.mp424.1MB
9. 009 - Transposing data and charts for a different perspective on your data.mp430.3MB
10. 010 - Applying conditional formatting across rows of data.mp420.45MB
11. 011 - Exploring various layouts for your PivotTable.mp413.86MB
12. 012 - Splitting and consolidating workbooks.mp420.53MB
13. 013 - Using date and time as metrics in a PivotTable.mp423.2MB
14. 014 - Adding illustrations to your workbook.mp433.62MB
15. 015 - Using a pick list, VLOOKUP, table, and chart together.mp422.57MB
16. 016 - Using formulas to calculate values across worksheets and workbooks.mp429.52MB
17. 017 - Dynamically presenting data via chart slicers.mp420.42MB
18. 018 - Parsing your data to create unique lists.mp414.51MB
19. 019 - Using the REPT function to represent data visually.mp48.82MB
20. 020 - Using source data from a table to make your PivotTable more dynamic.mp424.96MB
21. 021 - Accelerating Cut, Copy, Paste, and Insert tasks.mp423.25MB
22. 022 - Increasing your productivity with custom lists.mp420.89MB
23. 023 - Highlighting PivotTable results with conditional formatting.mp419.23MB
24. 024 - Customizing templates to fit your unique needs.mp420.37MB
25. 025 - Creating dynamic charts with in-cell conditional formatting.mp411.49MB
26. 026 - Enhancing table filtering with slicers.mp420.19MB
27. 027 - Converting charts into pictures.mp415.93MB
28. 028 - Custom grouping in PivotTables.mp412.99MB
29. 029 - Quick formatting tips.mp423.4MB
30. 030 - The top five unknown but useful keyboard shortcuts.mp425.61MB
31. 031 - Using the TREND and GROWTH functions for projecting future results.mp421.61MB
32. 032 - Using scroll bars and spin buttons to facilitate forms creation.mp423.56MB
33. 033 - Creating colorful 3D buttons for worksheet annotation and macro buttons.mp421.88MB
34. 034 - Calculating with hours, minutes, and times of day.mp429.66MB
35. 035 - Tracking down cell dependencies across multiple worksheets and workbooks.mp421.74MB
36. 036 - Discovering time-saving tools in the Go To Special feature.mp440.04MB
37. 037 - Adding, managing, and printing comments for worksheet documentation.mp419.26MB
38. 038 - Reducing data entry time by expanding AutoCorrect options.mp418.99MB
39. 039 - Setting up custom views for quick access to different worksheet displays.mp425.08MB
40. 040 - Using hyperlinks for rapidly switching worksheet locations and jumping to websites.mp421.15MB
41. 041 - Controlling worksheet security by allowing selected users to edit specific cell ranges.mp424.18MB
42. 042 - Using option buttons, group boxes, and checkboxes to facilitate forms creation.mp419.93MB
43. 043 - Exploring what-if scenarios using Goal Seek.mp428.04MB
44. 044 - Creating text, numerical, date, and time lists in a flash.mp417.89MB
45. 045 - Creating variable conditional formatting rules.mp430.8MB
46. 046 - Handling dates with unusual formats.mp420.25MB
47. 047 - Using wildcards (asterisk, tilde, and question mark).mp438.13MB
48. 048 - Custom formats using asterisk, semicolon, and brackets.mp439.18MB
49. 049 - Meeting unusual filtering needs via Advanced Filter.mp447.68MB
50. 050 - Create an organization chart.mp423.64MB
51. 051 - Two-way lookup using the MATCH and INDEX functions.mp450.68MB
52. 052 - Using Excel error-checking functions.mp433.63MB
53. 053 - Custom formats, relative addressing, and conditional formatting.mp423.95MB
54. 054 - Auditing.mp430.66MB
55. 055 - Adding comments and shapes.mp425.43MB
56. 056 - Drawing borders and border grids.mp421.36MB
57. 057 - Building a flexible monthly table using functions.mp432.81MB
58. 058 - Adding pictures as worksheet backgrounds.mp420.05MB
59. 059 - ADDRESS, ROW, and COLUMN functions.mp427.67MB
60. 060 - Join data with new CONCAT and TEXTJOIN functions.mp417.57MB
61. 061 - Working with formulas in tables.mp448.15MB
62. 062 - Locating data validation rules and violations.mp428.54MB
63. 063 - Creating an Excel template.mp423.18MB
64. 064 - Restoring missing column titles.mp426.37MB
65. 065 - Creating a pick list dependent on another pick list.mp418.84MB
66. 066 - Using command shortcuts with the Alt key.mp420.2MB
67. 067 - Set up a macro for sorting dynamic data.mp428.18MB
68. 068 - Use random number functions for sorting and creating sample data.mp428.83MB
69. 069 - Use calcuated fields and calculated items in a PivotTable.mp426.32MB
70. 070 - Replace characters by position with the REPLACE function.mp415.57MB
71. 071 - Work with formulas in tables.mp430.04MB
72. 072 - Keystroke shortcuts from A to Z Using the Ctrl key.mp455.03MB
73. 073 - Use special tricks for working with dates in charts.mp433.2MB
74. 074 - Use a data form for data entry and exploring data.mp424.91MB
75. 075 - Creating an Excel template to simplify updating a monthly file.mp419.66MB
76. 076 - Calculate dates efficiently using Excel's hidden function - DATEDIF.mp422.16MB
77. 077 - Create dynamic sheet name references with the INDIRECT function.mp419.17MB
78. 078 - Avoid errors and hidden data with the powerful AGGREGATE function.mp420.84MB
79. 079 - Hide worksheets, row, columns, cells and other Excel elements.mp427.8MB
80. 080 - Use slicers and charts together for dynamic presentations.mp429.07MB
81. 081 - Save a chart as a template Use the template for current or new charts.mp418.4MB
82. 082 - MAXIFS and MINIFS added to the COUNTIF-SUMIF family of functions.mp422.7MB
83. 083 - Adjust a chart's source data and adjust its series order.mp425.94MB
84. 084 - Use the OFFSET function for tabulating moving data.mp430.82MB
85. 085 - Alter numeric data without formulas.mp437.41MB
86. 086 - Use Flash Fill to rapidly combine or separate columnar data.mp434.31MB
87. 087 - Adjust banded-row formatting.mp457.78MB
88. 088 - Change the shape of comment boxes and other objects.mp419.25MB
89. 089 - How and when to use the 10 rounding functions in Excel.mp435.35MB
90. 090 - Calculate faster with the AutoSum button and AutoSum keystroke shortcut.mp418.91MB
91. 091 - How to use mixed cell references.mp423.41MB
92. 092 - Activate the Speak On Enter and Speak Cells features.mp414.71MB
93. 093 - Accelerate data entry with five special techniques.mp431.52MB
94. 094 - Insert a text box or shape and link a cell's content to it.mp416.99MB
95. 095 - Use Watch Window and other techniques to track changing cells.mp429.69MB
96. 096 - How to create time interval entrie.mp432.38MB
97. 097 - How to create frequency tabulations and distributions.mp424.93MB
98. 098 - Create an expanded list from a summary using PivotTable techniques.mp436.74MB
99. 099 - Solve matching issues with matching phone numbers and SS numbers.mp435.66MB
100. 100 - Use CHOOSE to calculate fiscal quarters and as a VLOOKUP alternative.mp416.52MB
101. 101 - Use outlining tools to quickly expand and collapse data for dynamic presentations.mp435.93MB
102. 102 - Use the SUBTOTAL (and AGGREGATE) functions to avoid double counting.mp430.07MB
103. 103 - Create lists of all 2nd Tuesdays, last Tuesdays. and other date series.mp429.79MB
104. 104 - Create linkage formulas that refer to other worksheets and workbooks.mp429.86MB
105. 105 - Formula nesting - Using multiple functions in the same formula.mp438.99MB
106. 106 - Keyboard shortcuts for numeric formats.mp425.85MB
107. 107 - Use the Subtotal command to analyze data with single- or multiple-level subtotals.mp441.35MB
108. 108 - Work with MAX, MAXIFS, LARGE, and related functions.mp424.59MB
109. 109 - Use the dynamic TODAY and NOW functions for real-time up-to-date calculations.mp424.29MB
110. 110 - Use the FIND, MID, LEFT, and RIGHT functions.mp429.82MB
111. 111 - Flip the left-to-right order of columns with the INDEX function or sort by columns.mp429.38MB
112. 112 - Working with hidden data when copying cells and creating charts.mp440.93MB
113. 113 - Use conditional formatting data bars to accentuate negative data.mp411.45MB
114. 114 - Creative use of sparklines in merged cells with axes.mp419.03MB
115. 115 - Copy formats quickly using dragging techniques and the Format Painter.mp417.39MB
116. 116 - Displaying multiple worksheets and workbooks together.mp432.4MB
117. 117 - Tips for creating and updating sample data.mp431.62MB
118. 118 - Use conditional formatting to overcome formatting limitations of the IF function.mp424.14MB
119. 119 - Use VLOOKUP, MATCH, and INDEX functions with array formulas.mp429.89MB
120. 120 - Summarize data from different worksheets with a PivotTable.mp423.43MB
121. 121 - AutoFill shortcuts for date series, one-two week intervals, EOM, and formulas.mp420.22MB
122. 122 - Create summary statistics using COUNTIFS, SUMIFS, and AVERAGEIFS functions.mp424.27MB
123. 123 - Use custom formulas in data validation to keep out bad data.mp435.01MB
124. 124 - Dealing with circular errors.mp422.37MB
125. 125 - Tips for creating charts quickly.mp419.24MB
126. 126 - Creating heat maps using conditional formatting.mp434.91MB
127. 127 - Using the INDEX function to extract data by row and column.mp428.38MB
128. 128 - Displaying tips when using Excel in presentations.mp429.65MB
129. 129 - The Wrap Text, Merge and Center, and Indent options.mp430.35MB
130. 130 - Using fill effects (gradients, patterns, styles) for colorful cell background variations.mp417.05MB
131. 131 - Working with multiple worksheets simultaneously.mp425.99MB
132. 132 - Exploring font choices not found on the Home tab.mp411.68MB
133. 133 - Using WordArt for special titles and headings.mp414.54MB
134. 134 - Creating a two-way lookup with VLOOKUP, HLOOKUP, INDEX, and MATCH functions.mp422.8MB
135. 135 - Borders and gridlines - Exploring variations, options, and differences.mp425.12MB
136. 136 - New Excel 2016 chart types Tree map and sunburst.mp430.13MB
137. 137 - Freezing column and row titles.mp441.85MB
138. 138 - Use data validation to force entries to be uppercase or lowercase.mp425.51MB
139. 139 - Avoiding common chart distortions.mp432.73MB
140. 140 - Use the new funnel chart available in Excel 365.mp410.63MB
141. 141 - Combine data using CONCATENATE, CONCAT, and TEXTJOIN functions and the ampersand (&) character.mp415.23MB
142. 142 - Recognizing Excel Formula errors #NA, #VALUE!, #REF!, #DIV0!, #NUM, and #NAME!.mp428.07MB
143. 143 - Creating cross-totals with the SUMIFS function and mixed references.mp417.62MB
144. 144 - New Excel 2016 chart types Histogram, Pareto, and box, and whisker.mp428.03MB
145. 145 - Using the AND and OR functions independently or within IF functions.mp427.92MB
146. 146 - Vertical and horizontal alignment settings and orientation options.mp418.54MB
147. 147 - Sorting tips and shortcuts.mp432.43MB
148. 148 - A look at new chart types in Excel 2016 - Waterfall.mp417.99MB
149. 149 - Work with formulas and formats Decimals and fractions.mp413.53MB
150. 150 - Creating mixed reference formulas; converting to a table; applying conditional formatting.mp427.89MB
151. 151 - Control gridlines in charts and on worksheets.mp421.68MB
152. 152 - Use the INDIRECT function with intersection formulas and range names to extract data.mp420.16MB
153. 153 - Using Expand-Collapse on PivotTables and PivotCharts for rapid display changes.mp421.95MB
154. 154 - Control phone number and postal code formats using built-in options or customized formats.mp418.7MB
155. 155 - Change the default Excel chart type; create and use chart templates.mp419.14MB
156. 156 - Create your own customized date and time formats.mp422.5MB
157. 157 - Create on-screen warnings and reminders with comments or data validation messages.mp418.79MB
158. 158 - Use REPLACE for formats only, for data only, or for both.mp422.46MB
159. 159 - Create numeric formats - Display in thousands or millions.mp435.8MB
160. 160 - Converting dates like 20102006 into usable date entries.mp411.98MB
161. 161 - Create custom lists for letter series like A to Z.mp428.74MB
162. 162 - Freeze Panes and Split.mp430.26MB
163. 163 - NETWORKDAYS.INTL and WORKDAY.INTL.mp414.74MB
164. 164 - Calculate % of change.mp414MB
165. 165 - Fill in all blank cells within a range with either formatting or data.mp416.56MB
166. 166 - Use the COUNT and COUNTA functions.mp413.93MB
167. 167 - Use workbook protection to prevent use of sheet commands.mp418MB
168. 168 - Rank data with the RANK and RANK.AVG functions.mp416.11MB
169. 169 - Use nonstandard fiscal years and quarters in PivotTables.mp418.83MB
170. 170 - Identify or extract unique entries in a list.mp422.68MB
171. 171 - Keyboard, mouse, and command techniques for viewing worksheets.mp427.06MB
172. 172 - Create artistic charts with random numbers.mp422.72MB
173. 173 - Extract day, month, and year from date fields.mp413.15MB
174. 174 - Compare data with EXACT, FIND, and SEARCH functions.mp413.17MB
175. 175 - How to covert ROMAN numberals.mp413.25MB
176. 176 - Use range names for more readable formulas.mp415.5MB
177. 177 - Customize your Quick Access Toolbar.mp420.33MB
178. 178 - Conditional formatting based on date proximity.mp413.75MB
179. 179 - How to adjust names.mp416.21MB
180. 180 - Differences and limitations of converting data to a table.mp418.78MB
181. 181 - Avoid the #DIV0 error message.mp413.76MB
182. 182 - Explore formatting options not available on the Home tab.mp415.55MB
183. 183 - Control table formatting with custom formats.mp412.2MB
184. 184 - Use date functions for age and tenure calculations.mp416.43MB
185. 185 - Avoid unintentional entries when typing code numbers.mp49.52MB
186. 186 - Sorting by moving columns.mp415.31MB
187. 187 - Calculating cumulative totals.mp411.22MB
188. 188 - Using the CONVERT function for different numbering systems.mp412.85MB
189. 189 - Creating an automatically expanding chart by basing it on a table.mp411.3MB
190. 190 - Keystroke shortcuts using the Alt key.mp414.71MB
191. 191 - Using the Solver Add-in.mp411.75MB
192. 192 - Column widths, row heights, merging cells, and related formatting issues.mp410.97MB
193. 193 - Adjusting default layouts and date grouping in PivotTables.mp411.42MB
194. 194 - Auditing cell content with Watch Window and dependent cell formulas.mp49.94MB
195. 195 - Use the LEN and REPT functions for specialized cell testing and display options.mp410.58MB
196. 196 - Use the TRIM and CLEAN functions to remove excess spaces and non-printing characters.mp411.98MB
197. 197 - Create double-spaced and triple-spaced printouts while repeating column headings.mp412.71MB
198. 198 - Helpful keystroke shortcuts.mp421.27MB
199. 199 - Identify weekdays and weekend days in data.mp411.98MB
200. 200 - Use chart and filter for presentations.mp49.52MB
201. 201 - Create picture links.mp419.23MB
202. 202 - How to use the error-checking rules in Excel.mp413.63MB
203. 203 - Protect worksheets and lock cells.mp417.24MB
204. 204 - Use mixed addresses in Excel formulas.mp412.68MB
205. 205 - Rapid filtering with Filter by Selection.mp412.68MB
206. 206 - Display large values.mp417.27MB
207. 207 - Tracking down conditional formatting and data validation rules.mp418.94MB
208. 208 - Transposing data and using the TRANSPOSE function.mp413.41MB
209. 209 - Displaying gridlines, borders, and column_row headings when printing.mp413.46MB
210. 210 - Exploring some of the 200-plus SmartArt graphic options.mp49.03MB
211. 211 - Using the CEILING and FLOOR functions for specialized rounding needs.mp411.87MB
212. 212 - Inserting, reshaping, and formatting shapes - Rectangles, arrows, stars, and banners.mp412.95MB
213. 213 - Tabulating totals with the VLOOKUP function and array constants.mp413.51MB
214. 214 - Working with array formulas more easily.mp411.05MB
215. 215 - Using the new UNIQUE function to count and copy unique list entries.mp410.23MB
216. 216 - Creating and updating sample data.mp415.74MB
217. 217 - Format macros to make you a more efficient Excel user.mp414.5MB
218. 218 - Use the new SORT and SORTBY functions to extract sorted lists.mp417.7MB
219. 219 - Use themes to adjust worksheet colors, fonts, and effects.mp411.3MB
220. 220 - Calculate loan payments and investments with PMT and FV functions.mp412.35MB
221. 221 - Use the XLOOKUP function to replace VLOOKUP.mp414.96MB
222. 222 - Chart display options with blank cells in source data.mp410.13MB
223. 223 - Adjust Conditional Formatting rules by altering percentage breakpoints.mp412.08MB
224. 224 - Discover new formula capabilities with new functions and dynamic arrays.mp413.68MB
225. 225 - Sort or filter data based on color font or cell color background.mp423.75MB
226. 226 - Simplify the use of special characters and symbols.mp411.49MB
227. 227 - Select from over 800 icons to enliven worksheets.mp416.46MB
228. 228 - Use the new RANDARRAY function that replaces RAND and RANDBETWEEN.mp415.76MB
229. 229 - How and when to use an area chart.mp411.11MB
230. 230 - Insert colorful images with the People Graph Add-in.mp414.87MB
231. 231 - Use formulas to create interactive charts.mp411.25MB
232. 232 - Track variables using the Scenario Manager.mp416.33MB
233. 233 - Get totals quickly without creating formulas.mp416.76MB
234. 234 - Use column or row references to create dynamic formulas.mp413.23MB
235. 235 - Overcoming obstacles when working with dates in charts.mp412.15MB
236. 236 - Create map-type charts based on geographical locations.mp49.49MB
237. 237 - Techniques for creating date series.mp410.81MB
238. 238 - Extract filtered data with data validation and the FILTER function.mp413.97MB
239. 239 - Use Find and Replace to change cell contents and formats.mp421.75MB
240. 240 - Add formula tools and symbols to the Quick Access Toolbar.mp416.51MB
241. 241 - Change PivotTable settings for titles and summaries.mp416.75MB
242. 242 - Use the Excel filtering capability for dates.mp413.11MB
243. 243 - Use worksheet names in formulas.mp411.93MB
244. 244 - Use data validation rules with special phone and social security formats.mp412.94MB
245. 245 - Work with time calculations in formulas.mp411.43MB
246. 246 - Prevent and locate duplicate worksheet entries.mp413.8MB
247. 247 - Look forward and backward using EDATE and other date functions.mp415.84MB
248. 248 - Display and highlight worksheet formula cells.mp415.23MB
249. 249 - Split or join columnar data with Text to Columns or Flash Fill.mp413.17MB
250. 250 - Highlight milestone data with conditional formatting and cumulative formulas.mp412.72MB
251. 251 - Use the XMATCH function to replace MATCH.mp49.47MB
252. 252 - Create a powerful macro.mp413.8MB
253. 253 - Work with hidden and visible data in filtered and subtotaled lists.mp421.59MB
254. 254 - Use TODAY, NOW, YEARFRAC, and DATEDIF.mp415.99MB
255. 255 - Use data validation rules to prevent duplicate entries in a range.mp416MB
256. 256 - Use various techniques to hide cells, rows, columns, and worksheets.mp418.09MB
257. 257 - Align, arrange, and rotate shapes in worksheets.mp417.31MB
258. 258 - File documentation with the Workbook Statistics button.mp411.33MB
259. 259 - Avoid misleading visuals when rescaling.mp413.63MB
260. 260 - Use the Fill Justify feature to wrap long text.mp410.18MB
261. 261 - Create range names from Column and Row headings.mp412.14MB
262. 262 - Use the SUBSTITUTE and REPLACE functions.mp414.28MB
263. 263 - Accelerate Conditional Formatting with the Quick Access Toolbar.mp413.7MB
264. 264 - Use wildcard symbols in functions.mp417.98MB
265. 265 - Use wildcard symbols in filtering and commands.mp420.6MB
266. 266 - Condense lengthy nested IF functions with the newer IFS function.mp411.21MB
267. 267 - Add flair to charts with these formatting options Gap width, shadow, glow, 3D, and more.mp414.54MB
268. 268 - Control worksheet security by allowing selected users to edit specific cell ranges.mp415.31MB
269. 269 - Retrieve vital data based on location using the Geography tool.mp411.44MB
270. 270 - Use the new SEQUENCE function to quickly build numeric and date arrays.mp410.87MB
271. 271 - Calculating text length and word count with LEN, TRIM, and SUBSTITUTE functions.mp414.22MB
272. 272 - Create a list box to facilitate selection of data from a list.mp414.72MB
273. 273 - Use various Excel count functions - COUNT, COUNTA, COUNTBLANK, and more.mp414.73MB
274. 274 - Enhance readability with banded rows via conditional formatting.mp418.76MB
275. 275 - Seven different ways to drag data using the Ctrl, Shift, and Alt keys.mp416.74MB
276. 276 - Use the SWITCH function - Compare with IFS, CHOOSE, and other lookup functions.mp413.43MB
277. 277 - Adjust charts quickly with quick layout, change colors, and chart styles options.mp410.61MB
278. 278 - Analyze PivotTable data with 13 different Show Values As options.mp421.79MB
279. 279 - Expand-collapse or sort displayed results when using the SUBTOTAL command.mp422.22MB
280. 280 - Use the AGGREGATE function to circumvent errors and ignore hidden data.mp421.4MB
281. 281 - Use the F9 key to evaluate parts of a formula, recalculate random entries, and control iteration.mp417.9MB
282. 282 - Using last-to-first (bottom-up) searches with XLOOKUP and XMATCH.mp413.73MB
283. 283 - Explore Paste Special options - Skip Blanks, Column Widths, Add, Transpose, and more.mp420.94MB
284. 284 - Reduce data entry drudgery with Ctrl+Enter, AutoComplete, and AutoCorrect.mp415.42MB
285. 285 - Using the LET function to simplify formulas via programming concepts.mp417.3MB
286. 286 - Create 51 new US State worksheets, or any multiple cluster of worksheets, in a flash.mp414.37MB
287. 287 - Create moving averages with formulas and chart trendlines.mp417.39MB
288. 288 - Use error-checking functions in Excel - IFERROR, ISERR, and ISERROR.mp419.93MB
289. 289 - Use Alt+Enter and Wrap Text for line-wrapping titles and improving formula readability.mp414.44MB
290. 290 - Work with cell colors, patterns, and effects to emphasize data and provide visual flair.mp419.68MB
291. 291 - Dynamic array formulas, new ways to calculate - One formula displays results in multiple cells.mp417.56MB
292. 292 - Fonts such as Arial and Calibri and a rundown of various available fonts in Excel.mp424.25MB
293. 293 - Use 3D formulas to tabulate data from multiple worksheets with the same layout.mp417.91MB
294. 294 - Multiple built-in date formats as well as tons of self-defined variations; m_d_y and mmm-d-yyyy.mp421.08MB
295. 295 - Where does that cell get its data from - Tracking down cell precedents.mp416.2MB