Category: Excel

Excel Tip: Determining the Remaining Length of a Loan Using NPER

Excel Tip: Use Conditional Formatting to Identify Unlocked Cells

by David Ringstrom, CPA

 

In a previous article, I described a technique that lets you tell at a glance if an individual cell is unlocked within a worksheet. In this article, I'll demonstrate how you can use conditional formatting to identify all unlocked cells within a worksheet range. This can serve both as an input aid, so that users know which cells they can affect, as well as an auditing tool, so that you know which cells are protected and which aren't.

 

We'll use the CELL function within Conditional Formatting to color unlocked cells. The CELL function provides information about worksheet cells, and takes this form:

 

=CELL(info_type,[reference])
The info_type argument signifies the type of information that we'd like to return about the cell. A couple of available info types include:
  • protect – returns 0 if a cell is unlocked, or 1 if it is locked.
  • filename – returns the workbook's file name.
  • type – returns b if a cell is blank, l (lowercase L) if the cell contains text, or v if the cell contains anything else.
Details about other info types are available in a Microsoft support article. The reference argument is an optional argument that allows us to specify a range of one or more cells. To use the CELL function in conjunction with conditional formatting:
Set up the example shown in Figure 1. To save time, type Monday in cell A2 and then drag the fill handle down to create a series of days. After you enter 100 in cell B2 and 200 in cell B3, select those two cells and double-click the Fill Handle to complete the series. In cell B9, hold down the Alt key then press and release the equal sign (=) to create an instant sum.
Figure 1: Set up the example by using time-saving shortcuts.

 

We now want to select just the numeric input cells, as shown in Figure 2. To do so, press Ctrl-G (or F5) to display the Go To dialog box and then click the Special button. Choose Constants, clear the checkmarks for Text, Logicals, and Errors, and then click OK.

 

At this point, cells B2 through B8 should be selected. Press Ctrl-1 to display the Format Cells dialog box, clear the Locked checkbox on the Protection tab, and then click OK.
Figure 2: Use the Go To Special dialog box to select the numeric constants in the example. Then, use the Format Cells dialog box to unlock the selected cells.

 

We now want to select the used range of our spreadsheet, as illustrated in Figure 3. To do so, click on cell A1, and then hold down the Shift key while you tap the End and then Home keys.
Access the Conditional Formatting feature:
  • Excel 2007 and later: Choose Conditional Formatting on the Home tab, New Rule, and then Use a Formula to Determine Which Cells to Format
  • Excel 2003 and earlier: Choose Format, Conditional Formatting, and then change Cell Value Is to Formula Value is.
Enter the following formula:
=CELL(“protect”,A1)=0
Click the Format button, and choose a color from the Fill tab, and then click OK twice.
Figure 3: Use the CELL function to color the unlocked cells.

 

As illustrated in Figure 4, cells B2 through B8 should appear in the color that you chose. To test the feature, select cells B2 through B4, press Ctrl-1 to display the Format Cells dialog box, click Locked, and then click OK. The color should vanish from cells B2 through B4.
Figure 4: Check the Locked checkbox in the Format Cells dialog box to test your work.

 

You can easily remove Conditional Formatting at any time:
  • Excel 2007 and later: Choose Conditional Formatting from the Home tab, click Clear Rules, and then Clear Rules from Entire Sheet. If you want to be more selective, select a range of cells before you access the Clear Rules command and then choose Clear Rules from Selected Cells.
  • Excel 2003 and earlier: Select the range of cells with conditional formatting, choose Format, Conditional Formatting, and then click Delete. Select Condition 1 and then click OK twice.
Figure 5: Choose Clear Rules from Entire Sheet to remove the Conditional Formatting from your worksheet.
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

Resetting the Last Cell in an Excel Worksheet

By David Ringstrom, CPA

It's frustrating when Excel acts as if the active area of a worksheet is significantly larger than the actual area where you have data. Suddenly your scroll bars move you into uncharted areas, such as column TX or row 5,000. In programming parlance, this is known as the “used range” of a worksheet. Fortunately, there's a quick and simple way to reset the used range when necessary.



 

As with many aspects in Excel, the used range is fraught with nuance, but you can easily set up an example to understand the concept. As illustrated in Figure 1, carry out these steps in a blank worksheet:
  • Type the number 100 in cell A10.
  • Press Ctrl-Home to move your cursor back to cell A1.
  • Press End-Home to move to the last used cell in your worksheet. Your cursor should now return to cell A10.
  • Press Ctrl-G (or the F5 key) to launch the Go To dialog box, enter the address TX5000, and then press Enter.
  • Type the number 100 in cell TX5000 and then press Enter.
  • Move your cursor up one cell and then press the Delete key to clear cell TX5000.
  • Press Ctrl-Home to move your cursor back to cell A1.
  • Press End-Home to move to the last used cell in your worksheet. Your cursor will now return to cell TX5000, even though you erased it.
