When looking up information in Microsoft Excel, you’ll regularly need to compare data against a table—and tables can be found in a variety of locations and formats. For example, in the image above you may need to find the appropriate tax rate in the table on the right for a given employee’s salary listed in the table on the left.
On the Formulas tab in the Excel ribbon, you’ll see a categories function called Look up and Reference. The two key functions for this type of task are VLOOKUP (V meaning vertical) and its companion function HLOOKUP (H meaning horizontal).
Why two functions instead of one? As shown in the image below, data tables can be found in horizontal and vertical orientations—so with two dedicated functions, you’re covered either way.
Published by Curt Frye | Monday, August 20th, 2012
Curt Frye is the author of over a dozen lynda.com courses and more than 20 books on Microsoft Excel, including Microsoft Excel 2010 Step by Step for Microsoft Press. He is also a popular speaker, presenting his Improspectives® keynote addresses and workshops for corporate clients.
Recognizing when your numbers don’t add up is key to successful operations management, which is why organizations of all types and sizes use the Excel spreadsheet program to manage their operations and inventory. A real-world example of this might be conducting a monthly inventory analysis that compares the number of products in your system with the units counted in your warehouse.
In Excel 2007 and 2010, you can quickly check for differences between these two inventory numbers. First, you select the numbers in your worksheet.
Next, go to the Home tab on the ribbon, click the Find & Select button, and then click Go To Special.
Your Excel data is laid out in two columns, so you want to look for differences between the two cells within each row (A2 compared to B2, A3 compared to B3, and so on). To do this, select the Row differences radio button in the Go To Special dialogue box and click OK.
When you click OK, Excel examines the selected cell range for differences between cells in the same row and highlights cells in the right-hand column that are different from their mates in the left-hand column.
In this case, cells B4 and B7 contain values that differ from their mates in cells A4 and A7.
If your data were arranged in rows, you could highlight cells with different values by selecting the data cells in the worksheet and clicking the Column differences radio button in the Go To Special dialog box.
The Go To Special dialog box is often overlooked by even advanced Excel users, but it’s worth exploring all its useful options.
Interested in more? • All Business courses on lynda.com
• All Excel courses on lynda.com
• All courses by Curt Frye on lynda.com
Microsoft Excel is well-known as the industry-standard spreadsheet program, and it is really a veritable Swiss Army knife of an application. It can be used for anything from making a quick structured chart to a reasonably powerful database. The fact that it’s hard to pin down just how people want to use Excel—anything from managing finances to data presentation—is the precise reason our library is full of specialized Excel courses. So how do you find the courses that will suit your particular Excel needs best?
What if you’re not a math wiz and you just want to learn how to manage the deluge of data that’s coming your way? You could be trying to makes sense of painstaking measurements you’ve documented for your own personal goals, or you could be trying to wrangle a collection of raw numbers from your latest auto-generated sales report. Which of the lynda.com Excel courses are going to help you analyze those numbers so that you can communicate trends, build strategies, and create the justification for a call to action? In this learning path, I’ll take you through some key Excel courses designed specifically to help you manage your data so that it is accessible and useful to you in your life, your work, and your community.
1. Starting from square one: I just need to learn the core Excel features and where they live in my version of the program.
First things first, you need to know what Excel can do and how to access the tools that might help your project. Whether you’ve never used Excel before or you’ve only touched it when you absolutely had to, the best place to get started is with Excel portion of our Essential Training series. If you’re using the most current version of Excel, start with Excel 2010 Essential Training, orExcel for Mac 2011 Essential Training, depending on your platform. If you haven’t updated to the latest version of Excel, we also have Excel 2007 Essential Training and Excel 2003 Essential Training available. Regardless of which course you choose, you’ll find everything you need to know to get started efficiently creating your first spreadsheet.
2. Sorting basics: I know how to create a spreadsheet, but I haven’t ever explored the key sorting features.
If you’ve only performed the most basic of A>Z sorts, then our course on Managing and Analyzing Data in Excel will help you understand Excel’s quick and sophisticated options for sorting your numbers. For instance, you can teach Excel to recognize non-numerical information like months, days, or other human-centered data. Here’s a movie from the course on sorting based on the order of data in custom lists:
3. Investigating new perspectives: I know how to perform basic ranking and sorting functions, but I need to quickly see the data from different angles.
When you have data that needs to be quickly analyzed from different perspectives, by year, by company, or against some other variable, a pivot table helps you dynamically rearrange your table data to find the answer you need. Our Excel 2010 course on Pivot Tables In Depth shows you how this powerful feature works. Even if you’ve never created a pivot table before, this course will walk you through the process. Check out this movie to see how they work and why they are so powerful for data analysis:
4. Preparing data for efficient and accurate analysis: I know how to use the tools, but the raw data I’m getting is inconsistent and in multiple formats.
Sometimes you get handed automatically generated, or humanly created information that comes in formats that Excel doesn’t quite know how to read efficiently, if at all. To get some important tips and workarounds for making sure this data is consistent enough to sort, check out Cleaning up Your Excel Data. Here’s a great example on how to create Excel-readable dates from an inefficient mixture of raw date formats:
5. Ensuring valid results: I can perform all the key analysis functions, but my file is huge and I don’t have a way to check my results.
Our Excel: Data Validation in Depth course is designed to reveal the various ways you can command Excel to double-check your results for accuracy. In this course, you’ll see how to use features within the program to perform validation on your outcomes. Check out this introductory video from the course to see what Excel tricks you can learn from Dennis Taylor:
Of course, beyond these five jumping-off points, there’s still an incredible amount to be learned about Excel, and we have a wide variety of courses to help you take your next steps. In the library, you’ll find that courses also have alternate options that coincide with earlier versions of Excel, so there’s a little bit of something for everyone. Speaking from personal experience, even as a Mac user, I know that sections of these courses have come to my own number-crunching rescue many times.
What sorts of tasks do you want to do in Excel? How can we help clear a path for you to get to that knowledge?
Interested in more?
• See all the Microsoft Excel courses available on lynda.com.
I recently had the pleasure of presenting all the content we hope to publish for you in 2012 to our content and production teams here at lynda.com. It was a wonderful opportunity to talk in broad strokes about our teams’ collective vision for the future. If you’ve ever given a high stakes presentation in front of a large group, you know that while giving presentations is a great opportunity, they can also be quite daunting to prepare and deliver.
At the outset of my planning I found myself scrambling to remember the presentation skills I learned long ago. (Oh yes, I briefly longed for my college Public Speaking 101 notes and those mortifying VHS tapes of class speeches on global issues.) After sitting for a little while with presentation anxiety, I decided to turn to the same library that would be the subject of my presentation.
Browsing the lynda.com Online Training Library® as a member on a mission, I quickly found that our courses empowered me to compile and deliver a compelling and visually interesting presentation for my peers. It was exciting to find help waiting for me—and comforting to learn from the very authors I have the pleasure of working with each day.
In case you’re curious (or madly preparing for your own end-of-year or look-ahead presentations), here is my presentation learning-path that helped prepare and inspire me.
1. Duarte Design, Presentation Designer: Wanting to start with a good dose of inspiration, I turned to our Creative Inspirations documentary on Duarte Design. The opportunity to see how the pros create compelling presentations armed me with just enough confidence to think that maybe I could pull this off. It was here that I realized the lynda.com Online Training Library® could empower my presentation.
2. Effective Presentations (2006): After thinking about big picture, I needed some specifics, which is precisely what I found in Effective Presentations (2006). This course is one I’ll define as a classic. Built in 2006, it still has the power to inspire today. Chapter two on Mission, Goals and Story is the one that helped me organize my ideas more clearly.
3. Excel for Mac 2011 Essential Training: With my ideas taking shape, I needed to dive into some data to learn more about lynda.com viewing statistics, including, how often courses are watched, what courses are watched, and what members would like to see published in the future. This required me to brush up on my Excel for Mac 2011 skills, which helped me easily navigate lots of data with speed and efficiency.
4. Keynote ’09 Essential Training: With growing confidence backed up by numbers and solid data, I was ready to start putting my story for 2012′s business content into Keynote. Enter Keynote ’09 Essential Training, which helped this long-time PowerPoint user convert easily to the new interface and features. Pretty soon, I was tooling around with master slides, backgrounds, fonts, and styles.
6. Time Management Fundamentals: As the week went by and I got busier with this presentation, I noticed that I could easily lose track of minutes or hours if I didn’t keep my time in check. So I decided on another quick visit to Time Management Fundamentals. Dave Crenshaw reminded me that switch tasking wasn’t worth my time and that I needed to focus in on my most valuable activities, including that presentation.
7. Effective Meetings: As I started to wrap up my presentation and prepare to deliver it, I wanted to check in with Dave Crenshaw again on Effective Meetings. What would I need to know in order to get the most out of our all-day planning session? I wasn’t disappointed. The principles of successful meetings helped me determine a note-taking strategy and the best way to absorb exciting new information from my colleagues.
8. Pitching Projects and Products to Executives: Finally, the night before my presentation, I wanted another dose of inspiration and confidence to get me ready for the next morning. Pitching Projects and Products to Executives helped me develop that confidence and focus-in on conveying my story with powerful intention.
As Effective Presentations (2006) reminded me, an estimated 30 million presentations make their way in front of an audience every day, so I was in good company as I prepared to sell my ideas up, down, and sideways. I was also, it turns out, in good company when I turned to the lynda.com Online Training Library® for the tools and inspiration necessary to communicate more effectively and make a memorable impression.
I hope you’re well on your way to developing lynda.com learning paths that work for your needs and your schedule. Please share your inspiration below; we love to hear from you!
Interested in more?
• All business courses on lynda.com
If you’re still muttering to yourself about the drama that accompanied filing your business taxes, it isn’t too late to streamline the process for 2010. Personal finance expert and lynda.com author Bonnie Biafore has this advice:
“Before you forget, jot down the information you were missing, the reports you needed but didn’t have, and the journal entries your accountant had you record again this year,” says Biafore. “Then, log in to your QuickBooks company file. Set up accounts to track all your income and expenses the way you report it on your tax return. Customize some reports to match your accountant’s requests. And memorize last year’s journal entries so you can reuse them next time around.”
If you don’t already use QuickBooks, Bonnie’s QuickBooks Pro 2010 Essential Training gets you up to speed in setting up your books, running reports, and managing your company files. If you do use QuickBooks, this course offers great advice for getting the most out of all the core features in the latest version.
P.S. If you’re a spreadsheet addict who does all your financial planning and tracking in Excel, lynda.com has courses for Excel users at all levels. Use Numbers? We’ve got that covered. Google Docs spreadsheets? That, too.