Quick Excel tips for accountants

Q. I would like my employees to be more efficient with Excel. They are all at different Excel proficiency levels. Are there some quick tips I could share with them?

A. There are so many tips that can be learned in Excel! I could write pages of them, but let’s start with a just a few quick ones for varying levels of proficiency.

1. Shortcuts for copy and paste: It’s a fact that the more time your hands are off the mouse and on the keyboard, the faster you work. Copying and pasting are two actions done many times when using Excel, so get in the habit of using these shortcuts:

• For a PC: Ctrl+C to copy and Ctrl+V to paste.
• For a Mac: Command+C to copy and Command+V to paste.

2. Instantly switch from one Excel document to another: If you are using more than one Excel workbook at a time, this will speed up the time it takes to switch between documents.

• For a PC: Ctrl+Tab.
• For a Mac: Option+Tab.

3. Select all consecutive data in columns and/or rows: Selecting consecutive data from columns and/or rows by scrolling is fine if you have a small amount of data to select. If you have ever tried doing this for a large amount of data, you probably know how unruly this gets. It takes time and you probably went past the last row or column of data — yes, Excel speeds up as you do this. There is a quick and less frustrating way to select your consecutive data from columns and rows. Put your cursor in the first cell you want selected, then:

• For a PC:
  • Ctrl+Shift+↓ to select the column of data.
  • Ctrl+Shift+→ to select the row(s) of data.
• For a Mac:
  • Command+Shift+↓ to select the column of data.
  • Command+Shift+→ to select the row(s) of data.

4. Use the Fill Handle to instantly copy down: When creating a formula or entering numbers or text into a cell, dragging the contents of that cell down a long column can be time-consuming and tedious. Instead, just double-click the Fill Handle (the green box in the bottom right corner of the cell) to instantly populate the column (see the screenshot below).

5. Resize a column: Sometimes you enter so much in a cell that it cannot all be seen, or you enter so little that there is a lot of extra space. Don’t try to manually resize the column. All you need to do is double-click the boundary between the column header that contains your data and the column header to the right of the column you are sizing. This will perfectly accommodate the contents of the cells in that column.

6. Quickly move a column: You may want to reorganize the order of columns in your Excel spreadsheet. Select the column you want to move and hover over the edge of the column until the cursor changes to an arrow icon. Click Shift and hold the left mouse button to drag and drop the column where you want the column in your spreadsheet.

7. Paste formulas as values: You may be using a spreadsheet that contains formulas, but before you email that spreadsheet, it might be wise to change the formulas to values to avoid the risk of any numbers being altered. Copy the entire worksheet by clicking the square on the top left of the spreadsheet located between the A column and the 1 row and clicking Ctrl+C on a PC or Command+C on a Mac. Select Paste, then select Paste Special on the Home tab. Select Values, then click OK.

8. Don’t show gridlines in your Excel spreadsheet: You may not want to show the gridlines in your Excel spreadsheet, for example, when preparing an invoice for clients. It may look sleeker and more professional without those gridlines. It’s very easy to remove them. Go to View and uncheck Gridlines.

9. Have Excel read the contents of the cells: You can have Excel read to you the contents of the cells for accessibility purposes or just convenience. First, access the Quick Access Toolbar at the top left of your screen. By default, the Quick Access Toolbar includes an icon to Save, Undo, and Redo. Click the dropdown arrow to the right of the Redo icon and choose More Commands. Choose Quick Access Toolbar if it is not already selected. Under Choose commands from:, choose All Commands. Then choose Speak Cells from the menu that appears below. Click Add, and click OK. Now place your cursor wherever you would like Excel to start reading.

You can access a video demonstration of these quick Excel tips below.

About the author

Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business at Middle Tennessee State University.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.