Figure 1: This example illustrates the concept of the “used range” of a workbook.



 

No amount of deleting rows or columns will reset this used range of your worksheet, but you can use a one-line macro to resolve the issue:
  • Press Alt-F11 to launch the Visual Basic Editor.
  • Choose View, and then Immediate to display the Immediate window. This task pane allows you to carry out an ad hoc programming task without actually creating a formal macro.
  • Type ActiveSheet.UsedRange in the Immediate Window and then press Enter. You won't get any visual indication that anything has happened, but the active area of your worksheet will be reset.
  • Choose File and then Close and Return to Microsoft Excel to close the Visual Basic Editor. There's no need to erase the Immediate Window – it will clear itself when you close Excel.
  • In your worksheet, press Ctrl-Home to return to cell A1 and then press End-Home to move to the last used cell. Your cursor should now return to cell A10.
Figure 2: Use the Visual Basic Editor to reset the used range of your worksheet.



 

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

Determining Worksheet Cell Protection at a Glance

By David Ringstrom, CPA
 

It can be frustrating when you're working in an unfamiliar worksheet and “the cell or chart you are trying to change is protected” prompt appears on-screen. By default, you can't easily tell at a glance if a cell is locked or not, but in this article, I'll show you how to add a visual aid. As an added bonus, this technique also makes it far easier for you to lock and unlock worksheet cells.

 

Before we get to the visual aid, let's first take a look at worksheet cell protection, as shown in Figure 1. By default, every cell on a worksheet is set to locked, which means if you then protect the worksheet itself, no cells can be changed. Thus, you must first unlock any cells that you wish for someone to be able to change. To do so:
  • Select one or more cells you wish to unprotect.
  • Press Ctrl-1 to display the Format Cells dialog box.
  • Clear the check mark next to Locked on the Protection tab and then click OK.


Figure 1: The Format Cells dialog box allows you to lock and unlock cells.



 

Once you've adjusted the protection level for individual worksheet cells, you can now protect the worksheet:
  • Excel 2007 and later: On the Review tab, click Protect Sheet, optionally enter a password, and then click OK.
  • Excel 2003 and earlier:  Choose Tools, Protection, and then Protect Sheet. Optionally enter a password, and then click OK.
As shown in Figure 2, to unprotect a sheet, carry out the same steps as above, but this time the Protect Sheet command will read Unprotect Sheet, and then enter the password if necessary.
Figure 2: Cell protection doesn't take effect until you protect a worksheet.



 

With that background out of the way, let's get to the trick. In any version of Excel, we can add an icon that will let us determine if a cell is locked and also enable us to toggle the locked setting on or off. In Excel 2007 we'll use the Quick Access Toolbar, which typically resides on the top of your Excel screen:
  • As shown in Figure 3, click the arrow at the end of the toolbar, choose More Commands, and then select the Home tab from the drop-down menu.
  • Scroll the list until you find Lock Cell, and then double-click on that command to add it to your Quick Access toolbar.
  • Click OK to close the Excel Options dialog box.
Figure 3: Excel 2007 and later allows you to add a Lock Cell shortcut to the Quick Access Toolbar.



 

As shown in Figure 4, if a cell is locked, the Lock Cell icon will have a colored background, while unlocked cells will have a gray background. Every icon on the Quick Access Toolbar is assigned a numeric shortcut, so you can now lock or unlock cells without using your mouse. To do so, select one or more cells, press the Alt key, and then the number that corresponds with the Lock Cells icon.
Figure 4: Locked cells will have a colored background; unlocked cells have no background.



 

In Excel 2003, choose Tools, Customize, Commands, and then click on the Format category. Scroll down the list on the right until you find Lock Cell, and then drag the command onto a toolbar or into the gray space to the right of the Help command. If a cell is locked, the icon will have a blue square around it or just a gray background if a cell is unlocked. You'll have to use your mouse to toggle the icon on or off, but this does give you an easier way to lock or unlock cells without going through the Format Cells dialog box.



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

Creating a Series of Letters in Excel

 

By David Ringstrom, CPA

