2015-2020 Excel Tips Weekly Updated 07 04 2020 -IVY

1. 019-Using_the_REPT_function_to_represent_data_visually.mp48.82MB
2. 210 - Exploring some of the 200-plus SmartArt graphic options.mp49.03MB
3. 251 - Use the XMATCH function to replace MATCH.mp49.47MB
4. 236 - Create map-type charts based on geographical locations.mp49.49MB
5. 200-Use_chart_and_filter_for_presentations.mp49.52MB
6. 185-Avoid_unintentional_entries_when_typing_code_numbers.mp49.52MB
7. 194-Auditing_cell_content_with_Watch_Window_and_dependent_cell_formulas.mp49.94MB
8. 222 - Chart display options with blank cells in source data.mp410.13MB
9. 215 - Using the new UNIQUE function to count and copy unique list entries.mp410.23MB
10. 195-Use_the_LEN_and_REPT_functions_for_specialized_cell_testing_and_display_options.mp410.58MB
11. 140-Use_the_new_Funnel_chart_available_in_Excel_365.mp410.63MB
12. 237 - Techniques for creating date series.mp410.81MB
13. 192-Column_widths_row_heights_merging_cells_and_related_formatting_issues.mp410.97MB
14. 214 - Working with array formulas more easily.mp411.05MB
15. 229 - How and when to use an area chart.mp411.11MB
16. 187-Calculating_cumulative_totals.mp411.22MB
17. 231 - Use formulas to create interactive charts.mp411.25MB
18. 189-Creating_an_automatically_expanding_chart_by_basing_it_on_a_table.mp411.3MB
19. 219 - Use themes to adjust worksheet colors, fonts, and effects.mp411.3MB
20. 193-Adjusting_default_layouts_and_date_grouping_in_PivotTables.mp411.42MB
21. 245 - Work with time calculations in formulas.mp411.43MB
22. 113-Use_conditional_formatting_data_bars_to_accentuate_negative_data.mp411.45MB
23. 025-Creating_dynamic_charts_with_in_cell_conditional_formatting.mp411.49MB
24. 226 - Simplify the use of special characters and symbols.mp411.49MB
25. 132-Exploring_font_choices_not_found_on_the_Home_tab.mp411.68MB
26. 191-Using_the_Solver_Add-in.mp411.75MB
27. 211 - Using the CEILING and FLOOR functions for specialized rounding needs.mp411.87MB
28. 243 - Use worksheet names in formulas.mp411.93MB
29. 160-Converting_dates_like_20102006_into_usable_date_entries.mp411.98MB
30. 196-Use_the_TRIM_and_CLEAN_functions_to_remove_excess_spaces_and_non_printing_characters.mp411.98MB
31. 199-Identify_weekdays_and_weekend_days_in_data.mp411.98MB
32. 223 - Adjust Conditional Formatting rules by altering percentage breakpoints.mp412.08MB
33. 235 - Overcoming obstacles when working with dates in charts.mp412.15MB
34. 183-Control_table_formatting_with_custom_formats.mp412.2MB
35. 220 - Calculate loan payments and investments with PMT and FV functions.mp412.35MB
36. 205-Rapid_filtering_with_Filter_by_Selection.mp412.68MB
37. 204-Use_mixed_addresses_in_Excel_formulas.mp412.68MB
38. 197-Create_double_spaced_and_triple_spaced_printouts_while_repeating_column_headings.mp412.71MB
39. 250 - Highlight milestone data with conditional formatting and cumulative formulas.mp412.72MB
40. 188-Using_the_CONVERT_function_for_different_numbering_systems.mp412.85MB
41. 244 - Use data validation rules with special phone and social security formats.mp412.94MB
42. 212 - Inserting, reshaping, and formatting shapes - Rectangles, arrows, stars, and banners.mp412.95MB
43. 028-Custom_grouping_in_PivotTables.mp412.99MB
44. 242 - Use the Excel filtering capability for dates.mp413.11MB
45. 173-Extract_day_month_and_year_from_date_fields.mp413.15MB
46. 249 - Split or join columnar data with Text to Columns or Flash Fill.mp413.17MB
47. 174-Compare_data_with_EXACT_FIND_and_SEARCH_functions.mp413.17MB
48. 234 - Use column or row references to create dynamic formulas.mp413.23MB
49. 175-How_to_covert_ROMAN_numberals.mp413.25MB
50. 208 - Transposing data and using the TRANSPOSE function.mp413.41MB
51. 209 - Displaying gridlines, borders, and column_row headings when printing.mp413.46MB
52. 213 - Tabulating totals with the VLOOKUP function and array constants.mp413.51MB
53. 149-Work_with_formulas_and_formats_Decimals_and_fractions.mp413.53MB
54. 202-How_to_use_the_error_checking_rules_in_Excel.mp413.63MB
55. 224 - Discover new formula capabilities with new functions and dynamic arrays.mp413.68MB
56. 178-Conditional_formatting_based_on_date_proximity.mp413.75MB
57. 181-Avoid_the_#DIV_0_error_message.mp413.76MB
58. 246 - Prevent and locate duplicate worksheet entries.mp413.8MB
59. 253 - Create a powerful macro.mp413.8MB
60. 011-Exploring_various_layouts_for_your_PivotTable.mp413.86MB
61. 166-Use_the_COUNT_and_COUNTA_functions.mp413.93MB
62. 238 - Extract filtered data with data validation and the FILTER function.mp413.97MB
63. 164-Calculate_%_of_change.mp414MB
64. 217 - Format macros to make you a more efficient Excel user.mp414.5MB
65. 018-Parsing_your_data_to_create_unique_lists.mp414.51MB
66. 133-Using_WordArt_for_special_titles_and_headings.mp414.54MB
67. 190-Keystroke_shortcuts_using_the_Alt_key.mp414.71MB
68. 092-Activate_the_Speak_On_Enter_and_Speak_Cells_features.mp414.71MB
69. 163-NETWORKDAYS.INTL_and_WORKDAY.INTL.mp414.74MB
70. 230 - Insert colorful images with the People Graph Add-in.mp414.87MB
71. 221 - Use the XLOOKUP function to replace VLOOKUP.mp414.96MB
72. 248 - Display and highlight worksheet formula cells.mp415.23MB
73. 141-Combine_data_using_CONCATENATE_CONCAT_and_TEXTJOIN_functions_and_the_ampersand_(&)_character.mp415.23MB
74. 186-Sorting_by_moving_columns.mp415.31MB
75. 176-Use_range_names_for_more_readable_formulas.mp415.5MB
76. 182-Explore_formatting_options_not_available_on_the_Home_tab.mp415.55MB
77. 070-Replace_characters_by_position_with_the_REPLACE_function.mp415.57MB
78. 216 - Creating and updating sample data.mp415.74MB
79. 228 - Use the new RANDARRAY function that replaces RAND and RANDBETWEEN.mp415.76MB
80. 247 - Look forward and backward using EDATE and other date functions.mp415.84MB
81. 027-Converting_charts_into_pictures.mp415.93MB
82. 168-Rank_data_with_the_RANK_and_RANK.AVG_functions.mp416.11MB
83. 179-How_to_adjust_names.mp416.21MB
84. 232 - Track variables using the Scenario Manager.mp416.33MB
85. 004-Using_the_FORMULATEXT_and_N_functions_for_worksheet_and_formula_documentation.mp416.4MB
86. 184-Use_date_functions_for_age_and_tenure_calculations.mp416.43MB
87. 227 - Select from over 800 icons to enliven worksheets.mp416.46MB
88. 240 - Add formula tools and symbols to the Quick Access Toolbar.mp416.51MB
89. 100-Use_CHOOSE_to_calculate_fiscal_quarters_and_as_a_VLOOKUP_alternative.mp416.52MB
90. 165-Fill_in_all_blank_cells_within_a_range_with_either_formatting_or_data.mp416.56MB
91. 241 - Change PivotTable settings for titles and summaries.mp416.75MB
92. 233 - Get totals quickly without creating formulas.mp416.76MB
93. 094-Insert_a_text_box_or_shape_and_link_a_cells_content_to_it.mp416.99MB
94. 001-Hide_and_unhide_worksheets_securely.mp417.01MB
95. 130-Using_fill_effects_(gradients_patterns_styles)_for_colorful_cell_background_variations.mp417.05MB
96. 203-Protect_worksheets_and_lock_cells.mp417.24MB
97. 206-Display_large_values.mp417.27MB
98. 115-Copy_formats_quickly_using_dragging_techniques_and_the_Format_Painter.mp417.39MB
99. 060-Join_data_with_new_CONCAT_and_TEXTJOIN_functions.mp417.57MB
100. 143-Creating_cross-totals_with_the_SUMIFS_function_and_mixed_references.mp417.62MB
101. 218 - Use the new SORT and SORTBY functions to extract sorted lists.mp417.7MB
102. 044-Creating_text_numerical_date_and_time_lists_in_a_flash.mp417.89MB
103. 148-A_look_at_new_chart_types_in_Excel_2016_Waterfall.mp417.99MB
104. 167-Use_workbook_protection_to_prevent_use_of_sheet_commands.mp418MB
105. 081-Save_a_chart_as_a_template_use_the_template_for_current_or_new_charts.mp418.4MB
106. 146-Vertical_and_horizontal_alignment_settings_and_orientation_options.mp418.54MB
107. 154-Control_phone_number_and_postal_code_formats_using_built-in_options_or_customized_formats.mp418.7MB
108. 180-Differences_and_limitations_of_converting_data_to_a_table.mp418.78MB
109. 157-Create_on_screen_warnings_and_reminders_with_comments_or_data_validation_messages.mp418.79MB
110. 169-Use_nonstandard_fiscal_years_and_quarters_in_PivotTables.mp418.83MB
111. 065-Creating_a_pick_list_dependent_on_another_pick_list.mp418.84MB
112. 090-Calculate_faster_with_the_AutoSum_button_and_AutoSum_keystroke_shortcut.mp418.91MB
113. 207-Tracking_down_conditional_formatting_and_data_validation_rules.mp418.94MB
114. 038-Reducing_data_entry_time_by_expanding_AutoCorrect_options.mp418.99MB
115. 114-Creative_use_of_sparklines_in_merged_cells_with_axes.mp419.03MB
116. 155-Change_the_default_Excel_chart_type_create_and_use_chart_templates.mp419.14MB
117. 077-Create_dynamic_sheet_name_references_with_the_INDIRECT_function.mp419.17MB
118. 023-Highlighting_PivotTable_results_with_conditional_formatting.mp419.23MB
119. 201-Create_picture_links.mp419.23MB
120. 125-Tips_for_creating_charts_quickly.mp419.24MB
121. 088-Change_the_shape_of_comment_boxes_and_other_objects.mp419.25MB
122. 037-Adding_managing_and_printing_comments_for_worksheet_documentation.mp419.26MB
123. 075-Creating_an_Excel_template_to_simplify_updating_a_monthly_file.mp419.66MB
124. 042-Using_option_buttons_group_boxes_and_checkboxes_to_facilitate_forms_creation.mp419.93MB
125. 058-Adding_pictures_as_worksheet_backgrounds.mp420.05MB
126. 152-Use_the_INDIRECT_function_with_intersection_formulas_and_range_names_to_extract_data.mp420.16MB
127. 026-Enhancing_table_filtering_with_slicers.mp420.19MB
128. 066-Using_Command_Shortcuts_with_the_Alt_key.mp420.2MB
129. 121-AutoFill_shortcuts_for_date_series_one_two_week_intervals_EOM_and_formulas.mp420.22MB
130. 046-Handling_Dates_with_unusual_formats.mp420.25MB
131. 177-Customize_your_Quick_Access_Toolbar.mp420.33MB
132. 024-Customizing_templates_to_fit_your_unique_needs.mp420.37MB
133. 017-Dynamically_presenting_data_via_chart_slicers.mp420.42MB
134. 010-Applying_conditional_formatting_across_rows_of_data.mp420.45MB
135. 012-Splitting_and_consolidating_workbooks.mp420.53MB
136. 078-Avoid_errors_and_hidden_data_with_the_powerful_AGGREGATE_function.mp420.84MB
137. 022-Increasing_your_productivity_with_custom_lists.mp420.89MB
138. 040-Using_hyperlinks_for_rapidly_switching_worksheet_locations_and_jumping_to_websites.mp421.15MB
139. 198-Helpful_keystroke_shortcuts.mp421.27MB
140. 056-Drawing_borders_and_border_grids.mp421.36MB
141. 003-Using_list_boxes_and_combo_boxes_to_facilitate_forms_creation.mp421.48MB
142. 252 - Work with hidden and visible data in filtered and subtotaled lists.mp421.59MB
143. 031-Using_the_TREND_and_GROWTH_functions_for_projecting_future_results.mp421.61MB
144. 151-Control_gridlines_in_charts_and_on_worksheets.mp421.68MB
145. 035-Tracking_down_cell_dependencies_across_multiple_worksheets_and_workbooks.mp421.74MB
146. 239 - Use Find and Replace to change cell contents and formats.mp421.75MB
147. 033-Creating_colorful_3D_buttons_for_worksheet_annotation_and_macro_buttons.mp421.88MB
148. 153-Using_Expand_Collapse_on_PivotTables_and_PivotCharts_for_rapid_display_changes.mp421.95MB
149. 076-Calculate_dates_efficiently_using_Excels_hidden_function_DATEDIF.mp422.16MB
150. 124-Dealing_with_circular_errors.mp422.37MB
151. 158-Use_REPLACE_for_formats_only_for_data_only_or_for_both.mp422.46MB
152. 156-Create_your_own_customized_date_and_time_formats.mp422.5MB
153. 015-Using_Pick_list_VLOOKUP_Table_and_Chart_together.mp422.57MB
154. 170-Identify_or_extract_unique_entries_in_a_list.mp422.68MB
155. 082-MAXIFS_and_MINIFS_added_to_the_COUNTIF_SUMIF_family_of_functions.mp422.7MB
156. 172-Create_artistic_charts_with_random_numbers.mp422.72MB
157. 134-Creating_a_2_way_lookup_with_VLOOKUP_HLOOKUP_INDEX_and_MATCH_functions.mp422.8MB
158. 063-Creating_an_Excel_template.mp423.18MB
159. 013-Using_date_and_time_as_metrics_in_a_PivotTable.mp423.2MB
160. 021-Accelerating_Cut_Copy_Paste_and_Insert_tasks.mp423.25MB
161. 029-Quick_formatting_tips.mp423.4MB
162. 091-How_to_use_mixed_cell_references.mp423.41MB
163. 120-Summarize_data_from_different_worksheets_with_a_PivotTable.mp423.43MB
164. 032-Using_scroll_bars_and_spin_buttons_to_facilitate_forms_creation.mp423.56MB
165. 050-Create_an_Organization_Chart.mp423.64MB
166. 225 - Sort or filter data based on color font or cell color background.mp423.75MB
167. 005-Using_the_ISBLANK_and_COUNTBLANK_functions_to_check_for_and_tabulate_blank_cells.mp423.75MB
168. 053-Custom_formats_relative_addressing_and_conditional_formatting.mp423.95MB
169. 008-Becoming_more_productive_with_these_10_tiny_tips_you_ll_use_often.mp424.1MB
170. 118-Use_conditional_formatting_to_overcome_formatting_limitations_of_the_IF_function.mp424.14MB
171. 041-Controlling_worksheet_security_by_allowing_selected_users_to_edit_specific_cell_ranges.mp424.18MB
172. 122-Create_summary_statistics_using_COUNTIFS_SUMIFS_and_AVERAGEIFS_functions.mp424.27MB
173. 109-Use_the_dynamic_TODAY_and_NOW_functions_for_real_time_up_to_date_calculations.mp424.29MB
174. 108-Work_with_MAX_MAXIFS_LARGE_and_related_functions.mp424.59MB
175. 074-Use_a_data_form_for_data_entry_and_exploring_data.mp424.91MB
176. 097-How_to_create_frequency_tabulations_and_distributions.mp424.93MB
177. 020-Using_source_data_from_a_table_to_make_your_PivotTable_more_dynamic.mp424.96MB
178. 039-Setting_up_custom_views_for_quick_access_to_different_worksheet_displays.mp425.08MB
179. 135-Borders_and_gridlines_Exploring_variations_options_and_differences.mp425.12MB
180. 055-Adding_comments_and_shapes.mp425.43MB
181. 138-Use_data_validation_to_force_entries_to_be_uppercase_or_lowercase.mp425.51MB
182. 030-The_top_five_unknown_but_useful_keyboard_shortcuts.mp425.61MB
183. 106-Keyboard_shortcuts_for_numeric_formats.mp425.85MB
184. 083-Adjust_a_charts_source_data_and_adjust_its_series_order.mp425.94MB
185. 131-Working_with_multiple_worksheets_simultaneously.mp425.99MB
186. 069-Use_calcuated_fields_and_calculated_items_in_a_PivotTable.mp426.32MB
187. 064-Restoring_missing_column_titles.mp426.37MB
188. 171-Keyboard_mouse_and_command_techniques_for_viewing_worksheets.mp427.06MB
189. 059-ADDRESS_ROW_and_COLUMN_functions.mp427.67MB
190. 079-Hide_worksheets_row_columns_cells_and_other_Excel_elements.mp427.8MB
191. 002-Resizing_moving_copying_and_manipulating_charts_and_other_objects.mp427.85MB
192. 150-Creating_mixed_reference_formulas_converting_to_a_table_applying_conditional_formatting.mp427.89MB
193. 145-Using_the_AND_and_OR_functions_independently_or_within_IF_functions.mp427.92MB
194. 144-New_Excel_2016_chart_types_Histogram_Pareto_and_box_and_whisker.mp428.03MB
195. 043-Exploring_what_if_scenarios_using_Goal_Seek.mp428.04MB
196. 142-Recognizing_Excel_Formula_errors_#N_A_#VALUE!_#REF!_#DIV_0!_#NUM_and_#NAME!.mp428.07MB
197. 067-Set_up_a_macro_for_sorting_dynamic_data.mp428.18MB
198. 127-Using_the_INDEX_function_to_extract_data_by_row_and_column.mp428.38MB
199. 062-Locating_Data_Validation_rules_and_violations.mp428.54MB
200. 161-Create_custom_lists_for_letter_series_like_A_to_Z.mp428.74MB
201. 068-Use_random_number_functions_for_sorting_and_creating_sample_data.mp428.83MB
202. 080-Use_slicers_and_charts_together_for_dynamic_presentations.mp429.07MB
203. 111-Flip_the_left_to_right_order_of_columns_with_the_INDEX_function_or_sort_by_columns.mp429.38MB
204. 006-Summarizing_data_from_diverse_ranges_with_Data_Consolidate.mp429.4MB
205. 016-Using_formulas_to_calculate_values_across_worksheets_and_workbooks.mp429.52MB
206. 128-Displaying_tips_when_using_Excel_in_presentations.mp429.65MB
207. 034-Calculating_with_hours_minutes_and_times_of_day.mp429.66MB
208. 095-Use_Watch_Window_and_other_techniques_to_track_changing_cells.mp429.69MB
209. 103-Create_lists_of_all_2nd_Tuesdays_last_Tuesdays_and_other_date_series.mp429.79MB
210. 110-Use_the_FIND_MID_LEFT_and_RIGHT_functions.mp429.82MB
211. 104-Create_linkage_formulas_that_refer_to_other_worksheets_and_workbooks.mp429.86MB
212. 119-Use_VLOOKUP_MATCH_and_Index_functions_with_array_formulas.mp429.89MB
213. 071-Work_with_formulas_in_tables.mp430.04MB
214. 102-Use_the_SUBTOTAL_(and_AGGREGATE)_functions_to_avoid_double_counting.mp430.07MB
215. 136-New_Excel_2016_chart_types_Tree_map_and_sunburst.mp430.13MB
216. 162-Freeze_Panes_and_Split.mp430.26MB
217. 009-Transposing_data_and_charts_for_a_different_perspective_on_your_data.mp430.3MB
218. 129-The_Wrap_Text_Merge_and_Center_and_Indent_options.mp430.35MB
219. 054-Auditing.mp430.66MB
220. 045-Creating_Variable_Conditional_Formatting_Rules.mp430.8MB
221. 084-Use_the_OFFSET_function_for_tabulating_moving_data.mp430.82MB
222. 093-Accelerate_data_entry_with_five_special_techniques.mp431.52MB
223. 117-Tips_for_creating_and_updating_sample_data.mp431.62MB
224. 096-How_to_create_time_interval_entries.mp432.38MB
225. 116-Displaying_multiple_worksheets_and_workbooks_together.mp432.4MB
226. 147-Sorting_tips_and_shortcuts.mp432.43MB
227. 139-Avoiding_common_chart_distortions.mp432.73MB
228. 057-Building_a_flexible_monthly_table_using_functions.mp432.81MB
229. 073-Use_special_tricks_for_working_with_dates_in_Charts.mp433.2MB
230. 014-Adding_illustrations_to_your_workbook.mp433.62MB
231. 052-Using_Excels_error_checking_functions.mp433.63MB
232. 086-Use_Flash_Fill_to_rapidly_combine_or_separate_columnar_data.mp434.31MB
233. 126-Creating_heat_maps_using_conditional_formatting.mp434.91MB
234. 123-Use_custom_formulas_in_data_validation_to_keep_out_bad_data.mp435.01MB
235. 089-How_and_when_to_use_Excels_10_rounding_functions.mp435.35MB
236. 099-Solve_matching_issues_with_matching_phone_numbers_and_SS_numbers.mp435.66MB
237. 159-Create_numeric_formats_Display_in_thousands_or_millions.mp435.8MB
238. 101-Use_Outlining_tools_to_quickly_expand_and_collapse_data_for_dynamic_presentations.mp435.93MB
239. 098-Create_an_expanded_list_from_a_summary_using_PivotTable_techniques.mp436.74MB
240. 085-Alter_numeric_data_without_formulas.mp437.41MB
241. 047-Using_wildcards_(asterisk_tilde_and_question_mark).mp438.13MB
242. 105-Formula_nesting_Using_multiple_functions_in_the_same_formula.mp438.99MB
243. 048-Custom_formats_using_asterisk_semicolon_and_brackets.mp439.18MB
244. 036-Discovering_time_saving_tools_in_the_Go_To_Special_feature.mp440.04MB
245. 007-Using_check_boxes_and_Conditional_Formatting_together_for_dynamic_displays.mp440.45MB
246. 112-Working_with_hidden_data_when_copying_cells_and_creating_charts.mp440.93MB
247. 107-Use_the_Subtotal_command_to_analyze_data_with_single_or_multiple_level_subtotals.mp441.35MB
248. 137-Freezing_column_and_row_titles.mp441.85MB
249. 049-Meeting_unusual_filtering_needs_via_Advanced_Filter.mp447.68MB
250. 061-Working_with_formulas_in_tables.mp448.15MB
251. 051-Two_way_lookup_using_the_MATCH_and_INDEX_functions.mp450.68MB
252. 072-Keystroke_shortcuts_from_A_to_Z_using_the_Ctrl_key.mp455.03MB
253. 087-Adjust_banded_row_formatting.mp457.78MB