5 More Excel Tips and Tricks for Accountants

Microsoft Excel is the go-to program for many accounting professionals. Unfortunately, being an accountant 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 tips to consider:

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 2010, Excel 2011 for Mac and Excel 2013 that allow you to display small charts inside 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.”)