Spreadsheet Tips and Tricks for Accountants
Many accountants often spend more time formatting a spreadsheet or attempting to make the cells to execute what they have in mind as opposed to actually inputting the data. But you can be the Excel guru of your office and ‘magically’ manoeuvre through the spreadsheet, as would an expert from any of the leading firms providing accounting services in singapore.
Here are some Excel shortcuts that’ll give you an edge over your colleagues:
Navigating Through Worksheets and Workbooks
Shifting data from one Excel worksheet or workbook to another can be frustrating, especially when you are manually clicking back and forth. It’s a time waster, not to mention it makes you prone to a ton of mistakes.
However, there are ways to have an easier time transferring the data and when editing. Here’s how:
If you are moving data from one worksheet to another, you can move to the next worksheet by pressing CTRL+PageUp. To move to the previous worksheet just press CTRL+PageDown
If you are working with different workbooks, a simple instruction of ALT+Tab will help you quickly toggle between workbooks.
Copying Visible Cells Only
Accountants frequently work with spreadsheets that contain tons of data. Often, you have to use the filter function to narrow down to more relevant metrics.
However, when it comes to moving only the relevant data to another spreadsheet, you’ll be surprised at how many accountants still struggle.
These steps will help you copy over filtered data and let you transfer it to another spreadsheet.
First, highlight the target data you want to copy.
Hit F5 and click the “special” button in the bottom left corner.
Choose the option of “Visible Cells Only” and click OK.
On your highlighted worksheet, Press Ctrl “C” to copy.
Then paste the data into the applicable worksheet.
Copying and Editing Formulas
For some accountants, the task of copying a formula from one cell and editing it in another can be a challenge. On other occasions, editing a copied formula takes several tries, and you’ll find frustrated accountants saying the cells are just stubborn. However, you can just press the control and apostrophe key (CTRL+ ‘) to paste the formula in the cell above and the cell below, and immediately enter editing mode.
Quick Analysis and Conditional Formatting
To crown you the Excel guru, you should be able to make quick analysis and inferences from data even before you make further processing. The quick analysis tool (Select your data and from the icon on the bottom right corner, pick the quick analysis tool) is useful when working with small datasets. It minimises the time spent creating charts.
Another tool is Conditional Formatting (Select your cells> Home> Conditional Formatting> Add). It’s a handy tool when you want to highlight cells with points of interest. For instance, you may want to see the efficiency of your collections and highlight payments more than 90 days overdue.
These tools will not only take away the headache of going round in circles on a simple issue but also increase your efficiency and make you look more organised.