Three Excel menus you may have overlooked

By David H. Ringstrom, CPA


Sheet Navigation

Everyone is familiar with using the arrow buttons in the lower left-hand corner of the Excel screen to push worksheet tabs back and forth across the screen. However, try right-clicking on these buttons instead, and you'll see a menu similar to Figure 1. Simply click on a sheet name to instantly activate that worksheet.

 

 

Figure 1: Many Excel users don't know that you can right-click on the navigation arrows.


Move Rows or Columns

Sometimes you need to rearrange columns or rows on your worksheet. Many users resort to inserting new, blank columns or rows, cutting the data to be moved, and then pasting it into the new location. Instead, there are two faster ways rearrange your worksheet:
 

  • Select the columns or rows you wish to move
  • Use your right mouse button to grab the border of your selection, and then move the columns or rows to the new location.
  • When you release your mouse, the menu shown in Figure 2 will appear. If you forget and use your left mouse button, you'll encounter a different prompt. Press Escape and then try again.

Figure 2: This hidden menu appears when you use the right-mouse button to drag a selection of cells.
Of course if you're just repositioning rows, you can bypass this menu entirely: simply hold down the Shift key while you move the columns or rows into their new position.


Hide and Unhide Worksheets

Most Excel 2007 users know that it's possible to hide or unhide worksheets:
 

  • Choose the Format icon in the Cells section of the Home menu
  • Choose Hide & Unhide
  • Choose Hide Sheet or Unhide Sheet.

However, there's an even easier way: As shown in Figure 3, right-click on a worksheet tab, and then choose Hide. You can also right-click on any worksheet tab and choose Unhide to display the Unhide Sheet dialog box. Although you can hide multiple worksheets at once — hold down the Ctrl key as you click on each worksheet, and then issue the Hide command — you must still unhide sheets one at a time. Anyone using Excel 2003 or earlier won't have this option and instead must choose Format, Sheet, and Hide or Unhide. In addition, you'll notice a new Protect Sheet command, which toggles to Unprotect Sheet when the worksheet is protected.

 

Figure 3: Excel 2007 has Hide, Unhide, and Protect Sheet commands on the worksheet menu.

 
A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

 

Introducing the AccountingWEBinar Series

AccountingWEB is pleased to announce a series of live webinars aimed squarely at the needs of CPAs in public and private practice. This wide-ranging series will cover the following topics:
•Microsoft Excel
•Microsoft Outlook
•Microsoft Word
•Microsoft Access
•Intuit QuickBooks
•Sage Peachtree Accounting
•XML/XBRL

Click here to read the full text of the article at AccountingWEB.com.

Provisioning your home accounting office

By David H. Ringstrom, CPA


As the economy continues to contract, more and more accountants are considering striking out on their own. A benefit to providing accounting services is that you can often ease your way into your own business, as long as you don't run afoul of any non-compete agreements with your primary employer. I'm sure you've read a million articles about how to establish a separate workspace that's all your own and how to organize your files, so instead I'm going to focus on elements of my own home office that I've found particularly effective.

Computer – I've always used a laptop as my primary machine, and I always purchase machines that can be snapped into a docking station, also commonly referred to as a port replicator. Doing so ensures that I don't have to take my power cord out of my bag, nor do I have to touch any other cables. If you choose a laptop, it's simple to connect an external monitor and expand your desktop across both monitors. I've gone a step further, and use Toshiba's DynaDock to have three screens. This allows me to have a dedicated e-mail monitor, as well as two others for whatever project I'm working on.

Printer – Laser printers offer far lower cost-per-page and crisper output. Indeed, if you're able to put your hands on a second-hand HP LaserJet IV or equivalent, you'll have a workhorse of a printer that will last for years — my LaserJet survived a lightning strike that took out all other equipment around it.

Scanner – I consider a scanner to be like a chainsaw…I don't use it very often, but when I need it, nothing else will do.

Shredder – Keep your shredder within an arms length of your desk so that you can immediately dispose of unneeded documents. Also, make sure to buy a heavy-duty version, as few things are more frustrating than trying to unjam a balky shredder, as you'll invariably end up with confetti on the floor.

Fax machine – Although I rarely use it, I keep a fax machine around for sending outgoing faxes. For years I've relied on the eFax service for incoming documents. Not only do my faxes appear in my inbox, my employees get copied on faxes automatically so I never have to forward a fax on.

eFax tip: If you already use eFax, be sure to log into the control panel and change the setting so that your faxes arrive as PDFs, instead of the proprietary eFax format. This can be a huge timesaver.

Separate telephone line with a headset – I've used my cell phone as my office number, which freed me up from checking two voice mailboxes. However, since I often have to walk outside to get a clear signal, I pay for a basic, no-frills landline that I use with a headset. Internet-based phone service, often referred to as VOIP, is an obvious alternative.

