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
INDEXto 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?
I was working on a function that uses regular expressions to determine whether a potential name for a workbook, worksheet or range contains illegal characters. I started by writing a little routine to determine which characters are illegal for sheet names. Of course, I could have just used one that I knew was prohibited and got the message below. But then I might never have thought about the use of single quotes in worksheet names.
Full article: Single quotes in worksheet names
