Category: AccountingWEB Excel articles

Jun 24

Automating Data Validation Lists in Excel

By David Ringstrom, CPA



Working in an Excel spreadsheet can be somewhat like the Wild West – unless other provisions are made, users can enter any value in any cell. One way you can restrict users to a predefined set of values is by way of Excel's Data Validation feature. I'll explain how you can create in-cell drop-down lists, along with how to “future proof” the Data Validation list so that the feature won't require maintenance if you add additional items in the future. This technique also resolves an annoying problem in Excel 2007 where data validation lists cannot be placed on other worksheets.


Before we look at Data Validation, let's first establish our list. Let's say we want the user to choose a type of fruit. On the second worksheet of a blank workbook, create a list, such as shown in Figure 1. Once you do so, make the list into a Table in Excel 2007 and later, or a List in Excel 2003 and earlier. After you click on any cell within your list:
  • Excel 2007 and later – Choose Insert and then Table. Make sure that My Table Has Headers is selected and then click OK.
  • Excel:Mac 2011 – On the Tables tab of the ribbon, click the arrow next to the New command and then choose Insert Table with Headers.
  • Excel 2003 and earlier – Choose Data, List, and then Create List.
Figure 1: Enter a few items on the second worksheet of a workbook.


A benefit of tables (and lists in Excel 2003 and earlier) is that if you add items to the bottom of the list, the table will expand automatically to encompass the new items. However, we can't use this self-expanding table or list directly with Data Validation. To do so, we must create a range name that encompasses all but the first row of the table. Select the second through last row of your range, and then:
  • Excel 2007 and later – On the Formulas tab choose Define Name.
  • Excel 2003 and earlier, or Excel – Mac 2011: Choose Insert, Name, and then Define.
Once the Define Name dialog box shown in Figure 2 appears, enter a name such as Fruit, ensure that the Refers to field references the second through last row of your table, and then click OK.
Figure 2: Assign a name to the second through last cell of your table or list.



Let's first take a look at the Data Validation feature. On the first worksheet of the workbook where you created your list, click on cell A1 and type the word Fruit. Next, select cell A2 and choose Data and then Data Validation. The dialog box in Figure 3 will appear. On the Settings tab, choose List in the Allow field. When the Source field appears, type an equal sign along with the range name that you assigned before, such as =Fruit.



[1]
Figure 3: Choose List and then specify your range name as the Source on the Settings Tab.



On the Input Message tab, enter a title, such as the word Fruit, to describe the input field, along with a message, such as “Choose an item from the list,” as shown in Figure 4. It's not necessary to use quotation marks in the Description field.
Figure 4: Although optional, the Input Message tab allows you to document the validation.



Finally, on the Error Alert tab, enter a Title, such as “Invalid Input,” and an Error Message, such as “You must make a selection from the list,” as shown in Figure 5. Keep the style set as Stop to prevent the user from bypassing the list items. Click OK to close the Data Validation dialog box.



Figure 5: Be sure to complete the Error Alert tab; otherwise, invalid inputs will trigger a generic and cryptic error prompt.



Going forward, when you click in cell A2, a note should appear as shown in Figure 6. This documents the spreadsheet. If the user makes a valid choice, he or she will then be able to move to another cell; otherwise, the prompt shown in Figure 7 will stop the user in his or her tracks.



Figure 6: Our choices on the Input tab yield a helpful prompt when the user clicks on cell A2.



Figure 7: The user will see this prompt if he or she tries to enter something that doesn't appear on the list.



Now, return to your original list, and add two more items, as shown in Figure 8. When you return to your cell with the Data Validation list, the new items should appear automatically. The combination of the table/list features and range names automates this aspect. Within the Data Validation dialog box, you can provide a specific set of cell coordinates in the Source field, but you would then need to manually change the setting if you later expanded the list. Further, Excel 2007 and earlier won't allow you to reference cell coordinates on other worksheets. Best practices in spreadsheet design call for separating supporting lists from the actual data on separate worksheets. Anyone using Excel 2007 and later is no longer faced with the struggle of where to safely position supporting lists for Data Validation on the same worksheet as the actual input is occurring.



Figure 8: Since the ultimate source for a validation list is a table/list, new items appear on the Data Validation list automatically.



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

Jun 14

Improving the Integrity of Excel’s SUM Function

