Journal of Accountancy Large Logo
ShareThis
|
TECHNOLOGY Q&A
Link preservation  
By J. Carlton Collins, CPA
December 2013

Q: I have created a “digital dashboard” in an Excel workbook that summarizes information from multiple Excel workbooks. As long as I maintain the linked files on my PC, the links work perfectly. However, when I email the dashboard workbook and related linked workbooks to others, these links stop working for some but not for others. What are we doing wrong?

A: To preserve links between workbooks, the resulting link paths and file names must be the same on the destination computer as on the origination computer; otherwise, Excel does not know where to look for the related linked files. Presented below are three suggestions:

1. Use identical file paths. Your links should continue to work correctly as long as your recipient saves the linked files using the same path and file name used when creating the links. For example, if the linked files were originally saved to C:/Data Folder, then your recipients should also save them to C:/Data Folder on their computers.

2. Repair broken links. Excel provides tools for repairing broken links. To use these tools, your recipients should open the dashboard workbook, and from the Data tab, select Edit Links to display the Edit Links dialog box, pictured below. (As each link is selected, the expected location of each linked workbook is displayed below the Select box. Clicking the Check Status button tests the link’s validity, and if the link is broken, an error message is reported instead of the Location.)

 

To repair the link, select it and click the Change Source button, then navigate to the correct source file, and click OK, Close. Repeat these steps for each broken link, then resave the dashboard workbook.

3. Share files in the cloud. Another approach to preserving links is to save files in the cloud via services such as Dropbox, Google Drive, iCloud, SharePoint Server, SkyDrive, etc. All parties can then access both the dashboard workbook (which contains the links) and the linked source files, without having to manage file locations or repair links. This approach offers the added advantage that the authors of the supporting source workbooks can update their information in the cloud and the resulting dashboard workbook can reflect their changes instantly (as links are refreshed), without having to constantly resend the linked source files to the recipients.

(Keep reading as the next topic describes new link management tools available in Excel 2013, which you may find helpful.)


TECHNOLOGY Q&A
Inquiring minds  
By J. Carlton Collins, CPA
December 2013

Q: In your April 2013 JofA article titled “Microsoft Office 2013,” page 32, you mentioned that Excel 2013 offers the ability to “compare two spreadsheets to display changes (similar to Word’s Compare tool),” but I can’t find this functionality. Can you please tell me where to find this tool?

A: To use Excel 2013’s new Compare tool, you must first activate the Inquire add-in from Excel’s File tab by selecting Options, Add-Ins. In the resulting Excel Options dialog box, from the Manage dropdown box in the lower-left corner, select COM Add-ins and click Go. In the resulting COM Add-Ins dialog box (pictured below), check the Inquire box and click OK.

 
These steps enable and display the Inquire tab in the Excel 2013 Ribbon, as pictured in the screenshot below. Make sure the two files you want to compare are open; then from the Inquire tab, select Compare Files, choose the two files you want to compare from the Compare and To dropdown boxes, and then click the Compare button.
 

 
 
Excel compares and analyzes the selected files, displaying them side by side in a new interactive Spreadsheet Compare window (a portion of which is pictured below). The cells that differ are highlighted and color-coded according to type (i.e., changed values, formulas, format, etc.). A separate window lists each changed cell address (along with descriptions of those changes), and a chart located in the lower-right corner summarizes the frequency of the types of changes detected. Additional windows summarize VBA code or macro changes line by line, when detected.
 

 

In addition to the Compare tool, the Inquire tab also includes four new analysis tools, described below:

 

1. Workbook Analysis. To view a report detailing dozens of characteristics of a workbook, from the Inquire tab, select Workbook Analysis to launch the dialog box (pictured in the next screenshot in the background) and place a checkmark next to the characteristics you want to analyze. For example, you might choose to analyze warnings, errors, hidden items, or linked workbooks. After making your selections, click Excel Export, Save. In the resulting confirmation dialog box click Load Export File to view the summary report (pictured below in the foreground).

 

The first worksheet summarizes the analysis, and the worksheets that follow provide details for each analysis. This type of report might be useful to help analyze or better understand a complicated Excel file you did not author.

2. Workbook Relationship. To help manage workbooks with numerous connections, from the Inquire tab, select Workbook Relationship to display an interactive diagram of all related files, with arrows depicting the directional flow of links to those files.

 

3&4. Worksheet Relationship and Cell Relationship tools. The Inquire tab’s Worksheet Relationship and Cell Relationship tools work similarly to the Workbook Relationship tool but instead summarize links between sheets for a specific workbook, or links to a specific cell address.


TECHNOLOGY Q&A
Sidebars  
By J. Carlton Collins, CPA
December 2013