By its very nature as a spreadsheet, it's easy to create a series of numbers in Excel. For instance, you can enter the number 1 in cell A1, hold down the Ctrl key, and drag the fill handle in cell A1 down to create an instant series of numbers. For the uninitiated, the Fill Handle is the little black notch in the right-hand corner of the active worksheet cell. Regardless, most users don't realize that you can configure Excel to create a series of letters in a similar fashion.


First we need to create the list itself. If you like, you can type the letters A through Z down a column or across a row. But, there's an easier way:


1. In a blank worksheet, press F5 to display the Go To dialog box.
2. Enter A1:A26 in the Reference field, and then click OK.
3. Type the following formula in cell A1, and then press Ctrl-Enter:
=CHAR(64+ROW())
Important: Make sure that you press Ctrl-Enter; otherwise, you'll have to manually drag or copy and paste the formula in to cells A2 through A26.


4. Press Ctrl-C to copy cells A1 through A26 to the clipboard.
5. Right-click on cell A1, choose Paste Special, and then double-click Values.
6. Access the Custom Lists feature:
  • Excel 2003: Choose Tools, Options, and then click the Custom Lists tab.
  • Excel 2007: Click the Office button, choose Excel Options, and then click the Edit Custom Lists button.
  • Excel 2010 and later: Choose File, Options, and then Advanced. The Edit Custom Lists button appears at the bottom of the Advanced options.
  • Excel: Mac 2011: Choose Excel, Preferences, and then Custom Lists.
7. Click the Import button within the Custom Lists window to add your list to Excel.
8. Click OK as needed to close any open dialog boxes.


Now that you've completed this one-time series of steps, you're ready to test your work. Type the letter A in any worksheet cell, and then drag the fill handle down or across to complete the series. You can use this technique with any Excel workbook on your computer. Custom Lists are specific to each computer, so you'll need to repeat the above steps on any other computers that you use.


There are a few other nuances you should be aware of:
  • At the start of this article, I instructed you to hold down the Ctrl key while you dragged to create a series of numbers. Don't hold down the Ctrl key while accessing a custom list, because you'll end up copying the initial letter instead of creating a series.
  • If you use the CHAR() function, make sure to convert the formula to values as we did in steps 4 and 5. The Custom Lists feature can only import actual text and not text shown by way of a formula.
  • Windows and Macintosh computers have a character set of 255 characters. An uppercase A is character number 65, while lowercase letters start at 97. We accessed the numeric equivalents by using the ROW() function to add the current row number to 64. For lowercase letters, you'd replace 64 with 96. If you wish to see the entire character set, carry out these steps:
  1. In a blank worksheet, press F5 to display the Go To dialog box.
  2. Enter A1:A255 in the Reference field, and then click OK.
  3. Type the following formula in cell A1, and then press Ctrl-Enter: =CHAR(ROW())
Some cells will appear blank or show a question mark. These represent non-printable characters, such as tabs and carriage returns.
  • Ctrl-Enter is a keyboard shortcut for filling multiple cells at once. By using the Go To dialog box, you're able to preselect the desired cells, and then type the formula once. If you press Enter, instead of Ctrl-Enter, only the first cell will get filled.
  • You can store any type of text-based list you use repeatedly. If you'd like to store a series of numbers as a custom list, you'll need to add an apostrophe before each number to convert the numbers to text. The Custom Lists feature doesn't allow you to embed numeric values.
  • In step 5 above, I instructed you to double-click on Values. You can use this trick in many of Excel's dialog boxes to skip the OK button. Double-clicking an option within a dialog box signifies that you've made your final selection and wish to close the dialog box.

 

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

Microsoft Excel: Inserting Rows and Columns with Ease

By David Ringstrom, CPA

Spreadsheet users frequently insert rows and columns into spreadsheets, but for most users, it's a multiple-step process. Even savvy users who rely on keyboard shortcuts have to use two different shortcuts in sequence. In this article, I'll explain how you can create your own custom shortcut to insert rows or columns with a single keyboard shortcut or mouse click.


There are several ways to add rows or columns to a spreadsheet:
  • Excel 2007 and later: As shown in Figure 1, on the Home tab, choose Insert and then Insert Sheet Rows or Insert Sheet Columns.
Figure 1: Many users rely on the Insert Sheet Rows command to insert new rows in a spreadsheet.


  • Excel 2003 and earlier: Choose Insert and then Sheet Rows or Sheet Columns
  • In any version of Excel, you can also insert rows and columns by way of the worksheet frame. As shown in Figure 2, select one or more rows or columns, right-click on the selection, then choose Insert.


