10 must-have Android apps (revisited)

There’s an old French saying (one I first heard in the Rush song “Circumstances”) that goes Plusça change, plus c’est la même chose. Roughly translated: “The more it changes, the more it’s the same.”

Changes to the Android platform have been tremendous over the past year. But our need for” best apps” lists remains the same. A year ago, I wrote 10 must-have Android apps. Now, many changes later, there is a need for a new version of that same article. More apps are available and some older apps have been vastly improved. It’s time they were given a list of their own. And this time around, I’ve also included tablet-friendly apps as well.

1: Google Docs (Free)

The Google Docs app is of exceptional value to anyone who regularly uses Google Docs. Although it is much more tablet- than smartphone-friendly, Google Docs makes it easy to manage and edit your Google documents. This app lets you collaborate with other Google Docs users; quickly make changes to spreadsheets; view your documents, PDFs, presentations, and more; upload and convert files to the Google Docs format; take a photo (with your device) of printed text and convert it to a Google document; and easily share docs with your device’s contacts.

2: Thumb Keyboard ($2.29)

If you use an Android tablet, you MUST buy and install Thumb Keyboard (Figure A). Your typing will not only improve, but you will be able to type longer and more comfortably. This is one of the first apps I install when testing or upon purchasing a new tablet. Once you’ve used this keyboard, you will never go back to the standard keyboard.

3: Intuit GoPayment (Free with approved account)

Intuit GoPayment allows you to accept payments from anywhere at any time. Money is deposited into a GoPayment Prepaid Visa Card account and can seamlessly integrate into QuickBooks. Once you download the app, you fill out the in-app application form. Once approved, you are ready to start taking payments. You will also receive a free credit card reader that attaches to your mobile device to make taking payments even easier. For those who are running their own business (and who use QuickBooks), this is a great app.

4: Kindle for Android (Free)

If you are a reader, you need Kindle for Android (Figure B). It’s one of the finest ereader apps available. Kindle for Android enables your tablet or smartphone to gain access to millions of books — and it allows you to read those books under any light, use a built-in dictionary, sync your ebooks with your Kindle or PC, and much more. This is an essential app for avid readers of nearly any genre.

Figure B

Kindle for Android

5: Google+ (Free)

Will Google+ overtake Twitter and Facebook as the number one social networking spot? Only time will tell. But for many businesses, Google+ seems to be a much better fit. So why not make use of this flexible social networking tool on your tablet or smartphone? With seamless integration into the Android platform, using Google+ on an Android device is even simpler and more elegant than the other social network apps.

6: LogMeIn Ignition ($29.99)

LogMeIn Ignition is the most expensive app on the list. But it’s also one of the most important apps on the list if you use LogMeIn to handle remote administration tasks. I use this tool daily, and on a number of occasions, I have deployed it for clients. Of all the remote administration tools you can have on a tablet or smartphone, LogMeIn Ignition is the best of the best.

7: Beautiful Widgets ($2.89)

If you’re looking for an awesome collection of widgets to add to your tablet or smartphone home screen, look no further than Beautiful Widgets (Figure C). Its biggest strength is the ability to personalize all the widget s. You can configure this collection of home screen tools to look and behave exactly how you like them. The features are too numerous to list. (Check out the apps page to see what all it includes.)

Figure C

Beautiful Widgets

8: ROM Manager ($5.99)

If you have a rooted device, you owe it to yourself to look into ROM Manager. With the help of a great UI, it lets you easily apply nightly builds. It also makes switching ROMs a snap and you can even back up your ROMs. This application will interest only a select number of users (power root users). But for them, this is without a doubt a must-have.

9: Poweramp (Free)

If your Android device also serves as your music playback device, you owe it to yourself to installPoweramp. For me, this app is worth it just for the 10-band graphic EQ. For others, the support for mp3, mp4/m4a (including alac), ogg, wma (wma pro files may require NEON support), flac, wav, ape, wv, tta, mpc, and aiff formats makes this a must-have music app. You will also find plenty of other features, such as outstanding OpenGL-based cover art animation and gapless playback, to woo you away from the included player.

10: AirDroid (Free)

