# 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.
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.

# 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.

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

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.

# 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.

# 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!

# Outlook – Retrieve Dismissed Reminders

Some days, your biggest enemy is yourself.  Have you ever hit that “Dismiss All” button, or accidentally dismissed a reminder you wanted to keep?

Here’s how to find that reminder, so that you can reset it.

Use the search, but instead of searching by topic, type in modified:<date>

Use the date when you accidentally dismissed those reminders (causing the modification). Your view will automatically switch into the Search Tools view. If you have multiple calendars you want to search (as I do) make sure the All Calendar Items button is pressed.

It will show you all the reminders you’ve modified.

In fact, you don’t need to be too precise about dates. Here’s an example, where only the name of the month was typed in.

Still works!

You can then reopen the item and reset the reminder.

# Fixing an annoyance in Outlook 2010

The scenario:

You like to flag your mail for Follow Up on a specific day. But Outlook always defaults to setting the reminder at the end of the day. Can this be changed to the beginning of the day?

It makes sense that if you want a reminder set for Today, that the default time for that flag is set to one hour before the end of your work day (as defined in your Calendar settings).

It makes sense that flagging a reminder for one of the pre-defined future dates (Tomorrow, This Week or Next Week) uses the start of your work day as the default time.

It makes NO SENSE that flagging a Custom date reminder reverts the default time to one hour before the end of the work day.

To change this default to the start of your work day.

Click on the drop-down arrow on the Follow Up button on the Home Ribbon.

Select Quick Click

The Set Quick Click dialog appears. Choose Tomorrow as the default. Click the OK button.

Done. Future custom reminder times will now default to the start of the work day – not the end.

# Google Slides vs PowerPoint for Social Media

Over the last few weeks I’ve been sharing tips for using PowerPoint for social media. Which leads naturally to asking about using Google Slides the same way.

And Google Slides offers comparable features; in particular it does support saving slides as JPG’s and custom dimensions.

What doesn’t Slides have? The photo editing tools and artistic filters that come with PowerPoint are not available, so you’ll have to pre-edit in another application.  And you’ll find that your capabilities are limited on the mobile versions of Slides, so you’ll be forced back to the desktop version.

# Set Word Up Right!: Postcard

I made a postcard with the basics for a custom installation of MS Word. I strongly recommend these settings as a starting point. Also using the Pin feature in MS Office, and couple of tips about getting tech organized. Plus the back has more nifty keyboard shortcuts!

# What’s On Your Toolbar: Postcard

I made a postcard introducing how to customize the Quick Access Toolbar, make your iDevice easier to view and using the Selection Pane. Plus the back has some nifty keyboard shortcuts too!