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 290 - Sparklines redux
Recommended tip from the archives *40 - Goal Seek
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
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 Excel219 - Nested dropdowns220 - 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
Data handling & analysis
*1 - Comparing lists4 - 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
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
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
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 passwords222 - 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
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
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
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, RANDBETWEEN148 - 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 formulas216 - 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
Developer
*22 - Data tables 28 - Text to columns33 - 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 loops147 - 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
Are you an Excel Community member? ITCounts is a public blog but Excel Community is subscription-based. If you are a member, log in to view the ExCom content.
If you find any ITCounts links that aren't working please let me know and I will correct them.
more specifically the links starting with "https://ion.icaew.com/excelcommunity/" are not working. the ones starting with "https://ion.icaew.com/itcounts" do work. but only some of the tips are correctly linked...
No - I just tested a sample of links and they all work properly. Which were you trying?
Is it normal that none of the links are working and I constantly get a "Page could not be found" error message?