Floor Plans in Excel?

Now here’s something new – someone has created a building floor plan in Excel.  I have to say, it’s quite an elegant file and I never would have thought of doing something like this.  It is set it up to automatically update the names and companies of employees on the map based on input data from a different tab.  The facilities team can see exactly who is assigned to each office at any point in time, as well as which offices are vacant.

We’ll take a look at how the file is set up, and then do some repairs on it, since a few of the links have been broken (this is always a risk when building an Excel file – if it’s not completely locked down, users who aren’t familiar with the setup may try to do something it wasn’t intended to do.)  We’ll walk through how this file can be locked down as well.  Let’s take a look:

There are actually two main components to the map – one we’ll talk about today, and another we will discuss later this week.  First, there is the image of the floor plan, which is basically an architectural drawing that has been scanned or digitally placed into an image editor (such as Photoshop).  The trick, though, is that all the space between the lines is transparent so that we can see through to the text in the cells behind it (the second component mentioned above.)  This means that, in the image editing software, the background selected must be “Transparent” and all the blank spaces between the lines (i.e., the offices) must also have no fill.

In Photoshop Elements, if I were to select a New file, it would give me the following dialogue box:

After choosing the size, etc., the important thing is that the Background is set to Transparent.  Then, if I scan an image of my map in, I can use a feature in Photoshop called Background Eraser to erase the white space and make it transparent, such as is shown below – the grey and white checkered area will be transparent in the file when we are done.

The final trick to creating a transparent image that we can use in Excel is to save the image as a GIF or a PNG file.  JPGs will not preserve the transparency of the image when we load it into Excel.

It would obviously be easier if a digital version of the map could be created in the first place, so that it is already transparent between the lines.  There may also be an easier way to change the white space in an image to be transparent.  I would love to hear from you via the comments section below if you have any better options for doing this – I do not profess to be an expert in photo editing by any means!

Tomorrow we’ll talk about how to insert our image (or any picture, for that matter) into an Excel worksheet.


About this entry