EXCELer.org

This week’s post is written by Amy Miller. Amy is a writer for Office.com. She’s written and edited content for Excel, Access, OneNote, and InfoPath.

Imagine you’re working in a spreadsheet and you innocently press the arrow keys on your keyboard to move to another cell, but instead of moving to another cell, the entire spreadsheet moves. You may have asked yourself, what the heck just happened, and how do I make it stop? Lucky you. You’ve encountered that pesky little problem called Scroll Lock.

Recently, while reviewing customer comments related to scrolling in a spreadsheet, I discovered that a lot of folks have run into this issue. Scroll Lock is a toggling lock key on the keyboard, just like the CAPS LOCK key. Once pressed, Scroll Lock is enabled. To turn it off, simply press the Scroll Lock key again.

Easy, right? Well, the real problem is that many people don’t know how Scroll Lock got turned on in the first place, so they don’t know where the key is to turn it off, and often times they don’t realize that Scroll Lock is causing the problem in the first place. All they know is that they suddenly can’t move between cells with the arrow keys.

So here’s a quick tip to help save some time and ease any scrolling-related panic. If you’re having these wonky scrolling issues, take a look at the Excel status bar. If Scroll Lock appears, then it’s turned on.

To turn it off, just press the Scroll Lock key, which sometimes appears as ScrLk on the keyboard.

If you can’t find it, try turning the Windows On-Screen Keyboard on (go to Start, All Programs, Accessories, Ease of Access), and disabling it from there.

For more information and troubleshooting tips, check out the article Turn off Scroll Lock.

Today’s blog post is brought to you by Roxanne Kenison. Roxanne is a writer on Office.com who writes primarily about Office Web Apps and Office Starter.

My son starts Kindergarten this fall. I’m excited, but a little sad that our days of co-op preschool are over. Our local community college sponsors a co-op preschool program where parents run the preschool. Each parent works one day per week in the classroom, rotating through a series of jobs. We use an Excel spreadsheet to keep track of job assignments.

One parent is assigned as the scheduler. Last year, Timmy’s mom did a great job, updating and redistributing the spreadsheet whenever someone needed to switch days. She e-mailed updates, which was fine, except that preschool correspondence went to my home e-mail, not always accessible from work.

On my preschool workdays I’d leave directly from Microsoft, pick up my son at daycare, and scoot over to preschool. I didn’t always know my job assignment until I arrived in the classroom. Once when this happened, I was assigned to bring snacks. Whoops! There’s food on hand for this situation, and the kids didn’t mind plowing through the supply of Tings that day. But I was embarrassed.

If Excel Web App had been available back then, it would have been easier to view and maintain the schedule. Instead of the preschool’s Tings, the kids might have had fresh fruit and veggies because I would have noticed it was my turn to bring snacks.

Here’s how it could have worked, if we’d had Excel Web App:

· Timmy’s mom sets up the schedule in Excel. Then she uploads it to her SkyDrive. You can use any version of Excel to do this, although in Excel 2010 you can do it without even leaving Excel. For details, see Store a file for Office Web Apps in Windows Live.

· On SkyDrive, the schedule would have looked something like this .

· Halfway into October I see that I’ve got an important meeting at work on a Thursday afternoon the following week. I need to switch my preschool workday with another parent. I send out e-mail asking, “Anybody want to trade their day for my Thursday next week?” and one mom responds, “Sure.”

· We go SkyDrive, click the spreadsheet, and then click Edit in Browser to make the switch. The updated schedule is immediately available to everyone, no e-mail necessary. Whenever someone clicks on the spreadsheet in SkyDrive, they’re looking at the latest version. For details, see Introduction to Excel Web App.

· This makes the scheduler’s job a breeze. Timmy’s mom no longer has to wrangle changes coming from multiple sources. Even if more than one person wants to update the spreadsheet on SkyDrive at the same time, they can.

· Timmy’s mom can focus on more important things, like scheduling special jobs for our last-day-of-school celebration.

I miss our preschool group, but I know there will be other opportunities to coordinate volunteers. If Kindergarten’s on my son’s horizon, I’m pretty sure the PTA is on mine.