By David Ringstrom, CPA

My unscientific observation is that the SUM function is the most widely used function within Excel spreadsheets. This function makes it easy to add up multiple cells at once without laboriously adding multiple cells together individually.

Taking things a step further, the AutoSum feature makes it easy to instantly add multiple totals into a spreadsheet. However, such ease of use actually introduces risk into Excel spreadsheets.

 

Let's say you need to total the values shown in Figure 1. Rather than manually add the SUM function to cells B4:G4 and H2:H4, you can use two keyboard shortcuts instead:
  • Click once on cell A1 and then press Ctrl-A. This will select the contiguous area, which we need to expand by one row and one column.
  • Hold down the Shift key, then tap the Down arrow, and then the Right arrow. At this point, your selection should look like Step 1 of Figure 1.
  • Press Alt-Equal Sign in Windows, or on a Mac, press Command-Shift-T. Alternatively, you can click the AutoSum icon, which looks like a Greek E. Any of these actions should add totals to row 4 and column H simultaneously. Do be sure to select the cells you wish to sum; otherwise, AutoSum will place a SUM function in the first numeric cell within the current region of your spreadsheet.

Figure 1: You can use AutoSum to add totals to the row below and column to the right if you expand the initial selection.


This technique added the necessary sums, but unfortunately, these formulas we so easily added are not future-proof, as shown in Figure 2. Here's how you can confirm this:
  • Insert a new row at row 4 so that the totals move down to row 5. Label cell A4 as Pears, and then enter 1000 in cells B4 through G4.
  • Notice how the totals in row 5 don't reflect the additional amount that was added for each month. 

Figure 2: The totals don't reflect the additional amount that has been added for each month. 


To correct this, we'd need to manually adjust the SUM formulas in row 5 to include rows 2 through 4, instead of rows 2 and 3. We'd then have to remember to carry out this action each time we add a new product line. Fortunately, a simple change to your spreadsheet design can liberate you from having to remember to adjust these formulas as shown in Figure 3:
  • Insert a blank row just above the total row, which in this case now appears on row 5. Change the row height to half of its normal height. An easy way to do so is to click on the row number on the worksheet frame and then drag the bottom of the row upward slightly. Next, adjust the SUM formulas in row 6 to be: =SUM(B1:B5).

Figure 3: Insert a blank row just above the total row to avoid adjusting the SUM formula each time a new item is added.


You'll notice that I included row 1 in the formula as well as the blank row 5. Going forward, if a user adds a new row, he or she will either enter it on or below row 2 or above row 5. Our SUM function will automatically encompass the additional row(s) without further interaction on our part. To improve the integrity of your spreadsheets, be sure your SUM formulas always sum one row above and one row below the actual numbers you're adding up.


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

Jun 04

Resolving #VALUE! Errors in Microsoft Excel

By David Ringstrom, CPA

 



It's a frustrating experience when a simple Excel spreadsheet displays #VALUE! in a worksheet cell rather than the expected result. Many times the problem is obvious, in that you've tried to do arithmetic using text and numbers, but sometimes the culprit is harder to track down.


As shown in Figure 1, the formula =C2/A2 returns #VALUE! because I purposely mistyped the formula and attempted to divide the value 5000 in cell C2 by the word Apples in cell A2. The same error would have appeared if I were to try to divide cell A2 by C2, or add or subtract one of those cells from the other. With that said, I could sum cells A2 through C2, as in =SUM(A2:C2), and the result would be 5500 and not #VALUE.


Figure 1: Dividing a number by a cell that contains text will return a #VALUE! error.



Now, let's change the scenario a little and assume that in cell D2 we have the formula =C2/B2, but cell C2 is blank. In this case, Excel should return 0, but you may still encounter a #VALUE! error as shown in Figure 2. The reason is that cell C2 may not be truly blank. Users, either on purpose or unintentionally, erase values by tapping the spacebar. Insidiously, this makes it appear as if a cell is blank when in actuality it isn't. Before we explore this further, keep in mind that if cell B2 is blank but C2 contains a number, then the aforementioned =C2/B2 will return a #DIV/0! error, which signifies division by zero.
Figure 2: Although cell C2 looks blank in both examples, Excel returns a #VALUE! error if the cell isn't actually blank.