AirDroid is a handy little app that lets you take control of your Android device from within a Web browser. With this app, you can transfer files back and forth, cut/copy/paste/manage files on the SD card, install/uninstall applications, preview/delete/import/export photos, share contents of the clipboard, manage contacts, and much more. The one hitch with this application is that the PC and the mobile device must be on the same wireless network. But once you have AirDroid working, you will find it an incredibly easy way to work between your PC and your mobile device.

Something for everyone

The list of top Android apps could easily go on and on. Couple this list with the original one, and you have a collection of Android apps that are sure to please nearly any cross-section of users. And since these apps are all “must haves,” you should skip the previews and just install and run them. Tout de suite!

10 tips for troubleshooting Excel formulas and functions

Takeaway: Finding and fixing errors in formulas and functions can be tedious and time-consuming. Here are some practical tips to help you cut to the chase.

Troubleshooting an Excel worksheet’s formulas and functions can be a big job. Errors just come with the package. Excel offers some valuable auditing tools, found on the Formulas tab in the Formula Auditing group, but you won’t always need that much power. Sometimes, just a bit of special knowledge can help you resolve an error. These 10 tips will help even the most experienced Excel users find common errors quickly.

1: Error values are a clue, not a nuisance

If Excel can’t calculate a formula, it displays an error value. These values are valuable clues and knowing what they mean can help you quickly spot errors. The following error values are your first step, and often the only step, to debugging an error:

• #DIV/0: Excel can’t divide by 0 and your formula refers to a cell that contains or evaluates to 0 or is blank. This is one of the easiest to find and fix.
• #NAME?: This error occurs when you refer to a range incorrectly or forget to enclose text in quotation marks. When Excel encounters text in a formula, it tries to interpret the text as a cell reference, a range name, or a function name. When it can’t recognize the text as any of those things, it displays this error value. Check your function names, cell references, and range names (make sure the range name actually exists if you’ve spelled it correctly). If all ranges are correct, make sure you’ve delimited text using quotation marks.
• #NULL!: You specified an intersection between two ranges that don’t intersect, such as A1:C3 and D4:F6. If the ranges do intersect, you’ve probably used a space character (the union operator) instead of a comma to separate the two ranges (A1:C3 D4:F6 instead of A1:C3,D4:F6).
• #NUM!: This error value indicates a problem with a number in the formula. Either the argument is invalid or the result is too large or small.
• #REF!: You’ve referenced a cell incorrectly. Most likely, you’ve deleted a cell. For instance, the expression =A1+B1 won’t return an error if you delete the value in B1. But if you delete column B, it will.
• #VALUE!: You’ve expressed the wrong data type or operator. The most common cause for this error value is a reference to text by a numeric operation.

2: Use error handling for #N/A

Excel’s #N/A error value isn’t like the rest — there’s usually nothing wrong with the formula itself. It means that Excel can’t return a result that makes sense. This error shows itself a lot with VLOOKUP() and HLOOKUP(). When one of these functions returns #N/A, it usually means it can’t find the value you’re looking for or that you haven’t sorted the list (which isn’t always necessary). Fixing this error requires error handling. Specifically, in this case, you’d wrap the original function in an error-handling routine that displays a meaningful message or more appropriate value. For instance, you might display the message, “value not found” — the user understands that — whereas the #N/A value might cause confusion.

3: Evaluate components

One of the quickest ways to track down a problem is to evaluate individual components in the Formula bar or even right in the cell. Specifically, you highlight a cell reference or simple equation, press [F9], and Excel evaluates the highlighted expression and returns the result in the Formula bar. Either way, you can quickly pinpoint specific problems with logic. (To edit in-cell, simply double-click the cell. If that doesn’t work, check the Advanced Editing Options.) Press [Esc] when you’re done.

Figure A shows a simple IF() function that returns the string “is down” if the yearly total for 2011 is less than 2010 — well, it’s supposed to. When evaluating the expression in the Formula bar, as shown in Figure B, you can quickly see that the true and false actions don’t match the conditional statement’s intent. This works, this time, because the expression follows the conditional logic: $44,075 is less than $45,962. But that won’t always be the case. You can also use the Formula bar to learn a constant’s value and to discover errors caused by invalid data. It’s not just for troubleshooting formulas.

