How to Create a Budget Report with Excel’s Amazing SUMIFS Function

The Advanced Budget Project — Part D: The Budget Report

A budget is probably the most important spreadsheet you can create. A good budget will keep you focused on your ultimate financial goal and help you avoid pitfalls like that adorable timeshare on Aruba.

Gold coin icon to indicate savingsNow we are at the last part of our four-part Advanced Budget Project for Microsoft Excel 2016 on Windows. This is the crucial part where we find out how we are doing relative to our budget. In the first three parts, we created the Budget itself, a Ledger to keep track of our expenditures, and a Pivot Table to summarize our actual expenditures.

In this part, we will set up the all-important Budget Report, that is, a comparison of budgeted versus actual expenses. After going through the trouble of recording all of our expenses, this Budget Report will tell us if we are on budget or not. The pivot table we created in the last part already gives us a good summary of our expenditures. Ideally, we would like to insert some columns into the pivot table where we could display the budget estimates right next the actual expenses. Unfortunately, pivot tables are too inflexible for this. We could copy the pivot table values into a second table that would be more flexible, but this starts getting complicated real fast. Fortunately, there is much more direct way.

If we want to compare our actual expenditures with our budget, we need to summarize our actual amounts, and the best way to do that is with an Excel function called SUMIFS. You may already be familiar with the SUMIF function, which is handy for adding up numbers that meet a certain condition. And yes, if all we wanted was to find the total expenses in each category and did not care about the month, the simple SUMIF function would be fine.

But for our summary, we want to add up the expenses based on two conditions, Category and Month. For example, we want to see the total Groceries expenses for each individual month: Jan, Feb, and Mar. The SUMIFS function is perfect for this. Unlike the simple SUMIF function, it can add up items according to any number of different conditions. And unlike the pivot table, the SUMIFS function updates itself automatically. Think of that last “S” in SUMIFS as standing for “Super”.

Because of space considerations, we will do a Budget Report only for the first quarter of the year. The procedures for the other quarters are exactly the same.

As mentioned above, this project was done using Excel 2016 (updated by Office 365) running on Windows 10. Most if not all aspects of this project can be performed on other versions of Excel, but the exact instructions may differ.

Click here for step-by-step instructions in a PDF file

Click here for the Excel file

How to Summarize Data with a PivotTable

In the first two parts of our Advanced Budget Project for Excel 2016 in Windows, we set up the Budget itself and then created a Ledger table to keep track of our expenditures. In the Ledger table, we set up filters using Excel’s Slicers so that we could see the total expenditures for each expense category and each month. The Slicer buttons are slick, but it will be tedious to use them to examine every single category and every month. There must be a better way.

And there is, of course. The fastest and easiest way to summarize a table is to use Excel’s PivotTables. This amazing feature has somehow obtained the reputation of being difficult, but I will let you in on a secret: Even though Pivot Tables are incredibly powerful, they are also surprisingly easy.

In this third part of our Advanced Budget project, we will set up a Pivot Table to see how the expenditures in our Expense Ledger are doing. In just a handful of mouse clicks, we’ll be able to peruse a summary of total expenses for all categories and months at the same time. We’ll also use two Excel features to give us a visual summary of our expenditures. Sparklines will show us a basic look at how our expenditures vary month to month, while a Pivot Chart will give us a much more detailed look.

Once you create a Pivot Table, you will be amazed at how easy it was. But don’t tell anyone. Let everyone continue to believe that this feature is too difficult for most people. And, of course, they will all be very impressed when you create them so quickly.

For instructions on Creating a PivotTable, click here.

For complete instructions on the Advanced Budget Project along with other projects in our Advanced Excel Course, click here.

— Rich Malloy

New WordPress Course

  • The One-Hour Website: How to Create a Website with WordPress

    WordPress Transp logo_500x500WordPress.com is the fastest and least expensive way to get a professional looking website up and running. No programming language is involved. Join thousands of photographers, bloggers and  small businesses creating new WordPress sites each day. In this hands-on course, you will learn how to get a site up quickly, how to refine it with the right design, how to add new content quickly, and how to format pictures for the best impact. We will also discuss how to get your new site noticed by Google. Students need to have an email address/account that they can access from the classroom. The course is taught at Norwalk Community College by Rich Malloy, MBA.

  • Click here for details at NCC.
    2 Sessions, Thursday 6:00PM-9:00PM Begins 6/15/2017
    Tuition: $99

Advanced Excel Budget Project: Part B – The Ledger

