Month: November 2022

Posted on

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.

Alternate line formatting

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

Posted on

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

Posted on

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