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.

A weekly calendar view of the same information.

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:

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 – catharine@mytechgenie.ca
Headline image by Kelly Sikkema

November 2, 2022July 6, 2022

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:

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.

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 – catharine@mytechgenie.ca

headline image via Kelly Sikkema

October 26, 2022July 6, 2022

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:

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.

Headline Image from William Iven

October 17, 2022July 6, 2022

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?

Finding where each day starts and the number of entries on that date

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.

October 12, 2022July 6, 2022

A Spreadsheet is Creative – Part 1

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:

This database view works fine for an Excel Nerd like me.

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:

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 – catharine@mytechgenie.ca

Headline Photo by Estée Janssens on Unsplash

August 31, 2022July 6, 2022

Eat Your Waffles

Ok, don’t eat the waffle chart

In a previous post, I discussed making a Button Bar Chart. That whole process really inspired me to think about simplified charts for presentations.

Which got me thinking about Waffle Charts.

Waffle charts are excellent for looking at data sets where the smallest numbers are the important ones. You can use colour (as I have above) to make those numbers stand out.

But oddly, I don’t see people using a lot of waffle charts in their presentations. And there is no template for a waffle chart in Excel.

You can find some interesting ideas about building Excel waffle charts for dashboard purposes and I recommend this article to you: Interactive Waffle Charts in Excel

However, I was looking for something different. Something that wouldn’t have me counting and colouring cells manually (shudder).

Building the Waffle

I chose to build the waffle chart using a series of conditional formatting rules. The first step was creating the formula to count the cells of the waffle.

In case the picture is a bit small, the formula used here is:
=(MOD(ROW()+8,10)*10)+(COLUMN()-2)+1

This uses the row and column position of the cell to count from 1 to 100 in a 10 by 10 grid.

I then built on that base formula with this monster formula:

=IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=’5 Category Waffle’!$A$2,’5 Category Waffle’!$A$2, IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=(‘5 Category Waffle’!$A$2+’5 Category Waffle’!$A$3),’5 Category Waffle’!$A$3, IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=SUM(‘5 Category Waffle’!$A$2:$A$4),’5 Category Waffle’!$A$4, IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=SUM(‘5 Category Waffle’!$A$2:$A$5),’5 Category Waffle’!$A$5, IF((((MOD(ROW()+8,10))*10)+COLUMN()-1)<=SUM(‘5 Category Waffle’!$A$2:$A$6),’5 Category Waffle’!$A$6,0)))))

The sheet BTW is called 5 Category Waffle.

The formula checks the position number of the cell generated by the base formula and sees if it is less than or equal to the number of values in each category in column A. It then returns the value of the category in each cell.

Because I wanted to put symbols in the cell like these examples.

I took that monster formula and made it into a named formula.

This made building the conditional formatting rules much easier to do(simply because the conditional formatting dialog is so cramped).

Lastly, I built a series of conditional formatting rules to change the background colour of the cell based on the value returned by the formula. For the waffles using symbols, the rule formats the colour of the font, instead of the background.

A couple of additional pointers

To create a perfect grid, switch the view in Excel to Page Layout View. Page Layout View uses the same measurement scale for both row height and column width. Set your measurements here.
For the symbol waffles, use the File> Options>Advanced> Display Options for this worksheet and turn off the display of gridlines. That way when you copy the waffle, the gridlines will be invisible.

This post is originally from 2018. If you want help with the newest and classic features in Excel & PowerPoint drop me a line at catharine@mytechgenie.ca

August 24, 2022July 6, 2022

Button Bar Chart

Simple or Complicated?

Simple or complicated? It’s been my observation that anyone can make a subject sound complicated – but it takes real understanding of a topic to simplify it in a way that is meaningful.

This is why, when I saw this sample slide below from designer Julie Terberg, I sat up and paid attention. Here is a wonderful example of a chart that is simple in a beautiful and useful way. Immediately, you can see that an audience would find this chart easy to read and understand

