by Charley Kyd on October 17, 2011
If our rotting economy causes most of our business problems these days, why is it that so few Excel reports and analyses include data about the economy?
These days, the road to business success seems to lead through a maze. Relevant economic data is like a photo taken from above the maze; it offers a view that helps managers find a path to safety. So it’s a mistake to deprive them of this information.
All the economic and business data your managers need is available on the web, probably for free. And before the end of the month, you’ll be able to use spreadsheet formulas to display that data in your reports, analyses, and forecasts.
Here are ten ways that spreadsheet data could help to improve your company’s performance… [click to continue…]
by Charley Kyd on July 7, 2011
The Excel 2010 help file provides more information about the CLEAN function than earlier versions:
“Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.
“Important: The CLEAN function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set … there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters.”
Actually, CLEAN is more powerful than this information describes. [click to continue…]
by Charley Kyd on July 1, 2011
This morning’s mail included this note:
Dear Sir,
My boss recently purchased an electronic copy of your “Dashboard Reporting With Excel” book. He asked me to do some reporting that involve times series plots with multiple trend lines on the same data series.
I have searched on the internet to learn how to do it but with no success. Could you kindly explain how I can do this, PLEASE?
By “multiple trend lines” I assume my reader wants to see trends for multiple periods that are a subset of the total number of periods available. So let’s see how to do this…
For the past several weeks I’ve been working on a bonus offer that will allow Excel users to create dashboards of stock data and economic data from the web. So I happened to have the perfect data series to answer this question: Apple’s revenue history for the past 50 quarters.
This image illustrates what I think my reader wants, and probably also illustrates why he wants it.
[click to continue…]
by Charley Kyd on May 25, 2011
by Charley Kyd on May 25, 2011
In Use Excel VBA to Test Report Calculation Times I described a macro that finds the total time necessary to calculate Excel for a specified number of times. This post describes the workbook I set up to test various lookup methods. The next post (Excel’s Fastest Lookup Methods: The Tested Results) will explain what I learned from these tests.
The testing workbook needed to meet two criteria.
First, it needed to have a database large enough to cause the lookup formulas to work fairly hard. Second, it needed to be volatile.
The first criterion is self-explanatory. But what’s volatility? [click to continue…]
by Charley Kyd on May 23, 2011
I recently exchanged email with an Excel expert who had concluded that VLOOKUP was Excel’s fastest lookup method. It was even faster, he said, than INDEX-MATCH. To support this surprising conclusion, he sent me his testing macro.
The macro was cleverly written, but had two fatal flaws. First, his testing method had so much overhead that it overshadowed the calculation times he was trying to test. Second, the overhead varied between calculation methods.
This is why his conclusions were incorrect.
In this post, I’m going to show you my macro that tests calculation times for alternate report designs. The macro is simple, but it works. [click to continue…]
by Charley Kyd on April 4, 2011
This is strange.
I recently used Google to try to answer a programming question I had about Excel shapes. After performing several similar searches, I searched for the terms: AutoSize excel anchor vba shape
I worked my way through the results until I noticed a very unusual sentence. I’ve marked it with an arrow in the following image: [click to continue…]
by Charley Kyd on March 19, 2011
Gosh I feel stupid.
I just rediscovered a handy Excel feature that’s been in the product for a long time. I vaguely remember using it years ago. But then I forgot all about it, which is why I feel so stupid.
Before I tell you about the feature, I’ll back up one step. Suppose you’re looking at a formula with a cell reference or range name that you’re unsure of. So you’d like to select the range to see if the cell reference or range name is correct.
For years, I’ve advised readers with this problem to copy [click to continue…]
by Charley Kyd on March 16, 2011
Certain publishers of software for Business Intelligence, for budgeting and planning, and for related software, see Excel as their primary competitor. They therefore take every opportunity to marginalize Excel.
I saw another example of this today in an Excel forum at LinkedIn.com. The Director of one of these companies posted a link to a five-year-old article from the Financial Times. The article, Beware the perils of spreadsheets, offered a collection of some costly errors that resulted from businesses using Excel for serious work.
You probably hear similar criticisms about Excel from your IT department or from some of your managers. So you might be interested in my response to that Director’s post: [click to continue…]
by Charley Kyd on March 10, 2011