Having a budget is a great first step. Now that we created a budget in the first part of this project, we must follow that budget, or at least try to. This means categorizing and adding up perhaps hundreds of expenses throughout the year. As we shall see, Excel has special functions that will make categorizing and adding a breeze. But first we have to record all the expense data into a table we call the Ledger.

A Ledger is simply a record of all income and expenses. In this part of the project, we will create a ledger for expenses. Later the fun will begin when we conjure up some special magic in Excel to see if we are on — or off — budget.

For instructions on Creating a Ledger, click here.

For complete instructions on the Advanced Budget Project along with other projects in our Advanced Excel Course, click here.

Advanced Excel Budget Project: Part A – The Budget

A budget is probably the most important spreadsheet you can create. A good budget will keep you focused on your ultimate financial goal and help you avoid pitfalls like that adorable timeshare on Aruba. Budgets are surprisingly popular among rich people. You would think that wealthy people have so much money that they do not need a budget. But according to the book The Millionaire Next Door by Thomas J. Stanley and William D. Danko, most wealthy people in fact do have budgets, which may be part of the reason why they are rich in the first place.

In this project, we are going to set up not only a budget, but also a ledger to keep track of expenses and a budget report to show how our actual expenditures compared with our budget. This project presents an ideal case for taking advantage of some of the more advanced features of Excel.

In this first part of our Advanced Budget Project, we are going to set up the actual budget. A budget is simply a plan, in this case a plan for getting and spending money. Like all plans, budgets do not fare extremely well when they come up against reality. But that may not entirely be the point. President and five-star general Dwight D. Eisenhower once said, “In preparing for battle I have always found that plans are useless, but planning is indispensable.”

Click here for more information.

Advanced Excel – Summer Class

As part of its summer session, Norwalk Community College is again offering an Advanced Excel class taught by Rich Malloy. Most people who use Microsoft Excel are familiar with only a few features of this amazingly powerful application. In this hands-on course, students will learn how to use the more robust features of this vitally important business tool.

The logo for Excel 2016 from Microsoft

The course includes a review of Excel’s database features such as Pivot Tables along with the powerful Lookup functions. Students will then learn how to automate data input with Data Validation, how to check formulas with auditing tools, how to use Excel’s new tables and slicers, and how to take advantage of the power of macros and the Visual Basic for Applications (VBA) programming language.

The four-session course is being made available by the college’s Extended Studies & Workforce Education Division. The first session starts at 6 pm on Tuesday, June 6. Tuition is just $229. Go to www.norwalk.edu to sign up, or visit the campus (directions). The CRN number for the course is: 6871.——————–—————————

Mail Merge in 10 Easy Steps

The Mail Merge feature of Microsoft Word is a great way to produce a large number of personalized letters or labels in a short amount of time. The process can seem daunting to a beginner, but if you break it down into a series of steps, is very easy to manage.

The Mail Merge feature of Microsoft Word is a great way to produce a large number of personalized letters or labels in a short amount of time. The process can seem daunting to a beginner, but if you break it down into a series of steps, is very easy to manage.

The Mail Merge process basically involves taking two files and merging them together. The first file is a letter, which is a basic word document. The second is a list of recipients. This list could be a table in Microsoft Word, but most often it is a worksheet in Excel. In this example, we will use an Excel spreadsheet and a simple letter that has already been created in Word.

1. Prepare the List of Recipients

The list of recipients is simply a table of names and addresses. There must be only one row of column headers at the top of the table, and each column headers should be unique. It will save some time later if you use a few standard labels in the column headers, such as, Last Name, Street, City, etc. I have prepared a very simple table of names and addresses in the file Mail_Merge_Recipients.xlsx. If you look carefully, you will note that the Postal Code column is set as text, which is why the ZIP codes appear on the left side of the cells. Unfortunately, this is mandatory: You must set the Postal Code column as text. Otherwise the leading zeros that are used in certain U.S. ZIP codes will be truncated off by Excel when it exports it to Microsoft Word. (This problem will occur even if you use the special Zip Code format of Excel.) Close the Excel file and proceed to the next step.

2. Prepare the Letter Document

You can use almost any document in Mail Merge. I have prepared a simple letter with the file name Mail_Merge_Letter.docx. The date near the top is set to update every time we create a new batch of letters, which is a good idea for a Mail Merge letter. There is a placeholder for the Inside Address and another for the salutation line. (We could also put some information from the recipient list into the body of the letter. For example, we can add the line, “I hope things are going well in X,” Where X would be substituted by the recipient’s city. But such simple-minded gimmicks impress nobody, and for this example we’ll keep things simple.)

3. Start the Mail Merge

