Customizing Excel

Five Excel oddities

You might rarely need any of the features presented in this section. However, in the right circumstance, they can be time-savers.

1. Adjust the gridline color in the Display section of the Advanced category. If you are tired of gray gridlines, you can get a new outlook with bright red gridlines. I’ve met people who have changed the gridline color and can attest that nothing annoys an old accountant more than seeing bright red gridlines.

2. Allow negative time by switching to the 1904 date system in the General section of the Advanced category. Excel never allows a time to return a negative time. However, if you are tracking comp time and you allow people to borrow against future comp time, it might be nice to allow negative time. In this case, switch to the 1904 date system to have up to four years of negative time. Use caution when changing this setting. All existing dates in the workbook will shift by approximately four years.

3. Put an end to the green triangles on your account numbers stored as text. Most of the green triangle indicators are useful. However, if you have a column of text account numbers in which most values are numbers, seeing thousands of green triangles can be annoying. Also, the green triangles can hide other, more serious problems. Clear the Numbers Formatted As Text or Preceded By An Apostrophe in the Error Checking Rules check box in the Formulas category.

4. Automatically Insert A Decimal Point replicates the antique adding machines that were office fixtures in the 1970s. When working with a manual adding machine, it was frustrating to type decimal points. You could type 123456, and the adding machine would interpret the entry as 1,234.56. If you find that you are doing massive data entry of numbers in dollars and cents, you can have Excel replicate the old adding machine functionality. After enabling this setting, you can indicate how many digits of the number should be interpreted as being after the decimal point. The only hassle is that you need to enter $5 as 500. The old adding machines actually had a 00 key, but those are long since gone.

5. Change Dwight to Diapers using AutoCorrect Options. If you were a fan of the NBC sitcom The Office, you might remember the 2007 episode in which Jim allegedly put a macro on Dwight’s computer that automatically changed the typed word Dwight to Diapers. However, this doesn’t require a macro. From Excel Options, choose the Proofing Category and then click the AutoCorrect Options button. On the AutoCorrect tab, you can type new correction pairs. In this example, you would type Dwight into the Replace box and Diapers into the With box. The next time someone types Dwight and then a space, the word will automatically change to Diapers. You can also remove correction pairs by selecting the pairs and then pressing Delete. For example, if you hate that Microsoft converts (c) to ©, you can delete that entry from the list.