Author's posts
Nov 08
Microsoft Office moves into the Cloud
By David H. Ringstrom, CPA
- Office Web Apps
- Exchange Online, including 25 GB mailboxes, and the ability to send 25 MB attachments
- SharePoint Online
- Lync Online
- Support provided via a moderated community forum
- Office Professional desktop software
- Office Web Apps
- Exchange Online, including 25 GB mailboxes, and the ability to send 25 MB attachments
- Sharepoint Online, including Forms, Access, Visio, and Excel services
- Lync Online
- 24/7 IT-level phone support
- Financially-backed 99.9% uptime service, or, in other words, downtime of less than 9 hours per year
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
Sep 30
UPDATE: BlackBerry PlayBook means business
By David Ringstrom
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
Sep 13
New spam attack attempts to disable security software
By David Ringstrom
A virulent new e-mail threat is causing significant problems for large businesses and individuals. The infected e-mail messages entice recipients to click on a hyperlink to either a PDF or Windows Media (WMF) file. Doing so allows a worm to simultaneously exploit an infected user’s e-mail address book and attempt to disable the user’s antivirus software.
- W32/VBMania@MM
- W32.Imsolk.B@mm
- W32/Autorun-BHO
- WORM_MEYLME.B
- Visal.B
Hello:
This is The Document I told you about,you can find it Here.
http://www.sharedocuments.com/library/PDF_Document21.025542010.pdf
Hello:
This is The Free Dowload Sex Movies,you can find it Here.
http://www.sharemovies.com/library/SEX21.025542010.wmv
- McAfee
- Microsoft
- Symantec (Norton Antivirus)
- TrendMicro
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
Sep 09
N() Function
The N() function allows you to embed notes within formulas, e.g. =SUM(A1:A5)+N(“Sums rows 1 through 5”). N evaluates to zero.
Jul 30
The Making of a Great Webcast Speaker
It goes without saying that if you are going to teach continuing education, you have to know your stuff. But the requirements of a good presentation go well beyond having knowledge of your subject—especially in an online presentation.
Click here to read the full text of the article at AccountingWEB.com.
Jul 27
Moving Columns Trick
To move selected columns, hold the Shift down while you move the edge of your selection with left mouse.
Jul 23
Undo Comparision
Excel 2007/10 tip: Press Ctrl-Z repeatedly to undo your last 100+ actions. Even better, you can still undo actions after you save your file. In Excel 2003 and earlier you can only undo the last 16 actions, and saving your file clears the undo stack. You cannot undo renaming a worksheet name or deleting a worksheet in any version of Excel
Jul 06
Start-up company aims to simplify small business cash flow management
By David H. Ringstrom
- The Solo plan is aimed at new entrepreneurs with up to 50 monthly transactions.
- The Small Business plan allows up to 500 monthly transactions for $29.95/month.
- The Enterprise plan allows unlimited transactions for $99.95/month.
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
Jun 17
What to do when Excel won’t let you insert columns
By David H. Ringstrom, CPA
A previous version of this article first appeared on 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.
Jun 16
Office 2010 has arrived! Customize your ribbon!
By David H. 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
Jun 08
Two ways to determine your top customers in QuickBooks, Excel
By David H. Ringstrom, CPA
Given the length of the Great Recession, your top customers today might not be the same folks who were your top customers even a couple months ago. In this article I’ll demonstrate two ways that you can determine who your top customers are based on total sales.
The first technique involves a minor tweak to an existing QuickBooks report, while the second demonstrates a couple of tricks that I use to transform QuickBooks reports into usable data in Excel.
To quickly determine your top customers for a given time period, follow these steps:
1. In QuickBooks, choose Reports, Sales, and then Sales by Customer Summary.
2. When the report appears onscreen, change the report dates to display the period that you wish to see, and then click Refresh, as shown in Figure 1.
Figure 1: Modify the dates of the Sales By Customer Summary to display the report period in question.
3. Click the Modify Report button that appears at the top of the report window.
4. In the Modify Report window, change Sort By to Total, instead of Default, as shown in Figure 2.
Figure 2: Change the Sort By to Total, instead of Default.
5. Click OK to display the modified report on the screen.
6. To save this report for future use, click the Memorize button that appears at the top of the report window. Assign a name, such as Top Customers, and then optionally choose a Memorized Report Group. Going forward, you can access your report by choosing Reports, Memorized Reports, and then looking for your report title.
This approach is simple enough, but doesn’t allow you to limit the report to a particular subset, say your top 10, 25, or 100 customers. Fortunately it’s easy to do this with Excel:
1. In QuickBooks, choose Reports, Sales, and then Sales by Customer Summary.
2. When the report appears onscreen, change the report dates to display the period that you wish to see, and then click Refresh, as shown in Figure 1.
3. Click the Export button at the top of the report screen, choose New Excel workbook, and then click Export.
4. The total sales for each customer are a =SUM formula that will get corrupted by a subsequent process that we’re going to do to this report, so we must convert the totals to values:
a. Right-click on the sales figure column (for instance, column D in Figure 3), choose Copy to copy the entire column to the clipboard.
Figure 3: Convert the sales figures to numbers instead of formulas.
b. Right-click again on the sales figure column, choose Paste Special, Values, and then click OK.
5. Click on cell A1, and then press Ctrl-* to select the entire report. Use the * key on the number pad of your keyboard, or press Ctrl-Shift-8.
6. Turn on the filter arrows:
- Excel 2007/2010: Choose Sort & Filter in the Editing section of the ribbon, and then choose Filter.
- Excel 2003 or earlier: Choose Data, Filter, and then AutoFilter.
7. Click the filter arrow in cell B1 (as shown in Figure 4), and then choose Text Filters, and then Custom (Users in Excel 2003 and earlier can just choose Custom).
Figure 4: Be sure to use the filter arrow in cell B1.
8. As shown in Figure 5, choose Does Not Equal, enter Total*, and then click OK.
Figure 5: Choosing Does Not Equal and Total* is the same as saying “does not begin with”
9. Use the worksheet frame to select all visible rows from row 2 through the end of the report, and then right-click on any of the rows you selected and then choose Delete, as shown in Figure 4.
10. Click the arrow in cell B1, and then choose All in Excel 2003 and earlier or click Select All in Excel 2007/2010.
11. Delete any blank columns, so that you’re left with one column with your customer names, and a second column with the sales amounts.
12. The next step is to eliminate the word Total from the start of each customer row:
a. Click on column A on the worksheet frame to select the entire column.
b. Choose Data, and then Text to Columns.
c. When the Text to Columns wizard appears, choose Fixed Width, and then click Next.
d. Excel automatically guesses that we want to eliminate the word Total, so click Next again.
e. As shown in Figure 6, click on the Total column, and then choose Do Not Import (skip), and then click Finish.
Figure 6: The Text To Columns wizard allows you to delete the word Total from the start of each customer name.
3. You can now use the Top 10 AutoFilter feature to display a selected range of customers. To do so, click the arrow in at the top of the column that contains the sales figures. Next, choose Top 10 in Excel 2003 and earlier, or Number Filters, and then Top 10 in Excel 2007/2010. As you can see in Figure 7, although the feature is “Top 10”, you can really display the top number of your choice. The final report format is shown in Figure 8.
Figure 7: The Top 10 feature in Excel actually allows you to filter any number of customers you choose.
Figure 8: The final report format generated by using Excel’s Top 10 filtering feature.
A previous version of this article first appeared on 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 24
Transform multiple-line cell contents into columns in Excel
By David H. 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
Mar 23
Some unlikely uses for Excel’s SUMPRODUCT function
By David H. Ringstrom, CPA
- lookup_value – This is the data that you want to look for in the first column of the table array.
- table array – The table array is a range of two or more columns.
- col_index_num – This argument allows you to specify the column within the table array for which you want to return data.
- range_lookup – In this position you indicate FALSE if you’re seeking an exact match for the lookup_value, or TRUE if you want an approximate match. For instance, you’d use FALSE to look up the price of an inventory item, or TRUE if you’re determining which tax bracket an income level falls into.
- range – This is a column or row where you want to look for specified criteria.
- criteria – This is the same as the lookup_value for VLOOKUP.
- sum_range – This is a column or row from which you want to add up numbers whenever the criteria is found in the range.
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 23
What’s the FREQUENCY? Using Excel’s FREQUENCY function
By David H. Ringstrom, CPA
- data_array – a range of cells containing numeric values
- bins_array –a range of cells containing bins into which the numeric values should be grouped
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 23
Excel Tip: Converting numbers formatted as <1> to -1
By David H. Ringstrom, CPA
- Select the range of cells that contain numbers surrounded by < >. It doesn’t matter if you select cells that have numbers without brackets, but it’s helpful to limit the area that you’re searching to avoid unintended replacements.
- In any version of Excel, press Ctrl-H to display the Find and Replace dialog box shown in Figure 1.
- As shown in Figure 2:
- IF – The IF function allows us to carry out a true/false test, return a result, or perform a calculation based on the result of the test. IF has three arguments: logical_test, value_if_true, and value_if_false.
- ISNUMBER – The ISNUMBER function allows us to test whether a cell contains a number. This function has a single value argument, and returns TRUE if a cell contains a number or FALSE if it contains text or is blank.
- VALUE – The VALUE function converts a number stored as text into a value, and has a single text argument.
- MID – The MID function allows us to extract text from the middle of a string, which is another way to refer to text within a cell. This function has three arguments: text, start_num, and num_chars.
- LEN – The LEN function returns the length of text within a cell, and has a single text argument.
- logical_test: the ISNUMBER function determines if the data in Cell A1 is a number or not.
- value_if_true: If ISNUMBER returns true, then Cell A1 contains a number, so I’ll simply return that value.
- value_if_false: If ISNUMBER returns false, I know that I have a number surrounded by < > that I wish to remove. In this case I’ll use the VALUE and MID functions together.
- text – In this case, I refer to Cell A1 for the text I wish to shorten
- start_num – I know that I want to eliminate the starting < character, so I instruct MID to start at the second position
- num_char – I also want to eliminate the trailing >, so I’ll use the LEN function to determine how long the text in Cell A1 is, and then subtract 2 from that number. Thus LEN(A1)-2 would return 1 for Cell A1. LEN(A3)-2 returns 2, and LEN(A5,2) returns 5 (the comma counts as a character).
- In any version of Excel, press Ctrl-F1 to display the Format Cells dialog box.
- Choose Custom from the Number tab.
- Erase the Type field, and enter this format code, as shown in Figure 4:
A previous version of this article first appeared on 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