Category Archives: Office 2016

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.

Social Media posts in a classic calendar view
The final view with conditional formats in place

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:

time converted to numbers
Excel time format, converted to the underlying number value

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.
unsplash-logoKelly 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.

The formula is expanded to more columns
Oops, it just repeats the first matching record

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.
using the ROW function
Adjusting the rows captured using the ROW function

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.

unsplash-logoKelly 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.

Showing the result of the Index function
Index and If function results

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:

The formula is expanded to more columns
Now I’m capturing more columns of data

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.

unsplash-logoWilliam Iven

A Spreadsheet is Creative

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:

Social Media Database Spreadsheet
This database view works fine for an Excel Nerd like me.
Into this view, a classic calendar view of the same information:
Social Media posts in a classic calendar view
A weekly 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:
Spreadsheet showing the calendar dates
Dynamically generating the date based on the day of the week
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

PowerPoint 2016 – a change in Guides

I noticed this improvement to guides in PowerPoint 2016 the other day.

If you apply your guides while in Master View, you can’t inadvertently move them while in Normal View. This is great! And consistent with the way objects behave between normal and master views.

By the way – guides applied in Normal View, can still be moved around in normal view. Like I said – consistent!