Category: AccountingWEB articles

Apr 18

My First Impressions of Excel for iPad

by David Ringstrom, CPA

 

Four years after the first iPad spreadsheet, users finally have a Microsoft-sanctioned solution. When I first installed Excel on my iPad, I immediately focused on its limitations, but upon reflection I see that Excel for iPad has certain strengths as well. To be sure, tablet computers have far to go before any heavy Excel user would be highly effective with this version. And out of the gate there are some key limitations to keep in mind:

The free version of the Excel for iPad app limits you to viewing documents onscreen. Any true functionality requires an Office 365 subscription, which for most users will run $99/year. Resist the urge to activate Excel for iPad within the app—sign up for the free 30 day trial online instead. Students can pay $79 for 4 years, or savvy searchers can purchase discounted Office 365 subscription keycards online through an online reseller.

 

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

Mar 29

You Want to Use Sparklines, but they Seem to Be Disabled

 

By David Ringstrom. CPA

 

Earlier this week I presented the Chart Edition of AccountingWEB’s High Impact Excel webinar series. One of the many topics I covered was the Sparklines feature, which was first introduced in Excel 2010. Several attendees asked “What do I do when the Sparklines feature is greyed out?” One of my favorite truisms is that Excel is fraught with nuance. Indeed, a subtle nuance can disable this feature, which I’ll reveal, along with an overview of Sparklines.

As shown in Figure 1, Sparklines are a means of displaying tiny charts inside worksheet cells. This capability is built into Excel 2010, Excel 2011 for Mac, and Excel 2013. A free add-in offers similar functionality for earlier versions of Excel. Sparklines are helpful in showing trends for numbers, and are often helpful in spreadsheet-based dashboards. Within Excel, dashboards are used to enable users to assimilate a lot of data within a compact space. Sparklines can take three different forms:

 

Continue reading at www.accountingWEB,com

Mar 21

Getting Excel to Handle Social Security Numbers Properly

by David Ringstrom, CPA

 

During the recent High Impact Excel: VLOOKUP Edition webinar a senior financial analyst named Lisa asked a question related to Social Security numbers. She periodically receives employee lists where the Social Security numbers aren’t necessarily in a uniform format. For instance, some are all numeric, others have dashes, and some might be text-based. Any data analysis involving look-up functions in Excel requires that our data be clean. Here are some techniques for cleaning up the numbers.

 

Continue reading 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

Mar 14

The Indispensable NCAA Bracket Tool: Excel

by David Ringstrom,CPA

 

It’s almost Selection Sunday, when it seems that everyone in the country, including President Obama, makes their picks for the Final Four. There’s no need to search the Internet for a bracket template – you’re just a few mouse clicks away from one in Microsoft Excel. Many users overlook the wide variety of templates that are readily available in Excel. Poke around a bit, and you’ll find a dizzying array of business templates, along with a cricket scorecard, football pool squares, and much more.

 

To continue reading see  www.accountingweb.com

An alternate version of this article also appeared at GoingConcern.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, teaches webcasts for CPE Link, and writes freelance articles on Excel for AccountingWEB, Going Concern, et.al.

 

Mar 11

Part 2 of Identifying Duplicate Values in an Excel List.

by David Ringstrom,CPA

 

In a previous article I explained how you can use Conditional Formatting in Excel 2007 and later to highlight duplicate values with just a couple of mouse-clicks. Although easy to implement, this technique identifies all instances of a duplicate value. A reader then asked how to format only the second and any subsequent instances. In this article I'll explain how, along with instructions on identifying duplicate values in Excel 2003 and earlier.

Let's say that we have a list of names, such as shown in Figure 1. Our goal is to highlight the second and any subsequent times that a name appears more than once on a list. To do so, we'll select the names, and then carry out these steps:

 

To continue reading see  www.accountingweb.com .

About the author:

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

 

Mar 11

Techniques for When Excel Worksheet Tabs Go Missing

by David Ringstrom,CPA

 

It can be disconcerting when you open an Excel workbook that has several worksheets, but you only see single worksheet. If this happens, your “missing” worksheets may be hiding in plain sight due to a simple Excel setting. In addition to restoring vanished worksheet tabs, I'll also describe a couple of techniques for navigating workbooks easier, as well as other ways to find hidden worksheets.
 
Typically, within an Excel workbook you'll see worksheet tabs along the bottom of the screen, but it's also possible to hide the worksheet tabs, as shown in Figure 1. To manage this setting:
  • Excel 2010/2013: As shown in Figure 2, choose File, Options, and then enable the Show Sheet Tabs setting in the Display Options section of the Advanced options.
  • Excel 2007: Click the Office button, choose Excel Options, and then then enable the Show Sheet Tabs setting in the Display Options section of the Advanced options.
  • Excel 2003 and earlier: Choose Tools, Options, Display, and then Show Sheet Tabs.
  • Excel 2011 for Mac: Choose Excel, Preferences, View, and then Show Sheet Tabs. 
 

 To continue reading see www.accountingweb.com . 

 
About the author:

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

Mar 10

Many Ways to Use Excel’s Name Box

 

