# 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

Among the many reasons I love Word Styles, is how it makes the Navigation Pane more powerful and easier to use.

Turn on the Navigation Pane by going to the View Tab, Show Group and checking the Navigation Pane check box.
At this point, if your text is unformatted, the Navigation Pane will not look that useful. But, watch what happens when I add styles to the unformatted text.
Now the text appears in the Navigation Page in the same style hierarchy used in the document. Now I can use the Navigation Page to quickly move around the document by clicking on the text I want to jump to.

If there is a lot of text, it can be collapsed and expanded using the triangle buttons.
The Navigation Pane can be used for more than navigation, it can also be used to reorder/reorganize text in the document. For example, perhaps I wish to move the section on the “The Adventures of Pinocchio” after “Aladdin”. I can do this easily by clicking on that heading in the Navigation Pane and dragging it below the heading I want it to follow.
Not only is that heading moved but all the subtext beneath it is moved as well. Fast and easy document reorganization!

I offer Word template design services and training. Feel free to send me an email.
Featured image from
oxana v

# One File to Many – MS Word

A few weeks ago the following request came to me: “I have also been told that there is a way to change and update several … in a bulk fashion, that would speed up the process when customizing many documents for a specific job.” Of course, I immediately started thinking about a process that would allow one to smoothly update a group of standard documents. For example; every time a new customer is being set up.

I reached for the INCLUDETEXT field in Word. In contrast to inserting a file (which takes the entire contents of a file), the INCLUDETEXT field allows you to specify text within a file, when that text has been identified by a bookmark.

# The Plan

Set up a “CustomerInfo” source document. Then in my standard documents (target documents), I’d use the INCLUDETEXT field to link to the relevant pieces of information stored in the source document. Updating would be a breeze, simply change the information in the source and the next time the fields are updated in the target document all the correct information will appear. In this process, the Customer Information source document would:

1. Always have the same file name
2. Be stored in the same folder as the rest of the customer files. If this is not the case, then the field code in the target document will need to be adapted from my example.

## The Source Document

Setting up the source document is pretty straightforward – I’d make a form detailing the information to be collected. However, bookmarks are too easy to delete when adding or updating information. I’d use content controls nested inside the bookmarks. This also takes advantage of tabbing from one control to another, making it faster to input and edit information. The controls can be grouped or placed in a table. But don’t use the Locking options when creating the control. Locking a control prevents the target document from updating.

## The Target Document

I’d place the following field in the target document
`{INCLUDETEXT "{FILENAME \P}\\..\\source document filename" bookmark}`
Replacing the source document filename with the Customer Information filename (including the docx extension) and the bookmark with the name of the bookmark from the source document.
The {FILENAME \P}\\..\\ portion of the field extracts the path & filename of the current file and clips off the filename (using \\..\\), which allows you to substitute the source document filename. Hat tip to MS Word MVP Paul Edstein for this clever solution.

## Updating

The INCLUDETEXT field is classified as a “warm” field in Word. This means it does not update automatically, but requires user intervention. The user needs to select the field and press the F9 function key to update. If multiple fields are used in the same document, use Ctrl + A to select the entire document, then press F9 to update all fields.
There are macros to update all the fields as well, but the keyboard commands are just as straightforward. Depending on the workflow, I might write a macro to loop through all the documents in a folder and force updating.

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

# Three Windows 10 Keyboard Shortcuts

Try:
Windows Key ⊞ + L to quickly lock your computer.
Windows Key ⊞ + M to minimize all windows.
Windows Key ⊞ + Printscreen to quickly take a screenshot. This will create a Screenshots folder in the default Pictures folder. Screenshots will be given the default name of Screenshot(x).png and numbered consecutively.

Photo by:
Anas Alshanti

# Excel – Previous Day Total

Here is a scenario: A running total of numbers, updated daily. You want to capture the previous day’s total, as you can see in the picture below.

I’m showing the answer in two steps here, in real life I’d make it into one formula.
The first step is to capture the row number of the previous day’s total. Finding it using the numbers in Column C would be way too complicated. But Column B has the kind of data we can use.
Using the formula `=LOOKUP(2,1/(B2:B29<>0),ROW(B:B))` captures the row.

