Realizing the naval battle in Excel

Struck, sunk Enough with numbers and formulas, stop work! Have you ever thought that through the use of logic functions, and use conditional formatting in Excel, you can transform one of the most popular and loved games, the naval battle? A school sufficed two sheets and a pen: you now have a computer available for all …

naval battle in Excel

Have fun.

Start Excel and predispose two sheets. Rename Sheet1 and Sheet2. rename Excel sheets, or by clicking on the name of the sheet on the bottom delkla main window or by clicking with the right mouse button on the name of the sheet and selecting “Rename “. Place names: “Defense” instead of sheet1 and “Attack” instead of Sheet2 .

Without these steps, save the file.

You need to make sure that the cells are of the naval battle of the squares, so all the same width and height. The field will have the size of 10 lines for 10 columns, rows A through J, and columns from 1 to 10. To do this, you need to select from the menu bar Format / Columns and Rows (Width / Height ). Unfortunately there is no default value: depends on the resolution of the video impostata.Fate attempts until the cells become square. To make it more lively the battlefield can add a colored background and a bold border, so they stand out the boundaries of campo.Per change the background color of the cells, first of all select the entire field, set the desired color by clicking Format / Cells. This will open a window where you can choose ” Pattern “and select a colore.Per change the border, you need to select the item and choose Edge of a sudden marked.

Repeat the above steps for both sheets. At this point, before you start placing the ships, it is necessary to agree with your opponent on the maximum number of boats that can be inserted into the battlefield. Set this number, you can start placing your ships strategically, so as to make it difficult to attack and lunge. In Defense sheet positioned vessels by inserting “x”.

You can do so that Excel automatically accounts the number of ships positioned in the field of battaglia.Per do this, use the ” CONTA.SE . ” In a point outside the field, position excel-hai-vintoyourself on the formula bar and type the following formula: = CONTA.SE (A1: J10, “x”)

In practice Excel check how many “x” you entered in the field. Highlight the cell with a background color and a font color that contrasts visually with the background. 6Usiamo a simplified version of the game: Each time your opponent will hit a ship, take an “x”. As a result, each shot will have an “x” in the least, and the formula of the previous step returns values ​​ever lower. At this point you can put another condition: when the previous value (that of the “x” still on the battlefield) becomes zero, the message of defeat.


Position yourself in a cell outside the battlefield, for example L6 and, in the space reserved for the formula, type the following statement: = IF (Score = 0; “Lost”, “”) The literal meaning of this condition is the following: if the score is zero, within the cell L6 will appear in the sentence announcing the defeat.

You can highlight your losses to the fleet using the “Conditional Formatting”.

For example, you can make sure that when your opponent hits a ship, the cell turns red. Select all the cells of the battlefield. From the menu bar choose Format / Conditional Formatting. This will open a window in which to place the condizione.Potete decide that if instead of the “x” represents a piece of the ship, put a dot (“.”). When you insert the point, the cell will change color. In the composition of conditional formatting, select the drop down menu “Cell Value Is”, “Equals” in the space provided you put a dot “.”.


Click the Format button.

This will open a second window called “Format Cell”. Navigate to the Patterns tab and choose the color red. Click “OK” to confirm your choice. The preview of the conditional formatting will be colored red. Click “OK” to complete the operation.

When your opponent will call a box containing a ship, put a dot and the dye cell of rosso.Se, conversely, make a call to empty, you can put [HTML2]to indicate a zero strikes failed. Now switch back to the second sheet, which you called “Attack”. This will initially be empty, but with the progress of the game will be filled with “0” when the shot will fail and “x” when the ship of your opponent will be hit. As for the defense, you can make sure that when you hit a boat, the cell

will color rosso.Per do so, use conditional formatting described. Obviously, the condition is that the value of the cell must be equal to “x”.

As described above for the defense, you can put a counter shots turned out well. The easiest way to do this is to select a cell on the side of the battlefield and write the formula: = CONTA.SE (A1: J10, “x”) As in the case of the paper on protection, this function counts all cells where it is This “x”.

When you have sunk all the vessels of the opponent, you can add an automatic control: when the counter reaches the total fleet will be written “You have won. “To do this, position yourself in a cell outside the field and type the following formula: = IF (Hit = 15; “You won!”, “”)

answersDownload File Naval Battle with Excel

The naval battle on-line If you are too lazy to carry out this exercise, but the idea of a nice naval battle in the office teases you, you can always play on the Internet. Connect at, and have fun with the flash game.


READ MORECreate an Excel macro to calculate the Salary