Sharp-eyed users can press F2 within a cell and then make note of where the blinking cursor is, as shown in Figure 3.  If the cursor is adjacent to the left border of the cell, the cell is most likely blank. However, if the cursor is a couple of millimeters to the right, then there's a space. To categorically check, click once on a cell, and then press the Delete key.
Figure 3: Zoomed-in view of individual worksheet cells contrasts empty vs. non-empty worksheet cells.



You can also use the ISBLANK worksheet function to determine whether a cell is blank or not. For instance, the formula =ISBLANK(C2) will return TRUE if cell C2 is blank or FALSE if it isn't.



Curiously, if you're doing simple arithmetic with cells that contain numbers stored as text, Excel will perform the calculation without issue. To try this out, type a single quote in cell B2, followed by a number such as 500 and then two spaces. In cell C2, enter a single quote followed by 5000 and two spaces. In cell D2, the formula =C2/B2 will return 10. Of course, if you were to try to sum the current values of cells B2 and C2, the SUM function would return 0, because worksheet functions don't generally convert text to numbers on the fly. Should you encounter numbers stored as text, the easiest solution is to use the Text to Columns wizard:
  • Select one or more cells in a single column, choose Text to Columns from the Data tab or menu, and then click Finish.
This technique only allows you to convert one column at a time. If you need to convert multiple columns:
  • Enter the number 1 in a blank worksheet cell and then copy it to the clipboard.
  • Select the range of cells you wish to convert to values and then right-click and choose Paste Special.
  • Double-click on Multiply.
This action will multiply all of the values by 1, which in turn also converts them to numeric values instead of text.



Regardless, there are other situations in Excel that can cause the #VALUE! error, such as a recalculating a linked reference to a closed workbook or including a text-based reference in a formula that's expecting a value, such as =SUM(“Apples”,5,500). The Microsoft website offers additional guidance [1] as well.



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

May 29

Overcome a Nuance of Excel’s Subtotal Feature

By David Ringstrom, CPA

Many users rely on the Subtotal feature in Excel to instantly insert totals, averages, counts, or other statistics into a list. As you'll see, the feature is easy to use – until you want to copy or format just the total rows. In this article, I'll explain the nuance so that you'll be in complete control of this feature.

Figure 1 shows a typical data set for use with the Subtotal feature. We can use the Subtotal feature to insert a total after each product as well as a grand total at the bottom. As an added benefit, we'll then be able to expand and collapse our list by way of outlining buttons that will appear at the left. To do so:
  1. Select any cell within your list.
  2. Choose the Subtotal command on the Data tab in Excel 2007 and later, or the Data menu in Excel 2003 and earlier.
  3. Select the Cases Sold field, and then click OK.
Figure 1: Use the Subtotal feature to automate inserting totals within a list of data.


Figure 2 shows the newly inserted subtotals, along with outlining controls at the left-hand side of the worksheet. If you click the 1 button, the list will collapse down to just the total rows, as shown in Figure 3.
Figure 2: The Subtotal feature inserted a total after each change in the Product column.


Figure 3: Click the 1 button to collapse the list down to just the total rows.


Click on cell A1 and then press Ctrl-A to select the contiguous block of data. Press Ctrl-C to copy, and then in a new worksheet, press the Enter key to paste your data. At this point the aforementioned nuance reveals itself. One would think Excel would copy and paste just the visible rows that contain the totals, but instead, Excel includes all of the rows, as shown in Figure 4. 
Figure 4: Even though we thought we copied just the total rows, Excel pasted all of the rows.


Similarly, any formatting that you apply would affect the hidden rows as well. Here's how to take charge of the situation:
  1. As shown in Figure 5, select the cells that you wish to copy or format.
  2. Press Ctrl-G to display the Go To dialog box and then click the Special button.
  3. Double-click Visible Cells Only.
Figure 5: Use the Go To Special command to select just the visible rows for copying or formatting.


At this point, you can copy or format just the total rows. You can access the Go To Special command in other ways as well:
  • In any version of Excel, press F5 instead of Ctrl-G.
  • In Excel 2007 and later, choose the Find & Select command on the Home Tab and then choose Go To Special.
  • In Excel 2003 and earlier, choose Edit, Go To, and then click the Special button.
