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
Right-click on the spin button and set the following control properties:
- Minimum value – 0
- Maximum value – 100
- Incremental change – 5
- Cell link – any cell in the wider column’s control area, such as cell $I$2
Every time we click the spin button, we increase or decrease the displayed value by 5.
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:
=RANDBETWEEN(1, 100)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.
Step 8 – Hide the random numbers
Select the cells with the random numbers and apply a custom number format of two semi-colons.
;;
This will effectively hide all numbers in the selected cells.
Play the game
We can now increment the spin button value and gradually reveal the mystery guests behind the grid.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