In Microsoft Word, if you want to start a Mail Merge, you will of course go to the Mailings tab. In that tab, click the button: Start Mail Merge. A menu of possibilities appears, and easiest choice is to go to the bottom and employ the Step-by-Step Mail Merge Wizard. So far, pretty simple, right?

4. Choose the Document Type

The Mail Merge Wizard has just six steps, the first of which is the easiest. It defaults to creating a letter, which is exactly what we want. So, all you need to do is go to the next step. Click the button at the bottom right-hand corner: Next: Starting document.

5. Choose the Document

We already have our document open, so all we need to do is click Next: Select recipients. (I told you it was easy!)

6. Choose a Recipient List

Now we choose the second ingredient in our Mail Merge recipe, the list of recipients. Click the Browse button and browse to the Excel spreadsheet that we looked at earlier. A dialog box should open up, showing all of the rows and columns of our Excel spreadsheet. To the left of each row there are checkboxes which we can use to manually select who should receive our letter. Also, note that each of the column headers has a filter button, a drop-down arrow which we could use to select which groups of recipients will receive the letter. (We could also use these filter buttons to sort our letters. In some cases, we can get discounted postal rates if we were to sort the letters in the order of their ZIP Codes.) In this example will leave all our recipient selected so that everyone will receive one of our amazing letters. Click OK to close the dialog box, and then click Next: Write your letter.

7. Write the Letter

Well, our letter is pretty much already written. But we do need to add two things: the inside address, and the salutation or greeting line. Delete the text that says Inside Address and leave the mouse pointer on that line. In the Mail Merge task pane at the right, click the option: Address block. A dialog box will appear asking you to confirm that the name and address information is correct. After you click OK, a merge field code will appear in your letter. This code is distinguished by the double angle brackets that enclose it. As with all fields, Microsoft Word will replace it with some relevant information, in this case, a few lines that list the name and address of the recipient. Because we used standard labels in the column headers of our Excel spreadsheet, Word knows how to combine the name and address information in a suitable way. (If we had not used standard labels, we would now have to tell Word which of our labels corresponds to the standard labels, so that Word could assemble the address block as needed.)

Next, we have to add the salutation or greeting line. Delete the text now in the salutation and click the Greeting line option in the task pane on the right. A dialog box will appear asking you to confirm the structure of the salutation. Because this is a business letter, we need to change the punctuation to a colon. Click the list arrow at the right near the comma and change it to a colon. Then click OK.

In this step, we have added two merge fields. Be sure there is a blank line below each merge field. It looks a little cryptic right now, but that will soon change—as soon as you click Next: Preview your letters.

8. Preview the Letters

Prepare to be amazed: In this step, the merge fields have been replaced with actual data. You can use the arrow buttons to move forward or backward in your recipient list to see how each of the letters will appear. If you see any mistakes regarding line spacing or word spacing in the salutation, this is a good chance to fix that. Assuming that everything looks fine, let’s go on to the next step. Click Next: Complete the merge.

9. Perform the Mail Merge

Before we do the actual merge, it’s a good idea to save our work: Press Ctrl + S. Now, as you can see in the task pane on the right, there are two basic choices. If everything looks pretty good so far, you can take a chance and click the button: Print. This will merge our letter with our recipient list and print out X number of letters. But if there’s a mistake someplace in the letter, you may print out X number of mistakes.

If you’re the more cautious type like me, the better choice is: Edit individual letters. This creates a new document which is composed of all the individual letters that are created in the Mail Merge process. We can now go through this batch of letters and correct any mistakes. We could also add a little personalization to a particular letter, for example, “I enjoyed seeing you at the park last week.” After we make our choice, Word will ask you to confirm that you want to print all the letters, which you usually want to do.

In the new “Letters” document that appears, note that each of the letters is separated not by a Page Break, but by a Section Break (Next Page). This means that each of the individual letters are actually sections of the document. I’m not sure why Microsoft chose to break up the letters this way, but it does have an important consequence for us: If we want to print only certain letters in the document, we would specify not their page numbers, as we would usually do. Instead, we must specify the section numbers. That is, instead of entering 1, 4, 7, in the box for Pages to be printed, we would instead enter: S1, S4, S7.

Let’s assume that all the letters look fine, and we have plenty of paper and ink in our printer. What we need to do now is print the Letters document. Once it is printed, the Letters document is no longer needed. We can use the original letter document we just saved a moment ago to create a new collection of letters in just a few mouse clicks. So, close the Letters document without saving it. The original mail-merge letter document will now appear, and that should be saved for possible reuse in the future.

10. Celebrate!

