Tag Archive: AccountingWEB

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

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@nullacctadv.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@nullacctadv.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@nullacctadv.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 10

Identifying Largest and Smallest Values in an Excel List

By David Ringstrom, CPA



Periodically, you may wish to rank a series of items within an Excel spreadsheet. Many users often rely on sorting data in ascending or descending order. I'll describe an alternative that uses the LARGE and SMALL functions to create an ordered list of whatever you'd like to rank.


You're probably already familiar with the MIN and MAX functions in Excel, which return the smallest or largest value within a list, respectively. As shown in Figure 1, MIN indicates the smallest value is 191, and the largest value is 958. MIN and MAX are limited to the single smallest or largest values respectively, but LARGE and SMALL allow you to return the second largest or third smallest value if you choose. I'll explain these functions in a moment, but first let's explore MIN and MAX.


Figure 1: MIN and MAX return the largest and smallest values from a list, respectively.



As shown in Figure 1, the MIN and MAX functions are similar in nature to the venerable SUM function, except they return the single smallest or largest value, respectively, instead of adding up values. The SMALL and LARGE functions work in a similar fashion, but with an extra argument:


=SMALL(array,k)
=LARGE(array,k)


In these functions, array is a range of cells, and k is the nth value you wish to return. As shown in Figure 2, =LARGE(B2:B11,2) would return 872 as the second largest value, while =SMALL(B2:B11,3) would return 266 as the third smallest value. By way of comparison, the following formulas would both return 958 and 191 for the largest and smallest values, respectively:


=MIN(B2:B7)


=SMALL(B2:B7,1)


=MAX(B2:B7)


=LARGE(B2:B7,1)


Figure 2:  LARGE and SMALL return the nth values from a given list.



If you're creating a list of the top or bottom 10 values, it can be tedious to manually edit each LARGE or SMALL function with the proper value for the k argument. To save time, I use the ROW function, either inside the LARGE or SMALL function or in a separate column. The ROW function returns the row number for a given cell. If you enter this in cell D2, Excel will return 2:


=ROW()


In Figure 3 you can see that I entered this formula in cell D2:


=ROW()-1


In this case, ROW() would return 2 because it's entered on the second row, so subtracting 1 changes the result to 1. Alternatively, I could provide the address of a cell in row 1 of the worksheet:


=ROW(D1)


Figure 3: These formulas are the basis for creating a ranked list without re-sorting the source data



In cell F2, I entered this formula:


=LARGE(B$2: B$11,D2)


Depending upon my needs, I might have used this formula instead:


=LARGE(B$2: B$11,ROW()-1)


The dollar signs in the formula instruct Excel not to change the row numbers when I copy the formula down. The last bit of information that you'll likely want is to associate a name with the values that you've isolated. To do so, you can use the MATCH and INDEX functions together in cell E2:


=INDEX(A$2:A$11,MATCH(F2,B$2:B$11,0))


I'll explain MATCH and INDEX in more detail in an upcoming article, but for now the short answer is that in this case MATCH is determining which row a sales figure amount is on, and then INDEX returns the corresponding text from column A. This is akin to VLOOKUP, but with the flexibility of being able to look up data from the left, which VLOOKUP can't do without making a special provision.


There's one caveat to this approach that you should be aware of. If the same value is on your list twice, then MATCH/INDEX will return the corresponding name twice. Next week I'll describe how you can use the COUNTIF function to create a tiebreaker that will give you a unique value to match for each item in the list.


In any case, once I have the formulas in cells D2 through F2 in place, I'm able to copy the formulas down as many rows as needed without any additional modifications, as shown in Figure 4. Do keep in mind that if you drag too far, LARGE or SMALL will return #NUM!.


Figure 4: If you drag the formulas in cells D2:F2 too far, LARGE will return #NUM!.



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

Jun 01

Automating Excel Charts in Two Keystrokes

By David H. Ringstrom, CPA


Readers of a certain age will remember a television game show in which contestants would state “I can name that tune in five notes,” or some such number of notes. Along those lines, I'll show you how to automate an Excel chart with just two keystrokes. I'll first show you the typical way that most users accomplish adding data to a chart, and then I'll share the simple technique that eliminates this mundane need.



Let's say that you maintain a monthly chart, such as the one in Figure 1, where you periodically add another column of data. In Excel 2007 and 2010, most users carry out these steps to expand the chart:
Figure 1: Most users manually expand charts like this one.
  • Add a new column of data, such as for June.
  • Click once on the chart, and then choose Select Data from the Design tab of the ribbon.
  • As shown in Figure 2, change the Source Data range to be $A$1:$G$3, and then click OK. The month of June should then appear on the chart. Repeat this action month after month.
Figure 2: Steps involved in manually expanding a chart in Excel 2007 and later.



Power user tip: Typically pressing an arrow key within the Source Data field inserts an extraneous cell address. Press F2 to toggle to Edit mode, which means you can use the arrow keys to navigate within the field.



Alternatively, you can let the Select Data button molder. In Excel 2007 and 2010, click any cell within your chart's source data, press Ctrl-T, and then press Enter. Add a new month of data into the worksheet, and you'll see that the chart expands itself automatically, as shown in Figure 3.



Figure 3: Charts based on tables expand automatically.



Ctrl-T is the keyboard shortcut for the Table feature, which first appeared in Excel 2007. It's actually a revamp of the List feature that has long existed on the Data menu of earlier versions of Excel. Charts automatically adopt the dynamic nature of tables, which expand automatically when you add columns or rows of data.
Unfortunately this technique isn't available in Excel 2003 and earlier. The predecessor List feature doesn't interact with charts in the same fashion as the Table feature in Excel 2007 and later. However, if you're using a current version of Excel, you can use this keystroke process to instantly automate your existing charts.




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