Figure 2: You can right-click on the worksheet frame and choose Insert to add new rows or columns.


  • Some users rely on a keystroke approach to insert rows:
1. Press Shift-space bar to select a single row. You can optionally then hold down the Shift key and use the arrow keys to select additional rows.
2. Press Ctrl -+ to insert rows. In this context, make sure you're using the + sign on your number pad; otherwise, you'll want to press Ctrl-Shift-+ if you're accessing the plus sign that is somewhat adjacent to the letter P on your keyboard. 
You can use this same approach with columns that you select by way of the Ctrl-space bar keyboard shortcut. Ctrl+ also works for rows   or columns that you select with your mouse.
  • You can also press Ctrl-+ or Ctrl-Shift-+ without preselecting any rows or columns. When you do so, Excel will display the prompt shown in Figure 3. Press R to select Rows or C to select Columns, and then press Enter.
Figure 3: Press Ctrl-+ on your number pad or Ctrl-Shift-+ on your keyboard within a worksheet cell to display the Insert dialog box.


Another commonly used technique involves using Excel's macro recorder to record a macro that you place in your personal macro workbook and then assign a keyboard shortcut or icon. While this can be effective in Excel 2003 and earlier, actions carried out by way of a macro can't be undone; plus, users sometimes assign keyboard shortcuts to macros that supersede built-in shortcuts. For instance, one might think that Ctrl-I would be a great keyboard shortcut for inserting rows, but that means other users of that spreadsheet could no longer press Ctrl-I to italicize text.


Regardless, it's easy to streamline this task in Excel 2007 and later:
  • As shown in Figure 4, choose Insert on the Home tab, right-click on Insert Sheet Rows, and then choose Add to Quick Access Toolbar. 


Figure 4: Add Insert Sheet Rows to your Quick Access Toolbar to streamline this repetitive task.


If you haven't previously modified your Quick Access Toolbar, Insert Sheet Rows will become the fourth icon on the toolbar, which means its keyboard shortcut will be Alt-4. Going forward, you can now press Alt-4 at any time to insert a new row into your spreadsheet. If you need to insert multiple rows, you have several options:
  • Press Alt-4 as many times as needed.
  • Press Alt-4 once to insert the initial row, and then press either F4 or Ctrl-Y to repeat this action.
  • Hold down the Shift key and then use the Down arrow key to select multiple cells. Press Alt-4 to insert multiple rows at once.
  • Select one or more rows by way of the worksheet frame with your mouse, and then press Alt-4, or click the Insert Sheet Rows icon on the Quick Access Toolbar with your mouse.
As you've probably surmised, you can add the Insert Sheet Columns command to your Quick Access Toolbar as well to simplify adding columns.
As always in Excel, there are some nuances that you should be aware of:
  • Make sure to use the 4 key that's along the top of your keyboard. If you press Alt and the 4 on your number pad, you'll insert a diamond symbol into your worksheet.
  • If your fingers slip and you press Alt-F4, you'll likely encounter the prompt shown in Figure 5. Alt-F4 is a Windows keyboard shortcut for closing the active program.

Figure 5: If you press Alt-F4 instead of Alt-4, you'll be instructing Excel to close.


  • You can reassign the numeric shortcut for Quick Access icons by changing the order of the commands. To do so, as shown in Figure 6, click the arrow at the right of the Quick Access Toolbar and then choose More Commands. Select a command from the list on the right, and then use the arrow keys to reposition the command on your Quick Access Toolbar. For instance, if you made Insert Rows be the first icon on your toolbar, its keyboard shortcut would become Alt-1.
Figure 6: Reassign keyboard shortcut numbers by reorganizing your Quick Access Toolbar.


  • To remove an icon from your Quick Access Toolbar, right-click on it and choose Remove from Quick Access Toolbar.




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

Recovering Unsaved Excel Workbooks

By David Ringstrom, CPA

It's every spreadsheet user's worst nightmare – you've worked on a workbook for a period of time, and then accidentally close it without saving. Or the power goes out, or Excel crashes . . . the list of spreadsheet hazards goes on and on.


If you're using Excel 2010 or later, there's a pretty good chance you can mitigate much – but not all – of the risk related to unsaved workbooks. Regardless, in any version of Excel, you can raise the odds of having a recovered copy of your work available after a software crash.


As shown in Figure 1, Excel has long had an AutoRecover feature that's designed to help you recover unsaved workbooks should Excel crash. If any version of Excel crashes, you sometimes get the opportunity to recover at least some of your work from the Document Recovery pane in Figure 1. However, this feature is limited to spreadsheets that were open in Excel at the time the program or your computer crashed.
Figure 1: The Document Recovery pane appears automatically when warranted in all versions of Excel.


