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.
Latest post
Posts by topic
Formulas
Tips 1 - 100
- 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
Tips 101 - 200
- 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
Tips 201 - 300
- 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
Tips 301 - 400
- 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
- 317 - Basic arithmetic functions
- 319 - Revisiting selecting a scenario
- 320 - Revisiting OFFSET
- 322 - Quartiles and percentiles
- 324 - Introduction to formulas and functions
- 326 - Revisiting loan functions
- 327 - Introduction to dynamic array functions
- 329 - CONCAT, TEXTJOIN
- 330 - IFS, SWITCH
- 333 - Revisiting GETPIVOTDATA
- 334 - Maximum and minimum functions redux
- 336 - Why is there an @ in my Excel formula?
- 337 - XLOOKUP
- 340 - Calculating tiered percentages
- 341 - Text extraction functions redux
- 342 - Revisiting HYPERLINK
- 343 - SUMPRODUCT redux
- 345 - Spilling errors with dynamic arrays
- 346 - Revisiting case-changing functions
- 349 - Rounding redux
- 352 - Keyboard shortcuts for entering formulas
- 353 - Audit sampling templates: Dynamic arrays
- 354 - REPLACE
- 356 - Audit sampling templates: Traditional formulas
- 358 - Revisiting FREQUENCY
- 371 - Revisiting ROW(S) and COLUMN(S) functions
- 372 - Creating a depreciation schedule
- 373 - The 12 formulas of Christmas
- 376 - Discounting functions redux
- 383 - Revisiting ranking functions
- 390 - Advanced uses of INDEX
- 391 - Comparing the lookup functions
- 393 - AND/OR redux
- 396 - Revisiting showing formulas as text
Tips 401 - 500
- 405 - DATEDIF redux
- 407 - Ranking within a group
- 422 - Better error catching formulas
- 429 - Revisiting cross-sheet formulas
- 431 - SUBSTITUTE redux
- 432 - Date-creating formulas redux
- 435 - Working with times
- 436 - Trimming excess spaces redux!
- 437 - Revisiting dynamic arrays
- 443 - Next level FILTERing (The FILTER Function)
- 444 - Revisiting XLOOKUP
- 449 - Let LET simplify formulas
- 450 - Revisiting Custom Data Validation
- 452 - Named Ranges Refreshed
- 459 - Examples of using LAMBDA() in Excel
- 462 - New text manipulation functions - TEXTBEFORE and TEXTAFTER
- 467 - Excel functions that do not return arrays or ranges
Formatting & appearance
Tips 1 - 100
- 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
Tips 101 - 200
- 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
Tips 201 - 300
- 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
Tips 301 - 400
- 311 - Less-known formatting options
- 325 - Revisiting quick formatting
- 332 - Revisiting number formatting
- 335 - Dealing with objects
- 360 -Revisiting typing special characters
- 381 -Treemap & Sunburst charts
- 395 - Revisiting hidden rows and columns
Tips 401 - 500
Data handling & analysis
Tips 1 - 100
- 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
Tips 101 - 200
- 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
Tips 201 - 300
- 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
Tips 301 - 400
- 318 - Power Query: Unpivoting
- 323 - Creating a trial balance from accounting data
- 339 - Power Query: Filtering for a date range
- 344 - Power Query: Getting started with web queries
- 348 - Fixing badly formatted dates
- 350 - Power Query: Creating a ranking
- 351 - Non-linear regression
- 355 - Connecting Google Sheets and Excel
- 357 - Removing duplicates redux
- 363 - Subtotal feature redux
- 364 - Dynamic arrays: Creating a visual work schedule
- 365 - Revisiting finding items that add to a target
- 367 - PivotCharts
- 368 - Power Pivot: Connections
- 369 - Power Pivot: Measures
- 378 - Revisiting splitting data by category
- 379 - Processing survey results
- 384 - Data types
- 385 - Power Query: Creating transaction references
- 386 - Power Pivot - RELATEDTABLE
- 387 - Revisiting fixing broken links
- 388 - Power Pivot: Filtering measures
- 389 - Power Query Dropping Rows and Columns
- 392 - Text to Columns redux
- 397 - Power Query - Looking up charge-out rates
- 399 - Power pivot - KPIs
Tips 401 - 500
- 403 - Power Query: Merge Kinds
- 406 - Power Pivot: Filtering active contracts
- 413 - Flattening and unfolding data
- 417 - Custom data types
- 420 - STOCKHISTORY and exchange rates
- 421 - Comparing lists redux
- 424 - Filters redux
- 425 - Revisiting unpivoting
- 427 - Power Query: Word frequency analysis
- 428 - Converting between statistical measures
- 434 - Revisiting making sets of PivotTables
- 439 - Power Pivot: Create a running total
- 448 - A distinct count conundrum
- 451 - Sparklines refreshed
- 455 - Data conversion
- 456 - Revisting loan modelling
Shortcuts & efficiency of use
Tips 1 - 100
- 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
Tips 101 - 200
- 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
Tips 201 - 300
- 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
Tips 301 - 400
- 313 - Revisiting find & replace
- 328 - Navigation shortcuts redux
- 361 - How to do data entry
- 362 - The Status Bar
- 375 - Workbook viewing redux
- 380 - AutoSum redux
Tips 401 - 500
VBA & macros
Tips 1 - 100
- 33 - Introduction to macros
- 81 - How to install macros and custom functions
- 82 - Useful macros and custom functions
Tips 101 - 200
- 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
Tips 201 - 300
- 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
Tips 301 - 400
- 306 - VBA case study: Creating filtered reports
- 307 - VBA case study: Protecting or hiding sheets
- 347 - VBA case study: Filtering visible sheets
- 359 - Audit sampling templates: VBA
- *370 - VBA case study: Making an advent calendar
- 374 - VBA case study: Reordering worksheets
- 382 - Keeping your VBA code readable
- 400 - VBA case study - Exhausting a question bank
Tips 401 - 500
Spreadsheet administration & best practice
Tips 1 - 100
- 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
Tips 101 - 200
- 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
Tips 201 - 300
- 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
Tips 301 - 400
- 312 - Revisiting customising the Ribbon and Quick Access Toolbar
- 321 - Setting an Excel New Year's Resolution
- 331 - Twenty Principles for Good Spreadsheet Practice: Seven years on
- 338 - Circularity redux
- 366 - Lost coronavirus cases
- 377 - The limits of Excel
- 394 - The Accessibility Checker
- 398 - Notes vs Comments
Tips 401 - 500
- 401 - Excel on Reddit
- 404 - Mail merges
- 408 - Data structure good practices
- 409 - Controls redux
- 415 - Spreadsheet Competency Framework - Five years on
- 418 - How to review a spreadsheet
- 423 - Our Excel Christmas list
- 426 -Passwords redux
- 433 - Revisiting Excel file types
- 440 - How to get better at Excel
- 441 - Under the (Easter) Bonnet: What’s an XLSX file anyway?
- 445 - Filter/Sheet Views
- 447 - Rounding errors revisited
- 457 - Excel error checking and handling
- 460 - Revisiting hidden worksheets
- 463 - Top tips of 2022
- 466 - Setting range permissions in Excel Online
Posts by level in the Spreadsheet Competency Framework
The framework is a structure for assessing ability and proficiency when using spreadsheets. You can view the framework here.
Basic User
Tips 1 - 100
- 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
Tips 101 - 200
- 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
Tips 201 - 300
- 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
Tips 301 - 400
- 311 - Less-known formatting options
- 313 - Revisiting find & replace
- 321 - Setting an Excel New Year's Resolution
- 324 - Introduction to formulas and functions
- 328 - Navigation shortcuts redux
- 331 - Twenty Principles for Good Spreadsheet Practice: Seven years on
- 352 - Keyboard shortcuts for entering formulas
- 361 - How to do data entry
- 362 - The Status Bar
- 363 - Subtotal feature redux
- 366 - Lost coronavirus cases
Tips 401 - 500
- 401 - Excel on Reddit
- 415 - Spreadsheet Competency Framework - Five years on
- 423 - Our Excel Christmas list
- 424 - Filters redux
- 433 - Revisiting Excel file types
- 440 - How to get better at Excel
- 442 - Revisiting screenshots
- 445 - Filter/Sheet Views
- 458 - Revisiting line breaks in Excel
- 466 - Setting range permissions in Excel Online
General User
Tips 1 - 100
- 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
Tips 101 - 200
- 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
Tips 201 - 300
- 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
Tips 301 - 400
- 301 - Google Sheets: SORT
- 303 - Revisiting the Formula Wizard
- 317 - Basic arithmetic functions
- 322 - Quartiles and percentiles
- 325 - Revisiting quick formatting
- 329 - CONCAT, TEXTJOIN
- 330 - IFS, SWITCH
- 332 - Revisiting number formatting
- 334 - Maximum and minimum functions redux
- 335 - Dealing with objects
- 337 - XLOOKUP
- 346 - Revisiting case-changing functions
- 348 - Fixing badly formatted dates
- 349 - Rounding redux
- 354 - REPLACE
- 360 - Revisiting typing special characters
- 371 - Revisiting ROW(S) and COLUMN(S) functions
- 375 - Workbook viewing redux
- 377 - The limits of Excel
- 381 - Treemap & Sunburst charts
- 387 - Revisiting fixing broken links
- 391 - Comparing the lookup functions
- 393 - AND/OR redux
- 396 - Revisiting showing formulas as text
- 398 - Notes vs Comments
Tips 401 - 500
- 404 - Mail merges
- 407 - Ranking within a group
- 411 - Revisiting Flash fill
- 412 - Working with large numbers of sheets
- 428 - Converting between statistical measures
- 429 - Revisiting cross-sheet formulas
- 430 - 3D maps
- 431 - SUBSTITUTE redux
- 436 - Trimming excess spaces redux!
- 444 - Revisiting XLOOKUP
- 446 - Desktop vs Online
- 447 - Rounding errors revisited
- 449 - Let LET simplify formulas
- 453 - Revisiting audit sampling with dynamic arrays
- 455 - Data conversion
- 461 - Things Google Sheets (still) does better than Excel
- 463 - Top tips of 2022
Creator
Tips 1 - 100
- 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
Tips 101 - 200
- 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
Tips 201 - 300
- 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
Tips 301 - 400
- 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
- 320 - Revisiting OFFSET
- 323 - Creating a trial balance from accounting data
- 326 - Revisiting loan functions
- 327 - Introduction to dynamic array functions
- 333 - Revisiting GETPIVOTDATA
- 336 - Why is there an @ in my Excel formula?
- 338 - Circularity redux
- 339 - Power Query: Filtering for a date range
- 341 - Text extraction functions redux
- 342 - Revisiting HYPERLINK
- 343 - SUMPRODUCT redux
- 350 - Power Query: Creating a ranking
- 351 - Non-linear regression
- 353 - Audit sampling templates: Dynamic arrays
- 355 - Connecting Google Sheets and Excel
- 356 - Audit sampling templates: Traditional formulas
- 364 - Dynamic arrays: Creating a visual work schedule
- 367 - PivotCharts
- 368 - Power Pivot: Connections
- 376 - Discounting functions redux
- 378 - Revisiting splitting data by category
- 379 - Processing survey results
- 383 - Revisiting ranking functions
- 384 - Data types
- 385 - Power Query: Creating transaction references
- 397 - Power Query - Looking up charge-out rates
Tips 401 - 500
- 403 - Power Query: Merge Kinds
- 405 - DATEDIF redux
- 406 - Power Pivot: Filtering active contracts
- 408 - Data structure good practices
- 413 - Flattening and unfolding data
- 416 - PivotTable layout
- 417 - Custom data types
- 418 - How to review a spreadsheet
- 420 - STOCKHISTORY and exchange rates
- 421 - Comparing lists redux
- 422 - Better error catching formulas
- 425 - Revisiting unpivoting
- 426 - Passwords redux
- 427 - Power Query: Word frequency analysis
-
462 - New text manipulation functions - TEXTBEFORE and TEXTAFTER
Developer
Tips 1 - 100
- 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
Tips 101 - 200
- 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
Tips 201 - 300
- 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
-
243 - VBA Case Study: Calculating on cells of a given colour
-
263 - VBA case study: Making a hyperlinked table of contents
-
271 - VBA case study: Organising collections and fair sharing
Tips 301 - 400
- 306 - VBA case study: Creating filtered reports
- 347 - VBA case study: Filtering visible sheets
- 359 - Audit sampling templates: VBA
- 365 - Revisiting finding items that add to a target
- 369 - Power Pivot: Measures
- 370 - VBA case study: Making an advent calendar
- 374 - VBA case study: Reordering worksheets
- 382 - keeping your vba code readable
- 386 - Power Pivot - RELATEDTABLE
- 388 - Power Pivot: Filtering measures
- 400 - VBA case study - Exhausting a question bank
Tips 401 - 500
- 402 - VBA case study: Capitalising titles
- 409 - Controls redux
- 410 - Installing VBA redux
- 414 - Select Case and stamp duty revisited
- 419 - Revisiting macros and custom functions
- 438 - Power Query: Creating custom functions
- 439 - Power Pivot: Create a running total
- 441 - Under the (Easter) Bonnet: What’s an XLSX file anyway?
- 454 - Revisiting splitting workbooks with VBA
- 456 - Revisiting loan modelling
- 460 - Revisiting hidden worksheets
Excel community
This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.