Readers’ Request: Easter Eggs

I find it interesting that one of the Most Popular Excel subjects among readers of this blog is Easter Eggs. As any of you who have been with me for a while knows, I addressed this a little over a year ago, and I regularly get requests to update this topic.

Virtual Easter Eggs are, of course, hidden games or messages that have been built into various software by cunning developers who have a sense of humor (utterly lacking at Microsoft these days) and enjoy building in a bit of intriguing fun into Excel. In years gone by, users who were “In-the-know” felt smug knowing how to reach the cryptic, and often entertaining, secret content.

The term “Easter Eggs” is attributed to one of the founding fathers of computer games, Warren Robinett. While working for Atari in the late 1970s, Robinett created a hidden screen which read, “Created by Warren Robinett”. Back then it was not uncommon for game designers to be given little credit for their work, so he probably felt his small ruse was justified (I agree…).

“Old-timers” of Excel will likely remember that Excel 97 had an ambitious Flight Simulator hidden within the application (it was pretty cool!). Using a simple combination of keyboard commands brought you to this remarkable (considering the era) simulator game.

Although a good deal more difficult to access, Excel 2000 included a Car Racing Easter Egg which resembled the classic Spy Hunter game (which, if you are interested in the “oldies”, can still be found online).

Excel 2003 included an Office Quiz featuring the Crabby Office Lady (remember her?). If you still have a copy of this version, you can access this egg by typing in “Tortured Soul” in the search box.

Although there have been occasional rumors to the contrary, I don’t believe there are any more hidden gems in the Excel versions after 2003. In fact, it has been reported that Microsoft will fire any employee who is caught inserting an Easter Egg into any of their applications.

That’s a pity, in my opinion. Sure, I fully understand the potential for security problems, and the inherent lack of professionalism in playing around with Easter Eggs, but I also find the days of Easter Eggs to be a charming time in software development. An age of innocence, if you will. Ah well, here’s a nod to the good old days…

Back to Basics!

Basics. I have heard many supposed Excel Experts scoff at the idea of revisiting the topic of Basics. The fact is, however, as with so many other disciplines, an occasional review of some of the fundamentals is inevitably a worthwhile Tune-up.

Navigating your worksheets, for instance, can be Drag unless you know a few simple tricks. For instance, if you wish to go to your last entry at the bottom of a list that contains 30 records, scrolling to where you wish to go is a Breeze! When you have a list containing 30,000 records, however, it is a bit tedious.

As is true with so many Slick Moves in Excel, keyboard shortcuts rule when it comes to saving time moving from one location to another on your spreadsheet.

Here are few Slick Moves you can make without ever touching a mouse:

1. Control / Down Arrow: Goes to last cell in column with data

2. Control / Right Arrow: Goes to last cell in row with data

3. Control / End: Goes to last row, column and cell

4. Control / Home: Returns to cell A1

When using your mouse, another way to navigate to the end of your data (whether in a column or row) is to carefully place your pointer on the adjacent border of cell in your range and double-click. For instance, if you wish to navigate to the last cell in a column of data that starts with cell B1, you can select B1 and double-click on the bottom border of the cell.

Want one additional way to navigate when you know the exact address of some remote cell? Simply enter the address (e.g. ET30000) in the Name Box and Zap: you have beamed directly to that location (without the help of Scotty...).

And how about Entering Repetitive Data?

While it is very intuitive to copy and paste Repetitive data to a set of noncontiguous cells (copy data, select the cells and paste), entering Newly Typed data doesn’t work quite as slick (I admit, I like “Slick” moves…)

To enter Repetitive New Data into a series of noncontiguous cells, simply hold down the Ctrl key and select all of the cells into which you want to enter your new data. Then type the text you want to enter and (a little drum roll, please…) press Ctrl+Enter. Bamm! Your Data is Entered!

Basics. Excel has a multitude of easily-mastered Slick Tricks that can save you time, and make you look good at the same time. It pays to revisit them once-in-a-while…

Imaginary Numbers

As they used to say on the old Monty Python Flying Circus show, “And Now for Something Completely Different”!