N.B.: You don't need to use the Go To Special command with filtered lists (the Filter command in Excel 2007 and later, or the AutoFilter command in Excel 2003 and earlier). Only the visible rows are affected when you copy or format filtered lists, but with the Subtotal feature, the extra step of Visible Cells Only will help you keep your sanity.


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

May 13

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

By David Ringstrom, CPA

Previously I’ve explained how you can use worksheet functions in Excel to determine the payment for a loan, as well as how to calculate total interest in a single worksheet cell. This time around I’m going to use the NPER function in Excel to show you how you can determine just how long it will take you to payoff that credit card bill on which you’re making monthly payments.

You can get a refresher on the PMT and CUMIPMT functions, but any typical loan has four key values:

  • Interest rate
  • Term of loan
  • Amount borrowed
  • Monthly payment

As discussed in my previous article, if you have the interest rate, term, and loan amount, you can then use the PMT function to solve for the payment. Sometimes, such as with a credit card balance, you’ll know the interest rate, payment, and loan amount, but not the term.  In this case, Excel’s NPER function (short for number of periods) enables you to calculate the fourth value.

NPER has 3 required and 2 optional arguments:

 

rate – The interest rate for the loan expressed as a monthly rate.

pmt – The monthly payment, which should always be shown as a negative amount.

pv – The current loan balance

fv – This optional argument allows you to specify a future value if a balloon amount is due at the end of the loan. Omitting this argument implicitly states that the loan is to be paid down to 0.

type – This optional argument allows you to specify if payments are made at the beginning of each period, or you can omit the argument to indicate that payments are made at the end of each period. You may also specify 0 in this position to explicitly indicate that payments are made at the end of each period.

 

As shown in Figure 1, it will take 36 months to pay back $20,000 with a monthly payment of $586.04 at an interest rate of 3.5 percent. Always be sure that the payment is shown as a negative number, otherwise NPER may show a slightly longer payment term. I omitted the 2 optional arguments, so in this case, the PMT function assumes the loan is paid to 0 and payments are made at the end of each period.


Figure 1: Use Excel’s NPER function to calculate the payment term for a loan.

For longer term loans, NPER may return a large number of months, such as 94, which can be difficult to convert to months in your head. Let’s extend our calculations to make the output more user-friendly.

First, as shown in Figure 2, we’ll add the ROUNDUP function to our NPER formula. Loan periods will typically involve some fraction of the final month, which for our purposes we want to treat as a whole month. The ROUNDUP function rounds a number up, as opposed to the commonly used ROUND function that may round numbers up or down.

ROUNDUP has two arguments:

number – In this case the result of NPER will be our number

num_digits – in this case we’ll use zero, as we wish to round up to the next whole month. If you wanted to round a number to say the nearest thousand, you’d use -3 instead.


Figure 2: Use the ROUNDUP function with the NPER formula to convert to months.

Next, we need to convert the result that ROUNDUP/NPER return into a number of years and months. To do so, we can use the TRUNC function. This function converts a number to an integer by removing the decimal or fractional portion. You could also use ROUNDDOWN and specify zero as the number of digits to accomplish the same effect.

To calculate the number of whole years in the loan, we can use this formula:

=TRUNC(B4/12)

Or this would work as well:

=ROUNDDOWN(B4/12,0)

In either case we’re taking the number of periods returned by NPER, dividing it by 12, and then truncating the decimal places.

Use this formula to calculate the number of months remaining after the whole years:

=ROUNDUP(B4-TRUNC(B4/12)*12,0)

We can then string all of this together into a tidy format, as shown in Figure 3:

=”months, or “&TRUNC(B4/12)& ” Years, “&ROUNDUP(B4-TRUNC(B4/12)*12,0)&” Months”

In this we’re using the ampersand to concatenate, or join together, text and calculations into an understandable phrase. I much prefer using the ampersand to join text together, but if you’re a fan of Excel’s CONCATENATE function, the formula would take this form:

=CONCATENATE(“months, or “,TRUNC(B4/12), ” Years, “,ROUNDUP(B4-TRUNC(B4/12)*12,0),” Months”)


Figure 3: Use the ampersand sign to concatenate text and calculations into a logical phrase.

 

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

Apr 26

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

Apr 26

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

Apr 14

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

Apr 05

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

Mar 25

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

Mar 15

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

Mar 07

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

Mar 05

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

Feb 15

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

Feb 01

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