• We are looking for you!
    Always wanted to join our Supporting Team? We are looking for enthusiastic moderators!
    Take a look at our recruitement page for more information and how you can apply:
    Apply

[Guide] How to Make a Model of Your City Using Excel

  • Thread starter DeletedUser4240
  • Start date

DeletedUser4240

It is important to players like myself who do not purchase many city expansions that they get the most use out of every single cell in their cities. Using the Excel spreadsheet program I have created city models that allow me to find the best way or ways to rearrange existing buildings and create space for new ones. I am sure that without this remodeling tool I would have on occasion missed the ideal solution to a remodeling or repacking problem..
.

.
Excel in its entirety is a daunting application, but using it to make a model of your city is much simpler than you might think. The following instructions explain in detail how to do that. I warn you, however. that they were written to be simple and specific to the point of rudeness. By using them even someone who has never worked with Excel or any other spreadsheet program in his life should be able to turn out a fine model of his city in no time. (I have been told that these instructions will also basically work with certain freeware spreadsheet programs, but I cannot personally confirm that.)
.
Step 0: Start Excel.
.
Step 1: Create a file. On the menubar (the one with text on it) click on File and then New... When the New Workbook menu pops up, select Blank workbook and your workbook (file) will appear.
.
Step 2: Save the file. Click on File and then Save As... Choose an easily accessible folder such as MyDocuments and a name for this file. The file name will now appear at the top of the window.
.
Step 3: Name the master worksheet. On the menubar click on Format, then Sheet, then Rename. Type in the name of your city. It will now appear on the tab at the bottom of the window. This is your master worksheet. It should always exactly represent the current state of your city. The other worksheets are for experimenting with changes.
.
How to Copy the Master Worksheet to a Different Worksheet:
a. Select the entire master worksheet by pressing Ctrl+A or by clicking on the small, empty box just above row 1 and to the left of column A.
b. Press Ctrl+C (copy)
c. Activate the worksheet you wish to copy to by clicking on its tab at the bottom of the window.
d. Click on cell A1 and press Ctrl+V (paste). You want to use the source formatting, so ignore the little menu that pops up when you do the paste.
.
You can update the master worksheet by reversing the copy/paste procedure once the changes to your city are in place.
.
Step 4: Make the worksheet cells square. We do this by setting their row height and column width. You are free to experiment with different values for these settings, of course, but the ones I give here work pretty well for me.
.
a. With the master worksheet active select the entire worksheet (Ctrl+A).
b. Right click on the tab for the master worksheet at the bottom of the window and click on Select All Sheets.
c. On the menubar select Format, then Row, then Height...
d. In the box that pops up enter the value 17.5 and press OK.
e. Repeat steps a and b. Then select Format, Column, Width...
f. In the box that pops up enter the value 3 and press OK.
.
Since you will probably begin using another worksheet by first copying the master worksheet to it, part b is not very important. But it is kind of neat.
.
IMPORTANT NOTE: If you do not see one of the menu entries mentioned as being on a pulldown menu, click on the button with a double down-arrow at the bottom of the menu. This will fully expand the menu. (They like to hide things from you.)
.
Step 5: Delimit the city expansion slots. I think this is a good idea, but it is not essential. The dimensions of the cityspace are 40 by 40 city cells, which means its worksheet representation will cover rows 1 to 40 and columns A to AN. Expansions are 4x4 in size, so there will be 10 by 10 expansion slots.
.
a. Left-click on cell A4 to select it. Expand the window or scroll it if necessary until you can see cell AN4. Hold Shift and left-click this cell. You should see a row of 40 cells highlighted. This is your selection.
b. Right-click on the selection and choose Format Cells..., then click on the tab labelled Border.
c. To use this tool you must first pick a line style from the box at the right. Since we are basically doing underpainting here, I would recommend one of the light and unobtrusive broken line styles. Click on it to select it.
d. Around the large box that says "Text" you will see some smaller boxes. Click on the one on the left that shows a solid line at its bottom. The line style you picked should now appear along the bottom of the Text box. This means it will be used as the bottom border of each cell in the selection.
e. Click OK.
.
You should now see a line bordering the bottom of cells A4 to AN4. If you do not, you can go back to the Border tab and clear the Text box by clicking on the None box at the top of the dialog. Try again. Also...
.
IMPORTANT NOTE: Pressing Ctrl+Z will undo most of the actions you can take in Excel. This is usually the fastest way to correct a mistake when you make one. Always remember this time-saving tip.
.
f. With cells A4 to AN4 selected, press Ctrl+C.
g. Click on cell A8 and press Ctrl+V.
h. Click on cell A12 and press Ctrl+V again. Continue copying the borders until you reach cell A40.
i. Since row 40 is the edge of the cityspace, you may wish to mark it with one of the heavier borders. You simply need to select cells A40 to AN40 and repeat the procedure in the Border tab using a different border.
.
We will basically be repeating steps a to i now to create the vertical delimiters. The differences are these:
.
j. Select cells D1 to D40. (Remember to hold down Shift when you select D40.)
k. In the Border tab use the same line style you used before, but click the box below and to the right of the Text box, so that your line style appears along its right edge. DO NOT TOUCH any of the other indications within the Text box.
l. If you did not mess up, click OK. Otherwise, close the dialog and start over.
m. Copy the selection using cells H1, L1, P1, T1, etc. as targets. If you do not arrive at column AN you made a mistake; use Ctrl+Z to back out.
n. Put the same border on column AN that you used for row 40.
.
For the rest of these instructions we will be using three tools: Merge and Center, Borders, and Fill Color. Try to locate them on the toolbar (the one with all the little icons on it). Hover over a tool to read its title. If you cannot find a tool, it might not be there. Click on the dark icon with the down arrow named Toolbar Options. Select Add or Remove Buttons and then Formatting. Make sure all three tools have check marks in front of them.
.
GOOD TIP: Remember to make your selection before you click on a tool. It is that simple and can save a lot of "why didn't that work" confusion. Select, then apply.
.
Step 6: Create a basic structure. The hardest part of this step may be establishing where exactly to put the very first structure on your worksheet. Placing additional structures is much easier than placing the first structure because you can relate their location to the locations of other nearby structures. But to place the first structure you must determine its city coordinates.
.
How to Determine the City Coordinates of a Structure: To do this you need to turn on the city grid. And, of course, there is no control for that ... However, it is possible to see the grid by bringing up the road-building dialog and clicking on Build.
.
There is only one natural way to associate the cells that you see on the city grid with the cells of your worksheet model. The physically uppermost cell of the city grid, way at the top of the screen, will be represented by worksheet cell A1. Counting along the "top" row of the city grid (which runs from Northwest to Southeast and contains the A1 cell) corresponds to counting along row 1 on the worksheet. And counting "down" a column (meaning to the Southwest) on the city grid corresponds to counting down a worksheet column. It should be clear then that worksheet coordinates are identical to city coordinates. For example, city coordinates (12,4) will correspond to L4 on the worksheet.
.
a. Using the road tile graphic as a cursor, determine the city coordinates of the upper-leftmost cell of the structure you want to place on the worksheet. (If you have a structure that borders either the "top" or the "left" edge of the cityspace, it is a good choice for first placement.)
b. On the worksheet place the mouse cursor at the determined coordinates and, holding down the left mouse button, drag the mouse diagonally to select a rectangle the size and shape of the structure.
c. With the selection still active click on the Merge and Center tool. (This is a toggle.) You now have a basic structure.
.
How to Move A Structure: With the structure selected place the cursor on one of its edges. You will see it change to a four-directional arrow. Hold down the left mouse button and you can drag the structure to a new location -- unless its new location would overlap its original location. In that case you must first drag the structure a distance away and then drag it back again. Weird, but true. (You will get a cross-shaped cursor if you hover the mouse on the lower right corner of the structure. This is used for doing things you do not want to do. It is easy to get this cursor when you go to move a 1x1 decoration, so watch it.)
.
How to Delete A Structure: The fastest way to do this is to drag the structure right past column AN, right click on it, select Delete..., and then pick Shift cells left. Gone in a jiffy.
.
Now it is time to finish the structure.
.
Step 7: Put a border around the structure. With the structure selected click the down arrow on the Borders tool and select the style named Outside Borders. (This is not required, but I think it improves the appearance of the map.)
.
If you simply click on the Borders tool, it will apply the style that it shows on its face. To remove all borders use the No Borders style. Notice the Thick Box border style? I think it looks great on Great Buildings.
.
Step 8: Label the structure.
a. With the structure selected right click and pick Format Cells..., then click on the Alignment tab. For Text alignment Vertical: choose Center and then put a check in the Wrap text box. All structures should have these settings.
b. Now type in a text label for the structure. I use the Arial type style (which I think is the default). It is a neat and clean sanserif style.
.
Notes: Remember to select the entire worksheet before you go changing type styles! The type size that looks best generally depends on the size of the structure and the size of the label. For 2x2 residential buildings I use 9 point. Labels like "estate", "arcade", "country", "gambrel" fit nicely at this size. (Be sure to enter apartment as "apart ment". That is why you have wrap on.) For bigger structures you can go up to 12 point -- or more. For 1x1's you must use 8 point. With this type size you can sometimes fit four characters into a 1x1 label -- e.g. "tree", "sign" (signpost), "lant" (lantern), or "fntn" (fountain). But "bush" will not fit.
.
Step 9: Add color. With the structure selected click the down arrow on the Fill Color tool and pick the color you want to use as the background color of the structure. If you simply click on this tool, you will apply the color it shows on its face. To remove color choose the No Fill style.
.
Color is obviously a matter of personal preference, but since I am being so explicit in these instructions, I will share my largely arbitrary color scheme with you. (It is hard to pick a color for the Wishing Well.)
.
White ------------ Residential Buildings. White is the color you first get when you use Merge and Center. I use it for residences, likely the majority of your city's buildings. I also use white for any building that pays out coin only, for instance, the King and City Hall.
Pale Blue -------- Production Buildings
Rose ------------- Goods Manufactories
Lavender -------- Cultural Buildings
Tan -------------- Military Buildings
Light Green ----- Floral Decorations. Here I decided that because there are so many tiny decorations, it would be helpful to distinguish the botanical kind from the non-botanical kind. Light greens include trees, bushes, hedges, plants in pots and plants in tubs: anything as long as it is a plant.
.
Lime Green ----- Other Decorations: statuary, signs, street lamps, pillars, flags, kites, fountains, ponds, etc.
Light Yellow ----- Great Buildings!
Gray - 25% ----- Roads. Road are not considered to be structures, so you only need to color a road cell to be done with it; they have no borders or labels. (I do not try to distinguish types of pavement because it is chiefly the city geometry that concerns me, but I suppose one could try using patterns to do that.)
.
Copy Structures and Save Time: After you have finished creating a structure you can save work by copying it over to a new location using Ctrl+C and Ctrl+V. Changing the label or the color of a structure is very easy, so you really only need to create one house, for example, and then copy it over and over.
.
Have fun making your city model!
 