Back-up – I recommend a four-pronged approach:

  1. I back up highly critical data like my own accounting records, on flash drives. I recommend alternating between at least two.
  2. I also alternate backing up my entire hard drive between two external hard drives. I try to keep one drive as a far away from my desk as possible in the event of fire or other disaster. I use a program called SmartSync Pro  to back-up my data, but many other alternatives are available.
  3. I also recommend using an online back-up service like Carbonite, Jungle Disk, or other services.
  4. I also use the free Windows Live Sync service to automatically replicate files from my primary computer onto a secondary computer. At one point my laptop hard drive crashed, but everything I was working on was immediately available for use on the second computer.

Accounting Software – You could purchase retail versions of these programs, but consider these respective professional accountant programs instead:

  1. Intuit ProAdvisor
  2. Sage Accountant's Network
  3. Microsoft Professional Accountant's Network

Tip: Not only is Microsoft's program free, it also qualifies you to apply for a Microsoft ActionPack subscription, which provides a staggering amount of Microsoft software for just a few hundred dollars — think 10 Office 2007 Enterprise licenses, 10 Office 2003 Professional licenses, and on and on.

Tax Preparation Software – Tax preparers should consider a package such as ATX 1040, which starts at $410/year. You'll be able to do far more with it than consumer-grade products like TurboTax or TaxCut.

Remote access – Many folks are aware of GoToMyPC [12], but there are other players, like LogMeIn, that provide free or low-cost remote access to your clients' computers. This allows you to assist clients directly during the business day, or use their computers to carry out work after hours.

Screen capture program – As a freelance writer, SnagIt is an indispensible tool, but I often use it for my clients, too. When someone e-mails me a question about Excel, Peachtree, or QuickBooks, I'll often make a quick screen capture of the window in question, drop a word balloon with some text on the image, and e-mail it on. With regard to tech support, a picture is indeed worth a thousand words.

Security Software – In the good old days security software pretty much meant anti-virus protection, but these days you must now have firewall, anti-malware, and even encryption. Symantec and Trend Micro, among others, offer Internet security packages. Encryption is easier than you might think, as Jeff Cherrington, vice president of product management for PKWARE notes: “A home worker should be aware that it is critical to consider measures above and beyond firewalls and virus protection software to ensure security of sensitive data. Security that remains with data is needed now more than ever as more sophisticated means of cyber threats and identity theft emerge. Sensitive business or customer information is vulnerable to compromise as it travels across home wireless networks and over the Internet or as it is stored on computers, DVDs, or USB drives. Many home workers are familiar with using .zip to compress documents. Now, it is possible to .zip and encrypt all types of data such as e-mail, e-mail attachments, pdf files, .jpg files, and even Microsoft Office documents. The information assets are sealed in secured virtual containers while the data is in transit or is being stored. It is even possible to protect files that are sent or stored on such email and storage services offered by AOL, Yahoo, or Google, provided that the files are zipped and encrypted before they are sent or stored. Home workers wishing to send large e-mail attachments will be pleased to learn that usage of .zip compression together with encryption helps to avoid the problem of exceeding mail size limits that often result in bounce back messages or delays in getting necessary information to business contacts. Zipping and encrypting sensitive data renders it useless to cyber criminals or thieves, while enabling home workers to securely engage in necessary business interactions. SecureZIP enables users to secure files and folders with strong passphrase or digital certificate-based encryption. It also supports digital signatures to ensure data integrity SecureZIP saves and sends files securely directly from Microsoft Office applications, including Word, Excel, and PowerPoint. PKWAREs SecureZIP is available for a 30-day trial at www.pkware.com.”

 

A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Add contacts or meetings to Contact Manager or CRM with two keystrokes

Product review by David H. Ringstrom, CPA


Accountants of a certain age may remember a television show in which contestants would bid on how few notes they'd need to hear to identify an unidentified song. However, instead of saying, “Tom, I can name that tune in 5 notes,” you may find yourself thinking, “I can add a new contact to Outlook with just two keystrokes!” The key is a clever program called Anagam that enables you to quickly add onscreen information — without manual transcription — into your contact manager or CRM.

Anagram unobtrusively resides in the taskbar area of your Windows screen, as shown in Figure 1. A tip appears when you hover over the icon to remind you of the shortcut key to use. Anagram works with these applications:

 

  • Outlook 
  • Palm Desktop 
  • Gmail & Google Calendar 
  • Jigsaw 
  • Salesforce 
  • Netsuite 
  • Tablet PC

 

 

Figure 1: Anagram resides in the Windows Taskbar, and is always available.

The program is simple to use:

1. Select the text that you wish to capture, as shown in Figure 2. In this case I've selected text from the Contact page of AccountingWEB.

 

 

Figure 2: Select the text that you wish to capture.

2. Press F12 (or configure Anagram to use a different shortcut key). As shown in Figures 3 and 4, Anagram instantly parses the text into address fields.

 

 

