Skip to content

This site uses cookies to store information on your computer. Some are essential to make our site work; others help us improve the user experience. By using the site, you consent to the placement of these cookies. Read our privacy policy to learn more.

Close
AICPA-CIMA
  • AICPA & CIMA:
  • Home
  • CPE & Learning
  • My Account
Journal of Accountancy
  • TECH & AI
    • All articles
    • Artificial Intelligence (AI)
    • Microsoft Excel
    • Information Security & Privacy

    Latest Stories

    • AI-driven spreadsheet tools — what CPAs need to know
    • Is spending on technology spinning out of control?
    • Using 3 Excel View tools to manage large spreadsheets
  • TAX
    • All articles
    • Corporations
    • Employee benefits
    • Individuals
    • IRS procedure

    Latest Stories

    • Job cuts mean strong 2025 tax season may be hard to repeat, IRS watchdog warns
    • IRS removes associated property rule in final interest capitalization regulations
    • Spouse is not entitled to sales proceeds in a judicial sale of taxpayer’s home
  • PRACTICE MANAGEMENT
    • All articles
    • Diversity, equity & inclusion
    • Human capital
    • Firm operations
    • Practice growth & client service

    Latest Stories

    • PCAOB publishes guidance related to Audit Evidence amendments
    • AI-driven spreadsheet tools — what CPAs need to know
    • Job cuts mean strong 2025 tax season may be hard to repeat, IRS watchdog warns
  • FINANCIAL REPORTING
    • All articles
    • FASB reporting
    • IFRS
    • Private company reporting
    • SEC compliance and reporting

    Latest Stories

    • SEC accepting Professional Accounting Fellow applications
    • SEC names new chief accountant
    • SEC ends legal defense of its climate rules
  • AUDIT
    • All articles
    • Attestation
    • Audit
    • Compilation and review
    • Peer review
    • Quality Management

    Latest Stories

    • PCAOB publishes guidance related to Audit Evidence amendments
    • AICPA unveils new QM resources to help firms meet Dec. 15 deadline
    • 8 steps to build your firm’s quality management system on time
  • MANAGEMENT ACCOUNTING
    • All articles
    • Business planning
    • Human resources
    • Risk management
    • Strategy

    Latest Stories

    • Business outlook brightens somewhat despite trade, inflation concerns
    • AICPA & CIMA Business Resilience Toolkit — levers for action
    • Economic pessimism grows, but CFOs have strategic responses
  • Home
  • News
  • Magazine
  • Podcast
  • Topics
Advertisement
  1. newsletter
  2. Cpa Insider
CPA INSIDER

Basic Excel time-saving tips

Here are shortcuts, features, and tools that can save you time, help you build better workbooks, and deepen your understanding of the numbers.

By Liz Farr, CPA
November 27, 2017

Please note: This item is from our archives and was published in 2017. It is provided for historical reference. The content may be out of date and links may no longer function.

Related

November 1, 2017

Microsoft Excel: Fixing a proofing formula that fails every time

November 1, 2017

Expect the unexpected: Risk assessment using Monte Carlo simulations

October 1, 2017

Microsoft Excel: 3 quick tips

TOPICS

  • Technology
    • Information & Data Management
  • Professional Development
    • Communication

Are you getting all you can out of Excel?

Excel has plenty of features, shortcuts, and tools that can save you time, improve the integrity of your workbooks, and simplify data analysis. Here are some tips to up your game. This article is based on Excel 2016; previous versions and Excel for the Mac may not have all these features or may work slightly differently.

1. Keyboard shortcuts

Keeping your hands on the keyboard shaves seconds off many tasks. While the few extra seconds it takes to use your mouse may not seem like much, as Anastasia Papadatos, general ledger accountant at Noodles & Company, a publicly traded restaurant chain headquartered in Broomfield, Colo., said, “When you’re able to combine all those keyboard shortcuts and stop using your mouse, that time that you’re saving builds up exponentially.”

Beyond the basics such as Ctrl+c to copy and Ctrl+v to paste, here are some useful shortcuts for Excel 2016:

             Ctrl+1 — opens the Format Cells dialog box

             Alt+= — inserts the sum function to add up all the numbers above it

Advertisement

             Ctrl+Arrow key — moves selection to the edge of a data region

             Shift+Arrow key — extends selection one cell at a time

             Ctrl+Shift+Arrow key — extends selection to the edge of a data region

             Ctrl+Home — moves selection to cell A1

             Ctrl+End — moves selection to last cell containing data

             Ctrl+Page Up/Page Down — toggles to previous/next worksheet in workbook

             F4 — changes a cell reference in a formula to an absolute reference