Figure A

Shouldn’t this expression return “is down?”

Figure B

Evaluating a portion of the formula in the Formula bar helps you pinpoint logic errors.

The Evaluate Formula option on the Formulas tab (in the Formula Auditing group) works similarly. It’s a bit more methodical but certainly useful. In Excel 2003, you’ll find Evaluate Formula on the Tools menu under Auditing.

4: Check for multiple lines

Check the right end of the Formula bar if a formula looks too simple. Figure C shows a double arrow, which indicates that the formula comprises multiple lines. Click the down arrow to walk through the lines or click the Expand Formula Bar button (to the right) to display the full formula.

Figure C

The down arrow to the right indicates that there’s more to this formula.

To separate formula components, simply position the cursor where you want the formula to wrap to a new line and press [Alt]+Enter. Breaking an expression across lines can help you confine expression logic, where each line contains a step toward the resulting value. (Doing so often confuses users, though.)

5: Display formulas for easy checks

To quickly display all formulas, press [Ctrl]+~ (that’s the tilde sign to the left of 1). Figure D shows a sheet’s formulas. It’s a toggle, so pressing [Ctrl]+~ a second time will display results. Display formulas to check consistency and spot potential errors. Printing the formulas lets you continue your audit away from your computer. Most users already know how to do this, but you can use this tip to work more efficiently by using two windows, one with formulas, one with results, as shown inFigure E.

Figure D

Toggling between formulas and their results can help spot errors and inconsistencies.

Figure E

Display both formulas and their results to work more efficiently while troubleshooting.

You can also click Show Formulas in the Formula Auditing group on the Formulas tab to toggle between these two views. In Excel 2003, choose Options from the Tools menu. Then, click the View tab and check or uncheck the Formulas options in the Window Options section.

6: Select formula cells

Locating all the formulas in a complex sheet can be tedious work and you’re apt to miss some if you rely on your memory or eyesight. Instead, let Excel do the work for you as follows:

1. Press [F5] to display the Go To dialog and click the Special button at the bottom. In Excel 2003, choose GoTo from the Edit menu and then choose Special.
2. In the resulting window, check the Formulas option, as shown in Figure F.
3. Click OK. Figure G shows the result — in this case, a small range of formulas (B2:B4).

Figure F

Check the Formulas option.

Figure G

Use the Special option to select cells that contain formulas.

If you need a more permanent solution, read Identify which cells in Excel are formula cells.

7: It’s a date; no, it’s a number; no, it’s an error!

Sometimes, a formula returns a date or time value instead of the number you were expecting. This usually happens when a formula references a cell you’ve formatted as a date or time. In this case, there’s nothing wrong with the formula; simply reformat the referenced cell correctly and the error value should disappear.

8: Check the number of characters

Older versions of Excel limit formulas to 1,024 characters; Excel 2007 has increased the maximum character limit to 8,192. It’s doubtful that Ribbon users will ever hit that wall, but those still using the menu version might keep the 1,024-character limitation in mind. In addition, function nesting has increased from seven to 64 — it boggles my mind. For more limits on Excel 2003 and Excel 2007/2010, read the following articles:

• Excel specifications and limits (2003)
• Excel specifications and limits (2010)

9: When your function returns the function

If you enter a function, press [Enter], and Excel displays the function, Excel is interpreting the cell contents as text. There are two possible fixes:

• If there’s an apostrophe character at the beginning of the function, delete it.
• Make sure you haven’t formatted the cell as Text. If you did, change it to General or something more appropriate.

10: When your formula doesn’t recalculate

You know the formula or function works — it was fine until you changed one of the dependent values. When you did that, the function did not recalculate and show the updated result. The most likely reason is that you’ve turned off Excel’s automatic recalculation. It’s easy to turn this option off while working and forget to turn it back on. Simply reset the calculation method to automatic as follows:

1. Select the File tab and click Options. In Excel 2007, click the Office button and click Excel Options. In Excel 2003, choose Options from the Tools menu.
2. Select Formulas in the left pane. In Excel 2003, click the Calculation tab.
3. In the Calculation sections, check the Automatic option.
4. Click OK.

10 cool ways to use Excel's conditional formatting feature

Formatting, such as currency, alignment, and color, determines how Excel displays a value. But conditional formatting is more flexible, applying specified formatting only when certain conditions are met. Here are some creative ways you can push conditional formatting beyond its expected uses.

This article assumes a basic knowledge of Excel’s conditional formatting feature. In Excel 2007 and 2010, Conditional Formatting is in the Styles group on the Home tab. In Excel 2003, Conditional Formatting is on the Format menu.

Note: This list is also available as a photo gallery, and you can download a sample workbook (xls and xlsx format) that includes examples of the techniques described here.

1: Distinguish business rule violations

Using conditional formatting, you can visually discern when something is breaking a business rule. For example, Figure A shows a simple timekeeping sheet that highlights a workday that’s greater than eight hours. Why? Because your organization requires approval for anything over an eight-hour day.

Figure A

Alert users to overtime hours requiring approval.

Working with the time values complicates things a bit, as you can see in Figure B. This solution uses >.34 to represent time values greater than eight hours, which will work in most cases — you can’t use the value 8 or even the time value 8:00. Or you could use the predefined Greater Than rule in Excel 2007 and 2010, which will automatically use the more accurate value of 0.333333….

Figure B

This formula returns True when a time value is greater than eight hours.

2: Display simple icons

Using conditional formatting (in 2007 and 2010), you can display icons that are often easier to interpret than the values they represent. For instance, a simple checkmark might be quicker to discern than the text value yes, on, true, and so on. Figure C shows an icon solution for the same rule violation in #1.

Figure C

Use icons instead of traditional formats to represent conditional rules.

First, select the values in column E and apply one of the default icon sets. Then, use Manage Rules to manipulate the results. Figure D shows the final settings (click Reverse Icon Order first).

Figure D

These settings display green and red icons to alert users to an overtime violation.

3: Highlight a row based on a single value

Filters are great for limiting what you see, but sometimes you want to compare records. When this is the case, conditional formats can distinguish records. Figure E shows a data set of products with a conditional format highlighting only Condiment records.

Figure E

You can distinguish records based on a single value.

Select the entire data range (not the column headings) so Excel can format the entire record (row). Figure F shows the formula-based settings. The $G2 component creates a relative address, which updates with each row: G4, G5, G6, and so on. When the value in the referenced cell equals the string “Condiment,” Excel highlights the entire row.

Figure F

You can highlight a row by applying the conditional format to the entire data range instead of a single column.

4: Create a dynamic record highlight

