Category: AccountingWEB Excel articles

Jan 23

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

Jan 09

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

Jan 09

Resolve to Learn and Use 14 Excel Keyboard Shortcuts in 2014

By David Ringstrom, CPA

 

It’s a new year, which brings the promise of fresh starts, and resolutions to work smarter. Keyboard shortcuts are one of the best ways to both save time and reduce wear and tear on your wrists when working in Microsoft Excel. In this article I discuss fourteen of the keyboard shortcuts that I use most often in Excel. This is by no means a comprehensive listing, but rather a throwing down of the gauntlet to challenge you to incorporate at least 14 keyboard shortcuts into your daily work.
In no particular order, here are fourteen of the keyboard shortcuts that I use most frequently in Excel:

 

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

 

Dec 17

Utilizing Excel’s COUNTIF Function to Break Ties

By David Ringstrom, CPA


In a recent article I demonstrated how you can use the LARGE and SMALL functions to isolate the x largest or smallest values within a list. I then used the MATCH and INDEX functions to return the corresponding names associated with the values. However, if two items on a list share the same value, MATCH/INDEX will return the same name for both items, as shown in Figure 1. In today's article, I'll describe how to use the COUNTIF function to create a tiebreaker in such situations.


Figure 1: Ties within a ranking list can result in duplicate matches on the item name.



In Figure 1, I used the LARGE function to rank items from largest to smallest. The LARGE function in cell G2 takes this form:
=LARGE(B$2:B$11,E2)


This formula is then copied down through cell G11.
In cell F2, I used this formula to return the corresponding names.
=INDEX(A$2:A$11,MATCH(G2,B$2:B$11,0))


As shown in Figure 1, bananas appear on the list twice in column F. This is because kiwi sales are also 637. As shown above, the MATCH function in cell F4 is looking for the number 637, based on cell G4. It finds this amount in cell B5, and so it quits looking and returns 5 as the row amount that INDEX should use to return the item name. The LARGE function also returns 637 in cell B6, but the MATCH function within cell F5 also returns 5, because MATCH stops looking at a list once it finds a match.


The solution to this problem is to make the figures in cells B2 through B11 be unique, without materially affecting the amounts. To do so, we can use the COUNTIF function, which has two arguments:
  • range – This is a range of cells in which we wish to look for a specified value.
  • criteria – This represents the value that we're seeking.
In this case, I'm going to use the COUNTIF function to add a multiple of .001 to values that appear multiple times on the list. To do so, I initially put this formula in cell C2:


=COUNTIF($B$2:B2,B2)


I then copied this down through cell C11. As you can see in Figure 2, this counts the number of times that each value appears on the list. Notice my judicious use of the $ signs to indicate absolute references. I want to create an expanding range, so I anchor the starting point at cell B2 by using $B$2. Don't use any dollars signs around the second B2, because we want this to become B3, B4, B5, and so on as we copy the formula down the column.


Figure 2: The COUNTIF function can determine the number of times a number appears within a list.



Now that I've identified the duplicates, the next step is to add a tiny increment to the duplicate amounts to make each be unique. I modified the previous formula to take this form:


=(COUNTIF($B$2:B2,B2)-1)*0.0001+B2


In this case, I'm letting COUNTIF determine how many times the value has appeared on the list and subtracting 1 from it. If the value appears on the list only once, there's no reason to change the original value. If the amount appears more than once, I want to add .0001 to it based on the number of times that it's appeared on the list. As shown in Figure 3, kiwi sales in cell C6 become 637.0001. If strawberry sales were also 637, its amount would become 637.0002.


Figure 3: This revised version of COUNTIF adds a tiny, additional amount to duplicate values.



Once I had the formulas in place, I copied cells C2 through C11 to the clipboard, and then right-clicked on cell B2 and chose Paste Special, and then double-clicked on Values, as shown in Figure 4. If you're using Excel 2010 or later, you can click the Paste Values icon. Within the Paste Special dialog box, double-clicking on Values eliminates the need to click the OK button. You can use this double-click trick in most dialog boxes when you're making a single selection. Once you've pasted the data, press Escape to clear with Windows clipboard.


Figure 4: Use the Paste Special Values option to replace the original values.



In any case, as shown in Figure 5, bananas no longer appear on the list twice. This use of COUNTIF gives you an effective means to break ties when necessary within your Excel spreadsheets.


Figure 5: The duplicate wording in our ranking list has been removed.



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

Dec 10

Identifying Largest and Smallest Values in an Excel List

By David Ringstrom, CPA



