You’d agree, right? The more comfortable you are with your office colleagues, the more effective you are working together. Knowing how to use available resources makes you more efficient. Right? Such is the case with Microsoft Office. So in this case, for accountants, the colleague you want to get the most comfortable with is Excel. Microsoft, always the premier marketer, touts all the “great new functionality” in the latest version of Excel 2007. Truth be told, there’s a lot of functionality in the older versions of Excel that we still don’t know about. That’s about to change, with Lisa’s List of the Top 5 Excel Strengtheners. Most of these are not new; they’re just underused and maybe relocated in 2007.
- #1 Time Saver Extraordinaire: No More Duplicates
Our client confessed that their old database had lots of duplicate data. Before we exported it to their new CRM system, it had to be scrubbed so their clients were in there only once. That should be easy, right? Just use the data sort feature, sort by customer name and then manually delete the duplicates. Yeah, except they had more than 3,000 names, and that would get cumbersome. The quicker solution: Excel’s “remove duplicates” feature, which is made even easier in 2007. Just highlight the column(s), click on the Data Tools Group on the Data Ribbon, and choose Remove Duplicates. It’s done. In 2003, you can accomplish the same thing, but it takes a few more steps: Go to Help and search for delete duplicate rows. With either version there is one minefield to avoid: Make a copy of your data before you start just in case you notice something after you have saved the cleaned document.
- #2 Copying Formulas: The Simple, The Cool and The Mindblower
Everyone loves the ability to copy formulas. The simple method is to highlight the cell with the formula you’ve built, and then copy and paste to the new cells. A more seasoned user will go for the cool, by grabbing the fill handle at the bottom right corner of the cell and dragging it to the range of cells, which copies the formula in a group of consecutive cells. But if you really want to “wow” yourself, try this on a vertical column of data: Create your formula in the first cell and then double click on the fill handle to copy it down until you run out of data. This is really impressive with large data records where it is isn’t always easy to get to the last field when trying to drag a formula. (Guess what? This is not new to 2007).
- #3 The Cure for The Common ######
Go ahead, Excel, rub it in. You’ve just spent hours on a spreadsheet, and when you’re finished you get those annoying “######” to let you know that your data is larger than the size of your cell. Here’s your answer back: Highlight all the columns you are formatting and then double-click between one of the highlighted column letters. That’s the signal to “autosize” based on the size of the data. Want to clean up the entire worksheet all at once? Click the “select all” — that square in the top left corner of your spreadsheet where rows and columns first meet — and, again, double-click between one of the highlighted column letters. Whooosh! You’re formatted. And forget that cumbersome format > columns > autofit in 2003, or Home > cells group > format > autofit in 2007. And for part two, if you want to force some of your data onto a new line in the same cell, press ALT+ENTER to create a line break within the cell. Your row will automatically word wrap for you.
- #4 The Data-Entry Express Lane: No Line, No Waiting
The scope of a project for one of our physician practice clients was going to entail a lot of data entry. The job: Analyze monthly data (January through December) for multiple years (2001 through 2007). As accountants, we are “as one” with the number pad on the keyboard or adding machine, especially when we’re doing one long column in Excel. But gum up the works with a number of columns side-by-side, and we lose momentum after the last month of the year when we need to move to the next column. The solution? Select your block for data entry, rows and columns. Type the first entry, in this January 2001 data, and hit enter. This moves you down to the next highlighted cell in the column, and, best of all, when it finishes one column it automatically jumps to the start of the second one.
- #5 Worksheet Building on Steroids
One of our distribution clients was refining how it monitored its five sales reps. The goal: Set up five worksheets in one workbook, one for each rep, and then import data. Knowing that formatting and cell name information would be the same for each rep’s worksheet, their accountant suggested the tried-and-true shortcut: Copy one sheet to another by right-clicking on the sheet name, choose move or copy, tell it where you want the new sheet, and check “Create a Copy.” This is easy enough for setting up just five sales reps, but it’s pretty tedious when you have to set up each rep’s 30+ customers in a workbook. The solution: Excel’s cool feature that allows you to enter or edit data on multiple worksheets at the same time. For the salesperson workbook, start by clicking the tab of the first worksheet, holding down CTRL, and click on the tabs of the other four worksheets. (Notice your title bar indicates you are in group mode.) Then, on the column you want months, type in January to December. Voila! All 12 months’ titles in all five worksheets. Want to save even more time? Just type Jan (the first month) into the first cell and drag the fill handle in the lower right corner of the cell down. Excel automatically fills in the rest of the months. A limited number of keystrokes, and you’ve got all the month’s title fields on five worksheets. Then, simply click on one of the sheet name tabs to come out of group mode to enter individual data.
So now the question: What to do with all this time you are saving? Invest it in learning one new feature each week. Go to http://office.microsoft.com for a wealth of information to get you moving or stop by our website for some tips at www.kianoff.com/officetips.
So … get to know your Office, Microsoft that is, and you will build your Excel-lence. You’ll be amazed at how much there is to learn.