Last edited by a moderator:

DeletedUser73

The program is superficially attractive, using graphics from the game to represent buildings, but I find flaws in this approach. The graphics are dysfunctional since they present odd and inconsistent perspectives and do nothing to reveal the underlying organization of the city. Simple text would work better; it is better to be able to just read the building labels than to have to hover over them. The 1x1 decorations are so small as to be nearly indistinguishable and zooming them is inconvenient and counterproductive.
Images are more easily distinguishable than plain text. One cell is small, and you won't be able to read most of the text if I put it in the grid. If you know what the building looks like, you can easily see which building it is.
As for the perspective, that can be off indeed. I am going to fix that, but that'll take some time to do properly.

The program appears to be defective. It doesn't always correctly identify the structure under the mouse cursor as it should. You may have to move the cursor around before the program gets it right. Other bugs are selling and moving 1x1's -- I have yet to make either of those things happen. Maybe I missed the trick here, but sell and move work fine for larger structures. These are really major problems.
Of course there are bugs, I am just 1 person working on it in my spare time, whereas excel for example is being developed by an entire team. The selling and moving of 1x1 buildings is because you are using Internet Explorer, which does things somewhat differntly. I have made a fix for that which should be released soon, but I would suggest using another browser altogether.

The program does not seem to lend itself to city remodeling experiments, which is what you want it for, isn't it? The tool stores your model in a remote location which apparently you access by originally saving it as a favorite. In this scheme one could make alterations in the model and then quit without saving, but I am not clear how one goes about making a copy of an existing model, making alterations to it, and then saving it as a separate file. Excel lets you do this and keep all your work in one local, easy to access file. Also, the Forgestats tools requires you to sell a building and then rebuild it in a new location. I find this less convenient than the Excel method of moving structures by dragging and dropping them.
There is a move option. Select it, click on the building and click on the new spot to move it, simple as that. It's that button with the 4 arrows on it.
As for changing an existing model, you can load it, change it and then save it again. It will generate a new link with the new model. I am planning to extend the save feature to include an auto save and some sort of password protection so you can keep the same link for your model.