Periodically, you may wish to rank a series of items within an Excel spreadsheet. Many users often rely on sorting data in ascending or descending order. I'll describe an alternative that uses the LARGE and SMALL functions to create an ordered list of whatever you'd like to rank.


You're probably already familiar with the MIN and MAX functions in Excel, which return the smallest or largest value within a list, respectively. As shown in Figure 1, MIN indicates the smallest value is 191, and the largest value is 958. MIN and MAX are limited to the single smallest or largest values respectively, but LARGE and SMALL allow you to return the second largest or third smallest value if you choose. I'll explain these functions in a moment, but first let's explore MIN and MAX.


Figure 1: MIN and MAX return the largest and smallest values from a list, respectively.



As shown in Figure 1, the MIN and MAX functions are similar in nature to the venerable SUM function, except they return the single smallest or largest value, respectively, instead of adding up values. The SMALL and LARGE functions work in a similar fashion, but with an extra argument:


=SMALL(array,k)
=LARGE(array,k)


In these functions, array is a range of cells, and k is the nth value you wish to return. As shown in Figure 2, =LARGE(B2:B11,2) would return 872 as the second largest value, while =SMALL(B2:B11,3) would return 266 as the third smallest value. By way of comparison, the following formulas would both return 958 and 191 for the largest and smallest values, respectively:


=MIN(B2:B7)


=SMALL(B2:B7,1)


=MAX(B2:B7)


=LARGE(B2:B7,1)


Figure 2:  LARGE and SMALL return the nth values from a given list.



If you're creating a list of the top or bottom 10 values, it can be tedious to manually edit each LARGE or SMALL function with the proper value for the k argument. To save time, I use the ROW function, either inside the LARGE or SMALL function or in a separate column. The ROW function returns the row number for a given cell. If you enter this in cell D2, Excel will return 2:


=ROW()


In Figure 3 you can see that I entered this formula in cell D2:


=ROW()-1


In this case, ROW() would return 2 because it's entered on the second row, so subtracting 1 changes the result to 1. Alternatively, I could provide the address of a cell in row 1 of the worksheet:


=ROW(D1)


Figure 3: These formulas are the basis for creating a ranked list without re-sorting the source data



In cell F2, I entered this formula:


=LARGE(B$2: B$11,D2)


Depending upon my needs, I might have used this formula instead:


=LARGE(B$2: B$11,ROW()-1)


The dollar signs in the formula instruct Excel not to change the row numbers when I copy the formula down. The last bit of information that you'll likely want is to associate a name with the values that you've isolated. To do so, you can use the MATCH and INDEX functions together in cell E2:


=INDEX(A$2:A$11,MATCH(F2,B$2:B$11,0))


I'll explain MATCH and INDEX in more detail in an upcoming article, but for now the short answer is that in this case MATCH is determining which row a sales figure amount is on, and then INDEX returns the corresponding text from column A. This is akin to VLOOKUP, but with the flexibility of being able to look up data from the left, which VLOOKUP can't do without making a special provision.


There's one caveat to this approach that you should be aware of. If the same value is on your list twice, then MATCH/INDEX will return the corresponding name twice. Next week I'll describe how you can use the COUNTIF function to create a tiebreaker that will give you a unique value to match for each item in the list.


In any case, once I have the formulas in cells D2 through F2 in place, I'm able to copy the formulas down as many rows as needed without any additional modifications, as shown in Figure 4. Do keep in mind that if you drag too far, LARGE or SMALL will return #NUM!.


Figure 4: If you drag the formulas in cells D2:F2 too far, LARGE will return #NUM!.



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

Nov 30

Microsoft Excel: Use Color to Identify Variances

By David Ringstrom, CPA


Accountants are often given the mundane task of identifying variances that exceed specific tolerances. Rather than trying to eyeball columns of numbers, you can use Excel's Conditional Formatting feature to make such variances leap out on the screen. I'll demonstrate how you can use Excel's conditional formatting to identify variances that are both $2,000+/- and 10%+/- of the budget.


Conditional Formatting is a powerful feature, but it has some nuances that can frustrate uninitiated users. Depending on the conditions you're trying to apply, it's often best to apply the formatting to a single cell and then use Excel's Paste Special Formatting feature to transfer the formatting to the remainder of the related cells. Formula-based Conditional Formatting, which I'll demonstrate in a moment, often won't return the desired results when you attempt to apply it to multiple rows or columns at once.