Today’s blog post is brought to you by Gary Willoughby. Gary is a writer on Office.com who has created and edited content about Excel, Access, and Project.

Sometimes, I find I have text in a cell that spans far past its own column or…yikes, maybe even off the screen. Because I tend to be a little borderline compulsive, that bothers me just a bit.

Of course, if I autosize the cell by clicking the column border on its right edge, all the cells below that cell could end up being unnecessarily too wide…and worse, the rightmost part of the cell might go off the screen entirely. I think I hate that even more. In fact, yes…I’m sure I do. I want my columns back.

At this point, I realize I need to wrap the text. By now, I’m fully invested in making this look just right. To wrap the text, I use…the Wrap Text command (that’s a great name!). In Excel 2010 and Excel 2007, it’s right there, waiting to be used, in the Alignment group on the Ribbon. For those of you who prefer the keyboard, you can also use the KeyTip Alt+H+W in Excel 2010 and 2007.

In other versions, such as Excel 2003 and earlier, you get to the Wrap Text command through the Format Cells dialog box (on the Format menu, click Cells and then click the Alignment tab). And for those fans of keyboard combinations…uh, here it is: Alt+O+C+A+Alt+W. Or you can use the slightly shorter Ctrl+1+A+Alt+W. And that makes me appreciate the versions of Excel that have the Ribbon even more.

Now, with the cell selected, I click Wrap Text. Most of the time, wrapping text will initially result in a very tall cell with just a few characters per line. OK, so I’m not done quite yet. The perfectionist in me just won’t let it go. My next step is to make the row the height I want (maybe two or three rows high), so I drag its bottom border up until its height is what I have in mind. At this point, I might see only part of the cell’s text. That means I’ll need to make the column wider.

I drag the right border of the column until it’s the width that makes me happy (or just happier? I’m not that easy to please). Now, looking at the following image, you might notice that it’s a little taller than it needs to be.

So I double-click the bottom border of that cell to autosize it. And that’s a wrap!

An alternative method that some people use to wrap text is to move the cursor to the end of the cell in edit mode, press ALT+Enter, and then press Enter. A downside of this method is that you’ve just added a line break to the end of the cell. To get rid of that line break, you need to select the cell again and then manually delete that blank line in edit mode.
But if you like this method, I won’t try to stop you (and really, how could I?).

In addition to wrapping text, there may be times when I want to force new lines (also known as line breaks) in a cell. I do this by pressing ALT+Enter (that key combination I just discussed, above) when I’m editing inside the cell. I can add as many lines as I want. Here, I want to break to new lines after the first, second, and fourth sentences. After all, what the world really needs is more open space.

Here’s the same cell with line breaks that I inserted by pressing ALT+Enter twice (to get both a new line and a blank line) at the end of the first and third sentences:

There. Looking at this cell makes me feel all warm and fuzzy now!

Today’s author is Charley Kyd, an Excel MVP, who describes how to use the SUMPRODUCT function to find the last sequential item in an Excel list.

Charley has worked with spreadsheets in business since 1979, and has written books on spreadsheet use for McGraw-Hill and Microsoft Press. You can find more useful tips and posts from Charley and other Excel MVPs at his website, ExcelUser.com.

Suppose you have a Sequential List of items, as in the second table here. And suppose you want to show the date of the first and last entry in the list. How would you do it?

This question isn't limited to dates, by the way. The Sequential List could display numbers or text instead. How would you write the formulas used in the range B4:C6 to return the dates of the first and last items?

Many Excel users could quickly write a formula that returns the first date for each item in the list. They would use an INDEX-MATCH formula like this for the cell shown:

B4:  =INDEX(Date,MATCH(A4,Item,0))

However, INDEX-MATCH won't help us to find the last occurrence of each item in the list. This is because MATCH (with a match-type equal to zero) returns the first item in an unsorted list. There's no version that returns the last item.

However, as the title of this short article suggests, Excel's SUMPRODUCT function provides just the power we need. Here's the formula for the cell shown:

