Contextures Blog - Page 110 of 140 - Excel tips and tutorials

Case Sensitive Lookup Formula 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?

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.
__________________

3 Ways to Prevent Excel Data Entry Errors

Garbage in, garbage out, am I right?

Valid Data is Crucial

You depend on Excel spreadsheets to show accurate data and analysis.

Enter the wrong data and the results will be incorrect, and that could have a negative impact on your business or reputation.

Safeguard Data Entry

Here are 3 ways to help safeguard your data entry, and I’m sure you use a few other methods in your worksheets too.

Here’s a screen shot of a data entry worksheet, where I’ve used each of those technique, to help people enter valid data.

But remember, these techniques help make your Excel files idiot resistant, not idiot proof!

You’ve met people, right? They’re very creative at finding ways around your helpful Excel data entry safeguards.

Data Validation Drop Down and colour coding

1) Use Keyboard Shortcuts

Keyboard shortcuts save you time and can prevent errors. Quickly enter the current date and time, or copy the cell above, instead of re-typing.

To enter the current date: Ctrl + ;
To enter the current time: Ctrl + :
To copy from the cell above: Ctrl + D

2) Use Drop Down Lists

Instead of allowing free-form data entry, you can add drop down lists to the worksheet using the Excel data validation feature.

For example, create a list of product names, and select from that list.

This prevents typos, and only valid entries are allowed. Then, if a product is discontinued, remove it from the list, and it won’t appear in the drop down for new entries.

To create a drop down list, follow these steps:

On a blank worksheet, create a list of items for the drop down list.
To name the list, select all the items, then click in the Name Box, at the left of the formula bar. Type a one word name for the list, such as Product_List then press the Enter key.
On the data entry sheet, select the cells where you want the drop down list.
On the Excel Ribbon, click the Data tab, then click Data Validation.
In the Data Validation dialog box, for Allow, select List.
In the Source box, type an equal sign, then the name of your list: =Product_List
Click OK.

3) Highlight Missing Data With Colour

Use a bright colour to highlight data entry cells that are empty. This makes it easy to spot the cells that have to be filled in.

To highlight an empty cell:

Select the cells that you want to highlight (to select non-adjacent cells, press the Ctrl key, then click on cells)
On the Excel Ribbon, click the Home tab, click Conditional Formatting, and click New Rule
In the Select a Rule Type section, click Use a Formula to Determine Which Cells to Format
In the Format Values Where This Formula is True box, type a formula that checks the active cell (Name Box shows active cell address), using double quote marks (“”) to test for an empty string. For example: =A3=””
Click Format, on the Fill tab select a color, then click OK, twice.

What Are Your Data Entry Safeguards?

What techniques do you use in your Excel files, to help people enter valid data?

Do you use the 3 techniques that I listed?

___________

Spreadsheet Demo May 1987-Excel-Trapeze

It’s Thanksgiving Day in Canada, so some of us will be eating turkey and watching football. However, we don’t limit ourselves to one day, like the USA – we celebrate for three days!

Tomorrow, eight feet of snow will fall, and we’ll stay inside for the rest of the winter. 😉

Computer Chronicles 1987

For those of you who don’t have football to watch today, here’s a program that you might find interesting.

It’s an episode of Computer Chronicles from May 1987, with the guests talking about two types of spreadsheets — Microsoft Excel, and Trapeze.

An Excel Demo

The episode starts with a discussion of “look and feel” and related lawsuits. Then, at the 8:00 mark in the program, the Excel demo starts, with Mike Slade from Microsoft, using a Macintosh.

He shows multiple worksheets open at the same time, links the sheets, creates a chart, and runs something called a macro.

This link will take you to a few seconds before the Excel demo starts.

I’m sure some of you started in Excel on the Mac, like I did, and this presentation will take you back to the good old days.

Microsoft Excel Demo multiple worksheets 1987

A Trapeze Demo

The Excel demo lasts about 3 minutes, then Andrew Wulf, from Data Tailor, shows his company’s Trapeze spreadsheet.

Trapeze is a freeform spreadsheet, and it can do charts in colour! (We did not have colour on our old Mac computer!)