Advertisement

             F2 — shows the formula in a cell and enters edit mode

             Alt+IR or Alt+HIR — inserts a row

             Alt+IC or Alt+HIC — inserts a column

Brian Tankersley, CPA/CITP, CGMA, director of strategic relationships at K2 Enterprises, suggests holding down the Alt key, “and you’ll see some letters show up, and if you type those letters, it’ll select different ribbon tabs, and it’ll actually select individual commands.”

Need an easy way to fill a formula down a column? Just double-click on the lower right corner of a cell with the formula.

Want more shortcuts? Here and here are some more.

2. Use the Quick Access Toolbar

Advertisement

Are there formulas or commands you use frequently, such as SUM or Sort? Adding those to the Quick Access Toolbar can save you time and effort, especially if they’re buried in menus or on one of the tabs, according to Tankersley. The first nine slots on the left are the prime positions because those can be accessed by holding down the Alt key and typing the number corresponding to that icon. You can add items to the Quick Access Toolbar using one of three methods. The easiest is to simply right-click on the icon in the ribbon and choose Add to Quick Access Toolbar.

Next easiest is to click on the downward triangle at the right edge of the Quick Access Toolbar, as shown below. You can choose one of the commands already there, or select More Commands.

Quick Access Toolbar


A third option is to choose File > Options > Quick Access Toolbar as shown below.

Quick Access Toolbar 2


3. Use the SUBTOTAL function instead of SUM

Jeff Lenning, CPA/CITP, founder and president of Excel University Inc., says that accountants should master the SUBTOTAL function. The SUBTOTAL function can be used repeatedly in the same column, then again at the bottom of that column to compute a grand total. Unlike the SUM function, SUBTOTAL won’t double-count the amounts included in the subtotals above it.

Besides adding up the numbers in a range of cells, the SUBTOTAL function can give you the average, maximum, and minimum of that range. To use the SUBTOTAL function to find the sum of a range of cells, use this syntax:

          =SUBTOTAL(9,range)

Advertisement

To learn more about using the SUBTOTAL function, check out this article by Lenning.

4. Store data in tables, not ranges

Lenning recommends storing expanding data sets in tables. Without a table, formulas that refer to a range won’t include rows added under the data set. Correcting the formula to include those new rows requires manual editing. However, when the data set is converted to a table, formulas automatically update to include the new rows.

To create a table, select any cell in the data set you want to convert to a table. Then on the Insert tab, choose Table. A dialog box will pop up to guide you through the creation of your table. If the first row of your data set has column descriptions, be sure to click the box for “My table has headers.” In this article, Lenning shows how to create a table and demonstrates the basic functionalities.

Besides keeping formulas updated and adding nice formatting, putting your data into a table makes it easier to manage and analyze data. This article demonstrates the analytical possibilities when a QuickBooks general ledger is converted to a table, and this article shows how tables can be used to create automated recurring reports.

5. PivotTables

Once your data is in a table, the PivotTable function allows you to quickly and easily analyze and summarize the data. Both Tankersley and Papadatos consider PivotTables to be one of the crucial Excel skills every accountant should master. “You can get a quick snapshot of what you’re looking for, and you can easily change it,” said Papadatos. “You can quickly get the sum of all your invoices, or the average, or the minimum, or the maximum.”

Here’s an example that shows how to summarize bank account transactions using a PivotTable. First, the data are converted to a table following the above instructions. We want the totals by category, which is in the column labeled Acct. After clicking on a cell in the table, Summarize Data with PivotTable is selected and the dialog box Create PivotTable pops up. Click OK, and a PivotTable will be created on a new sheet.

Summarize Bank Account Transactions PivotTable


In the new sheet, click in the check box beside Acct, then Amount, to create a PivotTable that summarizes the data by category.

Summarize Data by Category PivotTable


There’s always more to learn about Excel

These tips only brush the surface of what Excel can do. Tankersley has been doing “heavy-duty Excel since the late ’80s,” and said, “It’s amazing to me how there is still so much to learn.” As a demonstration of the surprising features of Excel, here’s a video Tankersley made using the Excel add-in Power Map. This video shows the hometowns of attendees at the 2016 AICPA Practitioners/TECH+ conference in Las Vegas.

