# A Spreadsheet is Creative (Part 5)

While the content of the spreadsheet is complete, the design isn’t finished yet. I think the best spreadsheets are “scannable”, in other words the reader can scan the sheet and get a sense of the pattern of information, before they even read it. I want the reader to be able to see at a glance whether it is a Facebook or Twitter Post, and to have a sense of the time of day the post will occur. Colour is going to make this happen. Since I have no intention of manually formatting the calendar, Conditional Formatting will make this happen.

I am a big fan of conditional formatting, but the working dialog where formulas are added leaves a lot to be desired. So when I base a conditional format on a formula, I will typically build the formula in the spreadsheet so it can be tested and then copy it out of the formula bar and paste it into the working dialog. In this spreadsheet I have 12 (TWELVE) conditional formats, so I ended up pasting the formulas into notepad, so that I could jump back and forth without having to close/reopen the dialog box repeatedly.
I thought quite a bit about how I wanted different times to be represented by colour. I eventually decided on 6 different time “zones”.

• 10 pm to 7 am
• 7 am to 9 am
• 9 am to 12 pm
• 12 pm to 3 pm
• 3 pm to 6 pm
• 6 pm to 10 pm

I needed to find the excel versions of those, so I went to an empty spreadsheet, typed in the time and removed the time format. This is what I found:

Here are the series of formulas I used for conditional formatting, the first in each group is the default which will format the 10 pm to 7 am time entries.

=(C5=”FB”)
=AND(C5=”FB”,B5>=0.292,B5<0.375)
=AND(C5=”FB”,B5>=0.375,B5<0.5)
=AND(C5=”FB”,B5>=0.5,B5<0.625)
=AND(C5=”FB”,B5>=0.625,B5<0.75)
=AND(C5=”FB”,B5>=0.75,B5<0.917)

=(C5=”TW”)
=AND(C5=”TW”,B5>=0.292,B5<0.375)
=AND(C5=”TW”,B5>=0.375,B5<0.5)
=AND(C5=”TW”,B5>=0.5,B5<0.625)
=AND(C5=”TW”,B5>=0.625,B5<0.75)
=AND(C5=”TW”,B5>=0.75,B5<0.917)

I chose two different colours, based on the http://colorbrewer2.org/ color blind safe palettes and then picked increasingly darker shades of each colour.
And that wraps up this spreadsheet build! I hope you found this look at my process interesting, and I hope that you find building your spreadsheets as creatively satisfying as I do.

I offer Excel template design services and training. Feel free to send me an email.
Kelly Sikkema

# A Spreadsheet is Creative (Part 4)

After the first test of the mega formula that will create content of our calendar, I spotted a problem.

Some formula tweaking is in order. I’m also going to trim the length of the Post Type entry to 2 characters, so only FB or TW will appear, And the Image (column E) will be trimmed to the last 3 characters, so only the file type will be visible.
I will use the current row number ROW() – 5 (the number of rows before the entries start) to help count down the entry list. I’ve also replaced the “stop” label with “” with empty quotes. Now when there is no matching entry, nothing will appear.
As I mentioned earlier, I’m trimming the Post Type entry to 2 characters using LEFT. I also decided to force those two characters to uppercase using UPPER. So the previous formula will be nested inside the LEFT and UPPER functions:

=UPPER(LEFT(IF(ROW()<5+(COUNTIFS(Content!\$E:\$E,">=”&B\$4,Content!\$E:\$E,”<"&B\$4+1)),INDEX(Content!\$C:\$C,(MATCH(B\$4,Content!\$E:\$E,0)+ROW()-5),1),""),2))

While it has been a long haul building this spreadsheet, now is not the time to stop. The next step will be adding the conditional formatting to this spreadsheet.

I offer Excel template design services and training. Feel free to send me an email.

Kelly Sikkema

# A Spreadsheet is Creative (Part 3)

Now, I need to start to pull the post type, time of post and post content from the Content sheet. Starting with the post type (which resides in Column C), the following INDEX function in cell B6 will do the trick:
=INDEX(Content!\$C\$1:\$C\$1187,B5,1)
This index formula uses the value from B5 to find the entry in row 530. And yes, at this point that is only the first entry for the day.