Julie Terberg’s Button Bar Chart from her #SlideADayProject project

I paid even more attention when I saw the way that Neil Malek put together an Excel version of the chart. Neil introduces a clever technique using shapes in data labels.

Unfortunately, Neil’s clever technique was only available in Office 2016. I wanted to build the chart in Office 2010, for the benefit of my clients still using 2010.

Button Bar Chart Slide, in PowerPoint 2010

I think that in the end, I succeeded. If you are interested in building this chart, and like me you are restricted to Office 2010, then I have a few pointers for you.

Button Bar Chart Pointers

Data Labels in 2010 can not use shapes. Instead, I tweaked the Shadow setting for the label, by setting the colour to match the fill on the label and the size to 150%. I left all other settings to zero. Shaping the label this way means that you can never achieve the circle that Julie used in her example. Instead, the best you can do is a lozenge shape. You can modify this when you change the font size in the label.
But once you’ve used the Shadow to enlarge your button, you can’t use it to shadow the data label. I solved this problem with an old fashioned solution. I made two charts (a 2016 and a 2017 chart). The two charts are grouped together. Each chart has a data label for the year and a data label for the shadow. In the example below those labels are using the 1 values. The column labelled 2016 value is the length of the bar.

The Shadow column must proceed the 2016 column or your shadow will wind up on top of the 2016 label. Also format your labels in that order as well, or the shadow will temporarily be on top of the 2016 label.
Format your shadow and label to the same font size.
The Chart Element selector on the Format Tab of the Chart Tools ribbon is your friend. Its’ really the only reasonable way to select the shadow data labels once they are under the visible label.
Link the label text to the cell in in Excel by using the formula bar and typing in the linking formula to the cell. This allows you to update the chart, by changing the text in the cell. A bit finicky to set up; but it will save a ton of time in the long run.
The best way to take this chart into PowerPoint is by copying/pasting the chart – as an image. Which means that you’ll need to presize the chart in Excel, so that text is not distorted by resizing once it is pasted into PowerPoint. Again, its a bit finicky – but worth it.
In PowerPoint, I created a layout, with text placeholders on the left and bottom of the slide.
Layout has text placeholders on left and bottom of slide

All in all, a pretty reasonable version of Julie’s stellar design.

If you want to follow Julie Terberg and Neil Malek on Twitter, you’ll find them here.

Follow @julieterberg

Follow @neilmalek

This post is originally from 2018. If you want help with the newest and classic features in PowerPoint drop me a line at catharine@mytechgenie.ca

July 27, 2022July 6, 2022

PowerPoint Links

I’ve just been working on a PowerPoint template for a Jeopardy style game. I inherited this template, and as frequently happens a little cleanup is necessary to ensure the PowerPoint template works as desired.

To help you visualize the problem – a picture of the game board

Each square hyperlinks to a separate slide with the question (and answer).

I felt there were a number of improvements I could do to make the presentation easier to use and maintain. I won’t go into every change today, but a couple of changes involved hyperlinks
(shortcut key Ctrl + K, if you are editing 25 hyperlinks, then the reason for using a shortcut key becomes obvious).

The first maintenance problem I ran into was that the previous designer had applied the hyperlink to both the shape AND the text on the shape (now there are 50 hyperlinks – if you are counting).

They did this for a very good reason; that the text on a hyperlinked shape does not change state like normal hyperlink does (the state change shows if the link has been visited or not).

So if the slides the shapes are linked to are reordered or edited, the links have to be painstakingly tracked down and edited and since essentially the links are layered one on top of each other it is a real pain.

I had a better plan. Move the button shapes to the Slide Master (after creating a layout designed for the Game Board slide). Then insert text placeholders (yes, 25 of them) for the dollar values. Position the placeholders over each button. No hyperlinks here.

Now moving back to the Game Board slide in Normal View, I can hyperlink the text box. Text boxes behave differently from shapes, and do change state to show the link has been visited.

