Excel Tips and Time-savers

Below are some tips and shortcuts that may help save time and effort while working on Excel-related accounting projects.

• Round numbers to thousands with hundreds in Microsoft Excel
   
• Adding comments to a formula in Microsoft Excel
   
• Hiding errors in formulas before printing in Microsoft Excel
   
• Copying a series from one chart to another in Microsoft Excel
   
• Quick Move to end of range of cells in Microsoft Excel
   
• Auto-expanding a Date Type Chart using Microsoft Excel
   
• Adding a wrap text shortcut in Microsoft Excel
   
• Create and add a function for Reversed Text Characters in Microsoft Excel
   
• Joining text using a keyboard shortcut in Microsoft Excel
   
• The best shortcut keys in Microsoft Excel

Round numbers to thousands with hundreds in Microsoft Excel

Press Ctrl+1 (Format Cells), Choose Number tab, from Category Choose Custom, in the Type box enter :

#,##0.0,_;[Red](#,##0.0,);- ;

Adding Comments to a Formula in Microsoft Excel

Use this trick to add a comment to a formula:  at the end of the formula, add a + (plus) sign, the N function, and an open parentheses; then type your comment in quotation marks, and close the parentheses. You can view the comment in the formula bar when you select the cell.

Example:
=A1+A2*4.71+N("Total Sales for January and February * Rate of Exchange")

The Best Shortcut Keys in Microsoft Excel

Hiding errors in formulas before printing in Microsoft Excel

1. Select the Print Area in the Worksheet, and from Format, select Conditional Formatting.

2. In Condition 1, select Formula Is. In the Formula box, type =IsError(A1).

3. Click Format and select the Font tab. Under Color, select white and click OK. In the Conditional  Formatting dialog box, click OK again.

Copying a series from one chart to another in Microsoft Excel

To copy one data series from one chart to another:
1. Select the series on the first chart.
2. Click in the formula bar, select and copy the series formula
3. Click on the edge of the second chart to select the chart.
4. Click in the formula bar and paste
5. Press Enter

Quick Move to end of range of cells in Microsoft Excel

Instead of moving from a cell to the end of a range of cells by using Ctrl+Arrow, just simply double-click on the edge of the cell nearest to the direction which you want to go.

Auto-expanding a Date Type Chart using Microsoft Excel

When making a Chart that contains info for a date range, you often have to fill in data you know, and go back and adjust your Chart to include the subsequent date range once data is available.

To avoid this particular issue, make the data your Chart reference a link to 'real' data.  In this reference data area, insert a formula such as =if(A1<>"",A1,NA()) so the cells referencing accumulated data are filled in, while cells referenced not yet accumulated data are filled with #N/A.

You also need to set the chart to time-scale by selecting Chart Options\Axes\Time Scale

By doing this, you can have the chart reference 'dates' and data that haven't yet arrived.  Excel ignores the #N/A fields until their referenced cells contain data, expanding the chart on the fly to include the 'new' data.

Adding a wrap text shortcut in Microsoft Excel

Wrap text is a useful technique that you will use frequently when working in Excel.

You add the shortcut by adding a style in the Style box.

Adding the Style box to the Formatting toolbar

1. Right-click one of the toolbars, and select Customize.

2. Select the Commands tab, and then select Format.

3. Drag the Style icon from the Customize dialog box to the Formatting toolbar, and drop it next to the Font Size box (or anywhere else you choose).

4. Click Close.

Adding wrap text to the Style box

1. Enter text or several words into a cell in the worksheet, and press Ctrl+1.

2. Select the Alignment tab, check the wrap text check box, and click OK.

3. In the Style box, enter the text wrap text.

Create and Add a Function for Reversed Text Characters in Microsoft Excel

Click Alt+F11 to open Visual Basic Editor (VBE).

From the Insert menu, select Module (to add a module).

Enter the following lines of code into the module:

Function ReverseText(text) As String
Dim TextLen As Integer
Dim i As Integer
TextLen = Len(text)
For i = TextLen To 1 Step -1
ReverseText = ReverseText & Mid(text, i, 1)
Next i
End Function

Testing the function:

1. To test the function, open Paste Function (Shift+F3).

2. From the User Defined category, select the ReverseText function.

3. In the function box, select any cell that contains text.

4. Click OK.

Joining text using a Keyboard shortcut in Microsoft Excel

1.In cell A1, enter the text "This is the".

2. In cell A2, enter the text "best excel book ever published".

3. In cell A3, enter the formula =A1&" "&A2.

Explanation

The ampersand symbol (&, Shift+7) joins text the same way the + symbol joins numbers. The quotation marks are used to add empty spaces between them.

In the example above, a space is added between the words combined. In cell A3, you can see the combined sentence.