Excel Tip of the Week Index

Hello!  This is a central index of all the Excel Tip of the Week posts.  They are grouped together by topic, and then by level in the Spreadsheet Competency Framework.  Each post is linked back to this post to aid navigation.  Posts with an asterisk (*) are open to the public; other posts require an Excel Community membership.

Most recent post 316 - Conditional summary functions redux

Recommended tip from the archives *62 - Group, ungroup, subtotal

Posts by topic

Formulas

2 - TRIM and CLEAN 
7 - VLOOKUP 
8 - Comparisons 
*10 - UPPER, LOWER, PROPER 
11 - Formulas with fixed references 
*14 - CONCATENATE 
15 - AND, OR 
17 - IF and nested IFs 
21 - LEFT, RIGHT, MID 
23 - Evaluate Formula and formula auditing 

26 - LEN, FIND, SEARCH 
30 - Reviewing formulas 
32 - Naming cells 
34 - ADDRESS, INDIRECT 
*35 - SUM, COUNT, COUNTA, COUNTBLANK 
37 - Creating error checks 
42 - ROUND, ROUNDDOWN, ROUNDUP 
43 - SUMIF, COUNTIF, SUMIFS, COUNTIFS 
45 - FLOOR, CEILING, MROUND 
46 - ABS 

47 - NETWORKDAYS, WORKDAY 
*49 - Working with dates 
50 - INDEX MATCH 
*53 - Causes of formulas not calculating 
55 - Finding a function that fits 
56 - SUMPRODUCT 
*57 - RAND, RANDBETWEEN 
60 - DATE, EDATE, EOMONTH 
61 - TODAY, NOW 
63 - ROW, COLUMN, ROWS, COLUMNS 

*66 - IF revisited 
68 - MAX, MIN, LARGE, SMALL 
69 - HYPERLINK 
*70 - Revisiting CONCATENATE 
74 - Revisiting formulas with fixed references 
78 - Revisiting COUNTIF, SUMIF, COUNTIFS, SUMIFS 
85 - NPV, XNPV, IRR, XIRR 
*88 - SUBSTITUTE 
89 - NETWORKDAYS.INTL, WORKDAY.INTL 
90 - DATEDIF 

91 - Revisiting VLOOKUP 
*92 - Revisiting SUM, COUNT, COUNTA, COUNTBLANK 
93 - AutoSum 
95 - Revisiting naming cells and the Name Manager 
*96 - CHOOSE 
99 - Introduction to array formulas 
100 - Working with arrays 
103 - Loan-related functions 
104 - N, T 
106 - CELL, INFO 

107 - Formula Wizard 
*109 - Selecting a scenario 
112 - OFFSET 
117 - Revisiting INDEX MATCH 
*122 - Formatting using formulas 
*127 - AND / OR revisited 
*131 - Revisiting MAX, MIN, LARGE, SMALL 
132 - Revisiting ADDRESS, INDIRECT 
133 - "IS" functions 
137 - R1C1 reference style 

138 - When will I get paid? 
142 - How many distinct items? 
145 - RANK.AVG, RANK.EQ 
146 - Revisiting RAND, RANDBETWEEN
151 - SUBTOTAL, AGGREGATE 
152 - Nesting functions in the Formula Wizard 
154 - Database functions 
155 - Using INDEX MATCH with multiple variables 
158 - Revisiting formulas that aren't calculating 
161 - Revisiting text extraction functions 

*162 - Normal distribution functions 
165 - FORMULATEXT 
168 - VLOOKUP redux 
169 - Revisiting NPV, XNPV, IRR, XIRR 
172 - Rotating between values 
176 - Basic statistics functions 
*179 - GETPIVOTDATA 
182 - Revisiting array formulas 
*183 - FREQUENCY 
*188 - Named cells redux 

189 - Revisiting CHOOSE 
197 - WEEKDAY 
198 - Checking formulas in the formula bar 
200 - Fixed references redux
*201 - INDEX MATCH redux 
204 - Count items in common 
206 - CONVERT 
207 - Revisiting SUBSTITUTE 
208 - Revisiting TODAY, NOW 
*210 - Switching bases 

