Named Ranges in Intermediate Excel Make Calculations Easier

I wish I could brag about how named ranges help me professionally.  They do, but I don’t have a cute story about it.  However, it is easier for me to set up tables and formulas when I think about what each cell’s contents represent rather than the actual cell references.  Seriously, Z3432?  Who cares?  I want to know what it means.

Named ranges also come in handy when you start working with the table objects in Excel.  This concept makes structured referencing that much easier.

Wanna learn how?

Excel Intermediate

Excel Intermediate is a six-week course starting:

Tuesday, January 4, 2022

7:00-8:00 pm EST

Excel Intermediate and Problems with Zip Codes

I was once hired to set up an email list for a wildlife rehabilitation center in New England.

Excel list? Check.

Mail Merge for printing the labels? Check.

Quick job. Easy turn-around. No problem



I printed out thousands of (okay I stopped the job after a couple of sheets…) of Northern New England zip codes with only FOUR digits instead of five.

How did that happen? The zip codes were correct in the spreadsheet!

In session two, we’ll be learning how to avoid errors like this easily as well as other custom masks, advanced formatting themes, merging, and transposition tricks that make the spreadsheet not only useful but easily readable.

Sign up and learn!

Excel Intermediate

This will be a six-week course starting:

Tuesday, January 4, 2022

7:00-8:00 pm EST


Multivalued Nested IFs and Other Advanced Excel Fun

“We have 8,000 computers to upgrade the operating system. Some of them aren’t licensed for it and will just need to be replaced. Some of them haven’t touched the network and we need to find them. Others? Well, we need to group them by department to upgrade. Oh, since a lot of them are laptops that won’t be on a network fast enough to push the new OS to them, we’re going to need to chase them down and bring them in to User Support to upgrade.”

Could I have kept track of this list and evaluated it using Python or something?

Well, no. I didn’t know Python at the time!

What about SQL? Well, yeah, but no one in the office was using that tool for such a small list.

Can Excel handle it?

At only 8,000 entries? You bet it can!

I created a table with a single-line 13-value nested formula that would evaluate the many criteria needed to track the OS upgrade process.

I even made a dashboard from it for a boss who was really into visual trackers.*

You can learn what I did and how I did it in Session One. This is flat-out my favorite session of Excel to teach. Don’t laugh at me for loving this. I can’t help it.

But maybe you’ll have fun with this, too. C’mon, you know you wanna…

Excel Advanced

This will be a six-week course starting:

Thursday, January 6, 2022

7:00-8:00 pm EST

* PivotTables and PivotCharts won’t be until Session Four

Fifty Pages of One Column and How Excel Classes Can Help You

“I need you to hurry up and get these reports printed for a manager’s meeting!” my boss said as he rushed by one morning a couple of decades ago.

He was new to the office and didn’t know about some traffic patterns or bottlenecks that could happen and much later than I expected. He’d been working on the reports the night before – large, complex spreadsheets. He wanted some handouts.

I liked the guy and wanted to help him impress his new team, so of course, I was happy to rush and get what he wanted printed and copied.


When I went to print out the report, I got this horrible layout and fifty pages of single-column figures with no way to reference what they meant!

Gulp! I neglected to preview what I was going to print before I sent it to the printer. Anyone want to guess how many pages of a single column spat out uselessly before I caught it?

Session One of Intermediate Excel will teach you how to solve this problem. Want to know what I did?

Sign up for the class.

Oh yeah, this session will also teach 3-D formulas, watch windows, and linking multiple worksheets. I just don’t have any stories about that other than the fact I use spreadsheets with them every working day.

Excel Intermediate

This will be a six-week course starting:

Tuesday, January 4, 2022

7:00-8:00 pm EST

Missing the Obvious

I like to travel by train. I have some friends on a convenient train route that I visit from time to time. In the US, Amtrak an app you can use to buy and display your train ticket. I enjoy it, but oh my word is the connection slow sometimes. I am not always sure of being able to pull up the ticket on time and on demand.

For many years, I have gone ahead and printed the ticket instead “Just in case.”

I was traveling by train this weekend when I had an “I am an idiot” moment.

You see, most smartphones (mine included) can take a screenshot. You know, an image stored on your phone and not subject to the inconsistencies of connection? Simple logic tells you that the ticket is actually that QR code and that this is merely an image, right?

