In this blog post, I’ll share how I created a professional looking weather report tile using Excel without using any charting or pivot tables, but just just formulas and data inputs that is repeatable and scalable to daily reports without any change in the workbook. Just input the new data, and the new visual is complete.
This is possible by Excel’s feature of inserting images into a cell, that enables us to use it like any value. That means we can apply formulas, conditions, and formatting like on any table or table range. The key is to set up the lookup values and corresponding images correctly. Then set up the report layout correctly and just enter the formulas. From that point onward, all we have to do is just update the lookup values (e.g. daily/weekly weather conditions data values) and everything will just work! Let’s look at an example below. Note that the weather conditions are made up for demonstration purposes. The actual weather is not that crazy within the week, albeit we have dynamic weather, but I wanted to demonstrate a wide range of lookup examples.
The final output I created is shown below without using any chart components:
To get there, we have to do some preparation first.
Create a table with the weather condition strings such as: Sunny, Rainy, Blizzard, Cloudy, Mostly Sunny, Partly Sunny, Mostly Sunny, Windy, Thunder, Snow. And whatever is relevant to you. Put them in a single column, each condition in its own row. Next to each, paste an appropriate image for it. Then depending on if the image is from online or from your local device, copy it and paste in the corresponding row. After pasting, make sure to click each and on its context-menu choose: Place in Cell.
My image table looks like this:
Now we can do lookup by the first column (Weather) and get its associated image using formulas!
Although not strictly necessary, but very useful, is to create a Data Validation cell with a List of values which will be the weather strings that I mentioned above, so that you can select any of them and next to that cell enter a lookup formula such as any of XLOOKUP, VLOOK, INDEX(MATCH) depending on your preference. I just chose VLOOKUP for this since it’s a simple table. And it is: =VLOOKUP(B34,images_tbl[#All], 2,FALSE)
Where B34 contains the weather string (e.g. Frosty), images_tbl is the table name shown above, and we’re returning the second column (2 for third argument) and FALSE for exact match (4th argument). Once you insert the data validation rule in a cell (B34 in my example), and its adjacent cell C34 has the above VLOOKUP formula, whenever you choose a weather string from the list, the corresponding image will show in C34.
Now let’s set up a current and a 5-day weather forecast visual! To do that, set up your values in a range that makes sense for you. Mine is set up as this, but that’s not the only layout. The key is to refer to these cells easily in your formulas for daily weather reports without having to change out the layout.
Next, I create a nice looking layout for a range of cells that brings in the required values from the above range such as current temperature (e.g. 73), and the unit (e.g. fahrenheit), and location (e.g. Seattle). I only need to change the location string above and the Temp above to customize to any location and any day.
So, the layout looks like this:
The header row gets all the values from above range, and all I have to do now is enter the high and low temperature for each day, and enter the images in Icon row are automatically filled in because we have VLOOKUP formula dragged across today + 5 days forecast. which returns the correct images based on the weather string we entered in the range shown right above (e.g. for Sat, Mostly Sunny; for Wed, Snow). This is the usefulness of setting up the image table first which is mentioned in the beginning.
Note that the ℉ symbol is actually a single character with Unicode code point 8457. That means we can use UNICHAR(8457) to always return its glyph instead of copying/pasting the actual text each time.
Rest of the work is more aesthetic because the hard part is done. You can format the font, size, the row height, column width, colors, etc. and then take a snapshot of the above range and use it in PowerPoint or save as an image for a web service to pick up and display on a web page.
You can use Snip & Sketch built-into Windows to take a snapshot of the visual, or you can also include a camera capture feature right within Excel. Here’s how…
How to include the Camera command in Excel:
File->Options: Quick Access Toolbar
Then select “All Commands” from the list under “Choose commands from:”. Otherwise, you won’t see “Camera” command.
Scroll and select “Camera” item and click “Add>>”. OK. This will add a camera icon in your top-level ribbon in Excel. You can use it to take a snapshot of any area of your Excel sheet.
My final presentation looks like as follows and it includes a rounded rectangle image as a backdrop of the snapshot of the range of output cells shown above.
Your choice of colors, location, font-face, size, and all else is up to you, but the method I explained here makes it all possible and absolutely flexible.
Thanks for reading and happy experimenting. To take it a step further such as getting live weather data into your reports check out the Related links below.
Related:
