This post poses a problem: create a unique list in Excel based on criteria. Rather than just presenting a solution, the author helpfully walks us through how to understand the complicated formula that solves the problem.
The key point is that if you find a formula online and simply copy / paste it without understanding how it works, you limit yourself from the power it has, and therefore, you limit the power that you have.
Full article: Create a unique list in Excel based on criteria
Here is a simple checklist to double check your Excel formulas work:
- Are you mixed up?
- Roughly know what your answer should be.
- Check the order of precedents or operations.
- Switch your view to show formulas.
- Ensure all formula arguments are accounted for.
Full article: 5 ways to double check your Excel formulas
This article provides a comprehensive guide to Excel's custom number formatting feature.
Topics include: creating and applying custom number formats, structure of custom number formats, placeholders, colors, text/labels, repeating text, and using symbols in custom number formats.
Full article: Excel custom number format guide
Understanding the order and precedence of operations in mathematical formulas or in an Excel Formula is very important.
Excel has a total 17 operators that you can use to build complex Excel formulas. Which operator will be evaluated first depends on the precedence of the operator.
This article describes the order of precedence for each of the Excel operators, including how to control the order using parentheses.
Full article: What is the order & precedence of operations in Excel?
Dan Bricklin changed the world forever when he codeveloped VisiCalc, the first electronic spreadsheet and grandfather of programs you probably use every day like Microsoft Excel and Google Sheets.
Join the software engineer and computing legend as he explores the tangled web of first jobs, daydreams and homework problems that led to his transformational invention.
Full article: Meet the inventor of the electronic spreadsheet
One of the nice features of Excel Tables is the banded row formatting, which makes it easier to read and scan your data.
Unfortunately Excel Tables aren't efficient with large data sets (over 100k rows), but we can replicate the banded rows with Conditional Formatting, and we can toggle it on and off at the click of a button, as shown in this blog.
Full article: Toggle Excel Conditional Formatting on and off
There have been countless posts on IT Counts and the Excel Community about the risks of poorly designed spreadsheets and the time that can be wasted by inefficient use of spreadsheets.
The IT Faculty is publishing what we hope will be a major contribution towards addressing those problems, and now is your chance to improve our 'public consultation draft', by providing feedback here.
Over the past several months a dozen or so 'spreadsheet gurus' – the term is meant as a compliment – have been debating what ought to be our 'Twenty Principles of Good Spreadsheet Practice'.
So this posting is part of our 'public exposure' of the Spreadsheet Principles. Comments and suggestions for improvement are welcome!
Full article: Twenty principles for good spreadsheet practice
Today I want to share with you an Excel secret. It's so powerful that when you know this your Excel life will become much easier.
I say it's a secret because I get A LOT of questions from people struggling to use the built in Excel tools like PivotTables, and functions like
Most of the time they're struggling for one reason… Their data is in the wrong format.
Full article: Excel tabular data format
As developers we all try our best to follow some modicum of best practices when it comes to our coding.
I want to share some of the best practices I now use when writing and developing VBA for Excel:
- Preparing the Visual Basic Editor.
- Preparing the code modules.
- Variable naming conventions.
- Using comments.
- Additional code module formatting.
- Using defined names.
- Using other peoples code.
Full article: Coding best practices using VBA for Excel