Category: AccountingWEB articles

Aug 17

Microsoft to Fully Embrace Open Document Standards

By David H. Ringstrom


After years of hegemony, Microsoft has finally acknowledged that the world wants to be able to save documents in open formats that are freely shareable with other software applications.
From the beginning, Microsoft Office users have been generally required to save documents in proprietary formats, such as .XLS or .DOC, that couldn't always be opened in competing productivity suites. These limitations will fall way when Microsoft Office 2013 is released.


Three new document format options will appear in Office 2013:
  1. Strict Open XML – an ISO standard that offers maximum cross-platform flexibility.
  2. ODF 1.2 (Open Document Format), the document standard utilized by free, open-source applications such as Apache OpenOffice.
  3. The ability to open PDF documents in Word, make edits, and then save the document back as a PDF – or any other document format.


It's presently sometimes difficult or impossible to use certain Office documents in other software programs. Over the years, Microsoft has made overtures to open document formats by including transitional document formats available in Office 2007 and 2010. However, this has meant that documents couldn't necessarily make a round-trip between say Microsoft Excel and other spreadsheet programs without a loss of functionality.


Long-term Office users have become accustomed to working around the restrictions of Microsoft's proprietary document formats. For instance, Word users can save documents in Rich Text Format, which most word processing programs can open without issue. However, complex Excel documents could often only be opened and edited cleanly in Microsoft Excel.  A free document viewer  was the lone olive branch offered to anyone unwilling or unable to purchase Microsoft Office.


Microsoft also long resisted the PDF format, to the extent that it launched a competing XPS document format that failed to gain traction in the marketplace. Microsoft Office 2007 haphazardly introduced the ability to save documents in PDF format, in that users had to manually download a software update. PDF capabilities were formally adopted in Office 2007 when Microsoft issued the Service Pack 2 update. Office 2010 shipped with native functionality for saving documents in PDF format, but Office 2013 builds on this by empowering users to both edit and save documents in PDF format.


This sea change was announced by Microsoft in a recent blog post, which includes a useful chart that documents Microsoft's gradual shift in document format availability over the past decade.



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

 

Aug 14

Excel Tip: Calculating Interest

By David H. Ringstrom, CPA


Microsoft Excel is an ideal tool for calculating the cost of borrowing money, but are you doing so in the most efficient manner? In this article, I'll describe how you can use the CUMIPMT function to calculate interest expense for a loan, whether for a month, a year, or the length of the loan – all within a single worksheet cell. I'll also show you how to add an amortization schedule to any workbook with just a couple of mouse clicks.


Before I describe the CUMIPMT function, let's first take a look at the PMT function, which calculates the payment amount for a loan. PMT has 3 required and 2 optional arguments:


rate – The interest rate for the loan expressed as a monthly rate.
nper – The length of the loan in months.
pv – The amount being borrowed, also referred to as the present value.
fv – This optional argument allows you to specify a future value if a balloon amount is due at the end of the loan. Omitting this argument implicitly states that the loan is to be paid down to 0.
type – This optional argument allows you to specify if payments are made at the beginning of each period, or you can omit the argument to indicate that payments are made at the end of each period. You may also specify 0 in this position to explicitly indicate that payments are made at the end of each period.


Figure 1: The PMT function calculates the monthly payment for a loan.



As shown in Figure 1, a monthly payment of $586.04 for 36 months is required to pay back $20,000 at an interest rate of 3.5 percent. The PMT function always returns a negative amount because Excel sees the payment as an outflow. I omitted the 2 optional arguments, so in this case, the PMT function assumes the loan is paid to 0 and payments are made at the end of each period.


You construct formulas using CUMIPMT in a similar fashion to PMT, but in this case, all 6 arguments are required:
rate – The interest rate for the loan expressed as a monthly rate.
nper – The length of the loan in months.
pv – The amount being borrowed, also referred to as the present value.
start_period – The starting month from which to calculate interest on the loan. Use 1 to calculate interest from the start of the loan, or 13 to calculate interest for just the second year of the loan.
end_period – The ending month through which to calculate interest on the loan. Use the same value as the nper argument to calculate interest for the life of the loan, or 24 to calculate interest for just the second year of the loan.
type – Specify 0 to indicate that payments are made at the end of the period, or 1 for payments made at the start of the period.


Figure 2: The CUMIPMT function computes total interest for all or part of a loan.



As shown in Figure 2, the CUMIPMT function shows that borrowing $20,000 at 3.5 percent for 36 months will cost $1,097.50. Although not shown, the cost for the second year of the loan is $368.55. To calculate the principal paid back for a given portion of the loan, use CUMPRINC, which utilizes the same sequence of arguments as CUMIPMT.


