If you are an accountant, you almost certainly use Microsoft Excel.
If you're like most Excel users, you almost certainly don't take full advantage of the application's numerous time-saving features.
That's OK. Not everybody can be an Excel savant — but almost everybody can get better.
This article features a quartet of Excel tricks to make you more efficient — and effective — with the world's most popular spreadsheet application. Each tip includes a video walkthrough. You can also download an Excel file with separate worksheets for each trick.
Note: The content of this article is based on Microsoft Excel 2016 for PCs. Other versions of Excel may work differently. More details on other Excel versions can be found by searching for the version (e.g., "Excel 2010" or "Excel for Office 365") at support.office.com.
How to quickly foot and cross-foot in Excel
Many accountants consider it good practice to foot and cross-foot a table of values. This means totaling the values of each column (foot) and then verifying the accuracy of those totals by summing together all foots and comparing it to the sum of all rows (cross-foot). Fortunately, Excel can do this in two quick steps.
AutoSum can be used to insert sum formulas that total all columns and rows at the same time. Highlight the data, plus one additional row below and one additional column to the right of the data. On the Home tab, click the AutoSum button, or press Alt + = (equal sign).
Play video walkthrough:
Simultaneously editing multiple worksheets in the same Excel workbook
Flipping between worksheets in an Excel workbook can become tedious and time consuming, especially for tasks such as linking cells from other worksheets. It is faster to have two (or more) versions of the same workbook open. You can do this by clicking View, New Window. Now there will be two versions of the same workbook open, and you can work in different worksheets at the same time. In the illustration below, the income statement balances are being linked to the balances on the year-end worksheet without having to flip back and forth between tabs. Keep in mind, even though you are viewing and working within two or more copies of this workbook, all of your changes are saved in that one workbook.
Play video walkthrough:
How to format faster in Excel
Excel has a Format Painter feature that makes it easy to copy the formatting of a cell and "paint" that same formatting on another cell. However, you may not be aware that you can lock that format painter to "paint" that same formatting on as many different cells as you like, as many times as you like.
It's easy to use. First, apply any formatting to your worksheet. In the example below, a bottom border is being added to cell C9.
Next, double-click the Format Painter icon.
This will lock the Format Painter, and any cell you click will have that same formatting applied. This can be done to as many cells as you like. In the example below, the bottom border was "painted" to cells B14, B16, C17, C29, C36, C40, and C44 just by clicking those cells.
When you are finished, press Esc (escape) and the Format Painter will unlock.
Play video walkthrough:
How to easily calculate end of month in Excel
You can easily calculate the end of the month using the function EOMONTH.
For the Start_date, either enter a date or reference the cell that contains the date (J2 in the screenshot above).
For Months, enter the number of months before or after the start date (0 in the screenshot above).
For example, if you would like to have Excel return the end of the same month as the start date, enter 0 for Months. If you would like Excel to enter the end of the next month, enter 1 for Months. If you would like Excel to enter the end of the previous month, enter -1 for Months.
Play video walkthrough:
— Kelly L. Williams, CPA, Ph.D., MBA, is an assistant professor of accounting in the Jones College of Business at Middle Tennessee State University. To comment on this article or to suggest an idea for another article, contact Jeff Drew, a JofA senior editor, at Jeff.Drew@aicpa-cima.com.