C4:  =INDEX(B:B,SUMPRODUCT(MAX((Item=A4)*ROW(Item))))

Even if you use SUMPRODUCT a lot, you might find it difficult to understand this formula at first glance. So let's take it one step at a time...

SUMPRODUCT works with arrays of data. But it's not limited to mere multiplication, as the "PRODUCT" part of the name implies. In fact, here's how I tend to think of SUMPRODUCT:

SUMPRODUCT = (do stuff as though the formula were entered as an array, and then return the results)

I know, that's not a very scientific explanation, but it's not a bad rule of thumb.

Looking inside the SUMPRODUCT formula above, let's start with: (Item=A4)

Here, the function compares the value of each cell in the Item range (cells A10:A19) to the text in cell A4 ("Hats"). In its memory, Excel sets up an array with TRUE where an Item equals "Hats" and FALSE where it doesn't.

Now consider: (Item=A4)*ROW(Item)

Here, I've told the function to multiply the row number for each item by the corresponding value in the TRUE/FALSE array.

Because TRUE evaluates as 1 in a formula and FALSE as 0, this product returns an array of numbers. Most of the numbers equal zero, where the Item doesn't equal "Hats." But where an Item does equal "Hats," the array contains the row number where the item is found.

The next step: MAX((Item=A4)*ROW(Item))

This is simple. MAX returns the value for the largest row number in the array of numbers and zeros, which is where "Hats" occurs last in the list.

And the next step: SUMPRODUCT(MAX((Item=A4)*ROW(Item)))

SUMPRODUCT is merely the function that tells Excel to handle the earlier calculations as arrays. We use this section of the formula to return one simple number: The number of the row in your worksheet where "Hats" appears last.

Here's the whole formula again:

C4:  =INDEX(B:B,SUMPRODUCT(MAX((Item=A4)*ROW(Item))))

This formula now should start to make more sense. There's only one step left to go:

INDEX(B:B, [the last row number for "Hats"])

Here, INDEX is looking at all of column B in the spreadsheet. Among other information, column B includes the date information in the Sequential List. INDEX returns the date found in the row number specified by the SUMPRODUCT function. And that row number is where the last occurrence of "Hats" can be found.

Finally, you might consider a variation of the formula, a variation that eliminates one risky aspect of this approach: The formula shown above for cell C4 references an entire column.

Referencing an entire column is a problem in this case because if you cut and paste the Sequential List to some other location, the formula still will reference column B, but the list won't be in it. And the formula will fail.

A modified formula corrects this problem:

C4:  =INDEX(Date,SUMPRODUCT(MAX((Item=A4)*ROW(Item)))
          -ROW(Date)+1)

Rather than referencing column B, this formula references the range named Date. To get the index value we need for this range, we subtract the row number of its first row, and then add 1. (Because the Item and Date ranges always must be in the same rows, we also could have used the first row number of the Item range.)

Enter the formula in cell C4 and then copy the range B4:C4 down two rows. When you do so, the new formulas will return the first and last occurrences of Shoes and Socks, respectively.

This blog post is brought to you by Karen Cheng a Program Manager on the Excel team.

In today's post, we'll show you how the arcade game Tower Defense was created in an Excel spreadsheet (see a video of the game being played here).  In this game, creeps move toward your castle. You must defend it by placing towers which shoot at the creeps. The more creeps you kill, the more money you get to buy and upgrade your towers.

To play, you'll need -

· To download the game here.

· To install Excel 2010, hot off the press last week. You can get a free trial here. The games use features that are new to Excel 2010, so they won't work in older versions.

When you open the file to play, don't forget to enable the macros. Also, if you get a circular reference warning, you need to enable iterative calc by setting “Enable iterative Calculation” in advanced options under File -> Options -> Formulas -> Enable Iterative Calculations.

Instructions and General Gameplay

After selecting your level and difficulty and clicking Play, the game begins.

Creeps - Creeps come in "waves" of four. Each wave, they get stronger - They require stronger bullets to kill (strength), move faster (speed), and reward you with more cash once killed (worth). These stats are under the Creeps part of the menu.