Although somewhat helpful, the Document Recovery feature doesn't protect workbooks you inadvertently closed without saving by clicking “no” on that eponymous “Do you want to save the changes you made?” prompt. However, in Excel 2010 and later, you have the ability to not only recover unsaved files, but sometimes recover a version of a file from a few minutes earlier. This is helpful when you make a blunder that you can't undo, or when you want to see how a workbook looked a few minutes earlier. To do so, choose File, Info, and then look for the Manage Versions button, as shown in Figure 2. If previous versions are shown, you can open these alongside the most current version of your workbook and copy and paste data between the workbooks as needed.


Figure 2: Excel 2010 and later offers the ability to access file versions that were inaccessible in Excel 2007 and earlier.


In any version of Excel, you should carry out the following steps to increase the odds of being able to recover unsaved work:
  • Excel 2010 and later: As shown in Figure 3, choose File, Options, Save, and then change the Save AutoRecover Information setting to every two minutes, down from the default of every ten minutes.
  • Excel 2007: Click the Office button, choose Excel Options, Save, and then change the Save AutoRecover Information setting to every two minutes, down from the default of every ten minutes.
  • Excel 2003 and earlier: Choose Tools, Options, click the Save tab, and then change the Save AutoRecover Information setting to every two minutes, down from the default of every ten minutes.
Bear in mind that Excel won't necessarily save your work every two minutes, as illustrated with the difference in times shown in Figure 2. Although I haven't studied it at length, my casual observation is that Excel saves temporary versions on a somewhat random basis, but regardless, lowering the setting to every two minutes increases the potential frequency for new versions to be saved.


Figure 3: In any version of Excel you should change the AutoRecover frequency to every two minutes.


In any version of Excel, documents presented in the Document Recovery pane are typically deleted when you close the pane and answer “yes” to the prompt that you no longer need access to those files. In Excel 2010 and later, my casual observation is that unsaved versions appear to linger for three or four days and then are swept away.


In a Hail Mary situation, such as if the Document Recovery pane doesn't appear, or you're working in Excel 2007 and earlier and want to try to access a version from a few minutes earlier, it's worth a shot to use Windows Explorer to navigate to the folder listed in the AutoRecover File location. You might just get lucky and find an accessible version of your document.


Although these features offer a modicum of safety against crashes, your best defense is to save frequently and to create multiple versions of your documents. Personally, I incorporate version numbers, such as 1.01, 1.02, and so on into some of my spreadsheet file names so I can go back in time when necessary.



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

How to Disable Worksheet Animation in Excel 2013

By David Ringstrom, CPA


Excel 2013 has arrived, and for the most part, it's much like Excel 2007 and 2010, but with some spiffy new features, such as Recommended Charts and Pivot Tables, Flash Fill, Quick Analysis, Power View, and more. I'll be exploring these features in upcoming articles, but first I want to show how you can disable the eye candy if you choose.


By comparison, worksheets in Excel 2010 and earlier were rather staid. You pressed Enter, and the cursor dropped to the next row with aplomb. In Excel 2013, the cursor wants to make sure that you realize it's moving to the next row, so it swoops its way there. When you change a formula, numbers flip like in a slot machine before settling into place. Click a few cells to the right, and the cursor visually whooshes into position.


These features are probably helpful to a new generation that's never experienced a spreadsheet before, but it borders on seizure inducing for long-time spreadsheet users. Fortunately, there's a simple fix to tone down the animation in Excel 2013. As shown in Figure 1, click on File, choose Options, and then Advanced. Scroll down to the Display section and then enable the Disable Hardware Graphics Acceleration setting. Think of this as the “turn off the bells and whistles, please” option. Click OK and settle into working in peace with your spreadsheets again.


Figure 1: Enable the Disable Hardware Graphics Acceleration option to turn off worksheet animation.


For good measure, once you change this setting in Excel 2013, as shown in Figure 2, choose File and then Account. On this screen you can change your Office Theme to Dark Gray to add some contrast to the default, stark-white user interface. This screen is also where you can sign out of Excel if you sign in via the Sign In link in the top right-hand corner or through the Office Apps store.


[2]
Figure 2: Set the Office Theme to Dark Gray if you find the Excel 2013's default theme to be too stark.



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

Restoring Classic Print Preview in Excel 2010/2013

By David Ringstrom, CPA