Figure 1 shows a representative actual versus budget comparison. Our goal is to apply yellow highlighting to any variances of both +/- $2,000 and +/- 10%. Specifically, a row should only be highlighted when both tests are met. I'll first describe the steps for use in Excel 2007 and later, and then offer a brief rundown in Excel 2003. To implement such a rule, carry out these steps:


Figure 1: We'll use Conditional Formatting to identify variances that are both +/- $2,000 and +/- 10%.



1. Excel 2007 and later: Click cell D2, choose Conditional Formatting on the Home tab, and then click New Rule, as shown in Figure 1.
Excel 2003 and earlier: Click on cell D2, choose Format, and then Conditional Formatting.
2. Excel 2007 and later: Choose Use a Formula to Determine Which Cells to Format, as shown in Figure 2.
Excel 2003 and earlier: Change Cell Value Is to Formula Value Is.


Figure 2: Conditional Formatting offers a variety of options, including crafting your own formulas.



3. All Excel versions: Enter this formula:
=AND(ABS($D2)>2000,ABS($E2)>0.1)
4. All Excel Versions: Click the Format button, choose Fill tab, click the desired color, such as yellow shown in Figure 3, and then click OK twice.


Figure 3: Color is one of many types of formatting that you can apply via Conditional Formatting.



5. All Excel versions:  Select cell D2, click the Format Painter as shown in Figure 4, and apply the formatting to cells D2 through E10. The Format Painter appears on the Standard toolbar in Excel 2003.


Figure 4: Use the Format Painter icon to transfer formatting from cell D2 to cells D2 through E10.



6. All Excel versions: Reapply the percentage number formats in cells E2 through E10, as shown in Figure 5. Excel doesn't offer a way to transfer conditional formatting by itself without overwriting the existing number formats. However, it's far easier to get Conditional Formatting right by applying it to a single cell first, and then reapply any number formats as required.


Figure 5: You may have to restore some number formats after transferring Conditional Formatting from one cell to many.



If you're using Excel 2007 or later, you can now right-click on one of the yellow cells, choose Filter, and then Filter by Selected Cell's Color, as shown in Figure 6. You can also sort by color as well in Excel 2007 and later. Figure 7 shows the filtered list. To remove the filter, click the Filter icon on the Data tab in Excel's menu known as the Ribbon.


Figure 6: You can filter (as well as sort) based on color in Excel 2007 and later.



Figure 7: The variances are identified and isolated.



With regard to the formula that I used:
  • The ABS function returns the absolute value of a number – in other words ($17,396) converts to $17,396.
  • The AND function allows you to test for up to 255 conditions at once. In this case, I'm testing for the absolute value of the variance in column D being greater than $2,000, and the absolute value of the variance percentage in column E being greater than 10%. If both tests are true, then conditional formatting will be applied.
  • The dollar signs before the column letters are critical when applying Conditional Formatting in this fashion since in effect we're copying and pasting the format to a second column. If you omit the dollar signs before the column references, Excel will adjust the column references, meaning that your Conditional Formatting won't return the desired result.




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

Nov 30

Managing Protected View in Excel 2010/2013

By David H. Ringstrom, CPA



As you migrate to Excel 2010 or 2013, you may sometimes encounter spreadsheets that open in a special Protected View mode. You can view – but not edit – such documents until you grant permission within an onscreen prompt. I'll explain Protected View and how you can adjust this security measure to suit your needs.


By default, Microsoft Excel 2010 and later assume that files opened from the Internet, e-mail attachments, or other certain locations could contain malicious programming code. Accordingly, Excel opens such documents in a special mode known as Protected View, which enables you to view, but not edit, the spreadsheet. You're notified of Protected View by way of the security prompt shown in Figure 1.


You'll notice that Excel's ribbon user interface is collapsed as well, since virtually all functionality in Excel is disabled except for navigating the workbook and copying selected cells to the clipboard. If you wish to edit the spreadsheet, you must click the Enable Editing button shown in Figure 1.


Figure 1: Protected View allows you to safely review documents of unknown provenance.



There are two ways to get to the settings that determine when Protected View is automatically invoked. If you open a document in Protected View, you can click on File, Info, and then choose Protected View Settings as shown in Figure 2.


Figure 2: The Info option in Excel 2010 and 2013 allows easy access to Protected View settings.



Alternatively, you can carry out the steps shown in Figure 3, which are more involved:
  • Choose File, and then Options.
  • Within the Options window, choose Trust Center, and then click the Trust Center Settings button.
  • Within the Trust Center Settings, choose Protected View, and make any adjustments necessary, and then click OK twice.