However, the most important advantage of using the spreadsheet approach is this: it runs locally and does not consume bandwidth. I have a standard broadband connection and have given up running FoE in two cities simultaneously. It just brings every other use of Internet to a screaming halt. Any way to avoid using Internet while this puppy of a game is running seems like a very good idea to me.
The city planner does indeed only work online, but it does not use a persistent connection. The only time something is loaded after the initial loading is when you want to see an image you haven't seen yet. If that influences your connection that much, you might want to get a better one.
 
Last edited by a moderator:

DeletedUser4240

Samples

Here are some samples. NOT
 
Last edited by a moderator:

DeletedUser4074

If that works for you, great. I personally prefer Slygoxx's, but having more options to help city planning is a good thing.
 

DeletedUser

AAAAAND, I wonder why Inno-FOE team do not put out their own city planner? maybe we can get real simulator and get real looks before we commit to rearranging our city. Efficiency is good, and with good look its better!

Innogames has a long history (habit?) of not doing exactly this sort of thing in any of their games, but rather relying on the players themselves to come up with the tools to make their games easier to play.
 

DeletedUser

........ a player can add his own code to calculate what he wants. I think Excel planner has certain potential in this area.

That's an excellent idea! As an Excel fan I am now trying to get this into my charts, but since I'm no math guru, I wonder if there's a way to tell the program to auto-fill the cells on the right (the ones with the 3 questionmarks, see picture at the end of this post) by recognizing the fill color and adding the stuff up. For each building, I've used the top left cell for the structure's value, so for example the highlighted decoration in cell H197 has the value 200 (for happiness). So to calculate the total happiness my city produces I would use the amateurish formula "Add all green, gray and lilac cells with numbers in them and display the result in cell # BC197". How do I translate that to a language Excel understands? I can do that manually by typing "=" and then clicking all buildings' top left cell, but as soon as I add or remove a building, I'd have to do that all over again. I want it auto-filled. Possible?