I do not want to admit how many years I have been printing tickets as a backup. When the obvious hit me, I felt very silly, indeed. (Not about being a belt and suspenders sort, mind. I am okay with that character trait)

I tell this story to put a point out there. Now, my job is to explore new technology, teach how to use available tools, and help people manage the new options and ways to do things in their lives. That is literally my job, and even I miss stuff sometimes.

Partially it is simply because there’s so available to you. Anyone can get used to a routine and not think about the alternative options. No-one can know everything. For me, this was a nudge that I was getting complacent and need to monkey with things more. Your lesson might be different, as it is probably not your job to poke buttons, dive for cover, and tell people about it after the dust settles.

Since that likely isn’t your job, don’t feel too bad when someone grabs a device, punches a few buttons and hands you a solution that seems obvious when you look at it. All of us get into mental ruts sometimes, and we do need to jar ourselves out of it.

On the other hand, exploration is good. A bit of monkeying and thinking about alternatives as you use technology is a grand human tradition dating back to the first time one of our ancestors deliberately put a bit of meat on a stick to burn it. You may not always have time, but it is a good idea to run with the urge when you can.

The Basics of a Pivot Table

Pivot tables are awesome. You can take a range of data, apply a little magic to it, then manipulate that data in so many ways to make analyzing and predicting much easier. It’s a great way to compare and manipulate data on the fly in a meaningful matter. They seem like these really difficult and esoteric things to create, and people who can use them well are very much valued. While a Pivot Table in Excel can seem quite complex, I want to let you in on a little secret:

The Basics of a Pivot Table are Easy

The first thing you need is a large range of data. For the purposes of this exercise, we have four friends who are competing for the first six months of 2017 to see who gets the highest step count (fitness-minded fellows that they are…) I’m not going to post the whole table, as 180 entries would just be eye-crossingly boring. And you know, that’s the point. But the source table is going to start something like this:

Date Grady Rich Derek John
1/1/2017 10532 12637 8679 12129
1/2/2017 11329 18430 9393 9221
1/3/2017 12475 16594 14010 13990

So, we have five columns – one for the date and one for each of the contestants. They record their step counts each day. But at the end of the contest, they want to analyze that data to see how they did not only by day, but by month or week.

Without Pivot Tables, this would be a royal pain. So, let’s make one.

To make a Pivot Table

  1. Select any cell in a data rage that includes a heading for each column in the top row.
  2. Activate the Insert tab.
  3. In the Tables group, click the PivotTable button to open the Create PivotTable dialog box.
  4. In the Table/Range box, verify that the range in the box is the range you want. By default, it will display the continuous range that has the selected cell in it, but you can also select the cells with the mouse, if you wish.
  5. Select a location for the PivotTable. You can place the PivotTable in a new or existing worksheet. I almost always go with a new worksheet.
  6. Click OK.

Add Pivot Table Fields

You can add fields to a PivotTable to specify the data you want to display. The Fields of the source data appear in the PivotTable Field List task pane.

To add field drag a relevant field from the top of the PivotTable Field List to one of the four areas at the bottom of the task pane. You can add more than one field to an area, and you don’t need to add all fields to the table.

This example was a fairly simple one, as the relevant headers were merely dates and the contestants’ names. I dragged the Date field to the Row box. I then dragged each of the contestants’ names to the Values box.

As of this writing, the current version of Excel does something pretty nifty and will automatically collapse the dates by month, using the sum of the values as the default.

See the plus signs beside each name? From here, you can click on the plus sign if you want to see the value for each date.

But let’s say you wanted to give this to someone who isn’t really very patient with learning how to manipulate Excel, but still needs to filter the data sometimes. What can you do to make this easy on the user?

Slicers in Pivot Tables

Slicers are just buttons that work as filters. To create a Slicer, go to the Pivot TablesTools|Analyze tab. In the Filter group, click on Insert Slicer.

I’ve chosen Months as the option here because the guys want to see how they do month by month as well as how well they’ve done in total.

When we click on each month, we will then display the data only for that month. The beauty here is that the source data is still safe. You can manipulate how the data is displayed in a Pivot Table, but the source data is always unchanged. You will notice also, that in a Pivot Table, the Grand Total always reflects the data that is being currently displayed – no extra formulas or functions to write. Just change on the fly to display what you need!

These are really just the basics of Pivot Tables. You can get a lot more complex with them, if you want to, and I urge you to play and explore. But these basics should be enough to help amaze your co-workers and excite your boss.