211 - Sorting with formulas 
212 - Revisiting TRIM 
213 - CODE, CHAR, UNICODE, UNICHAR 
224 - Revisiting SUMPRODUCT 
234 - Inserting a calculated value into a sentence 
236 - Creating numeric patterns 
238 - Revisiting working with dates 
245 - Finding the last number in a range 
246 - Modelling a simple loan 
247 - Causes of formulas not working 

*249 - Revisiting making dates with formulas 
252 - Revisiting rounding 
*253 - Calculating the effective interest rate 
254 - Rounding with totals 
259 - CONCATENATE redux 
261 - Revisiting AutoSum 
264 - Working day functions revisited 
265 - Revisiting DATEDIF 
267 - Creating dynamic ranges 
268 - Using quotation marks in formulas 

272 - Revisiting finding the function you need 
273 - IF redux 
*275 - Cross-sheet formulas 
*279 - Revisiting N, T 
280 - SUM and COUNT functions redux 
281 - Revisiting CELL and INFO 
282 - Spaces in formulas 
*288 - Revisiting database functions 
294 - Converting data stored as text 
295 - Binomial distribution functions

298 - Formula issues redux 
299 - Google Sheets: UNIQUE 
*301 - Google Sheets: SORT 
302 - Linear regression functions 
303 - Revisiting the Formula Wizard 
304 - Google Sheets: FILTER 
*305 - Revisiting counting distinct items 
308 - Google Sheets: QUERY 
*309 - Randomisation redux 
310 - Revisiting reviewing formulas 

*314 - ADDRESS and INDIRECT redux 
315 - Google Sheets: Counting distinct items 
316 - Conditional summary functions redux

Formatting & appearance

3 - Introduction to conditional formatting 
9 - Dropdowns 
16 - Data validation 
19 - Paste values 
41 - Precision as displayed 
52 - Quick formatting 
59 - Formatting and text formats 
65 - Merge, Wrap, Autofit 
71 - Introduction to charts 
73 - Revisiting conditional formatting 

76 - Quick charts and chart tabs 
*79 - Revisiting Tables 
87 - Sparklines 
*101 - Form controls 
108 - Revisiting Paste Special 
*114 - Revisiting data validation & creating dropdown menus 
115 - Style Manager 
125 - Too many cell formats 
126 - Transposing data 
129 - Hide gridlines 

135 - Why merging cells sucks 
*136 - Quick formatting for formulas, inputs, and blanks 
139 - Flipping the default direction 
143 - Conditional formatting: Colouring a whole row 
150 - Adding leading zeroes 
*153 - Conditional formatting: Layering multiple formats 
159 - Setting permissions for different ranges 
*171 - Using #N/A in charts 
177 - Formula-driven data validation 
185 - Revisiting sparklines 

*192 - Hidden rows and columns 
*214 - Line breaks in Excel
219 - Nested dropdowns
220 - Revisiting cell styles 
*227 - Making text fit 
228 - Paste Special redux 
233 - Why merging cells still sucks 
241 - Cell borders 
*244 - Revisiting Precision as Displayed 
251 - Cross-workbook Cell Styles 

269 - Revisiting form controls 
274 - Mixing multiple chart types 
276 - Conditional formatting redux 
*283 - Presenting text in a spreadsheet 
290 - Sparklines redux 
293 - Revisiting layering conditional formats 
297 - Revisiting using #N/A! in charts 
311 - Less-known formatting options

Data handling & analysis

*1 - Comparing lists
4 - Filters 
6 - Tables (basics) 
13 - Tables (further learning) 
*22 - Data tables 
24 - Remove duplicates 
28 - Text to columns 
29 - PivotTables (basics) 
36 - PivotTables (further learning) 
*40 - Goal Seek 

*62 - Group, ungroup, subtotal 
64 - Revisiting PivotTables 
77 - Revisiting Filters 
84 - Scenario Manager 
94 - Find & Replace 
97 - Working with imported data 
98 - Revisiting removing duplicates 
*105 - Revisiting Text to Columns 
113 - Revisiting Goal Seek 
116 - Solver Add-In 

119 - Revisiting Data Tables 
120 - Revisiting comparing lists 
134 - Making Benford's Law in Excel 
148 - Advanced Filter 
160 - Revisiting the subtotal feature 
163 - Tables redux, part 1 
164 - Tables redux, part 2 
*175 - The Excel Data Model 
190 - PivotTables redux 
*196 - Splitting data by category 