Figure 3: Anagram parses text that you select into discrete contact fields.

 

 

Figure 4: Addresses are instantly dispersed into the proper fields.

3. Now press the Enter key to save the record to your contact manager or CRM software. That's all there is to it!

As shown in Figure 4, you can choose to rescan text as a to do (task item), event (calendar item), or memo. In addition, the captured text appears in a separate window, as shown in Figure 5, so you can make any edits you choose, and then scan the information again.

 

 

Figure 5: You can edit the captured text, and then rescan it, or simply convert the information to another type.

Many professionals incorporate their contact information into an e-mail signature block, so it's a snap for me to add new contacts to my address book. As shown above, you can also capture text from a web site, or pretty much anywhere on your screen. Keep in mind that Anagram requires selectable text, so it can't scan pictures of text.

Pricing

Anagram is free for Gmail/Google Calendar, and Jigsaw. Outlook and Palm Desktop users pay a one-time $34.95 charge, while SalesForce and NetSuite users pay $74.95/year. Volume discounts are available, and you can try Anagram free for 45 days.

 
A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Free Microsoft Excel-based 1040 form available

By David H. Ringstrom, CPA



Just in time for tax season, Glenn Reeves of Burlington, Kansas has created a free Microsoft Excel-based version of the 2008 U.S. Individual Tax Return, commonly known as Form 1040. The spreadsheet includes both pages of Form 1040, as well as these supplemental schedules:

 

  • Schedule A – Itemized Deductions
  • Schedule B – Ordinary Interest and Ordinary Dividends
  • Schedule C – Profit or Loss from Business
  • Schedule D – Capital Gains and Losses, along with its worksheet
  • Schedule SE – Self-Employment Tax
  • Form 2441 – Childcare and Dependent Care Expenses
  • Form 6251 – Alternative Minimum Tax – Individuals

The spreadsheet also includes several worksheets:

  • Line 10 – State and Local Tax Refund Worksheet
  • Lines 16a and 16b – Simplified Method Worksheet taxable annuities and pension benefits
  • Lines 20a and 20b – Social Security Benefits Worksheet
  • Line 32 – IRA Deduction Worksheet
  • Line 42 – Deductions for Exemptions Worksheet
  • Line 44 – Qualified Dividends and Capital Gain Tax Worksheet
  • Line 52 – Child Tax Credit Worksheet
  • Lines 64a and 64b – Earned Income Credit (EIC)

Six additional worksheets round out the tool:

  • W-2 input forms that support up to 4 employers for each spouse
  • 1099-R Retirement input forms for up to 4 payers for each spouse
  • SSA-1099 input form to record Social Security Benefits
  • A tax table
  • Earned Income Credit table
  • Change log that records revisions to the spreadsheet

All of the worksheets are password-protected, and most of the underlying formulas are hidden, but you're free to add new worksheets to the file, or create links to other workbooks. As you can see in Figure 1, the form exactly mirrors the official IRS format. The protection also means you can't add comments to cells within the forms, or make notes out to the right.

 

 

Figure 1: Glenn Reeves created an Excel-based version of IRS Form 1040

The spreadsheet also includes some basic error-checking features, as shown in Figure 2, as well as hints, as shown in Figure 3.

 

 

Figure 2: The spreadsheet contains basic error-checking prompts.

 

 

Figure 3: In some cases the spreadsheet offers helpful tax-planning hints.

The spreadsheet's author clearly states that the spreadsheet is available for free, but he does accept appreciation contributions. As you might expect for someone so intimately familiar with U.S. tax law, Mr. Reeves will report all contributions as income, but will also donate 10 percent of any proceeds to his church.

This free spreadsheet enables just about anyone to use Microsoft Excel to prepare and print their entire 1040 return. Along with the actual forms, the spreadsheet includes some IRS documentation, as well as links to download official IRS forms and instructions. Excel-based versions of Form 1040 are available for all years from 1996 through 2008. In addition, the 2008 format is also available in Open Document Format, which is compatible with the free Open Office suite.

The spreadsheet is available at www.excel1040.com.

 
A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

Excel text transformation tricks

By David H. Ringstrom, CPA


Data arrives on your desk from multiple sources, and in many cases it's not quite in the format that you need. For instance, a user may have left the CAPS LOCK on while typing in a series of addresses. Or you've exported data from a program that pads fields with spaces at the beginning or end. Fortunately, there's no need to retype the list. Excel offers four easy-to-use functions that can quickly transform data into just the format that you need.

UPPER – As shown in Figure 1, this function transforms text into all upper case, so the phrase Old McDonald's Farm becomes OLD MCDONALD'S FARM. This function only has one argument, so you simply type =UPPER(x), where x is a cell that contains text you wish to convert.