Highlighting an entire record (#3) is convenient, but you might want the conditional format to be more… conditional. For instance, suppose you want users to choose the category on the fly, as shown in Figure G. First, use the Advanced Filter feature to copy a unique list to an out-of-the-way spot, as shown in Figure H. Then, use the Data Validation feature to create a list, also shown in Figure H.

Figure G

Let users choose the highlighted category from a validation list.

Figure H

You’ll need a unique list and a validation list.

With the list in place, update the conditional format formula to reference the input list cell, as shown in Figure I. Instead of referencing a cell within the row, the formula references the validation list in B2. Selecting an item from the validation list updates the conditional formatting.

Figure I

This rule references the validation list instead of a literal string value.

5: Compare values

It’s common to compare values. For instance, you might track inventory levels by comparing the stock on hand to a reorder level. Using conditional formatting, you can alert users when it’s time to reorder, as shown in Figure J. Select the values you want to format — in this case, that’s B2:B46. (You could highlight the entire row or one of the inventory values.) Then, apply the format shown in Figure K.

Figure J

Highlighted items that are running low.

Figure K

This rule highlights the product in column A when the in stock value goes below the reorder level.

6: Compare lists

You can find discrepancies between two lists using a conditional formatting rule, as shown inFigure L. This rule, shown in Figure M, compares each value in column A to its counterpart in column B. If they’re not the same, Excel highlights the value in column A. To highlight the values in column B instead, select the values in column B and update the rule formula to reference the values in column A.

Figure L

This rule highlights values in column A when they differ from the values in column B.

Figure M

Apply this rule to compare the two side-by-side lists.

7: Create alternating bands

Many sheets highlight every other row (banding) to improve readability. The Table feature offers several predefined formats that include bands, but you end up with a table instead of a plain data set, and that might not be what you want. When you don’t want a table, use conditional formatting to create alternating bands, as shown in Figure N. The rule shown in Figure O highlights cells to achieve the alternate band effect.

Figure N

Banding can improve readability, but you’ll probably want to highlight even or odd rows and not both, as shown here.

Figure O

Apply either of these rules to create an alternate band effect.

8: Find duplicates

To find duplicate values or records, you can use a filter, but conditional formatting can pinpoint duplicate values on the fly. For instance, the sheet in Figure P shows duplicate values in a single column. Select the values you want to format and apply the formula-based rule shown in Figure Q.

Figure P

Highlight duplicate values for a quick alert.

Figure Q

This rule highlights all occurrences of the same value.

To ignore the first occurrence and highlight only subsequent values, use this formula:

=COUNTIF($A$2:$A2,A2)>1

If you want to check for duplicate values across multiple columns, concatenate the values and apply a similar rule to the results, as shown in Figure R. You can also hide duplicates (which I don’t always recommend) by selecting a font color that matches the sheet’s background.

Figure R

The concatenating formula in column C finds duplicates across multiple columns.

You can use Excel 2007 and 2010’s built-in Find Duplicates rule, but it has a few limitations. First, you don’t have much flexibility with the actual formats. Second, this rule selects all duplicate values; you can’t choose to ignore the first occurrence and highlight just the subsequent values, as this more flexible formula-based rule can.

9: Find discrepancies

Verifying data is an important task, and Excel’s conditional formatting can help by alerting you to inconsistencies. Figure S shows a common accounting tool known as cross-footing — the process of double-checking totals by comparing subtotals across rows and columns — in cell F16. Adding the conditional format makes the discrepancy hard to miss when the two totaling values don’t match. Select either of the cross-foot formulas and apply the rule shown in Figure T.

Figure S

Combine conditional formatting with cross-footing for a hard-to-miss alert.

Figure T

This rule compares two values.

10: Find the smallest or largest duplicate

It’s easy to find the smallest or largest value using the predefined rule shown in Figure U, but you’ll need a more complex rule to highlight the smallest or largest duplicate value. As you can see in Figure V, the value 3 is the smallest value in the column, but Excel highlights each occurrence of the value 5.

Figure U

This option finds the smallest or largest value in a range.

Figure V

A more complex rule highlights the smallest or largest duplicate value in a range.

This rule is unstable if you use normal referencing, so apply a range name to your data set before applying the conditional formatting rule. The rule shown in Figure W will highlight the value 3 in the range named List only if 3 is also a duplicate. To find the largest duplicate value, substitute the MIN() function with MAX().

Figure W

This rule ignores the smallest value unless it’s also a duplicate value.

10 steps to creating a Word input form

Takeaway: User forms make data entry more efficient, reduce errors, and ensure consistency. This walk-through will show your users how easy it is to set up their own forms.

Input forms are a good way to guide users and control input. Most of the time, you’ll use them to generate routine forms, where the user supplies information to complete some process. This article will show your users how to create these input forms using content controls themselves — removing you from the loop! Most input forms don’t require any code or specialized knowledge beyond choosing the best control for the job.

Word 2007 introduced content controls, which replace form fields in earlier versions. Although you can use 2003 form fields to collect data, they’re a more complex tool and not interchangeable with content control behaviours. This article doesn't include instructions for using Word 2003 form fields.

1: Determine needs

The first step is always about design. Take a minute to consider the form’s purpose and the type of information that will be entered. If the form is complex, you might want to sketch a quick design. This step won’t take much time, and it will help you produce the right form the first time.

2: Create the shell

In this context, the term shell refers to the permanent labels and formats that won’t change with usage. For instance, in this example, we’ll work with the simple order form shown in Figure A. It contains a few descriptive labels and simple formatting.

Figure A

This shell contains labels and formatting.

3: Save the shell as a template

Most of the time, you’ll want to save the shell as a template. After entering the descriptive labels and applying formatting, save the form as follows:

1. Click the File tab (or the Office button in Word 2007) and click Save As.
2. From the File As Type drop-down, choose Word Template (*.dotx).
3. Give the document a name.
4. Click Trusted Templates in the Favorites bar to select Word’s default template folder. You can save the template anywhere you like, but this is the easiest route. Figure B shows the expanded drop-down so you can see the full path.
5. Click Save.

Figure B

Save the form as a template in Word’s default template folder.

You don’t have to save these forms as templates, but doing so is consistent with most usage conventions.

4: Display the Developer tab

The content controls are available via the Developer tab, which Word doesn’t display by default. To display this tab, click the Quick Access Toolbar (QAT) drop-down and choose More Commands. In the left pane, click Customize Ribbon. In the list to the right (under Main Tabs), select the Developer item, as shown in Figure C. Then, click OK. In Word 2007, click the Office button and then click Word Options. Choose Popular in the left pane and then select the Show Developer Tab In The Ribbon option.

Figure C

Display the Developer tab to gain access to content controls.

You’re not adding anything to the QAT, but the drop-down provides quick access to the Ribbon interface tool.

5: Add text boxes

Now you’re ready to add the first content control. Position the cursor a couple of tabs to the right of Name and click the Developer tab. In the Controls group, click Plain Text Content Control. Continue by inserting a plain text control for each of the following input areas: Ext #; Dept; Part; and Price, as shown in Figure D.

Figure D

These text controls allow plain text entries.

6: Add a date picker

Adding a date control is just as easy. Position the cursor to the right of Date and click the Date Picker content control. To change the way the control displays the date, click Properties in the Controls group (with the date picker control still selected). In the resulting dialog, shown in Figure E, change the display format by choosing the MMMM d, yyyy format. Then, click OK.

Figure E

You can change how the control displays the date.

7: Add a list box

Use a list or combo box when you know the choices. For instance, by providing a list of departments, you can limit typos and input errors, which isn’t possible with text controls. To add a list box, position the cursor to the right of Dept and click the Drop-Down List content control from the Controls group. To populate the list, click Properties in the Controls group. In the resulting dialog, click Add and enter Editorial, as shown in Figure F. The Value property isn’t of interest in this technique, so you can leave it alone. Continue adding items until you’ve completed the list, as shown in Figure G. Then, click OK to return to the form.

Figure F

Add list items.

Figure G

Add as many items as needed to create a comprehensive list.

8: Protect the form

At this point, you’re almost done. Once you’ve inserted all the content controls (for input), protect the document. On the Developer tab, click Restrict Editing in the Protect group to open the Restrict Formatting And Editing task pane. In Word 2007, click Protect Document and choose Restrict Editing And Formatting.

In the step 2 section, select the Allow Only This Type Of Editing in the Document under Editing Restrictions. Then, choose Filling In Forms from the drop-down list, as shown in Figure H. Finally, click the Yes, Start Enforcing Protection button. Word will prompt you for a password. In this type of document, you probably won’t need to password-protect anything. You can bypass this option by clicking OK without entering a password. Save the template one last time.

Figure H

Specific a fill-in form when protecting the form.

9: Distribute the form

This next step requires no special instructions. Users can distribute the template as they normally would, with instructions for saving the form in the users’ template directory.

10: Use the form

Using the form is simple. If you saved the form as a template, click the File tab and choose New. In Word 2007, click the Office button and choose New. Click My Templates in the Available Templates section and double-click the template to open a new document (form). Enter a name and press Tab; then, click the date picker’s drop-down list and select a date, as shown in Figure I. Pressing Tab will cycle through the controls. Figure J shows the drop-down list you created for the department information.

Figure I

Choosing a date from the date picker is easy!

Figure J

Providing lists helps users make choices.

When the user has entered all the appropriate items, he or she can print and close the document, or save the document, as required. Because they’re working from a template, users can quickly create as many forms as necessary.