Category: AccountingWEB Excel articles

My Quixotic Pursuit of Microsoft Excel for the Android Platform

by David Ringstrom,CPA
 
In a move that I can’t imagine occurring during Steve Ballmer’s reign, Microsoft has released Android versions of Microsoft Excel. I don’t have an Android phone, or the inclination to procure one, but I did want to try out the tablet version. On the Apple platform the latest versions of Excel for iPhone and iPad are surprisingly usable. Comparatively, the early iterations of Excel for iPhone were laughably unusable, but the iPad version hit the ground strong. It was easy for me to install Excel on my Apple devices, but I almost gave up trying to get access to Excel for Android.
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.

Use Excel to File Your 2014 Form 1040 and Related Schedules

by David Ringstrom,CPA
 
The brinksmanship between Congress and the Internal Revenue Service seems to ratchet up every year with the ever-present “will they or won’t they” approach to tax legislation. Yet amidst the annual chaos there is one rock-steady constant: the Excel-based version of Excel 1040 published by Glenn Reeves of Burlington, Kansas. This filing season marks his eighteenth spreadsheet-based version of the U.S. Individual Income Tax Return. Citizen Reeves continues to help his fellow taxpayers who wish to avoid the tedium of paper-based filing, as well as the chaos inflicted by online tax form providers changing their prices and features unexpectedly.
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.

How You Can Determine The First Sunday in a Month in Excel

by David Ringstrom,CPA
 
I was recently asked if there was an easy way to determine the first Sunday in a month. As you might expect, it can be tedious to manually determine the first Sunday of each month in an expense report spreadsheet. But you can do it more automatically: It’s a simple matter of using the WEEKDAY and CHOOSE functions together.
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.

Mastering the Conversion of Minutes to Hours/Minutes in Excel

by David Ringstrom,CPA
 
Working with time values in Excel often gives users fits. This is because Excel stores times as a decimal portion of a day. Thus 12 hours is the same as 0.5. Three and a half-hours is represented as 0.145833333333333. Understanding this decimal concept along with Excel’s time-based number formats will help you whip time-based values into shape.
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.

Freezing a Middle Row in Excel While Scrolling Above

by David Ringstrom,CPA
 
Reader Fiona posed an interesting question on my article regarding taking the pain out of freezing panes in Excel. She asked: “I want to freeze a row in the middle of a sheet but have the rows above it and below it to scroll together. Is this possible?” It is indeed, which I'll demonstrate in this 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.

Keyboard Shortcuts for Saving & Emailing Spreadsheets as PDF

by David Ringstrom,CPA
 
Saving Excel spreadsheets as PDF files has gotten incrementally easier over the years, but can still require more effort than necessary. When saving a document as a PDF, many users go through several mouse clicks on the File menu instead of choosing PDF as the file type in Excel's Save As dialog box. However, this technique can be streamlined even further with a single mouse-click or keyboard shortcut. Emailing a spreadsheet as a PDF attachment can be simplified in the same fashion, as I'll explain in this 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.

Timesaving File Menu Tricks in Excel 2010-2013

by David Ringstrom,CPA
 
You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted onto it (but you can fix that). I feel that we lost ground in Excel 2013 because frequent actions like Opening and Saving files require extra mouse clicks that result in additional wear-and-tear on your wrists. In this article I'll share a few tricks you can use to take back a tiny portion of your day.
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.

Microsoft Adds Key Features to Excel for iPad

by David Ringstrom,CPA
 
Earlier this year I wrote about my mixed feelings on Excel for iPad's debut. My closing sentence for that article read “The early iterations of Microsoft products tend to have rough edges that get smoothed out with time.” I'm no Nostradamus, but my instincts were correct, as in the intervening months Microsoft has added some key elements to Excel for iPad.
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.

When Excel Dates Mysteriously Shift by 4 Years

by David Ringstrom,CPA
 
If you've attended any of my free High Impact Excel webinars then you've probably heard me say that Excel is fraught with nuances. Sometimes the most mundane tasks, such as copying and pasting a series of dates from one workbook to another, can send you down a mysterious rabbit hole where things turn both curious and frustrating. In this article I'll explain why sometimes Excel dates may mysteriously change by 4 years, and to be more specific, 4 years and 1 day.
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.

Exploring the Nuances of Excel’s SUM Function

by David Ringstrom,CPA
 
My unscientific observation is that the SUM function is likely the most used—and abused—of all worksheet functions in Excel. Although the SUM function is far better than manually adding cells together (such as =B2+B3+B4+B5+B6+B7+B8), in this article I'll explore a couple nuances related to this venerable function.
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.

Maximizing Excel’s Alt-Down Arrow Keyboard Shortcut

by David Ringstrom,CPA
 
Alt-Down Arrow is one of my favorite keyboard shortcuts in Excel. First off, it gives me an easy means to speed through repetitive data entry within a column. Second, it allows me to access Excel's Filter arrows as well as Data Validation lists without using my mouse. I'll run through all three techniques in this 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.

Embed Frequently Used Lists into Excel

by David Ringstrom,CPA
 
As you're most likely aware, it's easy to instantly create a list of days of the week or months of the year. Just type either the first three letters or the full name of a day or month in a worksheet cell, and then drag the Fill Handle across a row or down a column. What you may not be aware of, is that this is part of the Custom Lists feature in Excel, and that you can add your own lists to use in the same fashion, as I recently noted in our AccountingWEB forum.
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.

Creating an Equivalent to the MAXIF Function

by David Ringstrom,CPA
 
Sometimes in Excel you may want to perform an analysis based on specific criteria. For instance, if I wanted to sum only the data in Figure 1 based on the month being equal to January, SUMIF would work. I could use COUNTIF to count the number of transactions in January, or in Excel 2007 and later use the AVERAGEIF function to average January's sales. However, what if I want to determine the largest amount for just the month of January? Although there's not a ready-made function, like MAXIF, I can easily adapt the MAX function to accomplish this.
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.

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

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