Figure 3: You can also access the Protected View settings through Excel's Options dialog box.



Thirty-two-bit versions of Excel 2010 offer an additional check box labeled Enable Data Execution Prevention Mode. This option was removed from Excel 2013 and does not appear in 64-bit versions of Excel 2010. This setting prevents poorly written programming code in Excel add-ins from potentially crashing your computer and should always be left enabled. Data Execution Prevention cannot be disabled in Excel 2013.


Keep in mind that you can manually open files in Protected View. To do so:
  • Press Ctrl+O to launch Excel's Open dialog box.
  • Click once on the name of a document.
  • Choose Open in Protected View from the Open button's menu, as shown in Figure 4.


Figure 4: You can manually open unknown documents in Protected View in Excel 2010 and later.



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

Nov 05

Automating Excel Spreadsheet Report Titles

By David H. Ringstrom, CPA



Many users tire of retyping report titles such as “For the Period Ended October 31, 2012” month after month. Further, if you're like me, sometimes printing the report reminds you that the title needs updating. I'll explain a couple of techniques you can use to simplify, and even automate, such date-based report titles.


Let's first assume that each month you'll enter a date, such as 10/31/12, into cell A1. We can transform this into “For the Period Ended October 31, 2012” with a simple formatting trick. In Excel 2007 and later, click the Format Cells: Number button on the Home tab, as shown in Figure 1. This displays the Format Cells dialog box shown in Figure 2. (In Excel 2003 and earlier, choose Format, and then Cells.)


Figure 1: The Format Cells: Number button displays the Format Cells dialog box.



Within the Number tab of the Format Cells dialog box, choose Custom, and then enter the following text in the Type field:


“For the Period Ended” mmmm d, yyyy


Figure 2: A custom number format can transform a date like 10/31/12 into a report title.



Be sure to place double quotes before For, after Ended, and include a space before mmmm. The four m's within the format signify that we wish to convert a numeric month to its equivalent month name. The d displays the day portion, the date that we input in cell A1, and yyyy presents a four digit year. Although I could use mmmm d, yyyy on its own as a custom format to spell a date out in long form, in this case, I enclosed additional text in quotes. Going forward, you can simply type a new date in mm/dd/yy format and the title will update automatically.


Figure 3: The report date can be updated by typing a new date in cell A1 in mm/dd/yy format.



Assuming that your report dates are always the last date of the previous month, you can completely automate your report title with a simple formula. To do so, we'll use the DATE function, along with the YEAR, MONTH, and NOW functions.


The DATE function has three inputs: year, month, and day. The YEAR and MONTH functions each accept a date as their only reference. The NOW function returns today's date, and so use these functions together as shown in Figure 4:


=DATE(YEAR(NOW()),MONTH(NOW()),0)
To break the formula down, I placed the NOW() function inside the YEAR and MONTH functions, respectively. This gives me the year and month for today's date. Although I could use Excel's DAY function to extract the day portion of a date, in this case, I used a zero instead. Doing so instructs Excel to return the last day of the preceding month. Couple this formula with the aforementioned custom number format, and you'll never need to update that report title again.


Figure 4: The combination of the DATE function and a custom number format automates your report title.



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

Oct 30

Trapping Errors within Excel Formulas

By David H. Ringstrom, CPA



From time to time you may craft a formula that in some cases will return an error, such as #DIV/0! or #N/A. Many users overcome this situation by using combinations of the IF, ISERROR, and ISNA worksheet functions. However, using these functions in concert can sometimes result in unnecessarily complex formulas. In this article, I'll discuss an alternative available to anyone using Excel 2007 or later.


Let's first start with a common scenario, where calculating the percentage change between two numbers results in a #DIV/0! error, as illustrated in Figure 1. Presently, the formula in cell D2 takes this form:
=(B2-C2)/B2


Figure 1: Excel returns a #DIV/0! error when you attempt to divide by zero.



As you can see, when we copy this formula down the column, D4 returns #DIV/0! because cell B4 equals zero. To eliminate the #DIV/0! error, we can amend the formula to take this form:


=IF(ISERROR((B2-C2)/B2),”-“,(B2-C2)/B2)
As shown in Figure 2, the formula in cell D4 now returns a dash instead of #DIV/0!.


Figure 2: A combination of IF and ISERROR can display alternative results when a calculation results in an error.



