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.
Thanks for reading CPA Practice Advisor!
Subscribe for free to get personalized daily content, newsletters, continuing education, podcasts, whitepapers and more…
Subscribe Already registered? Log In
Need more information? Read the FAQs