Towers - To buy a tower, click on one of the towers on the right and then click on the map to place it. Towers come in two varieties - Basic and Advanced. Advanced towers are basically supercharged versions of the basic towers that shoot stronger bullets (Strength), faster bullets (Speed), and bullets that travel farther (Range). Clicking on a tower will show you these stats. Since a tower can only fire one bullet at a time, hitting the creeps at a closer range lets the tower shoot more bullets before the creep gets too far away. Range is shown by a heat map around the tower.

When you earn enough cash, you can upgrade your towers, which will increase their stats. Upgraded towers are represented by blue shading.

Lives - If a creep reaches your castle (which is represented by the flag), you lose a life. You get three lives.

The Graphics Engine

Play the game and you'll notice the animation of the creeps and bullets. You wouldn't be able to achieve this level of smooth animation if each cell simply represented a pixel, as in Missile Command. So how was this done?

Creeps and Bullets

The secret lies in a transparent scatter chart (two charts, technically, but that's more detail than we'll get into) that lays on top of the game screen. The creeps and bullets are points on the scatter plot, which constantly recalculates and refreshes. The bullets are represented by one series on the chart (the Xs) and the creeps are represented by a second series (the red diamonds).

The X-Y coordinates of the creeps and bullets are calculated via linear interpolation based on a number of factors (the map path, the location of the castle, and game's clock).

I won't go specifically into what each of these columns mean, but here's a peek into some of the stats used to calculate the position of the creeps. They are on the hidden worksheet, Calc.

Map Path, Towers, and Castles

The map path, towers, and castles are all drawn on the grid with conditional formatting. Below I've overlaid an image of the numbers behind the cells with the game area. Below  you can see that -1 represents the map path, -23 is a basic tower with two upgrades, etc. These numbers are constantly being recalculated as you play the game.

The conditional formatting rules behind the grid

In Excel 2010, we've expanded your capability to create intricate and complex rules by allowing formula-based conditional formatting to reference other worksheets. Behind each cell is a formula that looks something like this:

=IF(Calc!B263<>0,Calc!B263,IF(AND('Fixed Data'!AE2=Calc!$D$6,'Fixed Data'!AE16=Calc!$C$5),0,('Fixed Data'!AE2-Calc!$D$46)^2+('Fixed Data'!AE16-Calc!$D$45)^2))

While we won't go into what every piece of that formula does, we do want to draw attention to the references to two hidden worksheets, Fixed Data and Calc - cross-sheet references made possible in Excel 2010. If you're curious, unhide the sheets and check out the formulas behind the cells. The ones that generate the heat map of tower strength on mouse hover are the most interesting. Finally putting that Pythagorean Theorem you learned back in grade school to good use!

Bells and Whistles

Sparklines

When you play the game, you'll notice an animated line at the bottom, which displays the frame rate of the game. This is done with sparklines, a new feature in Excel 2010 that allows you to embed mini charts in a cell.

While sparklines weren't exactly designed to be animated, they can be animated with some clever spreadsheet engineering. The sparkline is based on a data range (Calc worksheet, C56:C85) which is constantly recalculated, creating the illusion of movement.

Slicers

Did you notice that when you chose the level and difficulty at the beginning of the game, you didn't have to go to a dropdown to do it? Instead you clicked on buttons in slicers, a new feature in Excel 2010 that allows you to quickly and visually interact with your data.

Oh, and of course - no game would be legitimate without being available in multiple languages.

A final note

One cool thing about this spreadsheet is how little macros were used. For the most part, macros are only being used to start/stop the game and keep track of mouse movements and clicks. Everything else is pure Excel: the calculation engine, conditional formatting, and two new features to Excel 2010 - sparklines and slicers.

We want to thank all of you who submitted entries for our Excel challenge. We had great entries that were well designed using Excel 2010 features, VBA and the user interface. Many of you are as passionate about Excel as we are and we thank you for your feedback.

We extend our congratulations to the following winners:

In addition, we would like to have special mention to the following participants for their outstanding Excel 2010 applications. The following will receive “I love Macros” t-shirts:

· Elliot Bendoly                     United States

· Louise Sondergaard        Denmark

· Grid Logic                            United States

· Adrian Wibly                      Argentina

· Ji Fang                                   China

· Juan P Arbelaez R            Colombia

To learn more about how to develop on Office 2010, go to Office Developer Center.

The developer documentation team is asking for your feedback on a couple prototypes for the Open XML SDK Documentation. Visit their blog to download the prototypes and vote:

http://blogs.msdn.com/b/officedevdocs/archive/2010/07/13/vote-for-the-prototype-you-like-for-the-open-xml-sdk.aspx

This blog post is brought to you by Karen Cheng a Program Manager on the Excel team.

In today's post, we'll show you how the classic arcade game Missile Command was created in an Excel spreadsheet. We also explain how it's made in this video. In this game, missiles drop from the sky, attacking the city. You must click on the missiles to destroy them.

To play, you'll need -

· To download the game (here)

· To install Excel 2010, hot off the press last week. You can get a free trial here. The games use features that are new to Excel 2010, so they won't work in older versions.

When you open the file to play, don't forget to enable the macros.

The Graphics Engine

The "graphics engine" is made entirely with conditional formatting. Each cell, which represents a pixel, has a number in between 1-10 to represent a shade of grey.

You can't actually see the numbers because the cells have been shrunken down, but enlarge the cells a bit and you'll see the numbers behind the "pixels":

The numbers behind the graphics engine

The entire game runs from a macro that is triggered by clicking on Start. The macro constantly recalculates what numbers to populate the grid with.

Calculating Missile Paths

Each missile runs through this series of calculations:

1. The starting point for the missile is chosen to be somewhere at random at the top of the screen

2. Each missile will at random choose one of the remaining standing buildings to target. It chooses one cell in the general perimeter of the building

3. Based on the starting and ending cell, the slope of the path the missile is calculated, such that it travels at the correct X to Y (column to row) ratio

4. The macro then repopulates the cells in the path over and over again in a loop to "draw" the missile path

5. If the missile hits the building, the game notes which building has been destroyed. The cells around the building are redrawn to the "destroyed building" state and the missile is removed.

Destroying the Missiles

Missiles are destroyed by clicking and creating a shield, which looks like an explosion effect. If the shield and missile collide, the missile is destroyed.

1. To detect the gamer's click, the macro detects when the cell selection changes. If the new cell selection is within the playable game area, the shield is drawn.

2. To draw the shield, the macro quickly draws and redraws six frames in sequence

The shield explosion effect, frame by frame.

3. If any part of the shield and missile occupy the same cell, the missile's path is aborted and the missile is destroyed. If any part of the shield occupies a cell occupied by a standing building, that building is destroyed.

General Gameplay

When a missile is destroyed, another one is instantly created. The number of missiles on the screen at any given time corresponds to the level. So at level 5, there will be 5 missiles on the screen at once.

The progress bar indicating how far the gamer is along a level is value in a cell formatted by a data bar.

The level progress indicator

Formatted by data bars

One more thing - you can peek behind the code (and hack away to your heart's desire) by pressing Alt-F8 and clicking edit.

The code behind the scenes

Thanks for tuning into this episode of How It's Made. In the next edition, we'll show you how the classic arcade game Tower Defense is made in Excel.

This post is brought to you by Bruno Aziza, WW Strategy Lead for Microsoft Business Intelligence.

The Soccer World Cup is watched by billions around the world and many have tried to use models, from macro-economic to financial ones, to predict the outcome of the world cup.  Our team and partners have put together a set of tools and videos to help you experience a more “analytical”  world cup.  See our intro video here!

· Start by enrolling in the World Cup Challenge to win prizes! (see more here)

· Download your trial of Office 2010 here.

· Download the PowerPivot add-in here.

· Download our PowerPivot demo here and develop your own Soccer Analytics!

· Realize the power of social media analytics across the desktop, web and the phone.  See Extended Results BI social analytics solution here .

Finally, share the story of the AC Milan football club, who benefits from Microsoft Business Intelligence (video here).