5 More Excel Tips and Tricks for Accounting Professionals

Microsoft Excel is the go-to program for many accounting professionals. Unfortunately, being an accounting professional doesn’t automatically make you an expert in the program. Taking the time to learn some Excel tips and tricks will likely help you boost your productivity and streamline your spreadsheets.

We previously shared some Excel insights from a CPA. Here are five more Excel tips for accounting professionals:

1. Use number formatting shortcuts

For circumstances when you need to format a large amount of data, Excel offers time-saving shortcuts for many common formatting functions. Experiment with these handy ones:

  • Format numbers to include two decimal places: Ctrl+Shift+1

  • Format as time: Ctrl+Shift+2

  • Format as date: Ctrl+Shift+3

  • Format as currency: Ctrl+Shift+4

  • Format as percentage: Ctrl+Shift+5

  • Format in scientific/exponential form: Ctrl+Shift+6

2. Use Sparklines to display data

Sparklines are a built-in feature of Excel that allow you to display small charts inside individual cells. These can be line charts, bar charts or simple win/loss charts. To create a Sparkline chart, select the range of numbers you’d like to include, click the “Insert” menu, then choose one of the chart options. Select a location range, which must be located along a single row or column in the same worksheet as your data range. Sparklines can help you easily display trends in your data in a compact format.

3. Manipulate data with pivot tables

When you have a large, detailed data set, pivot tables allow you to easily manipulate your data. These tables are interactive and can help you analyze data, detect patterns and make comparisons. Creating a pivot table is as easy as using the built-in PivotTable and PivotChart Wizard, located in the “Data” drop-down menu. The wizard helps you choose the data to include in your PivotChart and format that information in a meaningful manner. For more information on creating pivot tables, refer to this Microsoft Office tutorial.

4. Move between formulas and results

To efficiently switch between the cell data and formula, use the Ctrl+tilde (~) keystroke. This allows you to rapidly check formulas when working in a large spreadsheet.

5. Hide zero values

Hiding zero values can be helpful within large data sets by allowing you to see data more clearly. To hide zero values, you simply need to change the options in your Excel setup. Navigate to this function by clicking the “File” drop-down menu, and choose “Options.” Then choose “Advanced” from the left-hand menu and uncheck the box for “Show a zero in cells that have zero value.” (Mac users: Go to the “Excel” drop-down menu and choose “Preferences,” then uncheck “Show zero values.”)

These are just a few of the helpful Excel features that can decrease the time you spend on spreadsheets and increase the amount of work you get done. 

Accounting professionals using Excel 

For budgeting and planning, Excel remains the darling of the accounting and finance world, according to Robert Half's Benchmarking in the Accounting and Financial Function. About two-thirds of survey respondents in the United States (63 percent) rely on this ubiquitous spreadsheet software, especially among companies earning less than $5 billion annually. Executives praise Excel’s agility and usability. “For anything that is innovative or creative or requires that you bring some gray matter to the table, the spreadsheet cannot be beaten,” said one of the chief financial officers surveyed.

More Excel tips