For the uninitiated, the IF function has three arguments, or inputs, which are:
  • logical_test – In this case, the ISERROR function carries out the test and either returns TRUE or FALSE.
  • value_if_true – If ISERROR returns TRUE, we want Excel to return a dash, which we must enclose in double quotes. The quotes are only required when we want the IF statement to return text, so there's no need to enclose numeric values, such as zero, in quotes.
  • value_if_false – If ISERROR returns FALSE, we want Excel to perform our original calculation.


Although valid, this approach introduces complexity to the formula because we must repeat our original calculation twice. However, as shown in Figure 3, if our spreadsheet will only be used in Excel 2007 or later, we can streamline the formula to this:
=IFERROR((B2-C2)/B2,”-“)


Figure 3: The IFERROR function streamlines complex error-trapping formulas.



First introduced in Excel 2007, IFERROR has two arguments:
  • value – This is the calculation we wish to test for errors.
  • value_if_error – This is the result we wish to display should our calculation return an error.


As you can see, IFERROR eliminates the need to repeat any portion of the calculation. However, it isn’t backwards compatible with earlier versions of Excel. As shown in Figure 4, the IFERROR function will return #NAME? in Excel 2003 and earlier. If you need compatibility with earlier versions of Excel, use the aforementioned IF/ISERROR approach instead.


Figure 4: The IFERROR function is incompatible with Excel 2003 and earlier, but can be used in Excel 2007 and later.



Also, a possible downside to both IFERROR and ISERROR is that they mask any # sign error that your formula may trigger. You may have situations where you want to mask a #N/A error but be notified of other errors, such as #DIV/0!, #REF!, or #NAME?. As shown in Figure 5, you can craft a formula to trap #N/A errors only, but display other # sign errors like #DIV/0!:


=IF(ISNA(VLOOKUP(A8,$A$1:$D$5,4,0)),0,VLOOKUP(A8,$A$1:$D$5,4,0))


Figure 5: ISNA tests specifically for #N/A errors but will allow other errors to be displayed.



Sharp-eyed readers will notice that I used a zero in place of the typical FALSE within VLOOKUP to indicate an exact match. If you're unfamiliar with VLOOKUP, it's a function that's used to return values from a list that has four inputs:
  • lookup_value – This represents the item that you're looking for within a list. For instance, in the formula above, A8 signifies that we want to look for the word “Apples.”
  • table_array –This represents the cell coordinates for the list you wish to search. In the formula above, our list is comprised of cells A1 through D5. VLOOKUP searches for the lookup_value in the first column of the table_array.
  • col_index_num – This argument tells VLOOKUP which column you want to return a value from when a match is found in the first column. In this case we want to return the % change from the fourth column of our table_array.
  • range_lookup – Use this cryptic setting to signify if you want an exact match or an approximate match. In this case, we want an exact match on the word “Apples,” so enter the word FALSE or a zero in this final argument. Omit this argument, or use the word TRUE or a one in that position to signify an approximate match, such as if you were looking up a tax bracket based on a gross income number.



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

Oct 05

Little Known Techniques for Opening Excel Files

By David H. Ringstrom, CPA


Every Excel user knows how to open a spreadsheet: Click on File (or the whimsical Office button in Excel 2007), choose the Open command, select a file, and then click the Open button. In this article, I'll explain several hidden techniques that can give you more control over unfamiliar spreadsheets.


Before we get to the specifics on the hidden features, first double-click on the title bar of the Open dialog box in Excel. This expands the window so that you can see more files without scrolling from side-to-side. Simply double-click on it again to restore it to its traditional size.


Next, click once on a file, and then click the arrow on the right edge of the Open button, as shown in Figure 1. Most users never realize that a menu exists here, which contains options that may vary based on your version of Excel:


Figure 1: Many users overlook the submenu within the Open button.



Open: This is the same function as clicking the Open button, so choosing it would simply add extra mouse clicks to your task.


Open Read-Only: This opens your file in a read-only state, so you can't accidentally save over the original. It's a great option to keep in mind when you want to look at a document but not necessarily create a new copy or save your work.


Open as Copy: This creates a copy of your document in the same folder as the original, but prepends Copy(x) before the file name. Unlike the Read-Only option, you can save changes to this document.


Open in Browser: This option becomes available when you click once on an HTML file within Excel's Open dialog box. The file you select will appear in your default web browser.


Open in Protected View: Excel 2010 and later offer a Protected View feature, which you can use to open unfamiliar documents in a sand-boxed mode. In Protected View you cannot make any changes, and any data connections or macros are disabled until you click the Enable Editing button shown in Figure 2. If you close this toolbar accidentally, you can also choose File, Info, and then release Protected View should you need to edit the document.


