Spreadsheet Page Blog
I also removed quite a bit of stale content -- mostly pages that nobody ever visited.
Last Chance for PUP v6
PUP v6 will be discontinued in a few weeks. This is the older version for Excel 2000, 2002, and Excel 2003.
If you purchase PUP v6, you will be eligible for a $5 upgrade to PUP v7 (for Excel 2007 and later).
PUP v7.2 is Available
This is a minor upgrade for Excel 2013 users. It fixes some problems related to the new single-document interface introduced in Excel 2013. The only significant feature added is the ability to insert a live array formula calendar that begins on Monday, rather than Sunday.
And one feature was removed: Currency Converter.
Most PUP users will not need this upgrade. It's primarily for those who experience problems when running Excel 2013.
To request an upgrade, click here.
A Functional IMAGINARY Tale
I've been sleeping on a COT for a MONTH NOW because I don't have any CLEAN SHEETS LEFT. So TODAY I went looking for SHEETS -- SUMPRODUCT that offers REAL VALUE for the DOLLAR. The MEDIAN cost varies in different AREAS, so I got on the Internet AND did a SEARCH to LOOKUP SHEETS. There were many to CHOOSE from: ROWS of every TYPE of SHEET you can think of, EVEN in the MID PRICE range. I thought it would take DAYS, but I got lots of INFO in a MINUTE. Excellent WEBSERVICE.
I found some SHEETS and told the clerk, LEN FISHER, that I could FIND a LOWER price online. He looked at the CEILING, sighed, and said, "NA, I can’t MATCH it ACOS I’m NOT the manager AND don’t have the PROPER POWER OR RANK. I must CELL it at the EXACT PRICE that’s listed, not a DOLLAR LOWER -- AND that’s a TRUE FACT. Can you SUBSTITUTE something else OR must it be the EXACT PRODUCT? Perhaps some rat POISSON? OR a slice of PI?"
"IRR . . . that makes no sense. Are you for real?" I asked.
"Yes, sir, IMREAL. Hold on a SEC AND I’ll send a TEXT message using our secret CODE AND try to ROUNDUP the FLOOR manager."
"BAHTTEXT!" I wondered: ISTEXT the best method of contacting him?
Apparently so, because the store manager appeared within a SECOND. He was just ROMAN ROUND, checking out the store. He was a TRIM man with LARGE ABS, a dark TAN, AND a moustache on his UPPER lip. The TYPE who likes to look in the MIRR. Nothing special about his eyes, though. Just STANDARDIZE.
"My name is MAX T. PEARSON. Is there SUM way I can help you TODAY?" he asked with a smile.
I showed him the SHEETS AND said, "Hello, I’m KURT N. BESSELY. I just want to buy DPRODUCT, AND your employee won’t YIELD on the PRICE."
The manager said, "To you, that ISODD AND probably seems off-BASE, but it ISLOGICAL to me because of our store policies. Be assured, however, that you can COUNT on me. I won’t waste your TIME with FALSE promises, AND I have CONFIDENCE I can help. Buying SHEETS shouldn’t be that COMPLEX, AND I want you to be satisfied to DMAX. Wanting a discount isn’t a SIN. IF you'd like a LOWER PRICE, just ASC."
"IF you do me a SMALL favor AND LOWER the PRICE by five PERCENTILE buy the SHEETS," I offered.
"No PROB," he replied.
Net EFFECT? I give MAXA lot of credit for a better than AVERAGE shopping experience. He FIXED the problem, AND the store has a CONVERT. I spent less than an HOUR shopping, RECEIVED good service, AND I RATE them highly. I'll be back during their end-of-year sale that runs from OCT2DEC.
Best of all, I was able to REPLACE my SHEETS, AND I don’t use ACOT. When the FORECAST calls for a drop of 30 DEGREES, I like to be under COVAR with a LOG burning in the fireplace for the DURATION.
30 Years Of 1-2-3
So, I've been messing around with spreadsheets for half of my life.
Dan Bricklin shares his thoughts.
There were many spreadsheet programs on the market when Lotus 1-2-3 came out, including VisiCalc, SuperCalc, Microsoft's Multiplan, and Context MBA. Mitch compared 1-2-3 in the demo he showed me to Context MBA. Context MBA was programmed in a slow, high-level, byte-coded language, as I recall, while 1-2-3 was in assembler for the IBM PC. He told me that Lotus tried to meet similar goals to what we had originally targeted with VisiCalc, such as keeping up with fast horizontal and vertical scrolling. It felt at least as good with big sheets and the full screen of the IBM PC as the original VisiCalc felt on small sheets on an Apple II with much fewer characters on the screen.
The image is from the Lotus Museum.
Here are some 12s. The image file is 12 Mb.
Office 2013 RIBBON TABS
During the Office 2013 preview, one of the complaints was about the UPPER CASE text in the tabs. For example, see this thread.
Apparently, Microsoft employees read their forums. In the final release, you can change the case of the Ribbon tab. Just go to the Customize Ribbon section of the Excel Options dialog box. Select a tab name and click Rename. Type your new name, using any mix of upper and lower case character. But… If the name is the same as the original tab name, it will revert to uppercase.
The trick: Just add a space character to the end of the name. Here's an example. The HOME tab has been renamed Home<space>.
Excel 2010 vs 2013 Speed Benchmark
In a recent comment, someone noted:
If you try to experiment your previous macros, you will find that they will take longer to run.
I haven't noticed any significant speed differences, so I did a quick search for a speed test and found this: Excel Benchmark 2011- An Excel VBA Speed Test. I ran the benchmark on Excel 2010 and got this:
On Excel 2013, I got this:
According to this, Excel 2013 is a bit faster. Not much faster, but it's certainly not slower.
New Units For the CONVERT Function
I've been reading all I can about Excel 2013, but I haven't seen this mentioned. The CONVERT function has been greatly enhanced -- 47 new units, by my count. Now, calculating the number of acres in a square light-year will be a piece o' cake.
Here's the Help page: Help Page
Check these out: Creative and advanced chart design in Excel.
You can download the workbooks to see how these charts are made. If you're stumped, use Excel's Name Manger to examine the names. Then you'll be even more stumped.
The Research Behind Flash Fill
I predict that one of the most controversial new features in Excel 2013 will be Flash Fill. Here's a video that demonstrates how it works. It's an AI alternative to extracting specific parts of text strings. For example, if you have a column of names, you can type a few last names and Flash Fill will figure out what you're doing, and fill in the empty cells.
It's a great concept, but it can also lead to lots of bad data. I think many users will look at a few "flash filled" cells, and just assume that it worked. But my preliminary tests leads me to this conclusion: Be very careful.
Flash Fill works in two ways: (1) It can extract data by example, and it can (2) create data by example. Creating data seems to be much more reliable. But use caution when extracting data. For example, most of the extracted data will be fine. But there might be exceptions that you don't notice unless you examine the results very carefully.
For the technically-minded, here's a Microsoft Research report (PDF) that seems to be the basis for this feature: Automating String Processing in Spreadsheets Using Input-Output Examples.
Excel 2013 Charting Notes
Today I discovered a new feature in Excel 2013 that I hadn't seen mentioned elsewhere: When you create a chart using Excel 2013, you can specify an arbitrary range of data to be used as data labels! Users have been requesting this feature for at least 10 years -- probably longer. Currently, you need to use a utility program (such as PUP or the utility from AppsPro).
Charting in Excel 2013 is vastly improved. The Format dialog box has been replaced with a task pane. Once you get over the initial shock of not being able to find the commands you need, you'll probably find that it's a significant improvement. In addition, when you activate a chart, you see three icons on the right. These icons are the express route to making common changes to your chart.
The third icon has some new filtering options. For example, you can easily hide (not delete) a series, or hide specific data points in a series.
Another improvement is creating combination charts. In the past, creating a combination chart required several manual changes to a normal chart. Now it's all done at the time you create the chart. And you even get a live preview.
Yet another new feature is "recommended charts." When you create a chart, you can choose to let Excel give you some recommendations. I can't say that I always agree with the recommended charts, but new users will appreciate the suggestions.
All in all, I'm impressed. Excel 2013 has come a long way in making charting easier to use for the masses.
Musical Resignation Letter
A Microsoft employee calls it quits
So Bye, bye Excel and I
It's been 3 good years, oh how time has flown by
With devs in the hall drinkin' whiskey and rye
Singin' forever recalc or die, forever recalc or die
The Spreadsheet Page Is 16 Years Old
Today is the 16th anniversary of The Spreadsheet Page. It's now old enough to drive.
Here's how it looked in 1997, the earliest archived page:
Today I entered a formula, and got six 11's.