P.S. .... and to complicate things: As you can see, the town is located beginning at row 196 of that sheet, reason being it's not the only town on that sheet, there are 5 others. So the calculations would need to be limited from B196 to AO235..... I could live with creating a seperate sheet for each town, but having them all on one would be preferrable.

2vj3020.png
 
Last edited by a moderator:

DeletedUser4844

I'm no Excel Guru or anything, but I suspect one way to do this might be to have another worksheet for each category you want to do this for (happiness produced, population produced, population used up, etc). For each cell in the old workbook you then have the corresponding cell in the new workbook but take the value in the old workbook and multiply it by ((cell color = green) or (cell color = gray) or (cell color = lilac)). Well, you would do it that way for the happiness produced worksheet. Each worksheet would have different color values associated with it that it needed to compare to. The upshot is that all the values that don't have the right color end up with zeros in them, because the boolean value of false is 0 and multiplying by 0 gets you 0. Whereas the values that do have the right color end up yielding a true boolean statement, with a value of 1, and when you multiply by 1 you get the original number back.

For happiness produced, the original worksheet would then add up all the numbers for the worksheet for happiness produced, not the numbers from the original worksheet. The extra worksheets are a bit of a pain to set up (very liberal use of copy/paste should help), but once they are set up you should never have to look at them again. All automated.
 
Last edited by a moderator:

DeletedUser

HH, I don't understand how you "assign" values to cells. If a cell has a value, it is shown in it.
Besides, all buildings have more than one value, for example, residential buildings have costs (coins, supplies, diamonds), population provided, coins produced, time of production, ...
Also, AFAIK, Excel doesn't allow you to work with cell color (unless you want to use macros).
Long story short - it won't work in your scheme.

I would do the following.
First, I would create, in a separate sheet, a big table with all buildings and all their stats. For each building, I would also add an "alias" - shorter version of the name, which will be shown in the city map (for instance, alias for a "Wheelwright" can be "WW", as in your example).
City map will contain blocks (as suggested in the original post) with aliases (and nothing else). Background color - only guide for an eye. If you want to have an additional info in the map (size, type of roads required), it can be added as a comment. If you don't want to see the alias (for small structures like decos, roads), text and background color can be made the same.
Once it's done, you can easily calculate any characteristics of the city. Examples:

Total population:

{=SUM(population-column-in-the-big-table * COUNTIF(city-map-range, alias-column-in-the-big-table))}

Cost of rebuilding from layout city1 to city2 (assuming 25% return on destroyed buildings):

{=SUM(building-cost-column * MAX(COUNTIF(city2, alias-column)-COUNTIF(city1, alias-column), 0) - 0.25*SUM(building-cost-column * MAX(COUNTIF(city1, alias-column)-COUNTIF(city2, alias-column), 0)}
 

DeletedUser4844

Looks like Ggryvi is right about needing to use macros to look at cell color. However, if you are willing to go to VBA macros this page looks like it has the exactly the information you are looking for.
 

DeletedUser

thanks guys, I will fiddle around with this a bit more next weekend when i'm back home.
 

DeletedUser13838

I thought the same thing but if you read the instructions (there is something there) you can put the system into construction mode and you can use the delete key for removing a building.
 

Vger

Well-Known Member
And Now ?
It has now been more than 6 years since this thread was started. Stuff has changed since then. Most notably, reconstruction mode.
But if you want to design your city in Excel, and it works for you, go for it.
 
Top