Hello! This is a central index of all the Excel Tip of the Week posts. They are listed in chronological order, by level in the Spreadsheet Competency Framework, and by topic area. Each post is linked back to this post to aid navigation.
All posts in order(posts marked with * appear in IT Counts and do not require community membership)
*1 - Comparing lists 2 - TRIM and CLEAN 3 - Introduction to conditional formatting 4 - Filters *5 - Quick navigation shortcuts 6 - Tables (basics) 7 - VLOOKUP 8 - Comparisons 9 - Dropdowns *10 - UPPER, LOWER, PROPER
11 - Formulas with fixed references 12 - Shortcuts for copying and pasting 13 - Tables (further learning) *14 - CONCATENATE 15 - AND, OR 16 - Data validation 17 - IF and nested IFs *18 - View shortcuts 19 - Paste values 20 - Securing workbooks
21 - LEFT, RIGHT, MID *22 - Data tables 23 - Evaluate Formula and formula auditing 24 - Remove duplicates 25 - Data entry shortcuts 26 - LEN, FIND, SEARCH *27 - Excel options 28 - Text to columns 29 - PivotTables (basics) 30 - Reviewing formulas
*31 - Printing and print preview 32 - Naming cells 33 - Introduction to macros 34 - ADDRESS, INDIRECT *35 - SUM, COUNT, COUNTA, COUNTBLANK 36 - PivotTables (further learning) 37 - Creating error checks 38 - Print titles 39 - Comments *40 - Goal Seek
41 - Precision as displayed 42 - ROUND, ROUNDDOWN, ROUNDUP 43 - SUMIF, COUNTIF, SUMIFS, COUNTIFS *44 - Is a spreadsheet the right tool? 45 - FLOOR, CEILING, MROUND 46 - ABS 47 - NETWORKDAYS, WORKDAY 48 - Backup and version control *49 - Working with dates 50 - INDEX MATCH
51 - Circular references 52 - Quick formatting *53 - Causes of formulas not calculating 54 - Sharing workbooks 55 - Finding a function that fits 56 - SUMPRODUCT *57 - RAND, RANDBETWEEN 58 - General efficiency tips 59 - Formatting and text formats 60 - DATE, EDATE, EOMONTH
61 - TODAY, NOW *62 - Group, ungroup, subtotal 63 - ROW, COLUMN, ROWS, COLUMNS 64 - Revisiting PivotTables 65 - Merge, Wrap, Autofit *66 - IF revisited 67 - Automatic vs. manual calculation 68 - MAX, MIN, LARGE, SMALL 69 - HYPERLINK *70 - Revisiting CONCATENATE
71 - Introduction to charts 72 - Copy and paste errors 73 - Revisiting conditional formatting 74 - Revisiting formulas with fixed references *75 - Working with multiple screens 76 - Quick charts and chart tabs 77 - Revisiting Filters 78 - Revisiting COUNTIF, SUMIF, COUNTIFS, SUMIFS *79 - Revisiting Tables 80 - Revisiting printing
81 - How to install macros and custom functions 82 - Useful macros and custom functions *83 - Revisiting navigation shortcuts 84 - Scenario Manager 85 - NPV, XNPV, IRR, XIRR 86 - 20 Principles 1 year on 87 - Sparklines *88 - SUBSTITUTE 89 - NETWORKDAYS.INTL, WORKDAY.INTL 90 - DATEDIF
91 - Revisiting VLOOKUP *92 - Revisiting SUM, COUNT, COUNTA, COUNTBLANK 93 - AutoSum 94 - Find & Replace 95 - Revisiting naming cells and the Name Manager *96 - CHOOSE 97 - Working with imported data 98 - Revisiting removing duplicates 99 - Introduction to array formulas 100 - Working with arrays
*101 - Form controls 102 - Go To & Go To Special 103 - Loan-related functions 104 - N, T *105 - Revisiting Text to Columns 106 - CELL, INFO 107 - Formula Wizard 108 - Revisiting Paste Special *109 - Selecting a scenario 110 - Grouping sheets
111 - Guide to naming spreadsheets 112 - OFFSET 113 - Revisiting Goal Seek *114 - Revisiting data validation & creating dropdown menus 115 - Style Manager 116 - Solver Add-In 117 - Revisiting INDEX MATCH *118 - Adding and removing passwords 119 - Revisiting Data Tables 120 - Revisiting comparing lists
121 - Further dropdown menu approaches *122 - Formatting using formulas 123 - Improving workbook viewing 124 - Flash fill 125 - Too many cell formats 126 - Transposing data *127 - AND / OR revisited 128 - Creating a good landing page 129 - Hide gridlines 130 - Customising the Ribbon and the Quick Access Toolbar *131 - Revisiting MAX, MIN, LARGE, SMALL 132 - Revisiting ADDRESS, INDIRECT 133 - "IS" functions 134 - Making Benford's Law in Excel 135 - Why merging cells sucks *136 - Quick formatting for formulas, inputs, and blanks 137 - R1C1 reference style 138 - When will I get paid? 139 - Flipping the default direction *140 - Launch of the Spreadsheet Competency Framework
141 - VBA case study - SDLT 142 - How many distinct items? 143 - Conditional formatting: Colouring a whole row *144 - VBA case study - Factor and loops 145 - RANK.AVG, RANK.EQ 146 - Revisiting RAND, RANDBETWEEN 147 - VBA case study - StringConcat and 'For Each' 148 - Advanced Filter *149 - Shortcuts: Working with rows and columns 150 - Adding leading zeroes
151 - SUBTOTAL, AGGREGATE 152 - Nesting functions in the Formula Wizard *153 - Conditional formatting: Layering multiple formats 154 - Database functions 155 - Using INDEX MATCH with multiple variables 156 - Designing for longevity *157 - Printing redux 158 - Revisiting formulas that aren't calculating 159 - Setting permissions for different ranges 160 - Revisiting the subtotal feature
161 - Revisiting text extraction functions *162 - Normal distribution functions 163 - Tables redux, part 1 164 - Tables redux, part 2 165 - FORMULATEXT *166 - VBA Case Study: Creating scenarios 167 - Revisiting calculation settings 168 - VLOOKUP redux 169 - Revisiting NPV, XNPV, IRR, XIRR 170 - Spell checking
*171 - Using #N/A in charts 172 - Rotating between values 173 - Hidden worksheets 174 - VBA Case Study: Timestamps *175 - The Excel Data Model 176 - Basic statistics functions 177 - Formula-driven data validation 178 - VBA Case Study: Always open on landing page *179 - GETPIVOTDATA 180 - Migrating VBA projects to 64-bit Excel
181 - More fill options 182 - Revisiting array formulas *183 - FREQUENCY 184 - Revisiting circular references 185 - Revisiting sparklines 186 - Quick navigation: Precedents 187 - VBA Case Study: TESTSUM and arrays *188 - Named cells redux 189 - Revisiting CHOOSE 190 - PivotTables redux
191 - Twenty Principles for Good Spreadsheet Practice - Three years on *192 - Hidden rows and columns 193 - VBA Case Study: DICETRAY and testing VBA code 194 - VBA Case Study: DICETRAY 2 and Split 195 - Spreadsheet Competency Framework: One year on *196 - Splitting data by category 197 - WEEKDAY 198 - Checking formulas in the formula bar 199 - Zooming 200 - Fixed references redux
*201 - INDEX MATCH redux 202 - Inserting hyperlinks 203 - Consolidate 204 - Count items in common *205 - Deletion vs clearing 206 - CONVERT 207 - Revisiting SUBSTITUTE 208 - Revisiting TODAY, NOW 209 - Inking *210 - Switching bases
211 - Sorting with formulas 212 - Revisiting TRIM 213 - CODE, CHAR, UNICODE, UNICHAR *214 - Line breaks in Excel 215 - MsgBox and InputBox 216 - Revisiting passwords 217 - Games compendium *218 - Data tables redux 219 - Nested dropdowns 220 - Revisiting cell styles
221 - AutoCorrect 222 - Revisiting working with multiple screens *223 - VBA Case Study: Splitting a workbook by sheet 224 - Revisiting SUMPRODUCT 225 - Finding items that add to a target 226 - Breaking links *227 - Making text fit 228 - Paste Special redux 229 - Alt key shortcutting 230 - Revisiting Excel options
*231 - VBA Case Study: Text handling and COMMAEXTRACT 232 - Analysing frequency tables 233 - Why merging cells still sucks 234 - Inserting a calculated value into a sentence *235 - Shortcuts from a financial modeller 236 - Creating numeric patterns 237 - Resetting overgrown worksheets 238 - Revisiting working with dates 239 - How to standardise data *240 - 6 things Google Sheets does better than Excel
241 - Cell borders 242 - Revisiting installing macros and custom functions 243 - VBA Case Study: Calculating on cells of a given colour *244 - Revisiting Precision as Displayed 245 - Finding the last number in a range 246 - Modelling a simple loan 247 - Causes of formulas not working 248 - VBA Case Study: Ordinals (1st, 2nd, 43rd) *249 - Revisiting making dates with formulas 250 - Goal Seek redux
251 - Cross-workbook Cell Styles 252 - Revisiting rounding *253 - Calculating the effective interest rate of an investment 254 - Rounding with totals 255 - Power Query: Combining multiple tables 256 - Power Query: Three-way matching *257 - Revisiting the Solver Add-In 258 - A guide to common Excel file types 259 - CONCATENATE redux 260 - VBA case study: FactorList and collections
261 - Revisiting AutoSum*262 - Screenshots 263 - VBA case study: Making a hyperlinked table of contents
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 AutoSum262 - Screenshots
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 options35 - 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 SUBSTITUTE210 - 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
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, WORKDAY49 - 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
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
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 redux201 - 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
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
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
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
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
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 types262 - Screenshots
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?