Category: Excel Formulas

After a long day of dealing with Excel formulas, pivot tables, and hidden Ribbon commands, you might be ready for an evening cocktail. Or six!

Newspaper Study

I found a link to this Canadian drinking study in the Toronto paper today, and it looks like we’re a nation of teetotalers. Is your country the same?

Apparently most Canadians don’t work with Excel, or perhaps they vent their frustrations on the hockey or curling rink at the end of the day.

The Excel Drink Calculator

To spare you the agony of checking your results in a hard-to-read green pie chart, I created an Excel version of the online drink calculator.

Use the Drink Calculator

In cells C3 to C9, enter your estimated number of drinks per day.

• The SUM Function in cell C10 calculates your total for the week.

Then, select Men or Women from the drop down list in cell C12.

• The VLOOKUP formula in cell C15 will compare your results to the rest of the men or women in Canada.

Hmmm…more than 85% – that can’t be good.

Time to move, I think. 😉 What country would you recommend?

Download the Excel Drink Calculator

If you’re brave enough to test your own results, you can download the Excel Drink Calculator. It’s in Excel 2007 format, with no macros.

Please do not operate heavy machinery, or leave a comment, if you have been drinking.
______

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 quick and easy 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.

Here are the simple steps to follow:

First, select all the cells with numbers, and the blank cells below and to the right, of those cells, where you want the grand totals

Use the AutoSum Button

Next, go to the Excel Ribbon, and click the Home tab

At the right end of the Home tab, in the Editing group, click the AutoSum button, to insert the Grand totals.

Grand Totals in Blank Cells

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

Watch the Video

To see me create Grand Totals in Excel with one click on the AutoSum button, you can watch this 12-second Excel video.

P.S.: For more Excel SUM tips visit my Contextures Excel SUM Functions page.

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

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.

Excel 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”).

• =CONVERT(B2,”C”,”F”)

Later, I can use CONVERT to see how many inches of snow we got, when the weather channel reports the snowfall in centimetres.

Excel Help for CONVERT Units

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.

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!

___________

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.

To help avoid errors, he wanted to check if the current time was later than the start time.

Worksheet Time Calculations

The formula in cell C6 compares C4 to C2. I

• f 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.

Excel NOW Function

If we use the NOW function in C4, it includes the date, as well as the current time.

As a result, 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, in the screen shot below.

Formula to Remove Date Value

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.

=NOW()-TRUNC(NOW())

Translated to English, the formula is: (DATE and TIME) minus DATE

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 more examples of Excel Date and Time functions, go to the Excel Date and Time page on my Contextures site.

___________

Do you have Excel horror stories, that you like to tell around the campfire, to scare your friends?

One of my recent Excel horror stories involves Excel sheet names. I set up a client’s workbook with pre-formatted data entry sheets, so sales managers could plan their annual product promotions.

They would rename the data entry sheets while working, to make it easier to navigate the completed workbook.

Hidden Sheet With Formulas

On a hidden summary sheet in the workbook, I added formulas to calculate the sheet names.

In another column on that sheet, a few Excel INDIRECT function formulas pulled data from specific cells on each data entry sheet, and other formulas created grand totals.

At the front of the workbook, the summary data was displayed in a monthly calendar, for sales managers to review. It was a work of art!

The Scary Phone Call

Everything worked well in testing, so we distributed the files to all the sales managers, and they started filling in their data.

The next day, the phone rang – some of the workbooks were “broken.”

Budget deadlines were looming, and the sales managers with broken files were in a panic. They sent me a couple of problem files, so I could figure out what was wrong.

Summary Sheet Formula Errors

On the Summary sheet, some of the formulas were working correctly, but others showed #REF! errors.

Comparing the good and bad sheets, I couldn’t see any problems with the data that had been entered, at first glance.

Sheet Name Apostrophes

Finally, after checking a few of the problem sheets, I spotted a similarity.

• All of the problem sheets included an apostrophe in the sheet name!
• I removed the apostrophes, and the problem was solved.

All the data showed up in the summary sheets, and the world was in harmony once again.

Note: For the next version of the workbook, I updated the workbook’s Summary sheet formulas, using the Excel SUBSTITUTE function.

Sheet Naming Rules

