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
Having formula errors on a spreadsheet can be bad news. They look ugly, they frighten the less Excel experienced among us and they stop our other formulas from working.
If you have formula errors on a spreadsheet it is normally best to stop it at its source. To either correct the error, or to hide it using formulas such as
However, if your spreadsheet is large, having these
IFERROR functions in every cell to protect against error values will add more calculation time to your spreadsheet.
There is a function in Excel called
AGGREGATE which allows us to perform various functions on a range whilst ignoring the formula errors.
Full article: Sum a column ignoring formula errors
I've been asked many times how to find either the cell reference of the first or last value in a range, or even return the values from those cells, and there are many ways to do it.
As usual I'm going to share the methods I think are the best:
- Find the first value in a range.
- Find the last value in a range.
- Find the last number in a range.
INDEXto return a reference.
Full article: Return the first and last values in a range