Microsoft Excel may be your go-to tool for budgeting, planning and analysis, but if you're like many accounting professionals, you're open to any Excel tips and tricks that come your way.
Our Benchmarking the Accounting & Finance Function report shows that 69 percent of U.S. companies rely on Excel, up slightly from 2016. While the program is used by organizations of all sizes, smaller companies (78 percent) find it especially valuable.
Excel will likely help you boost your productivity, streamline your spreadsheets and maximize your workday. As one of the CFOs surveyed said, “For anything that is innovative or creative or requires that you bring some gray matter to the table, the spreadsheet cannot be beaten."
Here are seven Excel tips for more agility and usability:
1. Format spreadsheets faster
When working on a spreadsheet, there are ways to add special formatting to different cells, columns and rows with less effort.
After applying any formatting to the spreadsheet — such as making a cell yellow or adding borders to a group of cells — you can use the format painter function to copy that formatting to other cells. If you double-click on the format painter icon, you can lock the function and single-click on any parts of the spreadsheet that you want to format. When you’re done, hit escape to unlock your cursor.
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
Read a CPA's 5 Excel Skills to Become a Fanatic Like Me.
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.
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.”)
6. Use Excel templates
Templates are available for any version of Excel. If you want to create an amortization table using a template, right-click on a blank worksheet and gain access through the “Insert” command. Under the “Spreadsheet Solutions” tab, you will find various templates, including an amortization schedule, which can then be inserted into the spreadsheet. You can also create your own templates by saving a worksheet as an Excel template.
7. Save time looking for worksheets
If you access the “File” menu or “Office Button” in Excel — depending on the version you are using — there’s a handy button called “Recent.” Using this function, you can quickly generate a list of the workbooks you recently worked on.
In the “Advanced” section of the “Options” menu, you can expand your view and adjust the number of recent documents shown. You can change the number to 50 for better access to recent folders and files.
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.