As you can see, it's possible to use worksheet functions to calculate interest and principal for all or part of a loan without building out a full-scale amortization schedule. However, when one is needed, right-click on any worksheet tab and then choose Insert, as shown in Figure 3. Click on the Spreadsheet Solutions tab, and then double-click on Amortization Schedule.


Figure 3: Add an amortization table to any workbook with just four 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

Aug 03

Managing Excel Add-ins

By David H. Ringstrom, CPA


Many programs interface indirectly with Microsoft Excel, but some offer built-in functionality that you may not need or want. In other cases, an add-in that you rely on may suddenly vanish from Excel. In this article I'll explain how you can manage these features.


Excel has long offered the ability for add-ins. For instance, Adobe Acrobat often helpfully adds a separate toolbar or ribbon tab. However, Excel 2007 and 2010 offer built-in PDF printing capability, so do you really need another handful of commands to create PDFs? Certain versions of Sage 50 (formerly Peachtree Accounting) and QuickBooks add extra ribbon tabs to Excel as well. This is great if you need these tools, but unwanted add-ins can cause Excel to take longer to launch, plus your Excel interface can become unwieldy, as shown in Figure 1.


Figure 1: Add-ins sometimes add extra menu commands to Excel's user interface


Here's how to manage add-ins in Excel 2010:
  1. As shown in Figure 2, click on File, Options, and then Add-ins.
  2. Click the drop-down at the bottom and choose COM Add-ins, and then click Go.
  3. Enable or disable add-ins as needed, and then click OK as needed.
The aforementioned steps should handle most add-ins that surreptitiously appear in Excel, but another place to check is the Excel Add-ins list in step 2 above.
The steps are the same for Excel 2007, except you'll click the Office button in the top left-hand corner, and then choose Excel options. From there, follow steps 3 through 9 in Figure 2. In Excel 2003 and earlier, click the Tools menu, choose Add-ins, and then make your selections.


Figure 2: Managing add-ins in Excel



From time to time, an add-in that you rely on may vanish from the Excel user interface or not function correctly. If Excel deems that the add-in has caused an issue, it will automatically place the add-in on a hidden disabled list. To determine if any add-ins have been disabled in Excel 2007 or 2010, follow steps 1 through 4 in Figure 2, and then choose Disabled Items in step 5. As shown in Figure 3, a prompt will appear from which you can enable any add-ins if necessary. In Excel 2003, click Help, About Microsoft Excel, and then click the Disabled Items button.


Figure 3: A hidden Disabled Items prompt allows you to restore an add-in's functionality



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

Jul 16

First Thoughts on Excel 2013

by David H. Ringstrom

 

The “customer preview,” or public beta version, of Office 2013 became available online today. I'll give you a quick read on what I found in my first spin through Excel 2013. I'll have much more to report in the coming months.



Microsoft hasn't yet confirmed a shipment date for the final release, but it's widely expected to be delivered late 2012 or early 2013.
As shown in Figure 1, the ribbon interface that was introduced in Excel 2007 is retained in Excel 2013. The user interface itself has been tweaked, with changes like blocky worksheet tabs and capitalized ribbon tab names ‒ e.g., “HOME” instead of “Home.”
Figure 1: Excel 2013's new user interface.



The File menu, shown in Figure 2, has been reworked as well.
Figure 2: Excel 2013's File menu.



At first glance, most new features seem to be focused on Cloud-based document storage and number crunching:
Quick Analysis – Right-click on a selection of cells to preview conditional formatting, charts, totals, tables, and sparklines. You can view each of these features on the fly or click the icon to apply the selected feature.
Flash Fill – This new feature on the data menu purports to generate patterns of data, but as of yet, I can't get it to produce any results.
Recommended Pivot Tables and Charts – These new features build pivot tables or charts on the fly from the source data that you select, saving you from having to build the features by hand.
Power View – This new add-in purportedly will “make better business decisions and create beautiful, interactive reports.”
Timeline – A feature for filtering dates interactively in pivot tables, pivot charts, and cubes.
Relationships – A new feature that will show how data from tables is linked together.



It's likely there are other changes beneath the hood, but the above items are what initially jumped out at me. This version of Office is tightly integrated with Microsoft's Cloud-based SkyDrive [4] storage platform, but documents can still be saved to a local disk.