Figure 2: Protected view allows you to safely open unfamiliar spreadsheets.



Open and Repair: Available in all versions of Excel, this hidden command may enable you to open workbooks that Excel reports as corrupted.
Unless you need one of these special features, I recommend that you double-click on a file name and skip the Open button.


There's one other trick that must be accomplished outside of Excel's Open dialog box. As shown in Figure 3, you can right-click on an Excel file within a Windows Explorer window and then choose Print. Windows will launch Excel if necessary, print a copy, and then close the document, and close Excel if it wasn't previously open. This is a handy technique for grabbing a printout on your way out the door to a meeting.


Figure 3: You can quickly print an Excel document from a Windows Explorer window.



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

Sep 10

Transposing Data in Excel Worksheets

By David H. Ringstrom, CPA


From time to time, you may encounter data that's presented in rows, when you actually need it to be in columns instead. Or you may wish to flip data in columns into a row format. Many users don't realize that Excel offers an easy way to accomplish this task with just a couple of mouse clicks. You can even create a keyboard shortcut in Excel 2007 and later. I'll also discuss a couple of frustrating nuances you may encounter when transposing data.


Let's assume that you have a list of car buyers and their vehicle information presented on two rows as we see in Figure 1. We'd like to transform this data into a columnar list, as shown in Figure 1, so that we can use the information to generate letters via Mail Merge in Microsoft Word.


Figure 1: Data presented in rows can be transposed to columns.



To do so, select cells A1 through C3, and press Ctrl-C to copy. Next, right-click on cell E3 and choose Paste Special. Click the Transpose checkbox, and then click OK, as shown in Figure 2.


Figure 2: You can easily transpose data from columns to rows, or vice versa.



Transforming data back to rows is just as simple. Select the data in cells E3 through F6, press Ctrl-C to copy, and then right-click on the cell of your choice and use Paste Special, Transpose.


Transposing data in Excel 2010 and later is even easier thanks to a new icon that appears when you right-click on a cell, as shown in Figure 3. The fourth icon in the Paste Options section allows you to skip the Paste Special dialog box. As an added benefit, if you hover over this icon for a moment, Excel shows you a preview of how the data will look if you transpose it.


Figure 3: Excel 2010 and later offers a Transpose shortcut.



If you spend a lot of time transposing data, you can create a keyboard shortcut in Excel 2007 and later. To do so, carry out these steps, as also illustrated in Figure 4:
  • Right-click on the Home tab and choose Customize Quick Access Toolbar.
  • Select All Commands from the Choose Commands From list.
  • Scroll down and click once on Paste and Transpose, then click Add, or simply double-click on Paste and Transpose.
  • Click OK to close the Excel Options dialog box.


Figure 4: You can add a Transpose icon to your Quick Access Toolbar in Excel 2007 and later.



Your new Transpose icon will have a shortcut key predicated on the order in which the command appears on your Quick Access Toolbar. As shown in Figure 5, you can now press Alt-4 to transpose data. Keep in mind that this option is only enabled when you have data on the clipboard.


Figure 5: Quick Access Toolbar icons can be accessed via keyboard shortcuts.



There are two quirks to keep in mind with regard to transposing data:


1. You cannot transpose data into the range of cells from which you've copied. If you attempt to do so, Excel will present the cryptic prompt shown in Figure 6. Simply consider the marquee border around the cells you've copied to be an electric fence that you shouldn't touch. Once you've transposed the data, you're then free to cut and paste it into a new position.


Figure 6: Excel doesn't allow data to be transposed into the cells from which you've copied data.



2. In many cases, the formulas will adjust automatically, but in other cases you may be presented with a series of #REF! Errors. In such cases, use the Paste Special dialog box and choose the Values option along with the Transpose checkbox, as shown in Figure 7.


Figure 7: Choose Values along with Transpose if transposed formulas result in #REF! errors.



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

Sep 09

Microsoft Excel: Taking the Pain Out of Freezing Panes

By David H. Ringstrom, CPA


Freezing or unfreezing worksheet panes typically involves multiple mouse clicks, but I'll show you a way to carry out this task with a single keystroke. For the uninitiated, freezing panes allows you to lock in place columns or rows that you specify along the left-hand and/or top of Excel's worksheet area. As you scroll to the right or down, the frozen columns or rows remain in place so that you can always view worksheet headings or the initial columns. When you no longer need the rows or columns locked in place, you in turn can unfreeze them.
To carry out this task in Excel 2007 and later, as shown in Figure 1, first click on the worksheet position you wish to freeze. Next, go to the View tab, click on Freeze Panes, and then make a selection from the submenu, such as Freeze Panes.
Figure 1: An example of freezing panes on a worksheet.



