Tag: Excel

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

 

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

 

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

 

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

 

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

 

 

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.

 

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.

 

Jan 09

Hide and Unhide Multiple Excel Worksheets with Ease

By David Ringstrom, CPA



It’s easy to hide worksheets in Excel, but unhiding multiple worksheets within a given workbook can be a tedious exercise. Users who don’t know otherwise are relegated to unhiding worksheets one at a time. In this article I’ll explain a technique that allows you to hide and unhide multiple worksheets at once.


There are a couple of different ways to hide worksheets in Excel. For instance, carry out these steps to hide a single worksheet:
  • Excel 2007 and later: As shown in Figure 1, on the Home tab choose Format, Hide and Unhide, and then Hide Sheet. Or, to save a couple of steps, right-click on the worksheet tab and then choose Hide.
  • Excel 2003 and earlier: Choose Format, Sheet, Hide.
  • Excel 2011 (Mac): Ctrl-left-click on a worksheet tab and choose Hide from the shortcut menu.

Figure 1: Right-clicking on a worksheet tab provides a faster means for hiding worksheets.


You can also hide multiple sheets at once. To do so, you’ll first group the sheets. In any version of Excel, hold down the Ctrl key as you click on individual worksheet tabs and then carry out the aforementioned steps. Or you can hide several contiguous sheets:
  • Click on the first worksheet tab and then hold down the Shift key as you click on the last worksheet tab within the group that you wish to hide.
  • Hide the sheets as discussed above.
Now that your sheets are hidden, making them visible again calls for repetitive action, as every version of Excel requires you to unhide worksheets one at a time:
  • Excel 2007 and later: On the Home tab, you can choose Format, Hide and Unhide, Unhide Sheet, and then unhide a single sheet. You must repeat this action for each worksheet that you wish to unhide. You can save a couple of steps by right-clicking on a visible worksheet tab and choose Unhide.
  • Excel 2003 and earlier: From any worksheet tab, choose Format, Sheet, and then Unhide. Select the sheet you wish to display and then click OK. Repeat as needed for each worksheet you wish to unhide.
  • Excel 2011 (Mac): Ctrl-left-click on a worksheet tab and choose Unhide from the shortcut menu. Select the sheet you wish to display and then click OK. Repeat as needed for each worksheet you wish to unhide.

Fortunately, the tedium of unhiding worksheets one at a time can be eliminated by use of Excel’s Custom Views feature. Think of Custom Views as snapshots of workbook settings – such as the hidden or visible status of individual worksheets – that you can toggle at will. To use this feature, make sure that all worksheets are visible and then then carry out these steps:
In all versions of Excel:
  • Choose Custom Views on the View tab or menu.
  • Click Add, and then type a name for your custom view, such as All Sheets, and then click OK.
  • Next, hide any worksheets as needed and then create a second view titled Presentation View, or a name of your choosing.
Figure 3: Create a baseline view that shows all worksheets before you hide any worksheets.

Going forward, you can toggle the view by selecting the Custom Views command and then double-clicking the view of your choice, or click once on the view and then click Show as indicated in Figure 4.
This article only scratches the surface of what’s possible with Custom Views. I’ll explore this feature further in future articles.
Figure 4: A custom view allows you to unhide multiple worksheets with just a couple of mouse clicks.



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