Top 2 Excel Tips That Every Accountant Should Know

As a chartered accountant, you’ll have to learn Microsoft Excel. No matter you love it or hate it, this is one tool that’ll streamline your work and that’ll let you save hours of headache. But, sadly, we’ve seen countless accountants not knowing the power of Excel; some hapless ones haven’t even heard of this powerful software. These dismal facts have actually motivated us to write this post. So without wasting any more time, let’s get started.
Leverage PivotTable to summarise and analyse data
A spreadsheet ninja will always set much store by the power of this powerful Excel tool. This tool is very quick to summarise a growing list of data at the push of a button. This tool is simple to work with and makes data management a cakewalk. But before working with PivotTable, you’ll have to ensure that your data sources are fully organised. Here are the steps to use PivotTable.
•You’ll have to start by selecting a cell having your source data.
•Click the PivotTable that has an insert tab.
•Pick any of the fields that you’ll have to add in your PivotTable report.
•You’ll, then, have to drag a “Label” field within your Row Labels area.
•Further, drag a specific field within your Values areas.
•While working with PivotTable, you may even pick from a number of options including percentage of running total and grand total.
Keeping your cell referenced fixed along with absolute references
Excel picks “relative references” as a default, which means that all the cell references (even the formulae) will be easily copied elsewhere. For instance, you have a specific formula in, say, cell A1 and you copy the formula to, say, cell B1—the result will be automatically and instantly updated within your reference cell’s positions.
Nevertheless, you’ll find times when you’ll want all your cell references to remain fixed in a single position while you copy it anywhere else. Here, you’ll need “absolute reference.” With this trick, you’ll be able to make the cell reference unchanged no matter where and when you copy the formula. Whenever you decide to copy the formula to any other sheet or cell, that same value/formula will still refer to its original cell reference. For creating absolute references, you’ll need a specific dollar sign ($) so that you’re able to fix your row and column references. You’ll have to use diverse absolute references, which we’ve given below, so that you can anchor each of your references.
•A$1: This command won’t let the row to be not changed whenever a value is copied.
•$A1: This command won’t permit the value of a column to be changed whenever a copy function is initiated.
•$A$1: This function will enable the row and the column to remain unchanged while copying of
data is done.
For avoiding the addition of absolute reference manually, you should press the key “F4” multiple times until the correct value is displayed.
So that’s it for now, readers. This and many other accounting software support tips and tricks will be shared with you in the future. To be in the know, you’ll have to just bookmark this page or keep checking this space periodically.