As shown in Figure 2, a thin black bar appears below a set of frozen rows, and to the right of any frozen columns. To unlock the rows or columns, navigate to the View tab, choose Freeze Panes, and then Unfreeze Panes. It's a little simpler in Excel 2003: choose Window, and then Freeze Panes or Window, and then Unfreeze Panes, respectively.
Figure 2: Thin black lines indicate frozen rows and/or columns.



Regardless, you streamline this process down to a simple keystroke of your choice in Excel 2007 and later. To do so, as shown in Figure 3, right-click on the View tab in Excel, and then choose Customize Quick Access Toolbar to display the Excel options window. Select Commands Not in the Ribbon, and then scroll down the resulting list until you find Freeze Sheet Panes. Either double-click on this command, or click once on it and click Add to add it to your Quick Access Toolbar. If you frequently freeze and unfreeze sheet panes, use the arrows on the right-hand side to move the Freeze Sheet Panes command so that it's the first command on the list. Click OK to close the Excel Options dialog box.
Figure 3: Add Freeze Sheet Panes to your Quick Access Toolbar



Once you've placed the command on your Quick Access Toolbar, you can now press the Alt key to reveal the numeric shortcut for the Freeze Sheet Panes command. If you made it the first command on the list, you can now press Alt-1 to freeze or unfreeze sheet panes.  You must still position your cursor as before when freezing panes, but you can bypass navigating through the View tab and the resulting Freeze Panes submenu.
Excel 2003 users can also give themselves one-click access to freezing/unfreezing panes, sans keyboard shortcut. As shown in Figure 4, choose Tools, Customize, and then click on the Commands tab. Choose Window, and then drag the Freeze Panes command either onto an existing toolbar, or to Excel's menu bar, into the unused space adjacent to the Help menu.
Figure 4: Excel 2003 users can arrange one-click access to the Freeze Panes command.



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

Aug 27

Yes, Microsoft Excel Is Talking to You

By David H. Ringstrom, CPA


From time to time, you may need to compare a set of figures or text in an Excel spreadsheet to a paper document. Doing so can be time consuming and error-prone as you shift your attention between screen and paper. A better approach is to enable Excel's hidden Text to Speech feature.
As shown in Figure 1, you can add the Text to Speech feature to the Quick Access Toolbar in Excel 2007 and later. If you're using Excel 2010 or later, you also have the option to add these commands to a custom group in your ribbon, but for purposes of this article, I'll focus only on the Quick Access Toolbar. At the end of this article, I'll provide alternate instructions for Excel 2003.
Once you add the icons, the Text to Speech feature is simple to use. Simply select one or more cells, and then click the Speak cells icon. Excel then reads the numbers or text to you aloud with surprising clarity.
Figure 1: The Speak Cells icon instructs Excel to read numbers and/or text aloud.



Carry out the following steps to add these icons to your Quick Access Toolbar:
  1. As shown in Figure 2, click the arrow at the end of the Quick Access Toolbar, which typically appears at the top of your Excel screen, above the Office button in Excel 2007 or the File menu in Excel 2010 and later, and then choose More Commands.
  2. When the Excel Options dialog box appears, choose Commands Not Shown in the Ribbon from the Choose Commands From drop-down list.
  3. Scroll down the list, click once on Speak Cells, and then click Add. To locate this command more quickly, click once on the <Separator> command at the top of the list, and then press the letter S. This will move you down the list near where Speak Cells appears. You can also skip the Add button by simply double-clicking the Speak Cells Command.
  4. Add the Speak Cells – Stop Speaking Cells command in the same fashion, and then click OK.
Figure 2: These steps show how to add the Speak Cells icons to your Quick Access Toolbar.



To test your work, enter a variety of text and numbers in a range of worksheet cells, which you'll then select to highlight. Click Speak Cells and listen while Excel reads aloud to you.
Excel 2003 users can utilize this functionality as well. To do so, choose Tools, Customize, and then click on the Commands tab. Select Tools from the Categories list, and then drag Speak Cells to any existing toolbar or to the unused area to the right of the Help command. Carry out the same process for the Stop Speaking icon, and then click Close.



 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

Aug 14

Excel Tip: Calculating Interest

By David H. Ringstrom, CPA


