Create Excel Grand Totals With One Click

I hope you had a wonderful Christmas, and with any luck, you're taking this entire week off. You might still be full of turkey and eggnog, so I'll just give you a small Excel tip today – something that's easy to digest.

Create Quick Excel Grand Totals

Instead of entering each SUM function individually, you can use the AutoSum feature to create all the grand totals with one click.

  1. Select all the cells with numbers, and the blank cells below and to the right, where you want the grand totals
  2. On the Excel Ribbon, click the Home tab
  3. Click the AutoSum button, to insert the Grand totals.

The SUM function is added to each grand total cell, to sum the cells above, or to the left.

Watch the Video

Watch this short Excel video tutorial to see the steps for creating Grand Totals in Excel with one click.

P.S.: For more Excel SUM tips visit the Contexture Excel SUM Functions page.

Quick Excel Tips: Paste Values and Sort

How long is your attention span? It's a short work week, so let's start it off with a couple of quick Excel tips. And when I say "Quick," I mean, "Don't blink, or you'll miss them."
I recorded a few quick Excel tips, and posted them in the Excel Quick Tips playlist on my Contextures YouTube channel. If you can't sit still through a lengthy 2 minute video, this might be the solution!

Select a Random Name

The first video shows you how to select a random name from a list. Enter the RAND function in the column adjacent to the list of name. Then, sort the column with the RAND function, and the name at the top is the winner.

This is the same technique that I used to select winners in my Excel Summer Giveaway and Excel Fall Giveaway contests. You can see the RAND function being used in the Excel random name draw video.

Paste Formulas as Values

In the second video, you can see how to use the mouse to copy cells that contain formulas, and paste the results as values.

If that was too quick for you, there's a longer version of this video that shows the Paste Values in Excel with a Mouse Shortcut technique.

The Weather Outside is Frightful

Let it snow! One of the advantages of working from home in Canada, is that you don't have to go out in rush hour, on snowy days. I can sit in my office, basking in the glow of the computer monitor, mesmerized by the flickering of the modem lights.
But eventually I'll have to go out to do some shovelling, in the sub-zero temperatures. Later, while thawing out, I'll create an Excel file, to track the miserable temperature and snowfall accumulation.

A Matter of Degree

thermometer We record our temperatures in Celsius, while our neighbours in the USA use a Fahrenheit scale. So, while I'm shivering on a -10°C day, it seems much warmer across the lake, where it's a balmy 8°F.
I'm sure there are good reasons why the USA didn't switch to the metric system when Canada did, but for now, we can use Excel to convert the temperatures.

Use Arithmetic

Maybe the temperature in the USA really isn't as warm as it seems. To convert the temperature from Fahrenheit to Celsius , you can use this formula:
°C = (°F – 32) x 5/9
If the Fahrenheit temperature is in cell B2, put this formula in cell C2:
=(B2 – 32) * 5/9
When I convert that balmy 8°F, it makes me feel better – it's actually colder there, at -13°C.

Let Excel Convert It

That formula isn't too difficult, but it might be hard to remember if your brain is affected by the cold weather. An easier way to convert the temperature is to use Excel's CONVERT function.
Note: If you're using Excel 2003, or an earlier version, you'll need to install the Analysis ToolPak to use the CONVERT function.
With the CONVERT function, you refer to the cell that contains the amount that you want to convert. Then you enter the original unit of measurement, and then the new unit of measurement. We want to convert the value in cell B2, from Celsius ("C") to Fahrenheit ("F").
Later, I can use CONVERT to see how many inches of snow we got, when the weather channel reports the snowfall in centimetres.
If you aren't sure what code to use for each unit of measurement, you can check the list in Excel's help for the CONVERT function.
Convert List
Now I have to go and figure out how many glasses of wine are in that 750 ml bottle. I think the answer might be – not enough!

For a Good Time Call Excel

Yes, Excel can help you have a good TIME. It's also useful if you're looking for a DATE, or the perfect MATCH. (Insert your own bad puns here.)
Today we'll focus on the TIME function, because one of my clients recently wanted some help with that.

What Time Is It?

There are a couple of keyboards shortcuts for entering the date or time.

  • To enter the current date on a worksheet, press the Ctrl key, and tap the ; key.
  • To enter the current time, press Ctrl and Shift, and tap the ; key

If you want the date or time to update when you open the workbook, you can use a formula instead.

  • To enter the date with a formula, type:  =TODAY()
  • To enter the date and time with a formula, type: =NOW()

Is It Too Late?

My client's workbook had a cell with a process start time. He wanted to check if the current time was later than the start time.
Compare Times in Excel
The formula in cell C6 compares C4 to C2. If the time in C4 is greater than the time in C2, the result is TRUE. Because C4 is empty in this screen shot, the result is FALSE.
If we use the NOW function in C4, it includes the date, as well as the current time. Even though the time in C4 is 8:17 AM, its value is much higher than the 9:00 AM time in C2.
I've added temporary formulas in column D, so you can see the numeric value for each cell in column C.
Now Function in Excel

It's About Time

