Excel – Find the Last Row and the AGGREGATE Function

Here is a common scenario for me. I like to apply alternate line formatting on spreadsheets, especially wide spreadsheets. It makes it easier to follow the correct row across the spreadsheet. Here a blue fill is applied to alternate lines.

But in this spreadsheet, I wanted to do something a little different. I wanted the alternate line formatting only be applied once the date is entered.

The Alternate Line formatting is not applied until a date is entered.

This means I need to find the last row in the date column. Now previously I’ve used a COUNTIF formula, to count the number of rows with data. Then subtract any blank rows at the top of the spreadsheet. The drawback of this formula, is that editing the spreadsheet later often means the COUNTIF is no longer correct. Gaps in the data also cause problems.

Enter the AGGREGATE Function

The AGGREGATE function was introduced in Excel 2010. Bill Jelen called it “SUBTOTAL on steroids”. It offers 8 more functions than SUBTOTAL and the ability to ignore hidden rows and error values. You can use it either in a Reference or Array form. Here is a link to the MS Support article. One of the new functions offered is LARGE.

So this formula uses
=AGGREGATE(14,6,ROW(A:A)/(NOT(ISBLANK(A:A))),1)
14 – refers to the LARGE function
6 – tells it to ignore error values
ROW(A:A)/(NOT(ISBLANK(A:A))) creates an array of the results of each row number in column A divided by whether the NOT(ISBLANK) portion of the formula returns a TRUE or FALSE answer. TRUE is evaluated as 1 and FALSE as 0. As division by zero creates an error, any blank cells are ignored.
1 – indicates that the LARGE function will return the largest number from the array. Hence, the last row.

The Aggregate function finds the last row
The formula correctly finds the last row, in spite of gaps in the data.

And things came to a grinding halt…

At this point, I did what I normally do and made my AGGREGATE formula into a Named Formula. I typically find this is to be a tidier way of working. When I did this; Excel froze. Repeatedly. Whenever, I did anything that caused a recalculation. Like clicking on a formula to examine it. And if I persisted and used the named formula in the Conditional Formatting formula … things … just … slowed … down … more.

But interestingly, the performance of the spreadsheet doesn’t appear to be affected when the formula is placed in a cell. I was working in Office 2016. I’ll test it later in Office 365, and report back if I get different results. I actually caused the same freezing problem by using the Evaluate Formula button. Excel slows right down as it creates the array of every cell in column A divided by NOT(ISBLANK). Somehow, however, when the formula is in a cell – Excel manages to handle the calculation much more efficiently. I even checked by making the reference to the formula into a named range. No problems. So, if you are planning to use the AGGREGATE function in this way, you’ll need to actually place it somewhere in the spreadsheet. Oh, and you will need to make that cell into a Named Range or else the Conditional Formatting Formula will not work (that was kinda unexpected).

The Conditional Formatting Formula

The formula to produce this conditional formatting is:

=AND(ROW()<=Last_Row,MOD(ROW(),2)=1)
Last_Row refers to cell $B$2
This checks to see if the current row number is less than or equal to the value in the Last_Row Named Range AND if it is an odd numbered row, if so a blue fill is applied.

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

November 9, 2022July 6, 2022

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

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

January 6, 2020December 5, 2019

Social Media Spreadsheet – Conditional Formatting

I’m a big fan of Excel’s conditional formatting feature. I use it a lot in my spreadsheets to check on the quality of data, find errors and many other tasks. Here is the first of a couple of examples of how I’m using conditional formatting in my social media spreadsheet.

Just a bit of background on the spreadsheet. I use this spreadsheet to compose Facebook Posts and Tweets for the Redcliff Library. I also use it to schedule when the posts/tweets will be published. This allows me to sit down and plan a coherent sequence of posts/tweets.

I often take the Facebook posts and cut them down to shorter lengths and reuse them on Twitter. Twitter has a character limit of 140 characters. However, I don’t want to use all 140 characters if I can avoid it. Its’ generally recognized that the ideal tweet length is around 120 characters. This length allows others to retweet and add hashtags without having to edit the tweet.

So I have created 4 conditional formatting rules to help me meet this length limit.

The background of the cell turns bright red [STOP] if the tweet is over 140 characters.
The background of the cell turns dull red if the tweet is over 135 characters.
The background of the cell turns bright orange [WARNING] if the tweet is over 125 characters.
The background of the cell turns dull orange if the tweet is over 120 characters.

Why four rules? I could use 2 warnings only; at 120 and 140 characters respectively. In fact, that is where I started. But, writing tweets can be a tricky thing and I found I needed a little wiggle room to help me when I compose. The other thing to keep in mind is that the conditional format isn’t applied until I finish editing the cell (by pressing the Enter key or the checkmark). It is possible to have an interactive format applied using VBA, but those functions are memory intensive and slow down the whole spreadsheet. Since my writing process seems to involve a lot of pauses to think, stopping to apply the conditional format isn’t really a big problem for me.

So what does it look like in action?

As a result, I can quickly identify which tweets need to be edited. Here are the 4 rules as displayed in the Conditional Formatting Dialog box.

These are formula based conditional formats.

closer look at formula — The formula the conditional format is based on.

A conditional formatting formula must return a value of TRUE in order to fire. The following formula uses the AND, SEARCH and LEN functions

=AND(((SEARCH(“TW”,B1))>0),LEN(H1)>140)

If you were reading this formula in something like english it would read: “If the letters TW appear in column B AND the length of text in this cell is more that 140 characters the result equals TRUE”.

Why am I testing for the presence of TW in the subject column? Remember I said that I had both Facebook and Twitter posts in the same spreadsheet. I don’t want the conditional formatting to flag Facebook posts, which by their nature are longer.

Pro Tip:

When you are writing a formula for conditional formatting, do it in a cell in the spreadsheet first. The dialog for conditional formatting is really cramped and you don’t get any help features. After you are sure the formula works, you can then copy/paste it into the dialog using the Ctrl + V keyboard shortcut. Also, because I planned on apply this conditional format to the entire Description column ($H:$H). I had cell H1 selected when I built the conditional formula. That way the formula will adjust relatively to the entire column. Using absolute and relative references properly is another tricky part of building conditional formatting formulas.

Once I have my formula built. I can click the format button and select the background colour fill.

When I’ve built my first format successfully I can then use it for the basis of the subsequent formulas. Just changing the length of the text in the cell.

=AND(((SEARCH(“TW”,B1))>0),LEN(H1)>140)
=AND(((SEARCH(“TW”,B1))>0),LEN(H1)>135)
=AND(((SEARCH(“TW”,B1))>0),LEN(H1)>125)
=AND(((SEARCH(“TW”,B1))>0),LEN(H1)>120)

The order of the rules and the stop if true flags must be set.

To make this really successful, the rules need to be placed in the proper order, with the Stop If True flags turned on. Now excel will check to see if the text exceeds 140 characters first, then 135, then 125 and finally 120. The Stop if True flag doesn’t need to be set on the final rule, because no other rules follow it.

Conditional formats can take time to build, but are extremely useful in many ways.

This post was originally published in 2015, and although the rules surrounding the length of a tweet have changed; my social media spreadsheet keeps chugging along. That is one of the things about a great spreadsheet. If you take the time to build it right, it will serve you well for years to come. Do you have a job that could be made easier with a well-designed spreadsheet? Drop me a line at catharine@mytechgenie.ca and let’s talk.