SidebarsQ: I produce large reports in Word 2013 with brief summary information displayed in the margins about every two or three pages to add interest to the report. Because these text box summaries are simply repeating information already contained in the report, I don’t like to include them as part of the report; instead, I want them to appear outside the report area, in the page margins. My current approach is to insert section breaks after almost every page and then enlarge the margin so it can accommodate the summary information (as suggested in the thumbnail image at right). The problem is this creates numerous section breaks, and managing them becomes difficult because they constantly need adjusting as I add, delete, or move content. I am wondering if there is a better approach that I might be overlooking.

A: The inclusion of teaser information is an excellent way to draw readers into your reports, but I think you would be better off using Word’s Sidebar Textbox capabilities to achieve the results you desire. To create a professional-looking sidebar, from the Insert tab, select Text Box and scroll down to choose one of the 11 professionally designed sidebars (there are 35 builtin text boxes altogether, and more options are available on the web), such as the Facet Sidebar (Right) option pictured below.

 

These steps create a sidebar template on the current page, with text boxes, placeholder text, and artistic images grouped together and formatted with the proper page positioning and word wrapping needed to accommodate your teaser information.

The sidebar’s default word wrap settings allow text to flow freely from one page to the next without interference from section breaks, even if you add, delete, or move content. Additionally, if you find you need to move a sidebar from one page to the next, you can drag and drop it to the desired position (as shown below). This sidebar approach should prove faster and less cumbersome than your current approach.

 


TECHNOLOGY Q&A
Loud & Square  
By J. Carlton Collins, CPA
December 2013

Q: I’m using a Square credit card reader that attaches to my smartphone, but I’m finding that the transactions don’t always go through properly. Is there a certain smartphone or cellular service provider that works better with these devices, or should I be using a different smartphone credit card reader?

A: The solution may be as simple as turning up the volume on your smartphone. The Square Reader (as with similar devices) draws its power from the smartphone’s audio port (standard 3.5mm headphone minijack), but if the smartphone’s volume is too low, the device may not be receiving enough power to work correctly or consistently.

Many smartphone-based credit card readers are on the market; each has different fees and capabilities. This industry is growing fast, and these devices’ plans and features are changing frequently. The brief comparison in this chart may help steer you toward the best option.

 


TECHNOLOGY Q&A
Cell selections reach new heights  
By J. Carlton Collins, CPA
December 2013

Q: In Excel, is there a way to click on a cell containing a hyperlink without triggering the hyperlink? (Surely my current method of clicking next to the cell and then using arrow keys cannot be the best method.)

A: Make the row height bigger or the column wider, then you can click in the white area of the cell to select it without triggering the hyperlink.

 


TECHNOLOGY Q&A
Excel quick tip  
By J. Carlton Collins, CPA
December 2013

Holding down the Alt key and clicking on any cell triggers a Bing search based on the contents of that cell. The results are displayed automatically in a pop-up Research side panel. Give it a try. (Note: For this to function properly, you may need to first adjust the Research panel’s options by selecting the dropdown box at the top of the Research panel and selecting Bing).

 

 


TECHNOLOGY Q&A
Reader challenge: Privacy test  
By J. Carlton Collins, CPA
December 2013

In the aftermath of so many revelations about computer privacy this year, I thought it might be interesting to put your own privacy to the test. Earlier this year I found myself shopping for camping tents on Amazon.com, but I did not make a purchase. Shortly thereafter, I noticed that the camping tents I had studied for a longer time began showing up in banner ads in various websites, and even in ads along my Facebook wall. I wasn’t shocked to see this because I am well aware that a multitude of cookies pay close attention to my web surfing habits and target me accordingly.

This observation prompted me to conduct a more dramatic test in which I sent my wife an email message (using all of my active email accounts) stating that “I was thinking about purchasing a Rolex watch for our son on his birthday,” and I then waited to see if Rolex watches were mentioned in any website banner ads. To my amazement, I started receiving junk email messages promoting Rolex watches, an example of which is shown on the next page.


I’ve often preached that you should assume 1,000 people are reading each email message you send or receive, because all of your unencrypted email messages (along with any unencrypted attachments) are vulnerable to peering hackers. This email may offer proof that at least one person is harvesting and exploiting my emails.

So here’s the privacy test I propose: Send an email mentioning a retail item (preferably an item not normally associated with you), and then pay attention to your banner ads and junk email messages for a week to see if that item is mentioned in any of your emails or webpage banner ads. The results you see may provide clues as to your level of personal privacy. If you care to share those results with others (no matter the outcome), post them on this website that I created to capture your results: tinyurl.com/n7s99ws. (You may submit results anonymously.) I will compile the results and post them on this same website.


View CommentsView Comments   |  
Add CommentsAdd Comment   |   ShareThis

AICPA Logo Copyright © 2013 American Institute of Certified Public Accountants. All rights reserved.
Reliable. Resourceful. Respected. (Tagline)