David Ringstrom

Author's posts

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 23

Homeland Security Warns of Java Vulnerability

By David H. Ringstrom, CPA



Computer security sites across the Internet lit up last week with the news that hackers are actively exploiting vulnerabilities in the ubiquitous Java software that resides on many computers. In a rare move, the United States Computer Emergency Readiness Team (US-CERT) advised all computer users to immediately disable Java within their browsers on any platform, including Windows, Mac, Linux, and Solaris.


Hackers are actively exploiting Java to control affected computers, potentially installing malware, attempting identity theft, and other malicious actions. Over the weekend, Oracle released Java 7 Update 11, which reportedly patches this vulnerability. All computer users that have Java installed on their computer should install this patch immediately. Another alternative is to disable Java in all web browsers. US-CERT, sponsored by the US Department of Homeland Security, offers more details and remediation guidance on its website.


Oracle Java 7 Update 10 and earlier reportedly are being actively exploited by hackers. It’s possible that some earlier versions, such as Java 6, aren’t affected, but to be safe, all users should immediately disable any version of Java or install Java 7 Update 11. Java 7 Update 10 and later offer a check box to disable Java in web browsers, but earlier versions of Java don’t offer this feature.


To access Java on a Windows computer, locate the Java icon in the Windows Control Panel. Click the About button on the General tab, as shown in Figure 1, to determine the version of Java you have installed. If it reads Version 7 Update 11, you have the latest version of Java installed. In this case, you may still wish to disable Java. To do so, close the About Java window and click on the Security tab as shown in Figure 2. Deselect the Enable Java Content in the Browser check box and then click OK.


Figure 1: Click the About button on the General tab of Java’s Control Panel icon to determine your Java version.



Figure 2: Java 7 Update 10 and later allow you to disable Java by deselecting a check box.



If you don’t have Java Version 7 Update 11 or later, click the Update tab, and then the Update Now button as shown in Figure 3, and then follow the onscreen prompts to install the latest version of Java. Once you install this update, the check box shown in Figure 2 may still be missing from the Security tab. If so, close the Java Control Panel and relaunch it by double-clicking on the javacpl.exe file that will likely be found in one of these two locations:
  • C:\Program Files\Java\jre7\bin
  • C:\Program Files (x86)\Java\jre7\bin
Figure 3: You can download the latest version of Java from within the Java Control Panel.



On a Macintosh OS X computer, launch a Finder window, search for Java, double-click on Java Preferences, and then follow the aforementioned instructions.
Oracle offers specific guidance on removing Java on its website.



A previous version of this article first appeared on www.accountingweb.com .
About the author:

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

Jan 14

Using Collections in Excel to populate UserForm Controls

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

 

Jan 14

Write to CSV file

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

 

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 22

Verifying Dates within Excel UserForms

The following code is a routine we frequently use in our Excel projects to verify that the user has entered a date properly within a text box in a userform. In addition, dates such as 0704 get transformed to 07/04/yy where yy is the current year. Valid date inputs include 0704, 070412, 07/04/12, or 07/04/2012. All four will be validated and transformed to 07/04/12.

 

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

Plug and Print

Click here to access Plug and Print. Plug and Print adds a dialog box to your spreadsheet that automatically lists every visible worksheet.

Print all worksheets by choosing Select All, or selectively print one or more worksheets. You can also choose whether to preview before printing.

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 29

Building an Amortization Table in Microsoft Excel

In this one-hour presentation for Lawline.com, David Ringstrom, CPA uses an amortization table exercise in Microsoft Excel to demonstrate numerous ways to use Microsoft Excel more effectively.