Excel and other technologies help accountants work faster and more efficiently. However, those who can use technology tools more effectively can achieve greater efficiency. Accountants are constantly looking for new ways to accomplish more work in a shorter time. Although technology tools can be a great help in this quest, they can also do more if you have the right techniques. FM magazine presents 100 tips and tricks to help you leverage technology like Microsoft Excel. Some features are only available in the most recent Microsoft Office and Windows versions.

Hide zero values

Including many zero values in your data can be distracting. You can hide zero values easily by going to the tab, Options, and Advanced. Uncheck Show zero in cells with zero value.

Blank rows can be deleted

You don’t have to delete each row separately. Instead, you can delete all the blank rows at once. To make this work, you must have your header rows on the first row in the spreadsheet. Click on the letters at the top of the columns to select all columns containing your data. Select Filter from the tab. Select the drop-down menu arrow to the right of the first column. Uncheck (Select all) and then check (Blank). If you see any numbers, move to the next column and repeat the steps above. Keep going until you see no data. Select the filtered rows, then go to the Home tab, cells group and choose Delete. Select Clear from the Data tab.

You can name a cell or a group of cells in Excel

Instead of trying to find or remember a particular cell, or range of cells, you can refer to the name in formulas and functions. Highlight the cell or range that you wish to name, and then enter the name in the Name Box (the box left of the formula bar). Click Enter.

Select an entire table, range, or range instantly by clicking anywhere in the table/data range and pressing Ctrl+A.

AutoSum shortcut

Select the cell to the right or the bottom of a vertically arranged list of values and then press Alt+=. Then, hit Enter.

AutoSum allows you to quickly foot and cross foot

It can also be used for sum formulas that combine all rows and columns. Highlight the table with data. Add one row to the left and one column below. Or press Alt+= to activate the AutoSum option.

Transpose of data

Copy the data and then place your cursor on the cell where you want to paste it. The Home tab on the ribbon will allow you to select Copy from Clipboard, then choose Paste Specific. Click OK to check Transpose.

 You can sort data by color

Excel does not only allow you to sort data based on cell values. You can also sort data based on font color and cell color. Select the data you wish to sort. Select the Home ribbon tab. Next, choose Sort and Filter. Check that My data contains headers. Select the column you wish to sort in the Sort By drop-down list. Choose a Cell Colour or font Color from the Sort on the drop-down menu. Choose the color that you wish to be displayed first in the Order drop-down list. Next, click add Level at the top of the Sort Window. Continue the above steps for the second color that should be displayed, and so forth until Excel is instructed on how to sort cell colors and font colors.

You can quickly resize a column so that it fits the contents

There may be data in a cell with a shorter or longer column width than the default. Double-click the border between two column headers to adjust the column width. This will allow you to fit the longest text in the column.

Extract characters from the left side of a text string

You may want to extract the area code for a telephone number, for example. Use the function LEFT (Text, Num_chars). Refer to the cell containing the text string for Text. Enter the Num_chars number to extract from the text string. Click OK.

You can extract characters from the right side of a text string

You may want to extract the four last digits of a National Insurance Number. Use the function RIGHT (Text, Num_chars). Refer to the cell containing the text string for Text. Enter the Num_chars number from the far right side of the text string you wish to extract. Click OK.

 Extract characters from the middle of text strings

You may, for example, need to extract the middle of a product #. Use the function MID (Text, [Start_num],[Num_chars]). Refer to the cell containing the text string for Text. Enter Start_num to determine the position of the first character you want to extract. For example, if you were to extract the fourth character from a text string, Start_num would be 4. Enter the Num_chars number of characters you want to extract from the text string. Click OK.

Combine multiple cells into one text string

In the new text string that you create, you can include symbols and spaces. Select the CONCATENATE function (Text 1, text 2, etc.). Enter the cell reference, text, or any other characters to start Text1. Enter the cell reference, text or any other characters for Text 2, and so on. If you have a first and last name in cells A1 and B1 and want to combine them into one cell, reference cell A1 in Text1. Text2 will reference cell A1 in Text2. Text 3 will reference cell B1 in Text 3. CONCATENATE (A1, “,B1).

 Hide/unhide worksheets To hide a worksheet, click on the tab and select Unhide. Next, choose which sheet you want to unhide.

Hide/unhide columns/rows Select the columns and rows that surround the hidden columns and rows in the worksheet to unhide them. Right-click within the columns or row, then select Unhide.

Only copy visible cells

Click F5, Special or Visible cell only to do this. To copy, press Ctrl+C.

Sum data from different places in a spreadsheet

These values can be summed, but not the AutoSum shortcut or the AutoSum tool. Select the function SUM. Select a cell (or range of cells) that will be part of the Number 1 total. Select another cell or range to be included in Number 2. Continue to Number 3 if necessary, and so on, until you have captured all the numbers that make up this sum. Click OK. Example: SUM(A2,C9:C12,E5).

You can instantly copy a formula to multiple columns

If a formula is already added to a row of data, it must be copied to all subsequent rows. It is much easier to double-click the Filled Handle instead of pulling it down on the Fill Handle (the green rectangle that appears at the bottom of each cell when it has been selected). If the cells to the left of the copied forms aren’t empty, this will work.

Freeze panes

If you have a large spreadsheet that has titles for columns and rows, it can be difficult to see which cell is being displayed if you scroll down where you cannot see the column or row titles. Select the cell to the right and below the first column title, and the row title to keep the column and row titles visible. Select Freeze panes from Window, then choose Freeze panes.

 Sparklines can be used to visualize your data

(See cells G3-G5 in the screenshot). Choose the cell where you want the Sparkline(s). Under the Sparklines group, click the Insert tab. Choose Line, Column, or Win/Loss. Choose the data that you want to include in the Sparkline(s).