Category: AccountingWEB articles

Sep 06

How to Create a “Chainsaw” List of Excel Worksheet Functions

 

By David Ringstrom, CPA

Depending upon your version of Excel, you have nearly 500 different worksheet functions at your disposal. Some worksheets functions, such as CUMIPMT and NPER, are like the chainsaw in my garage. I don’t use them very often, but when I do, no other tool will suffice. Other functions are more akin to screwdrivers and pliers in that I use these so often that I can type them in my sleep. In this article I’ll show you an easy way to keep a “chainsaw” list of functions at the ready, so you can avoid trying to remember “how’d I do that before?”

We’ll store our list in an unlikely location known as the Name Box. It’s pretty much impossible to use Excel and not notice the Name Box, which appears just above the upper-left-hand corner of the worksheet frame. Most users know this as the space in Excel where you can determine the address of the currently selected cell.

 

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

 

Aug 15

How to Speed Up Microsoft Excel’s Help Window

By David Ringstrom, CPA
 
From time to time, you probably get stymied in Excel and think, “I'll just look in the help file.” Doing so is as simple as pressing F1 or clicking the question mark icon that appears in the upper right-hand corner of Excel 2007 and later. However, you might end up waiting several seconds for the help window to appear. In this article, I'll explain how you can eliminate this annoying wait that can break your stride.
 

 

 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 08

Maximizing Excel’s Recent Items Menu

 
By David Ringstrom, CPA
 
 

Depending upon your version of Excel, the Recent list on the File menu can streamline access to both files and folders. If you work on numerous spreadsheets, this list offers marginal value in Excel 2003 and earlier. The list gained some new functionality in Excel 2007, reached its zenith in Excel 2010, and fell back somewhat in Excel 2013. This list provides one-click access to between 4 and 25 files (and sometimes folders). As you'll see in this article, each version of Excel offers markedly different functionality with regard to accessing recent spreadsheets.
 
 
 

Continue reading this article  where it first appeared: 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 02

Converting a Digital Photo into an Excel Spreadsheet

By David Ringstrom, CPA

In an unlikely mash-up, Matt Parker of Think Maths offers a free tool that converts a digital photo of your choice into an Excel spreadsheet. According to the website, “digital photographs are actually just spreadsheets. When you take a photo, your camera measures the amount of red, green, and blue light hitting each pixel, ranks them on a scale of 0 to 255, and then records those values as a spreadsheet.” Parker's website is able to extract said values from a digital photo, record the numeric values in worksheet cells, and then use Excel's Conditional Formatting feature to recreate the photograph.

 

 

Continue reading where this article first appeared:  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


 
 

Jul 26

How to Automate Text File Links in Microsoft Excel

By David Ringstrom, CPA
 

Some time ago, I explained how to use Excel's Text to Columns Wizard for separating text within a spreadsheet into columns. Although this approach is helpful for data that's in a spreadsheet, in other cases, you may wish to link spreadsheets to text files that change periodically. In this article, I'll walk you through the steps of automating this process.

 

Continue reading this article where it first appeared:  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

Jul 12

How to Eliminate a Common Spreadsheet Design Flaw

By David Ringstrom, CPA
 

Data within Excel spreadsheets is commonly organized in columns, with explanatory titles at the top of each section. When carrying out this most basic of data entry tasks, many Excel users often unwittingly cause Excel to be harder to use. Whenever column headings within a worksheet span two or more rows, a cascade of issues can occur. Fortunately, a simple technique can help you avoid frustration and save time when working in Microsoft Excel.

 

  Continue reading this article where it first appeared: www.accountingweb.com .

 

 

About the author:

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

Jun 28

Three Ways to Convert Text-Based Numbers to Values

By David Ringstrom, CPA



Periodically, you may encounter numbers in Excel that you can't sum or use arithmetically. A common cause for this is numbers formatted as text. Often, reports exported from other programs, such as an accounting package, will be formatted as text or they might contain embedded spaces.

In this article, I'll describe three ways you can convert numbers that appear trapped under glass into a usable format.


First, there are a couple of ways to determine if your numbers are formatted as text. Select one or more of the suspect values and then in:
  • Excel 2007 and later or Excel:Mac 2011: Determine if the word Text appears on the Home tab, as shown in Figure 1.
  • Excel 2003 and earlier: Choose Format, Cells, and then determine if the Number tab is set to Text.
Figure 1: The Text format prevents you from using numbers with mathematical functions.


The Text format in a cell displays the contents of a worksheet cell rather than its result. Thus, if you enter a formula in a cell formatted as text, the underlying formula will appear in your worksheet cell rather than the result.


However, the Text format is not the only way to store values as text. If you look closely within a worksheet cell, you may see that a numeric value is prefaced with a single quote. This is another means to display the contents of a cell rather than the result.