203 - Consolidate 
*218 - Data tables redux 
225 - Finding items that add to a target 
232 - Analysing frequency tables 
239 - How to standardise data 
250 - Goal Seek redux 
255 - Power Query: Combining multiple tables 
256 - Power Query: Three-way matching 
*257 - Revisiting the Solver Add-In 
278 - Using data tables for Monte Carlo simulations 

285 - FIFO and LIFO templates 
287 - Revisiting Advanced Filter 
289 - Floating point errors 
291 - Making a set of PivotTables 
*292 - Wildcards 
*296 - Data validation redux

Shortcuts & efficiency of use

*5 - Quick navigation shortcuts 
12 - Shortcuts for copying and pasting 
*18 - View shortcuts 
25 - Data entry shortcuts 
58 - General efficiency tips 
72 - Copy and paste errors 
*83 - Revisiting navigation shortcuts 
102 - Go To & Go To Special 
110 - Grouping sheets 
123 - Improving workbook viewing 

124 - Flash fill 
*149 - Shortcuts: Working with rows and columns 
181 - More fill options 
186 - Quick navigation: Precedents 
199 - Zooming 
202 - Inserting hyperlinks 
*205 - Deletion vs clearing 
221 - AutoCorrect 
226 - Breaking links 
229 - Alt key shortcutting 

*235 - Shortcuts from a financial modeller 
*266 - Keyboard shortcuts advent calendar 
*270 - Function key shortcuts 
284 - Revisiting Go To and Go To Special 
286 - Revisiting grouping ranges 
300 - Copying worksheet tabs 
313 - Revisiting find & replace

VBA & macros

33 - Introduction to macros 
81 - How to install macros and custom functions 
82 - Useful macros and custom functions 
121 - Further dropdown menu approaches 
141 - VBA case study - SDLT 
*144 - VBA case study - Factor and loops 
147 - VBA case study - StringConcat and 'For Each' 
*166 - VBA Case Study: Creating scenarios 
174 - VBA Case Study: Timestamps 
178 - VBA Case Study: Always open on landing page 

180 - Migrating VBA projects to 64-bit Excel 
187 - VBA Case Study: TESTSUM and arrays 
193 - VBA Case Study: DICETRAY and testing VBA code 
194 - VBA Case Study: DICETRAY 2 and Split 
215 - MsgBox and InputBox 
217 - Games compendium 
*223 - VBA Case Study: Splitting a workbook by sheet 
*231 - VBA Case Study: Text handling and COMMAEXTRACT 
242 - Revisiting installing macros and custom functions 
243 - VBA Case Study: Calculating on cells of a given colour 

248 - VBA Case Study: Ordinals (1st, 2nd, 43rd) 
260 - VBA case study: FactorList and collections 
263 - VBA case study: Making a hyperlinked table of contents 
271 - VBA case study: Organising collections and fair sharing 
277 - VBA case study: Inserting formulas into cells 
306 - VBA case study: Creating filtered reports 
307 - VBA case study: Protecting or hiding sheets

Spreadsheet administration & best practice

20 - Securing workbooks 
*27 - Excel options 
*31 - Printing and print preview 
38 - Print titles 
39 - Comments 
*44 - Is a spreadsheet the right tool? 
48 - Backup and version control 
51 - Circular references 
54 - Sharing workbooks 
67 - Automatic vs. manual calculation 

*75 - Working with multiple screens 
80 - Revisiting printing 
86 - 20 Principles 1 year on 
111 - Guide to naming spreadsheets 
*118 - Adding and removing passwords 
128 - Creating a good landing page 
130 - Customising the Ribbon and the Quick Access Toolbar 
*140 - Launch of the Spreadsheet Competency Framework 
156 - Designing for longevity 
*157 - Printing redux 

167 - Revisiting calculation settings 
170 - Spell checking 
173 - Hidden worksheets 
184 - Revisiting circular references 
191 - Twenty Principles for Good Spreadsheet Practice - Three years on 
195 - Spreadsheet Competency Framework: One year on 
209 - Inking 
216 - Revisiting passwords
222 - Revisiting working with multiple screens 
230 - Revisiting Excel options 