Office 2013 will only be compatible with Windows 7 and later, which means Windows XP users will be relegated to Office 2010 or earlier. Although details are still emerging, Office 2013 should be compatible with touch screens, but you'll need to purchase a Windows-compatible tablet or PC to use this feature. As of today's press release, no iPad- or Android-compatible versions of Office 2013 are presently planned.
If you take Office 2013 for a spin, please feel free to post your comments and thoughts below.



 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

Jul 16

First Look at Excel 2013

By David H. Ringstrom


The “customer preview,” or public beta version, of Office 2013 became available online today. I'll give you a quick read on what I found in my first spin through Excel 2013. I'll have much more to report in the coming months.


Microsoft hasn't yet confirmed a shipment date for the final release, but it's widely expected to be delivered late 2012 or early 2013.
As shown in Figure 1, the ribbon interface that was introduced in Excel 2007 is retained in Excel 2013. The user interface itself has been tweaked, with changes like blocky worksheet tabs and capitalized ribbon tab names ‒ e.g., “HOME” instead of “Home.”


Figure 1: Excel 2013's new user interface.


The File menu, shown in Figure 2, has been reworked as well.
Figure 2: Excel 2013's File menu.


At first glance, most new features seem to be focused on Cloud-based document storage and number crunching:


Quick Analysis – Right-click on a selection of cells to preview conditional formatting, charts, totals, tables, and sparklines. You can view each of these features on the fly or click the icon to apply the selected feature.
Flash Fill – This new feature on the data menu purports to generate patterns of data, but as of yet, I can't get it to produce any results.
Recommended Pivot Tables and Charts – These new features build pivot tables or charts on the fly from the source data that you select, saving you from having to build the features by hand.
Power View – This new add-in purportedly will “make better business decisions and create beautiful, interactive reports.”
Timeline – A feature for filtering dates interactively in pivot tables, pivot charts, and cubes.
Relationships – A new feature that will show how data from tables is linked together.
It's likely there are other changes beneath the hood, but the above items are what initially jumped out at me. This version of Office is tightly integrated with Microsoft's Cloud-based SkyDrive storage platform, but documents can still be saved to a local disk.


Office 2013 will only be compatible with Windows 7 and later, which means Windows XP users will be relegated to Office 2010 or earlier. Although details are still emerging, Office 2013 should be compatible with touch screens, but you'll need to purchase a Windows-compatible tablet or PC to use this feature. As of today's press release, no iPad- or Android-compatible versions of Office 2013 are presently planned.
If you take Office 2013 for a spin, please feel free to post your comments and thoughts below.




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

Jul 13

Is This Excel Worksheet Protected?

By David H. Ringstrom, CPA


Long-term Excel users are familiar with the prompt shown in Figure 1. In Excel 2003 and earlier, there were two ways to determine if a worksheet was protected. One way involved attempting to modify a locked cell, which would trigger the aforementioned prompt. The second approach involved choosing Tools, Protection, and looking for the Unprotect Sheet command, as shown in Figure 2. Excel 2007 makes this determination a little easier, as the Review tab displays an Unprotect Sheet command, shown in Figure 3, when you activate a protected worksheet.
Figure 1: A typical prompt that appears in Excel when a user attempts to modify a locked cell on a protected worksheet.



Figure 2: Multiple clicks are required to unprotect worksheets in Excel 2003.



Figure 3: You can tell from a glance at the Review tab if a worksheet is protected in Excel 2007.



Regardless, in Excel 2007 and earlier, you're required to activate each sheet in turn before you can turn off worksheet protection. In Excel 2003, this can require significant mouse clicks, whereas Excel 2007 streamlines the process somewhat since you can keep the Review tab active. Fortunately, Excel 2010 users have a much easier approach at their disposal.
As shown in Figure 4, Excel 2010 users can choose File, Info, and then determine at a glance exactly which sheets within a workbook are protected. Users can then turn off protection via the Unprotect links.
Figure 4: The Info pane in Excel 2010 provides a list of protected worksheets.



Excel 2010 users can also utilize the Review tab as shown in Excel 2007, but keep the Info pane in mind should you need to turn off protection on multiple worksheets within a workbook.
If you're still using Excel 2003, there's a way you can eliminate a couple of steps involved with protecting or unprotecting worksheets by following the steps shown in Figure 5.
Figure 5: A toolbar customization in Excel 2003 can streamline worksheet protection tasks.



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

Jul 05

Excel Speed Tips: Do You Know the Tricks?

There is not one among us who would not benefit from using Excel more effectively.  When you discover helpful features (that were always there!) you can save significant time. Do you use the Table and Form features to easily sift through large data sets? Do you know the keyboard shortcuts that help you blaze through your work?  Do you avoid wrecking formulas by using the Edit versus Enter modes appropriately?

