Author's posts
Apr 07
Accounting Class Extra Credit Project
Welcome to the extra credit project for your accounting class. The project entails using this comma-separated value file to create an interactive financial statement in Microsoft Excel. The techniques are designed to help you use Excel more effectively. You may watch the video and download the handouts at no charge. An optional $1.99 grading fee is required should you choose to submit your completed spreadsheet for grading and extra credit for your accounting class.
Supporting Materials:
- Comma-Separated Value file for use in building your spreadsheet
- PDF version of the presentation, along with some Excel technique examples
Project Parameters
Watch the video and create the spreadsheet shown on screen by using the comma-separated value file available above. To receive extra credit you must submit your completed spreadsheet below and pay a $1.99 grading fee.
[S3VIDEO file=’Kennesaw/intcont1.mp4′ width=’608′ height=’461′ bgimage=’https://www.davidringstrom.com/images/445-210.jpg’]Apr 01
Excel Charts Books
John Walkenbach created Excel Charts for Excel 2007 and Excel 2003.
Bill Jelen created Pivot Table Data Crunching for Excel 2013, Excel 2010.
Mar 31
Excel Pivot Tables and Pivot Charts Books
Bill Jelen and Michael Alexander created Pivot Table Data Crunching for Excel 2013, Excel 2010, Excel 2007, and Excel 2003.
Paul McFedries created Excel PivotTables and PivotCharts for Excel 2010, Excel 2007, and Excel 2003.
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 27
Excel-lent April Fool’s Day Pranks
by David Ringstrom, CPA
You know the feeling—some days Excel totally has a mind of its own. Fortunately rebooting your computer will get Excel back in line again. If not, your next course of action is to install the latest service packs for Excel 2013, Excel 2010, Excel 2007, or Excel 2003 (act fast if you’re still using this version, support ends April 8, 2014 and you’re only a decade behind).
With that useful information out of the way, let’s see how you can punk your coworkers by making subtle changes to their Excel settings. No, you’re not wasting company time, you’re testing your colleague’s Excel moxy. Feel free to charge prank time as “training” if necessary.
Continue reading article at www.goingconcern.com
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 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.
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 19
CSV Secrets: QuickBooks Reports to Excel
by David Ringstrom
While most users export QuickBooks reports as Microsoft Excel workbooks, I often use the comma-separated value (CSV) format instead. Doing so strips extraneous formatting from the report so that I can immediately filter a report or analyze it with a pivot table. In this article, I’ll show you how to use the CSV format to create Excel workbooks that automatically update themselves, along with any helper formulas you may wish to add alongside your QuickBooks data. In future articles, I’ll share techniques you can use to capitalize on this functionality.
See www.sleeter.com to read the rest of the article.
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 19
Use Excel Your Way – Part 1
by David Ringstrom,CPA
If you’re like most users, your Excel options are as pristine as the day Excel was installed on your computer. This is akin to buying a car and never adjusting the position of the driver’s seat. Now, I hear that Caleb likes having his knees near his chin when he drives, but hopefully someday he’ll look for that little lever that will let him customize his car just a bit.
Here’s how to find your “seat adjustor” in the desktop versions of Excel:
- Excel 2010/2013: Choose File, and then Options.
- Excel 2007: Click the round button in the top-left corner of your screen known as the Office button, and then choose Excel Options.
- Excel 2003: Really? You’re reading Going Concern and you’re still on Excel 2003? Fair enough. In your case, choose Tools, and then Options.
- Excel for Mac 2011: Choose Excel, and then Preferences.
See www.goingconcern to read the rest of the article.
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 19
Merging QuickBook Reports Using Excel VLOOKUP
by David Ringstrom, CPA
Generating reports in QuickBooks is generally quick and easy – until you hit on a specialized need. Certain reports allow you to add or remove columns of data, but sometimes a key piece of data you need won’t be available on a given report. For instance, the Inventory Stock Status by Vendor report shows you which items you need to reorder, but it doesn’t provide the ability to add the vendor’s contact information. In this article, I’ll demonstrate how you can use Excel VLOOKUP to merge QuickBooks reports data. I’ll also show how Excel’s HYPERLINK function can generate clickable links to your email software for sending new messages.
Continue reading article at www.sleeter.com
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 19
Become a Master of Excel From the Master of Excel
by David Ringstrom,CPA
Acknowledging a weakness is the first step toward converting deficits into strengths. Most likely the two Excel classes you took two or three years ago left you with only a rudimentary sense of what one can do with spreadsheets. The risks and opportunities in Excel lie in discovering its nuances. That’s why I coined the phrase “Either you work Excel, or it works you.” The vast majority of spreadsheet users fall into the latter category.
You can turn the tide, but note that Malcolm Gladwell says it takes 10,000 hours to become an expert. James Altucher says it takes five years to reinvent yourself, which is in effect your goal. Others beg to differ. No matter who’s right, you’re clearly going to need some serious keyboard time if you want to pwn Excel. Between now and this fall, carry out every imaginable life task in Excel:
Make a grocery list
Track your workouts
Maintain a reading list
Build a personal budget
Plot your retirement
See the complete article on www.goingconcern.com
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
- 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 .
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
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
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