Long-term Excel users will recall that Excel has historically provided a separate Print Preview window that made it easy to zoom in and adjust settings. Excel 2010 introduced a new “Backstage View” where print preview became embedded into the File menu. Excel 2013 continues this new tradition, but you can get your “old-school” print preview functionality back with a few quick steps.



To accomplish this, we're going to add an icon to Excel's Quick Access Toolbar (QAT). As shown in Figure 1, the QAT typically resides at the top of your Excel screen and serves as a custom toolbar of your making. To begin:
  • Right-click on the QAT and then choose Customize Quick Access Toolbar.
  • When the Excel Options dialog box appears, choose Commands Not in the Ribbon.
  • Scroll down the list until you find Print Preview Full Screen. Alternatively, you can click once on one of the first commands on the list, and then press P to jump down the list close to where Print Preview Full Screen resides.
  • Double-click on Print Preview Full Screen to add it to your Quick Access Toolbar. You can also click once on it and choose Add, but double-clicking saves a step. Note that it's important to choose Print Preview Full Screen as opposed to Print Preview and Print. The latter simply provides easier access to the Backstage View on the File menu.
  • Optionally, move Print Preview Full Screen further up the list. Each icon on the QAT is given a numeric keyboard shortcut. As shown in Figure 1, Print Preview Full Screen is the fourth icon on the list. This means on my computer I can press Alt-4 to display the classic Print Preview screen.
  • Click OK once you're satisfied with the positioning of the new icon on your QAT.



Figure 1: Adding a hidden command restores classic Print Preview in Excel 2010 and later.


You can now either click on the icon or press Alt-4 (or the corresponding number based on its position). If you forget the numeric shortcut, simply press the Alt key once in Excel to reveal the shortcuts. As shown in Figure 2, Excel 2007 users have an easier time:
  • Click the arrow at the end of the QAT.
  • Choose Print Preview.
Figure 2: Excel 2007 users can add Print Preview to the QAT with two mouse clicks.



If at some point in the future you want to remove Print Preview or other icons from your QAT in Excel 2007 or later, simply right-click on the icon within the QAT and choose Remove From Quick Access Toolbar.



Before I close, I do have a tip for Word 2010 and 2013 users. You can restore classic print preview functionality there as well, but the steps are just slightly different:
  • Right-click on the QAT and choose Customize Quick Access Toolbar.
  • Choose All Commands (instead of Commands Not in the Ribbon as you did in Excel).
  • Double-click Print Preview Edit Mode (instead of Print Preview Full Screen). You may see a Print Preview Full Screen command, but in Word that command doesn't work in the same fashion as Excel.




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

Part 1 on Identifying Duplicate Values in an Excel List

By David H. Ringstrom, CPA

 

From time to time, you may need to identify duplicate values with a list in Excel. Modern versions, including Excel 2007 and later, provide this capability with just a few mouse clicks. For good measure, I'll also describe a worksheet function-based approach that works in any version of Excel.
 
As shown in Figure 1, you can easily identify duplicate invoice numbers in a list. To do so:
  • Select the range of cells you wish to test. One way to do so is to click on a single cell and then press Ctrl-A.
  • On Excel's Home tab, choose Conditional Formatting, Highlight Cells Rules, and then Duplicate Values.
  • Click OK within the Duplicate Values dialog box to identify the duplicate values.
  • Duplicate values in the list will now be identified.
 Continue reading 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 Spreadsheet-Based Form 1040 Available for 2012 Tax Year

By David H. Ringstrom, CPA

Fresh from our pull-back from the fiscal cliff, it's not just the IRS that's been madly updating forms for the 2012 filing season. Right on schedule, Glenn Reeves of Burlington, Kansas has released his sixteenth spreadsheet-based version of the US Individual Tax Return, commonly known as Form 1040. Since 1997 Mr. Reeves has pursued this “labor of love,” which means he allows any taxpayer to download and use the spreadsheet for free.


Mr. Reeves' spreadsheet empowers anyone to prepare their tax return spreadsheet applications, such as Microsoft Excel, Office 365, Libre Office (formerly known as Open Office), or Gnumeric Portable – a spreadsheet that fits on a flash drive. As you can see in Figure 1, the form closely mirrors the official IRS format, but Mr. Reeves doesn't guarantee that the IRS will accept printed versions of this form. He personally files his return online after using the spreadsheet to compute his return.
Figure 1: Glenn Reeves has updated his Excel-based version of IRS Form 1040 for the 2012 tax year