LOWER – This function transforms text into all lower case letters, so Old McDonald's Farm becomes old mcdonald's farm. As with the UPPER function, you simply use the form =LOWER(x), where x is a cell that you wish to convert to lower case.

PROPER – This function capitalizes the first letter of each word, so the phrase old mcdonald's farm becomes Old Mcdonald'S Farm. This function takes the same form as the other functions: =PROPER(x), where x is the cell to convert. However, as you can see in Figure 1, PROPER can present problems in some cases:

 

  • The first D in McDonald's doesn't get capitalized.
  • The S in McDonald's is capitalized — in addition to capitalizing the first letter of a word, PROPER also capitalizes the next letter after an apostrophe or single quote.
  • Other items to look for are acronyms or initials, where ABC COMPANY would become Abc Company or BOB'S TV SERVICE would become Bob'S TV Service

You'll have to manually clear up problems like ABC or TV being converted to Abc and Tv, but you can use Excel's Replace function to clear up other problems, as I'll explain in a moment.

TRIM – Sometimes you'll inherit data that has extraneous spaces at the beginning, middle, or end of the string, such as ” Old McDonald's Farm”. In such cases you can use the TRIM function to strip out all extraneous spaces — the function automatically keeps one space between each word. The function takes the form =TRIM(x) where x is a cell reference.

Figure 1: These four functions can quickly transform text without retyping.

Expert tip: Sometimes it's helpful to have white space that the start of certain words, as shown in Figure 2. Rather than manually enter spaces at the start of cells A3 through A5, try this instead:

 

  1. Press Ctrl-1 to display the Format Cells dialog box.
  2. Click on the Alignment tab, and set the Indent field to 1 or 2.
  3. Click OK and your text will automatically indented, as shown in Figure 3.
  4. You can remove all of the indenting at any time by changing the Indent back to 0.

Figure 2: Use the Indent setting to move text to the right without using the spacebar.

Figure 3: Set the Indent level to 1 or 2 to create a slight indent.

 

Convert Formulas to Values

Typically when you use these functions you'll have data in one column, such as column A in Figure 1. You'll enter the formula in column B, and then copy it down the length of your list. Presto, your list is transformed, but don't rush off and delete column A just yet. You must first copy the formulas in column B to the clipboard, and then use the Paste Special command to convert the formulas to values:

 

  1. Copy the formulas to the clipboard.
  2. Right-click on the first cell that you copied, and then choose Paste Special.
  3. As shown in Figure 4, you can double-click on the word Values, or click once on Values and then click OK.

At this point you can delete the data in column A, because your converted data is in column B as text rather than formulas that refer to column A. If you forget to convert the formulas in column B to values before you delete column A, then you'll end up with a series of #REF! errors. Simply press Ctrl-Z to undo the column deletion, and then carry out the Paste Special Values command.

Figure 4: The Paste Special Values command converts formulas to text.

 

A PROPER Clean-up

Once you've converted your formulas to values, you can use the Replace function to resolve problems like that pesky ‘S in McDonald'S. To do so, press Ctrl-H to display the Replace dialog box. As shown in Figure 5, enter ‘S in the Find field, and ‘s in the Replace With field. Repeat this process for any other issues, like replacing Mcd with McD, Tv with TV, and so on.
Figure 5: Use the Replace command to quickly clean up capitalization problems that PROPER introduces.


A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

 

Excel 2007: Five ways to streamline Excel’s user interface

By David H. Ringstrom, CPA


It's easy to get caught up in the day-to-day use of Excel 2007 and overlook minor tweaks that can make a significant difference in your use of Excel. In this article I'll review five of my favorite Excel 2007 tricks. Most involve customizing the user interface so that I can speed through my work without hitting unnecessary bumps:

1. Extend your recently used file list
By default Excel shows the 17 most recent files that you've opened, but as shown in Figure 1, you can expand this to a maximum of 50:

 

  • a. Click the Office button, and then click Excel Options.
  • b. Choose Advanced, and then scroll down to the Display section.
  • c. Change the Show This Number of Recent Documents from 17 to a maximum of 50.

Figure 1: You can expand the number of recent documents that appear when click the Office button.

 

Expert tip: You can pin files permanently to the Recent Documents menu: Click the pushpin that appears to the right of the file name. Your document may shift further down the list over time, but will always remain on the Recent Documents menu.

2. Utilize the Places Bar

As shown in Figure 2, the Places Bar is the list of icons on the left-hand side of the Office dialog boxes. You can place shortcuts to frequently used folders here:

 

  • a. Click once on the desired folder, so that the folder name becomes highlighted. Be sure not to drill down inside the folder, but rather just click once on its name.
  • b. Right-click in the Places Bar and choose Add.

You can right-click on an item in the Places bar and move it up or down one position at a time. This menu also allows you to remove items you've added to the Places Bar, but you cannot remove the default locations like My Recent Documents, My Documents, and so on.

Figure 2: The Places Bar can give you once-click access to widely-scattered folders.