I hadn’t anticipated that problem, since I never use apostrophes in sheet names. They’re valid characters for a sheet name, but maybe they shouldn’t be.

It’s hard to find the sheet naming rules in Excel’s help, but you may have seen an Excel error message that lists them.

1. The name can’t be more than 31 characters
2. You can’t leave the sheet tab blank
3. Only a few characters are listed as invalid, like the following ones from the error message below:

• : \ / ? * [ ]
• colon, back slash, forward slash, question mark, asterisk, open square bracket, close square bracket

Apostrophes are okay though!

Sheet Naming Suggestions

In addition to those rules, I have a couple of guidelines of my own.

1. Use only letters, numbers and underscores in sheet names.
   • Sometimes I have to use a space character, if a client requests specific sheet names, but I try to avoid it.
   • For example, I’d use SalesData or Sales_Data, not Sales Data.
   • And please – don’t use apostrophes!
2. Use different names for worksheets and named ranges, to avoid confusion.

There’s lots more information about Excel names on my Contextures site, and sample files to download.

Your Sheet Naming Rules

• What kind of names do you use for worksheets?
• Any characters that you avoid or problems you’ve run into?

____________

In a lookup table, how can you get Excel to find the item that’s the exact match, including the upper and lower case?

Sample Data to Check

For example, in the lookup table shown below:

• Cell A4 contains the value,  M7, which has an upper-case M
• Cell A4 contains the value, m7, which has a lower-case m

ID Numbers

In column B on the worksheet, there’s a different value for each code that’s entered in column A.

• So, if I do a lookup for m7, I want the result to be 5, not 4.
• And if I do a lookup for M7, I want the result to be 4, not 5.

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, cell E4 is selected, and its  VLOOKUP formula is visible in the formula bar:

• =VLOOKUP(D1,$A$1:$B$6,2,0)

The formula refers to cell D1, where I’ve type m7, which has a lower-case m.

The 4th argument of the VLOOKUP is set as 0, which means “find an exact match”

However, the formula result is 4, which is the ID number for the M7 code (upper-case)

Microsoft Formula Suggestion

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, Microsoft’s suggested formula is:

• =IF(EXACT(D1,VLOOKUP(D1,A1:B6,1,FALSE))=TRUE, VLOOKUP(D1,A1:B6,2,FALSE), “No exact match”)

However, this does NOT 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 — they stop 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 array-entered INDEX, MATCH and EXACT combination:

=INDEX(B1:B6,MATCH(1,--EXACT(A1:A6,D1),0))

• Note: To array-enter this formula, type the formula, then press Ctrl+Shift+Enter. Curly brackets will automatically appear at the start and end of the formula.

Case Sensitive Formula – Correct Result

In the screenshot below, the revised formula is entered in cell E1.

Its formula is visible in the Formula bar, and the correct result of 5, is showing in cell E1.

The formula finds an exact, case-sensitive match for the lookup value, m7, that I typed in cell D1

More Excel Function Examples

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

Last week I was testing a client’s workbook, and had filled in all the data entry cells, to make sure everything was working correctly.

Find Data Entry Cells

Before sending the workbook back to my client, I wanted to clear all the data entry cells.

Instead of selecting each cell individually, and clearing it, it would be easier to clear groups of adjacent cells where possible.

However, some cells had formulas, and I didn’t want to accidentally clear any of those.

If the formulas are visible, that would prevent the problem.

See Formulas in Excel 2003

If you’re using Excel 2003, follow these steps to see the formulas on the worksheet:

• On the Tools menu, click Options
• On the View tab, add a check mark to Formulas.

See Formulas in Excel 2007

If you’re using Excel 2007, follow these steps to see the formulas on the worksheet, instead of the formula results:

• Click the Office button, then click Excel Options
• Click the Advanced category
• In the Display Options for This Worksheet section, add a check mark to
  • Show formulas in cells instead of their calculated results.

Show or Hide Formulas with a Keyboard Shortcut

The keyboard shortcut to show or hide the formulas is

•  Ctrl + ` 

The symbol at the right is an accent grave, and that key is above the Tab key on the my laptop’s keyboard. It might be in a different location on yours

The accent grave looks similar to an apostrophe, but its top leans to the left, instead of being straight up and down.

___________________