Skip to main content

Know Your Office: In Search Of Excel-lence

Column: Better Technology for Better Clients

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.