Click here to read the full text of the article at AccountingWEB.com.

Jun 28

Using Clip Art in Excel Charts

By David H. Ringstrom, CPA



Recently, I described how you can use Excel's Table feature to have charts expand automatically and to add a self-updating title (see “ReIated articles” below). I'll complete this three-part series by showing how you can enliven charts with clip art.
If you don't already have clip art you'd like to use, you can access thousands of free images on Office.com, such as an apple or an orange.



To create a chart in Excel 2007 and later, create a data set like the one shown in cells A1 through E3 of Figure 1. Next click on cell A1, choose Insert, Column, and then the first 2-D option. In Excel 2003, enter the data in cells A1 through E3, select cell A1, choose Insert, Chart, and then click Finish.
Figure 1: Creating a chart from a Table in Excel 2007 and later.



The next step is to associate a picture with a data series in your chart. As shown in Figure 2, in Excel 2007 and later, right-click on a data point or series, and then choose the corresponding Format command. Choose Fill, Picture, and then click the File button to choose an image. Click the Stack option, and then click Close.
Figure 2: Adding clip art to a chart in Excel 2007 and later.



In Excel 2003, right-click on a data point (or series), choose the corresponding Format command, and then click the Fill Effects button on the Patterns tab. Within the Fill Effects dialog box, click the Picture tab, select a picture, choose Stack, and then click OK twice.
Figure 3: Adding clip art to a chart in Excel 2003.



At this point your chart should look similar to Figure 4. Repeat the aforementioned steps for each data point or series in your chart, and the end result should look similar to Figure 5.
Figure 4: Chart with clip art added to one series.



Figure 5: Chart with clip art added to both series.




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

Jun 22

Automating Chart Titles

 

By David Ringstrom



Recently, I described how you can use Excel's Table feature to have charts expand automatically. In this article, I'll show how you can further automate your chart with a self-updating title.
To create a chart in Excel 2007 and later, create a data set like the one shown in cells A1 through E3. Next click on cell A1, choose Insert, Column, and then the first 2-D option, as shown in Figure 1.
Figure 1: Creating a chart from a Table in Excel 2007 and later.



The next step is to add a title to the chart. To do so, click once on the chart to select it, and then choose Layout, Chart Title, and then Above Chart, as shown in Figure 2. To manually change the title, you can click on the title field within the chart and edit the text as desired. However, we can use a formula to make the title dynamic instead.
Figure 2: Adding a title to the chart.



To automate your chart title, use two worksheet functions together:
  • COUNTA – This worksheet function returns the number of non-blank cells in a given range.
  • INDEX – This worksheet function returns data from cell coordinates that you provide.
Use COUNTA to determine the number of non-blank cells in row 1. This assumes you won't have any data to the right of your source data, shown in Figure 1. If you've entered four months of data, then COUNTA will return the number 5, because there will be five non-blank cells in the row. You'll then use the INDEX function to return the word “April.” To do so, you'll tell the INDEX function to look across row 1 of your worksheet, and return data from the nth cell, as provided by COUNTA. The formula will take this form:
=INDEX(1:1,COUNTA(1:1))



You may wish to add additional narrative, such as the word “Sales.” To do so, you can use a technique known as concatenation. Although Excel has a CONCATENATE worksheet function, I use this approach instead:
=INDEX(1:1,COUNTA(1:1))&” Sales”
In essence, I use an ampersand to join the additional text to my formula. Such text must be enclosed in double quotes.
The aforementioned formula can be entered in any cell in your worksheet. I've chosen to place it in cell A5, just below my source data, as shown in Figure 3.
Figure 3: A simple formula can return the month of the latest data in your chart.



You're now ready to automate your chart title. To do so, click on the title of your chart, and then click in the Formula bar. Click on the cell that contains your title formula, such as cell A5 in this case, and then press Enter, as shown in Figure 4.
Figure 4: Chart titles can reference a formula in a worksheet cell.



To make the chart completely dynamic, click on cell A1, choose Insert, Table, and then press Enter, as shown in Figure 5. My previous article in this series describes this technique in more detail.
Figure 5: Excel's Table feature automates charts such that new data appears automatically.



Your chart is now dynamic. If you've followed all of the steps in this article, you should be able to add a new column of data for May and see the new data and title automatically, as shown in Figure 6.
Figure 6: New data appears on the finished chart, along with a self-updating title.