Lenning encourages accountants to think of Excel as a platform that enables automation of manual, mechanical, data-driven tasks. “Right now, accountants are faced with this situation that they have more work than time,” he said. “Everyone’s busy; they’re stressed out. The coolest thing is to figure out how to have Excel get your work done for you so that you have more time in your schedule than work.”

Liz Farr, CPA, is a freelance writer based in Los Lunas, N.M. To comment on this article, contact Chris Baysden, senior manager of newsletters at the AICPA.

Advertisement

latest news

October 3, 2025

PCAOB publishes guidance related to Audit Evidence amendments

October 2, 2025

Job cuts mean strong 2025 tax season may be hard to repeat, IRS watchdog warns

October 2, 2025

Is spending on technology spinning out of control?

October 1, 2025

IRS removes associated property rule in final interest capitalization regulations

September 30, 2025

IRS withdraws prop. regs. affecting corporate spinoff transactions

Advertisement

Most Read

Why accountants need to master the art of reading the room
MAP Survey finds CPA firm starting pay on the rise
IRS finalizes regulations for Roth catch-up contributions under SECURE 2.0
Paper tax refund checks on the way out as IRS shifts to electronic payments
NASBA, AICPA release proposed revisions to CPE standards
Advertisement

Podcast

October 2, 2025

Car talk: M&A, AI and EVs changing the dealership landscape

September 25, 2025

Professional liability risks related to Form 1065, CPA firm acquisitions

September 18, 2025

‘We’re still the thinkers’ — a reminder for tax pros in the AI era

Features

AI-powered hacking in accounting: ‘No one is safe’
AI-powered hacking in accounting: ‘No one is safe’

AI-powered hacking in accounting: ‘No one is safe’

Building a better firm: How to pick the proper technology
Building a better firm: How to pick the proper technology

Building a better firm: How to pick the proper technology

Why accountants need to master the art of reading the room
Why accountants need to master the art of reading the room

Why accountants need to master the art of reading the room

How BI and analytics enhance management accountants’ partnering role
How BI and analytics enhance management accountants’ partnering role

How BI and analytics enhance management accountants’ partnering role

SPONSORED REPORT

Preparing clients for new provisions next tax season

Preparing clients for new provisions next tax season

As the 2025 filing season approaches, H.R. 1 introduces significant tax reforms that CPAs must be prepared to navigate. These legislative changes represent some of the most comprehensive tax updates in recent years, affecting both individual and corporate taxpayers. This report provides in-depth analysis and guidance on H.R. 1.

From The Tax Adviser

September 30, 2025

Current developments in taxation of individuals: Part 1

August 30, 2025

2025 tax software survey

August 30, 2025

Are you doing all you can to keep the cash method for your clients?

July 31, 2025

Current developments in S corporations

MAGAZINE

October 2025

October 2025

October 2025
September 2025

September 2025

September 2025
August 2025

August 2025

August 2025
July 2025

July 2025

July 2025
June 2025

June 2025

June 2025
May 2025

May 2025

May 2025
April 2025

April 2025

April 2025
March 2025

March 2025

March 2025
February 2025

February 2025

February 2025
January 2025

January 2025

January 2025
December 2024

December 2024

December 2024
November 2024

November 2024

November 2024
view all

View All

http://JofA_Default_Mag_cover_small_official_blue

PUSH NOTIFICATIONS

Coming soon: Learn about important news

CPA LETTER DAILY EMAIL

CPA Letter Logo

Subscribe to the daily CPA Letter

Stay on top of the biggest news affecting the profession every business day. Follow this link to your marketing preferences on aicpa-cima.com to subscribe. If you don't already have an aicpa-cima.com account, create one for free and then navigate to your marketing preferences.

Connect

  • X Logo JofA on X
  • facebook JofA on Facebook

HOME

  • News
  • Monthly issues
  • Podcast
  • A&A Focus
  • PFP Digest
  • Academic Update
  • Topics
  • RSS feed rss feed
  • Site map

ABOUT

  • Contact us
  • Advertise
  • Submit an article
  • Editorial calendar
  • Privacy policy
  • Terms & conditions

SUBSCRIBE

  • Academic Update
  • CPE Express

AICPA & CIMA SITES

  • AICPA-CIMA.com
  • Global Engagement Center
  • Financial Management (FM)
  • The Tax Adviser
  • AICPA Insights
  • Global Career Hub
AICPA & CIMA

© 2025 Association of International Certified Professional Accountants. All rights reserved.

Reliable. Resourceful. Respected.