I wondered what happened to Trapeze, and found a blog post by Andrew Wulf.

In the article, he reports that Apple’s Numbers program is similar to the old Trapeze program.

Since I haven’t used either program, I’ll take his word for it!

__________________

Grocery Store Charts and Other Signs

This week I was travelling, and signs are really important for helping you figure out where you are, and where you should be headed.

Whether you’re posting street signs, retail signs, or worksheet instructions, try to make them clear and helpful.
Continue reading “Grocery Store Charts and Other Signs”

See Formulas on Excel Worksheets

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.

___________________

Create Tableau Chart From Excel Data

Last month, I wrote about Tableau dashboards, and my first experiences with creating a Tableau workbook from Excel data.

In this post, you’ll see another sample dashboard, where you can experiment with the user controls.

Select one or more days of the week, or highlight a sales representative.

Tableau Dashboard

Here’s a screen shot of the Tableau dashboard, that focuses on Parts sales for each sales representative.

You can see the sales amounts per day of the week, average price compared to quantity sold, and the top three sales reps for parts.

To try the interactive features, go to the dashboard on the Public Tableau site.

Make Dashboards in Excel

You can make fancy, interactive dashboards in Excel too! For examples, tips, videos and ideas, go to the following dashboard pages on my Contextures site:

Dashboard Course Review – A detailed review of Mynda Treacy’s online Excel Dashboard course, which she opens 3-4 times each year, on her website.

Dashboard Problems Survey Results – I ran a survey, to ask if people used Excel Dashboards – Yes or No, and what problems they encountered. The detailed results and replies are shown on this page

Dashboard Templates– This page has sample Excel dashboard templates that you can download, to help you get started, or add new features to your existing dashboards

Dashboard Tips – These examples show common Excel dashboard mistakes, and how to fix them

_________________

Show Total Hours in a Pivot Table

In an Excel file, you might record the time that employee work on specific projects. For example, an employee worked on Project A for 8 hours Monday, 8 hours Tuesday, 6 hours Wednesday and 3 hours Thursday, for a total of 25 hours. Later, you can show total hours in a Pivot Table.
Continue reading “Show Total Hours in a Pivot Table”

Hide Duplicate Headings in Excel Report

A few weeks ago we looked at a way to fill blank cells in an Excel report, so you’d be able to filter or sort a table of data.

The downside of that technique is that it’s harder to pick out the sections in a report. The headings don’t pop off the page — they’re buried in a long list of items.

Continue reading “Hide Duplicate Headings in Excel Report”

Move Excel Data With Advanced Filter No Macro

In Excel, you can automatically copy data from one sheet to another, without using a macro.

For example, there’s a long list of orders on one worksheet, and you’d like to find your largest orders, and copy those to a different worksheet.

Continue reading “Move Excel Data With Advanced Filter No Macro”

Create List of Names In Excel Workbook

Well, I finally finished creating all the names in my client’s Excel workbook, and sent the file back to them.

To help my client see what I’d done, I created a list of the names in the workbook, and sent it along with the main file.

It was also a quick way for me to double-check the names and their formulas.

Create the List of Names

Here are the steps to create a list of names:

Insert a new worksheet, or select a cell in a blank area of an existing worksheet. On the Ribbon, click the Formulas tab.
In the Defined Names group, click Use in Formula

Excel Name Use in Formula

At the bottom of the list of names, click Paste Names (In Excel 2003, click Insert | Name | Paste)

Excel Name Paste Names

In the Paste Name dialog box, click Paste List.

Excel Name Paste Name

A 2-column list of names will be inserted, starting in the selected cell, so make sure you have room for your list.

What’s in the List?

The list of names will contain all the workbook level names, unless there’s a duplicate sheet level name on the sheet where the name list is pasted.

In that case, the sheet level name appears in the list, instead of the workbook level name.

Name Manager Add-In

I’ve mentioned Jan Karel Pieterse’s Name Manager before, as one of my favourite Excel add-ins.

If you’re working with names, in any version of Excel, you should download and install it. It’s free, easy to install, and simple, but powerful, to use.

You can download Name Manager for Excel 2007 or earlier versions.
______________