To calculate the current time value in C4, we can change the formula, to remove the date value.
The TRUNC function removes the fractional part of a number, so we'll subtract TRUNC(NOW()) from the NOW function.
Translated to English, the formula is: (DATE and TIME) minus DATE
Now Trunc Function
The revised formula leaves the time value only in C4, and now the value in D4 is lower than the value in D2.
The formula in C6 now shows the correct result of FALSE.

More Date and Time Info

For the ultimate source of Excel Date and Time information, go to Chip Pearson's Dates and Times in Excel page. Chip explains:

  • how Excel's date system works
  • date and time arithmetic
  • how to calculate working times
  • formulas to find out when the next holiday occurs
  • using dates and times in VBA
  • and even the elusive DATEDIF function

On his blog, Chandoo, at Pointy Haired Dilbert, shows how to calculate Thanksgiving dates, although it might be a bit late to figure out when Thanksgiving is this year.

Print Selected Items on Excel Invoice

You have an Excel order form with a long list of products to select. After a customer enters quantities for the products they want to order, you want to print an invoice for the selected items.

Select the Products

Here's the OrderForm sheet, where quantities have been entered for four of the products, in column E.
Formulas in column B check the quantity column (E). If the quantity is filled in, then the next available number is calculated.

Print the Order

On the Invoice sheet, formulas pull the data from the Order form. For example, this formula shows the name of the first product that was ordered:

Here's the Order Form sheet, ready to be printed. The numbered rows from the Products sheet have been added to the Order form. Products without a quantity, such as Sweater, don't appear.

The Sample File

To download the sample file, please visit the Excel Sample Files page, on my Contextures website. In the Functions section, look for FN0027 - Print Marked Invoice Items.

Case Sensitive Lookup in Excel

In a lookup table, how can you get Excel to find the item that's the exact match, including the upper and lower case?
For example, in this table, row 4 is M7 and row 5 is m7. There's a different value for each of these in column B. If I do a lookup for m7, I want the result to be 5, not 4.

VLOOKUP Is Not Case Sensitive

You could try a VLOOKUP formula, to find the value for m7, but it's not case sensitive. In the following screenshot you can see a VLOOKUP formula in the formula bar, and the result of 4 in cell E1.
On the Microsoft website, there's an article that explains how to perform a case sensitive lookup. One sample formula uses IF and EXACT with VLOOKUP to check the case. In our sample sheet, the suggested formula is:
=IF(EXACT(D1,VLOOKUP(D1,A1:B6,1,FALSE))=TRUE,VLOOKUP(D1,A1:B6,2,FALSE),"No exact match")
However, this doesn't work in our sample table, because it stops at the M7, and that's not an exact match for the lookup value m7.

Case Sensitive INDEX MATCH

The Microsoft article has other sample formulas, including an INDEX MATCH, but they all have the same problem, stopping at the M7 above the m7 value.
Fortunately, a search in Google Groups led me to an array formula posted by my old friend, former Excel MVP Peo Sjoblom. For our table, Peo's formula would be:


This is an array formula, so type the formula then press Ctrl+Shift+Enter. Curly brackets will automatically appear at the start and end of the formula.
In the screenshot below you can see the formula, and the correct result of 5, in cell E1. The formula finds an exact, case sensitive match for the lookup value.

More Excel Function Examples

On the Contextures website you can find more examples of the Excel INDEX function and the Excel MATCH function.

Create Bingo Cards in Excel

It’s Friday, and things are slow at the office. To liven things up, you could create bingo cards in Excel, and organize a game during the lunch hour.
In this example, there are three cards, each with a set of random numbers. You’ll need one of those numbered ball popper machines though, or create a number selector in Excel.
Continue reading "Create Bingo Cards in Excel"

Create Quick Equations in Word 2007

Word 2007 has a new Building Blocks feature that lets you quickly add items, such as cover pages, text boxes, watermarks and page numbers. If you're writing a scientific document, you can also insert equations.

Insert an Equation

  1. On the Ribbon, click the Insert tab.
  2. In the Symbols group, click on Equation, then click the equation that you want to insert.

Save an Equation

After you insert an equation, you can modify it, then save it to use again later.

  1. Click on the equation in the Word document, to activate it.
  2. Click the arrow at the bottom right of the equation box, and click on Save as New Equation.
  3. Enter the details for your equation, then click OK.
  4. When you close Word, you'll be prompted to save the changes to the Building Blocks template. Click Yes to save your changes.

Insert a Saved Equation

Saved equations are listed in the Building Blocks Organizer. Open it and select the item you want to insert.

  1. On the Ribbon, click the Insert tab.
  2. In the Text group, click on Quick Parts, then click Building Blocks Organizer
  3. In the Building Block Organizer dialog box, click on the Gallery heading, to sort the list by type.
  4. Scroll down to the Equations, and click on an equation to select it.
  5. Click the Insert button.

PUP Add-in With Free Source Code

In other news, John Walkenbach is having a rare clearance sale. If you buy a licence for his Excel PUP add-in, for Excel 2007 or earlier version, he'll throw in the source code free. It's a great way to look under the hood, and learn some new tricks in Excel VBA. And the PUP add-in is packed with features that will help you use Excel more efficiently. It even has a few games, for those times when you're tired of working so hard.
The offer is only good until November 17th, so if you're thinking about buying it, don't think too long.