Note: There are a couple of caveats to be aware of with regard to automating charts. First, if you make the data into a Table before you create your chart, Excel may group the data. To change this, click the Switch Rows/Columns button on the Design tab. Secondly, when automating chart titles, you must reference a worksheet cell, and your formula reference must include a worksheet name. Thus, you can't click on the title and enter =A5 in the formula bar. You must instead use the form =Sheet1!A5.
Stay tuned, as my next article in this series will explain the technique I use to utilize clip art within 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@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

Excel Tip: Using Today() versus NOW()

 

By David Ringstrom, CPA
Many users rely on the NOW() function to return today's date in a spreadsheet. This is a perfectly valid purpose, but in certain cases NOW() can cause a formula headache.



The NOW() function returns both today's date, but also the current time as well. For instance, if it's 12:00 p.m. on December 21, 2012, the formula =NOW() in a worksheet cell will return 41,264.50. This is because 12/21/12 is 41,264 days subsequent to January 1, 1900.



The .50 portion represents half of a twenty-four-hour day. Thus, if you're comparing a static date value, such as 12/21/12 to the NOW() function, Excel will indicate that they don't match, because 41,264 isn't equal to 41,264.50.



The solution for comparing dates is to use the TODAY() function instead, since it returns the date portion only; for example, 41,264 for 12/21/12.



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

Jun 16

Excel Tip: Pasting Text into Excel 2010

 
By David Ringstrom, CPA



From time to time, you might copy a list of data in text format from a webpage, report screen, or other source to the Windows clipboard. In such cases, Excel 2010 offers a hidden shortcut that makes it easier to break such data into columns.



With text data on the clipboard, click the lower half of the Paste button on the Home tab, and then choose Use Text Import Wizard.



This feature allows you to parse delimited data into columns based on a separator, such as tab, semicolon, comma, space, or a single character of your choice. Or, you can choose Fixed Width and manually place column breaks where you need them. This saves the step of pasting data into a worksheet column and then manually launching the Text to Columns wizard on the Data tab of the ribbon.




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

Jun 16

Excel Tip: Data Entry Shortcut

By David Ringstrom, CPA



If you find yourself making repetitive entries into an Excel spreadsheet, press Alt-Down to view a drop-down list of previous entries in the current column.



Arrow down the list and press Enter to make a selection.



Keep in mind that this technique works with text only, not numbers, and your cursor must be in or adjacent to a contiguous block of non-blank cells.



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

Jun 16

Excel Tip: Automatic Backup of Key Excel Workbooks

 
By David Ringstrom, CPA



From within the Save As dialog box of Excel, click the Tools menu, and then choose General Options.



Click the checkbox to Always Create Backup to have Excel automatically create an .xlk version of your file. For instance, if you save the workbook as Participant Hours Tracking.xlsx, each time you save the file Excel will create or overwrite a second workbook named Backup of Participant Hours Tracking.xlk.



The backup copy is only as old as the last time that you saved, so if you compulsively save every five minutes, your backup will always be five minutes old, but it does offer a bit of a safety net.



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

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

May 21

Business vs. Personal: How to Selectively Share on Facebook

By David H. Ringstrom, CPA



If you have a Facebook account, you’ve probably faced the dilemma of whether to accept a friend request from a business associate. Declining the invitation can cause wrinkles in professional relationships, but accepting the invitation may mean that you may not be able share as freely as you’d like on Facebook.
You can configure your Facebook friends list so that only your closest friends will see what you post, unless you choose to share with a broader group. This process involves creating a list and then adjusting your privacy settings:
  • Click Home, Friends, and then Create List as illustrated by steps 1 through 3 in Figure 1.
  • Assign a name, such as Business Friends, and then click Create, as shown in steps 4 and 5. Leave the Members field blank for now.
Figure 1: Initiating a New Friends List on Facebook
  • Click the Manage Lists button, and then choose Edit List as shown in steps 6 and 7 in Figure 2.
  • Change the On This List setting to Friends, and then click the pictures for any friends you wish to add to the list, as shown by steps 8 through 10. Make similar selections for Pages if you wish to restrict the types of posts that administrators of pages you've liked can view.
  • Click Finish to close the Edit dialog box as shown in step 11.

 

Figure 2: Managing a Friends List on Facebook

 

  • Click the arrow indicated by step 12, and then choose Privacy Settings.
  • Click the Custom button indicated by step 14, enter your list name in the Hide This From field, and then click Save Changes.
  • This set the default sharing basis for Facebook.
Figure 3: Choosing Privacy Settings
  • You can use the Custom button to confirm the audience for status updates. A tip appears when you hover over the button, or you can click the button to customize the settings for a specific post.
Figure 4: Confirming or Adjusting the Audience for Any Posts You Make on Facebook




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