You can categorically determine if a number is stored as text by way of the ISTEXT worksheet function. For instance, if cell A1 contains a value you think may be stored as text, type this formula in a nearby blank cell:
=ISTEXT(A1)
The ISTEXT function will return TRUE if the value in cell A1 is being stored as text, or FALSE if it isn't.


Now that we know how to determine if a number is stored as text, let's look at three ways to convert one or more cells back to numeric values:


One approach is to use the =VALUE function, as shown in Figure 2. If the cell that you reference with the VALUE formula can be converted to a numeric value, you'll see the corresponding number. Otherwise, you'll see a #VALUE! error, which signifies that the referenced cell contains letters or other non-numeric contents. You can then copy the VALUE formula as needed to convert additional values to numbers. Keep in mind that you'll then need to copy these formulas to the clipboard, and then use Paste Special, Values to preserve the numeric results. You might choose to replace the original set of numbers with the results from the VALUE formulas, after which you can clear the VALUE formulas from the spreadsheet.
Figure 2: You can use the VALUE function to convert numbers stored as text to usable values.


A somewhat simpler approach involves using the Paste Special, Multiply command. In any version of Excel, enter the number 1 in a blank cell and then copy that value to the clipboard. Next, select the range of values stored as text, right-click, and choose Paste Special, Multiply, and then OK. You can then clear 1 from the worksheet cell.
Figure 3: You can also convert numbers stored as text to values by multiplying the cells by 1.


The easiest way to convert a range of values to text, though, involves using the Text to Columns feature. In any version of Excel, select the range of cells that contain numbers stored as text, then choose Data, Text to Columns, and then click Finish. Don't select any choices within Text to Columns; simply launch the wizard and then click Finish, as shown in Figure 4. Your text-based numbers will be usable numbers in Excel.


Figure 4: The Text to Columns wizard is the easiest way to convert numbers stored as text to values.


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

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

Jun 24

Automating Data Validation Lists in Excel

By David Ringstrom, CPA



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


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


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



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



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



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



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



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



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



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



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



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



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



A previous version of this article first appeared on www.accountingweb.com .




 

About the author:

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

Jun 14

Improving the Integrity of Excel’s SUM Function

By David Ringstrom, CPA

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

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

 

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

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


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

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


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

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


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


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

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

Jun 09

Microsoft’s Chip In Program Offers Crowdfunded Laptops to Students

By David Ringstrom, CPA



Microsoft is dipping its corporate toe into the crowdfunding pool. An experimental program dubbedChip In allows students to tap friends and relatives in a quest to fund their next computer. The program will run through September 1, 2013, and offers a 10 percent discount on a selection of name-brand laptops, tablets, and all-in-one computers. Fleet-footed student funders can also score a free, four-year subscription to Office 365 University.

The Chip In program is limited to full- or part-time students in the United States with an .edu e-mail address. Employees of US educational institutions who have an .edu e-mail address can also participate in the program.


Participants start by selecting a laptop or tablet that costs as little as $359 or as much as $1,079. Prices reflect a 10 percent discount, or the amount that Microsoft is “chipping in” to get participants started.


Next, participants establish a profile by logging in with their Facebook account, and then the race is on to see which friends, relatives, and acquaintances are willing to “chip in.” The first 10,000 students to fully fund their purchase get a complimentary four-year subscription to Office 365 University, a $79 value.


There are a couple of Byzantine aspects to the program. Let's say you select the $359 ASUS X202e but only raise $300. In that case, all pledges will be returned; in effect, no one's credit card will be charged. But, if you select the $810 Lenovo Yoga and only raise $500, you can still apply that amount toward a less expensive computer, or you can “chip in” the remaining balance yourself. Specifically, participants must either (1) raise the entire amount of a computer priced less than $499, or (2) raise a minimum of $499 to be able to get a new laptop through this program.


Another catch is that one of the tablets, the Surface RT, uses an operating system incompatible with Office 365 University. Further, the FAQ section [3] of the program contains a hypothetical question about the ability to install Office 365 University on two devices, followed by the terse response: “We are not sharing any details about installation on mobile devices today.” Byzantine, indeed.


There are certainly at least a couple of upsides here for Microsoft. First, the program gets participants to use Windows 8 computers, as opposed to Mac OS or another operating system. Further, the free subscription to Office 365 gives students four years' use of the web-based Office applications. Once acclimated to this environment, they'll likely continue as paying subscribers, which furthers Microsoft's Cloud-migration and subscription-based revenue goals.


Office 365 [4] typically costs $99/year for home users and allows use of the web-based Office applications on any computer, and the desktop versions on up to five devices. Conversely, students can qualify for a discounted Office 365 University edition that runs $79 for four years. Just be sure not to ask questions about installing on mobile devices.


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

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

Jun 04

Resolving #VALUE! Errors in Microsoft Excel

By David Ringstrom, CPA

 



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


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


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



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



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



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



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



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