As always, if you have any questions, just comment and I’ll try to help you out.

The Secret to Using SUMIF

Today we’re going to learn about some features of Excel and how they interact.

  • Tables and how they’re named

We’re going to do this with a little problem our hypothetical boss has given us. It’s the end of the first quarter. She would like to know how much each client has paid us for this quarter.

We’re going to get all of this information from a little ledger she sent us. She’s no Excel expert, but she’s meticulous about records. This means we have plenty to work with just from this little table.

Date Client Source Work Type Payment
1/2/2017 Hogwarts Nepotism IT Consulting $ 1,729.00
1/4/2017 Torchwood Advertising Copywriting $ 2,020.00
1/9/2017 Bene Gesserit Competitive Bid Training $ 1,900.00
1/9/2017 The Royal Frog Trampling Institute Competitive Bid IT Consulting $ 3,829.00
1/11/2017 The Monitor’s Guild Competitive Bid Training $ 4,983.00
1/31/2017 Church of All Worlds Competitive Bid IT Consulting $ 4,422.00
2/1/2017 Torchwood Advertising Training $ 1,772.00
2/3/2017 Council of Elrond Advertising Training $ 1,029.00
2/3/2017 The Royal Frog Trampling Institute Competitive Bid Training $ 2,063.00
2/5/2017 Church of All Worlds Competitive Bid Copywriting $ 2,929.00
2/8/2017 Hogwarts Nepotism Copywriting $ 1,943.00
2/8/2017 The Monitor’s Guild Competitive Bid Training $ 3,872.00
2/10/2017 Bene Gesserit Competitive Bid IT Consulting $ 5,026.00
2/14/2017 The First Order Nepotism IT Consulting $ 2,032.00
2/28/2017 Xavier Institute for Gifted Youngsters Nepotism Training $ 3,098.00
3/2/2017 Bene Gesserit Competitive Bid Training $ 4,465.00
3/5/2017 Torchwood Advertising Copywriting $ 1,072.00
3/5/2017 Torchwood Advertising Training $ 1,943.00
3/6/2017 Hogwarts Nepotism IT Consulting $ 2,063.00
3/7/2017 The Royal Frog Trampling Institute Competitive Bid IT Consulting $ 2,972.00
3/8/2017 Torchwood Advertising Copywriting $ 3,872.00
3/13/2017 Bene Gesserit Competitive Bid Training $ 5,026.00
3/15/2017 Hogwarts Nepotism IT Consulting $ 1,072.00
3/16/2017 Hogwarts Nepotism Training $ 2,075.00
3/30/2017 The Royal Frog Trampling Institute Competitive Bid IT Consulting $ 3,141.00
4/2/2017 The Monitor’s Guild Competitive Bid Copywriting $ 1,772.00

This is just a ledger that records the date, the client, how we got the client, what work we did for the client, and how much we were paid.

The first thing we will do is turn this array into a table. This will make the rest of the calculations easier.

Tables in Excel

To turn an array into a table in Excel:

  1. Select the range you would like to make a table. (For the purposes of this exercise, that’s going to be cells A1:E27)
  2. On the Home tab in the Styles group, click on Format as Table.
  3. Choose the table style you would like.
  4. Make sure that in the Format as Table dialog box, the My table has headers checkbox is checked.
  5. Click OK.

You have now formatted your array as a table. This is more than pretty formatting, however, but also means you have created an object with some specific functions.

Before we get to the problem our boss has asked us to solve, I want to do one more thing – name the table. If you are going to be using more than one table in a spreadsheet, and will be performing calculations from more than one table, it is a good idea to give each table a meaningful name. While we are not going to go quite that far in this exercise, it’s a good general habit.

To Name a Table in Excel:

  1. Make sure a cell in the table is selected.
  2. Look at the top of your screen at the different tabs for ribbons. The last one should be Design with a Table Tools as a label above it. Click on it.
  3. On the Table Tools:Design ribbon in the Properties group, you will see a Table Name text box.
  4. Type the name you want for your table and press <Enter> on your keyboard. (We’re calling this table “Ledger”)

Now that we have formatted the array as a table, we are going to look back at what our boss wants.

The first thing she wants is how much each client has paid. We will start by creating another little array. Then we will use the SUMIF function to add up how much each client has paid us. As we go through this, you will see why I prefer to do this as a table rather than just keeping the array as-is. For the purposes of this exercise, we’re presuming that we’ll be starting an array to the side of the Ledger table starting in cell G1. The formula we will use in cell H2 will be:

=SUMIF(Ledger[Client], G2, Ledger[Payment])

Let’s break this down, as it might look like gobbledygook at first.

The function we will use is SUMIF, so we start with =SUMIF.

The syntax for the Sumif function is:

=SUMIF(range, criteria, sumrange)

So, the range is the comparison. In this case, we want the function to look in the Client column of the Ledger table, which is written as Ledger[Client], with the table name and the column name in brackets right beside it.

Then we want to know the criteria for comparison. There are a couple of ways to do this. You could look for specific text by putting it in quotes. So, we could have written it as “Bene Gesserit” (You put quotes around text you’re searching for). Which begs the question, why did I use the cell reference G2 instead? Well, mostly because I am lazy. I want to be able to write this equation once, then use autofill to complete the rest of the array.

The last thing we want to know is where to look for the values for which we want a total. In this case it is in the Payment column of the Ledger table, so we use Ledger[Payment].

Client Total
Bene Gesserit =SUMIF(Ledger[Client], G2, Ledger[Payment])
The Royal Frog Trampling Institute
The Monitor’s Guild
Church of All Worlds
Xavier Institute for Gifted Youngsters
The First Order
Council of Elrond

Pop quiz. If I did not set this up as a table, but left the array as-is, could I have used cell references instead?

My word, yes. In this particular example,

=SUMIF(Ledger[Client], G2, Ledger[Payment])

would become:

=SUMIF($B$2:$B$27, G2, $E$2:$E$27)

Notice that I made the cell references absolute? Again, lazy. I want to be able to write this formula once and copy down.

So, why would I not do it that way?

This is a personal choice thing, but I tend to try to give meaningful names to ranges of data I am manipulating so that I can think about what data I am trying to pull rather than being bogged down in making sure cell references match exactly. (Great at math, suck at arithmetic, what can I say?)

Tables are not the only way to do this. Stay tuned next week, where we’ll talk about Named Ranges and how to use them.

Please feel free to contact me with any questions you may have!

Using Styles to Format a Large Document

Formatting a large document can be a tedious process. Between pagination, making sure that chapter headings work correctly and making a table of contents, you might think you need someone to manage these things full time. You don’t, though. Word can do a lot of this for you. Today we’re going to look at two things – customizing a Style so that you can have a chapter title appear on a new page each time, and then using those styles to create a table of contents.

Customizing Word Styles

This is a lot less esoteric than you’d think. As always, right-click is your friend.

You’ll right-click on the style you want to modify. In our example, we want Heading 1 to be our chapter title and we want that chapter to start on its own page. So, we right-click on Heading 1 on the Home tab in the Styles group.

So, we’re going to change the style, right? Modify is another word for change. Let’s click on it and see what happens.

Lookie there! We get the Modify style dialog box.

Now, from here, we’re going to need to change the way a style is formatted. Using our superior powers of deduction, we’re going to look and see what we can press to do that. Wouldn’t you know it, there is a Format button for us to click on.

This is less intuitive. We’re going to need to click on Paragraph. I know, not as obvious as it could be in this case.

Now by default, it is going to show you the Indents and Spacing tab. What you really need in this case is the Page and Line Breaks. Once you’ve clicked on that, select Page Break Before. Then click okay to close the Page and Line Breaks tab, and click OK to close the Modify styles window.

Now, you don’t have to manually add a page break each time you start a new chapter. Just format the chapter with a Heading 1 style, and you’re good to go!

Creating a Table of Contents

Adding a table of contents

You can use Word to generate a table of contents from headings in a document. To be included in the table of contents, text must be formatted in a Heading style such as Heading1 or Heading 2.

You can select a table of contents from a gallery of style options. To do so:

  1. Activate the References tab.
  2. Click Table of Contents.
  3. Select the desired style.

However, the thing that makes this impressive is not the fact that you can generate the table of contents. It is that you can then update the table at any time. If you decide to add a few pages of material to your document somewhere in the middle, instead of tediously going through and changing the page references on the table of contents, all you have to do is click on the Update Table button when you have selected the table of contents. It’s a serious timesaver. Try it some time!

Everybody Needs an Editor

I spend most of my professional time as a tech editor these days. I was writing a casual piece when someone I know online poked at me to point out an error in the piece after I had published it. I made a note to correct it and then went on with my day.

