If you’ve ever tried to format your spreadsheets in Excel, you know that it can be time-consuming. Businesses of all sizes and across industries rely on it to collate their data and to see the bigger picture. Though we know that Excel is limited when it comes to lease accounting, we still know that many accountants use it in tandem with a lease accounting software, especially to export reports.
We interviewed a few of our in-house accountants to get an idea of how they use Excel. These tips can help you move faster in the program and handle your reporting with ease. Most of the tips below use hotkeys, which are keyboard shortcuts that help you do the things you need in Excel quicker.
Andrew McCown, Sr. Manager of Technical Accounting stated his best tip was Alt + CTRL + V, V (for values) + Enter = Paste, which allows you to paste only copied values, not formulas. Another shortcut he uses to copy data or functions down a specific column is to “double click the little black box in the corner of a cell with formulas to automatically copy them down the column.”
Jason Parker, one of our Technical Accounting Managers, uses hotkeys whenever he uses Excel: “I’m a big hotkeys guy.”
What hotkey tips does he recommend? “Pressing the ‘Alt’ key will allow you to select the different header tabs within Excel by touching the appropriate letter key. For example, Alt + H brings up the ‘Home’ tab keys.
Alt + H + B will bring up the ‘borders’ feature, and Alt + H + B + T will produce the thick black underline in the selected cell.”
Additionally, Jason recommends using Alt + H + 1 to bold something, Alt + H + 2 for italics, and Alt + H + 3 to underline.
Griffin Larkin, another Technical Accounting Manager, offers “easy tips” to help you navigate Excel using hotkeys:
- CTRL + down arrow (or any arrow) will highlight the entire column or row of data.
- CTRL + C = copy
- CTRL + V = paste
- CTRL + X = cut
- CTRL + Z = undo last function
- CTRL + Y= redo last function
Getting it in formation
If you’ve ever spent time attempting to format your spreadsheets in Excel, you know that it can be time-consuming. Rachel Reed, another member of our Technical Accounting team, has a few tips to help with formatting in Excel. “Pressing the ‘Alt’ key, then H will bring you to the home menu, then you can navigate through the commands… some of my favorites for formatting:”
- Alt + H + H + N removes the background color of a cell
- Alt H + F + P format painter- will take whatever format you have on that cell (colors, text size, font, etc.) and copies it so you can apply to other cells
- Holding Alt and + then enter will sum the numbers in the column above
- Control shift and then an arrow will highlight either the row or the column
- Alt + A + T will apply a filter
Ryan Stowe & David Dobson, two of our Lease Accounting Managers, collaborated on their answers. To keep your spreadsheets looking good, they recommend:
- Alt + H + O + I = autofit column width
- Alt + H + O + A = autofit column height
- Ctrl + space = highlight column
- Shift + Space = highlight row
- Alt + E + S = paste special menu
- Index + Match Functions = to match and return items from a range of items
- Indirect Function = use cell value in a formula
Ctrl + G = go to specific cells
- Named ranges = reference cell ranges by name rather than address
- F4 = toggle between locked cells
When you need to multitask
Often, our accountants are working on multiple Excel spreadsheets at once, or are using multiple applications to get the job done. Amanda Payne, another member of our Technical Accounting team, has a few go-to tips when she needs to work on multiple amortization schedules at once.
- Ctrl + Page Up/Page Down = allows you to move from sheet to sheet within the Excel file
- Alt + W + F + F = allows you to freeze all cells above the cursor
- Alt + I + R = lets you add a new row by selecting the row below where you want to add the new row
- Alt + I + C = lets you add a new column by selecting a column to the right of where you want to add the new column
- Alt + H + B + U = this creates the summation line (a single line at the top of the cell and double line at the bottom of the cell)
- Alt + H + B + N = clears all borders from a cell
If you need to toggle between your spreadsheets and the internet, Amanda recommends using Alt + Tab, which allows you to move to different applications open on the screen.
Bringing it together
Companies across industries rely on Excel to collate their data and perform basic tasks. We know that when it comes to tracking your leases, Excel may not be the most robust system to use. However, as you compare lease accounting systems, you’ll find that most allow for integration with Excel where you can export your amortization schedules, reporting, and more. These tips should help you manage your Excel spreadsheets and keep them looking streamlined and professional. For more tips and help, contact us today to learn more or check out our free tools for lease accounting and lease management. We have several Excel calculators and spreadsheets that can make your life easier.