Microsoft Excel is an ideal tool for calculating the cost of borrowing money, but are you doing so in the most efficient manner? In this article, I'll describe how you can use the CUMIPMT function to calculate interest expense for a loan, whether for a month, a year, or the length of the loan – all within a single worksheet cell. I'll also show you how to add an amortization schedule to any workbook with just a couple of mouse clicks.


Before I describe the CUMIPMT function, let's first take a look at the PMT function, which calculates the payment amount for a loan. PMT has 3 required and 2 optional arguments:


rate – The interest rate for the loan expressed as a monthly rate.
nper – The length of the loan in months.
pv – The amount being borrowed, also referred to as the present value.
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.


Figure 1: The PMT function calculates the monthly payment for a loan.



As shown in Figure 1, a monthly payment of $586.04 for 36 months is required to pay back $20,000 at an interest rate of 3.5 percent. The PMT function always returns a negative amount because Excel sees the payment as an outflow. 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.


You construct formulas using CUMIPMT in a similar fashion to PMT, but in this case, all 6 arguments are required:
rate – The interest rate for the loan expressed as a monthly rate.
nper – The length of the loan in months.
pv – The amount being borrowed, also referred to as the present value.
start_period – The starting month from which to calculate interest on the loan. Use 1 to calculate interest from the start of the loan, or 13 to calculate interest for just the second year of the loan.
end_period – The ending month through which to calculate interest on the loan. Use the same value as the nper argument to calculate interest for the life of the loan, or 24 to calculate interest for just the second year of the loan.
type – Specify 0 to indicate that payments are made at the end of the period, or 1 for payments made at the start of the period.


Figure 2: The CUMIPMT function computes total interest for all or part of a loan.



As shown in Figure 2, the CUMIPMT function shows that borrowing $20,000 at 3.5 percent for 36 months will cost $1,097.50. Although not shown, the cost for the second year of the loan is $368.55. To calculate the principal paid back for a given portion of the loan, use CUMPRINC, which utilizes the same sequence of arguments as CUMIPMT.


As you can see, it's possible to use worksheet functions to calculate interest and principal for all or part of a loan without building out a full-scale amortization schedule. However, when one is needed, right-click on any worksheet tab and then choose Insert, as shown in Figure 3. Click on the Spreadsheet Solutions tab, and then double-click on Amortization Schedule.


Figure 3: Add an amortization table to any workbook with just four 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

Aug 03

Managing Excel Add-ins

By David H. Ringstrom, CPA


Many programs interface indirectly with Microsoft Excel, but some offer built-in functionality that you may not need or want. In other cases, an add-in that you rely on may suddenly vanish from Excel. In this article I'll explain how you can manage these features.


Excel has long offered the ability for add-ins. For instance, Adobe Acrobat often helpfully adds a separate toolbar or ribbon tab. However, Excel 2007 and 2010 offer built-in PDF printing capability, so do you really need another handful of commands to create PDFs? Certain versions of Sage 50 (formerly Peachtree Accounting) and QuickBooks add extra ribbon tabs to Excel as well. This is great if you need these tools, but unwanted add-ins can cause Excel to take longer to launch, plus your Excel interface can become unwieldy, as shown in Figure 1.


Figure 1: Add-ins sometimes add extra menu commands to Excel's user interface


Here's how to manage add-ins in Excel 2010:
  1. As shown in Figure 2, click on File, Options, and then Add-ins.
  2. Click the drop-down at the bottom and choose COM Add-ins, and then click Go.
  3. Enable or disable add-ins as needed, and then click OK as needed.
The aforementioned steps should handle most add-ins that surreptitiously appear in Excel, but another place to check is the Excel Add-ins list in step 2 above.
The steps are the same for Excel 2007, except you'll click the Office button in the top left-hand corner, and then choose Excel options. From there, follow steps 3 through 9 in Figure 2. In Excel 2003 and earlier, click the Tools menu, choose Add-ins, and then make your selections.


Figure 2: Managing add-ins in Excel



From time to time, an add-in that you rely on may vanish from the Excel user interface or not function correctly. If Excel deems that the add-in has caused an issue, it will automatically place the add-in on a hidden disabled list. To determine if any add-ins have been disabled in Excel 2007 or 2010, follow steps 1 through 4 in Figure 2, and then choose Disabled Items in step 5. As shown in Figure 3, a prompt will appear from which you can enable any add-ins if necessary. In Excel 2003, click Help, About Microsoft Excel, and then click the Disabled Items button.


Figure 3: A hidden Disabled Items prompt allows you to restore an add-in's functionality



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