terra incognita A broad view of Microsoft Office

How it’s Made: Tower Defense - a Game in Excel 2010

This post is syndicated from Microsoft Excel 2010.

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.