Imaginary Numbers are not something that most of us run into, and only a small percentage of us will ever find a use for them in our jobs. They do come up as a topic in most Math Curriculums, however.

For those of us who have not had a math class in a long while, (or if you were not a Math Geek like me), an Imaginary Number is a number that can be written as a real number multiplied by the imaginary unit i. The Square of i is -1. An imaginary number has a negative or zero square. For example, 5i is an imaginary number, and its square is -25.

In terms of imaginary numbers, the Square Root of -4 is 2i.

Weird stuff, I know. Real-World applications can be found in Engineering and Scientific fields. They are nothing terribly new, as they were conceived as far back as the ancient Greeks (by a smart guy be the name of Heron of Alexandria).

Imaginary Numbers in Microsoft Excel

Interestingly enough, Excel can handle these quirks of the math world. It even has a couple of specially designed functions to deal with Imaginary and Complex (expressed in the form a + bi, where a and b are real numbers and i is the imaginary unit, where i2 = −1).

The Imaginary function operates with the syntax:
   =IMAGINARY(inumber)

The Complex function uses this syntax:
   =COMPLEX(realnumber, inumber, [syntax])

There are also several innate Excel tools for using imaginary numbers with your typical arithmetic functions of addition, multiplication, etc.

I suspect that I know what you may be thinking, “What does this have to do with me?”. Well, I wouldn’t be surprised if your son or daughter encounters Imaginary Numbers in school some time, and wouldn’t it Rock if you could show them how to do this in Excel?

He or she might even think you are pretty smart. Imagine that!

FORECAST: Sunny Skies!

Forecasting can be a tricky business (just ask anyone whose job it is to predict the weather). The problem with coming up with an accurate Forecast is the fact that you are dealing with Historical Data which doesn’t necessarily correlate with the future.

Over time, however, much data is essentially Linear and can, therefore, be used to predict reasonably viable future outcomes.

The good news, of course, is that when calculating Forecasts using historical data in Excel, our favorite spreadsheet program has a Built-in Function for this purpose. The FORECAST function has the following syntax:

=FORECAST(X, Known_Y's, Known_X's)

X is the data point for which you want to predict a value.
Known_Y's is the dependent range of data.
Known_X's is the independent range of data.

Stated in Easier-to-Understand English:

=FORECAST(ValueToForecast, RangeY, RangeX)

ValueToForecast is the point in the future which you need to forecast.
RangeY is the list of values which contain the Historical Data to be used as the basis of the forecast, (Sales Figures are classic…).
RangeX are the intervals used when recording the Historical Data. Months, for instance (Just Be Sure to express the Months as a Number!)

Let’s say that you wish to forecast the Sales for October-December using the Example below. Here is what you should do:

1. In B11 put:
=FORECAST(A11,$B$2:B10,$A$2:A10)

2. Copy/Drag the above formula to B12:B13

Notes:
1. Many Excel users find this Slightly Counterintuitive, so you may wish to spend an extra minute examining the syntax example.
2. Remember that the underlying assumption for your Forecast is Linear Data. If your data does not fit this model, you may wish to explore other avenues.

FORECAST – Give it a try. You may find it says, Sunny Skies Ahead!

SUMIFS (Not Just SUMIF)!

As you may know by now, I am a big proponent of using Interactive Reports. Reports of this nature can be made with a variety of innate Excel tools, including DFunctions, Sumproduct, Vlookup, etc.

There is one Typically Overlooked function for making interactive reports, however. The SUMIFS, (Note the plural), is a surprisingly powerful tool for this purpose.

First of all, the Syntax for SUMIFS:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Unlike its close cousin, SUMIF, the SUMIFS function allows you to conditionally Sum values with Multiple Conditions, rather than just one.

For instance, let’s say you have a table/database that has multiple entries of Sales for Months and Weeks. Using a SUMIFS formula, along with dropdown boxes for the Month and Week, you can make an Interactive Report that is contingent on the parameters you choose.

Bottom Line: If you have ever used the Little Cousin, SUMIF, and wanted more flexibility, you owe it to yourself to try out the vastly more powerful SUMIFS.

As I have said before, Interactive Reports – They are so easy, They'll Make You Smile!