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:

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.

Excel Charts Books

John Walkenbach created Excel Charts for Excel 2007 and Excel 2003.

Bill Jelen created Pivot Table Data Crunching for Excel 2013Excel 2010.

Excel Pivot Tables and Pivot Charts Books

Bill Jelen and Michael Alexander created Pivot Table Data Crunching for Excel 2013Excel 2010Excel 2007, and Excel 2003.

Paul McFedries created Excel PivotTables and PivotCharts for Excel 2010, Excel 2007, and Excel 2003.

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

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

 

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

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

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.

 

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 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.

 

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

 

 

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

 

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

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

 

 

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

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.

 

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.

 

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.

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.

 

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