If you're like many accounting managers, you're open to any Microsoft Excel tips and tricks that come your way, especially with the release of Microsoft Office 2019 and continual updates to Office 365 in the cloud.
Our Benchmarking the Accounting & Finance Function report shows that 63 percent of U.S. companies rely on Excel, down slightly from a year ago. While the program is used by organizations of all sizes, smaller companies (69 percent) find accounting with Excel especially valuable. Even the American Institute of CPAs has replaced its generic spreadsheet with Excel beginning with the 2018 CPA exam.
Excel is the preferred tool for budgeting and planning across accounting and finance functions, according to the Benchmarking report. 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 13 tips and features to bring more agility, functionality and usability to your Excel spreadsheets. Share this with your team as you work on upskilling your employees and temporary workers.
1. Fill cells quickly
Excel now has the power to finish your tedious info-typing session. Just start typing in the column to the right of your data.
Excel will then gather suggestions to fill the rest of your cells after typing a few examples. To accept Microsoft’s suggestions, press enter.
2. 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
3. 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.
4. Splice data easily
Slicing allows you to filter data easily.
To do so, select any range in a table or PivotTable, (refer to tip #5 for more information about PivotTables), and then go to Insert > “Slicer,” in the top right corner. Then, select the column you want to filter by.
5. 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.
6. Go formula free
Getting a total without an equation might just be one of Excel's most valuable features.
To get your total formula, click anywhere inside the Excel table. Then press Ctrl+Shift+T, and Excel will add the total to your total row that you created.
7. 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.
8. Use the status bar without a formula
The status bar shows counts, sums and averages, without typing up any formulas.
Just select the cells in your table and look to the bottom right of the Excel window. You’ll see instant stats about the cells you selected.
9. 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.”)
10. 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.
11. Chart your data quickly
Select any cells in your data range/ table and then press ALT+F1 on your keyboard. Now you have an instant chart.
12. 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.
13. Create tables quickly
You can turn data cells into a table quickly!
All you have to do is click within the parameters of your data and press Ctrl+T. Now you have an instant table displaying your data.
Now that you have some helpful features to reboot your Excel skills and those of the accounting professionals you manage, you will get more done, and before you know it, you’ll be expanding your team!