237 - Resetting overgrown worksheets 
*240 - 6 things Google Sheets does better than Excel 
258 - A guide to common Excel file types
*262 - Screenshots 
312 - Revisiting customising the Ribbon and Quick Access Toolbar

Posts by level in the Spreadsheet Competency Framework

Basic User

4 - Filters 
*5 - Quick navigation shortcuts 
12 - Shortcuts for copying and pasting 
19 - Paste values 
25 - Data entry shortcuts 
*31 - Printing and print preview 
58 - General efficiency tips 
*75 - Working with multiple screens 
77 - Revisiting Filters 
80 - Revisiting printing 

*83 - Revisiting navigation shortcuts 
93 - AutoSum 
94 - Find & Replace 
108 - Revisiting Paste Special 
129 - Hide gridlines 
139 - Flipping the default direction 
*149 - Shortcuts: Working with rows and columns 
*157 - Printing redux 
170 - Spell checking 
181 - More fill options 

186 - Quick navigation: Precedents 
191 - Twenty Principles for Good Spreadsheet Practice - Three years on 
*192 - Hidden rows and columns 
195 - Spreadsheet Competency Framework: One year on 
199 - Zooming 
202 - Inserting hyperlinks 
*205 - Deletion vs clearing 
209 - Inking 
*214 - Line breaks in Excel 
221 - AutoCorrect

222 - Revisiting working with multiple screens 
*227 - Making text fit 
228 - Paste Special redux 
229 - Alt key shortcutting 
230 - Revisiting Excel options 
*235 - Shortcuts from a financial modeller 
*240 - 6 things Google Sheets does better than Excel 
258 - A guide to common Excel file types 
261 - Revisiting AutoSum
*262 - Screenshots 

*266 - Keyboard shortcuts advent calendar 
*270 - Function key shortcuts 
*283 - Presenting text in a spreadsheet 
286 - Revisiting grouping ranges 
300 - Copying worksheet tabs 
311 - Less-known formatting options 
313 - Revisiting find & replace

General User

*1 - Comparing lists 
2 - TRIM and CLEAN 
6 - Tables (basics) 
7 - VLOOKUP 
8 - Comparisons 
*10 - UPPER, LOWER, PROPER 
11 - Formulas with fixed references 
*14 - CONCATENATE 
*18 - View shortcuts 
24 - Remove duplicates 

*27 - Excel options
*35 - SUM, COUNT, COUNTA, COUNTBLANK 
38 - Print titles 
39 - Comments 
41 - Precision as displayed 
42 - ROUND, ROUNDDOWN, ROUNDUP 
*44 - Is a spreadsheet the right tool? 
45 - FLOOR, CEILING, MROUND 
46 - ABS 
48 - Backup and version control 

50 - INDEX MATCH 
52 - Quick formatting 
*57 - RAND, RANDBETWEEN 
59 - Formatting and text formats 
*62 - Group, ungroup, subtotal 
65 - Merge, Wrap, Autofit 
*66 - IF revisited 
68 - MAX, MIN, LARGE, SMALL 
*70 - Revisiting CONCATENATE 
71 - Introduction to charts 

74 - Revisiting formulas with fixed references 
76 - Quick charts and chart tabs 
86 - 20 Principles 1 year on 
*88 - SUBSTITUTE 
91 - Revisiting VLOOKUP 
*92 - Revisiting SUM, COUNT, COUNTA, COUNTBLANK 
98 - Revisiting removing duplicates 
104 - N, T 
107 - Formula Wizard 
110 - Grouping sheets 

111 - Guide to naming spreadsheets 
115 - Style Manager 
117 - Revisiting INDEX MATCH 
*122 - Formatting using formulas 
123 - Improving workbook viewing 
126 - Transposing data 
*127 - AND / OR revisited 
*131 - Revisiting MAX, MIN, LARGE, SMALL 
133 - "IS" functions 
135 - Why merging cells sucks 

137 - R1C1 reference style 
140 - Launch of the Spreadsheet Competency Framework 
150 - Adding leading zeroes 
152 - Nesting functions in the Formula Wizard 
160 - Revisiting the subtotal feature 
163 - Tables redux, part 1 
165 - FORMULATEXT 
168 - VLOOKUP redux 
176 - Basic statistics functions 
200 - Fixed references redux 

