Busy professionals sometimes update to new software without the
time to explore fully all the new features. The present authors will
summarize, in a quick and easy to peruse format, certain features and
applications accounting professionals may wish to consider. Some of
these tips may work equally as well with earlier versions. Further, note
that when users of these tips select a command such as Tools located on
the menu toolbar, they may need to wait a few moments for the full
submenu of options to appear. Alternatively, users can click the double
arrows at the bottom of the menu item such as Tools for the full list of
submenu choices to appear. Since some users may not have accessed some
submenu options before, the options may be nearer the bottom of the
submenu lists.
Research Task Pane Part I (W & E)
Description:
A new online service is the Research Task Pane. One uses this pane
to seek quickly research assistance from a dictionary, thesaurus, and
encyclopedia.
Actions:
Connect to the Internet. Open a Word or Excel document. Select
Tools located on the menu toolbar, choose Research, in the Search for
box that appears to the right of your document or spreadsheet, type a
word or phase such as unearned income. From the dropdown menu below this
box click the down arrow, click a reference book such as Encarta
Dictionary: English (North America), if necessary click the green arrow.
Review pane outputs. Note in a similar fashion, one can also use the
Encarta Encyclopedia, the Thesaurus, or all reference books. To close
the Research Task Pane, click the x in the right hand corner of the
Research Task Pane.
Results:
The definition of unearned income will appear.
Research Task Pane Part II (W & E)
Description:
A new online service is the Research Task Pane. One uses this pane
to seek research assistance from MSN Money Stock Quotes and from Thomson
Gale Company Profiles.
Actions:
Connect to the Internet. Open a Word or Excel document. Select
Tools located on the menu tool bar, choose Research, in the Search for
box that appears to the right of your document or spreadsheet, key a
company's call letters such as GE. From the dropdown menu below the
box click the down arrow, select a reference such as MSN Money Stock
Quotes or Thomson Gale Company Profiles, if necessary click the green
arrow.
Results:
Information about the company, General Electric Company, will
appear in the Research Pane. From the MSN Money Stock Quotes, with a 20
or more minute delay, users view the stock price high, low, last, and
much more. Links to access stock charts also are available. From Thomson
Gale Company Profiles, users view the company address, phone, web,
ticker, revenue, number of employees, and similar information.
Worksheet Refreshed with Information on Up to 20 Stocks (E)
Description:
One can automatically generate and refresh a worksheet containing
recent information on up to 20 stocks.
Actions:
Connect to Internet. Open a new Excel worksheet. Click Al. Select
the Data command on the menu bar. Select Import External Data. Select
Import Data. Double click MSN MoneyCentral Investor Stock Quotes. Choose
the Existing Worksheet selection. Click OK. In the Parameter Value
dialog box, type up to 20 stock symbols. For this example, key CYL MRK
WWY. Add one space after each stock symbol. Click Use this
value/reference for future refreshes. Click OK. Be patient and wait for
a few moments for the completed worksheet to appear.
Results:
A worksheet will appear including the three companies, Community
Capital Corp., Merck & Co., Inc., and William Wrigley Jr. Co., as
row headings. For each stock, MSN Money supplies columnar information
including the Last, Previous Close, High, Low, Volume, Change, % Change,
52 Wk High, 52 Wk Low, Market Cap, EPS, P/E Ratio, and # Shares Out.
Other charts and news also are available by selection of underlined
words.
Diagrams Including Organizational Chart (W & E)
Description:
Users can insert diagrams in a Word or Excel documents. These
diagrams include an organizational chart, interlocking circles, a
pyramid, and more.
Actions:
Open a new document. For this example, open a new Word document and
select a place for your diagram. Click that place with your Mouse
pointer. Select Insert from the menu bar, choose Diagram. Note your
choice of diagrams. Double-click the Organizational Chart. Note the
Organizational Chart tool bar automatically appears in your document.
Click each of the four blocks and key a name. Note an extra toolbar has
appeared within your Word document. With your pointer in one block,
click the down arrow called Insert Shape on the Organizational Chart
tool bar. Select subordinate. Note the addition of the subordinate block
to your organizational chart. Complete the name in the subordinate block
of the chart. To increase the font size of the names in any block,
highlight the name, right click on the block, select Font from the menu
that pops up, and change size as desired. Click OK.
Results:
Displayed below is an individualized organizational chart with
names. For demonstration purposes, the font size for Jane Smith's
name is larger.
[ILLUSTRATION OMITTED]
Clip Art (W & E)
Description:
Users can add clip art to newsletters and similar documents to make
them more attractive. However, when using clip art about accountants one
needs to be careful not to promote obsolete stereotypes. Some examples
will illustrate.
Actions:
Place your Mouse pointer on the location where you wish to place
clip art in a Word document. Choose Insert on the menu bar, select
Picture, choose Clip Art. In the Search For box, key accountant. Click
the Go box. Using the downward arrow, scroll through the pictures
provided. Select the one you wish to appear in your document by
double-clicking your choice.
Results:
Some examples of stereotypical clip art that, in the opinions of
these authors, no longer represent typical accountants and that are not
appropriate in accountant newsletters are as follows:
[ILLUSTRATION OMITTED]
The first picture above shows outdated dress and hairstyle, no
computer, and dull-looking work. The second picture appears to be a
"green" eyeshade accountant who is behind on his work, and is
using a manual typewriter to help him catch up. The third picture looks
like an accountant that is too busy to accept any new work. None of
these images would be appropriate in accountant newsletters.
[ILLUSTRATION OMITTED]
Some examples of better clip art, more typical of today's
accountants, and clip art that inject appropriate humor are as follows:
Although these examples are among the best clip art using the key
word accountants, they are not stellar. The first picture includes a
laptop computer, modern higher quality, stuffed furniture, and an
uncluttered executive desk. The second picture could be used to remind
clients that it is tax time, that they do not want to do their returns
in pencil but to allow you to process them using modern computer
software programs and a professional staff. The third picture shows an
accountant thinking about an important business decision.
[ILLUSTRATION OMITTED]
[ILLUSTRATION OMITTED]
[ILLUSTRATION OMITTED]
[ILLUSTRATION OMITTED]
AutoSummarize (W)
Description:
Word will automatically summarize a document for you and present
the results in one of four formats. This feature is most useful when
someone sends you an extra long report.
Actions:
Open the selected lengthy Word document. Click Tools on the menu
bar, choose AutoSummarize. Note the four choices in the Type of Summary
section. Click on one of the four choices. From the drop down list in
the Length of Summary box, click the arrow, and choose the appropriate
length. The choices include assorted percentages of the original
document, a specific number of sentences, or a certain number of words.
Click OK.
Results:
As specified, users find the summary results highlighted in yellow
in the original document, summarized in front of the original document,
or summarized in a new Word document. This process also is interesting
to watch using your own document.
Thumbnails (W)
Description:
View a small copy of your document pages. Use this small copy to
help you navigate among the pages of the document and to visualize your
document.
Actions:
Choose View from the menu bar, select Thumbnails. To move to
another page in your document, click the thumbnail of other page. To
remove the Thumbnails, choose View, Thumbnails again.
Results:
On the left hand side of the Word document, a thumbnail miniature
of each page of the document appears. One can use these pages to move
among the pages of your document.
Short Cut for Frequently Used Text (W)
Description:
Users frequently key their own company name and other text. When
this text has an easy to remember short cut such as the company
initials, one can have Word change the initials to the proper company
name.
Actions:
As an example, let us use the name National Public Accountant. Drag
through the name, National Public Accountant, used in any Word document.
Select Insert from the menu bar, select AutoText, New, type NPA, and
click OK. Subsequently when you key NPA and then depress the F3 key, the
full name will replace your keyed initials in your Word document.
Results:
The NPA that you keyed will display in your document as National
Public Accountant.
Creating a Table without Excel (W)
Description:
Users can create tables in Excel and then copy and paste the tables
to Word documents. Additionally, users also can create tables in Word.
Actions:
Open a new Word document. Click the Mouse pointer for the placement
of a table in the Word document. Choose Table from the menu bar, select
Insert from the dropdown Table submenu, then choose Table. For our
example, from the drop down menu key 5 as the Number of Columns, key 6
as the Number of Rows, select the button for Fixed Column Width, select
the AutoFormat submenu, from the drop down Table Styles box select Table
Classic 2, click OK, click OK.
Results:
The results will look like the following:
[GRAPHIC OMITTED]
Actions:
Now fill in the rows and columns as follows. Move between the cells
using the tab and arrow keys.
Clients/Services January February March Total
Dalton 30 32 37 99
Moore 14 15 15 44
Patterson 20 30 41 91
Rich 21 30 36 87
Total 85 107 129 321
Results:
Once you have completed the table, it will look similar to the one
above.
Conditional Formatting (E)
Description:
One can automatically alter the formatting of a cell as specified
by a condition. For example, one may wish to display all negative
numbers in red.
Actions:
Drag through the numbered cells to be conditionally formatted.
Select Format from the menu bar, select Conditional Formatting, in the
second box select the appropriate condition, and in the right hand box
select the applicable number. In our example, we select Less Than in the
middle box, and 0 in the right hand box. Click Format. Choose the
Pattern tab. Click the red color. Choose OK. Choose OK again.
Results:
Excel will display all negative numbers in red.
Functions (E)
Description:
There are a number of mathematical and other types of functions
included in Excel. For convenience, Excel groups these functions by
categories. Some categories include Financial, Statistical, Math &
Trig, Date and Time, Database, Logical, and others. Each category has a
specific list of available functions. For example, some of the functions
included in the category Financial, are Future Value, Present Value,
Internal Rate of Return, loan payments and depreciation using assorted
methods, and more. Some functions in the category Statistical, are
Confidence Interval, Intercept, Normal Cumulative Distribution, Poisson
Distribution, and others. Once you select the function you wish to
perform, Excel provides you with dialog boxes to complete to supply the
dependent variables. As you use each dialog box, Excel provides a
definition for the box requirements following the last box to be
completed.
Actions:
As an example, let us solve a double declining balance depreciation
problem. Open a new Excel worksheet and place the mouse pointer in cell
Al. We will display our problem solution in this cell. Select Insert
from the menu bar, choose Function. From the drop down menu in the
category box, select Financial, from the list of financial choices,
select DDB, click OK. When the dialog box appears, key 11000 in the Cost
box. Note the definition of Cost will appear at the bottom following all
the dialog boxes. Continue by keying 1000 in the Salvage box, 4 in the
Life box, 1 in the Period box, and 2 in the Factor box. Click OK.
Results:
The first year double declining balance depreciation of $5,500 will
appear in Al of the worksheet. If you repeat the steps above keying 2,
3, and 4 respectively in the Period box, $2750, $1375, and $375
respectively will appear in cell Al for the second, third, and fourth
year depreciation.
Clear Content of Cells (E)
Description:
Users clear content of selected cells.
Actions:
Highlight filled cells one wishes to clear. Release the mouse.
Place the mouse pointer at the bottom right corner of the highlighted
cells. Wait until the fill handle appears. The fill handle is a black
plus sign without any pointing arrows on the ends. Hold down the mouse
pointer and drag it back over the filled cells one wishes to clear.
Results:
The cells no longer contain the filled content.
Clear Worksheet (E)
Description:
Users sometimes wish to clear the content of, format of, or all the
worksheet.
Actions:
Select Edit from the menu bar, choose Clear, select All or Format
or Contents.
Results:
The worksheet no longer includes the removed information or format.
Increase Column Widths or Row Heights (E)
Description:
Users can increase column widths or row heights by using the split
double arrow.
Actions:
Place your mouse pointer on the bottom right hand corner of a
column or row heading such as column C or row 2. Wait until the split
double arrow appears. This symbol looks like a plus sign with an arrow
added on the left and right hand parts of the plus sign. While holding
the mouse pointer down, drag the column to the right or the row downward
until you reach the desired width or height. Release the mouse pointer.
Results:
The column or row changes to the desired width or height.
Goal Seek (E)
Description:
Goal Seek alters a set of numbers to achieve desired results. For
example, one can take a current financial statement and determine the
needed sales for a 10 percent increase in gross profit.
Actions:
In cell Al, key Sales, in cell A2 key COGS and in cell A3 key Gross
Profit. In cell C1 key 100000, in cell C2 key =.60*C1 and in cell C3 key
=C1-C2. Select Tools from the menu bar, select Goal Seek. In the Set
Cell box key C3. In the To Value box key 44000. In the By Changing Cell
box, key C1. Click OK. Click OK.
Results:
The statement now reflects Sales of 110,000, COGS of 66,000, and
Gross Profit of 44,000. Complex financial statements are equally
appropriate for analyses using Goal Seek.
Sorting Data (E)
Description:
Excel will sort multiple columns of data. Both numerical and
alphabetic sorts are available. Ascending sorts list data from the
lowest number or the first alphabetic letter while the descending sorts
list data from the highest number or the last alphabetic letter. Sorts
are also available on multiple columns. For example, the primary or
first sort might be by salespersons and a secondary sort by sales of
that salesperson.
Actions:
As an example, key the following in a new worksheet beginning with
Salesperson in cell A1. Adjust column widths and row heights using the
Increase Column Widths or Row Heights (E) tip above.
Highlight cells A3.B9. Select Data, Sort. In the Sort by drop down
box, select Column A. Select the Ascending button. In the Then by box
select By Quantity or key Column B. Select Ascending button. Select the
button for My Range has no header row. Click OK.
Monthly
Sales
Salesperson By Quantity
Brown 1
Adams 3
Brown 5
Caldwell 2
Adams 3
Caldwell 5
Brown 9
Results:
Excel changes the data to display as follows:
Monthly
Sales
Salesperson By Quantity
Adams 3
Adams 3
Brown 1
Brown 5
Brown 9
Caldwell 2
Caldwell 5
The F4 Key (E)
Description:
When you create a formula in Excel, the formula can use relative
cell references, which refer to cells relative to the position of the
formula, or absolute references, which refer to cells in a specific
location. Formulas can also contain a mix of relative and absolute
references. An absolute reference is indicated by the $ symbol. For
example, $B$1, is an absolute reference to column B, row 1. Being able
to easily change column and row references from relative to absolute,
and back again, can be quite useful when copying formulas from one cell
to another.
Actions:
Select the cell that contains the formula. In the formula bar,
select the formula you want to change. Press F4 to toggle through the
relative/absolute combinations.
Results:
The following example illustrates the toggle sequence:
Original Formula = SUM(C1:C16)
F4 = SUM($C$1:$C$16)
F4 = SUM(C$1:C$16)
F4 = SUM($C1:$C16)
Maintaining the same relative reference when pasting a formula (E)
Description:
In many cases when you copy a cell containing relative references,
you want to avoid the formula adaptation when pasting the formula in a
different row or column. While you could use the F4 key to change the
formula back to absolute, then copy and paste, this method is repetitive
and annoying. An often overlooked solution is to copy the formula from
formula bar to formula.
Actions:
Select the cell B1 which contains the formula =C1+D1. Highlight the
formula in the formula bar and enter Ctrl+C to copy the formula to the
clipboard. Leave the formula bar by pressing Esc or Enter or Cancel
(clicking the X to the left of fx on the formula bar).
Select cell D3 and press Ctrl+V to paste the formula into cell D3.
Results:
Cell D3 now contains the formula = C1+D1.
Displaying Formula(s) in Cell(s) (E)
Description:
Select a single cell and view the syntax of the formula in the
formula bar. Use the Tool menu options to view all formulas in a
spreadsheet.
Actions:
Select the Tool menu, then Options. Then select the View tab and
check the Formulas option, then click OK. As an alternative you may use
Ctrl+~ (The ~ sign is located to the left of the number 1 on the
keyboard) to view all formulas. Press Ctrl+~ to view the formulas.
DIVISION A REVENUE (SALES) $233,200
DIVISION B REVENUE (SALES) $390,452
Results:
The formula results for Divisions A and B sales revenues are
displayed below:
DIVISION A REVENUE (SALES) =C19*C21
DIVISION B REVENUE (SALES) =C20*C22
To return to the normal view, press Ctrl+~ again.
Select Cells with Formulas for deletion, modification, or
protection (E)
Description
Use the Go-To dialog box to select all cells with formulas for the
purpose(s) of deletion or protection.
Actions:
From the Edit menu, select Go to or press F5 to access the Go to
dialog box. Click on the Special box. Select Formulas, deselect options
Numbers, Text, Logical, Error, then click OK.
Results:
All cells with formulas producing results of the appropriate format
are now highlighted and ready for deletion or protection operations.
Diana H. Clary, MBA, MPAcc, EdD, CMA, Professor Emerita of
Accounting, served 38 years in various teaching and administrative
positions in Virginia and South Carolina. Her last position before
retiring, June 2004, was Acting Dean of the School of Business
Administration and Economics, University of South Carolina Upstate.
Since retirement, she, her husband, and their two Pomeranians now reside
in Surfside Beach, SC. Her volunteer works include serving as a SCORE
counselor to small businesses. Her email address is dclary@sc.rr.com.
T. Brian Smith, AB, MMIS, is an Instructor at the School of
Business Administration and Economics, University of South Carolina
Upstate. A former IT Manager and network engineer, he's a member of
the ACM (Association of Computer Machinery) and the IEEE(Institute of
Electrical and Electronics Engineers). His email address is
tbsmith@uscupstate.edu.
COPYRIGHT 2005 National Society of Public
Accountants Reproduced with permission of the copyright holder. Further reproduction or distribution is prohibited without permission.
Copyright 2005, Gale Group. All rights
reserved. Gale Group is a Thomson Corporation Company.
NOTE: All illustrations and photos have been removed from this article.