by David Ringstrom,CPA

 

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. A smaller subset of users relies on the Name Box as a navigation aid. However, that unobtrusive rectangle belies a dizzying array of functionality in Microsoft Excel. 
 
Twenty-Five Techniques:
 
 To continue reading see www.accountingweb.com  

 

Hat tip to Richard Harker for his serendipitous discovery of how the letters R and C have special meaning within the Name Box.
.
 
About the author:

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

 

Feb 21

How to Improve Spreadsheet Integrity with VLOOKUP

By David Ringstrom, CPA

 

Building complex spreadsheets without lookup formulas, such as VLOOKUP, is akin to putting a screw in the wall with a hammer. It’s possible, but the results aren’t pretty, and most probably won’t maintain integrity. The same can be said for spreadsheets where you manually reference individual cells over and over again, instead of letting Excel do the work for you.
Let’s say that you want to be able to look up addresses from a list based on a name. Users that are unaware of VLOOKUP often resort to manually copying and pasting the information, or creating simple formulas that point to the information. In the context of an invoice or other form, these “manual” lookups can become tedious. Instead, we can give Excel’s VLOOKUP function four pieces of information:

 

Continue reading on www.accountingweb.com

 

About the author:

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

Feb 13

How to Resolve Duplicate Data within Excel Pivot Tables

By David Ringstrom, CPA

 

An attendee from my recent pivot table webinar posed a question that I hadn’t encountered before.
Pamela had an issue where some, but not all, items within her pivot table were being duplicated, with two different totals. If you’re new to pivot tables, you can catch up by watching a free recording of the webinar.

 

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

Jan 13

Spreadsheet-Based Form 1040 Available at No Cost for 2013 Tax Year

By David Ringstrom, CPA

Although the IRS is still madly preparing for the 2013 filing season, one man remains ahead of the curve.

Glenn Reeves of Burlington, Kansas, has released his seventeenth spreadsheet-based version of the US Individual Income Tax Return, commonly known as Form 1040. Since 1997, Mr. Reeves has pursued this “labor of love,” which means he allows any taxpayer to download and use the spreadsheet for free.

 

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 12

The Curious Case of Strikethrough in Word and Excel

By David Ringstrom, CPA
I often find myself using the strikethrough feature in both Word and Excel to mark items as completed. This feature is fairly straightforward in Word, as a strikethrough icon appears prominently on the Home tab in Word 2007 and later. Conversely, in Excel this feature doesn't have its own icon, but it does have a keyboard shortcut, Ctrl-5. Yet there's no built-in shortcut for strikethrough in Word. In this article, I'll describe a couple of ways that you can streamline access to this – and pretty much any feature – in both Word and Excel.
As shown in Figure 1, the strikethrough feature is a font setting that allows you to draw a line through text. In Word 2007 and later, you can select a block of text and then toggle strikethrough on or off with a mouse click. You can also access this feature from the Effects section of the Fonts dialog box shown in Figure 1. The traditional way to apply strikethrough in Excel involves carrying out steps A through C by way of the Format Cells dialog box.
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

Nov 23

How to Conditionally Display Decimal Places in Excel: Part 2

 
by David Ringstrom,CPA
 
In Part 1 of this series I showed how to use a custom number format to conditionally display decimal places. Although the technique is simple, the downside is it may not work in every situation. For instance, the number formats shown in Part 1 would display 0.75 with two decimal places, but would round 4,200.75 up to 4,201 since 4,200.75 is greater than 1. In this article, I'll describe how to use Excel's Conditional Formatting feature to handle just about every imaginable situation.
 
Excel's Conditional Formatting feature is available on the Home tab of Excel 2007 and later for Windows as well as Excel 2011 for Mac, or the Format menu of Excel 2003 and earlier. You can establish up to 64 levels of Conditional Formatting in Excel 2007 and later, or 3 levels in Excel 2003 and earlier.
 

 

Related article:

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 13

How to Conditionally Display Decimal Places in Excel: Part 1

 
by David Ringstrom,CPA
 
 
The simple task of displaying decimal places sometimes causes angst for spreadsheet users. If you have a list of both large and small numbers, there's tension between rounding the small numbers to whole values and making the large numbers harder to read by adding two trailing zeros.
 
In this article, I'll describe how to add decimal places on demand by way of using a custom number format. In Part 2 of this series, I'll demonstrate an alternative that uses the MOD function along with Excel's Conditional Formatting feature.
 

 

Related article:
 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.

 

Oct 31

Reinstalling Microsoft Office When You Don’t Have a Disc or Download

 
by David Ringstrom,CPA
 
 
From time to time, you may need to reinstall Microsoft Office because you purchased a new computer or you're trying to resolve a program error. Or, in Excel 2010 and later, you may need to move between the 32-bit and 64-bit versions. In this article, I'll describe how to download the installation files on demand as well as recover a lost product key for Office.
 

 

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.

Oct 24

How to Restore “Shrink One Page” in Word 2010 and 2013

by David Ringstrom,CPA

 

Long-time Word users may recall a handy feature in Word 2007 and earlier called Shrink One Page. This highly useful feature enables you to condense the font size of your document just enough so that you don't end up with a mostly blank page at the end. This feature may appear to have vanished from Word 2010 and 2013, but you can easily resurrect it.

 

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.