*201 - INDEX MATCH redux 
206 - CONVERT 
207 - Revisiting SUBSTITUTE
*210 - Switching bases 
212 - Revisiting TRIM 
213 - CODE, CHAR, UNICODE, UNICHAR 
220 - Revisiting cell styles 
226 - Breaking links 
233 - Why merging cells still sucks 
234 - Inserting a calculated value into a sentence 

237 - Resetting overgrown worksheets 
241 - Cell borders 
*244 - Revisiting Precision as Displayed 
247 - Causes of formulas not working 
251 - Cross-workbook Cell Styles 
252 - Revisiting rounding 
254 - Rounding with totals 
259 - CONCATENATE redux 
268 - Using quotation marks in formulas 
273 - IF redux 

*275 - Cross-sheet formulas 
*279 - Revisiting N, T 
280 - SUM and COUNT functions redux 
282 - Spaces in formulas 
289 - Floating point errors 
*292 - Wildcards 
294 - Converting data stored as text 
299 - Google Sheets: UNIQUE 
*301 - Google Sheets: SORT 
303 - Revisiting the Formula Wizard

Creator

3 - Introduction to conditional formatting 
9 - Dropdowns 
13 - Tables (further learning) 
15 - AND, OR 
16 - Data validation 
17 - IF and nested IFs 
20 - Securing workbooks 
21 - LEFT, RIGHT, MID 
23 - Evaluate Formula and formula auditing 
26 - LEN, FIND, SEARCH 

29 - PivotTables (basics) 
30 - Reviewing formulas 
32 - Naming cells 
36 - PivotTables (further learning) 
37 - Creating error checks 
*40 - Goal Seek 
43 - SUMIF, COUNTIF, SUMIFS, COUNTIFS 
47 - NETWORKDAYS, WORKDAY
*49 - Working with dates 
51 - Circular references 

*53 - Causes of formulas not calculating 
55 - Finding a function that fits 
56 - SUMPRODUCT 
60 - DATE, EDATE, EOMONTH 
61 - TODAY, NOW 
63 - ROW, COLUMN, ROWS, COLUMNS 
64 - Revisiting PivotTables 
69 - HYPERLINK 
72 - Copy and paste errors 
73 - Revisiting conditional formatting 

78 - Revisiting COUNTIF, SUMIF, COUNTIFS, SUMIFS 
*79 - Revisiting Tables 
85 - NPV, XNPV, IRR, XIRR 
87 - Sparklines 
89 - NETWORKDAYS.INTL, WORKDAY.INTL 
90 - DATEDIF 
95 - Revisiting naming cells and the Name Manager 
*96 - CHOOSE 
102 - Go To & Go To Special 
103 - Loan-related functions 

*105 - Revisiting Text to Columns 
106 - CELL, INFO 
112 - OFFSET 
113 - Revisiting Goal Seek 
*114 - Revisiting data validation & creating dropdown menus 
*118 - Adding and removing passwords 
120 - Revisiting comparing lists 
124 - Flash fill 
125 - Too many cell formats 
128 - Creating a good landing page 

130 - Customising the Ribbon and the Quick Access Toolbar 
*136 - Quick formatting for formulas, inputs, and blanks 
145 - RANK.AVG, RANK.EQ 
146 - Revisiting RAND, RANDBETWEEN
148 - Advanced Filter
 
151 - SUBTOTAL, AGGREGATE 
*153 - Conditional formatting: Layering multiple formats 
154 - Database functions 
155 - Using INDEX MATCH with multiple variables 
158 - Revisiting formulas that aren't calculating 

159 - Setting permissions for different ranges 
161 - Revisiting text extraction functions 
*162 - Normal distribution functions 
164 - Tables redux, part 2 
*171 - Using #N/A in charts 
172 - Rotating between values 
173 - Hidden worksheets 
177 - Formula-driven data validation 
*179 - GETPIVOTDATA 
184 - Revisiting circular references 

185 - Revisiting sparklines 
*188 - Named cells redux 
189 - Revisiting CHOOSE 
190 - PivotTables redux 
*196 - Splitting data by category 
197 - WEEKDAY 
198 - Checking formulas in the formula bar 
208 - Revisiting TODAY, NOW 
211 - Sorting with formulas
216 - Revisiting passwords