Now, I’ll use an IF statement to build the formulas you see in column C6:
=IF(ROW()<6+C\$5,"yes","stop")
At this stage in the process I usually use the IF statement to display a label. “Yes”, means there is matching data in the source sheet. “Stop” means there is no longer matching data. The test in column F shows the formula is working.
Now I’ve proven that the each formula works in turn, I can combine them into a larger “mega” formula. Don’t forget that when building these larger formulas you can; expand the formula bar and use Alt+Enter to force the formula to wrap for easier reading.
Like this:
=IF(ROW()<5+(COUNTIFS(Content!\$E:\$E,">=”&B\$4,Content!\$E:\$E,”<"&B\$4+1)), INDEX(Content!\$C:\$C,(MATCH(B\$4,Content!\$E:\$E,0)),1),"stop")

You’ll notice that I’ve also stopped referring to a specific range in column E/C and instead reference the entire column. It doesn’t make any difference to the result of this formula and makes it a bit simpler to read. Also the Row adjustment number changes from 6 to 5 as I play with the layout of the spreadsheet. Changing the layout includes reordering the data, so that the time of the post comes first and adding a column that shows the path to any images used in the post.

Here’s a shot of the results of that formula:

You’ve undoubtedly spotted that only the first entry is being captured and repeated. I’ll adjust the formula to correct that problem in my next post.

I offer Excel template design services and training. Feel free to send me an email.

William Iven

# A Spreadsheet is Creative (Part 2)

Continuing from my previous post, showing my process in designing a spreadsheet.

Here I’ve placed each Day/Date heading over 3 columns (later I figured out I needed 4). Its’ starting to look a little bit like a calendar.

One of the things making the design of this calendar a little more challenging is that each day has a different number of posts. Anywhere between 2-20 depending on the days activities (library author quizzes, anyone?). So my formula needs to account for a variable number of entries each day, I can’t simply copy 20 lines and call it done. So, how will I do that?