The 2012 version of the spreadsheet includes both pages of Form 1040 as well as these supplemental schedules:
  • Schedule A – Itemized Deductions
  • Schedule B –Interest and Ordinary Dividends
  • Schedule C – Profit or Loss from Business
  • Schedule D – Capital Gains and Losses, along with its worksheet
  • Schedule E – Supplemental Income and Loss
  • Schedule L – Standard Deduction for Certain Filers
  • Schedule SE – Self-Employment Tax
  • Form 6251 – Alternative Minimum Tax – Individuals
  • Form 8949 – Sales and Dispositions of Capital Assets
The spreadsheet also includes several worksheets:
  • Schedule D Worksheet
  • 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 44 – Qualified Dividends and Capital Gain Tax Worksheet
  • Line 51 – Child Tax Credit Worksheet
Five additional worksheets round out the tool:
  • W-2 input forms that support up to four employers for each spouse
  • 1099-R Retirement input forms for up to four payers for each spouse
  • SSA-1099 input form to record Social Security Benefits
  • A tax table
  • Change log that records revisions to the spreadsheet
All of the worksheets in the 1040 workbook 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. Mr. Reeves notes that the spreadsheet is constructed in such a fashion that proper use of the spreadsheet means a user won't need to access any of the protected cells. As shown in Figure 2, the spreadsheet also offers some limited error checking.
Figure 2: The spreadsheet offers limited error-checking prompts.


Although Mr. Reeves gives the spreadsheet away, he does accept appreciation contributions, which he reports on his tax return as income. He also donates 10 percent of any contributions to his church. Spreadsheet-based versions of Form 1040 are available for tax years 1996 through 2012 at www.excel1040.com. Questions regarding the spreadsheet should be directed to Glenn Reeves through his website.



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

Three Ways to Fill Blank Cells within Excel Spreadsheets

By David H. Ringstrom, CPA

Periodically, you may have a need to fill in gaps within an Excel spreadsheet. One way to do so is to manually fill in each cell, but in this article, I'll show you three alternatives. First I'll use a formula, then Excel's Find and Replace function, and finally the often-overlooked Go To Special feature.


Let's say you have a spreadsheet that looks like Figure 1. You'd like to replace the blank cells in cells B3, B6, and B7 with the words No Response. To do so, you could add this formula in cell C2, and then copy it down through cell C7:
=IF(B2=””,”No Response”,B2)
In this case, the two double quotes determine if the cell is blank. If so, the IF statement returns the words No Response; otherwise, it returns the present contents of cell B2. Next, select cells C2 through C7 and press Ctrl-C. Right-click on cell B2 and then choose Paste Special. Double-click on Values to replace the original values. At this point, you can erase cells C2 through C7.


Figure 1: You can use an IF statement to populate blank cells.



A longer version of this formula would take this form:
=IF(ISBLANK(B2),”No Response”,B2)
As you can see, ISBLANK returns TRUE if a cell is blank, or FALSE if it isn't.
Of course, in this case you don't necessarily need to use a formula. As shown in Figure 2, you can select cells B2 through B7, and then press Ctrl-H to display the Replace dialog box. Leave the Find What field blank and enter the words No Response in the Replace With field, and then click Replace All. This will automatically fill in the blank cells with the word No Response.


Figure 2: Find and Replace allows you to fill in blank cells.



A third way you can fill in these blank cells is to click once on cell A1, and then press Ctrl-A to select the list. Press Ctrl-G to display the Go To dialog box, and then click the Special button. Double-click on Blanks, which will result in just the blank cells being selected. Type the words No Response, and then press Ctrl-Enter. Doing so will put the words No Response in all of the selected cells at once, as shown in Figure 3.



Figure 3: The Go To Special command allows you to select Blanks, while Ctrl-Enter fills multiple cells.



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

Using Collections in Excel to populate UserForm Controls

The following is programming code we frequently use to populate a drop-down list or listbox on an Excel UserForm with a unique list of items from a spreadsheet.

Private Sub UserForm1_Intialize()

    'Creates a new collection
    Dim myList As New Collection

    'Determines number of rows to loop through
    numRows = Range("A1").CurrentRegion.Rows.Count

    'Optional - erases existing dropdown list from control
    Me.lstDropdown.Clear

    'Loops through each row and adds to collection
    For i = 2 To numRows

        'An item can only be added to a collection once, hence the on error
        On Error Resume Next
        myList.Add Cells(i, "A"), Cells(i, "A")
        On Error GoTo 0

    Next

    'Populates control with items from the collecton
    For i = 1 To myList.Count

        Me.lstDropdown.AddItem myList.Item(i)

    Next

    'Optional - erases an existing value
    Me.lstDropdown.Value = ""