219 - Nested dropdowns 
224 - Revisiting SUMPRODUCT 
232 - Analysing frequency tables 
236 - Creating numeric patterns 
238 - Revisiting working with dates 
239 - How to standardise data 
245 - Finding the last number in a range 
246 - Modelling a simple loan 
*249 - Revisiting making dates with formulas 
250 - Goal Seek redux 

*253 - Calculating the effective interest rate 
255 - Power Query: Combining multiple tables 
256 - Power Query: Three-way matching 
264 - Working day functions revisited 
265 - Revisiting DATEDIF 
267 - Creating dynamic ranges 
272 - Revisiting finding the function you need 
274 - Mixing multiple chart types 
276 - Conditional formatting redux 
281 - Revisiting CELL and INFO 

284 - Revisiting Go To and Go To Special 
285 - FIFO and LIFO templates 
287 - Revisiting Advanced Filter 
*288 - Revisiting database functions 
290 - Sparklines redux 
291 - Making a set of PivotTables 
293 - Revisiting layering conditional formats 
295 - Binomial distribution functions 
*296 - Data validation redux 
297 - Revisiting using #N/A! in charts

298 - Formula issues redux 
302 - Linear regression functions 
304 - Google Sheets: FILTER 
*305 - Revisiting counting distinct items 
308 - Google Sheets: QUERY 
*309 - Randomisation redux 
310 - Revisiting reviewing formulas 
312 - Revisiting customising the Ribbon and Quick Access Toolbar 
315 - Google Sheets: Counting distinct items 
316 - Conditional summary functions redux

Developer

*22 - Data tables 
28 - Text to columns
33 - Introduction to macros 
34 - ADDRESS, INDIRECT 
54 - Sharing workbooks 
67 - Automatic vs. manual calculation 
81 - How to install macros and custom functions 
82 - Useful macros and custom functions 
84 - Scenario Manager 
97 - Working with imported data 

99 - Introduction to array formulas 
100 - Working with arrays 
*101 - Form controls 
*109 - Selecting a scenario 
116 - Solver Add-In 
119 - Revisiting Data Tables 
121 - Further dropdown menu approaches 
132 - Revisiting ADDRESS, INDIRECT 
134 - Making Benford's Law in Excel 
138 - When will I get paid? 

141 - VBA case study - SDLT 
142 - How many distinct items? 
143 - Conditional formatting: Colouring a whole row 
*144 - VBA case study - Factor and loops
147 - VBA case study - StringConcat and 'For Each' 
156 - Designing for longevity 
*166 - VBA Case Study: Creating scenarios 
167 - Revisiting calculation settings 
169 - Revisiting NPV, XNPV, IRR, XIRR 
174 - VBA Case Study: Timestamps 

*175 - The Excel Data Model 
178 - VBA Case Study: Always open on landing page 
180 - Migrating VBA projects to 64-bit Excel 
182 - Revisiting array formulas 
*183 - FREQUENCY 
187 - VBA Case Study: TESTSUM and arrays 
193 - VBA Case Study: DICETRAY and testing VBA code 
194 - VBA Case Study: DICETRAY 2 and Split 
203 - Consolidate 
204 - Count items in common 

215 - MsgBox and InputBox 
217 - Games compendium 
*218 - Data tables redux 
*223 - VBA Case Study: Splitting a workbook by sheet 
225 - Finding items that add to a target 
*231 - VBA Case Study: Text handling and COMMAEXTRACT 
242 - Revisiting installing macros and custom functions 
243 - VBA Case Study: Calculating on cells of a given colour 
248 - VBA Case Study: Ordinals (1st, 2nd, 43rd) 
*257 - Revisiting the Solver Add-In 

260 - VBA case study: FactorList and collections 
263 - VBA case study: Making a hyperlinked table of contents 
269 - Revisiting form controls 
271 - VBA case study: Organising collections and fair sharing 
277 - VBA case study: Inserting formulas into cells 
278 - Using data tables for Monte Carlo simulations 
306 - VBA case study: Creating filtered reports 
307 - VBA case study: Protecting or hiding sheets 
*314 - ADDRESS and INDIRECT redux

Anonymous