I’ve put the following formula into B5, it will find the row that each days’ entries begin on:
=MATCH(B\$4,Content!\$E1:\$E1187,0)
Here the MATCH function does the trick of looking on the Content sheet (where my database lives) and counting down the rows to find the date that matches, then it returns the value of the rows counted.
In C5 I’m using the following formula to count the number of entries on that date:
=COUNTIFS(Content!\$E\$1:\$E\$1187,”>=”&B\$4,Content!\$E\$1:\$E\$1187,”<"&B\$4+1) The reasoning behind using COUNTIFS this way is because when dates are entered in the content sheet, sometimes a time is entered as well. The time remains unseen because of the formatting applied to the date column. I need to ensure I get every entry regardless of whether a time is present or not. Using COUNTIFS to count in a range will do this. I’ll continue with the design process in my next post.

I offer Excel template design services and training. Feel free to send me an email.

Creating a spreadsheet is a creative act. The choices made about data, formulas, layout and colour all contribute to communicating clearly. Communicating clearly and well is, in my book a creative process.

So, I thought I’d walk through my process in designing a spreadsheet to take information from what is essentially a database layout:

Into this view, a classic calendar view of the same information:
I’m planning to use the calendar view to share a social media posting schedule, as the people I’m sharing with aren’t Excel savvy (and why should they be?). I create the posting schedule in Excel, because I can easily save it in .csv format and upload it to Google Calendar. However, sharing the Google Calendar has drawbacks too, and it is simpler to print out a weekly calendar showing the planned posts.
When I began this process, I had some things sketched out in my mind. I wanted to enter a date and see the week around it. I always want to see the posts for the date in the context of the posts for the days around it. I also want a consistent layout of days – Sunday to Monday. Having the starting day of the week change each time would make it harder for my viewers.
That means I’m going to need to dynamically generate the dates based on the day of the week of my starting date. You can see my first pass below:I labelled cell A1 WeekStart, this will help me remember the purpose of the contents of cell A1.
I’ve labelled the days of the week, and above them in row 2, given each day a number 1-7. The day numbers relate to the way the WEEKDAY function works. I’ll be using the mode where the week starts numbering 1 on Sunday. Later I’ll hide that row, but for now having it visible is helpful.
You can see that I’m stepping out the formula in rows 4 – 6. When I’m designing a spreadsheet, I’ll often step formulas out like this. It helps me avoid errors and makes each step clear. Later I’ll consolidate the steps.
In row 4, I’m calculating the following:
=(WEEKDAY(WeekStart,1))-B\$2.
You can think of it as a way to calculate the number of days (+/-) from my start date. By the way, I chose to start with May 1, because it was on a Wednesday. That made sure I could test my formulas well, a Saturday or Sunday starting day would make testing harder.
Once I know I can count backwards and forwards from my starting date, I use the following formula:
=WeekStart-((WEEKDAY(WeekStart,1))-B\$2)
The WEEKDAY function calculates the day number of the week. Here, it returns the number 4(Wednesday).
From the number 4 I subtract the value in row 2. This gives me the number of days to subtract from the WeekStart value. You can see the results in rows 5 and 6. Row 5 is simply the unformatted date value, since sometime I find visualizing the pure number easier.

This turned out to be a 5 part series! I hope you’ll find the next 4 parts interesting.

I’ll continue with the design process in my next post.

I offer Excel template design services and training. Feel free to send me an email.

Photo by Estée Janssens on Unsplash

# 2018 Redcliff Youth Soccer Schedule

WebGenii Consulting has sponsored the Redcliff Youth Soccer League this year. And for every family looking to find out where and when their children are playing – here is the schedule.

# Updates to the Prairie Rose School Division Calendar

I keep a google version of the Prairie Rose School Division school calendar. This is for my own use, and is not maintained by Prairie Rose. However, you are welcome to subscribe to it. Having a subscription calendar certainly makes life easier!

Prairie Rose has published their school calendar until June 2019. 2020

If you are interested in my previous posts about calendar sharing you can check them out here:

# 2017 Redcliff Youth Soccer Schedule

WebGenii Consulting has sponsored a soccer team again this year. And for every family looking to find out where and when their children are playing – here is the schedule.

Adding a public Google Calendar is a great way to present activities in an easily shared fashion.

Make a public calendar. This can be an existing calendar or one created for this purpose.

### Make the Calendar Public

After the calendar has been created, click on the drop-down arrow beside the calendar name and choose Calendar Settings. The Calendar Details page will open.

Enable the Make this calendar public checkbox.

Click the Save button.

The calendar view will be displayed.

### Customize the Calendar HTML Code

Click on the drop-down arrow beside the calendar name and choose Calendar Settings. The Calendar Details page will open:

Scroll down the page and find the Embed This Calendar section.

Click on the Customize the color, size, and other options link.
A new window or tab will open, the Google Embeddable Calendar Helper:

A preview of your calendar is visible. You can change the title, default view and other elements of the calendar. The view will update to show you what your calendar will look like when the changes are applied. If you do make changes, be sure to press the UPDATE HTML button to ensure those changes are reflected in the HTML.

Be prepared to copy and paste the HTML code. However, do not do so yet.

Click the Use App link on the left hand side of the page.

The Static HTML: iframe tabs page will appear:

Click the Add Static HTML to a Page button.

The Add Page Tab window will open:

Click the Add Page Tab button.

You will be routed to the Set Up Tab view. Click the Set Up Tab button.

Follow the instructions in the index.html area

Copy the HTML code that you customized previously for your Google calendar into the index.html area.
Replace the instructions with your code (or the instructions will appear along with your calendar).

Click the Save & Publish button.

## Name the Tab

Click on the Apps category on the left.

Apps that have been added will be listed on the right.

From the Static HTML: iframe tabs app section, click on the Edit Settings link.

The Edit Static HTML: iframe tabs Settings dialog box will appear:

Enter the name of your calendar in the Custom Tab Name: text box.

Click the Save button.

Click the OK button.

You can also add an image that will appear beside the link on the right hand side of your page.

Click the Go to App link for the Static HTML: iframe tabs app.

The app will walk you through creating an additional tab.

Woobox has an app called Tweet Feed for Pages, which will embed your Twitter feed into a tab on your page. The concept is similar to embedding a Google Calendar. Woobox’s app will walk you through the process of adding the tab.

# Outlook – Retrieve Dismissed Reminders

Some days, your biggest enemy is yourself.  Have you ever hit that “Dismiss All” button, or accidentally dismissed a reminder you wanted to keep?

Here’s how to find that reminder, so that you can reset it.

Use the search, but instead of searching by topic, type in modified:<date>

Use the date when you accidentally dismissed those reminders (causing the modification). Your view will automatically switch into the Search Tools view. If you have multiple calendars you want to search (as I do) make sure the All Calendar Items button is pressed.

It will show you all the reminders you’ve modified.

In fact, you don’t need to be too precise about dates. Here’s an example, where only the name of the month was typed in.

Still works!

You can then reopen the item and reset the reminder.