A previous version of this article first appeared on www.accountingweb.com .


About the author:

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

May 29

Overcome a Nuance of Excel’s Subtotal Feature

By David Ringstrom, CPA

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

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


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


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


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


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


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


A previous version of this article first appeared on www.accountingweb.com .

 

About the author:

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

May 17

Farewell, Lotus 1-2-3

By David Ringstrom, CPA



 

IBM recently announced that Lotus 1-2-3 will no longer be available for purchase. Most readers of this article will likely have one of two reactions: “What is Lotus 1-2-3?” or else an incredulous “Lotus 1-2-3 was still on the market?” If you're of a certain age, you may wistfully remember Lotus 1-2-3 as your first spreadsheet program.

Lotus 1-2-3 has had a suggested retail price of $352, but it soon will no longer be available for purchase. Although it wasn't the first spreadsheet – VisiCalc has that distinction – Lotus 1-2-3 owned the spreadsheet for a number of years. Although Lotus did outlast Quattro Pro, Microsoft Excel eventually won the spreadsheet wars.


Lotus was slow to innovate and clung valiantly to its DOS-based heritage by way of feeble desktop publishing efforts like WYSIWYG. That wacky acronym stood for “What You See Is What You Get” with regard to applying fonts, colors, and other formatting to data and getting the same output on paper as one saw on-screen. If you want a sense of how spreadsheets looked “back-in-the-day,” you can download and run VisiCalc.


Lotus 1-2-3 was first released in 1983, so its discontinuance this year marks the end of a thirty-year run. IBM bought Lotus Corporation in 1995 and still sells Lotus Notes, among other offerings, but will soon be dropping the Lotus name entirely. Lotus Corporation had a storied past, including innovative spreadsheet alternatives such as Improv and Symphony that never gained traction in the marketplace.


IBM will still provide support for Lotus 1-2-3 until June 2014, so die-hard users have a bereavement period during which they can transition their spreadsheet affairs to Apple's Numbers, Google Docs, OpenOffice, or perhaps even Microsoft Excel.


A previous version of this article first appeared on www.accountingweb.com .



About the author:

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

May 13

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

By David Ringstrom, CPA

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

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

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

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

NPER has 3 required and 2 optional arguments:

 

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

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

pv – The current loan balance

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

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

 

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


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

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

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

ROUNDUP has two arguments:

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

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


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

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

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

=TRUNC(B4/12)

Or this would work as well:

=ROUNDDOWN(B4/12,0)

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

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

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

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

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

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

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


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

 

A previous version of this article first appeared on www.accountingweb.com .

 

About the author:

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

May 06

The End Is Near for Windows XP

By David H. Ringstrom

Despite three subsequent Windows releases, Windows XP remains the second most popular operating system version. This cycle started when users eschewed Windows Vista, even though Microsoft redeemed itself with Windows 7. In turn, Microsoft again alienated its customers with major user interface changes in Windows 8. Regardless, the 38 percent of computer users currently relying on Windows XP will soon need to move to a modern operating system, or risk exposure to malware.


Many users are holding on to Windows XP due to the incomprehensible changes Microsoft made in Windows 8 [1]. The technology rumor mills abound with chatter that the next version of Windows, apparently code named “Windows Blue,” will appear later this year and possibly resurrect the start button and traditional desktop that users hold dear. The latest word on the street is that this will be a free upgrade for existing Windows 8 users.


Further, personal computer sales have fallen by 14 percent as of late, which could be attributed to market push back on Windows 8. Many technology writers are making hay with this, proclaiming that we're entering a post-PC world. Others observe that PCs are becoming like microwave ovens. Most of us have a microwave oven in our house, which we only replace when it eventually wears out. While many tasks and information consumption is migrating to mobile platforms, there will always be a significant set of tasks that are best performed on a PC, particularly for accountants.


Regardless, Microsoft will end all support for Windows XP as of April 2014. This means that no technical support or new software patches will be available. A cadre of motivated individuals continuously seeks out vulnerabilities in Windows, which Microsoft subsequently closes with software patches. However, no Windows XP patches will be forthcoming after April 2014, which could mean open season for malware purveyors. But, even if Microsoft keeps its word and drops support for Windows XP, third-party antivirus and anti-malware sellers will continue to offer some level of protection for Windows XP users.


Although there are practical reasons to end support for Windows XP, Microsoft has a revenue incentive as well. Just like any other business, software companies need an ongoing stream of revenue. Accounting software vendors such as Intuit and Sage typically support their three most recent versions, which ensures periodic upgrades from their user interface. Indeed, Sage recently narrowed the cycle further by in effect requiring annual software upgrades from payroll service users. Operating systems are a bit different, though, as evidenced by so many computer users still booting up Windows XP computers. Personally, I'm still coaxing an aging Windows XP computer along on one of my desks, but waning support for XP offers yet another reminder that it's time to pasture my old workhorse.



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