3. Disable the Paste/Insert Icons

You may have noticed the little clipboard icon, shown in Figure 3, that appears when you paste data into a worksheet. This little icon makes a Paste Options menu available, from which you can make changes to how your data pasted on the worksheet. Some folks may find this helpful, but many find it annoying. Fortunately it's easy to eliminate these prompts:

 

  • a. Click the Office button, and then choose Excel Options.
  • b. As shown in Figure 4, choose Advanced, and then clear the check boxes for Show Paste Options Buttons and Show Insert Options Buttons in the Cut, Copy, and Paste section.

Figure 3: The Paste Options button can be disabled.

Figure 4: Clear the checkboxes shown to disable the Paste and Insert Options buttons.

4. Eliminate the Zoom Slider

As shown in Figure 5, the Zoom Slider appears in the lower right-hand corner of your Excel screen. It's easy to accidentally hit this when you're scrolling up and down or right and left. If you click the Zoom Slider, then suddenly your Excel spreadsheet may zoom in or out dramatically, leaving you to reset it again. The Zoom section of the View tab makes it easy to change the size of your text, so you may wish to turn the zoom slider off:

 

  • a. Right-click anywhere on the Status bar at the bottom of your screen.
  • b. Clear the checkbox for Zoom Slider.

Figure 5: Convenience aside, the Zoom Slider makes it easy to inadvertently resize your screen view.

5. Eliminate extraneous worksheets

By default Excel 2007 workbook includes three worksheets, but you often only need one or two tabs. Further, Excel 2007 includes a new Insert Worksheet tab, shown in Figure 6, which allows you to add a new worksheet with just one click. Thus it's helpful to always start with a single worksheet and then add new sheets as you need them. Here's how to change the default from 3 to 1:

 

  • a. Click the Office button, and then choose Excel Options.
  • b. As shown in Figure 7, change the Include This Many Sheets setting to 1.

Figure 6: The Insert Worksheet button makes it easy to add new sheets on the fly.

Figure 7: You can change the default number of worksheets from 3 to 1.

 
A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

 

Excel 2003: Six ways to streamline Excel’s user interface

By David H. Ringstrom, CPA

It's easy to get caught up in the day-to-day use of Excel 2003 and overlook minor tweaks that can make a significant difference in your use of Excel. In this article I'll review five of my favorite Excel 2003 tricks. Most involve customizing the user interface so that I can speed through my work without hitting unnecessary bumps or screen prompts. These tricks will work equally well in Excel 2002, but only some apply to Excel 97 and 2000:

1. Extend your recently used file list

By default Excel shows the four most recent files that you've opened, but you can expand this list to as many as 9:

 

  • a. Choose Tools, and then Options. 
  • b. As shown in Figure 1, click the General tab, and then set the Recently Used File List to a maximum of 9.

Figure 1: Expand your frequently used file list to 9.

 
2. Display the entire menu

By default Excel hides commands that you haven't used recently. The full menu appears after a second or two, but most users prefer to see the same menu every time:

 

  • a. Choose Tools, Customize, and then click on the Options tab. 
  • b. Click Always Show Full Menus, and then Click Close.

Figure 2: Set Excel to always give you the same menu every time.

 
3. Disable the Clipboard Task Pane

Excel has always had a strange relationship with the Windows clipboard. Just about every other program keeps items on the clipboard until you cut or copy something else there. Conversely, Excel tends to clear the clipboard as soon as you carry out an action that doesn't involve navigating or pasting. Excel 2003 tried to compensate for this by displaying recently copied items in the Clipboard Task Pane, shown in Figure 3. If you find this annoying, you can easily disable it:

 

  • a. Click the Options button at the bottom of the Clipboard task pane. 
  • b. Clear all of the checkboxes, and then close the Clipboard task pane. 
  • c. If you change your mind later, choose Edit, and then Office Clipboard to restore the Clipboard task pane.

Figure 3: The Clipboard task pane is intended to compensate for Excel's odd relationship with the clipboard.

 
4. Minimize the Reviewing Toolbar

Even if you turn it off, this toolbar reappears whenever you open an e-mail attachment. You could right-click on it and turn it off, but a better practice is to tuck it way so that only one icon appears, as shown in Figure 4. Simply grab the left-hand edge of the toolbar, and drag it to the right edge of your screen so that only one icon is visible.

Figure 4: Don't let the Reviewing Toolbar take up valuable screen space.

 
5. Disable the Paste/Insert Icons

You may have noticed the little clipboard icon that appears when you paste data into a worksheet. This little icon makes a Paste Options menu available, from which you can make changes to how your data pasted on the worksheet. Some folks may find this helpful, but many find it annoying. Fortunately you can easily disable the Paste Options feature:

 

  • a. Choose Tools, and then Options. 
  • b. Choose the Edit tab, and then clear the check boxes for Show Paste Options Buttons and Show Insert Options Buttons.

