Excel tips and tutorials
For the past 82 years (approximately), I’ve used Excel almost every day. Along the way, I’ve learned a few tricks, and produced some sophisticated workbooks.
One of the joys of Excel is that there’s always something new to learn. And this week I learned how to freeze panes to hide rows in Excel.
Thanks for participating in the Very Scary Fall Giveaway for Excel Nerds! Your Excel horror stories were truly frightening, and the costume ideas were very creative. Thanks again to the generous prize donors:
As a bonus for everyone who entered the giveaway, Patrick O’Beirne has an entertaining pdf file — “The Devil’s guide to creating spreadsheets.” If you’d like a copy, send me an email at ddalgleish @ contextures.com and I’ll email you a link to the download.
I did a random draw of prizes and entries, using the macro that I created for the Summer Giveaway for Excel Nerds. You can see the Summer video there, and download the macro sample file.
After the numbers were selected, I used VLOOKUP formulas to pull the prize names and winner names from the original lists of numbered prizes and entries. Congratulations to all the winners! Here’s the list:
I’ll send an email to all the winners today, with instructions for claiming your prize. If you’re on the list of winners, and don’t receive an email by end of today, please let me know at ddalgleish @ contextures.com or add a comment here.
Please reply by 5 PM (Eastern time zone) on Monday November 9, 2009, or your prize will be forfeited.
______________
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.
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!
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.
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.
Finally, after checking a few of the problem sheets, I spotted a similarity.
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.
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.
Apostrophes are okay though!
In addition to those rules, I have a couple of guidelines of my own.
There’s lots more information about Excel names on my Contextures site, and sample files to download.
____________
The Very Scary Fall Giveaway for Excel Nerds is going strong, with lots of frightening Excel horror stories and some scary costume ideas.
Please take a few minutes to read the comments, and add your contribution. Then, keep reading, to see how to stop automatic hyperlinks in Excel.
It’s time for another Excel giveaway! Hallowe’en is just around the corner, so let’s find out what Excel Nerds do to celebrate. I’ve been given some awesome Excel utilities, books, and ebooks to give away, so let’s have some fun. Rules are at the end of this article.
UPDATE: This giveaway has ended.
To enter, write an original (and suitable for work!) comment below, describing either:
OR
The submission deadline is 12:00 noon (Eastern Daylight Time) on Tuesday, October 27, 2009. Winners will be announced here on October 28th.
Note: You can make as many comments as you like, but only the first one will count as your entry.
Some scarily smart Excel authors, developers and publishers have contributed a monstrous mound of books and utilities for me to give away as treats.
Thanks to all the contributors – Patrick “Ogre” O’Beirne, Jan Karel “The Creeper” Pieterse, Charley “Crypt-Kicker” Kyd, John “Walking Dead” Walkenbach, Jon “Petrifier” Peltier, “Chiller” Chandoo and Matt “Killer” Kennedy.
From Patrick O’Beirne of Systems Modelling Ltd
From Jan Karel Pieterse, of JKP Application Development Services:
From Charley Kyd of ExcelUser, Inc.
From John Walkenbach of J-Walk & Associates, Inc.
From Jon Peltier, of Peltier Technical Services:
From Chandoo
From Matt Kennedy of Apress, 2 prizes – e-books that you can download from the Apress website:
And finally, from Contextures – 3 prizes. The 3 winners can each select one of my pivot table books:
______________
How much time do you spend trying to find and fix error in your Excel workbooks? Or even worse, fixing errors in other people’s workbooks.
Earlier this month, I showed how you can do some simple troubleshooting for Excel formulas, in this article: See Formulas on an Excel Worksheet
After he read that article, Patrick O’Beirne, author of Spreadsheet Check and Control , asked if I’d like a review copy of his new Excel add-in, XLTest. This tool is designed to test your workbooks, in Excel 2007 and earlier versions
Even though all my workbooks are error free 😉 I accepted Patrick’s offer.
Patrick sent the add-in, instructions, and a couple of sample files.
Here’s a screen shot of the first sample. If your files look like this, you might need more help than this add-in can provide!
After I installed the add-in, a drop-down menu appeared on the Ribbon, as well as all the icons.
I’d rather have just the drop-down menu, so maybe there’s a way to turn off one or the other.
The add-in adds 8 key shortcuts that you can see in the Ribbon, and in the popup menu that appears when you right-click a cell.
Since the add-in features these shortcuts, let’s look at a few of those first.
The rest of the commands let you test your workbooks for errors, starting with the Start New Test Session command.
It opens a dialog box that lets you choose from 4 options for keeping logs, recording settings, opening files and closing open workbooks.
Next, you can document your workbook with the Worksheet Documentation command. Select all the options, or just a few, and list the results in a new workbook or existing one.
All the details are reported in a well organized worksheet. Here’s a small section of the report for the demo file.
In Excel 2007, when I selected Number Formats with the other options, the Format Cells dialog box stayed open, and none of the Custom Number Formats were listed in the documentation.
Everything else was correctly documented though, including the VBA modules.
For me, the main feature in the XL Test add-in is the Detailed Inspection. Instead of spending hours or days combing through your worksheets, click a button and get a report in a few seconds.
Again, there were problems with reporting the Number Formats, but I’m sure Patrick can sort that out quickly.
It creates a detailed report, with errors and other problems listed. You can quickly focus on the crucial errors, and get things fixed.
I don’t know what happens if you choose to see errors in separate cells, and there are more errors than columns. Maybe it wraps around, or maybe its head explodes!
There are several other tests that you can run in the XLTest add-in. For example, colour and document the data validation, conditional formatting or number formats on a worksheet.
These test will quickly highlight any cells in a range that are different than their neighbours, and allow you to fix them.
After testing, you can click the add-in command to clear all the fill colour from a worksheet. Since the tests also add rectangle shapes with hyperlinks, it would help if those could also be removed with a single click.
The add-in also has a command for Batch Testing, so you can run all the tests on a workbook, with a single click, instead of running each test individually.
The documentation warns of Excel memory problems if you try this on a large workbook.
Test Cases: The XLTest add-in can run a list of tests, and create a report on the results of each test. Use this to ensure that a new version of a workbook works the same as the previous version, except where you have intentionally changed things. The add-in will also convert any existing Scenarios to test cases, so you can run those.
If you’re an expert programmer, you might have your own code that does error testing, comparison and housekeeping, so you won’t need Patrick’s add-in.
If you don’t have your own code, this add-in would be well worth its purchase price (£199, approx $296 US), in the time you’d save in looking for errors, and other tests.
Yes, the add-in is more expensive than many other utilities that I’ve seen. It’s a bargain though, when compared to hiring an Excel programmer or trying to do the testing yourself.
For workbooks that you’ve inherited from colleagues or clients, you might not even know where to begin the error hunt.
The XLTest add-in can do most of the detective work for you – it even unprotects and unhides sheets, rows and columns.
And, of course, the real value in XLTest is in finding those critical errors that you didn’t even know you should look for.
If the add-in saves your job, it’s priceless!
____________
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 the lookup table shown below:
In column B on the worksheet, there’s a different value for each code that’s entered in column A.
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:
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)
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:
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.
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))
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
On the Contextures website you can find more examples of the Excel INDEX function and the Excel MATCH function.
__________________
Garbage in, garbage out, am I right?
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.
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.
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.
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:
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:
What techniques do you use in your Excel files, to help people enter valid data?
Do you use the 3 techniques that I listed?
___________
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. 😉
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.
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.
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!
__________________
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”