In discussing it with another friend, she commented, “Weren’t you offended that the person sent a correction? Or were you embarrassed that you posted something with an error?”

I just laughed it off because it was a casual conversation, but as I got to thinking about it, I thought it deserved a more serious answer. In reality, no, I was not offended. In that particular case, the person was correct. Neither was I embarrassed. Why should I be? It is a mistake to assume that editors necessarily apply professional editing skills to their own casual work. The piece in question was on a personal blog.

More than that, however, there is no writer or editor so good that they could not benefit from another’s editorial skills. It’s rather like the old saw about the lawyer who works for himself has a fool for a client. We all have blind spots, things we miss, or things that seem very clear to ourselves that may not be to a reader.

I put this out there because it is not too unusual for the newer writer to be offended when someone suggests edits to a piece. It is as if writing is a math problem they solved incorrectly rather than an art with, especially if you write in English, some confoundedly difficult rules to follow. There’s no need to be offended or hurt. The best writers in the world often praise their editors. They’re correct to do so.

Do you know the editor’s actual job?

It’s to make the author look utterly brilliant. The smart author knows this.

Mail Merge to Create Holiday Letters

The holidays are here and many people will want to send out letters bragging about outlining their year and letting friends and family know what they’ve been up to since the last letter.

MS Word has a perfect solution for that in Mail Merge.

For this to work well, you will need some source data. An Excel spreadsheet works perfectly well for this, but if you use Outlook to maintain your contacts, that can be a good thing to use as well. We’ll be running with Excel today because I feel like it J

The first thing you need to do is make sure you have a list in an Excel spreadsheet with the information you want saved.

Firstname Lastname Spouse Child1 Address City State Zip
Harry Potter Ginny James 12 Grimauld Place New York NY


Miles Flint Rhonda Emmaline 78 Armstrong St. Verona WI


Matt Delamer Niqui Joshua 1100 Prince Edward Street Fredericksburg VA


Samuel Vimes Sybil Sam 1 Scoone Avenue Los Angeles CA


After that, you’ll write your letter, making sure to have place markers where you want the information to go. As a place marker, I’ve put brackets around the text I am going to be turning into fields for the mail merge.

Mr. and Mrs. Ronald Rust

                            1 Treacle Mine Road

                            White River Junction, VT 05001

                            December 1, 2016

<Firstname> <Lastname>


<City>, <State> <Zip>

Dear <Firstname>,

Happy Holidays. I hope <Spouse> and <child1> are doing well.

This year has been a difficult one for me. My oldest daughter, Buffy, was accepted to Harvard, Dartmouth, Princeton, and Columbia University. It was such a stressful time trying to decide where to send her! Our son, Digby, won the Decathlon at the most recent Olympics, but only Ronald could be at the award ceremony because I, of course, had to be at Stephanie’s ceremony where she won the gold for the 100-meter Freestyle at the same time. It was cause for quite a bit of tension Chez Rust, I can tell you. Really, the Olympic committee ought to know better than to schedule conflicting events, don’t you think?

After this summer, we remodeled our house, and I have to admit that was a comfort. I’d had the same kitchen for a whole three years, and just needed an upgrade!

Ronald has been working on some mysterious project in the basement that he just won’t tell me about. We keep getting medical grade equipment – bone saws and grinders… I cannot imagine what he’s going to create, but I’m sure that next year’s letter will have some exciting news of a new invention.

Do keep in touch. We miss hearing from you!

Happy Holidays and a Happy New Year,

After we have our text, it is time to start the Mail Merge.

  1. In the form letter, place the cursor where you want to insert the merge field.
  2. In the Write & Insert group, click Address Block to open the Insert Address Block dialog box. You’ll use this dialog box to insert the address merge fields.
  3. From the “Insert recipient’s name in this format list, select a format for the merge field. By default the company name and postal address are inserted along with the same field. You can also clear these setting, if you prefer.
  4. Click OK.
  5. In the Write & Insert Fields group, click Greeting Line to open the Insert Greeting Line dialog box.
  6. Select a greeting-line format and click OK.
  7. In the Write & Insert fields group, click Insert Merge field and choose the merge field you want to insert.

When you are done, you can click Preview Results.

The letter should look something like this:

If you like the way it looks, you can then click on Finish and Merge. This generates a new document, each letter on its own separate page. You can save that document, and even edit it, if you want to personalize particular letters to particular people. I don’t think it would be Poppy’s thing, of course. J