Figure 5: The Paste Options button can be disabled.

 
Figure 6: Clear the checkboxes shown to disable the Paste and Insert Options buttons.

 
6. Utilize the Places Bar

As shown in Figure 7, the Places Bar is the list of icons on the left-hand side of the Office dialog boxes. You can place shortcuts to frequently used folders here:

 

  • a. Click once on the folder, and then choose Tools, and then Add to My Places, as shown in Figure 7. 
  • b. You can right-click on an item in the Places bar and move it up or down one position at a time. This menu also allows you to remove items you've added to the Places Bar, but you cannot remove the default locations like My Recent Documents, My Documents, and so on.

Figure 7: The Places Bar can give you once-click access to widely-scattered folders.

 
A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

AccountingWEB Webinar: Creating error proof spreadsheets

In today's fast-paced business world, spreadsheet users don't always have the luxury of double-checking every element of their work, particularly in complex spreadsheets. Fortunately there are some simple practices you can adopt to help minimize the risk of errors in your spreadsheets. You can also learn how to track down errors in spreadsheets built by others. Take an hour tomorrow and get up to speed during our live Creating Error Proof Spreadsheets Webinar. AccountingWEB staff writer David Ringstrom, CPA, will share his best tips and secrets for improving the integrity of your spreadsheets.

Click here to read the full text of the article at AccountingWEB.com.

Excel 2007: Use these three techniques to turbo charge filtering

By David Ringstrom, CPA

Did you know Excel 2007 has a hidden command that enables you to filter a list with just one mouse click? And that there are two ways to sum just the visible rows in your filtered list? Read on to discover these time saving techniques.

Not sure how to filter? Read Excel Filtering 101.



 

Size Color Quantity
Small Blue 8,038
Medium Blue 4,245
XL Blue 5,241
XXL Blue 8,797
XXXL Blue 5,145
Small Orange 2,571
Medium Orange 5,595
XL Orange 8,825
XXL Orange 1,997
XXXL Orange 6,169
Small Green 6,192
Medium Green 3,380
XL Green 4,024
XXL Green 2,020
XXXL Green 8,295
Small Black 2,841
Medium Black 4,705
XL Black 4,202
XXL Black 4,846
XXXL Black 2,431

 

Table 1: Use this sample t-shirt sales data to explore Filtering in Excel 2007.

 

Filter versus Autofilter

As you're probably aware, the Filter command appears on both the Home and Data tabs of the Excel 2007 ribbon. However, going this route to filter a list usually means multiple mouse clicks. Instead, I recommend that you add the AutoFilter command to your Quick Access Toolbar:

 

  1. Right-click anywhere on the Excel 2007 ribbon and, then choose Customize Quick Access Toolbar.
  2. As shown in Figure 1, choose Commands Not In the Ribbon, and then add AutoFilter to your Quick Access toolbar. Close the dialog box shown in Figure 1.

 

 

Figure 1: AutoFilter is one of many secret commands that you can add to your Quick Access Toolbar. (Click on image to expand.)

New to the QAT? Learn more about the Quick Access Toolbar via the Microsoft Professional Accountant's Network.

Although it's similar in name to the Filter command, AutoFilter has a special capability: it instantly filters your list based on the contents of the currently selected cell:

 

  1. Copy and paste the sample data from Table 1 into a blank worksheet.
  2. Click on cell B14 — which contains the word green — and then click the AutoFilter button that you added to your Quick Access toolbar. As shown in Figure 2, your list should now show only green T-shirt sales.

Caveat: AutoFilter only allows you to filter for a single value, such as the word green, so you'll still need to use the filter drop-down lists for more complex filtering.

 

 

Figure 2: Use AutoFilter to create a summary with just two mouse clicks.

Important: Although AutoFilter turns on the arrows, you must still choose Sort & Filter, and then Filter to turn off the filtering arrows.

Analyze Visible Rows

Now let's look at two ways to analyze data as you filter it. First, many users overlook the fact that Excel 2007 automatically displays the total of selected cells in the status bar at the bottom of the screen, as shown in Figure 3. If you don't see this total, right-click on the status bar and enable the Sum option. Excel usually also displays these statistics, too: Average, Count, Numerical Count, Minimum, and Maximum.

 

 

Figure 3: The status bar generally gives you an instant total for the selected cells. (Click on image to expand.)

You can also accomplish this by using the SUBTOTAL function. To do so, enter this formula in cell C23:

=SUBTOTAL(9,C1:C21)

The 9 instructs SUBTOTAL that we want to sum. As shown in Figure 4, Excel automatically displays the additional options as you type the formula. SUBTOTAL is similar to SUM, but has a special capability of only tallying visible rows.

 

 

Figure 4: The SUBTOTAL function has several capabilities.

For comparison, enter this formula in cell C24:

=SUM(C1:C21)

Now, filter the list for Orange T-shirts. As shown in Figure 5, SUBTOTAL should return 25,157, while SUM always returns 99,559, regardless of how the list is filtered. SUM always tallies all rows, regardless of their hidden status. SUBTOTAL has other interesting capabilities, which I'll explore soon in a separate article.

 

 

Figure 5: SUBTOTAL tallies just the visible rows, while SUM tallies all rows.


A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

 

Excel 2003: Use These Three Techniques to Turbocharge Filtering

By David Ringstrom, CPA


Did you know that you can streamline the AutoFilter feature in Excel 2003 and earlier versions? And that there are two ways to sum only the visible rows in your filtered list? Read on to discover these time saving techniques.

Not sure how to filter? Read Excel Filtering 101.

Size Color Quantity
Small Blue 8,038
Medium Blue 4,245
XL Blue 5,241
XXL Blue 8,797
XXXL Blue 5,145
Small Orange 2,571
Medium Orange 5,595
XL Orange 8,825
XXL Orange 1,997
XXXL Orange 6,169
Small Green 6,192
Medium Green 3,380
XL Green 4,024
XXL Green 2,020
XXXL Green 8,295
Small Black 2,841
Medium Black 4,705
XL Black 4,202
XXL Black 4,846
XXXL Black 2,431

Table 1: Use this sample t-shirt sales data to explore Filtering in Excel 2003.

AutoFilter's Hidden Powers

Most users choose Data, Filter, and then AutoFilter to enable filtering in Excel. However, you can give yourself one-click access to the AutoFilter command:

 

  1. Choose Tools, Customize, and then click on the Commands tab.
  2. Choose Data from the Categories list, and then drag AutoFilter onto one of your toolbars, or to the right of the Help command.
  3. Drag the Show All command and place it to the right of the AutoFilter icon that you just dragged.
  4. Click Close to dismiss the Customize dialog box.


Figure 1: AutoFilter takes on special powers when you add it to a toolbar or your Excel menu.

You can now instantly filter a list based on the contents of the currently selected cell:

 

  1. Copy and paste the sample data from Table 1 into a blank worksheet, starting in Cell A1.
  2. Click on cell B14 — which contains the word green — and then click the AutoFilter button that you added above. As shown in Figure 2, your list should now show only green T-shirt sales.
  3. You can click the Show All command to keep the filtering arrows in place, but display all rows.

Caveat: AutoFilter only allows you to filter for a single value, such as the word green, so you'll still need to use the filter drop-down lists for more complex filtering. However, you can filter on one column at a time, such as green in column B, and then Medium in column A.


Figure 2: Use AutoFilter to create a summary with just two mouse clicks.

Important: Although the toolbar-based AutoFilter button turns on the arrows, you must still choose Data, Filter, and then AutoFilter to turn off the arrows.

Analyze Visible Rows

Now let's look at two ways to analyze data as you filter it. First, many users overlook the fact that Excel 2003 automatically displays the total of selected cells in the status bar at the bottom of the screen, as shown in Figure 3. If you don't see this total, right-click on the status bar and enable the Sum option. Alternatively you can choose to display another statistic instead: Average, Count, Numerical Count, Minimum, and Maximum.


Figure 3: The status bar generally gives you an instant total for the selected cells.

You can also accomplish this by using the SUBTOTAL function. To do so, enter this formula in cell C23:

=SUBTOTAL(9,C1:C21)

The 9 instructs SUBTOTAL that we want to sum. Consult Excel's online help file for the other numeric codes, although I will mention that 1 will give you an average, while 4 gives you the maximum, or largest value. SUBTOTAL is similar to SUM, but has a special capability of only tallying visible rows.

For comparison, enter this formula in cell C24:

=SUM(C1:C21)


Figure 4: The filtered list.

Now, filter the list for Orange T-shirts. As shown in Figure 4, SUBTOTAL should return 25,157, while SUM always returns 99,559, regardless of how the list is filtered. SUM always tallies all rows, regardless of their hidden status. SUBTOTAL has other interesting capabilities, which I'll explore soon in a separate article.

 
A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

 

QuickBooks 2009 Splashes on the Scene with Plenty of New Features

By David Ringstrom, CPA


