GUESS what’s HIDDEN in Excel?
Who says Excel can’t be fun?
We will show how you can bring some fun to your next meeting or lecture using only Excel and some creative uses of Excel features.
We want to play a game where we slowly reveal portions of a photo and see who can guess who the people in the photo are, in with the fewest exposed tiles.
The tools used in this fun little project include, but are not limited to, the following:
- The RANDBETWEEN Function
- Excel Backgrounds
- Conditional Formatting
- Paste Special – Paste Values
- Row and Column Resizing
- Row and Column Hiding
- Spin Button Form Controls
- Cell Formatting
- Custom Number Formatting
It looks like a LOT of things to work with, but each tool is just a small piece of a larger puzzle that takes less than 5 minutes to put together.
Let’s Play “Guess the Excel Stars!”
This idea comes from Robert Palechek, Ph.D.
When coming up with a name for this project, Dr. Palechek says, “After more thought, I think the more appropriate name is a “Mosaic” graph.”
Step 1 – Add a picture
as the background
Begin by adding a picture as the Excel background.
This is done by selecting Page Layout (tab -> Page Setup (group) -> Background.
Browse to the folder holding your picture (or you can search for an image with the Bing Image Search feature) and select the picture and click Open.
We will potentially see many tiled copies of the image.
We only need the top-left picture, so we will end up hiding all the tiled duplicates.
Step 2 – Set up the grid density
Before we hide the tiled pictures, we need to figure out how many boxes we want and what size the boxes will be that cover the final picture.
This is accomplished by selecting and sizing the rows and columns.
The narrower you make the rows and columns, the smaller the portion of the picture you will remove when playing the game.
We will set the width of the right-most column (the column directly to the right of the right edge of the image. In this case, column I) to a larger width. This will serve as a control panel for the user interface and the percentage indicator.
Step 3 – Hide the unneeded
rows and columns
Once we have the desired grid density for our top-left image, we will select all the rows and columns for the remainder of the spreadsheet and hide them.
If our image and control panel end with column I, select the next available column heading (i.e. column J) and then press CTRL-Right Arrow to select all the remaining columns. Right-click on any selected column heading and select Hide.
Now well perform a similar operation on the rows. Select the row number of the first row below the first image (i.e. row X) and then press CTRL-Down Arrow to select the remaining rows. Right-click on any selected row heading and select Hide.
Select the visible cells in the control column (column I) and give them a light-gray fill color.
Step 4 – Adding the Spin Button
Select the right-most column (the wide column I) and add a title to the top cell in this column, such as “Completed”. Consider enlarging and centering the text in the cell.
Add a Spin Button to the control panel. Select Developer (tab) – Controls (group) -> Insert -> Spin Button.
Drag a box in the wider column to create the spin button.
You can resize and position the spin button as you see fit.
Note: If you don’t have the Developer tab, right-click on any ribbon button and select “Customize the ribbon…”
Place a check next to the Developer option and click OK.
Step 5 – Format the spin button controls
Step 6 – Generate random numbers
for cell selections
Select all the cells that cover the picture and (i.e. A1:H10) and type the following formula in the Formula Bar:
Do not press ENTER. If you do, you will create only a single random number in the first cell.
Instead, Press CTRL-Enter. This will “blast” the formula to all the selected cells.
These random number will change whenever the sheet recalculates. We need the numbers to be static.
Highlight all the random numbers and click Copy. Without moving or clicking anywhere, select the lower part of the Paste button and click Paste Values.
This will replace all the RANDBETWEEN functions with static numbers.
Consider increasing the values of cells that will cover more sensitive portions of the picture (such as the faces) with higher values. This way, the mystery is held for a longer period.
Step 7 – Conditionally format the cells (hiding the picture)
We want the cells to be a solid white color and slowly reveal the image by removing the white cell color as we increment the spin button value.
Any cell with a value that is less than or equal to the spin button value will become clear.
Highlight the cells that are in front of the image and set the cell fill color to white.
With the same cells highlighted, select Home (tab) -> Styles (group) -> Conditional Formatting -> New Rule.
Set the Rule Type to “Format only cells that contain”. Set the Rule Description to Cell Value – Less than or equal to – cell holding the spin button value (i.e. $I$2).
Click Format and set the Fill tab to No Color.
The cells should now interact with the spin button control.
Feel free to Download the Workbook HERE.
Excel Dashboards that Inform & Impress