How to Use VLOOKUP

In this video, we use the amazingly powerful VLOOKUP function to make it easier to keep track of the calories we consume.

New Video: Dieting with Excel, Part 4

Well, it took a while, but I finally finished the fourth and final part of my video series on Dieting with Excel. In this video, we use the amazingly powerful VLOOKUP function to make it easier to keep track of the calories we consume.

And as always, If you want to see the other videos in this series as well as several other videos and resources by Rich Malloy and Tech Help Today, be sure to click that same link: https://techhelptoday.com/videos/

To follow along, click https://techhelptoday.com/videos/ and click the link for the Excel workbook designed for this video.

Here’s a summary of the various parts in the Dieting Series:

Part 1: Dieting with a simple table
Part 2: Dieting with Excel Tables
Part 3: Dieting with Pivot Tables
Part 4: Dieting with the VLOOKUP function

The Mail-Merge Number Format Disappointment

No more 9.48999999999999!
Microsoft has finally fixed the number format problem in Microsoft Word’s Mail Merge feature. But not completely.

When 9.5 turns into 9.48999999999999!
Has Microsoft finally fixed the number format problem in Microsoft Word’s Mail Merge feature? Some glimmers of hope — But no.

Platform: Windows & Mac
Apps: Word & Excel
Version: Microsoft 365 (nee Office 365), June 2021
Date: June 8, 2021, Updated: June 21, 2021

I was fooled! I thought that Microsoft had finally fixed that annoying number formatting problem in Mail Merge. Some early tests with the latest version of Microsoft 365 indicated that this was so. But I celebrated too soon. It turns out that the number formatting works correctly, but only in very special circumstances.

Here is what happened: I was doing some tests to see if there had been any improvement in the way Mail Merge worked. I set up an Excel workbook with columns of numbers. In each row of the numbers, I used a different number format: No decimal places in one row, then one decimal place, then two, then the Currency format, etc. Well, guess what happened? When I brought these numbers into a Word document using Mail Merge, the formats were perfect! Could it be? Had Microsoft finally fixed the number formatting problem? It seemed so, and I was ecstatic! Sort of. I quickly put together a blog post and a YouTube video to spread the good news.

But then the bad news. A few days later, I did a real-world test. After all, nobody puts a different number format on each row of a column. In this new test, I did what everybody does: the same format for each row.

And once again, you can guess what happened. In the Word document, the formats were terrible. Again, the 16 decimal places returned. What was going on?

Did Microsoft change the program again? I returned to me first test with the different format, and it still worked. After a few hours of more testing, I determined that there is some strange bug in Word 2019. If one or two of the numbers in a column have a different format from the others, the number formatting works fine in Mail Merge. Huh?

From what little I know of programming, I can’t imagine how this could happen. Maybe it’s just me. Or maybe Word does have the capability to handle number formatting correctly, but there’s a slight bug in the code. And maybe, just maybe, Microsoft will spot it and fix it.

Then, we can really celebrate. Until then, we have to use the three techniques I mentioned in another post (https://techhelptoday.com/3-ways-to-fix-mail-merge-number-formatting-in-microsoft-word/).

Mail Merge is an awesome tool, in fact, it’s my favorite part of Microsoft Word. If you use it right, it can save you countless hours of tedious work. Let’s hope Microsoft fixes this number formatting problem, and that they do it soon.

— Rich Malloy, Tech Help Today

Checkbook Register in Excel

I’ve created a new video showing Excel beginners how to create a checkbook register. If you follow along with the video, you will end up, not only with a simple but useful spreadsheet, but also with a grasp of some fundamental Excel skills.

For this video I used Excel 2016 running in Windows, but the procedure outlined here can be performed in all recent versions of Excel. If you are an Excel beginner, watch the video, open a blank workbook, and have fun!

https://youtu.be/KtovaP-5NDU

Advanced Excel Class

An Excel spreadsheet on a laptop.Microsoft has equipped the latest versions of Excel with some amazing “Power” features. In this hands-on Advanced Excel 2016 course taught by Rich Malloy at Norwalk Community College, students will learn how to use the most important of these industrial-strength tools: specifically, Power Query (also known as Get & Transform), Power Pivot, and Power View.

The course includes a review of Excel’s database features such as pivot tables, the VLOOKUP function, and the INDEX/MATCH combination. Students will also learn how to use Excel’s new formatted tables and slicers and how to take advantage of macros and the Visual Basic for Applications programming language.

The logo for Excel 2016 from MicrosoftThe six-session course will be held on Wednesday evenings from 6 pm to 9 pm. The first session is scheduled to start on April 10. Tuition is just $349. Go to www.norwalk.edu to sign up (use CRN number 5210), or visit the campus (directions).

Advanced Excel Class

An Excel spreadsheet on a laptop.Microsoft has equipped the latest versions of Excel with some amazing “Power” features. In this hands-on Advanced Excel 2016 course taught by Rich Malloy at Norwalk Community College, students will learn how to use the most important of these industrial-strength tools: specifically, Power Query (also known as Get & Transform), Power Pivot, and Power View.

The course includes a review of Excel’s database features such as pivot tables, the VLOOKUP function, and the INDEX/MATCH combination. Students will also learn how to use Excel’s new formatted tables and slicers and how to take advantage of macros and the Visual Basic for Applications programming language.

The logo for Excel 2016 from MicrosoftThe four-session course will be held on Wednesday evenings from 6 pm to 9 pm. The first session is scheduled to start on Monday, October 29. Tuition is just $249. Go to www.norwalk.edu to sign up (use CRN number 6809), or visit the campus (directions).

1-Hour Website Course: Sept. 26

WordPress logoThe next session of my 1-Hour Website mini-course will be on Wednesday evening, September 26. WordPress.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. Continue reading “1-Hour Website Course: Sept. 26”

Microsoft Access Course at NCC

This fall Rich Malloy will again be offering his hands-on course covering the Microsoft Access database program at Norwalk Community College will be taught by Rich Malloy.

Databases are indispensable for businesses, and the most popular database program for small businesses is Microsoft Access 2016. This object-oriented relational database enables even non-technical users to organize, analyze and report on large amounts of complex data. AC00-Access-logo-fr-MicrosoftStudents will learn the essentials for planning and designing databases, building and modifying tables and forms, defining table relationships, producing reports, working with queries in Access and integrating with Microsoft Word and Excel. During this six-week class, each student will create a complete database application for managing contacts.

Date/Time: Six Monday evenings, 6-9 pm, starting September 17, 2018. To register or to obtain further information on the course, click: norwalk.edu/extended-studies/.