Category: AccountingWEB articles

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

First Impressions of Windows 8

By David Ringstrom, CPA


On October 26, 2012, Windows 8 officially became the latest version of Microsoft's flagship operating system. With previous Windows upgrades, you could pretty much be up and running on a new PC quickly – well as long as you weren't using Windows Vista. To me, Windows 8 feels designed to stop you in your tracks at every turn, or makes you jump through unnecessary hoops until you get the lay of the land. You're going to experience this to a lesser extent in Office 2013 as well, as I'll explain in the coming weeks. Here, I'll describe my initial experiences with Windows 8 in hopes that it will aid your eventual transition.


Windows 8 perpetuates the current peek-a-boo fad software designers have embraced as of late. Long-term computer users are accustomed to always seeing a button or menu through which they can access programs or features. This simultaneously goes out the window and gets skewed in Windows 8.


Many Windows 8 features require you to move your mouse to a particular location on your screen and hover. For instance, you won't see a visible Start button, nor will you initially see a taskbar, but rather a “start panel” that is comprised of tiles. Think of these tiles as icons on steroids. Tiles can be of various sizes and may blink at you to the point of distraction. My first order of business was to remove all of the blinking tiles, and I'm slowly accumulating tiles that let me access features and programs that I actually use. However, this start panel isn't your actual desktop, at least not in a traditional Windows sense.


To get to your true Windows desktop, you'll click on a tile labeled Desktop, as shown in Figure 1. When you locate your desktop, you can place icons on it just as you would in previous versions of Windows. The desktop also has a taskbar that you can pin items to as you may have done in Windows 7 and earlier, as shown in Figure 2.


In essence you'll have two desktops, whether you like it or not. The start panel is a re-imagination of the traditional Start menu (did I mention that the start panel scrolls to the right when your mouse touches the right edge of your screen?). Once you've launched your desktop by clicking on its tile, you can return to it by pressing Alt-Tab, or access the start panel again by moving your mouse to the lower left-hand corner of the screen, hovering for a moment, and then clicking the Start button that appears, and then clicking on the desktop tile again.


Figure 1: Windows 8 doesn't offer a start menu, and only indirect access to a Desktop screen.



Figure 2: You can establish a traditional desktop and taskbar in Windows 8.



Moving your mouse to either corner on the right-hand side of your screen reveals a hidden toolbar known as the Charms bar, shown in Figure 3. The first icon on this panel is a Search command, which I've found to be the most useful feature in Windows 8. You can use this panel to search for programs, files, or features such as the Windows Control Panel. You can then pin selections from the search results list to your desktop, start panel, or taskbar so that you can find them without searching again later.


Figure 3: Many Windows 8 features, such as the Charms bar, require you to move your mouse to certain screen positions.



Get used to searching though, because you'll either be using the Search charm, or using Google to figure out even the most basic functions in Windows 8. To give you a sense of what I mean, here's how you shut your Windows 8 computer down:
  • Move your cursor to either corner on the right-hand side of your screen to display the Charms bar.
  • Within the Charms bar click Settings.
  • Within the Settings panel click Power.
  • Within the Power section choose Sleep, Shut Down, Restart, or Update.
A couple of undocumented methods for shutting down a Windows 8 computer include holding down the Power button on the front of your PC, or yanking the cord out of the back of the computer. Yes, I know these aren't appropriate, but you'll be tempted.


In short, all of the functionality that you're accustomed to is available in Windows 8, but you're going to have to dig to find it. Microsoft has foisted on us a grand vision of a single operating system for all devices. I can see where Windows 8 could be useful on a tablet device, but on a traditional, non-touch screen computer, it's been an exercise in frustration.


There are some aspects of Windows 8 that I do like, and I'm sure over time I'll acclimate to the changes. If you're intrigued by Windows 8, make sure that you plan adequate time to get up to speed. In my experience, it's an operating system that focuses on bells and whistles rather than productivity. Hopefully Microsoft will recant and give professionals the ability to turn off the glitz and actually get some work done.



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

Microsoft Outlook: Disabling the Send without a Location Prompt

By David Ringstrom, CPA


Outlook often tries to be helpful and alert you when you've left the Location field blank within a meeting request. However, often the meeting location is implicitly known or isn't necessary, such as for a phone call. In this article, I'll demonstrate how you can eliminate the prompt shown in Figure 1. You can adapt this technique to prefill other fields within Outlook forms as desired.
Figure 1: A simple form customization in Outlook can eliminate this prompt.



To get started, carry out these steps:
1. For all versions of Outlook: Select your calendar in Outlook.
  • Outlook 2010 and later: Turn on the Developer tab by choosing File, Options, and then Customize Ribbon. Click the Developer checkbox, and then click OK, as shown in Figure 2.  Click the Design a Form button on the Developer tab, as shown in Figure 3.
  • Outlook 2007 and earlier: Choose Tools, Forms, and then Design a Form.
Figure 2: Enable the Developer tab in Outlook 2010 and later.



2. Double-click on Appointment in the Standard Forms library, as shown in Figure 3.
Figure 3: In Outlook 2010 and later, Design a Form appears on the Developer tab. Double-click on Appointment in all versions of Outlook.



3. Fill in a location, such as (none), as shown in Figure 4.
  • Outlook 2007 and later: Click Publish and then Publish Form, as shown in Figure 4.
  • Outlook 2003 and earlier: Choose Tools, Forms, and then Publish Form.
Figure 4: Publish the form after adding text in the Location field.



4. Assign a Display Name, such as Appointment, and then click Publish, as shown in Figure 5.
Figure 5: Assign a name to your custom form.



