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 ISERROR
and IFERROR
.
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.
- Using
INDEX
to return a reference.
Full article: Return the first and last values in a range
We are going to learn how to travel in time when using Excel. In simple terms, you are going to learn how to move forward or backward in time using Excel formulas.
This article lists 42 tips for working with times and dates in Excel.
Full article: 42 tips for Excel time travelers
PivotCharts allow for charting-based data exploration and summarization in a way that no other Office visualization can. But they also offer power and flexibility that are largely unknown to many standard chart users, unless they work across businesses where PivotCharts play a significant role within their business intelligence efforts.
Like PivotTables, PivotCharts work really well on aggregate data, which is why a lot of casual Excel analysts don't often encounter the need to use them. I'll show you some of the reasons and ways to use PivotCharts to explore your data.
Full article: Practical PivotCharts in Excel
Microsoft tells us that many worksheet functions are 'deprecated.' So what does that mean, exactly?
Question:
- What's the risk of using a deprecated function?
- What function replaces the deprecated function?
- What types of functions tend to be deprecated?
Full article: What's a deprecated function in Excel?
Imagine you want alternating rows (or columns) in a report to have alternating colors.
One option is to convert your data in a table. An easy option, if you can’t use tables, is to use conditional formatting.
Full article: Reports of all stripes with conditional formatting
A paper used to justify austerity economics appears to contain an Excel error.
An economics paper claiming that high levels of national debt led to low or negative economic growth could turn out to be deeply flawed as a result of, among other things, an incorrect formula in an Excel spreadsheet.
[The academic paper that this article is based on is available at:
Does high public debt consistently stifle economic growth? A critique of Reinhart and Rogoff]
Full article: Microsoft Excel: The ruiner of global economies?
I got a email from a client asking for help because Excel was "destroying," to use his terminology, his 2003 workbook after conversion to the 2007 format. And, after analyzing the kind of change Excel made, I had to agree.
The basic problem is that names that are legitimate names in Excel 2003 may become unacceptable in 2007 (or later). But, a more devastating problem is with a formula using a name with a dot in it. Even though it is completely legitimate, Excel changes the dot to a colon. This causes the formula =SW1.SW2
to become =SW1:SW2
. Don't ask me why. It just does. The result is the formula is all wrong and destroys the integrity of the workbook.
Full article: Excel corrupts certain workbooks in migrating from 2003 to 2007
When working on a spreadsheet that someone else has built, there are a number of things that can make life particularly difficult. One of these, which is rarely necessary, is the use of merged cells.
The purpose of today's post, however, is not to teach you how to use Merge Cells, but to warn of the dangers of using them unnecessarily, and in the wrong place.
Full article: Do you really need to merge those cells?