A Coloring Companion Book

This little book, with its accompanying files, is a supplement to the main book about drawing beautiful pictures in Excel: “Spreadsheet Adventures: Coloring Fun with Conditional Formatting”. In the main book you will learn the theory and practical ways of building coloring pages.

Here, on the contrary, the coloring pages are already provided (you will downloaded those files separately as “digital extras”), and all that is left is to get instructions on how to use them.

Again: the most important part of this companion book is not the book itself, but the Excel files provided with your purchase!. To download the files, navigate to https://leanpub.com/user_dashboard/library, select the “Coloring Guide” companion book, and click on “Download Extras” at the bottom of the pane. See screenshot on the next page:

Getting Started

1. After downloading the zip file, extract its contents. On Windows, you can click on Extract all in the File Explorer:

2. The zip file, even when extracted, will have several nested folders within it. Click into them, until you get to the actual Excel files:

At this point, it may be a good idea to more or copy these files to somewhere more convenient; and you can always go back to the zip file if you want to start over with the original ones…

3. When first opening the files, you will most probably see the warning:

Since you do want to make changes, click on Enable Editing.

4. Each file is a collection of several sheets. Each sheet has a picture area and a few control cells:

5. Don’t be surprised at those strange names for the control cells: “Divisor”, “R_power”… They make sense when you learn about the way the pattern was created: by writing a special formula in every cell of the picture area (Range A1:Y25 in this case), and then using Conditional Formatting to color those cells. For the picture above the formula is:

=MOD(ABS(ROW()-13)^R_power + ABS(COLUMN()-13)^C_power + Extra, Divisor)

If it still doesn’t make much sense, I’ll refer you to Part III of the main book: “Coloring Fun with Conditional Formatting”. You will find a detailed explanation there.

6. The picture area is protected against an accidental change: if you select a cell or group of cells inside the picture and try to write down some number or text in them, you will get a message box instead:

I strongly advise you against making changes to anything other than the control cells: although it may not ruin the picture completely, it will degrade the coloring:

Still, you may want to have complete control over the file. So, if by some reason you need to change certain cells, first unprotect the area. Go to the Review tab on the main menu, and click on Unprotect Sheet:

An even easier way to do it is to right-click on the sheet name (Sheet3 in this particular case), and then click on Unprotect Sheet… in the pop-up menu:

7. Control cells are here to create new patterns: changing values in any of them will change the picture. For example, the Divisor was 0.16 in one of the pictures above. Changing it to 2 (and leaving all other parameters the same) will radically transform the pattern:

Now, keeping a new Divisor value 2, let’s replace R_power with a new value as well, and the pattern will change again:

Since we have several parameters to change, and each of them may be almost anything, there are endless pattern possibilities. Not all of them are interesting, but many are…

See “The Gallery” chapter for inspiration.

8. I’ve said that each of the parameters can be almost anything… Well, in same cases their values may be invalid. Here, for example, a value for R_power is too big for some of the cells, and Excel doesn’t like it!

If this happens for you, just go back (CTRL + Z), or pick a more appropriate value for the offensive cell, and the picture will become whole again.

9. After choosing the pattern, it is time to color it. I will explain how to do this in a separate chapter; for now just appreciate how much the different color schemes change the character of the picture:

Coloring Techniques

Let’s get started! Suppose you open the first sheet in the file Small Patterns.xlsx, and see a pattern in quite dramatic colors. Now you want to change them.

The new coloring scheme is just a few simple steps away.

1. Click on any cell inside the picture. Then click on Conditional Formatting on the Home tab, and then on Manage Rules on the drop-down menu.

2. A new window will open: Conditional Formatting Rules Manager. Click on Edit Rule:

3. Next window shows the actual rule (“Format all cells based on their value”) and the Format Style (“3-color Scale”). Three current colors are shown below:

4. Clicking on a color, you will produce another small menu - a “palette” - with about 50 different colors and their tints. Pick any you like!

5. If those 50 colors are not enough for you, click on “More Colors”. A new window will open, where you can choose between about 120 Standard and - theoretically - more than 16 million Custom colors!

6. After choosing all 3 colors, click OK several times, and a new coloring is here to enjoy!

The Golden Mean

Let me show you one more tool that will help you to fine-tune your coloring.

So far we were speaking about control cells that affect the pattern. I added another control cell here that affects the coloring itself. It has the name Percent, and changes the distribution of the colors inside the 3-color scale.

Here is one more pattern with this extra control cell already added:

And here is its color scheme:

When the value in Percent control cell is 50 or close to it, the picture is balanced, in the sense that all three colors are represented equally. Try the lower or higher value, and either of the “outside” colors will begin to disappear. Look what happens when this value goes from -10% to 110% with 20% increments (50% picture is omitted here, but you can see it on the previous page):

Let me also show you this effect on a bigger picture with this coloring scale:

---

 

 

Keep in mind that even when Percent is equal to 0 or 100, the corresponding color doesn’t disappear completely. If you want to eliminate it altogether, put -1 or 101 (or -10 and 110 – any numbers that are less than 0 or more than 100), respectively…

Gallery

Here are a few pictures, obtained with the formula:

=MOD(ABS(ROW()-13)^R_power + ABS(COLUMN()-13)^C_power + Extra, Divisor)

Each page presents a pattern, a coloring scheme (for small pictures), and values for all parameters that were used in a formula. The Percent was 50 in all of the pictures except one.

When looking at the bigger pictures, it is better to adjust your zoom level to where they are spread across the whole screen: the bigger the picture, the more beautiful it looks.

---

 

 

---

 

---

 

 

---

 

 

---

 

 

 

Conclusion

I plan to add new coloring albums from time to time. To be notified of updates, please go to https://leanpub.com/user_dashboard/library, click on this book, and ensure that you’ve selected the option to be notified when a new album is available.

To stay informed about the whole “Spreadsheet Adventures” series – with information about new chapter and book releases, and also weekly posts with puzzles and beautiful pictures – follow me on https://facebook.com/spreadsheetadventures:

Thanks again for reading; please share this book with your friends and colleagues; and don’t hesitate to reach out via email, LeanPub’s web form, or Facebook if you have questions or feedback.

Happy coloring!