5. Close the form window, and click Yes to save if prompted.
6. The next steps involve making this new form the default for your calendar. To do so in all versions of Outlook:
  • Right-click on your calendar (underneath “My Calendars” along the left-hand side of Outlook) and choose Properties, as shown in Figure 6.
  • Select the form that you just created from the “When posting to this folder, use” drop-down list, and then click OK. The built-in Outlook forms have a prefix of IPM; any forms that you customize will not have this prefix.
Figure 6: Make this new form the default for your Calendar.



Going forward, the Location field will be prefilled with the text of your choice, and you'll no longer encounter the dreaded “Send meeting request without location” prompt.



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 12

Microsoft Office 2013 Crosses RTM Threshold

By David H. Ringstrom, CPA



Microsoft Office 2013 has crossed an important threshold referred to in the industry as Release to Manufacturing (RTM). This means the Office development team has finalized the flagship suite and formal beta testing is now complete.


Unlike Windows 8, which has a formal release date of October 26, 2012, Microsoft continues to be cagey regarding a shipment date for Office 2013. Presently, Microsoft assures general availability in first quarter 2013. Starting in November 2012, select customer groups will receive final versions of Office 2013. In the interim, anyone can download and install a free, but time-limited version of Office 2013 from www.office.com/preview . This beta version can be installed alongside existing Office software, such as Office 2007 or 2010.


As noted in one of my previous articles , Office 2013 will formally adopt open document standards. This means spreadsheets, word processing documents, etc., that are created in Office 2013 can be opened without issue in competing office suites, such as the freely available OpenOffice .


Office 2013 also heralds a sea change with regard to pricing . Microsoft is implementing a “subscribe and pay less, or buy shrink-wrapped and pay more” approach to this new version.



Other new features include: 
  • Touch-enabled support under Windows 8, although the software will also run on Windows 7. Windows XP users will not be able to install Office 2013.
  • A rejiggering of the File menu in most Office applications, although the ribbon interface introduced in Office 2007 remains largely intact.
  • Cloud integration, such as the ability to seamlessly save and retrieve documents to Microsoft's Skydrive service.
  • Tablet-enabled versions of Office for Windows RT devices (formerly known as Windows on ARM devices).


Each application will sport individual improvements as well. For example, Excel 2013 includes a Recommended Pivot Table feature to help users visualize data summaries. A Flash Fill feature fills in gaps within data based on pattern recognition. A new Recommended Charts guides users through telling a story in graph form. A compelling feature within Word 2013 is the ability to edit PDF documents, while a Resume Reading feature automatically bookmarks where you last left a document. PowerPoint has a new Slide Zoom feature that allows you to zoom in and out within individual slides. Outlook will offer a new Weather Bar that will show the weather forecast within your calendar.


Customers purchasing Office 2010 on or after October 19, 2012, will receive Office 2013 at no charge when it becomes available.



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 24

Microsoft Reveals New Office 2013 Pricing Scheme

By David H. Ringstrom, CPA


Earlier this week, Microsoft unveiled a major pivot in how it plans to market its flagship productivity suite in the future. Office 2013 will offer inducements intended to encourage users to subscribe to the software rather than purchasing traditional licenses. Shrink-wrapped versions of the software will be available, albeit at potentially punitive prices. Home users with multiple computers will find the subscription model to be particularly enticing, while business users may have to sharpen their pencils before deciding.
Here's an overview of the upcoming offerings:
  • Office 365 Home Premium – $99.00/year – Up to 5 users can use Office on up to 5 PCs or Macs
  • Office 365 Small Business Premium – $149.00/year – A single user can use Office on up to 5 PCs or Macs
  • Office Student and Home – $139.99 for use on a single computer
  • Office Home and Business – $219.99 for use on a single computer
  • Office Professional – $399.99 for use on a single computer
All Office 365 users get the following programs and benefits:
  • Word
  • Excel
  • PowerPoint
  • OneNote
  • Outlook
  • Access
  • Publisher
  • The ability to temporarily use Office on any PC
In addition, Office 365 Home Premium users receive:
  • 27 GB of storage space on Skydrive
  • 60 minutes per month Skype world calling (if this seems like an odd addition, it's because Microsoft recently purchased Skype)
Office 365 Small Business Premium Users receive a different set of add-ons:
  • 25 GB Outlook mailbox, along with shared calendar, task lists, and contacts
  • 10 GB Cloud storage plus 500 MB storage per user
  • Online meeting hosting
  • A website with no additional hosting fees
On the other hand, Office Student and Home users only receive four applications: Word, Excel, PowerPoint, and OneNote. The Home and Business version adds Outlook, while Office Professional adds Access and Publisher as well. Further, Office 2010 offers single or multiple PC licensing, while Office 2013 will require one license per device. Here's a quick comparison of Office 2010 pricing versus Office 2013:
Office Home and Student
  • 2010: $119.99 for one PC, or $149.99 for 3 PCs
  • 2013: $149.99 per PC
Office Home and Business
  • 2010:  $199.99 for 1 PC, or $279.99 for 2 PCs
  • 2013: $219.99 per PC
Office Professional
  • 2010:  $349.99 for 1 PC, or $499.99 for 2 PCs
  • 2013: $499.00 per PC
With Microsoft simultaneously eliminating multiple PC licensing and raising prices on the shrink-wrapped suites, some users may need an Excel spreadsheet to determine which pricing scheme works best. Regardless, Microsoft isn't alone in moving to a subscription-based model; companies such as Intuit are testing similar arrangements.
Although a formal release date for Office 2013 hasn't been announced, free preview (a.k.a. beta) versions of the software are available for trial use:

 

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