QuickBooks 2009 is now available, and includes a number of long-wished for features:

 

  • Instead of being limited to 8 figure numbers (basically 99 million), you're now limited to 11 figure numbers (basically 99 billion).
  • QuickBooks 2009 supports multiple currencies, and offers an international payment service to allow you to initiate drafts and wires from within the program. Updated currency rates can be imported upon demand. You assign a currency to each customer or vendor, and then transactions automatically show both the foreign and domestic totals.
  • At long last you can sort the columns within the bank reconciliation window. Even better, your clients can now reconcile their bank account while the accountant's copy is out.
  • One user can back-up QuickBooks while other users are logged in — single-user mode is no longer required for this activity.
  • The new Messenger feature allows one user to instantly communicate with other users currently logged into the software.
  • The accountant's copy now allows you to map 1099 fields, as well as modify and merge classes, plus QuickBooks Accountant Edition can import and export Accountant's Copy files from both QuickBooks 2008 and 2009.
  • A new Company Snapshot serves as an executive dashboard.
  • Online banking has been improved so that more downloaded transactions should match automatically to their manually entered counterparts.
  • The Item List window has a new search feature simplifies finding inventory items.
  • Converting from Peachtree 2008 and Microsoft Office Accounting 2008 to QuickBooks is easy via the free QuickBooks Conversion Tool available at www.quickbooks.com/converttoquickbooks.
  • Adobe Acrobat Form templates can be imported into QuickBooks. This requires Adobe Acrobat Pro, but allows for accounting tasks, such as estimates, invoices, and purchase orders, to be completed in the field.
  • The Administrator password now uses stronger, 2048-bit encryption.
  • A new Live Community feature allows users to see questions and answers posted by other QuickBooks users.
  • QuickBooks 2009 users can have a free business web site — comprised of three pages — hosted for twelve months.
  • QuickBooks Enterprise Edition now supports up to 30 users.
  • QuickBooks 2009 is Windows Vista-certified by Microsoft.As of June 1, 2009, Intuit will no longer support QuickBooks 2006, meaning technical assistance and services such as online banking, payroll, credit card processing, and so on for that version will all cease.


A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

 

Excel Chart Advisor Prototype Simplifies Graphs

By David Ringstrom


Excel 2007 users have a new tool in their arsenal: the Chart Advisor from the Microsoft Office Labs — a new proving ground for potential new Office features. Although this tool is a prototype, you may find it helpful in your daily work. This add-in for Excel utilizes an advanced rules engine to analyze your data, and then rank chart suggestions based on their respective score. Remember, this tool only works with U.S. English version of Excel 2007.

Free trial available: You can download a free 60 day trial of Office 2007.

Before you rush off and download the Chart Advisor, do note the caveats listed in Figure 1. You cannot install Chart Advisor without agreeing to these conditions. Also, some early versions of the Chart Advisor caused Excel to hang or crash, but those problems were quickly rectified by the Office Labs team. The tool worked without issue during my testing.


Figure 1: Read the terms of the Chart Advisor closely before proceeding with the installation.

Once you've downloaded the tool and accepted the various conditions and agreements, you'll notice a new Office Labs section within the Insert tab of the ribbon, as shown in Figure 2. The next step is to put some sample data in a blank worksheet, such as Table 1.







Figure 2: A new Chart Advisor icon will appear in the Insert tab of the Excel 2007 ribbon.
 

 

Chain Product Cases Sold Total Sales
Fruit R Us Oranges 6,168 61,680
Fruit R Us Apples 6,079 85,106
Fruit R Us Kiwi 6,058 66,638
Fruit R Us Bananas 6,868 75,548
Fruit R Us Mixed Berries 1,996 29,940
Bob's
Fruit
Oranges 7,818 93,816
Bob's Fruit Apples 1,565 21,910
Bob's Fruit Kiwi 9,967 99,670
Bob's Fruit Bananas 9,842 98,420
Bob's Fruit Mixed Berries 8,993 89,930

 


Table 1: You can copy and paste this sample data into a blank worksheet for use with the Chart Advisor.

 

Once you've done so, click on a single cell within your table of sample data, and then click the Chart Advisor icon shown in Figure 2. The prompt shown in Figure 3 will appear briefly, and then you'll see the chart advisor itself in Figure 4. As you can see, each chart is ranked based on a score that appears in the lower right-hand corner. Hover over each chart to see a larger version.


Figure 3: The Chart Advisor is a proof of concept tool that may later become a formal part of Excel.


Figure 4: The Chart Advisor makes up to five recommendations based upon your data.

Once you select a chart, you can use the Modify section of the Chart Advisor to add or remove fields. As you can see in Figure 5, the tool informs you if you inadvertently remove a critical field from your chart. You can also use the Filter section, shown in Figure 6, to limit the data shown in your chart. Click the Insert Chart button when you're happy with your chart.

 

Expert tip: Right-click on the chart and choose Move Chart to relocate the chart to another worksheet, or its own chart sheet.


Figure 5: The Chart Advisor helps you head off potential mistakes.


Figure 6: You can limit the data shown in your chart without having to copy a subset to a new location.




A previous version of this article first appeared on www.accountingweb.com .

About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link

 

Tips for improving your Microsoft Office Accounting experience

Are you new to Office Accounting? Have you got questions about how the program works or how you can improve your use of the program? This collection of Tips for improving your Microsoft Office Experience is one place where you can look for answers. Continue reading the full article on

Click here to read the full text of the article at AccountingWEB.com.