Well, it turned out to be even easier than I thought. Really only nine steps! Practice this a few times, and pretty soon you’ll be able to do it all by yourself—without the wizard.

Of course, there is one more step: If we want to mail these letters, we need to print either labels or envelopes. Sorry, we’ll have to leave that for another lesson.

— Rich Malloy

Computer Security Update

How to Protect Yourself from
Viruses, Worms, Hackers and
Other Computer Lowlife

Greenwich Library, Greenwich, CT
Wednesday, March 22, 2017, 2:00 pm

Computer Hacker: Learn how to protect yourself
Computer hacking has become an all too frequent topic in today’s news reports. It is more necessary than ever that we protect ourselves from the many threats that currently exist and that are sure to arrive in the future.

In this course, you will learn about the various computer viruses and worms that can afflict your computer, smartphone and other equipment. You will hear how hackers can guess passwords for bank accounts or spy on baby monitor cameras. More important, you will learn several valuable safeguards and best practices that can keep your computers and your data safe.

Rich Malloy, M.B.A., works as a computer consultant based in Greenwich, CT.  He also serves as an Adjunct Professor at Norwalk Community College, where he teaches computer applications.  He is a Microsoft-certified Expert in Excel and Word.  Previously Rich served as Executive Editor of Byte magazine and Editor in Chief of Mobile Computing magazine.

Spring Forward, Fall Back—Time to Check Your Backup

Twice a year, we change our clocks. Safety experts tell us that these two occasions are also ideal times to check the batteries in our smoke detectors. Well, there is one more thing we need to check on a semiannual basis: our data backup systems.

Yes, I know. Computer backup systems are about as exciting as insurance policies. We set them up and then just assume they continue to work. Then one day, when we need them the most, we find out that they stopped working two years ago!

So, let’s take a quick look at your backup system. The exact procedure varies depending on which of the many backup programs you are using. But the general process is the same and it’s very simple:

How to Check Your Backup System

  1. Think of a file that you updated a few days ago.
  2. Open your backup program.
  3. Start the Restore process.
  4. Browse to the desired file.
  5. Restore it to a test folder on your desktop.
  6. Open the file to be sure it is intact.
  7. Then delete the file in the test folder.

That’s it. If everything works fine, you’re all set for the next 6 months. If not, well, you need to find out what’s wrong with your backup system and correct it as soon as possible.

By the way, if you are among the 75 percent of computer users who don’t back up their data at all, consider this a wakeup call. You are skating on thin ice, and it is only a matter of time before you lose irreplaceable photographs and other files. Start setting up a backup system right away.

 

Passwords Don’t Make Sense …

What Shakespeare Could Tell Us About Good Passwords

shakespeare-fr-wikipedia-250pxYes, passwords don’t make sense, or more accurately, they shouldn’t. What I mean is, if you have a password that is easy to remember, it is probably no good. If your password is a simple word or name, you might as well publish it on Facebook. Even if the password is combination of words, or maybe even has a number or two, you and your data are skating on thin ice.

Hackers are cobbling together super-powerful PCs from parts such as old video-game consoles. What do they use them for? Play games? Predict the weather? No. They are used to guess people’s passwords, at the rate of gazillions a minute. And they are getting faster and better every day.

Actually, in a sense they already probably know your password. Thanks to companies with lax security, millions of in-use passwords have been stolen and are freely available on the Internet. Many of our favorite passwords are no doubt in this collection. If your password is your child’s name followed by the year she was born, they probably already have that. If you cleverly switch the “a” with the symbol “@”, they probably have that as well. We are seeing, as security expert Steve Gibson termed it, “The Death of Clever.”

The best passwords are long strings of completely random characters, upper case, lower case, numerals, and symbols. And, we should have a different password for each of our accounts. But who can handle that? Well, there is a nice way of doing that – a password manager. I will mention more about that in another post. But right now, I’ll describe a handy way of creating pseudo-random passwords that are memorable.

Take one of your handy books, songs or poems. Look at the second line (the first may be too obvious). Take the first letter of each word. Capitalize the words that are already capitalized and any long word. Be sure to include punctuation marks. After the punctuation, include a numeral (say, the length of the word preceding the punctuation). Stop when you have at least 12 characters. (Experts now recommend at least 14 characters.)

So, for example, here is the second sentence from Shakespeare’s Sonnet 116:

Love is not love
Which alters when it alteration finds,
Or bends with the remover to remove.

The password might be:

LinlWawiAf,5

Now, don’t use this technique exactly as I described it. Customize it in your own way. Just be sure to remember how you did it.