End Sub

 

Write to CSV file

The following is programming code we frequently use when creating CSV files from Excel.

Sub WriteToCSVFile()

    'Captures date/time stamp for appending to file name
    strTime = Replace(Format(Now(), "mm-dd-yy hh:mm"), ":", "-")

    'Prompts user to specify a file name
    strFile = Application.GetSaveAsFilename("File Name " & strTime, "*.csv,*.csv", , "Specify File Name")

    'Terminates routine if user clicks Cancel
    If strFile = False Then Exit Sub

    'Ensures text file is closed
    Close #1

    'Opens text file for output
    Open strFile For Output As #1

    'Determines number of rows to write
    numRows = Range("A1").CurrentRegion.Rows.Count

    'Determines number of colums to write
    numCols = Range("A1").CurrentRegion.Columns.Count

    'Loops through all rows...
    For i = 1 To numRows

        '...as well as all except for last column
        For j = 1 To numCols - 1

            'Include semi-colon to prevent Excel from adding a line-break
            Write #1, Cells(i, j).Value;

        Next

        'Write last columnn for given row, omit semi-colon so Write adds a line-break.
        Write #1, Cells(i, numCols).Value

    Next

    'Close text file
    Close #1

    'Notify user
    MsgBox "The upload file " & strFile & " has been created.", vbInformation, "Upload file created"

End Sub

 

Hide and Unhide Multiple Excel Worksheets with Ease

By David Ringstrom, CPA



It's easy to hide worksheets in Excel, but unhiding multiple worksheets within a given workbook can be a tedious exercise. Users who don't know otherwise are relegated to unhiding worksheets one at a time. In this article I'll explain a technique that allows you to hide and unhide multiple worksheets at once.


There are a couple of different ways to hide worksheets in Excel. For instance, carry out these steps to hide a single worksheet:
  • Excel 2007 and later: As shown in Figure 1, on the Home tab choose Format, Hide and Unhide, and then Hide Sheet. Or, to save a couple of steps, right-click on the worksheet tab and then choose Hide.
  • Excel 2003 and earlier: Choose Format, Sheet, Hide.
  • Excel 2011 (Mac): Ctrl-left-click on a worksheet tab and choose Hide from the shortcut menu.

Figure 1: Right-clicking on a worksheet tab provides a faster means for hiding worksheets.


You can also hide multiple sheets at once. To do so, you'll first group the sheets. In any version of Excel, hold down the Ctrl key as you click on individual worksheet tabs and then carry out the aforementioned steps. Or you can hide several contiguous sheets:
  • Click on the first worksheet tab and then hold down the Shift key as you click on the last worksheet tab within the group that you wish to hide.
  • Hide the sheets as discussed above.
Now that your sheets are hidden, making them visible again calls for repetitive action, as every version of Excel requires you to unhide worksheets one at a time:
  • Excel 2007 and later: On the Home tab, you can choose Format, Hide and Unhide, Unhide Sheet, and then unhide a single sheet. You must repeat this action for each worksheet that you wish to unhide. You can save a couple of steps by right-clicking on a visible worksheet tab and choose Unhide.
  • Excel 2003 and earlier: From any worksheet tab, choose Format, Sheet, and then Unhide. Select the sheet you wish to display and then click OK. Repeat as needed for each worksheet you wish to unhide.
  • Excel 2011 (Mac): Ctrl-left-click on a worksheet tab and choose Unhide from the shortcut menu. Select the sheet you wish to display and then click OK. Repeat as needed for each worksheet you wish to unhide.

Fortunately, the tedium of unhiding worksheets one at a time can be eliminated by use of Excel's Custom Views feature. Think of Custom Views as snapshots of workbook settings – such as the hidden or visible status of individual worksheets – that you can toggle at will. To use this feature, make sure that all worksheets are visible and then then carry out these steps:
In all versions of Excel:
  • Choose Custom Views on the View tab or menu.
  • Click Add, and then type a name for your custom view, such as All Sheets, and then click OK.
  • Next, hide any worksheets as needed and then create a second view titled Presentation View, or a name of your choosing.
Figure 3: Create a baseline view that shows all worksheets before you hide any worksheets.

Going forward, you can toggle the view by selecting the Custom Views command and then double-clicking the view of your choice, or click once on the view and then click Show as indicated in Figure 4.
This article only scratches the surface of what's possible with Custom Views. I'll explore this feature further in future articles.
Figure 4: A custom view allows you to unhide multiple worksheets with just a couple of mouse clicks.



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