Another advantage of the text placeholder is that if the user inadvertently moves the text boxes, the Reset command will snap them back into position. (A definitely plus when editing 25 text boxes).

The other visual difficulty I had, was with the colours of the hyperlinks themselves. They didn’t have a strong contrast with my (new) button colour, and the visited colour was still (kinda) visible. I wanted a strong link colour and once visited I wanted the link to disappear. I could add animations, but why bother when I could solve both problems easily by changing the link colours in the Color Theme.

Here is the theme colour panel after I adjusted the Hyperlink and Followed Hyperlink Colours.

The colours in the theme were picked after playing with the free https://coolors.co/ app I also got some good advice from this article. The image at the top of the article is the colour palette created by the Coolors.co app – translated into RGB. I usually add this information as a layout in the slide master.

This post is originally from 2018 If you want help with the newest and classic features in PowerPoint drop me a line at catharine@mytechgenie.ca

July 20, 2022July 6, 2022

A little bit of history – lost … and found

Excel has been around for decades, so it isn’t surprising that there are many features tucked away under the hood. What is surprising is when a useful feature is lost and only careful archeology can bring it back to life.

Excel 4.0 had a rudimentary macro language, mostly using excel formula approaches to building functionality. This was replaced by the VBA programming language. But there are still useful little items tucked away in this early language that haven’t been replaced.

One of these is Get.Cell.

Get.Cell had a boatload of switches that allowed the user to pull information about the cell formatting and contents and most of these have been replaced by the Cell and Type functions in Excel.

But one piece of information that Cell and Type can’t tell you is whether your cell or cells contain formulas vs values and sometimes this is a very handy thing to know at a glance. For example, if you build a spreadsheet using formulas to estimate amounts; but then start to drop in values as more concrete information becomes available.

In this situation I like to format cells containing formulas differently from the cells containing values, so that I can see at a glance where my estimates are. Its’ handy to have the formatting change automatically, so I don’t have to remember what my rules are weeks or months later.

This is where Get.Cell shines. The syntax I’m going to use is

=GET.CELL(48,A1) – where A1 is the cell I’m going to reference.

The trick here is that Get.Cell is NOT entered in a cell, but instead as a named formula. After creating the named formula, I can reference it while applying conditional formatting. In this way, when the type of content in the cell changes the conditional formatting automatically updates.

Where does one find information about the Get.Cell function? Not from Microsoft or at least not easily from Microsoft.

Try this post https://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html to see the possible switches for Get.Cell

First published in 2017, the Get.Cell function remains useful today. I remain hopeful that MS will incorporate its’ functionality in newer versions of Excel. If you want expertise in Excel, drop me a line at catharine@mytechgenie.ca

July 6, 2022

PowerPoint – Making a Mask

Creating a mask effect in PowerPoint is easy, once you’ve located the Shape Combine command. You can add this command to the Ribbon or the Quick Access Toolbar.

Below, you see it being added to my toolbar.

Adding the Shape Combine Command to the Toolbar, alternately look for the Combine Shapes command as more options are available.The command will not be active until there are two shapes selected. Below, I’ve created a blue rectangle and a red oval. The oval shape will be cut out from the rectangle.

Blue rectangle with red oval positioned for the cut out. You may prefer to add the Combine Shapes command instead. More options are available as you can see below.

The result of the Shape Combine command, a rectangle with an oval “hole: in the center.

The result of the Combine Shapes CommandOnce the mask is created, you can dress it up. Below, I’ve changed the fill to an image of a leafy forest floor.

The forest floor has a hole in it. Now I can layer whatever image I wish (in this case a frog) under the mask. You can animate the layer underneath the mask. Can you image a wheel of creatures rotating into the viewpoint in the center of the mask? That would be great for a talk about ecology!

This post is originally from 2016. If you want help with the newest and classic features in PowerPoint drop me a line at catharine@mytechgenie.ca
Frog image via Wikipedia