What the lookup formula is doing is starting by evaluating the numbers from B2:B29 looking for values that aren’t equal to zero.
This creates an array like this: {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
The TRUE values will equal 1 and the FALSE values equal 0.
This means when the formula divides 1 by those values, an array looking like this is created:
{1;1;1;1;1;1;1;1;1;1;1;1;1;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}
Lookup can’t find 2 in the array, so it settles for the largest value in the array that is less than or equal to lookup_value.The ROW function tells it to return the row number from the array B2:B29.
The second step is to combine the row number with the column number and show the result using `=INDIRECT("C"&D2)`
And there you have it. A quick way of always finding the previous day (month, year, whatever).

# Three Presentation Mistakes

Last weekend I attended the Southern Alberta Library Conference. I really enjoy this conference, the speakers are great and the topics really relevant to my volunteer work with the Redcliff Public Library. So what does this have to do with presentation mistakes? It was interesting to see the kind of presentation mistakes that speakers who are good at presenting make.

# Mistake Number 1

Our old friend – too much text on the slide. Even good speakers do this, even though they shouldn’t. I suspect because they worry about leaving something out of their presentation.

Once again, I’d like to join my voice to all the presentation experts telling you NOT to put all your text on the slide. But, I know it will happen anyway, so what can we do to improve a slide like this?

## Remove Bullets

If you are going to write full sentences with punctuation, then bullets are completely unnecessary. They take the viewer’s eye away from the content of the sentence. Save bullet points for sentence fragments, which is what they are designed for.

## One Sentence Per Slide

Help the audience focus its’ attention by restricting yourself to one sentence per slide at a time.

## Position Sentence Text

There is no rule in PowerPoint (or any presentation software) that requires you to use the default text wrapping. Add line breaks to force text to wrap for greater readability and easier recall. Notice how the ham jumps out from the rest of the text, when it is forced onto its’ own line. Think about the part of the sentence you wish to emphasize and add line breaks accordingly. Also, if the sentence is on its’ own slide, there will be room to do this.

# Mistake Number 2

Smart Art can cause problems of its’ own. In particular, the seductive way it shrinks text to fit into the graphic makes people forget to edit. (See mistake number 1)

Also, the default colour schemes means a lovely rainbow of colours. How is this a bad thing you ask? Well, inevitably you get a colour combination like point three in the graphic above. White text on a yellow background. That’s readable on a computer monitor, but when projected onto a screen it doesn’t have enough contrast.
The rainbow effect above, does something else as well. It wastes the potential usefulness of those colours. Colour is a great way of adding organization and hierarchy to a presentation. In the slide above, perhaps green refers to free-range meat, blue to fish, yellow to poultry, red to spicy foods, and I have no idea what pink would refer to. Because there is no organization being used here, just the random default applied by Smart Art.
Ignoring the organizational impact of colour, is like leaving money on the table.

# Mistake Number 3

This last mistake is a little bit of mistake 1 AND mistake 2 combined, and it comes from using Smart Art process graphics like the one below:

Every time, a process graphic like this leads to the speaker saying “I know this is hard to read, but”.
Hmmm, yes it IS hard to read, but I can understand the desire to help people understand the flow of a process. So why not introduce your process in a series of slides like this:

In this sample slide I’ve taken the process and reduced to a smaller graphic in the top left corner. Here it will act as a map to show people where we are. I’ve toned down the colours of the steps that are not being talked about on this slide. I’ve left the bright blue alone, because we are talking about the blue step on this slide. I’ve cut out the blue step and enlarged it, so the text will be easier to read. It is easy to imagine each step in turn being featured on a separate slide and highlighted on the map.

Once again, thanks to everyone who spoke at the Southern Alberta Library Conference. I learn a lot about how to be a better library board member every time I attend. And, if you are a resident of Alberta; consider volunteering in your local library. It really is the best volunteer gig around. Such a positive environment that really makes a difference in the community!

I offer presentation design services and coaching. Feel free to send me an email.